PL/SQL 저장 프로시저

SQL	관계형 데이터베이스에 저장된 데이터에 Access하기 위하여 사용하는 표준언어
PL/SQL	SQL문을 사용하여 프로그램을 작성할 수 있도록 확장해 놓은 오라클의 프로그래밍 언어
SQL*Plus	SQL 및 PL/SQL 문장을 실행할 수 있는 환경을 제공하는 오라클의 툴

변수의 종류

--일반 변수
변수명 타입(크기);
v_name VARCHAR2(15);
--상수
변수명 CONSTANT 타입(크기)
v_name CONSTANT varchar2(15) := '김말이';
--해당 테이블 컬럼의 데이터 타입을 가져옴
변수명 테이블이름.컬럼명%TYPE
--%TYPE와 다르게 하나 이상의 값에 적용 
변수명 테이블이름%ROWTYPE

 

!프로시저 사용 전 설정!

기본적으로 PL/SQL은 결과물을 보여주지 않기때문에 미리 SERVEROUTPUT을 ON으로 설정해주어야 한다.

SET SERVEROUTPUT ON;

 

 

프로시저 생성

CREATE [OR REPLACE] PROCEDURE 저장_프로시저_이름
   ( 매개변수 [mode] 테이블이름.열이름%TYPE,
     매개변수 [mode] 데이터타입. . .
   )	--매개변수는 주로 테이블의 열의 값을 받기때문에 그와 동일한 형식을 지정하는 것이 좋다.
IS [ or AS]
   변수 선언
BEGIN
   PL/SQL 코드 작성
END;
/

create or replace procedure sp_salary
is
  vename emp.ename%type;
  vsal     emp.sal%type;
begin
  select ename, sal into vename, vsal
  from emp
  where ename = 'SCOTT';
  DBMS_OUTPUT.PUT_LINE(vename||' 의 급여는 '||vsal);
end;
/

+

ed proc01  --proc01.sql 파일 생성
@proc01    --파일의 pl/sql코드 실행

 

프로시저 실행

execute 저장_프로시저_이름;

 

프로시저 조회

DESC USER_SOURCE

SELECT name, text FROM user_source WHERE name LIKE('%SP_SALARY%');

 

매개변수(파라미터)가 담긴 저장 프로시저 생성

CREATE OR REPLACE PROCEDURE DEL_ENAME
  (VENAME EMP01.ENAME%TYPE)
IS
BEGIN
  DELETE FROM EMP01 WHERE ENAME=VENAME;
END;
/

매개변수가 담긴 저장 프로시저 실행(매개변수 넣어줌)

EXECUTE DEL_ENAME('SMITH');

매개변수 선언 시 IN, OUT, INOUT(mode)

사원 번호로 특정 고객을 조회할 것이기 때문에 사원 번호를 IN으로 지정

조회해서 얻은 고객의 정보 중에서 고객의 이름과 급여와 담당 업무를 얻어오기 위해서 이름과 급여와 담당 업무컬럼을 OUT으로 지정

--생성
CREATE OR REPLACE PROCEDURE SEL_EMPNO
( VEMPNO IN EMP.EMPNO%TYPE,		--IN := 전달될 데이터(디폴트)
  VENAME OUT EMP.ENAME%TYPE,		--OUT := 결과로 나갈 데이터
  VSAL OUT EMP.SAL%TYPE,
  VJOB OUT EMP.JOB%TYPE
)
IS

BEGIN
 SELECT ENAME, SAL, JOB INTO VENAME, VSAL, VJOB
 FROM EMP
 WHERE EMPNO=VEMPNO;
END;
/
--실행
EXECUTE SEL_EMPNO(7788, :VAR_ENAME, :VAR_SAL, :VAR_JOB)
  --OUT 매개변수 호출시 변수 앞에 ‘:’를 붙여주어야 한다.(변수는 이미 선언된 상태라고 가정)

 

 

저장함수

함수의 실행 결과를 되돌려 받을 수 있음

