[교육] SQL초보에서실전전문가까지 ORACLE - 8

• Composite Type 변수 만들기

DECLARE

    TYPE name_table_type IS TABLE OF emp.ename%TYPE

        INDEX BY BINARY_INTEGER;

        name_table    name_table_type;

    begin

        name_table(1) := 'KKKK';

        name_table(2) := 'HHHH';

        insert into emp(empno, ename) values (5151, name_table(1));

        insert into emp(empno, ename) values (6161, name_table(2));

        --// 내부에  commit 넣어줄 수 있음.

        commit;

    end;

    

select * from emp;

•  테이블변수 만들기

DECLARE 

    TYPE EMP_RECORD_TYPE IS RECORD

    (

        EMPNO       NUMBER(4),

        ENAME       VARCHAR2(10),

        HIREDATE    DATE NOT NULL := SYSDATE

    );

    EMP_RECORD  EMP_RECORD_TYPE;

    

    BEGIN

        EMP_RECORD.EMPNO := 1212;

        EMP_RECORD.ENAME := 'GGGG';

        EMP_RECORD.HIREDATE := SYSDATE;

        

        INSERT INTO EMP(EMPNO, ENAME, HIREDATE)

        VALUES (EMP_RECORD.EMPNO, EMP_RECORD.ENAME, EMP_RECORD.HIREDATE);

        

        DBMS_OUTPUT.PUT_LINE(EMP_RECORD.EMPNO);

        DBMS_OUTPUT.PUT_LINE(EMP_RECORD.ENAME);

        DBMS_OUTPUT.PUT_LINE(EMP_RECORD.HIREDATE);

    END;

    

select * from emp;

VARIABLE B_AVG NUMBER

ACCEPT B_MATH PROMPT 'INPUT MATH VALUE : '

ACCEPT B_ENG PROMPT 'INPUT ENG VALUE : '

DECLARE

    V_MATH NUMBER(9,2) := &B_MATH;

    V_ENG NUMBER(9,2) := &B_ENG;

    

    BEGIN

        :B_AVG := (V_MATH + V_ENG)/2;

    END;

    

• PROCEDURE 생성하기

CREATE OR REPLACE PROCEDURE EMP_PUT

(

    DPNO     IN  EMP.DEPTNO%TYPE,

    NEWSAL IN  EMP.SAL%TYPE

)

IS BEGIN

    

    UPDATE EMP

    SET SAL = NEWSAL

    WHERE DEPTNO = DPNO;

END;

EXECUTE EMP_PUT(10, 8000);

SELECT * FROM EMP;

CREATE OR REPLACE PROCEDURE EMP_DEL

(

    DPNO IN  EMP.DEPTNO%TYPE

)

IS BEGIN

    

    DELETE FROM EMP

    WHERE DEPTNO = DPNO;

END;

EXECUTE EMP_DEL(10);

CREATE OR REPLACE PROCEDURE EMP_INS

(

    ENO IN EMP.EMPNO%TYPE,

    ENM IN EMP.ENAME%TYPE,

    AMT IN EMP.SAL%TYPE

)

IS BEGIN

    INSERT INTO EMP 

    (

        EMPNO,

        ENAME,

        SAL

    ) 

    VALUES 

    (

        ENO,

        ENM,

        AMT

    );

END;    

EXECUTE EMP_INS(1112, 'HOKIA', 1020);

SELECT * FROM EMP WHERE EMPNO=1112;

CREATE OR REPLACE PROCEDURE SHOW_EMP

(

    ENO  IN  EMP.EMPNO%TYPE

)

IS 

    V_SAL   EMP.SAL%TYPE;

    V_DATE  EMP.HIREDATE%TYPE;

BEGIN

    SELECT SAL, HIREDATE

    INTO V_SAL, V_DATE

    FROM EMP

    WHERE EMPNO=ENO;

    

    DBMS_OUTPUT.PUT_LINE('SAL:'||TO_CHAR(V_SAL, '9,999'));

    DBMS_OUTPUT.PUT_LINE('HIREDATE:'||TO_CHAR(V_DATE));

END;

EXEC SHOW_EMP(1112);