ORACLE 函数返回对象类型结果集

来源:互联网 发布:php开源网店系统 编辑:程序博客网 时间:2024/03/29 03:37
注:适用于表中只有一行数据,如果多行数据建议用表类型结果集创建对象类型CREATE OR REPLACE TYPE "T_B_PARAMETER"  AS OBJECT(   PARAMETER_ID                   NUMBER         ,  PARAMETER_WITHDRAWAL_R         NUMBER(15,2),  PARAMETER_WITHDRAWAL_LL        NUMBER(15,2),  PARAMETER_ASESME_A_LL          NUMBER(15,2),  PARAMETER_ASESME_D_LL          NUMBER,  PARAMETER_DEDUCTAX_R           NUMBER(15,2),  PARAMETER_MANAGE_A_R           NUMBER(15,2),  PARAMETER_ORGANIZ_MANAGE_A_UL  NUMBER(15,2),  PARAMETER_MUTUALAID_C_LL       NUMBER,  PARAMETER_MUTUALAID_G_LL       NUMBER,  PARAMETER_SUBSIDY_R            NUMBER(15,2),  PARAMETER_AWARD_MEMBER_L_LL    NUMBER,  PARAMETER_MUTUALAID_P_LL       NUMBER(15,2),  PARAMETER_PURCH_A_LL           NUMBER(15,2),  PARAMETER_PURCH_A_R            NUMBER(15,2)) 创建函数CREATE OR REPLACE FUNCTION F_B_PARAMETERRETURN T_B_PARAMETER ASR_PARAMETER T_B_PARAMETER := T_B_PARAMETER( -1 , -1 , -1, -1, -1 , -1 , -1 , -1, -1, -1 , -1 , -1 , -1, -1, -1   ); --必须初始化 BEGIN                     --直接将表中数据写入对象中    SELECT        PARAMETER_ID ,        PARAMETER_WITHDRAWAL_R ,         PARAMETER_WITHDRAWAL_LL ,        PARAMETER_ASESME_A_LL ,         PARAMETER_ASESME_D_LL ,         PARAMETER_DEDUCTAX_R ,        PARAMETER_MANAGE_A_R ,         PARAMETER_ORGANIZ_MANAGE_A_UL ,        PARAMETER_MUTUALAID_C_LL ,         PARAMETER_MUTUALAID_G_LL ,        PARAMETER_MUTUALAID_P_LL ,        PARAMETER_SUBSIDY_R ,         PARAMETER_AWARD_MEMBER_L_LL ,         PARAMETER_PURCH_A_LL ,         PARAMETER_PURCH_A_R    INTO         R_PARAMETER.PARAMETER_ID ,        R_PARAMETER.PARAMETER_WITHDRAWAL_R ,         R_PARAMETER.PARAMETER_WITHDRAWAL_LL ,        R_PARAMETER.PARAMETER_ASESME_A_LL ,         R_PARAMETER.PARAMETER_ASESME_D_LL ,         R_PARAMETER.PARAMETER_DEDUCTAX_R ,        R_PARAMETER.PARAMETER_MANAGE_A_R ,         R_PARAMETER.PARAMETER_ORGANIZ_MANAGE_A_UL ,        R_PARAMETER.PARAMETER_MUTUALAID_C_LL ,         R_PARAMETER.PARAMETER_MUTUALAID_G_LL ,        R_PARAMETER.PARAMETER_MUTUALAID_P_LL ,        R_PARAMETER.PARAMETER_SUBSIDY_R ,         R_PARAMETER.PARAMETER_AWARD_MEMBER_L_LL ,         R_PARAMETER.PARAMETER_PURCH_A_LL ,         R_PARAMETER.PARAMETER_PURCH_A_R    FROM        B_PARAMETER;    RETURN R_PARAMETER;    END;使用方式SELECT F_B_PARAMETER().PARAMETER_WITHDRAWAL_R FROM DUAL;

0 0
原创粉丝点击