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
|
Declare
cBatchsize constant Pls_Integer := 3;
Type rEmployee Is Record (
employee_id employees.employee_id%Type,
first_name employees.first_name%Type,
last_name employees.last_name%Type,
email employees.email%Type,
phone_number employees.phone_number%Type,
hire_date employees.hire_date%Type,
job_id employees.job_id%Type,
salary employees.salary%Type,
commission_pct employees.commission_pct%Type,
manager_id employees.manager_id%Type,
department_id employees.department_id%Type
);
Type Employee_va Is Varray(1000) Of rEmployee;
l_Employees Employee_va;
--
Cursor CrsEmployee(department_id employees.department_id%Type) Return employees%Rowtype Is
Select t.employee_id,
t.first_name,
t.last_name,
t.email,
t.phone_number,
t.hire_date,
t.job_id,
t.salary,
t.commission_pct,
t.manager_id,
t.department_id
From hr.employees t
Where t.department_id = CrsEmployee.department_id;
Begin
Open CrsEmployee(60);
--
Loop
Fetch CrsEmployee Bulk Collect Into l_Employees Limit cBatchsize;
--
For i In 1..l_Employees.Count
Loop
Dbms_Output.Put_Line(l_Employees(i).employee_id);
End Loop;
--
Exit When l_Employees.Count() < cBatchsize;
End Loop;
--
Close CrsEmployee;
End; |
Partager