ORACLEPL/SQL记录(Record)学习笔记(二)

二、以形参的形式定义和使用记录、对象类型

在用作形式参数时，记录类型和对象类型有很多相同之处。在将它们作为游标、函数或过程的形式参数以前，事先都必须定义一个记录类型或者对象类型。

如下例所示：

 

记录

DECLARE -- Define a record type. TYPE individual_record IS RECORD (individual_id INTEGER ,first_name VARCHAR2(30 CHAR) ,middle_initial VARCHAR2(1 CHAR) ,last_name VARCHAR2(30 CHAR)); -- Define a record type. TYPE address_record IS RECORD (address_id INTEGER ,individual_id INTEGER ,street_address1 VARCHAR2(30 CHAR) ,street_address2 VARCHAR2(30 CHAR) ,street_address3 VARCHAR2(30 CHAR) ,city VARCHAR2(20 CHAR) ,state VARCHAR2(20 CHAR) ,postal_code VARCHAR2(20 CHAR) ,country_code VARCHAR2(10 CHAR)); -- Define a record type of two user defined record type variables. TYPE individual_address_record IS RECORD (individual INDIVIDUAL_RECORD ,address ADDRESS_RECORD); -- Define a variable of a user defined compound record type. individual_address INDIVIDUAL_ADDRESS_RECORD; -- Define a local procedure to manage addresses inserts. PROCEDURE insert_address (address_in ADDRESS_RECORD) IS BEGIN -- Insert the values into the target object. INSERT INTO addresses VALUES (address_in.address_id ,address_in.individual_id ,address_in.street_address1 ,address_in.street_address2 ,address_in.street_address3 ,address_in.city ,address_in.state ,address_in.postal_code ,address_in.country_code); END insert_address; -- Define a local procedure to manage addresses inserts. PROCEDURE insert_individual (individual_in INDIVIDUAL_RECORD) IS BEGIN -- Insert the values into the table. INSERT INTO individuals VALUES (individual_in.individual_id ,individual_in.first_name ,individual_in.middle_initial ,individual_in.last_name); END insert_individual; BEGIN -- Initialize the field values for the record. individual_address.individual.individual_id := 6; individual_address.individual.first_name := 'Ruldolph'; individual_address.individual.middle_initial := ''; individual_address.individual.last_name := 'Gulianni'; -- Initialize the field values for the record. individual_address.address.address_id := 3; individual_address.address.individual_id := 6; individual_address.address.street_address1 := '89th St'; individual_address.address.street_address2 := ''; individual_address.address.street_address3 := ''; individual_address.address.city := 'New York City'; individual_address.address.state := 'NY'; individual_address.address.postal_code := '10028'; individual_address.address.country_code := 'USA'; -- Create a savepoint. SAVEPOINT addressbook; -- Process object subtypes. insert_individual(individual_address.individual); insert_address(individual_address.address); -- Commit the record. COMMIT;EXCEPTION -- Rollback to savepoint on error. WHEN OTHERS THEN ROLLBACK to addressbook; RETURN;END;/  

 

对象

 DECLARE -- Define a variable of the record type. individual_address INDIVIDUAL_ADDRESS_RECORD; -- Define a local procedure to manage addresses inserts. PROCEDURE insert_address (address_in ADDRESS_RECORD) IS BEGIN -- Insert the values into the target object. INSERT INTO addresses VALUES (address_in.address_id ,address_in.individual_id ,address_in.street_address1 ,address_in.street_address2 ,address_in.street_address3 ,address_in.city ,address_in.state ,address_in.postal_code ,address_in.country_code); END insert_address; -- Define a local procedure to manage addresses inserts. PROCEDURE insert_individual (individual_in INDIVIDUAL_RECORD) IS BEGIN -- Insert the values into the table. INSERT INTO individuals VALUES (individual_in.individual_id ,individual_in.first_name ,individual_in.middle_initial ,individual_in.last_name); END insert_individual; BEGIN -- Construct an instance of the object type and assign it. -- This uses two nested constructors within the constructor. individual_address := individual_address_record( individual_record(7,'Quentin','','Roosevelt'), address_record(4,7,'20 Sagamore Hill','','' ,'Oyster Bay','NY','11771-1899','USA')); -- Create a savepoint. SAVEPOINT addressbook; -- Process object subtypes. insert_individual(individual_address.individual); insert_address(individual_address.address); -- Commit the record. COMMIT;EXCEPTION -- Rollback to savepoint on error. WHEN OTHERS THEN ROLLBACK to addressbook; RETURN; END;/

