文章目录
1、使用场景
在存储过程、PL/SQL 块里需要
返回 insert、delete、update
等dml
语句后的信息时使用,合理使用returning
能够简化程序逻辑(少一次 select into
)、提高程序性能。
1.2、基础数据
DROP TABLE stu; -- if exists
CREATE TABLE stu (
s_id NUMBER,
s_xm VARCHAR2(30)
);
INSERT INTO stu(s_id, s_xm) VALUES(1, '小游子');
INSERT INTO stu(s_id, s_xm) VALUES(2, '小优子');
INSERT INTO stu(s_id, s_xm) VALUES(3, '小倩子');
COMMIT;
2、实例分析
2.1 insert、update、delete 修改单行记录
insert、update
是提取 操作后 的数据delete
是提取 操作前 的数据
DROP TABLE stu1; -- if exists
CREATE TABLE stu1 AS SELECT * FROM stu WHERE 1 = 2;
DECLARE
v_xm system.stu.s_xm%TYPE;
BEGIN
INSERT INTO stu(s_id, s_xm) VALUES (1, '小游子') RETURNING s_xm INTO v_xm;
dbms_output.put_line('插入后的 s_xm :' || v_xm);
v_xm:= NULL;
UPDATE stu SET s_xm = '小优子' WHERE s_id = 1 RETURNING s_xm INTO v_xm;
dbms_output.put_line('修改后的 s_xm :' || v_xm);
v_xm:= NULL;
DELETE stu WHERE s_id = 1 RETURNING s_xm INTO v_xm;
dbms_output.put_line('删除前的 s_xm :' || v_xm);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLCODE || ' : ' || SQLERRM);
dbms_output.put_line(dbms_utility.format_error_backtrace);
END;
测试结果:
2.1.1 + 动态sql
returning into
在 动态sql内部和外面都要写,且外面的 returning
后面不加字段直接 into
。
TRUNCATE TABLE stu;
INSERT INTO stu(s_id, s_xm) VALUES(1, '小游子');
INSERT INTO stu(s_id, s_xm) VALUES(2, '小优子');
INSERT INTO stu(s_id, s_xm) VALUES(3, '小倩子');
COMMIT;
-------- 以上为:数据准备 --------
DECLARE
v_id system.stu.s_id%TYPE;
v_xm system.stu.s_xm%TYPE;
v_sql VARCHAR(200);
BEGIN
v_sql := 'UPDATE stu SET s_xm = ''a'' WHERE s_id = :b1 RETURNING s_id, s_xm INTO :b2, :b3';
EXECUTE IMMEDIATE v_sql USING 3 RETURNING INTO v_id, v_xm; -- 内外 returning 都要写
ROLLBACK;
dbms_output.put_line(v_id||' : '||v_xm);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLCODE || ' : ' || SQLERRM);
dbms_output.put_line(dbms_utility.format_error_backtrace);
END;
2.2 record + table 修改多行记录
TRUNCATE TABLE stu;
INSERT INTO stu(s_id, s_xm) VALUES(1, '小游子');
INSERT INTO stu(s_id, s_xm) VALUES(2, '小优子');
INSERT INTO stu(s_id, s_xm) VALUES(3, '小倩子');
COMMIT;
-------- 以上为:数据准备 --------
DECLARE
TYPE stu_record IS RECORD(
v_id system.stu.s_id%TYPE,
v_xm system.stu.s_xm%TYPE);
TYPE stu_table IS TABLE OF stu_record INDEX BY PLS_INTEGER;
v_stu_table stu_table;
BEGIN
UPDATE stu t SET t.s_xm = 'a' WHERE 1 = 1 RETURNING t.s_id, t.s_xm BULK COLLECT INTO v_stu_table;
ROLLBACK;
FOR i IN v_stu_table.first .. v_stu_table.last LOOP
dbms_output.put_line(v_stu_table(i).v_id||' : '||v_stu_table(i).v_xm);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLCODE || ' : ' || SQLERRM);
dbms_output.put_line(dbms_utility.format_error_backtrace);
END;
测试结果:
2.2.1 + 动态sql
动态sql 内部仍然是 returning into
而不是 returning bulk collect into
2.2.1.1 returning 支持 table
TRUNCATE TABLE stu;
INSERT INTO stu(s_id, s_xm) VALUES(1, '小游子');
INSERT INTO stu(s_id, s_xm) VALUES(2, '小优子');
INSERT INTO stu(s_id, s_xm) VALUES(3, '小倩子');
COMMIT;
-------- 以上为:数据准备 --------
DECLARE
TYPE stu_id_table IS TABLE OF system.stu.s_id%TYPE;
TYPE stu_xm_table IS TABLE OF system.stu.s_xm%TYPE;
v_id stu_id_table;
v_xm stu_xm_table;
v_sql VARCHAR2(2000);
BEGIN
v_sql := 'UPDATE stu t SET t.s_xm = ''a'' WHERE t.s_id <= :b1
RETURNING t.s_id, t.s_xm INTO :b2, :b3';
EXECUTE IMMEDIATE v_sql USING 3 RETURNING BULK COLLECT INTO v_id, v_xm;
ROLLBACK;
FOR i IN v_id.first .. v_id.last LOOP
dbms_output.put_line(v_id(i));
END LOOP;
dbms_output.new_line();
FOR i IN v_xm.first .. v_xm.last LOOP
dbms_output.put_line(v_xm(i));
END LOOP;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLCODE || ' : ' || SQLERRM);
dbms_output.put_line(dbms_utility.format_error_backtrace);
END;
测试结果:
2.2.1.2 returning 不支持 record
TRUNCATE TABLE stu;
INSERT INTO stu(s_id, s_xm) VALUES(1, '小游子');
INSERT INTO stu(s_id, s_xm) VALUES(2, '小优子');
INSERT INTO stu(s_id, s_xm) VALUES(3, '小倩子');
COMMIT;
-------- 以上为:数据准备 --------
DECLARE
TYPE stu_record IS RECORD(
v_id system.stu.s_id%TYPE,
v_xm system.stu.s_xm%TYPE);
TYPE stu_table IS TABLE OF stu_record INDEX BY PLS_INTEGER;
v_stu_table stu_table;
v_sql VARCHAR2(30);
BEGIN
v_sql := 'UPDATE stu t SET t.s_xm = ''a'' WHERE t.s_id := b1
RETURNING t.s_id, t.s_xm INTO :b2';
EXECUTE IMMEDIATE v_sql USING 1 RETURNING BULK COLLECT INTO v_stu_table;
ROLLBACK;
FOR i IN v_stu_table.first .. v_stu_table.last LOOP
dbms_output.put_line(v_stu_table(i).v_id||' : '||v_stu_table(i).v_xm);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLCODE || ' : ' || SQLERRM);
dbms_output.put_line(dbms_utility.format_error_backtrace);
END;
测试结果: