1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61
|
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as mni
SQL>
SQL> set serveroutput on
SQL> Create Or Replace Type dept_obj As Object (
2 department_id number(4),
3 department_name varchar2(30),
4 manager_id number(6),
5 location_id number(4)
6 )
7 /
Type created
SQL> Declare
2 Type dept_aa Is Table Of dept_obj;
3 l_dept dept_aa;
4 sql_stmt constant varchar2(1000) := 'Delete departments Returning dept_obj(department_id, department_name, manager_id, location_id) Into :1';
5 Begin
6 Execute immediate sql_stmt RETURNING BULK COLLECT INTO l_dept;
7 --
8 Rollback;
9 --
10 For i In 1..L_dept.Count() Loop
11 dbms_output.put_line(l_dept(i).department_id||' '||l_dept(i).department_name);
12 End Loop;
13 End;
14 /
10 Administration
20 Marketing
30 Purchasing
40 Human Resources
50 Shipping
60 IT
70 Public Relations
80 Sales
90 Executive
100 Finance
110 Accounting
120 Treasury
130 Corporate Tax
140 Control And Credit
150 Shareholder Services
160 Benefits
170 Manufacturing
180 Construction
190 Contracting
200 Operations
210 IT Support
220 NOC
230 IT Helpdesk
240 Government Sales
250 Retail Sales
260 Recruiting
270 Payroll
PL/SQL procedure successfully completed
SQL> |
Partager