Oracle 函数function返回table--管道化表函数

原创已于 2022-02-10 17:53:43 修改 · 1.5k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #数据库 #database

于 2022-02-10 17:53:11 首次发布

Oracle 专栏收录该内容

3 篇文章

订阅专栏

本文介绍了Oracle数据库中函数如何返回表，特别是管道化表函数的使用，包括定义type行类型、table类型以及如何在function中返回记录表。

Oracle 函数function返回table–管道化表函数

1 、定义type行类型

CREATE OR REPLACE TYPE MESTK_ADMIN.LOTPARAM_TABLE_ROW  FORCE  as object
(
  PARAM_NAME varchar2(100),
  PARAM_VALUE     varchar2(100),
  PARAM_TYPE          varchar2(10)
);

2、定义table类型

CREATE OR REPLACE TYPE MESTK_ADMIN.LOTPARAM_TABLE IS TABLE OF LOTPARAM_table_ROW ;

3、function返回记录表table

CREATE OR REPLACE FUNCTION MESTK_ADMIN.FUN_GETLOTPARAMPROFILE (
   F_ORDER_NO         VARCHAR2,
   F_ORDER_SUB_LOT    VARCHAR2,
   F_CUST_CODE        VARCHAR2)
   RETURN LOTPARAM_TABLE
IS
   TTAB            LOTPARAM_TABLE := LOTPARAM_TABLE ();

   TTAB_INDEX      NUMBER := 1;
   V_Param_Value   VARCHAR2 (100);
   V_TableName     VARCHAR2 (4000);
   query_Count     VARCHAR2 (100) := 'select count(1) from (';
   V_count         NUMBER;
BEGIN
   FOR ROW_CUR_FIRST_INDEX IN (SELECT CUST_CODE,
                                      PARAM_NAME,
                                      NEED_DISPLAY,
                                      QUERY,
                                      PARAM_TYPE
                                 FROM WIP_LOT_PARAMETERS_PROFILE
                                WHERE CUST_CODE IN (F_CUST_CODE, '*'))
   LOOP
      IF ROW_CUR_FIRST_INDEX.NEED_DISPLAY = 'Y'
      THEN
         V_Param_Value := '-';

         V_TableName :=
               ROW_CUR_FIRST_INDEX.QUERY
            || ' WHERE order_No = '''
            || F_ORDER_NO
            || ''' AND ORDER_SUB_LOT = '''
            || F_ORDER_SUB_LOT
            || '''';

         EXECUTE IMMEDIATE query_Count || V_TableName || ')' INTO V_count;

         IF V_count > 0
         THEN
            EXECUTE IMMEDIATE V_TableName || ' AND ROWNUM = 1'
               INTO V_Param_Value;
         END IF;



         TTAB.EXTEND ();

         TTAB (TTAB_INDEX) :=
            LOTPARAM_TABLE_ROW (
               PARAM_NAME    => ROW_CUR_FIRST_INDEX.PARAM_NAME,
               PARAM_VALUE   => V_Param_Value,
               PARAM_TYPE    => ROW_CUR_FIRST_INDEX.PARAM_TYPE);

         TTAB_INDEX := TTAB_INDEX + 1;
      END IF;
   END LOOP;

   RETURN TTAB;
END;
/