[Oracle] 오라클 PL/SQL

PL/SQL이란

Procedural Language
PROCEDURE, FUNCTION, PACKAGE , TRIGGER를 제작할 때 사용하는 언어이다.
- PROCEDURE : 리턴형이 없는 함수
  - 리턴형이 없다는 특징이 자바스크립트 언어와 같음
  - 캐시 메모리에 저장되어 속도가 빠르다.
  - 트랜젝션 제어할 때 사용된다.
- FUNCTION : 리턴형이 있는 함수
  - 함수 : 독립(C언어)
  - 메소드 : 클래스 종속
- PACKAGE : 관련된 PROCEDURE , FUNCTION 을 모아서 둔 곳
- TRIGGER : 이미 지정된 이벤트 발생시에 자동 처리
사용자 정의 언어이다.
반복구간이 많을 때 재사용하여 코드를 단축하기 위함이 목적이다.
코드를 통해 PL/SQL 내용이 드러나지 않기 때문에 정보 보안에 유리하다.

PL/SQL BLOCK 기본 구성

형식

DECLARE
-- 선언부(변수선언)
BEGIN
-- 구현부
-- 예외처리 (생략 가능)
END;
/

사용시 유의사항
- SELECT에서 실행된 결과값을 받을 때, INTO를 사용한다.
- 변수에 값 설정 할 때, :=를 사용한다.

PL/SQL에서 변수의 의미와 사용법

변수

지역변수
매개변수 : SUBSTR(‘‘,1,2) MAX(컬럼명)

변수 사용법

스칼라변수 : 단순 변수(NUMBER,VARCHAR2,CLOB,DATE)
- id VARCHAR2(10)

SET SERVEROUTPUT ON;
DECLARE
    vempno NUMBER(4);
    vename VARCHAR2(20);
    vjob VARCHAR2(20);
    vhiredate DATE;
    vsal NUMBER(7,2);
BEGIN
    SELECT empno,ename,job,hiredate,sal INTO vempno,vename,vjob,vhiredate,vsal
    FROM emp
    WHERE empno=7788;
    -- 출력
    DBMS_OUTPUT.PUT_LINE('======결과======');
    DBMS_OUTPUT.PUT_LINE('사번:'||vempno);
    DBMS_OUTPUT.PUT_LINE('이름:'||vename);
    DBMS_OUTPUT.PUT_LINE('직위:'||vjob);
    DBMS_OUTPUT.PUT_LINE('입사일:'||vhiredate);
    DBMS_OUTPUT.PUT_LINE('급여:'||vsal);
END;

%TYPE : 참조변수
- emp.ename%TYPE => ename이 가지고 있는 데이터형을 가지고 온다.
- 다른 테이블이 가지고 있는 데이터 원형을 가지고올 때 사용한다.

SET SERVEROUTPUT ON;
DECLARE
    vempno NUMBER(4);
    vename VARCHAR2(20);
    vjob VARCHAR2(20);
    vhiredate DATE;
    vsal NUMBER(7,2);
BEGIN
    SELECT empno,ename,job,hiredate,sal INTO vempno,vename,vjob,vhiredate,vsal
    FROM emp
    WHERE empno=7788;
    -- 출력
    DBMS_OUTPUT.PUT_LINE('======결과======');
    DBMS_OUTPUT.PUT_LINE('사번:'||vempno);
    DBMS_OUTPUT.PUT_LINE('이름:'||vename);
    DBMS_OUTPUT.PUT_LINE('직위:'||vjob);
    DBMS_OUTPUT.PUT_LINE('입사일:'||vhiredate);
    DBMS_OUTPUT.PUT_LINE('급여:'||vsal);
END;

%ROWTYPE :
- emp%ROWTYPE => emp테이블이 가지고 있는 모든 컬럼의 데이터형을 읽어 온다.

SET SERVEROUTPUT ON;
DECLARE
    vemp emp%ROWTYPE;
BEGIN
    SELECT * INTO vemp
    FROM emp
    WHERE empno=7788;
    -- 출력
    DBMS_OUTPUT.PUT_LINE('======결과======');
    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;

RECORD : VO와 같다. 여러개의 테이블 컬럼을 받아서 처리한다. 복합변수
CURSOR : ArrayList와 같다. 여러개의 ROW처리가 가능하다.
- 스칼라변수 , %TYPE , %ROWTYPE , RECORD 는 한 개의 ROW만 처리 가능하다.

