Database_87_서브 프로그램

-- 서브 프로그램
    -- 정의
        -- 서브 프로그램 : 내장 프로시저 및 함수
        -- 명명된 PL/SQL 블록입니다.
        -- 익명 블록과 유사한 블록 구조를 가집니다.
            -- 선택적 선언 섹션
                -- DECLARE 키워드를 사용하지 않습니다.
            -- 필수 실행 섹션
            -- 예외 처리 옵션 섹션
        -- 명시적으로 실행해야 합니다.
    -- 익명 블록과 서브 프로그램의 차이
        -- 익명 블록
            -- 명명되지 않은 PL/SQL 블록입니다.
            -- 매번 컴파일됩니다.
            -- 데이터베이스에 저장되지 않습니다.
            -- 다른 응용 프로그램에서 호출할 수 없습니다.
            -- 파라미터를 사용할 수 없습니다.
            -- 값을 반환해야 하는 함수를 정의할 수 없습니다.
        -- 서브 프로그램
            -- 명명된 PL/SQL 블록입니다.
            -- 한 번만 컴파일됩니다.
            -- 데이터베이스에 저장됩니다.
            -- 명명되었으므로 다른 응용 프로그램에서 호출할 수 있습니다.
            -- 파라미터를 사용할 수 있습니다.
            -- 함수를 정의하여 값을 반환할 수 있습니다.
    -- 장점
        -- 유지 관리가 용이합니다.
        -- 코드의 명확성이 향상됩니다.
        -- 데이터 보안, 무결성이 향상됩니다.
        -- 성능이 향상됩니다.
    
    -- 프로시저
        -- 특정 작업을 수행하는 일종의 서브 프로그램입니다.
        -- 데이터베이스에 스키마 객체로 저장할 수 있습니다.
        -- 재사용성과 유지 관리 용이성이 증대됩니다.
        -- 과정
            -- 프로시저 생성/편집 > 컴파일러 경고/오류 > 컴파일러 경고/오류보기
                -- 컴파일러 경고/오류 없는 경우 프로시저 실행
        -- 생성
            /*
            CREATE [ OR REPLACE ] PROCEDURE procedure_name
            [ (parameter1 [mode] datatype1,
                parameter2 [mode] datatype2, ...) ]
            IS | AS
                [ local_variable_declarations; ... ]    -- PL/SQL 블록입니다.
            BEGIN
                -- actions;
            END [ procedure_name ];
            
                -- 프로시저 폴더 우클릭 > 새 프로시저 > 설정 후 확인 시 생성할 수 있습니다.
            */
        -- 컴파일 오류 해결
            -- 프로시저 편집
            -- 오류 수정
            -- 재컴파일
        -- 파라미터 
            -- PL/SQL 헤더에서 서브 프로그램 이름 뒤에 선언합니다.
            -- 호출 환경과 서브 프로그램 간에 데이터를 전달합니다.
            -- 로컬 변수처럼 사용되지만 파라미터 전달 모드에 따라 달라집니다.
            -- 파라미터 전달 모드
                -- IN (기본값)
                    -- 처리할 값을 서브 프로그램에 제공합니다.
                    -- 값이 서브 프로그램에 전달됩니다.
                    -- 형식 파라미터가 상수로 동작합니다.
                    -- 실제 파라미터가 리터럴, 표현식, 상수, 초기화된 변수가 될 수 있습니다.
                    -- 기본값을 할당할 수 있습니다.
                -- OUT
                    -- 호출자에게 값을 반환합니다.
                    -- 값이 호출하는 환경으로 반환됩니다.
                    -- 초기화되지 않은 변수입니다.
                    -- 변수만 가능합니다.
                    -- 기본값을 할당할 수 없습니다.
                -- IN OUT
                    -- 입력 값을 제공합니다.
                    -- 수정된 값으로 반환될 수도 있습니다.
                    -- 서브 프로그램으로 전달되는 값, 호출 환경으로 반환되는 값입니다.
                    -- 초기화된 변수입니다.
                    -- 변수만 가능합니다.
                    -- 기본값을 할당할 수 없습니다.
            -- 형식 파라미터 : 서브 프로그램 명세의 파라미터 리스트에 선언된 로컬 변수입니다.
            -- 실제 파라미터 : 호룿 서브 프로그램의 파라미터 리스트에 사용되는 리터럴 값, 변수, 표현식
                /*
                -- Procedure definition, Formal_parameters
                CREATE PROCEDURE raise_sal(p_id NUMBER, p_sal NUMBER) IS
                BEGIN
                . . .
                END raise_sal;
                    -- 파라미터 모드를 작성하지 않았습니다.
                        -- IN으로 생각합니다.
                -- Procedure calling, Actual parameters (arguments)
                v_emp_id := 100;
                raise_sal(v_emp_id, 2000)        
                    -- 프로시저를 실행하기 위해서는 값을 입력해야 합니다.
                */
        -- 프로시저 파라미터 모드
            -- 파라미터 이름과 데이터 유형 사이인 형식 파라미터 선언 부분에 지정됩니다.
            /*
            CREATE PROCEDURE proc_name(param_name [mode] datatype)
            */
    
    -- IN 파라미터
