PL/SQL 정리, 변수 사용

최고고·2022년 12월 7일

PL/SQL

PL/SQL Procedual Language / SQL의 약어

SQL을 이용하는 절차형 프로그램 언어
SQL은 구조화된 조회 언어라는 의미로, 집합적 언어라서 절차적 프로그래밍은 지원되지 않지만 SQL을 이용하면서 절차적인 프로그래밍도 함께 필요한 경우가 있음
크게 Procedure, Function, Trigger (프로시져, 사용자함수, 트리거 ) 로 나뉨 -> 내부에서 다양한 SQL을 복합적으로 구성이 가능
유저 프로세스가 PL/SQL 블록을 보내고
서버 프로세서는 PL/SQL Engine에서 해당 블록을 받고
SQL과 Procedural를 나눠서 SQL은 SQL Statement Executer로 보냄
SQL의 단점을 해결해줌 (변수가없고, 한번에 하나의 명령문으로 트래픽이 증가하고, 제어문X, 예외처리가 없는 단점)
성능 향상
모듈식 프로그래밍 가능
이식성 굿
예외 처리 가능
패키지를 사용한 모듈화를 지원
패키지는 연관성을 가지는 타입, 상수, 변수 , 커서, 예외처리, 서브프로그램들을 역어서 하나의 응용 단위로 구성하는 것
블록단위 실행 BEGIN, END 사용 하며 마지막라인에 / 입력 하면 실행
행에 '/'가 있으면 종결된 것
객체 지향 프로그래밍 지원 - 객체 타입을 제공
변수, 상수 선언 가능 ---> DECLARE절에서만 사용가능하며 BEGIN 섹션에서 새 값 할당 가능
다양한 내장 API
조건문, LOOP문 등
커서 사용해 여러행 검색하고 처리가능
사용가능 SQL은 Query, DML, TCL
DDL,DCL은 동적SQL 이용할때만 가능 (동적SQL 컴파일시 SQL문확정 되지않을때, PL/SQL블록상 ALTER SYSTEM/SESSION명령어 실행해야할때 등 SQL사용시 동적으로 바뀔때가 발생하는데, 문자열로 저장해놓고 가져다쓰면 재사용성과 유지보수가 용이함)
원시동적 SQL을 사용하는 방식과 DBMS_SQL이란 시스템 패키지를 사용하는 방식이 있다!
PL/SQL의 SELECT문 :
SELECT의 결과를 PL/SQL Engine으로 보내게 되는데 이를 캐치하기 위한 변수를 DECLARE해야 하고, INTO절을 꼭 선언하여 넣을 변수를 꼭 표현해주어야 함. SELECT 문장은 반드시 한 개의 행이 검색되어야 한다.
검색되는 행이 없으면 문제가 발생한다.

기본 PL/SQL Block 구조

DECLARE (선언부)
변수/상수/커서 등 을 선언
BEGIN (실행부)
제어문, 반복문, 함수 정의 등 로직을 기술할수있는 부분
EXCEPTION (예외 처리부)
실행되는 중에 에러가 발생할수있는데 이러한 예외 사항이 발생 시 해결하기 위한 문장을 기술하는 부분
END (실행문 종료)

PL/SQL Block의 종류

익명 블록 : 이름이 없는 PL/SQL Block
이름 있는 블록 : DB의 객체로 저장되는 블록
프로시저 : 리턴 값을 하나 이상 가질 수 있는 프로그램
함수 : 리턴 값을 반드시 반환해야 하는 프로그램
패키지 : 하나 이상의 프로시저, 함수, 변수, 예외 등의 묶음
트리거 : 지정된 이벤트가 발생하면 자동으로 실행되는 PL/SQL 블록

PL/SQL 사용을 위한 기본 설정 및 확인 법

결과물 보여주는 명령어
SET SERVEROUTPUT ON;
PL/SQL은 결과물을 보여주지 않아서 결과물을 보고 싶다면 SERVEROUTPUT 설정 을 ON 으로 설정
에러 보여주는 명령어
SHOW ERRORS;
오라클에서 화면출력을 위해선 PUT_LINE이란 프로시저를 이용
DBMS_OUTPUT.PUT_LINE(출력할 내용)
이 외에 다양한 내장 모듈 있다.

실행순서

PL/SQL Block 실행
PL/SQL 엔진에서 PL/SQL 부분과 일반 SQL 부분이 분리
SQL 부분을 Database Server 가 처리
Database Server 에서 처리된 SQL 결과를 PL/SQL Engine 로 전달
PL/SQL Engine 에서 처리된 SQL 결과를 바탕으로 나머지 작업 수행

https://www.ibm.com/docs/ko/db2/11.1?topic=data-plsql-support
참고해보자~!

변수선언

선언부(DECLARE)에서 선언해야됨 변수명 다음 데이터타입 기술

문법?
identifier [CONSTANT] datatype [NOT NULL][:=|DEFAULT expression];

뭔말인가 싶어서 예시를 살펴봐야겠다

예)

DECLARE 변수이름 데이터타입
DECLARE NAME VARCHAR2(10)

DECLARE 변수이름 데이터타입 :=값;
DECLARE NAME VARCHAR2(10) :='고고';

DECLARE 변수이름 데이터타입 DEFAULT 기본값;
DECLARE NAME VARCHAR2(10) DEFAULT '고고';

변수를 여러개 선언

DECLARE 
	NAME VARECHAR2(10);
    AGE NUMBER(2);
    GENDER VARCHAR(10) DEFAULT '안알랴줌';

변수 선언 후 사용법

DECLARE NM VARCHAR2(10):='고고';
BEGIN
	DBMS_OUTPUT.PUT_LINE('고고'||NAME);
END

데이터타입 작성안하는 방법

%ROWTYPE, %TYPE

%ROWTYPE : 테이블이나 뷰 컬럼속성을 그대로 가져옴
변수명 테이블명%ROWTYPE

DECLARE NM EMP%ROWTYPE;
BEGIN
	SELECT * INTO DATA 
    FROM EMP
    WHERE EMPNO = '1234';
    DBMS_OUTPUT.PUT_LINE(DATA.ENM|| ',' ㅣㅣ DATA.ENO );
END

SELECT INTO 절 : 원본이 있지만 대상 테이블은 새로 생성하려고 할 때
EX SELECT * INTO COPY_TABLE FROM ORIGINAL_TABLE;
특정 컬럼만 가져올 때
SELECT * INTO COPY FROM(SELECT COL1,COL2,COL3 FROM ORIGINAL_TABLE) AS ORIGINAL;

그러면 INSERT INTO SELECT는?
원본, 대상 테이블 모두 존재
스키마가 둘다 동일해야함
INSERT INTO B_TABLE SELECT * FROM A_TABLE;
특정 컬럼 추출해서 맞춰줄수 있음~

신규 테이블을 만들땐?
CREATE TABLE NEW_TABLE
AS
SELECT * FROM OLD_TABLE;

%TYPE
컬럼속성 지정해 들고옴
변수명 테이블명.컬럼명%TYPE

DECLARE 
	V_EMPNM EMPLOYEE.EMPNM%TYPE;
    V_DNO EMPLOYEE.DNO%TYPE;
BEGIN
	SELECT EMPNM, DNO INTO V_EMPNM, V_DNO
    FROM EMPLOYEE
    WHERE EMPNO = '1234';
    DBMS_OUTPUT.PUT_LINE(V_EMPNM || ','|| V_DNO);
END