**출력함수**
- DBMS_OUTPUT.PUT_LINE() : System.out.println()
- DBMS_OUTPUT.PUT() : System.out.print()

PL/SQL Cursor(커서)

Cursor란?

여러개의 Row(Record)데이터를 저장하는 공간이다.
- 처리하고 자바에서 받을 때, ResultSet으로 받는다.

CURSOR emp_cur IS
  SELECT * FROM emp;

Cursor 사용 방법

1) 커서 등록

Cursor cur_name IS
  SELECT * FROM emp

2) open
3) fetch : 데이터 가져오기
4) close

Cursor 실습

예)

SET SERVEROUTPUT ON;
DECLARE
    vemp emp%ROWTYPE;
    CURSOR cur IS
        SELECT * FROM emp;
BEGIN
    OPEN cur;
    DBMS_OUTPUT.PUT_LINE('========결과=========');
    LOOP
        FETCH cur INTO vemp;
        EXIT WHEN cur%NOTFOUND; -- 데이터가 없는 경우 
        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);
        DBMS_OUTPUT.PUT_LINE('=====================');
    END LOOP;
    CLOSE cur;
END;

예) for문 사용하기

SET SERVEROUTPUT ON;
DECLARE
    vdept dept%ROWTYPE;
    CURSOR cur IS
        SELECT * FROM dept;
    
BEGIN
    FOR vdept IN cur LOOP
        DBMS_OUTPUT.PUT_LINE(vdept.deptno||' '||vdept.dname||' '||vdept.loc);
    END LOOP;
        
END;
/

PL/SQL에서의 연산자

산술연산자 : + , - , * , / , MOD(숫자,나머지) , % (x)
- %는 사용할 수 없다.
비교연산자 : = , != , <> , ^= , < , > , <= , >=
논리연산자 : OR , AND , NOT , !(x)
- 부정을 표현할 때 !를 사용하면 안되고, NOT을 붙여야 한다.
BETWEEN AND : 기간, 범위
IN
LIKE
제어문 : IF , IF ~ ELSE , FOR

PL/SQL 제어문

조건문

단일 조건문 : IF ~ ELSE
- 비교 연산자(= , != , < , > , <= , >=) , 논리 연산자(NOT , OR , AND)
- 변수에 값 대입할때는 :=를 사용한다.

IF(조건문) THEN
  처리
END IF;

예)

DECLARE
-- 선언부(변수선언)
    vename emp.ename%TYPE;
    vjob emp.job%TYPE;
    vdname dept.dname%TYPE;
    vdeptno emp.deptno%TYPE;
BEGIN
-- 구현부
-- 변수에 값 대입 => :=
    SELECT deptno,ename,job INTO vdeptno,vename,vjob
    FROM emp
    WHERE ename='KING';
    
    IF(vdeptno=10) THEN
        vdname:='개발부';
    END IF;
    IF(vdeptno=20) THEN
        vdname:='영업부';
    END IF;
    IF(vdeptno=30) THEN
        vdname:='총무부';
    END IF;
    -- 결과값 출력
    DBMS_OUTPUT.PUT_LINE('=======결과값=======');
    DBMS_OUTPUT.PUT_LINE('이름:'||vename);
    DBMS_OUTPUT.PUT_LINE('부서:'||vdname);
-- 예외처리
END;
/

예)

DECLARE
-- 선언부(변수선언)
    vename emp.ename%TYPE;
    vjob emp.job%TYPE;
    vdname dept.dname%TYPE;
    vdeptno emp.deptno%TYPE;
BEGIN
-- 구현부
-- 변수에 값 대입 => :=
    SELECT deptno,ename,job INTO vdeptno,vename,vjob
    FROM emp
    WHERE ename='KING';
    
    IF(vdeptno=10) THEN
        vdname:='개발부';
    ELSIF(vdeptno=20) THEN
        vdname:='영업부';
    ELSIF(vdeptno=30) THEN
        vdname:='총무부';
    END IF;
    -- 결과값 출력
    DBMS_OUTPUT.PUT_LINE('=======결과값=======');
    DBMS_OUTPUT.PUT_LINE('이름:'||vename);
    DBMS_OUTPUT.PUT_LINE('부서:'||vdname);
-- 예외처리
END;
/

선택 조건문 : IF ~ ELSE
- 연산처리 => NULL값일 때, 값이 NULL이다.

IF(조건문) THEN
  처리(조건문이 TRUE) => SQL문장
