Database_80_PL/SQL_개요

-- PL/SQL 
    -- 개요
        -- 인사관리 > 트리거 를 배웁니다.
    -- 정의
        -- Oracle's Procedural Language Extension to SQL
        -- SQL 문장에서 변수 정의, 조건 처리( IF ), 반복 처리( LOOP, WHILE, FOR )를 지원합니다.
        -- 오라클 자체에 내장되어 있는 Procedure Language입니다.
        -- DECLARE 문을 사용하여 정의됩니다.
        -- 선언문의 사용은 선택 사항입니다.
        -- PL/SQL 문은 블록 구조로 되어 있습니다.
        -- PL/SQL 자신이 컴파일 엔진을 가지고 있습니다.
    -- 런타임 구조
        -- PL/SQL 블록은 클라이언트 툴 또는 Oracle Server 내에 있는 PL/SQL 엔진에 전달되어 처리됩니다.
        -- Oracle Server 내의 PL/SQL 엔진이 PL/SQL 블록을 처리합니다.
        -- PL/SQL 엔진은 SQL 문을 분리하여 하나씩 SQL 문 실행자로 전송합니다.
    -- 장점
        -- BLOCK 구조로 다수의 SQL 문을 한 번에 Oracle DB로 보내 처리하므로 수행 속도를 향상시킬 수 있습니다.
        -- 모든 요소는 하나 또는 두 개 이상의 블록으로 구성하여 모듈화가 가능합니다.
        -- Variable, Constant, Cursor, Exception 을 정의하고 SQL과 Procedural 문장에서 사용합니다.
        -- 여러 가지 데이터 형태의 변수를 선언합니다.
        -- 테이블의 데이터 구조와 데이터베이스의 컬럼에 준하여 동적으로 변수를 선언할 수 있습니다.
        -- Exception 처리 루틴을 이용하여 Oracle Server Error를 처리합니다.
        -- 사용자 정의 에러를 선언하고 Exception 처리 루틴으로 처리가 가능합니다.
        /*
        PL/SQL을 사용하지 않고 JAVA에서 처리할 수도 있습니다.
        -- 부하를 분산시키기 위해 PL/SQL을 사용하는 것이 좋습니다.
        */
        
    -- PL/SQL Block Structure
        -- 프로그램을 논리적인 블록으로 나누는 구조화된 블록 언어입니다.
        -- 사용하는 변수는 블록에 대해 논리적으로 선언할 수 있고 사용할 수 있습니다.
        -- 구성
            ----------------------------------------
            -- 선언부 [선택] : DECLARE
            -- 실행부 [필수] : BEGIN -- END
            -- 예외 처리부 [선택] : EXCEPTION
            ----------------------------------------
            -- Declarative Section_선언부
                -- 선언
                    -- 변수
                    -- 상수
                    -- CURSOR
                    -- USER_DEFINE Exception
            -- Executable Section_실행부
                -- SQL, 반복문, 조건문 실행
                -- BEGIN -- END
            -- Exception Handling Section_예외 처리
                -- 일반적으로 오류를 정의하고 처리하는 부분입니다.
        -- 작성 요령
            -- PL/SQL 블록 내에서는 한 문장이 종료될 대마다 세미콜론을 사용합니다.
            -- END 뒤에 세미콜론을 작성하여 하나의 블록이 종료됨을 명시합니다.
            -- PL/SQL 블록의 작성은 편집기를 통해 파일로 작성하거나 SQL 프롬프트에서 바로 작성합니다.
            -- DECLARE나 BEGIN이라는 키워드로 시작합니다.
            -- 주석
                -- 단일 행 : --
                -- 여러 행 : /*  */
            -- 행에 /가 있는 경우 PL/SQL 블록이 종료됩니다.
        -- 개발 환경
            -- Oracle SQL Developer
            -- Oracle SQL*Plus
        -- 프로그램 생성 방법
            -- 도구 생성자
                -- 익명 블록
                -- 응용 프로그램 프로시저/함수
                -- 응용 프로그램 패키지
                -- 응용 프로그램 트리거
                -- 객체 유형
            -- 데이터베이스 서버 생성자
                -- 익명 블록
                -- 내장 프로시저/함수
                -- 내장 패키지
                -- 데이터베이스 트리거
                -- 객체 유형
        -- 블록 유형
            -- Anonymous PL/SQL_익명 
                /*
                [ DECLARE ] -- 변수를 선언합니다.
                BEGIN
                    -- statements   -- SELECT  column  INTO 변수 FROM tables...
                [ EXCEPTION ]
                END;
                */
                -- '*.sql' 파일로 OS에 저장됩니다.
                -- 스크립트를 공유해야 사용할 수 있습니다.
                    -- 암호화가 되지 않습니다.
                -- 명명되지 않은 PL/SQL 블록입니다.
                -- 매번 컴파일합니다.
                -- 데이터베이스에 저장되지 않습니다.
                -- 다른 응용프로그램에서 호출할 수 없습니다.
                -- 값을 반환하지 않습니다.
                -- 파라미터를 사용할 수 없습니다.
            -- 서버 프로그램
                -- 프로시저
                    /*
                    PROCEDURE name
                    IS
                    BEGIN
                        -- statements
                    [ EXCEPTION ]
                    END;
                    */
                    -- EXECUTE 명령문으로 실행할 수 있습니다.
                -- 함수
                    /*
                    FUNCTION name
                    RETURN datatype
                    IS
                    BEGIN
                        -- statements
                        RETURN value;
                    [ EXCEPTION ]
                    END;
                    */
                -- 패키지로 묶어 사용할 수 있습니다.
                -- 명명된 PL/SQL 블록입니다.
                -- 한 번만 컴파일됩니다.
                -- 데이터베이스에 저장됩니다.
                -- 다른 응용 프로그램에서 명명된 이름으로 호출할 수 있습니다.
                -- 함수는 값을 반환합니다.
                -- 파라미터를 사용할 수 있습니다.
        
