PL/SQL _ Oracle's Procedural Language extension to SQL
오라클 SQL을 확장하여 사용하는 프로그래밍 언어
절차지향형 프로그래밍 언어
DECLARE ---선언부
IS
BEGIN ---실행부
쿼리문
END;
PL/SQL문을 사용하는 이유
1. 대용량 데이터 연산이 필요할 때 서버로 전송해서 처리하려면 네트워크에 부하가 걸릴 수 있는데
이때 함수를 사용하여 데이터를 연산하고 가공한 후에 결과만 서버로 전송하면 부담을 줄일 수 있다.
2. 로직을 수정하기 위해 서버를 셧다운시킬 필요가 없다.
3. 쿼리문을 직접 노출하지 않는 만큼 SQL injection 의 위험이 줄어든다. (SQL injection : 해킹의 한 형태)
프로시저(PROCEDURE)
PL/SQL문의 대표적인 부 프로그램
데이터베이스에 대한 일련의 작업을 정리한 절차를 RDBMS에 저장한 것
일련의 쿼리를 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합
프로시저를 호출하면 내부적으로 PL/SQL 코드가 실행됨
CREATE OR REPLACE PROCEDURE 프로시저명 (
매개변수1 IN 타입 := 값,
매개변수2 IN 타입 %TYPE
)
IS
함수 내에서 사용할 변수 선언
BEGIN
실행할 명령
END;
사용법
CALL 프로시저명 (값1, 값2 ... )
장점
하나의 요청으로 여러 SQL문 실행 가능
네트워크 소요시간을 줄여 성능개선 가능
단점
문자나 숫자연산에 사용하면 오히려 C나 JAVA보다 느린 성능
쉽지 않은 유지보수
--간단한 프로시저 만들어보기
--F(X) = 2X + 1;
--F는 프로시저명, X가 매개변수, 결과값은 2X+1
CREATE OR REPLACE PROCEDURE F(
X IN NUMBER
)
IS --변수는 필요없어서 안씀
BEGIN
DBMS_OUTPUT.PUT_LINE(2*X+1); --콘솔에 찍고싶다면 PUT_LINE
END;
CALL F(3);
JOBS 테이블에 데이터를 INSERT해주는 프로시저 만들기 (변수명 : MY_NEW_JOB_PROC)
CREATE OR REPLACE PROCEDURE MY_NEW_JOB_PROC(
--JOBS테이블에 INSERT하기 위해서는 컬럼 4개에 대한 값이 필요함
P_JOB_ID IN JOBS.JOB_ID%TYPE,
P_JOB_TITLE IN JOBS.JOB_TITLE%TYPE,
P_MIN_SALARY IN JOBS.MIN_SALARY%TYPE,
P_MAX_SALARY IN JOBS.MAX_SALARY%TYPE
)
IS
BEGIN
INSERT INTO JOBS(JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY)
VALUES(P_JOB_ID, P_JOB_TITLE, P_MIN_SALARY, P_MAX_SALARY);
DBMS_OUTPUT.PUT_LINE('ALL DONE ABOUT'||' '||P_JOB_ID);
END;
CALL MY_NEW_JOB_PROC('IT','Developer',14000,20000);
SELECT * FROM JOBS;
JOBS 테이블에 데이터를 INSERT 하거나
INSERT하고자 하는 데이터가 이미 존재한다면 UPDATE해주는 프로시저 만들기 (변수명 : MY_NEW_JOB_PROC)
CREATE OR REPLACE PROCEDURE MY_NEW_JOB_PROC(
--JOBS테이블에 INSERT하기 위해서는 컬럼 4개에 대한 값이 필요함
P_JOB_ID IN JOBS.JOB_ID%TYPE,
P_JOB_TITLE IN JOBS.JOB_TITLE%TYPE,
P_MIN_SALARY IN JOBS.MIN_SALARY%TYPE,
P_MAX_SALARY IN JOBS.MAX_SALARY%TYPE
)
IS
CNT NUMBER := 0; --카운트넘버를 0으로 초기화
BEGIN
SELECT COUNT(JOB_ID) INTO CNT
FROM JOBS WHERE JOB_ID = P_JOB_ID;
IF CNT = 0 THEN --중복값이 없다면 아래 INSERT 쿼리문 그대로 실행
INSERT INTO JOBS(JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY)
VALUES(P_JOB_ID, P_JOB_TITLE, P_MIN_SALARY, P_MAX_SALARY);
DBMS_OUTPUT.PUT_LINE('INSERT DONE ABOUT'||' '||P_JOB_ID); --기존 INSERT 구문
ELSE --중복값이 있다면
UPDATE JOBS --update로 갱신함
SET JOB_TITLE = P_JOB_TITLE,
MIN_SALARY = P_MIN_SALARY,
MAX_SALARY = P_MAX_SALARY --마지막은 반점 없음
WHERE JOB_ID = P_JOB_ID;
DBMS_OUTPUT.PUT_LINE('UPDATE DONE ABOUT'||' '||P_JOB_ID); --새로운 UPDATE 구문
END IF;
END;
CALL MY_NEW_JOB_PROC('IT','Developer',5000,10000);
SELECT * FROM JOBS;
JOBS 테이블에서 지정하는 내용과 일치하는 조건의 행을 DELETE하는 프로시저 만들기 (변수명 : DEL_JOB_PROC)
CREATE OR REPLACE PROCEDURE DEL_JOB_PROC(
P_JOB_ID IN JOBS.JOB_ID%TYPE --매개변수로 하나만 지정한 행을 삭제할 것이기 때문에
INSERT, UPDATE와 달리 하나의 매개변수만 두어도 무관
)
IS
CNT NUMBER := 0;--카운트넘버를 0으로 초기화
BEGIN
SELECT COUNT(JOB_ID) INTO CNT
FROM JOBS WHERE JOB_ID = P_JOB_ID;
IF CNT = 1 THEN -- 1 또는 !=0
DELETE FROM JOBS WHERE JOB_ID = P_JOB_ID; --위에서 지정한 매개변수로 DELETE
DBMS_OUTPUT.PUT_LINE('DELETE DONE ABOUT'||' '||P_JOB_ID);
ELSE
DBMS_OUTPUT.PUT_LINE('NO EXIST'||' '||P_JOB_ID);
END IF;
END; --create부터 end까지 블록지정해서 실행해보기
CALL DEL_JOB_PROC('IT') --매개변수가 잡아이디 하나였으므로 하나만 call
SELECT * FROM JOBS;
IF문 (3가지)
IF 조건 THEN 실행문;
END IF;
IF 조건 THEN 실행문;
ELSE 실행문;
END IF;
IF 조건식1 THEN 실행문;
ELSIF 조건식2 THEN 실행문;
ELSE 실행문;
END IF;
--정수에 맞는 학점 출력하기
DECLARE
SCORE NUMBER := 80; --SCORE는 변수, :=가 대입
GRADE VARCHAR2(5);
BEGIN
IF SCORE >=90 THEN GRADE := 'A';
ELSIF SCORE >=80 THEN GRADE := 'B';
ELSIF SCORE >=70 THEN GRADE := 'C';
ELSIF SCORE >=60 THEN GRADE := 'D';
ELSE GRADE :='F';
END IF;
DBMS_OUTPUT.PUT_LINE('점수 : '||SCORE||'점'||CHR(10)||'학점 : '||GRADE);
END;
요약
PL/SQL : Oracle 데이터베이스에서 사용되는 프로그래밍 언어로, 프로시저를 포함한 여러 유형의 데이터베이스 객체와 상호 작용하는 데 사용됨.
프로시저 : PL/SQL 코드로 작성된 데이터베이스 로직을 캡슐화하고 실행하는 방법 중 하나이며, PL/SQL은 이러한 프로시저를 작성하고 호출하는 데 사용되는 언어.
'DBMS (MySQL) > DBMS (Oracle)' 카테고리의 다른 글
| [DB] NULL과 NVL (1) | 2023.10.19 |
|---|---|
| [DB] 시퀀스(SEQUENCE) (0) | 2023.10.19 |
| [DB] VIEW (1) | 2023.10.18 |
| [DB] 서브쿼리 (1) | 2023.10.17 |
| [DB] ROLLUP과 CUBE (0) | 2023.10.17 |