CREATE OR REPLACE PROCEDURE P_OPERATIONNAMEREFCOUNT( tablesName varchar2, columnName varchar2, columnVals varchar2, numsresult OUT INTEGER )as p_num INTEGER; f_sql varchar(1000); cursor curs IS ( SELECT b.column_name pkcolumn_name,c.table_name fktable_name,c.column_name fkcolumn_name FROM user_cons_columns b LEFT JOIN (SELECT constraint_name, r_constraint_name FROM user_constraints WHERE constraint_type='R' ) a ON b.constraint_name=a.r_constraint_name LEFT JOIN user_cons_columns c ON c.constraint_name=a.constraint_name WHERE c.position =b.position AND b.table_name = upper(''||tablesName||'') UNION select 'OPERATIONNAME','DESIGNTASK','TASKNAME' from dual ); begin p_num:=0; for cur in curs loop f_sql := 'select count(*) into '||p_num||' from '||upper(tablesName)||' b inner join '||cur.fktable_name||' d ON b.'||cur.pkcolumn_name||'=d.'||cur.fkcolumn_name||' where b.'||upper(columnName)||' like '''||columnVals||''''; execute immediate f_sql; numsresult:=p_num+numsresult; --dbms_output.put_line(f_sql||';'); end loop; END P_OPERATIONNAMEREFCOUNT;
此存储过程报如下错误:
连接到数据库 orcl-eman。
ORA-00905: 缺失关键字
ORA-06512: 在 "EMAN.P_OPERATIONNAMEREFCOUNT", line 22
ORA-06512: 在 line 11
进程已退出。
从数据库 orcl-eman 断开连接。
我将:
f_sql := 'select count(*) into '||p_num||' from '||upper(tablesName)||' b inner join '||cur.fktable_name||' d ON b.'||cur.pkcolumn_name||'=d.'||cur.fkcolumn_name||' where b.'||upper(columnName)||' like '''||columnVals||'''';
改变成:
f_sql := 'select count(*) into from '||upper(tablesName)||' b inner join '||cur.fktable_name||' d ON b.'||cur.pkcolumn_name||'=d.'||cur.fkcolumn_name||' where b.'||upper(columnName)||' like '''||columnVals||'''';是可以成功运行,但问题是为什么不能将值into p_num中呢,如何才能做到?