DB관련/oracle (18) 썸네일형 리스트형 PIVOT - 행을 열로 바꾸기 통계페이지와 같은 집계가 필요한 경우 종종 로우를 칼럼으로 바꿔서 출력해야될 경우가 있다. 로우를 칼럼으로 바꾸는 방법을 3개 정도 알아보고자 한다. 1.DECODE 혹은 CASE 문을 사용하는 방법 2.WITH 절을 이용한 방법 3.PIVOT 함수를 이용하는 방법 이 글에서 사용하는 테스트 데이터는 다음과 같다. create table table_score( years varchar(100) ,game_name varchar(100) ,play_level varchar(100) ,rate int ); insert into table_score values ('2023','ez2on','lv14',92); insert into table_score values ('2023','ez2on','lv15',8.. CONNECT BY 사용하기 CONNECT BY 란 계층형 쿼리를 조회하기 위한 쿼리이다. 다음과 같이 회사 조직도가 있다고 해보자. 조직에는 각 계층이 있다. 각 계층을 A레벨, B레벨 ,C 레벨이라 하겠다. 각 레벨들의 정보들을 기록한 테이블이 다음과 같다고 해보자. 각 row 에는 상위 부서 번호와 해당 부서의 번호가 있고, 부서번호가 만약에 시퀸스값으로 insert 가 된다고 할 때 만약에 조회를 할 때 A레벨부터 B레벨, C레벨 순으로 나오게끔 하고 싶다면 어떻게 해야될까? 단순히 order by 를 하면 레벨별로 나온다고 무조건 장담할 수 없다. 이럴때 사용하는게 계층형 쿼리이다. 1. 계층형 쿼리 오라클에서 계층형 쿼리의 예약어는 START WITH 이다. START WITH - 계층의 루트노드(처음행)로 사용될 행을 .. 서브 프로그램 4 - 트리거 (TRIGGER) 트리거 콜백 메소드라 생각하면 된다. 사용자가 지정해서 제어할 수 없다. 특정 이벤트가 일어날때 자동적으로 실행된다. 너무 많으면 퍼포먼스 저하 우려가 있다. 트리거에 관한 기능을 수행할때 생성 CREATE TRIGGER 수정 ALTER TRIGGER 삭제 DROP TRIGGER 의 권한이 다 필요한다. 트리거의 몸체에는 TCL 사용은 불가능하다. B>트리거의 구조 실행 시점 실행시키는 이벤트 트리거의 BODY 로 구성되어 있다. C> 종류 DML과 관련된 트리거 시스템과 관련된 트리거가 있다. D>사용 E>예시 서브 프로그램 3 - 패키지 (ORACLE PACKAGE) A>패키지 1.패키지란 연관성이 높은 함수나 프로시저를 묶어놓은 그룹 PL/SQL 이나 복합 유형의 데이터 함수나 프로시져 등이 패키지라는 그룹으로 묶여있다. 2.구성 선언부와 몸체부로 구성된다. 2-1 . 선언부 함수나 프로시져, 변수 등의 정의 선언 부분 B.사용 문형 선언부 CREATE PACKAGE 패키지 이름 IS 함수, 프로시저 ,변수 등의 선언부 END 패키지 이름; 몸체부 CREATE PACKAGE BODY 패키지 이름 IS 실제 실행문 구현부 END 패키지 이름 C> 삭제 둘다 할수도 있고 둘 중 하나만 할 수도 있다. DROP PACKAGE package_name ; DROP PACKAGE BODY package_name ; D> 예시 1) 예시1 --패키지 선언부 생성 CREATE O.. 서브 프로그램 - 내장함수(FUNCTION) A>선언 CREATE FUNCTION 이름 [ 파라미터 ] RETURN 데이터 타입 IS PL/SQL블록 B>수정 DROP FUNCTION 으로 삭제 ALTER FUNCTION 으로 수정 C>예시 1.예시 CREATE OR REPLACE FUNCTION s_max_sal return number IS max_sal emp.sal%TYPE; BEGIN SELECT max(sal) INTO max_sal (s_deptno emp.deptno%TYPE) FROM emp WHERE deptno = s_deptno; RETURN max_sal; --3번째 줄의 리턴타입과 같아야한다. END; / --결과 출력 SELECT s_max_sal(10) FROM dual; 2)예시2 CREATE OR REPLACE FU.. 서브 프로그램 - 프로시져(PROCEDURE) PL/SQL은 재사용을 못한다. 서브 프로그램은 된다. 종류는 프로시저, 함수, 패키지 ,트리거 등이다. A>서브 프로그램은 1. 이름이 지정된 PL/SQL 블록이다. 2. 최초 실행될때 한번만 컴파일 된다. 3. 데이터 베이스에 저장이 된다. 4. 다른 응용프로그램에서 호출이 가능하다. 5. 함수일 경우 리턴값이 존재할 수 있다. 6. 파라미터를 사용가능하다. B>프로시져 1.생성 문법 CREATE[OR REPLACE] PROCEDURE 프로시져 이름 IS|AS PL/SQL블록 2.파라미터 IN, OUT, INOUT 모드로 나뉘어 진다. 선언시 데이터 타입은 명시하되 크기는 명시하지 않는다. 선언부의 파라미터 = 형식 파라미터 실행시 파라미터 = 실행 파라미터 선언시 default 사용해 기본값 부여 .. 예외와 예외처리 A>예외 오라클 예외와 사용자 정의 예외가 있다. 1.오라클 예외 오라클이 정의한 상황에서 자동 발생 예외명이 정해진것과 없는것이 있다. 2. 사용자 정의 예외 사용자가 예외처리를 정의 예외처리부에서 RAISE 문을 통해 명시적으로 예외를 발생시킨다. B>종류 ACCESS_INTO_NULL CASE_NOT_FOUND COLLECTION_IS_NULL CURSOR_ALREADY_OPEN :이미 커서가 열려있음 JUP_VAL_ON_INDEX: 인덱스 중복 INVALID_NUBER:문자 to 숫자 실패 등 C> 사용 주의점 : 예외 종류가 중복되면 안된다. 문형1. DECLARE BEGIN 실행문 실행문 EXCETPION WHEN 오류 THEN 실행 내용 END; 문형2. 사용자 정의 오류 DECLARE 예외.. PL/SQL -4 CURSOR A> CURSOR SQL에서 커서란 SQL문을 실행, 처리를 위한 메모리공간에서 특정한 레코드에 위치한 포인터와 비슷한 개념 커서의 위치를 변경해서 특정 실행 결과를 가지고 온다. 커서의 서브쿼리는 select문만 올 수가 있다. B> 종류 묵시적/ 명시적 1.묵시적 커서 오라클 서버가 만듦 SQL% 이 있으면 묵시적 커서 하나만 선언 가능 1-1.문형 SQL%속성 1-2. 종류 1)SQL%ROWCOUNT 해당 커서에서 실행한 총 행의 개수 2)SQL%FOUND 커서 안에 데이터가 있느냐 없느냐 TURE, FALSE 리턴 3)SQL%ISOPEN 메모리에 OPEN되어 있으면 true 아니면 false 4)예시 DECLARE v_deptno number := &dno; BEGIN delete emp wher.. 이전 1 2 3 다음
