PL/SQL : FUNCTION, CURSOR

PL(Procedure Language)/SQL

PL : 프로시저 언어 = 함수를 만드는 언어

즉, 데이터베이스 응용 프로그램을 작성하는 데 사용하는 오라클의 전용 언어이다.

 

PL로 만들 수 있는 것 3가지 → Function, Procedure, Trigger

 

Function	리턴형이 있는 함수
Procedure	리턴형이 있거나 없을 수도 있는 함수 Call by Reference를 중심으로 짜인다.
Trigger	이벤트 발생 시에 처리한다. (ex) 입고/출고/재고 테이블이 각각 있다고 했을 때, 입고 및 출고에 따른 데이터 변경을 재고 테이블에도 자동처리도록 한다.

 

PL/SQL에는 아래의 요소들이 사용된다.

 

1) 변수

 - 일반 변수(지역 변수), 매개 변수

 - 스칼라 변수

 - %TYPE

 - %ROWTYPE

 - RECORD

 - CURSOR

2) 연산자

 - 산술, 비교, 논리, NULL, IN, BETWEEN, LIKE...

3) 제어문

 - 조건문 : IF, IF~ELSE, IF~ELSIF~ELSIF...

 - 반복문 : BASIC LOOP, FOR, WHILE

 

 

PL 기본 형식

크게 선언부와 실행부로 구성된다.

선언부에서는 변수와 매개변수를 선언하고, 실행부에서는 프로그램 로직을 작성한다.

*중괄호가 없음 → BEGIN과 END;가 중괄호({}) 역할을 한다고 생각하면 된다.

DECLARE
--변수(매개변수, 지역변수) 선언
BEGIN
--처리
--예외 처리
END;
/ --이것까지 꼭 쳐줘야됨

 

스칼라 변수

오라클의 일반 데이터형으로 주어진 컬럼을 스칼라 변수라고 생각하면 된다. (NUMBER, VARCHAR, DATE등)

스칼라 변수를 선언하면 단일행의 데이터만 불러온 후 그 값을 집어넣는 게 가능하다.

(한 번에 여러 행의 데이터를 집어넣을 수 없다.)

SET serveroutput ON; --콘솔창에 값을 띄울 수 있도록 함

DECLARE --변수선언
 vempno NUMBER(4);
 vename VARCHAR2(20);
 vjob VARCHAR2(15);
 vhiredate DATE;
 vsal NUMBER(7,2);
BEGIN --처리내용
 SELECT empno, ename, job, hiredate, sal INTO vempno,vename,vjob,vhiredate,vsal
 FROM emp WHERE empno=7788;
 DBMS_OUTPUT.PUT_LINE('*****결과******'); --sysout같은거임
 DBMS_OUTPUT.PUT_LINE('사번: '||vempno);
 DBMS_OUTPUT.PUT_LINE('이름: '||vename);
 DBMS_OUTPUT.PUT_LINE('직위: '||vjob);
 DBMS_OUTPUT.PUT_LINE('입사일: '||vhiredate);
 DBMS_OUTPUT.PUT_LINE('급여: '||vsal);
END;
/ --끝맺음

결과

테이블에 설정된 데이터형을 모를 때는...

컬럼명 뒤에 %TYPE 를 붙여 주면 해당 테이블의 그 컬럼명의 타입을 그대로 가져와 준다.

--%TYPE : 테이블에 존재하는 컬럼의 데이터형을 가지고 온다.
DECLARE 
 vempno emp.empno%TYPE;
 vename emp.ename%TYPE;
 vjob emp.job%TYPE;
 vhiredate emp.hiredate%TYPE;
 vsal emp.sal%TYPE;
BEGIN
 SELECT empno, ename, job, hiredate, sal INTO vempno,vename,vjob,vhiredate,vsal
 FROM emp WHERE empno=7788;
 DBMS_OUTPUT.PUT_LINE('*****결과******'); --sysout같은거임
 DBMS_OUTPUT.PUT_LINE('사번: '||vempno);
 DBMS_OUTPUT.PUT_LINE('이름: '||vename);
 DBMS_OUTPUT.PUT_LINE('직위: '||vjob);
 DBMS_OUTPUT.PUT_LINE('입사일: '||vhiredate);
 DBMS_OUTPUT.PUT_LINE('급여: '||vsal);
END;
/

 

하나의 컬럼의 타입뿐만 아니라, 그 테이블에서 하나의 행 안에 있는 모든 컬럼을 타입과 함께 가져오려면

테이블명 뒤에 %ROWTYPE을 붙여 준다.

DECLARE --변수가 선언되는 부분
 vemp emp%ROWTYPE; --emp가 갖고있는 모든 데이터를 다 넣어달라(클래스같은 느낌)
BEGIN --SQL문장으로 요청 처리하는 부분
 SELECT * INTO vemp FROM emp
 WHERE empno = 7788;
 DBMS_OUTPUT.PUT_LINE('*****결과******'); --sysout같은거임
 DBMS_OUTPUT.PUT_LINE('사번: '||vemp.empno);
 DBMS_OUTPUT.PUT_LINE('이름: '||vemp.ename);
 DBMS_OUTPUT.PUT_LINE('직위: '||vemp.job);
 DBMS_OUTPUT.PUT_LINE('입사일: '||vemp.hiredate);
 DBMS_OUTPUT.PUT_LINE('급여: '||vemp.sal);
