[ PL/SQL ]
- 구조적인 SQL언어와 달리 SQL을 절차적인 방식에 의해 사용하도록 설계한 언어
1) PL/SQL의 구조
DECLARE -- 선언(변수선언,커서,사용자정의 예외,..)
BEGIN -- 실행(SELECT,DML,..)
EXCEPTION -- 예외처리
END;
/
예1)
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 I_MSG VARCHAR2(100); -- 변수선언
3 BEGIN
4 I_MSG:='HELLO'; -- 값대입
5 DBMS_OUTPUT.PUT_LINE('I_MSG:' || I_MSG); -- 화면출력
6 END;
7 /
[결과]
I_MSG:HELLO
SQL> DECLARE
2 VEMPNO NUMBER(4);
3 VENAME VARCHAR2(20);
4 VDEPTNO NUMBER(4);
5 VSAL NUMBER(4);
6 BONUS NUMBER(10);
7 BEGIN
8 SELECT EMPNO,ENAME,DEPTNO,SAL INTO VEMPNO,VENAME,VDEPTNO,VSAL
9 FROM EMP
10 WHERE EMPNO=7369;
11 IF(VDEPTNO=10) THEN
12 BONUS:=VSAL*0.1;
13 ELSIF(VDEPTNO=20) THEN
14 BONUS:=VSAL*0.2;
15 ELSIF(VDEPTNO=30) THEN
16 BONUS:=VSAL*0.3;
17 END IF;
18 DBMS_OUTPUT.PUT_LINE('사원번호 급여 부서번호 보너스');
19 DBMS_OUTPUT.PUT_LINE(VEMPNO ||' '||VSAL||' '||VDEPTNO||' '||BONUS);
20 END;
21 /
사원번호 급여 부서번호 보너스
7369 800 20 160
[ 저장 프로시져(Stored Procedure) ]
- 특정한 처리를 수행하는 PL/SQL 서브프로그램. 파라미터를 받아서 특정 작업을 수행한다.
- 형식)
CREATE OR REPLACE PROCEDURE 프로시져명(파라미터,..)
IS
변수선언;
..
BEGIN
실행문;
..
END;
/
예)-- 연습용 테이블
CREATE TABLE MEM
(
NUM NUMBER PRIMARY KEY,
NAME VARCHAR2(20),
PHONE VARCHAR2(15),
ADDR VARCHAR2(20)
);
예1) 프로시져만들기
CREATE OR REPLACE PROCEDURE ADDMEM
(
NUM NUMBER, -- 파라미터
NAME VARCHAR2,
PHONE VARCHAR2,
ADDR VARCHAR2
)
IS
BEGIN
INSERT INTO MEM
VALUES(NUM,NAME,PHONE,ADDR);
END;
/
-- 프로시져 오류보기
SQL>SHOW ERROR
-- 프로시져 수행
SQL> EXECUTE ADDMEM(1,'홍','010','대구');
예2)
CREATE OR REPLACE PROCEDURE ADDMEM
(
NUM MEM.NUM%TYPE,
NAME MEM.NAME%TYPE,
PHONE MEM.PHONE%TYPE:='010',-- 파라미터값이없으면 디폴트값으로 채워짐
ADDR MEM.ADDR%TYPE:='서울'
)
IS
BEGIN
INSERT INTO MEM
VALUES(NUM,NAME,PHONE,ADDR);
COMMIT;
END;
/
SQL> EXECUTE ADDMEM(2,'김아무');
-- 예제테이블
CREATE TABLE HAKSA
(
HAKBUN NUMBER(10) PRIMARY KEY,
KOR NUMBER(3),
ENG NUMBER(3),
TOT NUMBER(3),
HAKJUM CHAR(2)
);
SQL> INSERT INTO HAKSA(HAKBUN,KOR,ENG) VALUES(1,100,90);
SQL> INSERT INTO HAKSA(HAKBUN,KOR,ENG) VALUES(2,80,70);
-- 학번을 파라미터로 받아서 총점과 학점을 계산해주는 저장 프로시져
CREATE OR REPLACE PROCEDURE HAKSA_PRO(NUM HAKSA.HAKBUN%TYPE)
IS
HAK HAKSA.HAKJUM%TYPE;
SUM1 HAKSA.TOT%TYPE;
AVG1 NUMBER;
BEGIN
SELECT (KOR+ENG) INTO SUM1
FROM HAKSA
WHERE HAKBUN=NUM;
AVG1:=SUM1/2; --평균구하기
-- IF절 이용해서 학점구하기
IF(AVG1>=90) THEN
HAK:='A';
ELSIF(AVG1>=80) THEN
HAK:='B';
ELSIF(AVG1>=70) THEN
HAK:='C';
ELSE
HAK:='F';
END IF;
UPDATE HAKSA
SET TOT=SUM1
, HAKJUM=HAK -- 계산된 데이터로 수정하기
WHERE HAKBUN=NUM;
COMMIT;
END;
/
-- 프로시져 실행하기
SQL> EXECUTE HAKSA_PRO(1);
[ 사용자 정의 함수(Stored Function) ]
- 사용자가 만든 함수이며 어떤 연산을 수행한뒤 결과값을 반환한다.
형식)
CREATE OR REPLACE FUNCTION 함수명(파라미터,..)
RETURN 리턴형
IS
변수선언;
..
BEGIN
실행문장;
..
RETURN 값;
END;
/
예) 입력받은 값으로부터 10%의 세율을 구하는 함수
CREATE OR REPLACE FUNCTION TAX(P_VALUE NUMBER)
RETURN NUMBER
IS
VAL NUMBER;
BEGIN
VAL:=P_VALUE*0.1; -- 세율구하기
RETURN VAL; --세율반환하기
END;
/
SQL> SELECT TAX(3000) FROM DUAL;
TAX(3000)
----------
300
## IF문 형식
IF 조건절 THEN
실행문;
ELSE
실행문;
END IF;
Q1) 사원의 급여를 파라미터로 받아서 5000이상이며 15%에 해당하는 금액을 리턴하고 5000미만이면 10%를 리턴하는 함수 작성
CREATE FUNCTION MYTAX(P_VAL NUMBER) RETURN NUMBER
IS
V_RESULT NUMBER;
BEGIN
IF(P_VAL>=5000) THEN
V_RESULT:=P_VAL*0.15;
ELSE
V_RESULT:=P_VAL*0.1;
END IF;
RETURN V_RESULT;
END;
/
-- FUNCTION호출
SQL> SELECT MYTAX(6000) FROM DUAL;
MYTAX(6000)
-----------
900
Q2) 파라미터로 사원번호를 받아서 근무하는 부서명을 반환하는 함수작성
CREATE OR REPLACE FUNCTION EMPDNAME(NUM EMP.EMPNO%TYPE)
RETURN VARCHAR2 -- 반환타입
IS
V_RESULT VARCHAR2(20); --부서명저장할 변수
BEGIN
SELECT D.DNAME
INTO V_RESULT
FROM EMP E,
DEPT D
WHERE E.DEPTNO=D.DEPTNO
AND E.EMPNO=NUM;
RETURN V_RESULT;
END;
/
-- FUNCTION호출해보기
SQL> SELECT EMPDNAME(7369) FROM DUAL;
