Oracle PL/SQL record learning notes (2)

Ii. Define and use records and object types as parameters

When used as a formal parameter, there are many similarities between the record type and the object type. Before using them as parameters in the form of a cursor, function, or procedure, you must define a record type or object type.

For example:

 

Record

Declare </P> <p> -- Define a record type. </P> <p> type individual_record is record </P> <p> (individual_id integer </P> <p>, first_name varchar2 (30 char) </P> <p>, middle_initial varchar2 (1 char) </P> <p>, last_name varchar2 (30 char )); </P> <p> -- Define a record type. </P> <p> type address_record is record </P> <p> (address_id integer </P> <p>, individual_id integer </P> <p>, street_address1 varchar2 (30 char) </P> <p>, street_address2 varchar2 (30 char) </P> <p>, street_address3 varchar2 (30 char) </P> <p>, city varchar2 (20 char) </P> <p>, State varchar2 (20 char) </P> <p>, postal_code varchar2 (20 char) </P> <p>, country_code varchar2 (10 char )); </P> <p> -- Define a record type of two user defined record type variables. </P> <p> type individual_address_record is record </P> <p> (individual individual_record </P> <p>, address address_record ); </P> <p> -- Define a variable of a user defined compound record type. </P> <p> individual_address individual_address_record; </P> <p> -- define a local procedure to manage addresses inserts. </P> <p> procedure insert_address </P> <p> (address_in address_record) is </P> <p> begin </P> <p> -- Insert the values into the target object. </P> <p> insert </P> <p> into addresses </P> <p> values </P> <p> (address_in.address_id </P> <p> >, address_in.individual_id </P> <p>, Region </P> <p>, address_in.street_address2 </P> <p>, address_in.street_address3 </P> <p>, address_in.city </P> <p>, address_in.state </P> <p>, address_in.postal_code </P> <p>, address_in.country_code); </P> <p> end insert_address; </P> <p> -- define a local procedure to manage addresses inserts. </P> <p> procedure insert_individual </P> <p> (individual_in individual_record) is </P> <p> begin </P> <p> -- Insert the values into the table. </P> <p> insert </P> <p> into individuals </P> <p> values </P> <p> (individual_in.individual_id </P> <p >, individual_in.first_name </P> <p>, individual_in.middle_initial </P> <p>, individual_in.last_name); </P> <p> end insert_individual; </P> <p> begin </P> <p> -- initialize the field values for the record. </P> <p> fingerprint: = 6; </P> <p> individual_address.individual.first_name: = 'ruldolph'; </P> <p> fingerprint: = ''; </P> <p> individual_address.individual.last_name: = 'gulianni'; </P> <p> -- initialize the field values for the record. </P> <p> individual_address.address.address_id: = 3; </P> <p> individual_address.address.individual_id: = 6; </P> <p> comment: = '89th st '; </P> <p> individual_address.address.street_address2: = ''; </P> <p> priority: =''; </P> <p> individual_address.address.city: = 'New York City'; </P> <p> individual_address.address.state: = 'ny '; </P> <p> individual_address.address.postal_code: = '2016 '; </P> <p> individual_address.address.country_code: = 'usa'; </P> <p> -- create a savepoint. </P> <p> savepoint addressbook; </P> <p> -- Process object subtypes. </P> <p> insert_individual (individual_address.individual); </P> <p> insert_address (individual_address.address); </P> <p> -- commit the record. </P> <p> commit; </P> <p> exception </P> <p> -- rollback to savepoint on error. </P> <p> when others then </P> <p> rollback to addressbook; </P> <p> return; </P> <p> end; </P> <p>/</P> <p> 

 

