我写的是这样的: CREATE OR REPLACE PROCEDURE imp_tmp_icstock(user_id dbms_sql.Number_Table, --用户ID max_id dbms_sql.Number_Table) as --id最大值 K NUMBER; J NUMBER; c integer; ret integer; sql_stat VARCHAR2(1000); TYPE ARRSYVAR IS VARRAY(36) OF VARCHAR2(30); ARRAYPARA ARRSYVAR; TABLENAME VARCHAR2(50); BEGIN ARRAYPARA := ARRSYVAR('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', '0', '1', '2', '3', '4', '5', '6', '7', '8', '9' ); FOR K IN 1 .. 1 LOOP FOR J IN 1 .. 1 LOOP c:=dbms_sql.open_cursor;
TABLENAME := 'IC_'||ARRAYPARA(K)||ARRAYPARA(J); sql_stat := 'INSERT INTO '||TABLENAME||' SELECT * FROM sysicstock where userid=:user_id and stockid>:max_id;'; dbms_sql.parse(c, sql_stat, dbms_sql.native); dbms_sql.bind_array(c , ':user_id', user_id); dbms_sql.bind_array(c , ':max_id', max_id); ret := dbms_sql.execute(c); dbms_sql.close_cursor(c); END LOOP; END LOOP; END imp_tmp_icstock;运行是这样的: execute imp_tmp_icstock(8,40)但是运行不通过,报错为:SQL> execute imp_tmp_icstock(8,40) BEGIN imp_tmp_icstock(8,40); END; * ERROR 位于第 1 行: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'IMP_TMP_ICSTOCK' ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'IMP_TMP_ICSTOCK' ORA-06550: line 1, column 7: PL/SQL: Statement ignored请各位帮忙看一下是什么原因?
CREATE OR REPLACE PROCEDURE imp_tmp_icstock(user_id dbms_sql.Number_Table, --用户ID
max_id dbms_sql.Number_Table) as --id最大值
K NUMBER;
J NUMBER;
c integer;
ret integer;
sql_stat VARCHAR2(1000);
TYPE ARRSYVAR IS VARRAY(36) OF VARCHAR2(30);
ARRAYPARA ARRSYVAR;
TABLENAME VARCHAR2(50); BEGIN
ARRAYPARA := ARRSYVAR('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J',
'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V',
'W', 'X', 'Y', 'Z', '0', '1', '2', '3', '4', '5', '6', '7',
'8', '9' );
FOR K IN 1 .. 1 LOOP
FOR J IN 1 .. 1 LOOP
c:=dbms_sql.open_cursor;
TABLENAME := 'IC_'||ARRAYPARA(K)||ARRAYPARA(J);
sql_stat := 'INSERT INTO '||TABLENAME||' SELECT * FROM sysicstock where userid=:user_id and stockid>:max_id;';
dbms_sql.parse(c, sql_stat, dbms_sql.native);
dbms_sql.bind_array(c , ':user_id', user_id);
dbms_sql.bind_array(c , ':max_id', max_id);
ret := dbms_sql.execute(c);
dbms_sql.close_cursor(c);
END LOOP;
END LOOP;
END imp_tmp_icstock;运行是这样的: execute imp_tmp_icstock(8,40)但是运行不通过,报错为:SQL> execute imp_tmp_icstock(8,40)
BEGIN imp_tmp_icstock(8,40); END; *
ERROR 位于第 1 行:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'IMP_TMP_ICSTOCK'
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'IMP_TMP_ICSTOCK'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored请各位帮忙看一下是什么原因?