Oracle 9일차(2)-PL/SQL

3.PL/SQL

▶PL/SQL(Procedural Language/SQL)은 최근의 프로그래밍 언어의 특성을 수용한, SQL의 확장이라 할 수 있다. SQL의 데이터 조작(DML)과 질의문(QUERY)을 블록 구조에 절차적 단위(IF, LOOP, FOR등)로 된 코드를 포함할 수 있으며 절차적 프로그래밍을 가능하게 한 강력한 TRANSACTION 처리 언어이다

▶선언문(DECLARE), BEGIN, EXCEPTION, END로 구성

DECLARE

variables, cursor, user_defined, exception

BEGIN

SQL,PL/SQL statements;

EXCEPTION

actions to perform when errors occur

END;

  ○BEGIN : 쿼리문, DML,TCL 사용 가능( SELECT, INSERT, DELETE, UPDATE, COMMIT, ROLLBACK)

▶Anonymous Block(익명 블록) : 일반적으로 관리자가 사용

▶Subprogram(procedure, function) : 개발자가 사용

▲사원 테이블

행제한

▶무명의 프로시져 생성(1)

DECLARE

TYPE FIRSTTYPE IS RECORD

(A 사원.사원명%TYPE, B 사원.직급%TYPE, C 사원.급여%TYPE);

CUS FIRSTTYPE;

BEGIN

SELECT 사원명,직급,급여 INTO CUS FROM 사원 → SELECT된 결과를 CUS에 넣음

WHERE 사원번호=2001;

DBMS_OUTPUT.PUT_LINE('사원명  직급 급여');

DBMS_OUTPUT.PUT_LINE('--------------------');

DBMS_OUTPUT.PUT_LINE(CUS.A||'  '||CUS.B||'  '||TO_CHAR(CUS.C));

DBMS_OUTPUT.PUT_LINE('현재 질의한 사용자는 '||USER||' 입니다.');

DBMS_OUTPUT.PUT_LINE('질의한 시간은 '||TO_CHAR(SYSDATE,'YYYY-MM-DD HH:MI:SS'));

END;

○ DMBS_OUTPUT.PUT_LINE은 실제로 작성X → 결과를 보기위해 작성

○ CMD창에서는 SET SERVEROUTPUT ON; 실행해야 결과가 보임

▶무명의 프로시져 생성(2)

DECLARE

SAWON_REC 사원%ROWTYPE;             → 사원 테이블 행의 모든 타입을 읽어옴

BEGIN

SELECT * INTO SAWON_REC FROM 사원

WHERE 사원번호=2001;

DBMS_OUTPUT.PUT_LINE('사원번호:'||SAWON_REC.사원번호);

DBMS_OUTPUT.PUT_LINE('사원명:'||SAWON_REC.사원명);

DBMS_OUTPUT.PUT_LINE('직급:'||SAWON_REC.직급);

DBMS_OUTPUT.PUT_LINE('급여:'||SAWON_REC.급여);

END;

▶CMD

○SQL파일로 저장한 뒤 실행

  이름.SQL / @이름.SQL

열제한

▶무명의 프로시져 생성(1)

FOR 문

FOR 변수 IN (SELECT 사원명,직급 FROM 사원) LOOP

실행문1

...

실행문M

END LOOP;

FOR 변수 IN 1..MAX LOOP         →1부터 시작해서 MAX까지

실행문1

...

실행문M

END LOOP;

사원명과 직급을 출력

DECLARE

TYPE SNAME_TABLE_TYPE IS TABLE OF 사원.사원명%TYPE

INDEX BY BINARY_INTEGER;

TYPE JIK_TABLE_TYPE IS TABLE OF 사원.직급%TYPE

INDEX BY BINARY_INTEGER;

SNAME_COL SNAME_TABLE_TYPE; →SNAME_TABLE_TYPE을 저장할 변수 SNAME_COL

JIK_COL JIK_TABLE_TYPE;         →JIK_TABLE_TYPE을 저장할 변수 JIK_COL

I BINARY_INTEGER := 0;

BEGIN

FOR K IN (SELECT 사원명,직급 FROM 사원) LOOP

I := I+1;

SNAME_COL(I) := K.사원명;

JIK_COL(I) := K.직급;

END LOOP;

DBMS_OUTPUT.PUT_LINE('사원명    직급');

DBMS_OUTPUT.PUT_LINE('--------------');

FOR J IN 1..I LOOP

DBMS_OUTPUT.PUT_LINE(RPAD(SNAME_COL(J),12) || RPAD(JIK_COL(J),9));

