一.存储过程参数使用自定义table
--定义列信息
CREATE OR REPLACE TYPE REP_TYPE_PARAM AS OBJECT
(
columnID Varchar2(100),
valueData clob
);
--as table of 定义table
CREATE OR REPLACE TYPE REP_TABLE_PARAM AS TABLE OF REP_TYPE_PARAM ;
(
columnID Varchar2(100),
valueData clob
);
--as table of 定义table
CREATE OR REPLACE TYPE REP_TABLE_PARAM AS TABLE OF REP_TYPE_PARAM ;
--定义存储过程
create or replace procedure p_test(p_param in REP_TABLE_PARAM,p_cur out sys_refcursor)as
v_type_param REP_Type_Param;
v_columnId varchar2(100);
v_valuedata clob;
begin
v_type_param REP_Type_Param;
v_columnId varchar2(100);
v_valuedata clob;
begin
--循环输出自定义table
-- 动态获取表信息
select valueData into v_valuedata from TABLE(p_param) where columnid ='tony1';
dbms_output.put_line('######'||v_valuedata);
for i in 1.. p_param.count loop
v_type_param := p_param(i);
v_columnId := v_type_param.columnid;
v_valuedata :
dbms_output.put_line('######'||v_valuedata);
for i in 1.. p_param.count loop
v_type_param := p_param(i);
v_columnId := v_type_param.columnid;
v_valuedata :