(8.0.34)
調べた限りない模様
(23c)
https://tech.excite.co.jp/entry/2023/10/27/100350
https://docs.oracle.com/cd/F82042_01/lnpls/RETURNING-INTO-clause.html#GUID-38F735B9-1100-45AF-AE71-18FB74A899BE
http://www.mitene.or.jp/~rnk/TIPS_ORCL_OTHER.htm
https://qiita.com/500InternalServerError/items/576d0b0981723a6653ff
※ Oracle Database 23cではUPDATE文によって更新された列の更新前と更新後の値が取得可能
-- 1. テストデータ作成
drop table tab1 purge;
create table tab1
(
id int,
name varchar2(100),
score int
);
insert into tab1 values(1,'taro',800);
insert into tab1 values(2,'jiro',10);
insert into tab1 values(3,'saburo',100);
commit;
select * from tab1;
-- 2. 動作確認
set serveroutput on
declare
type rec01 is record (
name tab1.name%type,
old_score tab1.score%type,
new_score tab1.score%type
);
type t01 is table of rec01;
output t01;
begin
update tab1
set score = score * 1.1
where id > 1
returning name, OLD score, NEW score BULK COLLECT INTO output;
for i in output.first..output.last loop
dbms_output.put_line('score of ' || output(i).name || ' raised from ' || output(i).old_score || ' to ' || output(i).new_score);
end loop;
delete from tab1
where id != 2
returning name, 0, 0 BULK COLLECT INTO output;
for i in output.first..output.last loop
dbms_output.put_line(output(i).name || ' is deleted ');
end loop;
insert into tab1 values(4,'siro',1010)
returning name, 0, score BULK COLLECT INTO output;
for i in output.first..output.last loop
dbms_output.put_line(output(i).name || ' is inserted ');
end loop;
end;
/
select * from tab1;
(16)
https://www.postgresql.jp/document/15/html/dml-returning.html
https://qiita.com/jun99stu0209/items/5d97d6333bc12e1320f5
-- 1. テストデータ作成
drop table tab1;
create table tab1
(
id int,
name varchar(100),
score int
);
insert into tab1 values(1,'taro',800);
insert into tab1 values(2,'jiro',10);
insert into tab1 values(3,'saburo',100);
select * from tab1;
-- 2. 動作確認
update tab1
set score = score * 1.1
where id > 1
returning *
;
delete from tab1
where id != 2
returning *
;
insert into tab1
values(4,'siro',1010)
returning *
;
select * from tab1;
(2022)
https://sql55.com/query/sql-server-output-clause.php
https://learn.microsoft.com/ja-jp/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-ver16
-- 1. テストデータ作成
drop table tab1;
create table tab1
(
id int,
name varchar(100),
score int
);
insert into tab1 values(1,'taro',800);
insert into tab1 values(2,'jiro',10);
insert into tab1 values(3,'saburo',100);
select * from tab1;
-- 2. 動作確認
declare @output table (
name varchar(100),
old_score int,
new_score int
);
update tab1
set score = score * 1.1
output inserted.name, deleted.score, inserted.score
into @output
where id > 1
;
select * from @output;
delete from @output;
delete from tab1
output deleted.name, 0, 0
into @output
where id != 2
;
select * from @output;
delete from @output;
insert into tab1
output inserted.name, 0, inserted.score
into @output
values(4,'siro',1010)
;
select * from @output;
select * from tab1;
