ORACLE PL/SQL 記錄(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> ,address_in.street_address1</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> individual_address.individual.individual_id := 6;</p><p> individual_address.individual.first_name := 'Ruldolph';</p><p> individual_address.individual.middle_initial := '';</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> individual_address.address.street_address1 := '89th St';</p><p> individual_address.address.street_address2 := '';</p><p> individual_address.address.street_address3 := '';</p><p> individual_address.address.city := 'New York City';</p><p> individual_address.address.state := 'NY';</p><p> individual_address.address.postal_code := '10028';</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> 

 

對象

 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> ,address_in.street_address1</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(4,7,'20 Sagamore Hill','',''</p><p> ,'Oyster Bay','NY','11771-1899','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 />

其中的INDIVIDUAL_ADDRESS_RECORD、INDIVIDUAL_RECORD和ADDRESS _RECORD都是在前面的程式裡定義過的物件類型，它們已經儲存在資料庫中，所以不需要重新定義。這種用法的代碼聲明塊會變得更短，而且對象初始化的過程中也比記錄類型初始化要簡潔。

 

三、從函數中返回記錄、物件類型的值

 

記錄

在定義記錄類型作為函數傳回值的時候，你只有一種選擇。在定義函數以前，你必須先定義一個顯式記錄類型，並且不能使用%ROWTYPE屬性定義這個記錄類型。

如下例所示：

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> 

 

對象

和記錄類型一樣，物件類型也可以作為函數的傳回型別。只是在定義函數以前，必須先顯示定義一個物件類型。

如下例所示：

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> 

 

仔細觀察上面兩個例子。當遊標返回一行記錄的時候，它隱式地返回了一個%ROWTYPE結構。

在使用記錄作為函數傳回型別的例子中，返回遊標的時候，PL/SQL會隱式管理運行時的轉換。這雖然可以簡化編碼，但是卻會帶來一些問題。例如，如果底層表的結構發生了變化，而記錄的結構卻保持不變，程式通常就會出錯。

在使用對象作為函數傳回型別的例子中，PL/SQL引擎並沒有隱式地向物件類型拋出一個%ROWTYPE。因此，必須在for-loop中顯式地使用一個物件建構函數。