记录类型的变量跟其他常用数据类型的变量一样可以作为参数传递给子程序。

    示例:创建和调用带记录类型参数的子程序

    下面示例用两种方式定义了记录类型的变量:

    1. r_ware1 TYPE_WARE;
    2. r_ware2 ware%ROWTYPE;

    然后通过 SELECT INTO 语句演示了给记录变量赋值的两种方法。由于这两个变量的类型实际结构是相同的,所以两个变量可以进行赋值操作:r_ware1 := r_ware2;

    最后将记录变量作为参数传递给子程序打印变量的值。

    1. delimiter /
    2. CREATE OR REPLACE PACKAGE pkg_ware_mgmt as
    3. TYPE TYPE_WARE IS RECORD(
    4. w_id ware.w_id%TYPE
    5. ,w_ytd ware.w_ytd%TYPE
    6. ,w_tax ware.w_tax%TYPE
    7. ,w_name ware.w_name%TYPE
    8. ,w_street_1 ware.w_street_1%TYPE
    9. ,w_street_2 ware.w_street_2%TYPE
    10. ,w_city ware.w_city%TYPE
    11. ,w_state ware.w_state%TYPE
    12. ,w_zip ware.w_zip%TYPE
    13. );
    14. PROCEDURE sp_record_print(p_w_id IN ware.w_id%type);
    15. END;
    16. /
    17. delimiter ;
    18. delimiter /
    19. CREATE OR REPLACE PACKAGE BODY pkg_ware_mgmt
    20. AS
    21. PROCEDURE sp_record_print_by_record(p_record IN TYPE_WARE)
    22. AS
    23. BEGIN
    24. dbms_output.put_line('Print a record :');
    25. dbms_output.put_line('W_ID : ' || p_record.w_id
    26. || ', W_YTD : ' || p_record.w_ytd
    27. || ', W_TAX : ' || p_record.w_tax
    28. || ', W_NAME : ' || p_record.w_name
    29. || ', W_STREET_1 : ' || p_record.w_street_1
    30. || ', W_STREET_2 : ' || p_record.w_street_2
    31. || ', W_CITY : ' || p_record.w_city
    32. || ', W_STATE : ' || p_record.w_state
    33. || ', W_ZIP : ' || p_record.w_zip )
    34. ;
    35. dbms_output.put_line('');
    36. END;
    37. PROCEDURE sp_record_print(p_w_id IN ware.w_id%TYPE )
    38. IS
    39. r_ware1 TYPE_WARE;
    40. r_ware2 ware%ROWTYPE;
    41. BEGIN
    42. SELECT "W_ID","W_YTD","W_TAX","W_NAME","W_STREET_1","W_STREET_2","W_CITY","W_STATE","W_ZIP"
    43. 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
    44. FROM ware
    45. WHERE w_id = p_w_id;
    46. sp_record_print_by_record(r_ware1);
    47. SELECT "W_ID","W_YTD","W_TAX","W_NAME","W_STREET_1","W_STREET_2","W_CITY","W_STATE","W_ZIP"
    48. INTO r_ware2
    49. FROM ware
    50. WHERE w_id = p_w_id + 1;
    51. r_ware1 := r_ware2;
    52. sp_record_print_by_record(r_ware1);
    53. EXCEPTION
    54. WHEN OTHERS THEN
    55. dbms_output.put_line('Raise an unkown exception !');
    56. END ;
    57. END;
    58. /
    59. delimiter ;
    60. obclient> call pkg_ware_mgmt.sp_record_print(1);
    61. Query OK, 0 rows affected (0.12 sec)
    62. Print a record :
    63. 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
    64. Print a record :
    65. 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
    66. obclient>