CREATE [OR REPLACE ] FUNCTION 함수명 
  ( argument1 [mode] data_taye, 
    argument2 [mode] data_taye . . .
  )
  RETURN 리턴타입;
IS 
  변수 선언;
BEGIN
  pl/sql문;
  RETURN 리턴값;
END;
/

--생성
CREATE OR REPLACE FUNCTION CAL_BONUS(
      VEMPNO IN EMP.EMPNO%TYPE )
  RETURN NUMBER
IS
  VSAL NUMBER(7, 2);	--소수점 둘째자리 까지 나타내기
BEGIN
  SELECT SAL INTO VSAL	--sal의 값을 vsal에 저장( into문 := 간단하게 단일 행을 변수로 가져옴)
  FROM EMP
  WHERE EMPNO = VEMPNO;

  RETURN (VSAL * 2); 	
END;
/
--호출
VARIABLE VAR_RES NUMBER; --변수 만들어줌(이렇게 선언한 변수를 사용할 때는 앞에 : 붙여줌
EXECUTE :VAR_RES := CAL_BONUS(7788);

 

커서

select문에서 반환된 결과가 여러개인 경우에도 값을 처리할 수 있게 해줌

DECLARE  --sql영역 생성
  -- 커서 선언(* into절이 없는 select문)
  CURSOR 커서명 IS select문;
BEGIN
  -- 커서 열기(첫번째  행을 가리킴)
  OPEN 커서명;
  --커서로부터 데이터를 하나씩 읽어와 변수에 저장(레코드 없을 때까지 읽어옴)
  FETCH 커서명 INTO 변수;
--커서 닫기
CLOSE 커서명;
END;

--프로시저 생성
CREATE OR REPLACE PROCEDURE CURSOR_SAMPLE01
IS
   VDEPT DEPT%ROWTYPE;
   CURSOR C1 
   IS
   SELECT * FROM DEPT; 
BEGIN
   DBMS_OUTPUT.PUT_LINE('부서번호 / 부서명 / 지역명'); 
   DBMS_OUTPUT.PUT_LINE('-----------------------');
   --커서 열기
   OPEN C1;
--반복하기 위해 LOOP로 묶어줌
LOOP
   --데이터를 읽어와 변수에 저장
   FETCH C1 INTO VDEPT.DEPTNO, VDEPT.DNAME, VDEPT.LOC;
   --다 읽었으면 나가기
   EXIT WHEN C1%NOTFOUND;
   --출력할 내용
   DBMS_OUTPUT.PUT_LINE(VDEPT.DEPTNO||
      '  '||VDEPT.DNAME||'  '||VDEPT.LOC);   
END LOOP;
   --다 되면 닫아줌
   CLOSE C1;
END;
/

 

커서 속성	의미
%NOTFOUND	커서 영역의 자료가 모두 FETCH됐었다면 TRUE
%FOUND	커서 영역에 FETCH 되지 않은 자료가 있다면 TRUE
%ISOPEN	커서가 OPEN된 상태이면 TRUE
%ROWCOUNT	커서가 얻어 온 레코드의 개수

 

명시적 커서 FOR LOOP

for loop는 각 반복마다 커서를 열고 모든 행이 처리되면 자동으로 close됨

FOR record_name IN cursor_name LOOP
sql문1;
sql문2;
. . . . . .
END LOOP

CREATE OR REPLACE PROCEDURE CURSOR_SAMPLE02
IS
  VDEPT DEPT%ROWTYPE;
  CURSOR C1 
IS
  SELECT * FROM DEPT;
BEGIN
  DBMS_OUTPUT.PUT_LINE('부서번호 / 부서명 / 지역명'); 
  DBMS_OUTPUT.PUT_LINE('----------------------------');
FOR VDEPT IN C1 LOOP	--into가 아니라 in이 사용됨
  EXIT WHEN C1%NOTFOUND;
  DBMS_OUTPUT.PUT_LINE(VDEPT.DEPTNO||
    ' '||VDEPT.DNAME||' '||VDEPT.LOC); 
END LOOP;
END;
/