Rock Bottom Dev Bus

 + 오라클 조회 순번 매기는 방법 : 로우넘 사용법 (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...

*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;

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;

*로그인된 유저의 권한 및 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 ...

  SELECT * FROM a, b WHERE b.id(+)    = a.id    -- Oracle OUTER JOIN SELECT * FROM a LEFT OUTER JOIN b ON b.id = a.id  -- 동일한 표현 a.id = b.id (+) -- LEFT OUTER   A.id(+) = b.id    -- RIGHT OUTER LEFT OUTER조인과 2개의 조인 copyright by :  https://blog.edit.kr/entry/Oracle-쿼리중에-의-의미  [소금인형 - SW개발자?] copyright by  :   m.blog.naver.com/yysvip/220132590942