Rock Bottom Dev Bus

+ sqlDeveloper 대체변수 '&' 입력 - sqlDeveloper에서 쿼리를 실행 할 때 '&' 기호 등과 같은 특수문자가 포함된 텍스트 문자가 있는 경우 아래 사진과 같이 대체 변수를 입력하라는 팝업 창이 나올 경우 대처 방법. ​ 1. SHOW DEFINE; 현재 어떤 문자에 대체 변수 기능이 걸려있는지 확인. * 조회가 안되는 경우도 있는데 안되도 상관없음. 2. SET DEFINE OFF; 1번에서 조회한 문자에 대체 변수 기능을 끔. 3. SET DEFINE ON; 이 옵션을 다시 켤 때 사용. ​ 4. SET DEFINE 다른문자열; 대체 변수 기능을 끄거나 다른 문자열로 지정 가능 - 기존 '&' 기호가 아닌 다른 문자열을 가지고 변수를 사용하겠다고 선언하는 방법이다.

 + 오라클 조회 순번 매기는 방법 : 로우넘 사용법 (HR예제) 1. ROWNUM 사용방법 - 조회된 순서대로 순번을 매김. SELECT  ROWNUM , a.* FROM EMPLOYEES a; - ORDER BY 를 사용하면 순번이 뒤섞이므로 정렬된 서브쿼리 결과에 ROWNUM 을 매겨야 함 SELECT       ROWNUM      , x.* FROM (                SELECT                      a.*      FROM EMPLOYEES a                ORDER BY a.FIRST_NAME ) x; 2. ROW_NUMBER() 함수 사용방법  - ORDER BY 된 결과에 순번을 매길때에는 ROWNUM 보다 ROW_NUMBER() 함수가 더 편함  SELECT       ROW_NUMBER() OVER(ORDER BY a.JOB_ID, a.FIRST_NAME) row_num      , a.* FROM EMPLOYEES a ORDER BY a.JOB_ID, a.FIRST_NAME; - 그룹별(PARTITION)로 순번을 따로 부여할 수 있다. SELECT       ROW_NUMBER() OVER(PARTITION BY a.JOB_ID ORDER BY  a.JOB_ID, a.FIRST_NAME) row_num      , a.* FROM EMP...

+ Sqldeveloper에서 DB를 Erd형태로 보는 방법 - ERD는 ExErd 같은 Tool을 사용해서만 볼 수 있는 줄알았으나 DBMS 에서도 테이블 구조를 ERD형태로 볼수있다는걸 알게되었다.  1. 파일 >> data modeler >> 임포트 >> 데이터 딕셔너리 선택 2. 접속 정보 선택 >> 다음 3. 해당 스키마 데이터베이스 체크(계정명 호스트네임) >> 다음 >> 다음  4. 완료 선택 5. 병합 선택 6. 완료  * ( 참고 ) : 중간에 문제가 생기면 프로그램 종료 후 다시시도할것 혹은 재부팅할 것

1. 셈플데이터 다운로드 https://github.com/oracle/db-sample-schemas/releases/tag/v19.2 2. ORACLE_HOME 경로 확인 : ex) C:\app\YG\product\21c\dbhomeXE\demo\schema https://rockbottomdevbus.blogspot.com/2021/12/oracle-oraclehome-path-how-to-find.html 3. CMD >> Sqlplus / as sys dba  실행 4. 오라클 12C 이상 버전 아래 스크립트 실행 alter session set "_ORACLE_SCRIPT"=true; 5.  스크립트 실행 : tip @ = 실행, ? = 오라클홈 환경변수 위치 @?/demo/schema/human_resources/hr_main.sql  6. - hr 비밀번호 : hr     - hr 기본 테이블스페이스지정 오라클튜토리얼 권고사항 : users    - hr 임시 테이블스페이스 : temp    - SYS 비밀번호 : 원하는비밀번호    - 로그 위치 :  스키마 하위 log 폴더       - 정상적으로 실행되었는지 확인 :       select * from employees where rownum =1        1 row selected 라고 출력되면 정상 7. hr계정 접속해보기   - conn  사용자명 입력 : hr 비밀번호 입력 : hr 연결되었습니다. (정상) 8. dbTool 에서 접속해보기 출처 : https://jhnyang.tistory.com/274

1. 명령 프롬프트창(cmd) 열기 2. sqlplus / as sysdba 입력 엔터 (sqlplus 접속) 3.  var OHM varchar2(100); 입력 엔터 4.  EXEC dbms_system.get_env('ORACLE_HOME', :OHM ); 입력 엔터 5.  PRINT OHM 입력 엔터 = 오라클 HOME 디렉토리 경로가 출력된다.

