Database_82_PL/SQL_변수 사용, 실행문 작성

-- PL/SQL BLOCK
    -- 렉시칼 단위 : PL/SQL 텍스트 행에 포함되는 문자 그룹을 의미합니다.
    -- 분류 
        -- 식별자: v_fname, c_percent 
        -- 구분자: ; , +, - 
        -- 리터럴: John, 428, True 
        -- 주석: --, /* */
    -- 주의 사항
        -- 문자, 날짜 리터럴은 작은 따옴표로 묶어 작성합니다.
            -- 리터럴 : 식별자로 표현되지 않는 명시적 숫자, 문자, 문자열, 부울 값을 의미합니다.
        -- 숫자로는 단순 값이나 과학적 표기법을 사용할 수 있습니다.
        -- 행에 슬래시만 있으면 PL/SQL 프로그램이 종료/실행됩니다.
-- SQL 함수
    -- 프로시저 문에 사용 가능한 내장 함수
        -- 숫자
        -- 문자
        -- 변환
        -- 날짜
        -- 기타
    -- 사용할 수 없는 함수
        -- DECODE
        -- 그룹 함수
    -- 데이터 유형 변환
        -- 데이터를 유사한 데이터 유형으로 변환할 수 있습니다.
        -- 유형
            -- 암시적 변환
            -- 명시적 변환
        -- 함수
            -- TO_CHAR
            -- TO_DATE
            -- TO_NUMBER
            -- TO_TIMESTAMP
-- implicit data type conversion

    -- 중첩 블록
        -- PL/SQL 블록은 실행문을 사용 가능한 모든 곳에서 중첩할 수 있습니다.
        -- 하나의 문장이 됩니다
        -- EXCEPTION 섹션도 중첩 블록을 포함할 수 있습니다.
SET serveroutput ON
DECLARE
    v_outer_variable VARCHAR2(20):='GLOBAL VARIABLE';
BEGIN
    DECLARE
        v_inner_variable VARCHAR2(20):='LOCAL VARIABLE';
    BEGIN   -- 중첩 블록입니다.
        DBMS_OUTPUT.PUT_LINE(v_inner_variable);
        DBMS_OUTPUT.PUT_LINE(v_outer_variable); 
            -- 밖의 블록 변수를 참조할 수 있습니다.
    END;
    DBMS_OUTPUT.PUT_LINE(v_outer_variable); 
        -- 안의 블록 변수를 참조할 수 없습니다.
END;

    -- 변수_식별자 범위
        -- 식별자를 참조할 수 있는 프로그램 단위( 블록, 서브 프로그램, 패키지 )의 영역입니다.
        -- 식별자가 선언된 블록과 모든 중첩 서브 블록, 프로시저 및 함수에서 볼 수 있습니다.
        -- 블록이 지역 식별자를 찾지 못하는 경우 상위 블록의 선언 부분을 검색합니다.
        -- 블록은 서브 블록과 인접한 형제 블록을 검색하지 않습니다.
            /*
            ...
                x BINARY_INTEGER;
            BEGIN
                ...
                DECLARE
                    y NUMBER;
                BEGIN
                    y:= x;  -- Y의 범위입니다.
                END;
                ...
            END;    -- X의 범위입니다.
            */
    -- 변수의 가시성
        -- 검증되지 않은 이름을 사용하여 식별자를 참조할 수 있는 영역에서만 볼 수 있습니다.

SET serveroutput ON
DECLARE
    v_father_name VARCHAR2(20):='Patrick';
    v_date_of_birth DATE:='1972/04/20';
BEGIN
    DECLARE
        v_child_name VARCHAR2(20):='Mike';
        v_date_of_birth DATE:='2003/12/12';
    BEGIN
        DBMS_OUTPUT.PUT_LINE('Father''s Name: '||v_father_name);    -- 밖의 변수입니다.
        DBMS_OUTPUT.PUT_LINE('Date of Birth: '||v_date_of_birth);   -- 더 가까운 안의 변수입니다.
        DBMS_OUTPUT.PUT_LINE('Child''s Name: '||v_child_name);  -- 안의 변수입니다.
    END;
    DBMS_OUTPUT.PUT_LINE('Date of Birth: '||v_date_of_birth);   -- 밖의 변수입니다.
END;
/
    -- 코드가 애매합니다.

    -- 코드를 수정합니다.
BEGIN 
    DECLARE
        v_father_name VARCHAR2(20) := 'Patrick';
        v_data_of_birth DATE := '1972-04-20';
    BEGIN <<outer>>
        DECLARE
            v_child_name VARCHAR2(20):='Mike';
            v_date_of_birth DATE:='2002-12-12';
        BEGIN
             DBMS_OUTPUT.PUT_LINE('Father''s Name: '|| v_father_name);
             DBMS_OUTPUT.PUT_LINE('Date of Birth: '|| outer.v_date_of_birth);    -- 밖의 변수입니다.
             DBMS_OUTPUT.PUT_LINE('Child''s Name: '|| v_child_name);
             DBMS_OUTPUT.PUT_LINE('Date of Birth: '|| v_date_of_birth);
        END;
    END;
END outer;

            
    
    -- 연산자  
        -- 논리적 연산자
        -- 산술 연산자
        -- 연결 연산자
        -- 괄호 연산자 : 연산 순서 제어
        -- 지수 연산자(**)
        
-- 루프의 카운터를 증가시킵니다.
loop_count := loop_count + 1;

-- 부울 플래그의 값을 설정합니다. 
good_sal := sal BETWEEN 50000 AND 150000;

