Oracle 프로시저 - 오브젝트 어레이 선언해서 돌리기

테스트코드 짜다가 버리기 아까워서 저장.

 

타입선언

create or replace TYPE USER_OBJECT AS OBJECT ( V_USERNAME VARCHAR2(20), V_PHONE VARCHAR2(20), V_ADDRESS VARCHAR2(100) );

create or replace TYPE USER_OBJECT_ARRAY AS TABLE OF USER_OBJECT;

테이블

CREATE TABLE USERTABLE ( V_USERNAME VARCHAR2(20), V_PHONE VARCHAR2(20), V_ADDRESS VARCHAR2(100) )

 

 

그냥 실행문

DECLARE V_CODE VARCHAR2(100); V_MSG VARCHAR2(100); v_CNT NUMBER(4) :=0 ; v_userArr USER_OBJECT_ARRAY; v_user USER_OBJECT;

BEGIN

v_userArr := USER_OBJECT_ARRAY(USER_OBJECT(‘김유저’, ‘010-000-3333’, ‘서울시 전체’), USER_OBJECT(‘최유저’, ‘010-4444-4444’, ‘경기도 전체’));

for i in 1..v_userArr.count loop dbms_output.put_line(v_userArr(i).V_USERNAME); end loop;

END;

 

 

프로시저

create or replace PROCEDURE MASS_USER_PROCEDURE ( v_userArr in USER_OBJECT_ARRAY, O_RESULR_CODE OUT VARCHAR2, O_RESULT_MESSAGE OUT VARCHAR2)

IS

–V_USERNAME VARCHAR2(20); –V_PHONE VARCHAR2(20); –V_ADDRESS VARCHAR2(100);

i BINARY_INTEGER := 0;

BEGIN

for i in 1..v_userArr.count loop dbms_output.put_line(v_userArr(i).V_USERNAME); insert into usertable (V_USERNAME, V_PHONE, V_ADDRESS) values (v_userArr(i).V_USERNAME, v_userArr(i).V_PHONE, v_userArr(i).V_ADDRESS); end loop; commit;

END MASS_USER_PROCEDURE;

 

프로시저 호출코드

DECLARE v_code VARCHAR2(100); v_message VARCHAR2(100); v_userArr USER_OBJECT_ARRAY; v_user USER_OBJECT;

BEGIN

v_userArr := USER_OBJECT_ARRAY(USER_OBJECT(‘김유저’, ‘0103333’, ‘서울시 전체’), USER_OBJECT(‘최유저’, ‘0104444’, ‘경기도 전체’));

for i in 1..v_userArr.count loop MASS_USER_PROCEDURE(v_userArr, V_CODE, V_MSG); dbms_output.put_line(v_CODE); end loop;

END;

 

 

 

 

제일 도움된 페이지 :

https://oracle-base.com/articles/8i/collections-8i