[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;
/