-- 익명 PL/SQL 블록 실행
    -- [ Run Script ]로 익명 블록을 실행합니다.
DECLARE
    v_fname VARCHAR2(20);
BEGIN  
    SELECT first_name INTO v_fname
    FROM employees
    WHERE employee_id=100;
END;
    /*
    PL/SQL 프로시저가 성공적으로 완료되었습니다.
    */

        -- PL/SQL 블록의 출력 활성화
            /*
            SET serveroutput ON
            BEGIN
            DBMS_OUTPUT.PUT_LINE( 'Hello World!!' );
            END;
                -- SET serveroutput ON :  PL/SQL 블록 출력을 활성화합니다.
                -- DBMS_OUTPUT.PUT_LINE( statement ) : 문장을 출력합니다.
            */
SET serveroutput ON
BEGIN
DBMS_OUTPUT.PUT_LINE( 'Hello World!!' );
END;
    /*
    Hello World!!
    PL/SQL 프로시저가 성공적으로 완료되었습니다.
    */

SET SERVEROUTPUT ON
DECLARE
    v_fname VARCHAR2(20);
BEGIN
    SELECT first_name INTO v_fname
    FROM employees
    WHERE employee_id=100;
    DBMS_OUTPUT.PUT_LINE('The First Name of the Employee : ' || v_fname );
END;
    /*
    The First Name of the Employee : Steven
    PL/SQL 프로시저가 성공적으로 완료되었습니다.
    */

---------------------------------------------------------------------------------------------------------
-- 치환 변수
    -- 단일 치환 변수 &
        -- &column 
            -- 실행 시 사용자가 값을 입력할 수 있습니다.
        -- 값을 임시로 저장할 수 있습니다.
        -- SQL, PL/SQL에서 사용할 수 있습니다.
        -- 문자, 날짜 값을 지정하는 경우 작은 따옴표로 묶어 작성합니다.
            -- 따옴표로 묶지 않는 경우 모두 대문자로 변환됩니다.
        -- WHERE 절, 열 이름, 표현식, 텍스트를 치환할 수 있습니다.
    -- 이중 치환 변수 &&
        -- &&column
            -- 변수 값을 재사용하는 경우 사용합니다.
            -- 값을 한 번만 입력해도 됩니다.
            -- 해제할 때까지 값이 유지됩니다.
        -- 이중 치환 변수 해제
            -- UNDEFINE column
    
    -- 단일 치환 변수
-- WHERE 절에서 문자열 대신 사용할 수 있습니다.
SELECT employee_id, last_name, salary, hire_date, job_id
FROM employees
WHERE last_name=INITCAP('&emp_name');
    -- 변수를 검색해야 합니다.
    -- 검색하는 모든 문자가 대문자로 변환하는 것을 방지하기 위해 ' ' 로 작성합니다. 
    -- 대소문자를 구분하지 않기 위해 INITCAP을 작성합니다.