-- 사원 번호에 값이 포함되어 있는지 확인합니다.
valid := (empno IS NOT NULL);

    -- 코드 유지 관리를 쉽게 만드는 방법
        -- 주석을 사용하여 코드에 대한 설명을 추가합니다.
        -- 코드의 대소문자 규칙을 개발합니다.
        -- 식별자, 기타 객체의 이름 지정 규칙을 개발합니다.
        -- 들여쓰기로 가독성을 향상시킵니다.
        


-- SQL문 사용
    -- SELECT 명령을 사용하여 행을 검색합니다.
    -- DML 명령을 사용하여 행을 변경합니다.
    -- 트랜잭션을 제어합니다.
        -- COMMIT
        -- ROLLBACK
        -- SAVEPOINT
    -- SELECT
        /*
        SELECT select_list
        INTO { variable_name [, variable_name ]... | record_name }
        FROM table
        [ WHERE condition ];
        */
        -- 주의사항
            -- 각 SQL문은 세미 콜론으로 종료합니다.
            -- PL/SQL에 SELECT 문을 넣을 경우 INTO 절을 작성해야 합니다.
            -- WHERE 절은 입력 변수, 상수, 리터럴, PL/SQL 표현식을 지정하는 데에 사용됩니다.
        -- 이름 지정 규칙
            -- 모호성을 방지하기 위해 사용합니다.
            -- 데이터베이스 열 이름을 식별자로 사용하지 않습니다.
            -- 데이터베이스를 검사하여 테이블의 열이 있는지 확인하므로 구문 오류가 발생할 수 있습니다.
            -- 로컬 변수와 형식 파라미터의 이름은 데이터베이스 테이블의 이름보다 우선시됩니다.
            -- 데이터베이스 테이블 열의 이름은 로컬 변수의 이름보다 우선합니다.
            -- 변수 이름은 함수 이름보다 우선시됩니다.

SET serveroutput ON
DECLARE
v_fname VARCHAR2(25);
BEGIN
SELECT first_name INTO v_fname 
FROM employees WHERE employee_id=200;
DBMS_OUTPUT.PUT_LINE(' First Name is : '||v_fname);
END;
/

-- 지정 사원의 HIRE_DATE, SALARY를 검색합니다.
SET serveroutput ON
DECLARE
v_emp_hiredate employees.hire_date%TYPE;
v_emp_salary employees.salary%TYPE; 
BEGIN
SELECT hire_date, salary
INTO v_emp_hiredate, v_emp_salary
FROM employees
WHERE employee_id = 100;
DBMS_OUTPUT.PUT_LINE ('Hire date is :'|| v_emp_hiredate);
DBMS_OUTPUT.PUT_LINE ('Salary is :'|| v_emp_salary);
END;
/

 
-- 지정된 부서에 속한 모든 사원의 급여 합계를 조회합니다.  
SET serveroutput ON
DECLARE 
v_sum_sal NUMBER(10,2); 
v_deptno NUMBER NOT NULL := 60; 
BEGIN
SELECT SUM(salary) -- group function
INTO v_sum_sal FROM employees
WHERE department_id = v_deptno;
DBMS_OUTPUT.PUT_LINE ('The sum of salary is ' || v_sum_sal);
END;    

    -- DML 조작
        -- INSERT
        -- UPDATE
        -- DELETE
        -- MERGE

    -- INSERT
-- 시퀀스를 생성합니다.
CREATE SEQUENCE employees_seq 
START WITH 250
NOCACHE 
NOCYCLE;
    /*
    Sequence EMPLOYEES_SEQ이(가) 생성되었습니다.
    */

-- 시퀀스를 사용하여 데이터를 삽입합니다.
BEGIN
INSERT INTO employees (employee_id, first_name, 
                       last_name, email, hire_date, job_id, salary)
VALUES(employees_seq.NEXTVAL, 'Ruth', 'Cores',
        'RCORES',CURRENT_DATE, 'AD_ASST', 4000);
END;
/
    /*
    PL/SQL 프로시저가 성공적으로 완료되었습니다.
    */

SELECT employee_id, first_name, last_name, email, hire_date 
FROM employees
WHERE first_name='Ruth';

    -- UPDATE
-- 변경할 데이터 조회
SELECT employee_id AS EM_ID, last_name, salary
FROM employees
WHERE job_id = 'ST_CLERK';

    
-- 데이터 수정
DECLARE
    sal_increase employees.salary%TYPE:=800;
BEGIN
    UPDATE employees
    SET salary=salary + sal_increase
    WHERE job_id='ST_CLERK';
END;
/
    /*
    PL/SQL 프로시저가 성공적으로 완료되었습니다.
    */

-- 데이터 조회
SELECT employee_id AS EM_ID, last_name, salary
FROM employees
WHERE job_id = 'ST_CLERK';

    
-- 데이터를 되돌립니다.
ROLLBACK;
    /*
    롤백 완료.
    */

    -- DELETE
DECLARE
    deptno employees.department_id%TYPE:=10;
BEGIN
    DELETE FROM employees
    WHERE department_id=deptno;
END;
/
    /*
        ---<오류>-----------------------------------------------------------------------
        오류 보고 -
        ORA-02292: integrity constraint (HR.JHIST_EMP_FK) violated - child record found
        ORA-06512: at line 4
        02292. 00000 - "integrity constraint (%s.%s) violated - child record found"
        *Cause:    attempted to delete a parent key value that had a foreign
                   dependency.
        *Action:   delete dependencies first then parent or disable constraint.
        --------------------------------------------------------------------------------
    */