[sql]Procedure(프로시저)

:저장할 수 있는 준비된 SQL 코드로, 코드를 계속 다시 사용할 수 있다. 따라서 반복적으로 작성하는 SQL 쿼리가 있는 경우 이를 저장 프로시저로 저장한 후 호출하여 실행하기만 하면 된다.

1. FUNCTION   -- 함수
   - 결과값 반드시 한개만 돌려준다(RETURN 문 사용)
2. PROCEDURE  -- 프로시저(SUBROUTINE)
  - 결과가 없거나 여러개일때 

107 번 직원의 이름과 월급 조회

 SELECT   FIRST_NAME || ' ' || LAST_NAME 직원이름, SALARY 월급
  FROM    EMPLOYEES
  WHERE   EMPLOYEE_ID = 107;

stored procedure

CREATE PROCEDURE 프로시저명 (매개변수 목록)
BEGIN SQL 명령어 END;

 

CREATE OR REPLACE PROCEDURE  GetEmpSal( IN_EMPID  IN  number  ) 
IS --IS부터 END까지 DECLARE부분
  V_NAME    VARCHAR2(46);
  V_SAL     NUMBER(8, 2);
  BEGIN
     SELECT   FIRST_NAME || ' ' || LAST_NAME, SALARY
      INTO    V_NAME, V_SAL
      FROM    EMPLOYEES  
      WHERE   EMPLOYEE_ID = IN_EMPID;
      DBMS_OUTPUT.PUT_LINE( '이름 :' || V_NAME  );
      DBMS_OUTPUT.PUT_LINE( '월급 :' || V_SAL  );
  END;

 

DBMS_OUTPUT.PUT_LINE() : sysout()

SET SERVEROUTPUT ON

serverouput명령 실행해야 결과가 보인다

SET SERVEROUTPUT ON;
CALL  GETEMPSAL(  107  ); --CALL≒EXECUTE

예제

사번입력, 이름, 전화, 이메일 출력 프로시저

CREATE OR REPLACE PROCEDURE  GETEMPINFO(
    IN_EMPID   IN   NUMBER 
)
IS
    V_EMPID   NUMBER(6, 0);
    V_ENAME   VARCHAR2(46);
    V_PHONE   VARCHAR2(20);
    V_EMAIL   VARCHAR2(25);
 BEGIN
   SELECT    EMPLOYEE_ID, 
             FIRST_NAME || ' ' || LAST_NAME, 
             PHONE_NUMBER, 
             EMAIL
     INTO    V_EMPID,
             V_ENAME,
             V_PHONE,
             V_EMAIL
    FROM     EMPLOYEES   
    WHERE    EMPLOYEE_ID = IN_EMPID;
    
    DBMS_OUTPUT.PUT_LINE( V_EMPID );
    DBMS_OUTPUT.PUT_LINE( V_ENAME );
    DBMS_OUTPUT.PUT_LINE( V_PHONE );
    DBMS_OUTPUT.PUT_LINE( V_EMAIL );
 END;

 

SET SERVEROUTPUT ON;
EXECUTE   GETEMPINFO( 101  );

결과

---

부서번호입력, 해당부서의 최고월급자의 이름, 월급 출력

CREATE OR REPLACE PROCEDURE GET_NAME_MAXSAL(  IN_DEPTID  NUMBER   )
AS
     V_MAXSAL   NUMBER(8,2);
     V_NAME     VARCHAR2(46);
  BEGIN
     SELECT  MAX(SALARY) 
      INTO   V_MAXSAL             
      FROM   EMPLOYEES      
      WHERE  DEPARTMENT_ID = IN_DEPTID;  -- 12008 
     
     SELECT  FIRST_NAME || ' ' || LAST_NAME
      INTO   V_NAME
      FROM   EMPLOYEES
      WHERE  DEPARTMENT_ID = IN_DEPTID
      AND    SALARY = V_MAXSAL;
    
    DBMS_OUTPUT.PUT_LINE('이름:'|| V_NAME) ;   
    DBMS_OUTPUT.PUT_LINE('월급:'|| V_MAXSAL) ; 
      
  END;

 SELECT *
  FROM    EMPLOYEES
  WHERE   DEPARTMENT_ID = 100

결과

 SET SERVEROUTPUT ON;
 EXEC  GET_NAME_MAXSAL(100);

dept id가 100인부서에서 sal이 max인 사람

 

---

