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>