ELSE
  처리 => SQL문장
END IF;

다중 조건문 : IF ELSIF ~ ELSIF ~ ELSE

IF(조건문) THEN
  처리
ELSEIF(조건문) THEN
  처리 => SQL문장
ELSEIF(조건문) THEN
  처리 => SQL문장
ELSEIF(조건문) THEN
  처리 => SQL문장
ELSE
  처리
END IF;

DECLARE
-- 선언부(변수선언)
    vename emp.ename%TYPE;
    vcomm emp.comm%TYPE;
    vsal emp.sal%TYPE;
BEGIN
-- 구현부
-- 변수에 값 대입 => :=
    SELECT ename,comm,sal INTO vename,vcomm,vsal
    FROM emp
    WHERE ename='MARTIN';
    -- 연산처리 => NULL값일 때, 값이 NULL이다.
    IF(vcomm>0) THEN
        dbms_output.put_line(vename||'님의 성과급은 '||vcomm||' 입니다');
    ELSE
        dbms_output.put_line(vename||'님의 성과급은 없습니다');
    END IF;
-- 예외처리
END;
/

선택문 : CASE ~ WHEN ~ THEN

CASE(조건)
  WHEN 조건 THEN 결과
  WHEN 조건 THEN 결과
  WHEN 조건 THEN 결과
ELSE
  처리
END;

DECLARE
-- 선언부(변수선언)
    vempno emp.empno%TYPE;
    vename emp.ename%TYPE;
    vdeptno emp.deptno%TYPE;
    vdname dept.dname%TYPE;
BEGIN
-- 구현부
-- 변수에 값 대입 => :=
    SELECT empno,ename,deptno INTO vempno,vename,vdeptno
    FROM emp
    WHERE ename='SMITH';
    vdname:=CASE vdeptno
        WHEN 10 THEN '개발부'
        WHEN 20 THEN '총무부'
        WHEN 30 THEN '자재부'
        WHEN 40 THEN '신입'
        END;
    DBMS_OUTPUT.PUT_LINE('======결과======');
    DBMS_OUTPUT.PUT_LINE('이름:'||vename);
    DBMS_OUTPUT.PUT_LINE('사번:'||vempno);
    DBMS_OUTPUT.PUT_LINE('부서:'||vdname);
END;
/

반복문

WHILE

WHILE 조건 LOOP
  처리
END LOOP;

예) while : 1~10까지 출력하기

DECLARE
    i NUMBER:=1; -- 초기값
BEGIN
    WHILE i<=10 LOOP
        DBMS_OUTPUT.PUT_LINE(i);
        --증가식
        i:=i+1;
        END LOOP;
END;

예) while : 짝수만 출력하기

DECLARE
    i NUMBER:=1; -- 초기값
BEGIN
    WHILE i<=10 LOOP
        IF(MOD(i,2)=0) THEN
        DBMS_OUTPUT.PUT_LINE(i);
        END IF;
        --증가식
        i:=i+1;
        END LOOP;
END;
/

basic (loop) : do~while문과 같다.

LOOP
  출력
  증가식
  EXIT WHEN 조건 => 종료조건
END LOOP;

예) basic loop : 1~9까지 출력하기

DECLARE
    i NUMBER:=1; -- 초기값
BEGIN
    LOOP         -- {
        DBMS_OUTPUT.PUT_LINE(i);
        i:=i+1;
        EXIT WHEN i>=10;
    END LOOP;    -- }
END;
/

예) basic loop : 짝수만 출력하기

DECLARE
    i NUMBER:=1; -- 초기값
BEGIN
    LOOP         -- {
        IF(MOD(i,2)=0) THEN
        DBMS_OUTPUT.PUT_LINE(i);
        END IF;
        i:=i+1;
        EXIT WHEN i>=10;
    END LOOP;    -- }
END;
/

FOR
- 가장 많이 쓰인다.
- 선언(DECLARE)이 필요없다.

-- 형식(1) : 코틀린 형식과 동일하다.
FOR 변수 IN low..high LOOP
  출력
END LOOP;

-- 형식(2)
FOR i IN [REVERSE] 1..9 LOOP
  처리
END LOOP;

예) for : 1~10까지 출력

BEGIN
    FOR i IN 1..10 LOOP
        DBMS_OUTPUT.PUT_LINE (i);
    END LOOP;
END;
/

예) for : 정수를 입력받아서 해당 구구단을 출력하는 프로그램을 작성하시오.