-- SQL Developer에서 이 명령문을 실행하면 RAISE_SALARY 프로시저가 작성됩니다.
    -- 호출 시 RAISE_SALARY가 사원 ID에 해당하는 파라미터를 받아 10% 인상된 급여로 사원의 레코드를 갱신합니다.
CREATE OR REPLACE PROCEDURE raise_salary (
                                p_id IN employees.employee_id%TYPE,
                                p_percent IN NUMBER)
IS 
BEGIN
    UPDATE employees
    SET salary = salary * (1 + p_percent/100)
    WHERE employee_id = p_id;
END raise_salary;
/
    /*
    Procedure RAISE_SALARY이(가) 컴파일되었습니다.
        -- 실행된 것이 아닙니다.
        -- 데이터베이스에 저장합니다.
        -- 프로시저 폴더에서 확인 가능합니다.
    */

-- 변경할 데이터를 확인합니다.
SELECT salary FROM employees
WHERE employee_id=178;

    
-- 178번 사원의 급여를 10프로 인상합니다.
EXECUTE raise_salary(178, 10);
    -- = EXE raise_salary(176, 10)
    /*
    PL/SQL 프로시저가 성공적으로 완료되었습니다.
    */

-- 변경된 데이터를 확인합니다.
SELECT salary FROM employees
WHERE employee_id=178;

-- 원상태로 되돌립니다.
ROLLBACK;
    /*
    롤백 완료.    
    */

    -- OUT 파라미터
-- QUERY_EMP 프로시저는 사원 ID 값을 받아 해당 사원의 이름과 급여를 검색하여 두 개의 OUT 파라미터에 넣는 프로시저입니다.
CREATE OR REPLACE PROCEDURE query_emp (
                    p_id IN employees.employee_id%TYPE,    -- 변수 파라미터입니다.
                    p_name OUT employees.last_name%TYPE,
                    p_salary OUT employees.salary%TYPE
                    ) 
IS
BEGIN
    SELECT last_name, salary INTO p_name, p_salary  -- OUT 파라미터를 사용합니다.
    FROM employees
    WHERE employee_id = p_id;   -- IN 파라미터를 사용합니다.
END query_emp;
/
    /*
    Procedure QUERY_EMP이(가) 컴파일되었습니다.
    */
    
-- 익명 블록을 생성해야 합니다.
SET SERVEROUTPUT ON
DECLARE
    v_emp_name employees.last_name%TYPE;
    v_emp_sal employees.salary%TYPE;
BEGIN
    query_emp(144, v_emp_name, v_emp_sal); 
        --프로시저 실행 후 받을 변수 지정
    DBMS_OUTPUT.PUT_LINE(v_emp_name||' earns '|| to_char(v_emp_sal, '$999,999')); 
        --DBMS_OUTPUT.PUT_LINE 서브 루틴 사용
END;
/
    /*
    Vargas earns    $2,750

    PL/SQL 프로시저가 성공적으로 완료되었습니다.
    */
    
    -- IN OUT 파라미터
CREATE OR REPLACE PROCEDURE format_phone (
                                    p_phone_no IN OUT VARCHAR2 ) 
IS
BEGIN
    p_phone_no := '(' || SUBSTR(p_phone_no,1,3) || ') ' 
                    || SUBSTR(p_phone_no,4,3) || '-' || SUBSTR(p_phone_no,7);
END format_phone;
/
    /*
    Procedure FORMAT_PHONE이(가) 컴파일되었습니다.
    */

-- 출력합니다.
SET SERVEROUTPUT ON
DECLARE
    v_phone_no VARCHAR2(15) := '8006330575';
BEGIN
    format_phone(v_phone_no); 
    DBMS_OUTPUT.PUT_LINE(v_phone_no);
END;
/
    /*
    (800) 633-0575
    
    PL/SQL 프로시저가 성공적으로 완료되었습니다.
    */


        -- 실제 파라미터 전달 시 사용 가능한 표기법
            -- 위치 지정 방식
                -- 실제 파라미터를 형식 파라미터와 동일한 순서로 나열합니다.
            -- 이름 지정 방식 
                -- 실제 파라미터를 임의의 순서로 나열합니다.
                -- 연관 연산자를 사용하여 명명된 형식 파라미터를 실제 파라미터와 연관시킵니다.
            -- 혼합 방식
                -- 일부 실제 파라미터는 위치로 나열하고 나머지는 이름으로 나열합니다.
