Like variables of other common data types, variables of the record type can also be passed as parameters to subprograms.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL mode.
Example: Create and call a subprogram with record type parameters
The following example defines variables of the record type in two ways:
r_ware1 TYPE_WARE;
r_ware2 ware%ROWTYPE;
The SELECT INTO statement shows two methods for assigning values to the record variables. The two variables have the same structure, and can be assigned with values as follows: r_ware1 := r_ware2;.
Finally, the variables of the record type are passed as parameters to the subprogram.
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 unknown 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