DML语句的Returning...Into...

Rturning 列名|值 [bulk collect] into variable_name|collect_variable_name[,......]

SQL> Declare

  2    vID Number;
  3    vNM Varchar2(20);
  4    Type t_stu Is Table Of stu%Rowtype;
  5    v_stu t_stu;
  6    i Number;
  7  Begin
  8    dbms_output.put_line('*******insert*******');
  9    I nsert Into stu Values(1,'张三') Returning Id,nm Into vID,vNM;
 10    dbms_output.put_line('ID:'||vID||' NM:'||vNM);
 11    Insert Into stu Values(2,'李四') Returning Id,nm Into vID,vNM;
 12    dbms_output.put_line('ID:'||vID||' NM:'||vNM);
 13    dbms_output.put_line('*******update*******');
 14     Update stu Set nm=nm||'a' Returning Id,nm Bulk Collect Into v_stu;
 15    For i In v_stu.first .. v_stu.last Loop
 16        dbms_output.put_line('ID:'||v_stu(i).id||' NM:'||v_stu(i).nm);
 17    End Loop update结果输出;
 18    dbms_output.put_line('*******delete*******');
 19     Delete stu Returning Id,nm Bulk Collect Into v_stu;
 20      For i In v_stu.first .. v_stu.last Loop
 21        dbms_output.put_line('ID:'||v_stu(i).id||' NM:'||v_stu(i).nm);
 22    End Loop delete结果输出;
 23    Rollback;
 24  End;
 25  /
*******insert*******
ID:1 NM:张三
ID:2 NM:李四
*******update*******
ID:1 NM:张三a
ID:2 NM:李四a
*******delete*******
ID:1 NM:张三a
ID:2 NM:李四a
PL/SQL procedure successfully completed


SQL> 
SQL> Declare
  2    vID Number;
  3    vNM Varchar2(20);
  4  Begin
  5     Insert Into stu Select 1,'张三' From dual Returning Id,nm Into vID,vNM;(不支持)
  6    dbms_output.put_line('ID:'||vID||' NM:'||vNM);
  7    Rollback;
  8  End;
  9  /
Declare
  vID Number;
  vNM Varchar2(20);
Begin
  Insert Into stu Select 1,'张三' From dual Returning Id,nm Into vID,vNM;
  dbms_output.put_line('ID:'||vID||' NM:'||vNM);
  Rollback;
End;
ORA-06550: 第 5 行, 第 55 列: 
PL/SQL: ORA-00933: SQL 命令未正确结束
ORA-06550: 第 5 行, 第 3 列: 
PL/SQL: SQL Statement ignored


SQL> 
SQL> Declare
  2    vID Number;
  3  Begin
  4    Merge Into stu a Using(Select Level As Id From dual Connect By Level<=1) b On(a.id=b.id)
  5    When Not Matched Then
  6         Insert (Id) Values(b.id) Returning Id Bulk Collect Into vID ;(不支持)
  7  End;
  8  /
Declare
  vID Number;
Begin
  Merge Into stu a Using(Select Level As Id From dual Connect By Level<=1) b On(a.id=b.id)
  When Not Matched Then
       Insert (Id) Values(b.id) Returning Id Bulk Collect Into vID ;
End;
ORA-06550: 第 6 行, 第 43 列: 
PL/SQL: ORA-00933: SQL 命令未正确结束
ORA-06550: 第 4 行, 第 3 列: 
PL/SQL: SQL Statement ignored


SQL>