CREATE OR REPLACE PROCEDURE add_dept(
    p_deptid IN departments.department_id%TYPE, 
    p_name IN departments.department_name%TYPE,
    p_loc IN departments.location_id%TYPE) 
IS
BEGIN
    INSERT INTO departments (department_id, department_name,location_id)
    VALUES (p_deptid, p_name, p_loc);
END add_dept;
/
    /*
    Procedure ADD_DEPT이(가) 컴파일되었습니다.
    */
    
---- Passing parameters using the positional notation.
EXECUTE add_dept (270,'TRAINING', 2500);
    /*
    PL/SQL 프로시저가 성공적으로 완료되었습니다.
    */

-- Passing parameters using the named notation.
EXECUTE add_dept (p_loc=>1700, p_deptid=>360, p_name=>'EDUCATION');
    -- 이름 지정 방식
    /*
    PL/SQL 프로시저가 성공적으로 완료되었습니다.
    */
    
SELECT * FROM departments;

-- 되돌립니다.
ROLLBACK;
    /*
    롤백 완료.
    */

        -- DEFAULT 옵션
            -- IN 파라미터에 대한 기본값을 정의하여 초기화할 수 있습니다.
            -- 위치 지정 및 이름 지정 표기법 파라미터 전달 구문을 함께 사용하여 유연한 작업을 지원합니다.
            -- OUT, IN OUT 파라미터에는 기본값을 사용할 수 없습니다.

CREATE OR REPLACE PROCEDURE add_dept(
p_deptid IN departments.department_id%TYPE,
p_name departments.department_name%TYPE:='Unknown',
p_loc departments.location_id%TYPE DEFAULT 1700)
IS
BEGIN
INSERT INTO departments (department_id, department_name, 
location_id)
VALUES (p_deptid, p_name, p_loc);
END add_dept;
/
    /*
    Procedure ADD_DEPT이(가) 컴파일되었습니다.
    */
    
EXECUTE add_dept(p_deptid => 280);
    /*
    PL/SQL 프로시저가 성공적으로 완료되었습니다.
    */
    
EXECUTE add_dept (290, 'ADVERTISING', p_loc => 1500);
    /*
    PL/SQL 프로시저가 성공적으로 완료되었습니다.
    */
    
EXECUTE add_dept (300, p_loc => 1700);
    /*
    PL/SQL 프로시저가 성공적으로 완료되었습니다.
    */
    
SELECT * FROM departments;

-- 되돌립니다.
ROLLBACK;
    /*
    롤백 완료.
    */



    -- 프로시저 정보 보기
        -- USER_OBJECTS
-- 프로시저 정보를 조회합니다.
SELECT object_name, object_type, status
FROM user_objects
WHERE object_type = 'PROCEDURE';

-- 테이블의 열 이름을 변경합니다.
ALTER TABLE departments
RENAME column location_id TO loc_no;
    /*
    Table DEPARTMENTS이(가) 변경되었습니다.
    */

-- 변경 사항을 조회합니다.
SELECT object_name, object_type, status
FROM user_objects
WHERE object_type = 'PROCEDURE';

 
-- 원래대로 변경합니다.
ALTER TABLE departments
RENAME column loc_no TO location_id;
    /*
    Table DEPARTMENTS이(가) 변경되었습니다.
    */   

-- 프로시저 정보를 조회합니다.
SELECT object_name, object_type, status
FROM user_objects
WHERE object_type = 'PROCEDURE';
        -- 변경되지 않았습니다.

-- 접근할 수 있는지 실행합니다.
EXECUTE add_dept (300, 'TRAINING', 2500);
    /*
    PL/SQL 프로시저가 성공적으로 완료되었습니다.
    */
    
-- 프로시저 정보를 조회합니다.
SELECT object_name, object_type, status
FROM user_objects
WHERE object_type = 'PROCEDURE';
        -- VALID로 변경된 것을 확인할 수 있습니다.

    
        -- USER_SOURCE
-- 정보를 조회합니다.
DESC user_source;
    /*
    이름 널? 유형             
    ---- -- -------------- 
    NAME    VARCHAR2(30)   
    TYPE    VARCHAR2(12)   
    LINE    NUMBER         
    TEXT    VARCHAR2(4000) 
    */
    
-- 프로시저를 조회합니다.
SELECT * FROM USER_SOURCE;
        -- 원하는 프로시저를 라인따라 복사한 뒤 실행하면 사용할 수 있습니다.

   
-- 권한을 확인합니다.
SELECT * FROM session_privs;

    
    -- 프로시저 삭제
        /*
        DROP PROCEDURE procedure_name;
        */
DROP PROCEDURE query_emp;
    /*
    Procedure QUERY_EMP이(가) 삭제되었습니다.
    */