ORA-00905: 缺失关键字

Question

 

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

Answer 1

如果你所说的改成
[code="sql"]
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||'''';
[/code]
就成功了，那就这样好了，然后在execute的时候into：
[code="sql"]
execute immediate f_sql into p_num;
[/code]