[Oracle] PL/SQL set Enhancement

[Oracle] PL/SQL set Enhancement

Builder.com

29/12/2004

URL: http://www.zdnet.com.cn/developer/database/story/0,3800066906,39265363,00.htm

Oracle Release 2 has greatly enhanced the PL/SQL collection function. It can performCodeMaintenance. For example, the PL/SQL set can be used as a single parameter in Release 2, thus avoiding the use of a long string of individual fields as parameters.

Another advantage is that the record set can now be the target of the bulk collection into clause. Before Release 2, we must create a set for each returned column. For example:

Declare

Type deptno_coll is table of dept. deptno % type;

Type dname_coll is table of dept. dname % type;

Type loc_coll is table of dept. Loc % type;

Deptno_list deptno_coll;

Dname_list dname_coll;

Loc_list loc_coll;

Begin

Select * Bulk collect into deptno_list, dname_list, loc_list from Dept;

End;

Changed:

Declare

Type dept_coll is table of dept % rowtype;

Dept_list dept_coll;

Begin

Select * Bulk collect into dept_list from Dept;

End;

Note that the field name is no longer referenced here. If the number or name of fields in the dept table is modified, you do not need to modify the second sample code. This code is easier to maintain than the old version. However, applicationsProgramA Release 2 database is required for compilation.

Now, you can insert a row of data into a table using a set of records. Before Release 2, you must insert a PL/SQL record into the table and then separately determine each field. The procedure is as follows:

Declare

Dept_row dept % rowtype;

Begin

/* Populate dept_row ...*/

Insert into dept values (dept_row.deptno, dept_row.dname, dept_row.loc );

End;

In Release 2, the insert statement can be simplified:

Insert into dept values dept_row;

Records can be used to update data in a table in a set manner. Like the insert statement, each field must be updated separately before:

Update Dept

Set deptno = dept_row.deptno,

Dname = dept_row.dname,

Loc = dept_row.loc

Where deptno = dept_row.deptno;

In Release 2, the entire data can be updated based on the position in the record:

Update dept set ROW = dept_row where deptno = dept_row.deptno;

(Note that "set row" is a new clause .)

You can use records to retrieve information from the returning clause of DML statements. It is usually useful to keep a local copy when updating data for subsequent PL/SQL operations. Before Release 2, we can use the returning clause to store data somewhere after computation and call are complete. In Release 2, data can be stored in a record. For example, the following is the practice before release 2:

Declare

Foorow footab % rowtype;

Begin

Insert into footab (foono, fooname) values (fooseq. nextval, 'foo ')

Returning foono, fooname into foorow. foono, foorow. fooname;

End;

Now we can simplify the insert statement:

Insert into footab (foono, fooname) values (fooseq. nextval, 'foo ')

Returning foono, fooname into foorow;

These enhancements make the syntax easier to read and help us avoid program errors caused by table Matching errors in returned statements. But remember that these enhancements depend on the physical location of the first field in the table and record. If the field position of a table changes, the field order also changes. Therefore, it is better to use rowtype matching fields in a database table than to explicitly list field names.

Scott Stephen has been working in Oracle for more than 13 years. He has worked in technical support, e-commerce, marketing, software development, and other departments. For More Oracle skills of Scott Stephen ens, visit our Oracle Dev tips library.

Responsible editor: Li Ning