Select from update row implementation, updaterow

Select from update row implementation, updaterow

At the DTCC conference, Alibaba's speech mentioned: select from update hot row;

I don't understand how to implement it in Oracle. It means to implement the update and select update field information in an SQL statement.

According to dbsnake, we learned that this is an imitation of the returning into clause of Oracle. You can select the values of the specified columns of the Row Records affected by the DML statement.

Examples are provided in the official documentation:

Http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/tuning.htm

You can useBULK COLLECTClause inRETURNING INTOClause ofINSERT,UPDATE, OrDELETEStatement:

Example 11-15 Using bulk collect With the returning into Clause

CREATE TABLE emp_temp AS SELECT * FROM employees;DECLARE   TYPE NumList IS TABLE OF employees.employee_id%TYPE;   enums NumList;   TYPE NameList IS TABLE OF employees.last_name%TYPE;   names NameList;BEGIN   DELETE FROM emp_temp WHERE department_id = 30      RETURNING employee_id, last_name BULK COLLECT INTO enums, names;   DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows:');   FOR i IN enums.FIRST .. enums.LAST   LOOP      DBMS_OUTPUT.PUT_LINE('Employee #' || enums(i) || ': ' || names(i));   END LOOP;END;/

Note: The set serveroutput on clause must be executed in sqlplus..

The above example is not very familiar with PLSQL. It is described in a simple example:

1. Create a test table:

create table tbl_returninto(id number,remark varchar2(5));

SQL> select * from tbl_returninto;ID REMARK---------- -------------------------------------------------- 2 one 3 two 4 three

2. Insert a record and use returning into to get the inserted id value in the same SQL statement:

SQL> declare  2  l_id tbl_returninto.id%type;  3  begin  4  insert into tbl_returninto values(tr_seq.nextval, 'one')  5  returning id into l_id;  6  commit;  7  dbms_output.put_line('id=' || l_id);  8  end;  9  /id=1PL/SQL procedure successfully completed.

3. Update and delete a record. Use returning into to obtain the updated and deleted id values:

SQL> declare l_id tbl_returninto.id%type;  2  begin  3  update tbl_returninto  4  set remark = 'one2'  5  where id = 2  6  returning id into l_id;  7  dbms_output.put_line('UPDATE ID=' || l_id);  8  delete from tbl_returninto where remark = 'three'  9  returning id into l_id; 10  dbms_output.put_line('DELETE ID=' || l_id); 11  commit; 12  end; 13  /UPDATE ID=2DELETE ID=4PL/SQL procedure successfully completed.

Summary:

Using the returning into clause, you can select the specified field information for the rows affected by insert, update, and delete in an SQL statement. insert and update are the results after execution, delete is the result before execution. Of course, the PLSQL syntax is used here.