CREATE TABLE SFZ_TEST_MANAGER_XG( yxgh VARCHAR2(100), ygxm VARCHAR2(100), position_name VARCHAR2(100) ); CREATE TABLE SFZ_TEST_MANAGER_XG AS select T.CCUST_ID as yxgh,T.ACCS_NBR as ygxm,T.CCUST_NAME as position_name from HEMS_FILTER_CUST_LIST t where t.ccust_id is not null and t.accs_nbr is not null and t.ccust_name is not null;
DROP TABLE SFZ_TEST_MANAGER_XG;
select * from SFZ_TEST_MANAGER_XG; select count(*) from SFZ_TEST_MANAGER_XG;
create or replace TYPE SFZ_MANAG_M_OBJ_TYPE AS OBJECT( yxgh VARCHAR2(100), ygxm VARCHAR2(100), position_name VARCHAR2(100));
DROP TYPE SFZ_MANAG_M_OBJ_TYPE;
select * from user_objects t where t.object_name not like '%SFZ_MANAG_M_OBJ_TYPE%' and t.object_type = 'TYPE';
CREATE OR REPLACE TYPE SFZ_MANAG_M_TAB_TYPE IS TABLE OF SFZ_MANAG_M_OBJ_TYPE;
DROP TYPE SFZ_MANAG_M_TAB_TYPE;
create or replace procedure proc_sfz_proc_test(user_yxgh IN VARCHAR2,v_table out SFZ_MANAG_M_TAB_TYPE) is cursor cursor_cur_type is select * from SFZ_TEST_MANAGER_XG; --可以增加条件 i NUMBER(8) :=1; BEGIN v_table:=SFZ_MANAG_M_TAB_TYPE(); for cursor_record in cursor_cur_type loop v_table.EXTEND; v_table(i) := NEW SFZ_MANAG_M_OBJ_TYPE(cursor_record.yxgh,cursor_record.ygxm,cursor_record.position_name); --v_table(i) := NEW SFZ_MANAG_M_OBJ_TYPE('123','124','125'); i:=i+1; end loop; end proc_sfz_proc_test;