Variables of record types can be passed as parameters to subprograms, just like variables of other commonly used data types.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition does not support this feature.
Example: Create and invoke subprograms with record-type parameters.
In the following example, two variables of record types are defined in two different ways:
r_ware1 TYPE_WARE;
r_ware2 ware%ROWTYPE;
Then, two methods are used to assign values to the record variables by using the SELECT INTO statement. Since the actual structures of the two variables are the same, the two variables can be assigned values: r_ware1 := r_ware2;
Finally, the record variables are passed as parameters to a subprogram to print the values of the variables.
obclient> CREATE TABLE ware (
w_id int
, w_ytd decimal(12,2)
, w_tax decimal(4,4)
, w_name varchar(10)
, w_street_1 varchar(20)
, w_street_2 varchar(20)
, w_city varchar(20)
, w_state char(2)
, w_zip char(9)
, unique(w_name, w_city)
, primary key(w_id)
);
Query OK, 0 rows affected
obclient> INSERT INTO ware VALUES(1, 1200, .1868, 'W_NAME_1', 'jTNkXKWXOdh',
'lf9QXTXXGoF04IZBkCP7', 'srRq15uvxe5', 'GQ', 506811111);
Query OK, 1 row affected
obclient> INSERT INTO ware VALUES(2, 1200, .0862, 'W_NAME_2', 'xEdT1jkENtbLwoI1Zb0',
'NT0j4RCQ4OqrS', 'vlwzndw2FPrO', 'XR', 063311111);
Query OK, 1 row affected
obclient> CREATE OR REPLACE PACKAGE pkg_ware_mgmt as
TYPE TYPE_WARE IS RECORD(
w_id ware.w_id%TYPE
,w_ytd ware.w_ytd%TYPE
,w_tax ware.w_tax%TYPE
,w_name ware.w_name%TYPE
,w_street_1 ware.w_street_1%TYPE
,w_street_2 ware.w_street_2%TYPE
,w_city ware.w_city%TYPE
,w_state ware.w_state%TYPE
,w_zip ware.w_zip%TYPE
);
PROCEDURE sp_record_print(p_w_id IN ware.w_id%type);
END;
/
Query OK, 0 rows affected
obclient> CREATE OR REPLACE PACKAGE BODY pkg_ware_mgmt
AS
PROCEDURE sp_record_print_by_record(p_record IN TYPE_WARE)
AS
BEGIN
dbms_output.put_line('Print a record :');
dbms_output.put_line('W_ID : ' || p_record.w_id
|| ', W_YTD : ' || p_record.w_ytd
|| ', W_TAX : ' || p_record.w_tax
|| ', W_NAME : ' || p_record.w_name
|| ', W_STREET_1 : ' || p_record.w_street_1
|| ', W_STREET_2 : ' || p_record.w_street_2
|| ', W_CITY : ' || p_record.w_city
|| ', W_STATE : ' || p_record.w_state
|| ', W_ZIP : ' || p_record.w_zip )
;
dbms_output.put_line('');
END;
PROCEDURE sp_record_print(p_w_id IN ware.w_id%TYPE )
IS
r_ware1 TYPE_WARE;
r_ware2 ware%ROWTYPE;
BEGIN
SELECT "W_ID","W_YTD","W_TAX","W_NAME","W_STREET_1","W_STREET_2","W_CITY","W_STATE","W_ZIP"
INTO r_ware1.w_id, r_ware1.w_ytd, r_ware1.w_tax, r_ware1.w_name, r_ware1.w_street_1, r_ware1.w_street_2, r_ware1.w_city, r_ware1.w_state, r_ware1.w_zip
FROM ware
WHERE w_id = p_w_id;
sp_record_print_by_record(r_ware1);
SELECT "W_ID","W_YTD","W_TAX","W_NAME","W_STREET_1","W_STREET_2","W_CITY","W_STATE","W_ZIP"
INTO r_ware2
FROM ware
WHERE w_id = p_w_id + 1;
r_ware1 := r_ware2;
sp_record_print_by_record(r_ware1);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Raise an unkown exception !');
END ;
END;
/
Query OK, 0 rows affected
obclient> CALL pkg_ware_mgmt.sp_record_print(1);
Query OK, 0 rows affected
Print a record :
W_ID : 1, W_YTD : 1200, W_TAX : .1868, W_NAME : W_NAME_1, W_STREET_1 : jTNkXKWXOdh, W_STREET_2 : lf9QXTXXGoF04IZBkCP7, W_CITY : srRq15uvxe5, W_STATE : GQ, W_ZIP : 506811111
Print a record :
W_ID : 2, W_YTD : 1200, W_TAX : .0862, W_NAME : W_NAME_2, W_STREET_1 : xEdT1jkENtbLwoI1Zb0, W_STREET_2 : NT0j4RCQ4OqrS, W_CITY : vlwzndw2FPrO, W_STATE : XR, W_ZIP : 063311111