Oracle PLSQL Advanced Programming

1.1 index table
The index table stores data in the memory !!!
1.1.1 define index table
-- Define a record set
Type yang_rec is record (ename varchar2 (30), Eid number );
-- Define the index table Type
Type yang_tab is table of yang_rec index by binary_integer;
-- Define an instance of the index table object
Test_tab yang_tab;
1.1.2 Insert entries into the index table
Each element in the index table is identified by a unique integer value (index. When referencing a value in a table, you must provide an index for this value.
Index range: 1 ---- 2147483647. The index value can be discontinuous, and PL/SQL does not reserve space for unused entries.
For example:
Declare
Cursor all_emps is select * from employee order by emp_id;
Type emp_table is table of employee % rowtype index by binary_integer;
EMPs emp_table;
Emps_max binary_integer;
Begin
Emps_max: = 0;
For EMP in all_emps Loop
Emps_max: = emps_max + 1;
EMPs (emps_max). emp_id: = EMP. emp_id;
EMPs (emps_max). emp_name: = EMP. emp_name;
End loop;
End;
/
1.1.3 perform operations on the index table
1) insert: see the preceding example.
2) reference:
If EMPs. exist (10) then
Dbms_output.put_line ('There are 10th records. ');
End if;
3) Modify:
Modify the 100th entries in the EMPs table:
EMPs (100). emp_name: = 'yang linker ';
4) Delete:
-- Delete the 100th entries in the EMPs table:
EMPs. Delete (100 );
Advanced PL/SQL programming
4th pages 4 pages
-- Delete entries in the EMPs table from 1 to 100:
EMPs. Delete (1,100 );
-- Delete all entries in the EMPs table:
EMPs. Delete;
1.1.4 index table functions
1) count: number of returned table entries:
Num_rows: = EMPs. count;
2) exists: if the specified entry exists, true is returned; otherwise, false is returned.
If EMPs. exist (10) then
Dbms_output.put_line ('There are 10th records. ');
End if;
3) limit: This method returns the maximum number of elements that a set can contain. The upper limit is available only for variable-length arrays. Use Limit for nested tables and Indexes
The return value is null.
4) frist: This method returns the smallest index value used in the set.
5) Last: This method returns the maximum index value used in the set.
6) Next: This method returns the next index value currently used in the collection.
7) Prior: This method returns the value of the previous index currently used in the collection.
8) Delete: delete entries in the set. For details, see "deleting.
9) TRIM: deletes one or more entries from the end of the set without returning values. It is only applicable to variable-length arrays and nested tables.
EMPs. Trim (1); -- deletes an entry from the end of the set.
EMPs. Trim (3); -- deletes three entries from the end of the set.
10) Extend: Add an entry at the end of the set or copy an existing entry. It is only applicable to variable-length arrays and nested tables.
EMPs. Extend (1); -- Add an entry from the end of the Set
EMPs. Extend (3); -- add three entries from the end of the Set
EMPs. Extend (1, 3); -- copy the third entry of the set and add it to the end of the table.
1.2 nested tables
Save data in memory !!!
A nested table is a set of unordered records.
When retrieving nested tables in a database, the index of entries is continuous and the index value cannot be skipped as it is.
You need to use constructors to initialize nested tables.
Nested tables cannot be of the following data types:
Boolean, nchar, nclob, nvarchar2, ref cursor, table, varray, not null.
The definition of a nested table is similar to that of an index table, but the index by clause cannot be used.
1.2.1 initialize nested tables
You must use the constructor to initialize a nested table before you can add entries to it!
-- Define the index table Type
Type emp_tab is table of EMP % rowtype;
-- Define an instance of the index table object
Advanced PL/SQL programming
5th pages 5 pages
EMPs emp_tab;
-- Initialize a nested table
EMPs: = emp_tab ();
1.2.2 extended nested tables
Extend is used to extend the data of nested tables to the memory.
Declare
Cursor all_emps is select * from EMP order by empno;
Type emp_table is table of EMP % rowtype;
EMPs emp_table;
I pls_integer;
Rochelle count pls_integer;
Begin
Rochelle count: = 0;
EMPs: = emp_table (); -- initialize the nested table and generate an empty record
For C1 in all_emps Loop
Rochelle count: = Rochelle count + 1;
EMPs. Extend;
EMPs (l_count). empno: = c1.empno;
EMPs (l_count). ename: = c1.ename;
EMPs (l_count). Job: = c1.job;
EMPs (l_count). Mgr: = c1.mgr;
EMPs (l_count). hiredate: = c1.hiredate;
EMPs (l_count). SAL: = c1.sal;
EMPs (l_count). COMM: = c1.comm;
EMPs (l_count). deptno: = c1.deptno;
End loop;
-- Clone the first entry five times
EMPs. Extend (5, 1 );
For I in 1 .. l_count + 5 Loop
Dbms_output.put_line (to_char (EMPs (I). empno) | ''| MPs (I). ename );
End loop;
End;
/
1.2.3 Delete entries in a nested table
1) Delete method:
EMPs. Delete (10); -- delete 10th entries in a nested table.
Note: After the entries in the nested table are deleted, the entries in the nested table are not re-numbered and can still be used.
2) trim method:
The TRIM method deletes a specified number of entries at the end of the table.
The TRIM method can only be used for nested tables and variable-length arrays.
Declare
Cursor all_emps is select * from EMP order by empno;
Type emp_table is table of EMP % rowtype;
EMPs emp_table;
I pls_integer;
Rochelle count pls_integer;
Begin
Advanced PL/SQL programming
6th pages 6 pages
Rochelle count: = 0;
-- Initialize the nested table and generate an empty record
EMPs: = emp_table ();
For C1 in all_emps Loop
Rochelle count: = Rochelle count + 1;
EMPs. Extend;
EMPs (l_count). empno: = c1.empno;
EMPs (l_count). ename: = c1.ename;
EMPs (l_count). Job: = c1.job;
EMPs (l_count). Mgr: = c1.mgr;
EMPs (l_count). hiredate: = c1.hiredate;
EMPs (l_count). SAL: = c1.sal;
EMPs (l_count). COMM: = c1.comm;
EMPs (l_count). deptno: = c1.deptno;
End loop;
-- Clone the first entry five times
EMPs. Extend (5, 1 );
-- Trim off the five clones of entry #1
EMPs. Trim (5 );
-- Delete the first entry
EMPs. Delete (1 );
For I in 1 .. l_count + 5 Loop
Dbms_output.put_line (to_char (EMPs (I). empno) | ''| EMPs (I). ename );
End loop;
End;
/
Note: debug the above Code and pay attention to the error message !!!
1.3 Variable Length Array
The variable length array is similar to the nested table, but the maximum length of the Variable Length array is fixed.
Variable-length arrays must be initialized like nested tables.
1.3.1 define a variable-length Array
-- Defines a variable-length array with a maximum length of 100.
Type type_name is varray (100) of varchar2 (20 );
1.3.2 extended variable-length Array
Similar to a nested table, but cannot exceed the maximum length. A routine is similar to a nested table routine.
1.4 batch binding
PL/SQL batch binding is a new feature in Oracle8i.
1) use Bulk collect
2) use forall
Advanced PL/SQL programming
7th pages 7 pages
Routine:
Declare
Cursor C1 is select empno, ename from EMP;
Type eno_tab is table of EMP. empno % type;
Type ename_tab is table of EMP. ename % type;
Rochelle eno_tab;
Rochelle enames ename_tab;
Begin
Open C1;
Fetch C1 bulk collect into maid, l_enames;
Close C1;
For I in 1 .. l_enos.count Loop
Dbms_output.put_line (to_char (maid (I) | ''| l_enames (I ));
End loop;
Forall I in l_enos.first .. l_enos.last
Update EMP set ename = maid (I) Where empno = maid (I );
End;
/
1.5 collection Exception Handling
Exception cause
Collection_is_null tries to use it before the constructor initializes the set.
No_data_found tries to access entries that do not exist in the collection
Subcript_beyond_count: The subscript used exceeds the current number of elements in the set.
Subcript_outside_limit the subscript used in the variable-length array is greater than the maximum value specified in the variable-length array declaration.
Value_error uses a subscript that cannot be converted to an integer.