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||'''';
则是可以成功。