ORACLE PL/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中顯式地使用一個對象構造函數。