二维数组作为存储过程入口参数

--0-知识储备： ---2 查主键
select cons.column_name,cols.data_type from all_cons_columns cons, all_constraints con,all_tab_cols cols
where con.constraint_type='P'
and cons.table_name = upper('glfw_szdj')
and cons.constraint_name=con.constraint_name
and cons.column_name=cols.column_name
and cons.table_name=cols.table_name
order by cons.column_name;

--1 建立操作类型存储过程，作用：对我要用的存储过程进行调用。

create or replace procedure For_show_list(v_sm out varchar2)
is
/* type v_keys_row is varray(30) of varchar2(50);
type var_keys is varray(10) of v_keys_row;*/
--测试1--修改记录使其有误
varray_keys var_keys:= var_keys(v_keys_row('520201','520201009000006'),v_keys_row('520201','520201010000002'));
--测试2(正确记录测试)
--排序按主键的字母排序。
/* select SBH, SZ, SM, KJKM, XJKM, JCDM, HYDM, DWSE, ZSFS, DWID,glfw_szdj.* from glfw_szdj where sbh=520201002000001 and to_number(dwse)=to_NUMBER('2.000000') and
sz=1030127 and sm=to_char('01') and kjkm='99' and xjkm=00 and jcdm=2000 and hydm=106920 and zsfs=10 and dwid=520201 */
/* varray_keys var_keys:=var_keys(v_keys_row('520201','2.000000','106920','2000','99','520201002000001','01','1030127','00','10'));*/
begin
--varray_keys:= var_keys(v_keys_row('52012100000','520121'),v_keys_row('52012100001','520129'));

pkg_keys_all.proc_keys_all('glfw_swdj',varray_keys,v_sm);
end;

---

---2.主体存储过程之包头

create or replace package pkg_keys_all is

v_errtext varchar2(2000);
--需要先定义二维数组
--create or replace type v_keys_row as varray(30) of varchar2(50);
--create or replace type var_keys as varray(10) of v_keys_row;
procedure proc_keys_all(v_tabname varchar2,varray_keys in var_keys,v_sm out varchar2);
end pkg_keys_all;

----3.主体存储过程之包体

create or replace package body pkg_keys_all is
--------------------------------------------------------------------------------------------
/*fun-运行通用动态SQL*/
--------------------------------------------------------------------------------------------
FUNCTION F_RUNSQL(v_sql VARCHAR2) RETURN BOOLEAN IS
/*动态执行没有传入参数的SQL*/
BEGIN
EXECUTE IMMEDIATE v_sql;
RETURN TRUE;
EXCEPTION
WHEN OTHERS THEN
v_errtext :='1.'|| substr(SQLERRM, 1, 512);
RETURN FALSE;
END F_RUNSQL;
-----------------------------------------------------------------------------------------------
/*function-根据主键个数拼接sql语句*/
----------------------------------------------------------------------------------------------

FUNCTION f_sqlBykeys(v_tabname varchar2,keys_row in v_keys_row,keyNameValues out varchar2) return varchar2 is
v_keyname varchar2(50);
v_keytype varchar2(50);
v_sql_return varchar2(1000);
i number(2);

/*0-查找指定表的主键。*/
cursor c_keys is
select cons.column_name,cols.data_type from all_cons_columns cons, all_constraints con,all_tab_cols cols
where con.constraint_type='P'
and cons.table_name = upper(v_tabname)
and cons.constraint_name=con.constraint_name
and cons.column_name=cols.column_name
and cons.table_name=cols.table_name
order by cons.column_name;
begin
v_sql_return:='';
i :=1;
open c_keys;
loop
fetch c_keys into v_keyname,v_keytype;
if c_keys%found then
---对keytype 为char的进行and累加。
if v_keytype='CHAR' then
v_sql_return:=v_sql_return||' and '||v_keyname||'='''||keys_row(i)||'''';
else
v_sql_return:=v_sql_return||' and '||v_keyname||'=TO_'||v_keytype||'('''||keys_row(i)||''')';
end if;
---对keyname,keyvalue赋值给输出变量keyNameValues
keyNameValues:=keyNameValues||v_keyname||keys_row(i)||',';
i:=i+1;
else return v_sql_return;
end if;
end loop;
close c_keys;
return v_sql_return;
exception
when others then
v_errtext:=v_errtext||'2.f_sqlBykeys函数调用出错';
return 'false';
end f_sqlBykeys;

--------------------------------------------------------------------------------------------
/*function-对某个sbh，dwid下的glfw_swdj中的数据进行规则查错*/
--------------------------------------------------------------------------------------------
FUNCTION f_chkkey(v_tabname varchar2,keys_row in v_keys_row,keyNameValues out varchar2) RETURN boolean is
v_sqls varchar2(2000);
v_sm_zl varchar2(500);
v_sm varchar2(500);
v_cnt_zl number(15);
v_cnt_wrongnum number(3);
v_cnt number(15);
v_sql_temp varchar(1000);

/*1-从查错规则过程中取规则来源*/
cursor c_sqls is
select chksql from ta_rules t where upper(t1_name)=u