END;
/

 

 

IF /  ELSIF / CASE문 (조건문)

 

각 조건문은 아래와 같이 사용한다.

자바에서의 IF문과 SQL의 CASE문과 조금씩 비슷한 형태를 가진다.

--IF문
IF(조건) THEN
처리내용
END IF;
--ELSIF문
IF(조건1) THEN
처리내용1
ELSIF(조건2) THEN
처리내용2
END IF;
--CASE문
CASE 
  WHEN 조건1 THEN 처리내용1;
  WHEN 조건2 THEN 처리내용2;
END CASE;

각 조건문이 끝났을 때는 반드시 맨 뒤에 END IF; / END CASE; 를 넣어 주어야 한다.

DECLARE
 vDeptno emp.deptno%TYPE:=0;
 vDname VARCHAR2(20);
 vEname emp.ename%TYPE;
BEGIN
  -- 구현
  SELECT deptno,ename INTO vDeptno,vEname
  FROM emp
  WHERE empno=7788;

  --일반 IF문
  IF(vDeptno=10) THEN
    vDname:='개발부';
  END IF;

  IF(vDeptno=20) THEN
    vDname:='영업부';
  END IF;

  IF(vDeptno=30) THEN
    vDname:='기획부';
  END IF;

  --ELSIF문
  IF(vDeptno=10) THEN
    vDname:='개발부';
  ELSIF(vDeptno=20) THEN
    vDname:='영업부';
  ELSIF(vDeptno=30) THEN
    vDname:='기획부';
  END IF;

  --CASE문
  CASE 
   WHEN vDeptno=10 THEN vDname='개발부';
   WHEN vDeptno=20 THEN vDname='영업부';
   WHEN vDeptno=30 THEN vDname='기획부';
  END CASE;

 --결과값 출력
 DBMS_OUTPUT.PUT_LINE('*****결과값*******');
 DBMS_OUTPUT.PUT_LINE('이름:'||vEname);
 DBMS_OUTPUT.PUT_LINE('부서:'||vDname);

END;
/

DECLARE
 vComm emp.comm%TYPE;
 vEname emp.ename%TYPE;
 vSal emp.sal%TYPE;
BEGIN
 SELECT ename,sal,comm INTO vEname,vSal,vComm
 FROM emp
 WHERE empno=&sabun;

 IF vComm>0 THEN
	DBMS_OUTPUT.PUT_LINE(vEname||'님의 급여는 '||vSal||'이고 성과급은 '||vComm||'입니다.');
 ELSE
 	DBMS_OUTPUT.PUT_LINE(vEname||'님의 급여는 '||vSal||'이고 성과급은 없습니다.');
 END IF;

END;
/

 

 

LOOP (반복문)

 

반복문은 아래와 같은 형태를 가진다.

IF문과 마찬가지로 반복문이 끝난 부분에는 END LOOP; 를 반드시 써 주어야 한다.

LOOP
 --처리문장
 --처리문장
 EXIT [조건]
END LOOP;

일반 LOOP

*값을 대입할 때는 =가 아니라 :=를 써 주어야 한다.

= 는 대입연산자가 아니라 비교연산자로서 작용한다!

DECLARE
 no NUMBER:=1;
 eno NUMBER:=&eno;
BEGIN
 LOOP
  DBMS_OUTPUT.PUT_LINE(no);
  no:=no+1;
  EXIT WHEN no>eno;
 END LOOP;
END;
/

no가 1부터 시작해서 하나씩 출력 후 no에 1을 더하고,

no의 값이 eno보다 커지면 루프를 빠져나간다.

 

WHILE LOOP

WHILE LOOP의 경우 WHILE 뒤에 EXIT 조건이 들어간다.

DECLARE
 no NUMBER:=1;
BEGIN
 WHILE no<=10 LOOP
  DBMS_OUTPUT.PUT_LINE(no);
  no:=no+1;
 END LOOP;
END;
/

FOR LOOP

EXIT 조건이 별도로 존재하지 않고, 몇부터 몇까지 반복할 것인지 지정한다.

DECLARE
BEGIN
 FOR i IN 1..10 LOOP
  DBMS_OUTPUT.PUT_LINE(i);
 END LOOP;
END;
/

FOR i IN REVERSE 1..10 LOOP 해주면 1부터 10이 아니라 10부터 1까지 수행한다.

단, 1..10을 10..1로 입력하는 것은 안된다! 범위는 반드시 작은 수..큰 수 형태로 작성해야 한다.

DECLARE
BEGIN
 FOR i IN REVERSE 1..10 LOOP
  IF MOD(i,2)=0 THEN 
   DBMS_OUTPUT.PUT_LINE(i);
  END IF;
 END LOOP;
END;
/

DECLARE
 vsum NUMBER:=0;
 veven NUMBER:=0;
 vodd NUMBER:=0;
