프로시저 Procedure 정의 및 변수종류 (2)

rowtype 사용 프로시저

CREATE OR REPLACE PROCEDURE rowtype_emp(
	emp_id IN EMPLOYEES.EMPLOYEE_ID%TYPE
) AS
	emp_row EMPLOYESS%ROWTYPE;
BEGIN 
    SELECT first_name, last_name, job_id
        INTO emp_row.first_name, emp_row.last_name, emp_row.job_id
    FROM employess WHERE employee_id = emp_id;
    DBMS_OUTPUT.PUT_LINE(emp_row.first_name || '|' || emp_row.last_name || '|' || emp_Row.job_id);
END;

 

record 사용 프로시저

CREATE OR REPLACE PROCEDURE recprd_emp(
	emp_id IN EMPLOYEES.EMPLOYEE_ID%TYPE
) AS
	TYPE emp_type IS RECORD (
    	first_name VARCHAR2(10),
    	last_name VARCHAR2(10),
        job_id VARCHAR2(10));
	emp_record emp_type;
BEGIN
	SELECT first_name, last_name, job_id
    	INTO emp_record.first_name, emp_record.last_name,emp_record.job_id
    FROM employees WHERE employee_id = emp_id;
    DBMS_OUTPUT.PUT_LINE(emp_record.first_name || '|' || emp_record.last_name || '|' || emp_record.job_id);
END;

 

collection 사용 프로시저 

CREATE OR REPLACE PROCEDURE collection_ex AS
	TYPE v_array_type IS VARRAY(5) OF NUMBER(10);
    TYPE nest_tbl_type IS TABLE OF VARCHAR2(10);
    TYPE a_array_type IS TABLE OF NUMBER(10) INDEX BY VARCHAR2(10);
    v_array v_array_type;
    nest_tbl nest_tbl_type;
    a_array a_array_type;
    idx VARCHAR2(10);
BEGIN
	v_array := v_array_type(1,2,3,4,5);
    nest_tbl := nest_tbl_type('A','B','C','D','E');
    a_array('A') :=1;
    a_array('B') :=2;
    a_array('C') :=3;
    a_array('D') :=4;
    a_array('E') :=5;
    FOR i IN 1..5 LOOP
    	DBMS_OUTPUT.PUT_LINE(v_array(i) || ' | ' || nest_tbl(i));
    END LOOP;
    idx := a_array.FIRST;
    WHILE idx IS NOT NULL LOOP
    	DMBS_OUTPUT.PUT_LINE(idx || ' : ' || a_array(idx));
        idx :=a_array.NEXT(idx);
    END LOOP;
END;