15. Oracle PL/SQL

14_PL.sql

0.01MB

PL/SQL(Procedural Language/SQL)이란?
오라클에서 제공하는 프로그래밍 언어
일반 프로그래밍 언어적 요소를 가지고 있고, 데이터베이스 업무를 처리하기 위한 최적화 언어 입니다.
    
기본구조

선언부(Declare)          : 모든 변수나 상수를 선언하는 부분입니다. 
실행부(Executable)     : 제어문 반복문 함수정의 등을 작성합니다. (BEGIN ~ END) 사이에 담기게 됩니다. 
예외처리부(Exception) : 실행 중 에러 발생 시 해결을 위한 명령을 작성합니다. 
    

PL/SQL 의 특성

PL은 블록 단위로 작업을 처리하며 익명 블록 (일회성) , 저장 블록(서버에 저장 후 주기적으로 호출이 있습니다. )

DECLARE , BEGIN , EXCEPTION 은 세미콜론을 붙이지 않습니다.
PL/SQL 뒤에 " / " 를 넣음으로써 프로시저 생성 후 실행 시켜 줍니다. 

할당 연산자 = 이 아닌 := 를 사용 합니다.  

---

 SQL을 이용하지 않는 익명 PL 

SET serveroutput on;        -- dbms_output.put_line 입력 시 켤과값을 SQLDeveloper 에서 출력시켜줍니다. 

DECLARE 
    cnt integer; 
BEGIN 
    cnt := cnt+1;   -- := 는 1씩 증가하겠습니다.( 할당 연산자 = 를 사용하지않고 := 를 사용합니다) 
    IF cnt is null THEN 
        dbms_output.put_line('결과: cnt는 Null입니다'); -- 콘솔창에 출력하게 해 줍니다.   
    END IF; 
END; 
/ 

 

SQL을 이용한 익명 PL(SQL 실행자 -> PL 실행자로 데이터를 전달합니다.)

DECLARE 
    empNo number(20); 
    empName varchar2(10); 
BEGIN 
    SELECT employee_id, first_name into empNo, empName      --employee_id,first_name 을 선언한 empNo, empName에 담아줍니다. 
    FROM employees 
    WHERE employee_id = 124; 
     
    dbms_output.put_line(empNo||' '||empName); 
END; 
/ 

PL/SQL 변수 생성 규칙
1.  반드시 문자로 시작해야 합니다.
2.  문자나 숫자, 특수문자를 포함 할 수 있습니다.
3.  변수명은 30Bytes 이하만 가능 합니다.
4.  예약어를 사용할 수 없습니다.
    
-   변수는 선언부(Declare)에서 선언 된 후, 값을 초기화 할 수 있습니다.
-   실행부에서 실행 될 경우, 값이 할당 됩니다.
-   서브프로그램의 파라메터 및 출력 결과를 저장하기도 합니다.
    
PL/SQL 변수의 데이터 타입
char:              고정 길이 문자를 저장, 기본 생성시 1byte 이며 최대 32,767bytes 까지 가능 합니다.
varchar2:        가변 길이 문자를 저장, 기본값은 없습니다. 최대 32,767bytes 까지 저장 가능합니다.
number :        전체자릿수 혹은 소수점이하 자리 수를 저장, 전체 자릿수 범위는 1~38까지, 소수점 자리수는 -84~127까지 가능합니다. 
binary_double: 부동 소수점 수를 저장합니다. 9bytes가 필요합니다.
date:              날짜 및 시간을 초까지 저장합니다. 범위는 4712B.C ~ 9999A.D 입니다.
timestamp:      date타입의 확장판이며 연도,월,일,시,분,초 및 소수로 표시됩니다. 범위는 0~9까지 정수를 사용하며 기본값은 6 입니다.
                 
PL/SQL 참조 변수
테이블명.필드명%Type

empNo employees.employee_id%TYPE    --empNo의 데이터 타입은 employee_id의 변수 타입을 가져옵니다. 

테이블명%RowType

empRow employees%ROWTYPE    employees테이블의 모든 컬럼을 한번에 저장하기 위한 변수선언하기 위한 변수입니다

 

%Type을 사용한 PL

DECLARE 
    empNo employees_1.employee_id%TYPE; 
    empSalary employees_1.salary%TYPE; 
BEGIN 
    SELECT employee_id, salary INTO empNo , empSalary 
    FROM employees_1 
    WHERE department_id = 10; 
     
    dbms_output.put_line(empNo||' '||empSalary); 
END; 
/ 

%RowType을 사용한 PL

DECLARE 
    empRow employees_1%ROWTYPE; 
BEGIN 
    SELECT * INTO empRow 
    FROM employees_1 
    WHERE employee_id = 100; 
     
    dbms_output.put_line(empRow.employee_id||' '||empRow.salary||' '||empRow.department_id); 
END; 
/ 

-----------------------------------------------------------------------

DECLARE 
    allRec row_test%ROWTYPE; 
BEGIN 
    SELECT * INTO allRec 
    FROM row_test 
    WHERE no = 3; 
     
    INSERT INTO row_test2 VALUES allRec; 
END; 
/ 

-----------------------------------------------------------------------

DECLARE 
    allRec row_test%ROWTYPE; 
BEGIN 
    SELECT * INTO allRec 
    FROM row_test 
    WHERE no = 3; 
     
    allRec.name :='김말자'; 
    
    UPDATE row_test2  
    SET row=allRec 
    WHERE no = 3; 
END; 
/ 

사용자로부터 숫자를 입력받아 연산을 하는 PL/SQL [ 치환연산자 "&" 를 사용합니다 ]

DECLARE 
    no1 number:=&no1; 
    no2 number:=&no2; 
    numSum number; 
BEGIN 
    numSum := no1+no2; 
    dbms_output.put_line('no1:'||no1||',no2:'||no2||',합:'||numSum||'입니다'); 
END; 
/ 

복합변수

여러 변수들을 선언 할 수 있습니다. 식별자를 선언하기 전까지 메모리에 등록 되지 않습니다.

 

복합변수의 구조

TYPE 타입명 IS RECORD( );

식별자 타입명

DECLARE 
    TYPE emp_rec is record 
    (
    	emp_id employees.employee_id%TYPE, 
    	emp_name employees.first_name%TYPE, 
    	emp_job employees.job_id%TYPE 
    ); 
     
    rec1 emp_rec; 
    
BEGIN 
    SELECT employee_id, first_name, job_id 
    INTO rec1 
    FROM employees 
    WHERE department_id = 10; 
     
    dbms_output.put_line('사번 이름 업무번호'); 
    dbms_output.put_line(rec1.emp_id||' '||rec1.emp_name||' '||rec1.emp_job); 
END; 
/ 

-----------------------------------------------------------------------

DECLARE 
    TYPE emp_rec2 is record 
    (
    	emp_id employees.employee_id%TYPE, 
    	emp_name employees.last_name%TYPE, 
    	emp_email employees.email%TYPE, 
    	emp_salary employees.salary%TYPE 
    ); 
     
    rec1 emp_rec2; 
     
    inEmpId employees.employee_id%TYPE := '&empId'; 
    
BEGIN 
    SELECT employee_id, last_name, nvl(email,'없음'), salary 
    INTO rec1 
    FROM employees 
    WHERE employee_id = inEmpId; 
     
    dbms_output.put_line('사번'||rec1.emp_id); 
    dbms_output.put_line('이름'||rec1.emp_name); 
    dbms_output.put_line('이메일'||rec1.emp_email); 
    dbms_output.put_line('급여'||rec1.emp_salary); 
END; 
/