BEGIN
 FOR i IN 1..100 LOOP
  vsum:=vsum+i;
  IF(MOD(i,2)=0) THEN
   veven:=veven+i;
  ELSE
   vodd:=vodd+i;
  END IF;
 END LOOP;

 DBMS_OUTPUT.PUT_LINE('1~100까지 총합: '||vsum);
 DBMS_OUTPUT.PUT_LINE('1~100까지 짝수합: '||veven);
 DBMS_OUTPUT.PUT_LINE('1~100까지 홀수합: '||vodd);
END;
/

DECLARE
 dan NUMBER:=&dan;
 result VARCHAR2(100):='';
BEGIN
 FOR i IN 1..9 LOOP
 result:=dan||'*'||i||'='||(dan*i);
 DBMS_OUTPUT.PUT_LINE(result);
 END LOOP;
END;
/

 

<자주 나오는 에러>

심볼 "?"를 만났습니다 다음 중...

이 같은 에러는 일반적으로 콤마(,), 세미콜론(;) 등이 적절한 위치에 없거나, 부적절한 위치에 있을 때 발생한다.

위 코드에서는 vsum이 아니라 sum 이 들어갈 경우,

SQL Plus가 sum을 변수명이 아닌 함수명으로 인식하여 오류가 발생할 수 있다.

 

 

CURSOR

 

SQL문의 실행 결과가 다중 행, 또는 다중 열일 경우에 프로그램에서는 한 행씩 처리하게 된다.

이 때 커서가 결과를 한 번에 한 행씩 처리할 수 있도록 순서대로 가리켜(👈)준다.

Java에서 List나 배열을 사용하듯이 한 번에 여러개의 정보를 집어넣을 수 있도록 하기도 한다.

 

형식은 아래와 같다.

DECLARE
 vemp emp%ROWTYPE;
 --CURSOR 선언
 CURSOR cur IS
	SELECT * FROM emp;
BEGIN
 FOR vemp IN cur LOOP
  DBMS_OUTPUT.PUT_LINE(vemp.empno||' '||vemp.ename||' '||vemp.job);
 END LOOP;
END;
/

DECLARE
 TYPE eds IS RECORD(
  ename emp.ename%TYPE,
  job emp.job%TYPE,
  sal emp.sal%TYPE,
  dname dept.dname%TYPE,
  loc dept.loc%TYPE,
  grade salgrade.grade%TYPE
 );
 e eds; --RECORD 선언
 CURSOR cur IS
  SELECT ename, job, sal, dname, loc, grade
  FROM emp, dept, salgrade
  WHERE emp.deptno = dept.deptno
  AND sal BETWEEN losal AND hisal;
BEGIN
 FOR e IN cur LOOP
  DBMS_OUTPUT.PUT_LINE(e.ename||' '||e.job||' '||e.dname||' '||e.loc||' '||e.grade);
 END LOOP;
END;
/

 

 

FUNCTION

 

복잡한 SQL문 처리를 사용자 정의 함수로 선언하여 사용할 수 있다.

CREATE만 사용해도 되고, 뒤에 OR REPLACE를 써주면 이미 있는 함수의 내용을 대체할 수 있다.

CREATE [OR REPLACE] FUNCTION fun_name(매개변수)
RETURN 데이터형
IS
--선언부
BEGIN
--구현부
END;
/

매개변수는 대체로 실제 테이블에 있는 데이터형을 사용하기 위해 %TYPE을 써 준다.

리턴은 한 개만 가능하고,

이렇게 만든 함수는 SELECT, WHERE, GROUP BY, ORDER BY 등에 활용할 수 있다.

CREATE OR REPLACE FUNCTION std_sum(vsno student.sno%TYPE)
RETURN NUMBER
IS
 vsum NUMBER:=0;
BEGIN
 SELECT kor+eng+math INTO vsum
 FROM student
 WHERE sno = vsno;
 RETURN vsum;
END;
/

CREATE OR REPLACE FUNCTION std_avg(vsno student.sno%TYPE)
RETURN NUMBER
IS
 vavg NUMBER:=0;
BEGIN
 SELECT ROUND((kor+eng+math)/3.0,2) INTO vavg
 FROM student
 WHERE sno = vsno;
 RETURN vavg;
END;
/

SELECT name, kor, eng, math, std_sum(sno), std_avg(sno) FROM student;

CREATE OR REPLACE FUNCTION dept_dname(vdeptno emp.deptno%TYPE)
RETURN VARCHAR2
IS
 vdname dept.dname%TYPE;
BEGIN
 SELECT dname INTO vdname
 FROM dept
 WHERE deptno=vdeptno;
 RETURN vdname;
END;
/

CREATE OR REPLACE FUNCTION dept_loc(vdeptno emp.deptno%TYPE)
RETURN VARCHAR2
IS
 vloc dept.loc%TYPE;
BEGIN
 SELECT loc INTO vloc
 FROM dept
 WHERE deptno=vdeptno;
 RETURN vloc;
END;
/

DROP FUNCTION function_name; 하면 삭제 가능