存储过程不能自己使用DDL语句,要使用动态语句来执行
CREATE or replace function Code
Return Varchar2 as
vGlideCode Varchar2(20);
seq_year varchar2(4);
str varchar2(100);
begin
update tbl_seqYear set seqYear=to_char(sysdate,'yyyy');
str:='drop sequence xxSeq';
execute immediate str;
str :='create sequence xxSeq '
execute immediate str;
select xxSeq.nextval into vGlideCode from dual;
end if;
return(vGlideCode);
end;
CREATE or replace function Code
Return Varchar2 as
vGlideCode Varchar2(20);
seq_year varchar2(4);
str varchar2(100);
begin
update tbl_seqYear set seqYear=to_char(sysdate,'yyyy');
str:='drop sequence xxSeq';
execute immediate str;
str :='create sequence xxSeq '
execute immediate str;
select xxSeq.nextval into vGlideCode from dual;
end if;
return(vGlideCode);
end;
EXECUTE IMMEDIATE DDLSQL
Return Varchar2 as
vCode Varchar2(20);
sy varchar2(4);
sqlStr varchar2(200);
begin
select sn into sy from syy;
if sy=to_char(sysdate,'yyyy') then
select ss.nextval into vcode from dual;
else
sqlStr := 'update syy set sn ='||to_char(sysdate,'yyyy');
execute immediate sqlStr;
execute immediate 'drop sequence ss;
execute immediate 'create sequence ss';
select ss.nextval into vcodefrom dual;
end if;
return(vGlideCode);
end;ORA-14551: 无法在查询中执行 DML 操作
请问怎么解决在查询的同时做DML操作啊?????
Return Varchar2 as
vCode Varchar2(20);
sy varchar2(4);
sqlStr varchar2(200);
begin
select sn into sy from syy;
if sy=to_char(sysdate,'yyyy') then
select ss.nextval into vcode from dual;
else
update syy set sn =to_char(sysdate,'yyyy');--这里不需要动态执行
--execute immediate sqlStr;
execute immediate 'drop sequence ss;
execute immediate 'create sequence ss';
select ss.nextval into vcode from dual;--z这里少了个空格
end if;
return(vGlideCode);
end;
↑少了一個単引号
这条语句如果返回多条记录会出错,这样要使用到游标
此処↑会出Exception (NO_DATA_FOUND,TOO_MANY_ROWS等)在最後要加BEGIN --main
-- 省略
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN '0';
WHEN TOO_MANY_ROWS THEN
RETURN '0';
WHEN OTHERS THEN
RETURN '0';
END;
select ..., GlideCode() , ... from ... ?
权限我已经加了,没有问题
是在执行update syy set sn =to_char(sysdate,'yyyy');时提示法在查询中执行 DML 操作
权限我已经加了,没有问题
---------------------------------------------------
上面的语句单独执行肯定是没有问题的。
SQL> update test set time=to_char(sysdate,'yyyymmddhh24miss');3 行 已更新SQL> commit;提交完成你单步调试,看问题是出在哪句上。
你应该把你的DML语句写在过程中执行.
创建表怎么连字段都不加?其次,动态DML可以在存储过程中使用,但函数中我就不清楚了。