PL/SQL 1: 문법
프로시저(PL/SQL) 피엘에스큐엘 문법
PL/SQL
1) 오라클에서 제공하는 절차적언어
2) 사용자 정의 함수(문법) : procedure, function
2-1) function : 리턴형이 존재
(셀렉트와 관련)
=> max, min, count 등 셀렉트 문법
2-2) procedure: 리턴형이 존재하지 않는 함수
(인서트, 업데이트와 관련)
– 프로시저는 캐시메모리에 저장 (속도가 빠르다)
– 여러개의 SQL문장을 동시에 처리할 수 있다
– 반복적으로 수행되는 문장에서 주로 사용(자바의 메소드와 비슷한 역할임)
ex) 페이지나누기
3) 형식
3-1) 선언부 => 변수 선언(지역변수)
3-2) 구현부 => SQL
3-3) 예외처리부 =>Exception
4) 변수 선언 방식
4-1) 단일변수 선언
vempno NUMBER(4);
또는
vempno emp.empno%TYPE;
–emp테이블의 empno와 동일한 데이터 형으로 선언함
(위의 것은 데이터 형의 크기를 외워야 하므로 주로 아래 것으로 사용함)
4-2) ROW변수 선언
vemp emp%ROWTYPE => 레코드변수 (자바에서는 VO와 같다)
4-3) 사용자 정의변수(RECORD) 선언
필요한 데이터를 사용자 정의한다. 사용자 정의 타입(레코드)는 조인이 걸려도 쓸 수 있다.
TYPE ed IS RECORD(
vempno NUMBER,
vename VARCHAR2(10)
);
4-4) 목록처리(CURSOR)
CURSOR => ArrayList와 비슷. 자바에서는 ResultSet과 같다.
5) 제어문
조건문, 반복문
조건문: 단일, 다중, 선택
5-1) 조건문
단일
IF(조건문) THEN
SQL
END IF;
다중
IF(조건문) THEN
SQL
ELSE IF(조건문) THEN
SQL
ELSE
SQL
END IF;
선택
IF(조건문) THEN
SQL
ELSE
SQL
END IF;
5-2) 반복문
FOR i IN 1..9 LOOP
처리
END LOOP;
예제1) 단일변수 선언/출력
1. sqlplus에서 set serveroutput on 이라고 칠것.
2. ed test 로 아래 내용을 작성
DECLARE
vempno emp.empno%TYPE;
vename emp.ename%TYPE;
vjob emp.job%TYPE;
vday emp.hiredate%TYPE;
vsal emp.sal%TYPE;
BEGIN
SELECT empno,ename,job,hiredate,sal INTO vempno,vename,vjob,vday,vsal
FROM emp
WHERE empno=7788;
DBMS_OUTPUT.PUT_LINE(‘***** Result *****’);
DBMS_OUTPUT.PUT_LINE(‘사번:’||vempno);
DBMS_OUTPUT.PUT_LINE(‘이름:’||vename);
DBMS_OUTPUT.PUT_LINE(‘직위:’||vjob);
DBMS_OUTPUT.PUT_LINE(‘입사일:’||vday);
DBMS_OUTPUT.PUT_LINE(‘급여:’||vsal);
END;
/
3. @test로 실행
예제2) 레코드 변수 선언/출력
DECLARE
vemp emp%ROWTYPE;
BEGIN
SELECT * INTO vemp
FROM emp
WHERE empno=7900;
DBMS_OUTPUT.PUT_LINE(‘***** Result *****’);
DBMS_OUTPUT.PUT_LINE(‘사번:’||vemp.empno);
DBMS_OUTPUT.PUT_LINE(‘이름:’||vemp.ename);
DBMS_OUTPUT.PUT_LINE(‘직위:’||vemp.job);
DBMS_OUTPUT.PUT_LINE(‘입사일:’||vemp.hiredate);
DBMS_OUTPUT.PUT_LINE(‘급여:’||vemp.sal);
END;
/
예제3) 사용자 정의 레코드 변수 선언/출력
내가 원하는 형태로 정의할 수 있는게 레코드임. (VO를 만드는 것과 같음)
DECLARE
TYPE empdeptRecord IS RECORD
(
empno emp.empno%TYPE,
ename emp.ename%TYPE,
job emp.job%TYPE,
dname dept.dname%TYPE,
loc dept.loc%TYPE
);
ed empdeptRecord;
BEGIN
SELECT empno,ename,job,dname,loc INTO ed
FROM emp,dept
WHERE emp.deptno=dept.deptno
AND empno=7900;
DBMS_OUTPUT.PUT_LINE(‘***** Result *****’);
DBMS_OUTPUT.PUT_LINE(‘사번:’||ed.empno);
DBMS_OUTPUT.PUT_LINE(‘이름:’||ed.ename);
DBMS_OUTPUT.PUT_LINE(‘직위:’||ed.job);
DBMS_OUTPUT.PUT_LINE(‘부서명:’||ed.dname);
DBMS_OUTPUT.PUT_LINE(‘지역:’||ed.loc);
END;
/
예제4)
ACCEPT pno PROMPT ‘사번:’
–자바의 SCANNER와 같다
DECLARE
vempno emp.empno%TYPE:=&pno;
–주소값을 넘김
vename emp.ename%TYPE;
BEGIN
SELECT ename INTO vename
FROM emp
WHERE empno=vempno;
DBMS_OUTPUT.PUT_LINE(‘이름:’||vename);
END;
/
/*
int *p;
int a;
p=&a;
int b=*p;
*/