其中的INDIVIDUAL_ADDRESS_RECORD、INDIVIDUAL_RECORD和ADDRESS _RECORD都是在前面的程序里定义过的对象类型，它们已经存储在数据库中，所以不需要重新定义。这种用法的代码声明块会变得更短，而且对象初始化的过程中也比记录类型初始化要简洁。

 

三、从函数中返回记录、对象类型的值

 

记录

在定义记录类型作为函数返回值的时候，你只有一种选择。在定义函数以前，你必须先定义一个显式记录类型，并且不能使用%ROWTYPE属性定义这个记录类型。

如下例所示：

DECLARE -- Define a record type. TYPE individual_record IS RECORD (individual_id INTEGER ,first_name VARCHAR2(30 CHAR) ,middle_initial individuals.middle_initial%TYPE ,last_name VARCHAR2(30 CHAR)); -- Define a variable of the record type. individual INDIVIDUAL_RECORD; -- Define a local function to return a record type. FUNCTION get_row (individual_id_in INTEGER) RETURN INDIVIDUAL_RECORD IS -- Define a cursor to return a row of individuals. CURSOR c (individual_id_cursor INTEGER) IS SELECT * FROM individuals WHERE individual_id = individual_id_cursor; BEGIN -- Loop through the cursor for a single row. FOR i IN c(individual_id_in) LOOP -- Return a %ROWTYPE from the INDIVIDUALS table. RETURN i; END LOOP; END get_row;BEGIN -- Demonstrate function return variable assignment. individual := get_row(1); -- Display results. dbms_output.put_line(CHR(10)); dbms_output.put_line('INDIVIDUAL_ID : '||individual.individual_id); dbms_output.put_line('FIRST_NAME : '||individual.first_name); dbms_output.put_line('MIDDLE_INITIAL : '||individual.middle_initial); dbms_output.put_line('LAST_NAME : '||individual.last_name); END;/  

 

对象

和记录类型一样，对象类型也可以作为函数的返回类型。只是在定义函数以前，必须先显示定义一个对象类型。

如下例所示：

DECLARE -- Define a variable of the record type. individual INDIVIDUAL_RECORD; -- Define a local function to return a record type. FUNCTION get_row (individual_id_in INTEGER) RETURN INDIVIDUAL_RECORD IS -- Define a cursor to return a row of individuals. CURSOR c (individual_id_cursor INTEGER) IS SELECT * FROM individuals WHERE individual_id = individual_id_cursor; BEGIN -- Loop through the cursor for a single row. FOR i IN c(individual_id_in) LOOP -- Return a constructed object from the %ROWTYPE. RETURN individual_record(i.individual_id ,i.first_name ,i.middle_initial ,i.last_name); END LOOP; END get_row; BEGIN -- Demonstrate function return variable assignment. individual := get_row(1); -- Display results. dbms_output.put_line(CHR(10)); dbms_output.put_line('INDIVIDUAL_ID : '||individual.individual_id); dbms_output.put_line('FIRST_NAME : '||individual.first_name); dbms_output.put_line('MIDDLE_INITIAL : '||individual.middle_initial); dbms_output.put_line('LAST_NAME : '||individual.last_name); END;/  

 

仔细观察上面两个例子。当游标返回一行记录的时候，它隐式地返回了一个%ROWTYPE结构。

在使用记录作为函数返回类型的例子中，返回游标的时候，PL/SQL会隐式管理运行时的转换。这虽然可以简化编码，但是却会带来一些问题。例如，如果底层表的结构发生了变化，而记录的结构却保持不变，程序通常就会出错。

在使用对象作为函数返回类型的例子中，PL/SQL引擎并没有隐式地向对象类型抛出一个%ROWTYPE。因此，必须在for-loop中显式地使用一个对象构造函数。