Oracle DBMS 및 SQL - 프로시저(PROCEDURE), 저장프로시저와 저장함수

--PL/SQL => PL(Procedural Language Extension)/ SQL(Structured Query Language)

--DML의 단순 데이터 활용 한계로 SQL 언어에 절차적 언어의 요소를 더하여 데이터 처리를 
--향상시킨 기능이다. 일반 프로그래밍 언어에서 제공하는 많은 기능들이 탑재되어 있다.

--DB에 직접 탑재되어 컴파일되고 실행되어 성능 면에서도 우수하다.

 

--1. 기본구성 : DECLARE ~ BEGIN ~ EXCEPTION ~ END

 

--1) 함수 구성

--출력창에서 보려고 하면은 기본설정인 OFF를 ON으로 바꿔주어야한다.

 

 

--2) 선택처리 : IF ~ THEN ~ END IF, IF ~ THEN ~ ELSE ~ END IF, IF ~ THEN ~ ELSIF ~ END IF

 

 

--3) 반복문 : LOOP ~ END LOOP, FOR ~ LOOP ~ END LOOP, WHILE ~ LOOP ~ END LOOP

 

-- FOR ~ LOOP ~ END LOOP

 

-- LOOP ~ END LOOP

 

-- WHILE ~ LOOP ~ END LOOP

 

 

-- 2. PL/SQL 에서 SQL
-- 1) SELECT 컬럼리스트 INTO 스칼라변수 FROM 테이블 WHERE

/* 2-1) PL/SQL 사용하는 DML 명령어
    1) SELECT 컬럼리스트 INTO 스칼라변수 FROM 테이블 WHERE
    2) UPDATE 테이블명 SET 컬럼명 = 스칼라변수(래퍼런스변수),... ,.. WHERE 조건절 [스칼라/래퍼변수]
    3) INSERT INTO 테이블명(컬럼리스트) VALUES(스칼라 또는 래퍼런스 변수, ..., ...)
    4) DELETE FROM 테이블명 WHERE 조건절 [스칼라/래퍼변수]
*/

 

---

 

--3. 저장 프로시저와 함수 / 권한 설정 grant create procedure, create session to (계정이름);

--함수와 프로시저 : 반환값이 하나면 함수, 두 개 이상이면 프로시저

--프로시저를 더 많이 사용한다. 

프로시저 권한 설정 방법

 

[정리]

--뷰는 자주쓰는 거 텍스트로 저장해놓은 것

--PL(스칼라변수, 래퍼런스 변수, 조건문, 반복문 사용)/SQL 만 사용하는 것은 한계가 있다. (UPDATE, INSERT, SELECT, DELETE) 따라서, 프로시저와 함께 사용한다.

--프로시저가 뭐야? 자주 실행하거나 복잡한 트랜잭션을 수행하는 PL/SQL 블록을 데이터베이스에 저장하기 위해 사용한다. C나 JAVA언어처럼 함수를 만들어서 저장해놓고 필요할 때마다 쓴다. 

 

 

 

--INSERT : INSERT INO 테이블명(컬럼리스트) VALUES(스칼라 또는 래퍼런스 변수, ..., ...)

 

--DELETE : UPDATE 테이블명 SET 컬럼명 = 스칼라변수(래퍼런스변수),... ,.. WHERE 조건절 [스칼라/래퍼변수]

PL/SQL 프로시저가 성공적으로 완료되어 9번이 삭제되었다. 

 

--SELECT : SELECT 컬럼리스트 INTO 스칼라변수 FROM 테이블 WHERE

--실행이 안된다. OUT한 데이터를 받는 변수의 선언이 필요하다.

 

--1) 바인딩 변수 선언 

--VARIABLE 변수명 자료형;

 

--2) 변수 출력           

--PRINT변수명; 

 

 

 

변수를 선언시에 타입에 주의해서 작성하자

 

 

--4. CURSOR & FETCH / 복수행 출력시에 사용한다. 

-- 수강 테이블 내용 출력

 

프로시저를 사용해서 작성해보고 수강테이블을 출력하는 위의 결과와 똑같은 지 확인해본다. 

 

--부서번호 10에서 근무하는 사원 출력

 

[정리]

--커서 선언

      CURSOR 커서이름 IS SELECT 문장;

 

--커서 열기

          OPEN 커서이름;

 

--커서로부터 데이터 읽기(LOOP END의 반복문을 활용한다)

          LOOP

                 FETCH 커서이름 INTO 저장할 로컬변수

                 EXIT WHEN 조건;

          END LOOP;

--커서닫기

          CLOSE 커서이름;

 

--커서 속성

 %FOUND  -- PL/SQL 코드가 마지막으로 얻은 커서의 결과가 있으면 참.

 %NOTFOUND -- %FOUND의 반대

 %ROWCOUNT -- 커서에서 얻은 레코드수 반환

 %ISOPEN -- 커서가 열렸고 아직 닫히지 않은 상태라면 참

 

 

 

 

 

테이블명에 주의하고 컴파일 후 오류가 없는 지 체크한다. 

 

--과목번호 101번 넣고 실행한다. 

 

-- 합을 출력해보자

 

 

--복수행 EXECUTE() 출력시 바인딩 변수 REFCURSOR 실행 하기 위해,

-- URSOR 관련된 내용을 나의 테이블에 맞게 customize 해야된다.

/*

  %ROWTYPE으로 행을 통째로 받거나, 여러 레코드의 행을 가져오는 경우,

  이를 받을 수 있는 바인딩 변수 자료형이 없다. 

  REFCURSOR을 사용해야하는데, 마치 JAVA에서 클래스를 사용하는 것과 비슷하다.

*/

 

--3-3) 프로시저 삭제

---

--4. 저장함수 : PROCEDURE 차이점은 복수행 출력이 안됨(CURSOR, FETCH)

 

-- 프로시저에서 OUT 여러개 사용할 수 있지만, 저장함수는 RETURN 하나  

 

 

바인딩 변수를 사용해서 출력도 가능하다.