ACCEPT pno PROMPT '몇단?'
DECLARE
    vno NUMBER:=&pno;
BEGIN 
    FOR i IN 1..9 LOOP
        DBMS_OUTPUT.PUT_LINE(vno||'*'||i||'='||vno*i);
    END LOOP;
END;

PROCEDURE

PROCEDURE 형식

생성 형식 : ALTER가 없다.

CREATE [OR REPLACE] PROCEDURE pro_name(
  매개변수,
  매개변수,
  매개변수....
    1) 스칼라 변수 , 2) %TYPE
)
IS (AS)
  변수 선언 (지역변수)
BEGIN
  SQL 구현 => 제어문 || 연산자 || SQL
END;
/

매개변수
- IN : SQL 실행 시 필요한 데이터 (WHERE ,INSERT ,UPDATE ,DELETE)
- OUT : SQL 문장을 실행하고 결과값을 받아올 때 사용한다. (SELECT)
삭제 형식

DROP PROCEDURE pro_name;

호출 형식
- SELECT는 EXECUTE로 호출한다.
- INSERT , UPDATE , DELETE는 CALL로 호출한다.

매개변수

IN : 내부에서만 사용하는 변수 , 값을 넣어주기만 하는 것 (INSERT , DELETE)
OUT : Call By Reference , 결과 값을 받는 변수 (SELECT)
INOUT : 내부사용 , 값을 받는 변수
생략하면 default가 IN 변수임
일반 function은 결과값이 1개이기 때문에 사용할 수 없다.
예)

CREATE PROCEDURE empInsert(
  name IN VARCHAR2(20),
  addr IN VARCHAR2(100),
  tel IN VARCHAR2(20),
  result OUT VARCHAR2(100)
)

예) p는 c언어의 포인터와 유사한 out변수 , k는 in변수와 비슷하다

int* p;
void disp(int* p,int k)
(
  *p=100;
)
disp(p,10); ==> p=100

PL/SQL 만들어보기

테이블 제작

CREATE TABLE pl_student(
    hakbun NUMBER PRIMARY KEY,
    name VARCHAR2(34) NOT NULL,
    kor NUMBER,eng NUMBER, math NUMBER
);

값 삽입

INSERT INTO pl_student VALUES(1,'홍길동',90,90,100);
INSERT INTO pl_student VALUES(2,'박문수',85,80,75);
COMMIT;

추가함수 작성

CREATE OR REPLACE PROCEDURE studentInsert(
    pName pl_student.name%TYPE,
    pKor pl_student.kor%TYPE,
    pEng pl_student.eng%TYPE,
    pMath pl_student.math%TYPE
)
IS
BEGIN
    INSERT INTO pl_student VALUES(
        (SELECT NVL(MAX(hakbun)+1,1) FROM pl_student),
        pName,pKor,pEng,pMath
    );
    COMMIT;
END;
/

추가함수 호출하여 값 삽입

CALL studentInsert('심청이',80,90,76);

데이터 하나만 상세 읽기 함수 작성

CREATE OR REPLACE PROCEDURE studentDetailData(
    pNo NUMBER,
    pName OUT VARCHAR2,
    pKor OUT NUMBER,
    pEng OUT NUMBER,
    pMath OUT NUMBER
)
IS 
BEGIN
    SELECT name,kor,eng,math INTO pName,pKor,pEng,pMath
    FROM pl_student
    WHERE hakbun=pNo;
END;
/

데이터 하나만 상세 읽기 함수 실행

VARIABLE pName VARCHAR2;
VARIABLE pKor NUMBER;
VARIABLE pEng NUMBER;
VARIABLE pMath NUMBER;
EXECUTE studentDetailData(1,:pName,:pKor,:pEng,:pMath);
PRINT pName;
PRINT pKor;
PRINT pEng;
PRINT pMath;

삭제하기 함수 제작

CREATE OR REPLACE PROCEDURE studentDelete(
    pNo pl_student.hakbun%TYPE
)
IS 
BEGIN
    DELETE FROM pl_student
    WHERE hakbun=pNo;
    COMMIT;
END;
/

Update하기
- 값만 넣어주는 것은 IN 변수
- 값을 가져오는 것은 OUT 변수

CREATE OR REPLACE PROCEDURE studentUpdate(
    pNo NUMBER,
    pName VARCHAR2,
    pKor NUMBER,
    pEng NUMBER,
    pMath NUMBER
)
IS
    -- 변수 필요없음
