开篇立意
oracle在函数中使用dml语句时,有两者情况。即:(1)直接使用select调用该函数;(2)在匿名块中调用该函数。
针对第一种情况我们测试一下
简单的函数:
create table nested_tab(id int, name varchar2(100), job varchar2(100), age int);
insert into nested_tab values (1, 'asda', 'gfdgd', 12);
insert into nested_tab values (2, 'sdfsd', 'cvxvx', 14);
insert into nested_tab values (3, 'uyiy', 'mmbv', 16);
create or replace function support_dml return int as
begin
update nested_tab set id = 4 where name = 'uyiy';
return 12;
end;
/
报错如下
SQL> select support_dml() from dual;
select support_dml() from dual
*
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "SYS.SUPPORT_DML", line 3
针对第二种情况我们测试一下
declare
retcode int := 1;
begin
retcode := support_dml();
dbms_output.put_line(retcode);
end;
/
12
PL/SQL procedure successfully completed.
由此可以知道定义在函数中的dml,只能在plsql中使用。
plorasql实现
lightdb 在兼容的过程中,上面描述的oracle的两种情况都支持。
plorasql测试
create table nested_tab(id int, name varchar2(100), job varchar2(100), age int);
insert into nested_tab values (1, 'asda', 'gfdgd', 12);
insert into nested_tab values (2, 'sdfsd', 'cvxvx', 14);
insert into nested_tab values (3, 'uyiy', 'mmbv', 16);
create or replace function support_dml return int as
begin
update nested_tab set id = 4 where name = 'uyiy';
return 12;
end;
/
lightdb@test_hs_oracle=# select support_dml() from dual;
support_dml
-------------
12
(1 row)
lightdb@test_hs_oracle=# select dbms_output.serveroutput('t');
serveroutput
--------------
(1 row)
lightdb@test_hs_oracle=# declare
lightdb@test_hs_oracle$# retcode int := 1;
lightdb@test_hs_oracle$# begin
lightdb@test_hs_oracle$# retcode := support_dml();
lightdb@test_hs_oracle$# dbms_output.put_line(retcode);
lightdb@test_hs_oracle$# end;
lightdb@test_hs_oracle$# /
12
DO