[SQL] PL/ SQL

PL / SQL

- SQL 의 장점은 잘만든 쿼리문 하나로 원하는 데이터를 검색 조작할 수 있다는 점인데, SQL문 자체는 비 절차적 언어이기 때문에 몇개의 쿼리문 사이에 어떠한 연결 및 절차성이 있어야 하는 경우는 사용할 수 없다.

- IF문, FOR문 안에 SQL를 사용할 수 있다.

 

- 이 점을 극복하기 위해서 SQL언어에 절차적인 프로그래밍 언어를 추가해서 만든 것이 PL/SQL이다.

 

PL / SQL 구조

DECLARE SECTION (선언부)

 - PL / SQL에서 사용하는 모든 변수나 상수를 선언

 ex. b emp.empno%type; <- 변수선언 / 존재하는 테이블 안의 컬럼이 갖는 자료형을 참조

c number(2) := 30;  <-- 선언과 동시에 초기화가 가능하다. 새우

 

EXECUABLE SECTION (실행부)

 - 절차적 형식으로 SQL을 실행할 수 있도록 절차적요소인 제어문, 반복문

 - 함수정의 등 로직기술

 - SQL문 1, 2, 3(DQL, DML) 작성

                     주의! SELECT EMPNO, ENAME, SAL INTO a,b,c FROM EMP;

  dbms_output.put_line(a) <--조회 가능 (a,b,c라는 변수에 값을 담았기 때문

 

if else 조건문

 if(조건식)

   then  -- if시작

        SQL 4; (조건이 참일때 실행할 문장)

 end if -- if끝

 

 if 조건식

    then  조건 결과가 참일때 실행할문장;

    else   조건 결가가 거짓일때 실행할 문장;

 end if;

 

 if 조건식1

    then 조건식1 참일때 실행할 문장

    elseif 조건식2

            then 조건식1이 거짓이고 조건식2가 참일때 실행할 문장

    elseif 조건식3

            then 조건식1,2가 거짓이고 조건식3 참일때 실행할 문장

    else 조건식 1,2,3이 거짓일때 실행할 문장;

 end if;

 

반복문

for 변수 in [reverse] 최소값 .. 최대값

Loop

   반복실행할 문장;

end Loop;

 

loop(반복문)

Loop

   반복실행할 문장1;

   반복실행할 문장2;

  exit [when 조건식] ; -- 탈출 조건

End Loop;

 

EXCEPTION SECTION (예외처리)

 - 실행 중 발생되는 에러를 해결하기 위한 문장으로 구성

 

변수선언!

vempno number(4)

vempno2 emp.empno % type;

  --> 존재하는 emp테이블의 empno컬럼이 갖는 자료형을 참조

 

레퍼런스 변수

  - %TYPE (한개의 컬럼), %ROWTYPE(한 행을 구성하는 모든 컬럼을 참조)

  vemp emp%ROWTYPE;

  --> emp테이블이 갖는 모든 컬럼의 자료형을 vemp변수로 참조하겠음

---

저장프로시저 - Stored Procedure

- 자주 사용되는 DML, DQL을 모듈화 시켜서 DB에 저장하였다가 필요할때 호출해서 사용

- JAVA의 메소드와 같음

 

CREATE [ OR REPLACE ] PROCEDURE 프로시저명 (SU NUMBER, 변수명2 [ IN / OUT ] 자료형)

                                                                                    -- 매개변수가 없을시에는 ()를 빼줘야 한다.

IS

   실행부 변수 선언

BEGIN 

   실행할 문장1 ;

   실행할 문장2 ;

   실행할 문장3 ;

  -- 초기화, SQL문, 함수호출, 절차적요소( 조건문, 반복문 )

END;

/

 

실행방법!

EXECUTE 프로시저명 ( ) ;

 

바인딩변수 사용법

CREATE OR REPLACE PROCEDURE del_dept(delNo in number, tomorrow out varchar2)

IS

BEGIN

tomorrow := '토요일' ;

 

DML문 

COMMIT; -- 동시에 여러번 수정된다면 문제가 생길 수 있어 commit

END;

---

저장함수 - Stored Function

- 실행 (함수호출) 후 결과를 되돌려 받을 수 있는 return 데이터';를 반드시 명시해야 한다.

 

형식.

CREATE [OR REPLACE] FUNCTION 함수명( 매개변수 선언)

RETURN 자료형

IS

변수선언 :바이드변수 := 함수명();

BEGIN

  (순차적으로) 실행할 SQL문

   RETURN 데이터;

END;

 

사용법.

1. CREATE FUNCTION ~ (함수를 DB에 저장) -- 함수 생성

2. VARIABLE 바인드 변수명 자료형; -- 변수 선언

3. EXEC 함수명 :바인드변수명 := 함수명() -- 함수호출, 결과값 저장

4. PRINT 바인드변수명; -- 저장값 확인

 

참고. 

void hello(String name){

  Sysout("안녕,"+name);

}

String hi(){

  return "좋은하루!";

}

---

Cursor

select의 결과가 2개행 이상일 때 명시적으로 사용한다.

2개행 이상을 출력하는 select문을 저장하는 변수

 

declare

  변수선언, 커서정의

  CURSOR 커서명 IS SELECT문장;

BEGIN

  커서 사용

  OPEN 커서명;

    FETCH 커서명 INTO 변수명;

  CLOSE 커서명;

 

declare
     
     CURSOR cur is select deptno, dname, loc from dept;
         --변수명   테이블명.컬럼명%type;  ==> 특정테이블의 컬럼에 정의된 자료형 참조!!
         --변수명   테이블명%rowtype;     ==> 특정테이블이 갖는 모든 컬럼에 정의된 자료형 참조!!
     vdept  dept%rowtype; -- %rowtype(테이블 타입- 테이블을 구성하는 모든 컬럼!!)    
  begin
     OPEN cur;
     
     dbms_output.put_line('부서번호/부서명/부서위치');
     dbms_output.put_line('------------------------------');
     loop
      FETCH cur into vdept; -- vdept: deptno, dname, loc ( vdept== dept테이블!!)
        exit when cur%notfound; -- 커서를 통해 인출된 행이 없다면 
      dbms_output.put_line(vdept.deptno ||'/' || 
                           vdept.dname || '/' || vdept.loc);
     end loop;
     CLOSE cur;     
  end;
  /