遭遇错误:ORA-14552 cannot perform a DDL, commit or rollback inside a query or DML
原因:
query中不能直接执行dml操作的方式,这个是ORACLE的限制
解决方法:用变通赋值方式
例子:
-------------------procedure-----------------
--删除序列
create or replace procedure p_deleteSeq (sequenceName in varchar2)
is
strSql varchar2(1000);
begin
strSql:='DROP sequence '||sequenceName;
-- dbms_output.put_line(strSql);
execute immediate strSql;
commit;
end p_deleteSeq;
------------------FUNCTION-------------
--删除序列
create or replace procedure p_deleteSeq (sequenceName in varchar2)
is
strSql varchar2(1000);
begin
strSql:='DROP sequence '||sequenceName;
-- dbms_output.put_line(strSql);
execute immediate strSql;
commit;
end p_deleteSeq;
------------------FUNCTION-------------
CREATE OR REPLACE FUNCTION f_getNum(tmp_c1 varchar2)
RETURN number IS
c number:=1;
strsql varchar2(2000);
BEGIN
c := c + nvl(tmp_c1,0);
--dbms_output.put_line(c);
strsql :='call p_deleteSeq( ''seq_testDefaultValue_test'')';
execute immediate strsql;
RETURN c;
END f_getNum;
RETURN number IS
c number:=1;
strsql varchar2(2000);
BEGIN
c := c + nvl(tmp_c1,0);
--dbms_output.put_line(c);
strsql :='call p_deleteSeq( ''seq_testDefaultValue_test'')';
execute immediate strsql;
RETURN c;
END f_getNum;
----------FUNCTION调procedure--------------------------------
set serveroutput on
declare
c number;
begin
c:=getNum('5');
dbms_output.put_line (c);
end;
----------------------
总结:
在查询中使用dml操作应该注意用变通赋值方式,如:c:=f_getNum('5'); 。不能用select f_getNum('5') into varnum from dual;
因为f_getNum函数中有 :execute immediate strsql;
转载于:https://blog.51cto.com/yuwenhu/146771