oracle 缺失关键字_ORA-00905: 缺失关键字

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中呢，如何才能做到？

问题补充：上面描述有点小问题

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||'''';

则是可以成功。

问题补充：上面描述有点小问题

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(*) 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||'''';

则是可以成功。