レコード型パラメータを持つサブルーチンの作成と呼び出し

r_ware1 TYPE_WARE;
r_ware2 ware%ROWTYPE;

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