Example 1: Bulk Query The department number "10" and print them out.
DECLARE
TYPE Emp_table_type is table of My_emp%rowtype INDEX by Binary_integer;
V_emp_table Emp_table_type;
BEGIN
SELECT * BULK COLLECT into v_emp_table from My_emp WHERE deptno=&deptno;
For I in 1..v_emp_table. COUNT LOOP
Dbms_output.put_line (' employee_info: ' | | V_emp_table (i). ename| |
','|| V_emp_table (i). job| |
','|| V_emp_table (i). HireDate);
End LOOP;
End;
Description section:
1. DECLARE the section below that you want to declare
2. Type declaration is the name of type Emp_table_typ E type
3. Is table of specifies the array type of the table of a collection, simply a data type that can store more than one row of rows, my_emp indicates on which table (the existing table)%rowtype the data type of the row on the table.
4. Index by Binary_integer refers to the indexing organization type
5. v_emp_table defines a variable to store the collection data type
6. BULK COLLECT into refers to a batch aggregation type, which, in simple terms, can store a multiline, multiple-column storage type, into which the following specifies where to come from,
7. V_emp_table. Count is used to v_emp_table the quantity inside.
8. (i) indication of the next label
Example 2: Batch update employee pay with department number "10"
DECLARE
TYPE Ename_table_type is table of My_emp.ename%type;
TYPE Sal_table_type is table of My_emp.sal%type;
V_ename_table Ename_table_type;
V_sal_table Sal_table_type;
BEGIN
UPDATE my_emp SET sal=sal*1.20 WHERE Deptno=&deptno returning ename,sal
BULK COLLECT into v_ename_table,v_sal_table;
For I in 1..v_ename_table. COUNT LOOP
Dbms_output.put_line (' employee_info: ' | | V_ename_table (i) | | | V_sal_table (i));
End LOOP;
End;
Description section:
1.%type and My_emp.ename data types
V_ename_table Ename_table_type;
2. V_sal_table Sal_table_type; Define variables to store them.
3. Returning is used to specify the part to be returned, BULK COLLECT into a mass aggregation type
4. Print them out with a for
(summary):
1. Open the set serveroutput on the experiment
2. The above examples are intended primarily to improve performance. The performance here mainly refers to speed. If the integrated from the entire OLTP or OLAP system is a different story, not discussed here.
3. Speed refers to the bulk INSERT, UPDATE, Delete, why will the speed increase?
4. The extracted data is processed in memory because it is common knowledge that the memory is processed more quickly.
5. Here are a few examples to see for yourselves.
Example 3: BULK INSERT
CREATE TABLE my_emp (empno number (4), ename VARCHAR2 (15))
/
DECLARE
TYPE Empno_table_type is Table of number (4) INDEX by Binary_integer;
TYPE Ename_table_type is Table of VARCHAR2 (a) INDEX by Binary_integer;
V_empno_table Empno_table_type;
V_ename_table Ename_table_type;
BEGIN
For I in 1..1000 loop
V_empno_table (i): =i+2000;
V_ename_table (i): = ' NAME ' | | To_char (i);
End LOOP;
ForAll i in 1..v_empno_table. COUNT inserts into My_emp VALUES (v_empno_table (i), v_ename_table (i));
End;
/
Example 4: Batch Update
CREATE TABLE my_emp (empno number (4), ename VARCHAR2 (15))
/
DECLARE
TYPE Empno_table_type is table of My_emp.empno%type INDEX by Binary_integer;
TYPE Ename_table_type is table of My_emp.ename%type INDEX by Binary_integer;
V_empno_table Empno_table_type;
V_ename_table Ename_table_type;
BEGIN
For I in 1..1000 loop
V_empno_table (i): =i+2000;
V_ename_table (i): = ' CN ' | | To_char (i);
End LOOP;
ForAll i in 1..v_empno_table. COUNT UPDATE my_emp SET ename=v_ename_table (i) WHERE empno=v_empno_table (i);
End;
/
Example 5: Bulk deletion
DECLARE
TYPE Empno_table_type is table of My_emp.empno%type INDEX by Binary_integer;
V_empno_table Empno_table_type;
BEGIN
For I in 1..8 loop
V_empno_table (i): =i+2000;
End LOOP;
ForAll i in 1..3 deletes from my_emp WHERE empno=v_empno_table (i);
End;
/
Example 6:sql%bulk_rowcount Property
DECLARE
TYPE Deptno_table_type is Table of number (2);
V_deptno_table Deptno_table_type:=deptno_table_type (10,20,30);
BEGIN
ForAll i in 1. V_deptno_table. COUNT UPDATE emp2 SET sal=sal*0.5 WHERE deptno=v_deptno_table (i);
Dbms_output.put_line (' department_10: ' | | Sql%bulk_rowcount (1) | | | ' rows ');
Dbms_output.put_line (' department_10: ' | | Sql%bulk_rowcount (1) | | | ' rows ');
Dbms_output.put_line (' department_20: ' | | Sql%bulk_rowcount (2) | | | ' rows ');
Dbms_output.put_line (' department_30: ' | | Sql%bulk_rowcount (3) | | | ' rows ');
End;
/
Sql%bulk_rowcount (i) represents the number of rows that the ForAll statement acts on