-- WHERE 절에서 상수 대신 사용할 수 있습니다.
UPDATE employees
SET salary=salary*1.05
WHERE employee_id=&empno;   
    /*
    -- 103 번 입력
        -----<과정>------------------------
        이전:UPDATE employees
        SET salary=salary*1.05
        WHERE employee_id=&empno
        
        신규:UPDATE employees
        SET salary=salary*1.05
        WHERE employee_id=103
        -------------------------------------
    1 행 이(가) 업데이트되었습니다.
    */
    
-- 조회하는 열 이름을 치환할 수 있습니다.
SELECT employee_id, last_name, salary, hire_date, &column_name
FROM employees;

-- 두 개를 작성하는 경우 각각 값을 지정해야 합니다.
SELECT employee_id, last_name, salary, hire_date, &column_name
FROM employees
ORDER BY &column_name;

    -- 이중 치환 변수
-- 이중 치환 변수를 사용하면 한 번만 값을 지정해도 됩니다.
SELECT employee_id, last_name, salary, hire_date, &&column_name
FROM employees
ORDER BY &column_name;

-- 이중 치환 변수를 조회합니다.
DEFINE column_name;
    /*
    DEFINE COLUMN_NAME     = "department_id" (CHAR)
    */

-- 이중 치환 변수를 해제합니다.
UNDEFINE column_name;
    /*
    UNDEFINE column_name;
    */
    
-- 이중 치환 변수를 조회합니다.
DEFINE column_name;
    /*
    SP2-0135: column_name 기호가 정의되지 않았습니다.
    */
    
        --<test01.sql> >> C:ORACLEXE ---------------------------------------
        SELECT employee_id, last_name, salary, hire_date, &&column_name
        FROM employees
        ORDER BY &column_name;
            >>> email 입력
        -- 이중 치환 변수를 해제합니다.
        UNDEFINE column_name;
        ------------------------------------------------------------------------
@C:\ORACLEXE\test01.sql

    
DEFINE deptno=60
SELECT * FROM departments
WHERE department_id=&deptno;     >>>  60 입력

    
SELECT last_name, department_id, manager_id FROM employees
WHERE department_id=&deptno;     >>>  60 입력

    
    -- 익명 PL/SQL 저장, 실행하기
        --<test02.sql> >> C:ORACLEXE ---------------------------------------
        DEFINE deptno=60
        SELECT * FROM departments
        WHERE department_id=&deptno;
            
        SELECT last_name, department_id, manager_id FROM employees
        WHERE department_id=&deptno;
        
        UNDEFINE deptno;
        ------------------------------------------------------------------------
@C:\ORACLEXE\test02.sql

    
        --<test02.sql> >> C:ORACLEXE ---------------------------------------
        SET verify OFF
        DEFINE deptno=60
        SELECT * FROM departments
        WHERE department_id=&deptno;
            
        SELECT last_name, department_id, manager_id FROM employees
        WHERE department_id=&deptno;

        UNDEFINE deptno;
        ------------------------------------------------------------------------
@C:\ORACLEXE\test02.sql

        --<test03.sql> >> C:ORACLEXE ---------------------------------------
        DECLARE 
            v_fname VARCHAR2(20);
        BEGIN
            SELECT first_name INTO v_fname
            FROM employees
            WHERE employee_id=100;
            DBMS_OUTPUT.PUT_LINE('The First Name of the Employee : ' || v_fname );
        END;
        ------------------------------------------------------------------------
@C:\ORACLEXE\test03.sql
    /*
    The First Name of the Employee : Steven
    PL/SQL 프로시저가 성공적으로 완료되었습니다.
    */
    
SELECT table_name, constraint_name, constraint_type, status
FROM user_constraints
WHERE table_name=UPPER('&table_name');    >>> employees 검색

    
SELECT table_name, tablespace_name, read_only
FROM user_tables
WHERE table_name=UPPER('&table_name');

    

        --<test04.sql> >> C:ORACLEXE ---------------------------------------
        DEFINE table_name=&&table_name
        SELECT table_name, constraint_name, constraint_type, status
        FROM user_constraints
        WHERE table_name=UPPER('&table_name');
        
        SELECT table_name, tablespace_name, read_only
        FROM user_tables
        WHERE table_name=UPPER('&table_name');
            
        UNDEFINE table_name
        ------------------------------------------------------------------------
@C:\ORACLEXE\test04.sql     >>> departments 입력

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