一、UPDATE 语句
语法如下
UPDATE table
SET column = value [, column = value, ......]
WHERE condition;
二、使用RETURNING子句
所有的DML语句都可以使用RETURNING子句来获取操作后的值。
INSERT 、UPDATE 、DELETE 语句中的RETURNING子句的语法如下。
INSERT INTO <table> (c1, c2, .. ,cn) VALUES (v1, v2, ... ,vn) RETURNING <expression> INTO <variables>
UPDATE <table> SET (c1) = (v1) , (c2) = (v2), (cn) = (vn) WHERE <condition> RETURNING <expression> INTO <variables>
DELETE FROM <table> WHERE <condition> RETURNING <expression> INTO <variables>
INSERT 语句,返回的是添加后的值。
UPDATE语句,返回的是更新后的值。
DELETE语句,返回的是删除前的值。
DECLARE v_id NUMBER;
BEGIN
INSERT INTO "TEST"."BUSER"("ID", "ACCOUNT", "EMAIL", "MOBILE", "PASSWORD", "NAME", "PIC_PATH", "BIRTH", "SEX", "TIME", "STATUS") VALUES ('2', 'zhangsan', 'zhangsan@qq.com', '13400010002', '13400010002', '张三', NULL, '19900101', 's', '20210311', '0') RETURNING id INTO v_id;
DBMS_OUTPUT.put_line('add id:' || v_id);
END;