BEGIN
    UPDATE pl_student SET
    name=pName,kor=pKor,eng=pEng,math=pMath
    WHERE hakbun=pNo;
    COMMIT;
END;
/

totalPage 함수
- SYS_REFCURSOR : 자바에서 받아서 처리할 수 있게 하기 위해서

CREATE OR REPLACE PROCEDURE studentListData(
    pResult OUT SYS_REFCURSOR
)
IS
BEGIN
    OPEN pResult FOR
        SELECT * FROM pl_student;
END;
/

평균 구하기

CREATE OR REPLACE FUNCTION studentAvg(
    pNo pl_student.hakbun%TYPE
)RETURN NUMBER
IS
    pAvg NUMBER;
BEGIN
    SELECT (kor+eng+math)/3 INTO pAvg
    FROM pl_student
    WHERE hakbun=pNo;
    
    RETURN pAvg;
END;
/

총점 구하기

CREATE OR REPLACE FUNCTION studentTotal(
    pNo pl_student.hakbun%TYPE
)RETURN NUMBER
IS
    pTotal NUMBER;
BEGIN
    SELECT (kor+eng+math) INTO pTotal
    FROM pl_student
    WHERE hakbun=pNo;
    
    RETURN pTotal;
END;
/

각 과목별 점수, 총점, 평균 확인하기

SELECT name,kor,math,eng,studentAvg(hakbun),studentTotal(hakbun)
FROM pl_student;

FUNCTION

Function은 리턴값이 있는 함수이다.

Tip

PROCEDURE : 리턴값이 없는 함수가 필요할 때 사용함
FUNCTION : 리턴값이 있는 함수가 필요할 때 사용함

FUNCTION 형식

CREATE [OR REPLACE] FUNCTION func_name(
  매개변수 (OUT 변수 존재X)
  ...
) RETURN 데이터형 (결과값의 데이터형)
IS
  변수선언(지역변수)
BEGIN
  SQL 구현 => 쿼리문장
  RETURN 값 (결과값 보내주기)
END;
/

매개변수
- OUT변수가 존재하지 않는다.

Tip

IN변수와 OUT변수

IN변수 : Call by value(값에 의한 호출)
OUT변수 : Call by reference(참조에 의한 호출)

RETURN
- IS 앞에 선언한 RETURN 데이터형과 실제 RETURN되는 결과값의 데이터형이 동일해야 한다.

FUNCTION 실습해보기

예 1-1) JOIN을 통해 값 출력하기

SELECT ename,job,dname,loc
FROM emp,dept
WHERE emp.deptno=dept.deptno;

예 1-2) SubQuery을 통해 값 출력하기

SELECT ename,job,(SELECT dname FROM dept d WHERE d.deptno=e.deptno) as dname,
(SELECT loc FROM dept d WHERE d.deptno=e.deptno) as loc
FROM emp e;

예 1-3) Function을 통해 값 출력하기
- 사용 : SELECT ename,job,getDname(deptno) FROM emp;

CREATE OR REPLACE FUNCTION getDname(
    pDeptno emp.deptno%TYPE
) RETURN VARCHAR
IS
    vdname dept.dname%TYPE;
BEGIN
    SELECT dname INTO vdname
    FROM dept
    WHERE deptno=pDeptno;
    RETURN vdname;
END;
/

예 2) Function을 통해 loc값 출력하기
- 값 조회하기 : SELECT ename,job,getLoc(deptno) FROM emp;

CREATE OR REPLACE FUNCTION getLoc(
    pDeptno emp.deptno%TYPE
) RETURN VARCHAR
IS 
    vdname dept.dname%TYPE;
BEGIN
    SELECT loc INTO vdname
    FROM dept
    WHERE deptno=pDeptno;
    RETURN vdname;
END;
/

이 블로그 검색

프로그래밍 공부 노트

[Oracle] 오라클 PL/SQL

PL/SQL이란

PL/SQL BLOCK 기본 구성

PL/SQL에서 변수의 의미와 사용법

변수

변수 사용법

PL/SQL Cursor(커서)

Cursor란?

Cursor 사용 방법

Cursor 실습

PL/SQL에서의 연산자

PL/SQL 제어문

조건문

반복문

PROCEDURE

PROCEDURE 형식

매개변수

PL/SQL 만들어보기

FUNCTION

FUNCTION 형식

FUNCTION 실습해보기

댓글

댓글 쓰기