Object

 Declare </P> <p> -- Define a variable of the record type. </P> <p> individual_address individual_address_record; </P> <p> -- define a local procedure to manage addresses inserts. </P> <p> procedure insert_address </P> <p> (address_in address_record) is </P> <p> begin </P> <p> -- Insert the values into the target object. </P> <p> insert </P> <p> into addresses </P> <p> values </P> <p> (address_in.address_id </P> <p> >, address_in.individual_id </P> <p>, Region </P> <p>, address_in.street_address2 </P> <p>, address_in.street_address3 </P> <p>, address_in.city </P> <p>, address_in.state </P> <p>, address_in.postal_code </P> <p>, address_in.country_code); </P> <p> end insert_address; </P> <p> -- define a local procedure to manage addresses inserts. </P> <p> procedure insert_individual </P> <p> (individual_in individual_record) is </P> <p> begin </P> <p> -- Insert the values into the table. </P> <p> insert </P> <p> into individuals </P> <p> values </P> <p> (individual_in.individual_id </P> <p >, individual_in.first_name </P> <p>, individual_in.middle_initial </P> <p>, individual_in.last_name); </P> <p> end insert_individual; </P> <p> begin </P> <p> -- construct an instance of the object type and assign it. </P> <p> -- this uses two nested constructors within the constructor. </P> <p> individual_address: = </P> <p> individual_address_record (</P> <p> individual_record (7, 'quentin ','', 'roosevelt'), </P> <p> address_record (, '20 Sagamore Hill ', '','' </P> <p>, 'oyster Bay ', 'ny ', '2014-11771', 'USA '); </P> <p> -- create a savepoint. </P> <p> savepoint addressbook; </P> <p> -- Process object subtypes. </P> <p> insert_individual (individual_address.individual); </P> <p> insert_address (individual_address.address); </P> <p> -- commit the record. </P> <p> commit; </P> <p> exception </P> <p> -- rollback to savepoint on error. </P> <p> when others then </P> <p> rollback to addressbook; </P> <p> return; </P> <p> end; </P> <p>/<br/>

Here, individual_address_record, individual_record, and address _ record are the object types defined in the previous program. They are already stored in the database and therefore do not need to be redefined. The Code declaration block for this usage will become shorter, and the object initialization process is also simpler than the record type initialization.

 

3. Return record and object type values from the function

 

Record

When defining the record type as the function return value, you have only one option. Before defining a function, you must first define an explicit record type, and you cannot use the % rowtype attribute to define this record type.

For example:

Declare </P> <p> -- Define a record type. </P> <p> type individual_record is record </P> <p> (individual_id integer </P> <p>, first_name varchar2 (30 char) </P> <p>, middle_initial individuals. middle_initial % Type </P> <p>, last_name varchar2 (30 char); </P> <p> -- Define a variable of the record type. </P> <p> individual individual_record; </P> <p> -- define a local function to return a record type. </P> <p> function get_row </P> <p> (individual_id_in integer) </P> <p> return individual_record is </P> <p> -- Define a cursor to return a row of individuals. </P> <p> cursor C </P> <p> (individual_id_cursor integer) is </P> <p> select * </P> <p> from individuals </P> <p> where individual_id = individual_id_cursor; </P> <p> begin </P> <p> -- loop through the cursor for a single row. </P> <p> for I in C (individual_id_in) loop </P> <p> -- return a % rowtype from the individuals table. </P> <p> return I; </P> <p> end loop; </P> <p> end get_row; </P> <p> begin </P> <p> -- demonstrate function return variable assignment. </P> <p> individual: = get_row (1); </P> <p> -- display results. </P> <p> dbms_output.put_line (CHR (10); </P> <p> dbms_output.put_line ('individual _ ID: '| individual. individual_id); </P> <p> dbms_output.put_line ('first _ name: '| individual. first_name); </P> <p> dbms_output.put_line ('Middle _ initial: '| individual. middle_initial); </P> <p> dbms_output.put_line ('Last _ name: '| individual. last_name); </P> <p> end; </P> <p>/</P> <p> 

 

Object

Like the record type, the object type can also be used as the return type of the function. Before defining a function, you must display and define an object type.

For example:

Declare </P> <p> -- Define a variable of the record type. </P> <p> individual individual_record; </P> <p> -- define a local function to return a record type. </P> <p> function get_row </P> <p> (individual_id_in integer) </P> <p> return individual_record is </P> <p> -- Define a cursor to return a row of individuals. </P> <p> cursor C </P> <p> (individual_id_cursor integer) is </P> <p> select * </P> <p> from individuals </P> <p> where individual_id = individual_id_cursor; </P> <p> begin </P> <p> -- loop through the cursor for a single row. </P> <p> for I in C (individual_id_in) loop </P> <p> -- return a constructed object from the % rowtype. </P> <p> return individual_record (I. individual_id </P> <p>, I. first_name </P> <p>, I. middle_initial </P> <p>, I. last_name); </P> <p> end loop; </P> <p> end get_row; </P> <p> begin </P> <p> -- demonstrate function return variable assignment. </P> <p> individual: = get_row (1); </P> <p> -- display results. </P> <p> dbms_output.put_line (CHR (10); </P> <p> dbms_output.put_line ('individual _ ID: '| individual. individual_id); </P> <p> dbms_output.put_line ('first _ name: '| individual. first_name); </P> <p> dbms_output.put_line ('Middle _ initial: '| individual. middle_initial); </P> <p> dbms_output.put_line ('Last _ name: '| individual. last_name); </P> <p> end; </P> <p>/</P> <p> 

 

Take a closer look at the two examples above. When the cursor returns a row of records, it implicitly returns a % rowtype structure.

In the example of using records as the function return type, PL/SQL implicitly manages the conversion at runtime when the cursor is returned. Although this can simplify coding, it may cause some problems. For example, if the structure of the underlying table changes, but the record structure remains unchanged, the program usually fails.

In the example of using objects as function return types, PL/SQL engine does not implicitly throw a % rowtype to the object type. Therefore, an object constructor must be explicitly used in the for-loop.