[Oracle] PL/SQL

▶ PL/SQL (Procedural Language SQL)

SQL을 확장한 절차적 언어(Procedural Language)이다

오라클에서 제공하는 프로그래밍 언어이다

PL/SQL 과 함께 활용하여 효과적으로 데이터 다룰수있다
일반적인 절차적 프로그래밍 언어 기능을 가진다 : 변수, 제어문.. 등..

관계형 데이터베이스에서 사용되는 Oracle의 표준 데이터 엑세스 언어로, 프로시저 생성자를 SQL과 완벽하게 통합한다

 

 

▷ 기본 특징
· 블록 단위의 실행을 제공한다. 이를 위해 BEGIN과 END;를 사용한다. 그리고 마지막 라인에 / 를 입력하면 해당 블록이 실행된다.
· 변수, 상수 등을 선언하여 SQL과 절차형 언어에서 사용한다
· 변수의 선언은 DECLARE절에서만 가능하다. 그리고 BEGIN 섹션에서 새 값이 할당될 수 있다.
· IF문을 사용하여 조건에 따라 문장들을 분기 가능하다
· LOOP문을 사용하여 일련의 문장을 반복 가능하다
· 커서를 사용하여 여러 행을 검색 및 처리하다
· [ PL/SQL에서 사용 가능한 SQL은 Query, DML, TCL이다. ]   

DDL (CREATE, DROP, ALTER, TRUNCATE …), DCL (GRANT, REVOKE) 명령어는 동적 SQL을 이용할 때만 사용 가능하다.
· [ PL/SQL의 SELECT문은 해당 SELECT의 결과를 PL/SQL Engine으로 보낸다. ]

 

이를 캐치하기 위한 변수를 DECLARE해야 하고, INTO절을 꼭 선언하여 넣을 변수를 꼭 표현해주어야 SELECT 문장은 반드시 한 개의 행이 검색된다.   그리고 이를 INTO절을 꼭 사용해야한다. 또한 검색되는 행이 없으면 문제가 발생한다.

 

 

▷ 기본 PL/SQL Block 구조

영역	설명	옵션/필수
DECLARE (선언부)	PL/SQL에서 사용하는 모든 변수나 상수를 선언하는 부분으로서 DECLARE로 시작 => 변수/상수/커서 등 을 선언	옵션
BEGIN (실행부)	절차적 형식으로 SQL문을 실행할수있도록 절차적 언어의 요소인 제어문, 반복문, 함수 정의 등 로직을 기술할수있는 부분이며 BEGIN으로 시작	필수
EXCEPTION (예외 처리부)	PL/SQL문이 실행되는 중에 에러가 발생할수있는데 이를 예외 사항이라고 한다. 이러한 예외 사항이 발생했을때 이를 해결하기 위한 문장을 기술할수있는 부분	옵션
END (실행문 종료)		필수

 

 

▷ PL/SQL 프로그램의 작성 요령

·  PL/SQL 블록 내에서는 한 문장이 종료할 때 마다 세미콜론( ; )을 사용하여 한 문장이 끝났다는 것을 명시한다

· END 뒤에 세미콜론 ( ; ) 을 사용하여 하나의 블록이 끝났다는 것을 명시한다
· 단일행주석은 -- 이고 여러 행 주석은 /* */ 이다

· 쿼리문을 수행하기 위해서 ' / '가 반드시 입력되어야 하며, PL/SQL 블록은 행에 ' / '가 있으면 종결된것으로 간주한다

 

▷ 화면 출력

오라클에서 화면 출력을 위해서는 PUT_LINE이란 프로시저를 이용한다

DBMS_OUTPUT.PUT_LINE (출력할 내용) 같이 사용한다

** 위 프로시저를 사용하여 출력되는 내용을 화면에 보여주기 위해서는 환경 변수 SERVEROUTPUT (디폴트값이 OFF이므로) ON으로 변경한다

 

- 화면 출력 기능 활성화

SET SERVEROUTPUT ON;

 

▷ &를 이용한 치환변수의 값 변화 표시를 끈다 off (기본값이 ON)

SET verify off

old  10: HERE profno = '&profno';
new  10: WHERE profno = '1003'; 안뜬다

 

 

- 화면에 출력하기 (실행은 console에서 할 것)

ctrl + shift + o 출력은 가능하지만 입력은 안된다

console에서 출력할 경우네는 끝에 / (슬래시) 를 해야한다

BEGIN 
	DBMS_OUTPUT.PUT_LINE('hello oracle')
END;

 

▷ oracle에서 제공하는 단일 함수를 쓸 수 있다

/ 의 의미 : 마지막으로 작성된 PL/SQL 블록 실행 한 것이다 반복 실행 가능) 

DECLARE 
	v_age INTEGER;
    v_name VARCHAR(10);
