我已经成功实现了一个proc,可以从表中删除删除重复的记录。继续进行下去,我进一步尝试使用Dynamic sql来概括此过程,但是在编译自身时出现以下错误。
ERROR line 13, col 7, ending_line 14, ending_col 14, Found ''SELECT ROWID, ERR_MOD FROM '', Expecting: ( SELECT -or- WITH
这是我的代码:-
CREATE OR REPLACE PROCEDURE BIOCON.proc_dedup (
p_tblname IN VARCHAR2,
p_cname IN varchar2,
p_cvalue IN varchar2,
p_out OUT VARCHAR2:=0
)
IS
v_cnt NUMBER;
CURSOR TMP
IS
'SELECT ROWID, ERR_MOD
FROM ' || p_tblname||
'WHERE '|| p_cname '='||''p_cvalue '';
BEGIN
BEGIN
'SELECT COUNT ( * )
INTO v_cnt
FROM ' || p_tblname||
'WHERE '|| p_cname' = '||''p_cvalue'';
EXCEPTION
WHEN OTHERS
THEN
p_out:=1;
end;
FOR REC IN tmp
LOOP
IF v_cnt = 1
THEN
EXIT;
ELSE
'DELETE FROM '|| p_tblname||
'WHERE ROWID = REC.ROWID';
END IF;
v_cnt := v_cnt - 1;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
p_out := 2;
END;
/
嗨,您不能在这种情况下为普通游标创建动态查询,您应该使用ref游标并遵循以下步骤-创建查询字符串-打开ref游标,并请使用立即执行来执行delete语句...
CREATE OR REPLACE PROCEDURE BIOCON.proc_dedup (
p_tblname IN VARCHAR2,
p_cname IN varchar2,
p_cvalue IN varchar2,
p_out OUT VARCHAR2:=0
IS
v_cnt NUMBER;
v_string varchar2(200);
TYPE ref_cur IS REF CURSOR
ref_cursor_name ref_cur;
BEGIN
v_string:='SELECT ROWID, ERR_MOD FROM ' || p_tblname|| 'WHERE '|| p_cname
'='||''p_cvalue '';
OPEN cursor_variable_name FOR v_string;
.....
.....
EXECUTE IMMEDIATE 'DELETE FROM '|| p_tblname|| 'WHERE ROWID = REC.ROWID';--you need execute immediate for this
..... .....
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句