*2개 이상의 컬럼에 DISTINT(중복제거) 가 필요할 때 아래처럼 사용해야 합니다. 1. 잘못된 사용법  SELECT       DISTINCT column1      , DISTINCT column2 FROM table_name; 2. 올바른 사용법 SELECT       DISTINCT column1      , column2 FROM table_name;

1. 에러 원인 : 여러 컬럼들을 그룹으로 묶어서 조회 시 컬럼 수 가 다르면 발생 -에러발생 구문 SELECT  CARNAME, CARNUM FROM CAR_INFO GROUP BY  CARNAME; -정상 구문  SELECT  CARNAME, CARNUM FROM CAR_INFO GROUP BY  CARNAME, CARNUM;

system 계정에 접속 시 비밀번호를 설정해둔 수만큼 틀리게 될경우 계정이 Lock에 걸린다. - 오라클 계정 Lock 해제방법  1. 윈도우키 >> CMD(명령 프롬프트) >> sqlplus 2. 사용자명 입력 : /as sysdba  3.  비밀번호가 틀려서 잠긴계정 목록 조회 SELECT USERNAME FROM DBA_USERS WHERE ACCOUNT_STATUS ='LOCKED(TIMED)'; 4. 잠긴계정(시스템) 잠금해제 ALTER USER SYSTEM ACCOUNT UNLOCK;  5. 시스템 계정 비밀번호 변경 ALTER USER SYSTEM IDENTIFIED BY 원하는 비밀번호 ; (SYSTEM계정) 6. 시스템 계정 접속 CONN >> 사용자명 입력 : SYSTEM / 비밀번호 입력 : 변경한비밀번호 - 오라클 계정 상태 종류 OPEN : 계정을 사용할 수 있는 상태. 일반적으로 접속 가능한 유저는 이 상태 EXPIRED : 비밀번호가 만료되서 사용할 수 없는 상태. 계정의 비밀번호를 변경해주어야 함 EXPIRED(GRACE) :  PROFILE에 PASSWORD_GRACE_TIME으로 설정된 기간(만료날로부터 - PASSWORD_GRACE_TIME)이 되면 해당 상태가 됨. 비밀번호를 변경하라고 경고 해주는 상태. LOCKED(TIMED) : PROFILE에 PASSWORD_LOGIN_ATTEMPTS에 설정된 값 만큼 접속시도를 했으나 실패 시 나오는 상태.  UNLOCK 명령어로 OPEN으로 변경해줘야 계정 사용가능

오라클 설치 시 system 비밀번호가 기억이 나지않는다. 아래 프로세스를 따라해보자. - 설치 시 system 비밀번호를 설정하지 않은 경우 1. 윈도우키 >> CMD(명령 프롬프트) >> sqlplus 2. 사용자명 입력 : system    비밀번호 입력 : (아무것도입력하지않고 엔터) - 비밀번호를 설정했지만, 기억이 나지않는경우  1. 윈도우키 >> CMD(명령 프롬프트) >> sqlplus 2. 사용자명 입력 : /as sysdba  3. alter user system identified by 원하는 비밀번호 ; (system계정)    alter user sys identified by 원하는 비밀번호 ; (sys계정) 

1. 함수생성 CREATE OR REPLACE FUNCTION test_func      RETURN VARCHAR IS      v_temp VARCHAR2(100); BEGIN      v_temp := 'testFunction'; RETURN v_temp; END; 2. 실행 SELECT test_func() FROM DUAL; 3. 결과

1. 오라클 함수 / 프로시저 조회 쿼리 - 함수이름중 (fu -> FU)가 포함되있는 모든 함수 조회 (조건:USER = SYSTEM)  SELECT * FROM ALL_SOURCE WHERE TYPE = 'FUNCTION' AND NAME LIKE UPPER('%fu%') AND OWNER = 'SYSTEM' ; - 함수이름중 (pro -> PRO)가 포함되있는 모든 프로시저 조회 (조건:USER = SYSTEM)  SELECT * FROM ALL_SOURCE WHERE TYPE = 'PROCEDURE' AND NAME LIKE '%DEC%' AND OWNER = 'SYSTEM' ; - 전체 함수 생성 일자로 조회 SELECT *  FROM DBA_OBJECTS WHERE 1=1 AND OWNER = 'SYSTEM' AND OBJECT_TYPE = 'FUNCTION' ORDER BY CREATED DESC  ; SELECT * FROM ALL_OBJECTS WHERE OWNER ='SYSTEM' AND OBJECT_TYPE = 'FUNCTION' ORDER BY CREATED DESC ; - 전체 프로시저 생성 일자로 조회 SELECT  *  FROM DBA_OBJECTS WHERE 1=1 AND OWNER = 'SYSTEM' AND OBJECT_TYPE = 'PROCEDURE' ORDER BY CREATED DESC  ; SELECT * FROM ALL_OBJECTS WHERE OWNER ='SYSTEM' AND OBJECT_TYPE = 'PROCEDURE' ORDER BY CREATED DESC ; 2. 오라클 함수 / 프로시저 에러 조회 쿼리 - 함수 / 프로시저 에러 조회 방법  SELECT * FROM USER_ERRORS WHERE NAM...