END LOOP;

END;

저장 PROCEDURE

○ 속도가 빠르다

   처음 :  번역 → 컴파일 → 저장 → 실행

   다음부터 : EXECUTE → 저장된 거 실행

이름을 호출하면서 사용하는 매개변수

IS

내부적(BEGIN)으로 사용할 매개변수 이름

CREATE OR REPLACE PROCEDURE CHANGE_PAY

(V_SANO IN NUMBER, V_NEW_PAY NUMBER) → 변수이름 변수타입 / IN은 생략가능

IS

BEGIN

UPDATE 사원 SET 급여=V_NEW_PAY WHERE 사원번호=V_SANO;

COMMIT;

END CHANGE_PAY; → 정석, END; 도 가능

▶저장 PROCEDURE 실행

SELECT * FROM 사원 WHERE 사원번호='2005';

EXECUTE CHANGE_PAY(2005,4000);

함수

CREATE OR REPLACE FUNCTION F_TAX

(V_SANO IN NUMBER)

RETURN NUMBER → 함수는 RETURN값이 있어야 함 ;필요X

IS

V_TAX NUMBER;

BEGIN

SELECT ((급여*12) +NVL(커미션,0))*0.05 INTO V_TAX

FROM 사원 WHERE 사원번호=V_SANO;

RETURN V_TAX;

END;

▶T_FAX함수 사용

○ T_FAX(사원번호) → 사원번호 = V_SANO

SELECT 사원번호,사원명,직급,급여,커미션,F_TAX(2001) 세금 FROM 사원

WHERE 사원번호=2001;

SELECT F_TAX(2001) 세금 FROM 사원

WHERE 사원번호=2001;

▶SELECT사용하지 않고 오로지 함수만 사용할 때

IN / OUT

○ IN : 내부에서 받을 매개변수

○ OUT : 외부로 내보낼 변환값

CREATE OR REPLACE PROCEDURE P_OUTEX

(V_SANO IN 사원.사원번호%TYPE,

V_SANAME OUT 사원.사원명%TYPE,

V_PAY OUT 사원.급여%TYPE,

V_COMM OUT 사원.커미션%TYPE)

IS

BEGIN

SELECT 사원명,급여,커미션 INTO V_SANAME,V_PAY,V_COMM

FROM 사원 WHERE 사원번호=V_SANO;

END;

▶CMD 실행

번호사이에 - 넣기

CREATE OR REPLACE PROCEDURE ADD_ONE

(V_TEL IN OUT VARCHAR2)

IS

BEGIN

V_TEL := SUBSTR(V_TEL,1,3)|| '-' ||SUBSTR(V_TEL,4);

END;

▶CMD 실행

주민번호로 생일 출력

CREATE OR REPLACE PROCEDURE P_JUMIN

(V_JUMIN IN OUT VARCHAR2)

IS

BEGIN

V_JUMIN := SUBSTR(V_JUMIN,1,2) || '년' ||SUBSTR(V_JUMIN,3,2) || '월' ||SUBSTR(V_JUMIN,5,2) || '일';

END;

▶CMD 실행

SCRIPT로 만들어 실행

SET SERVEROUTPUT ON → 항상 있어야 결과물 확인 가능

SET VERIFY OFF         → 변수의 값이 바뀐걸 보여주지 않음

ACCEPT ID PROMPT '검색할 아이디를 입력하세요:';

DECLARE

TYPE GOGAK IS RECORD

(A CUSTOM.USERID%TYPE,

B CUSTOM.USERNAME%TYPE,

C NUMBER(12,2),

D NUMBER(5));

CUS GOGAK; → 변수선언 ▶ 4개의 값을 저장할 수 있는 변수

BEGIN

SELECT A.USERID,USERNAME,HAP,NCOUNT INTO CUS

FROM CUSTOM A,(SELECT USERID, SUM(PRICE) HAP, COUNT(*) NCOUNT FROM SALES GROUP BY USERID) B

WHERE A.USERID=B.USERID AND A.USERID='&ID';

DBMS_OUTPUT.PUT_LINE('사용자 아이디: ' || CUS.A);

DBMS_OUTPUT.PUT_LINE('사용자 이름: ' || CUS.B);

DBMS_OUTPUT.PUT_LINE('전체 판매액: ' || CUS.C);

DBMS_OUTPUT.PUT_LINE('판매횟수: ' || CUS.D);

END;

/

▶SQL파일로 저장

▶CMD 실행