-- 익명블럭 

 DECLARE
    V_MAXSAL    NUMBER(8,2);
    IN_DEPTID   NUMBER(6) := 100; -- := IN_DEPID변수에 100을 넣어라
    V_NAME      VARCHAR2(32767); -- PLSQL에서는 VARCHAR2의 최대가 32767 BYTE임.
 BEGIN
   SELECT  MAX(SALARY) 
      INTO   V_MAXSAL             
      FROM   EMPLOYEES      
      WHERE  DEPARTMENT_ID = IN_DEPTID;       
   DBMS_OUTPUT.PUT_LINE( V_MAXSAL  ) ;
   
   SELECT  FIRST_NAME
      INTO   V_NAME
      FROM   EMPLOYEES
      WHERE  DEPARTMENT_ID = IN_DEPTID
      AND    SALARY = V_MAXSAL;
    DBMS_OUTPUT.PUT_LINE( V_NAME  ) ;   
 END;

dept id가 100인부서에서 sal이 max인 사람

 SELECT  MAX(SALARY) 
  --    INTO   V_MAXSAL             
      FROM   EMPLOYEES      
      WHERE  DEPARTMENT_ID = 90;

부서번호가 90인 사람중에서 max sal인 사람의 월급

---

90 번 부서번호입력, 직원들 출력
 
 INTO 사용시 아래예제 처럼 결과가 여러줄 일때는 오류가 발생한다
 ⚠️ORA-01422: 실제 인출은 요구된 것보다 많은 수의 행을 추출합니다.

CREATE OR REPLACE PROCEDURE  GETEMPLIST( IN_DEPTID  NUMBER) 
 AS
      VEID     NUMBER(8,2);
      VFNAME   VARCHAR2(4000);
      VLNAME   VARCHAR2(4000);
      VPHONE   VARCHAR2(4000);
   BEGIN
       SELECT  EMPLOYEE_ID, FIRST_NAME, LAST_NAME, PHONE_NUMBER
        INTO   VEID, VFNAME, VLNAME, VPHONE 
        FROM   EMPLOYEES
        WHERE  DEPARTMENT_ID = IN_DEPTID;
   END;

BEGIN  GETEMPLIST(90); END;

( = EXEC  GETEMPLIST(90);)

⚠️ 오류 보고 -ORA-01422: 실제 인출은 요구된 것보다 많은 수의 행을 추출합니다
ORA-06512: "HR.GETEMPLIST",  8행
ORA-06512:  1행

∵ 변수개수가 4개뿐인데 60번부서의 사원이 2명이상이기때문에 변수개수가 모자람
 
💡해결책) 커서( CURSOR ) 사용 

CREATE OR REPLACE  PROCEDURE GET_EMPLIST( 
        IN_DEPTID  IN    NUMBER, --IN 파라미터
        O_CUR      OUT   SYS_REFCURSOR ) --OUT파라미터 SYS_REFCURSOR로 커서를 돌려준다.
AS
  BEGIN
      
    OPEN O_CUR FOR  
      SELECT  EMPLOYEE_ID, FIRST_NAME, LAST_NAME, PHONE_NUMBER
       FROM   EMPLOYEES
       WHERE  DEPARTMENT_ID  = IN_DEPTID;       
      
  END;

---

📍O_CUR : 변수가 :으로 시작하면 BINDBING 변수라고 부른다.
                  변수에 DATA 외부에 넘어오는 형태로 사용

VARIABLE O_CUR REFCURSOR; --O_CUR를 쓸떄는 DECLARE대신 VARIABLE를 쓴다.
EXEC GET_EMPLIST(90, :O_CUR);

넘어온 아웃데이터를 받는것 리턴을 받을때는 바인딩 변수를 받아서 리턴값을 여러개 받을 수 있다.

PRINT :O_CUR;

 

---

부서명, 직원이름

CREATE OR REPLACE PROCEDURE  GET_DNAME_ENAME(
    IN_EMPID   IN   NUMBER,
    O_DNAME    OUT  VARCHAR2,
    O_ENAME    OUT  VARCHAR2
) 
AS  
    BEGIN --하나만 리턴하는거라 커서사용X
      SELECT   D.DEPARTMENT_NAME,  E.FIRST_NAME || ' ' || E.LAST_NAME
       INTO    O_DNAME          , O_ENAME
       FROM    DEPARTMENTS  D, EMPLOYEES E
       WHERE   D.DEPARTMENT_ID  =  E.DEPARTMENT_ID
        AND    E.EMPLOYEE_ID    =  IN_EMPID ;
        --AND    D.DEPARTMENT_ID  =  90; -- IN_DEPT_ID; 
    END;

 

VAR    O_DNAME           VARCHAR2;

 

VAR    O_ENAME           VARCHAR2;

 

CALL   GET_DNAME_ENAME(200, :O_DNAME, :O_ENAME);

 

PRINT  O_DNAME;

 

PRINT  O_ENAME;

결과

PL/SQL 프로시저가 성공적으로 완료되었습니다.
O_DNAME
--------------------------------------------------------------------------------
Executive 

O_ENAME
--------------------------------------------------------------------------------
Steven King