1. DBeavert실행 / PROCEDURE 문법 TEST { CALL DECLARE BEGIN      DBMS_OUTPUT.put_Line('이효리'); END } 1. Mybatis 사용 예제 - carInfo.xml  <insert id="carInfoInsert" parameterType="mapDto">  { CALL DECLARE BEGIN       <![CDATA[           INSERT INTO SYSTEM.TB_CAR_INFO(                CAR_NUMBER           ) VALUES (                 #{carNumber, jdbcType=NUMERIC}           );       ]]> END } </insert>

1. 디비버 실행  2. 쿼리 순서대로 실행 - 자동차 정보 테이블의 데이터 중 자동차 브랜드가 기아인 데이터 존재여부  CREATE TABLE TB_CAR_INFO( CAR_NUMBER NUMBER NOT NULL , CAR_BRAND varchar2(20)  ); INSERT INTO TB_CAR_INFO (CAR_NUMBER,CAR_BRAND) VALUES (1,'​HYUNDAI'); DECLARE   V_CAR_NUM_EXT NUMBER(1); BEGIN SELECT CASE WHEN EXISTS( SELECT 1 FROM TB_CAR_INFO WHERE CAR_BRAND = 'KIA' )  THEN 1  ELSE 0  END INTO V_CAR_NUM_EXT FROM DUAL; IF V_CAR_NUM_EXT = 1  THEN DBMS_OUTPUT.put_line('exist'); ELSE DBMS_OUTPUT.put_line('does not exist'); END IF; END; 3. 결과 DBMS_OUTPUT = does not exist 4. 현대인 데이터 존재로 변경 시  SELECT 1 FROM TB_CAR_INFO WHERE CAR_BRAND = 'KIA'  >>  SELECT 1 FROM TB_CAR_INFO WHERE CAR_BRAND = 'HYUNDAI' 변경 DBMS_OUTPUT = exist * 참고 링크  https://stackoverflow.com/questions/13217600/if-exists-condition-not-working-with-plsql

1. 해당 예제 하기전에 이전글 먼저 해보시는걸 추천합니다. https://rockbottomdevbus.blogspot.com/2021/11/db-procedure-dbeaver-tutorial-select.html 2. PROCEDURE  오류 로그 조회 방법 SELECT * FROM USER_ERRORS; 3. PROCEDURE  업데이트 예제 (DBeaver 에서 실행) -- 테이블생성 CREATE TABLE TB_CAR_INFO( CAR_NUMBER      NUMBER NOT NULL , CAR_BRAND     VARCHAR2 (20)  ); -- 인서트  INSERT INTO TB_CAR_INFO (CAR_NUMBER,CAR_BRAND) VALUES (1,'기아'); -- 프로시저 생성  CREATE OR REPLACE PROCEDURE update_car ( param_num IN NUMBER , param_str IN VARCHAR2 )  IS  BEGIN  UPDATE TB_CAR_INFO  SET CAR_BRAND = param_str WHERE CAR_NUMBER = param_num;  COMMIT;  END update_car;  -- 프로시저 실행 BEGIN  update_car(1,'현대'); END; -- SELECT  SELECT * FROM TB_CAR_INFO;

디비버를 사용하여 간단한 오라클 프로시저 예제를 실행해보았는데 생각보다 막히는 부분이 많았다. 간단한 예제와 사용방법을 설명하겠다.  1. 오라클과 연동된 DBeaver 실행 : 아래링크 참고 https://rockbottomdevbus.blogspot.com/2021/10/db-xe-dbeaver-sqlplus-sql-xe11g-oracle.html 2. 아래쿼리 실행 : 블록잡고, 컨트롤 + 엔터 (세미콜론 주의) CREATE OR REPLACE PROCEDURE SIMPLE_PROC IS MESSAGE VARCHAR2(100) := 'SUCCESS';  BEGIN DBMS_OUTPUT.PUT_LINE(MESSAGE);  END SIMPLE_PROC; 3. OUT PUT 텝 열기 : 컨트롤 + 시프트 + O  4. PROCEDURE 호출 : 여기가 함정이다. SQLPLUS에서는 EXEC로 실행이 되지만,  DBMS 툴 에서는 실행이 되지 않는다. EXEC SIMPLE_PROC; SQL Error [900] [42000]: ORA-00900: SQL 문이 부적합합니다 4. DBMS 툴 PROCEDURE 호출 방법을 통해 PROCEDURE 실행  (세미콜론 주의) BEGIN SIMPLE_PROC; END; 5. 실행결과  6. PROCEDURE 조회 / 삭제 조회 SELECT * FROM ALL_SOURCE WHERE NAME = 'SIMPLE_PROC'; 삭제 DROP PROCEDURE SIMPLE_PROC;

이전글 : https://rockbottomdevbus.blogspot.com/2021/11/db-1-aquerytool-dummy-data-excel.html   4. 아래 사진 처럼 생성이된다. 하지만 pk값을 1번부터 시작하고싶다     To Be Continue. 다음글에서 계속.  5. CAR_NUMBER(pk)의 VALUE값을 1~111까지 들어가게하고싶은데 001부터 시작한다.  5-1.  알트를 누른상태에서 001부분부터 111까지 휠을사용하는동시에 드래그한다          5-2. 그상태에서 ; 를입력 >> 컨트롤 + H >> 찾을내용 : , 바꿀내용 : ,; 입력 >> 모두바꾸기     5-3. 맨마지막 열에 알트드래그로 ; 한줄을 더추가 >> 컨트롤 A로 모두선택 >> 복사    5-4. 엑셀을 열기 >> 1행 A열 첫번째컨을 선택 >> 붙여넣기  5-5. A열전체 선택 >> 데이터탭 선택 >> 텍스트 나누기 선택 >> 구분기호로 분리됨 선택 >> 다음선택  5-6. 세미콜론선택 >> 마침선택   5-7. E열선택 >> 우클릭 >> 삽입 5-8. E열 1행에 1입력 >> 컨트롤누른상태에서 빨간줄부분 클릭한상태로 111행까지 드래그 5-9. 컨트롤 A >> 복사  5-10. DBeaver 실행 >> 콘솔에 붙여넣기 >> SQL 스크립트 실행 (ALT + X ) 6. 실행하면 디비에 한방에 인서트가 되는 모습을 확인할 수 있다. 7. 총정리 : AQueryTool 에서 ERD만들고 테스트데이터생성 >> 노트패드++와 엑셀로 데이터 가공 ...

1. 준비할것 AQueryTool, Excel, Notepad++, DB, DBeaver, Create Table https://aquerytool.com/ https://notepad-plus-plus.org/downloads/   https://dbeaver.io/download/ https://www.oracle.com/database/technologies/xe-downloads.html https://www.microsoft.com/ko-kr/microsoft-365/try * 오피스가 없다면 마이크로소프트에서 1개월 무료체험이 가능하다 회원가입 후 사용 * DB를 설치해야한다면 아래링크 참고 https://rockbottomdevbus.blogspot.com/2021/10/db-xe-dbeaver-sqlplus-sql-xe11g-oracle.html 2. AQueryTool 페이지 접속 https://aquerytool.com/   2-1. 화면 우클릭 >> 테이블 추가 >> 데이터베이스 타입 오라클, ERD이름/데이터베이이름 입력 >> OK 2-2. 테이블/컬럼정보/데이터타입 입력 * 테스트데이터 생성 시 PK 컬럼과  VALUE가 입력이안되는 버그가있으니 2-3참고 2-3. 아래처럼 PK컬럼아래 진짜 PK값을 입력 위컬럼은 신경쓰지말자 3. 다운로드된 테스트 데이터 파일 우클릭 >> Notepadd++로 연다 4. 아래 사진 처럼 생성이된다. 하지만 pk값을 1번부터 시작하고싶다     T o Be Continue. 다음글에서 계속. 

- 전에 사용하던방법  오라클 xe 설치 >> sqlplus를 사용하여 유저생성 >> 오라클 Oracle SQL Developer >> 생성한유저로 접속 >> db사용  - 이번에 사용한 방법  오라클 xe 설치 >> dbeaver 설치 >> system 접속 >> 유저생성 >> user 접속 1. 오라클 설치  설치 시 system 비밀번호 설정하는 부분에서 해당 비밀번호를 기억하고있어야합니다. https://www.oracle.com/database/technologies/xe-downloads.html 2. DBeaver 설치 https://dbeaver.io/download/ 3. DBeaver 접속  - 새 데이터베이스 연결 >> 오라클 선택 >> 다음 - SID로 변경하고 Database 이름 입력 (대소문자 구분해야합니다. 모르겠으면 아래처럼)  >> Username : system >> Password : 오라클설치 시 입력한 비밀번호 4. 오라클 12c 부터는 공통계정 앞에 c##을 붙이도록 네이밍규칙이 바뀌었다고한다. 귀찮으니까 11g 이상이면 아래 쿼리 두줄을 실행하도록하자 alter session set "_ORACLE_SCRIPT"=true;  commit; 위의 쿼리를 실행하면 c##을 붙이지않아도 된다. https://coding-factory.tistory.com/413   5. USER 생성 create user [userName] identified by [password] ; -- 계정 생성 6. 권한부여 GRANT CREATE SESSION TO username;         -- 데이터베이스에 접근할 수 있는 권한 GRANT CREATE DATABASE LINK TO u...

*로그인된 유저의 권한 및 VIEW에 따라 조회결과가 달라질수있습니다 - 찾고 싶은 테이블 조회 시 사용 (테이블이름에 CAR 라는 단어가 들어간경우) (결과 : 테이블명) SELECT * FROM ALL_OBJECTS  WHERE OWNER = '계정' AND OBJECT_TYPE = 'TABLE'  AND OBJECT_NAME LIKE '%CAR%' ; (결과 : 테이블명,COMMENTS) SELECT * FROM USER_TAB_COMMENTS WHERE TABLE_NAME LIKE '%CAR%'; (결과 : 테이블명,컬럼) SELECT * FROM ALL_COL_COMMENTS WHERE TABLE_NAME LIKE '%CAR%' AND OWNER = '계정'; (결과 : 테이블명,컬럼,데이터타입) SELECT * FROM ALL_TAB_COLUMNS WHERE TABLE_NAME LIKE '%CAR%' AND OWNER = '계정'; - COMMENTS로 찾고싶은 테이블 조회 시 사용 (COMMENTS=자동차 정보,자동차 이름) (테이블 COMMENT로 조회하는방법) SELECT * FROM USER_TAB_COMMENTS WHERE COMMENTS IS NOT NULL AND TABLE_NAME LIKE '%CAR%'   AND COMMENTS LIKE '%자동차%'; (테이블 COMMENTS로 조회) SELECT T1.TABLE_NAME   AS 테이블명 , T2.COMMENTS     AS 테이블코멘트 , T1.COLUMN_NAME  AS 컬럼명 , T1.COMMENTS     AS 컬럼코멘트 FROM USER_COL_COMMENTS T1 LEFT JOIN USER_TAB_COMMENTS T2 ON (T2.TABLE_NAME = T1.TABLE_NAME) WHERE T1.COMMENTS LIKE '...

0. 점수테이블 생성  CREATE TABLE TB_SCORE (     COURSE_ID  VARCHAR2(10) NOT NULL,     STUDENT_ID VARCHAR2(10) NOT NULL,     SCORE      NUMBER(5, 2) NULL,     CONSTRAINT PK_SCORE PRIMARY KEY (COURSE_ID, STUDENT_ID) ); 1. 강의ID컬럼과 학생ID컬럼에 같은형식값이 들어가는 임시점수테이블 생성  CREATE TABLE TMP_SCORE (     COURSE_ID  VARCHAR2(10) NOT NULL,     STUDENT_ID VARCHAR2(10) NOT NULL,     SCORE      NUMBER(5, 2) NULL,     CONSTRAINT PK_SCORE PRIMARY KEY (COURSE_ID, STUDENT_ID) ); 2. 임시점수테이블에 있는것과 동일한 강의ID와 학생ID를 가진 데이터가 점수테이블에  있으면 점수를 UPDATE하고, 없으면 INSERT (임시테이블데이터를 점수테이블데이터에 옮기는 예) MERGE INTO TB_SCORE S     USING TMP_SCORE T        ON (S.COURSE_ID = T.COURSE_ID AND S.STUDENT_ID = T.STUDENT_ID)     WHEN MATCHED THEN         UPDATE SET S.SCORE = T.SCORE     WHEN NOT MATCHED THEN         INSERT ...