散步蜗牛
2012-07-04 18:36
浏览 837

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

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • liuyinhuan0409 2012-07-05 09:17
    已采纳

    如果你所说的改成
    [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]

    点赞 评论

相关推荐 更多相似问题