BEGIN
	SELECT 10 INTO v_age FROM dual; -- 변수에 값 대입
    SELECT 'hong' INTO v_name FROM dual; 
	DBMS_OUTPUT.PUT_LINE(v_name || ' ' || TO_CHAR(v_age));
END;
/

DECLARE
	vno NUMBER(4);
    vname VARCHAR2(10);
BEGIN
	SELECT empno, ename INTO vno, vname -- 테이블에 담겼던 내용이 변수에 담겨져 출력된다
    FROM t_emp
    WHERE empno = 7900;
    DBMS_OUTPUT.PUT_LINE(vno || ' ' || vname);
END;
/

 

▷ 기본 규칙

문장은 여러 줄 겹칠 수 있으나, 키워드는 분리가 불가하다
식별자는 " ~ " ,  문자, 날짜 리터럴은 ' ~ ' 로 나타낸다

일반적인 오라클 함수 (단일행함수)는 블럭내에서 사용 가능하나, 그룹함수, DECODE 함수는 SQL에서만 사용 가능하다

 

▷ 변수 Type을 선언할때 명시적으로 작성하지 않고 사용하는 방법도 있다.

%ROWTYPE - 해당 테이블이나 뷰의 컬럼 속성을 그대로 들고 오는 형태이다.  - 사용방법 : 변수명 테이블이름%ROWTYPE

%TYPE - 해당 테이블의 컬럼 속성을 지정하여 그대로 들고 오는 형태이다. - 사용방법 : 변수명 테이블이름.컬럼명%TYPE

DECLARE
	v_profno f_professor.profno%TYPE; -- column 속성을 지정해서 들고 오기
    	v_pay f_professor.pay%TYPE;
BEGIN
	SELECT profno, pay INTO v_profno, v_pay
	FROM T_PROFESSOR 
	WHERE profno = 1001;
	DBMS_OUTPUT.PUT_LINE(v_profno || '번 교수의 급여는' || v_pay || '입니다');
END;
/

 

▷ 입력하는 방법

& 붙인걸 문자 그대로 치환해버린다

where 절에서 empno = &empno 값에 맞는 조건을 뽑아 출력하기

DECLARE
	v_empno t_emp2.empno%TYPE;
	v_name t_emp2.name%TYPE;
	v_birth t_emp2.birthday%TYPE;
BEGIN
	SELECT empno, name, BIRTHDAY 
	INTO v_empno, v_name, v_birth
	FROM t_emp2
	WHERE empno = '&empno';  -- 사용자에게 입력받아서 변수에 할당. &기호 사용

	DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_name || ' ' || v_birth);
END;
/

DECLARE
	v_profno t_professor.profno%TYPE;
	v_name t_professor.name%TYPE;
	v_deptno t_professor.deptno%TYPE;
	v_hiredate t_professor.hiredate%TYPE;    
BEGIN
	SELECT profno, name, deptno, hirdate INTO v_profno, v_name, v_deptno, hiredate
    	FROM t_professor
    	WHERE profno = '&profno'
    
    	DBMS_OUTPUT.PPUT_LINE(v_profno || ' ' || v_name || ' ' || v_deptno || ' ' || v_hiredate);
END;

 

 

▷ PL/SQL에서 DML 사용하기

INSERT / UPDATE / DELETE

1. INSERT 예제

   테이블 생성 + 시퀀스 생성

CREATE TABLE p1_test(
	no NUMBER;
    name VARCHAR2(10);
);

CREATE SEQUENCE p1_seq;

BEGIN 
	INSERT INTO P1_TEST VALUES (p1_seq.nextval, 'AAA');
END;
/

 

 

▷ 변수 대입 방법

- 명시적인 값 대입
변수값을 저장하기 위해서는 := 를 사용한다. - := 의 좌측에는 변수를 , 우측에는 값을 기술

 identifier := expression;

 

2. INSERT 예제

CREATE TABLE p1_test2(
    no NUMBER,
    name varchar2(10),
    addr varchar2(10)
);

DECLARE
	v_no NUMBER := '&no';
	v_name varchar2(10) := '&name';
	v_addr varchar2(10) := '&addr';
BEGIN
	INSERT INTO p1_test2 VALUES(v_no, v_name, v_addr);
END;
/

 

▷ DECLARE 안의 DECLARE 가능 (블록 안의 블록)

DECLARE
	v_first VARCHAR2(5) := 'Outer'
BEGIN
	DECLARE 
    	v_second VARCHAR2(5) := 'inner';
    BEGIN
    	DBMS_OUTPUT.PUT_LINE(V_FIRST);
    	DBMS_OUTPUT.PUT_LINE(v_second);
    END;
    	DBMS_OUTPUT.PUT_LINE(V_FIRST);
    	DBMS_OUTPUT.PUT_LINE(v_second); -- 내부의 블록 변수 사용할 수 없음 error
END;
/