文档首页 > > 工具指南> Migration Tool SQL语法迁移工具> Oracle(Beta)语法迁移> 系统函数> 其它函数

其它函数

分享
更新时间: 2019/08/09 GMT+08:00

TABLE()

输入

SELECT
                    *
               FROM
                    STAT_DETAIL
               WHERE
                    DETAIL_ID IN ( SELECT
                    COLUMN_VALUE
               FROM
                    TABLE
                    ( V_DETAIL_ID_ARRAY1 ) A
          UNION
          ALL SELECT
                    COLUMN_VALUE
               FROM
                    TABLE
                    ( V_DETAIL_ID_ARRAY2 ) B
          UNION
          ALL SELECT
                    COLUMN_VALUE
               FROM
                    TABLE
                    ( V_DETAIL_ID_ARRAY3 ) C )

输出

SELECT
                    *
               FROM
                    STAT_DETAIL
               WHERE
                    DETAIL_ID IN ( SELECT
                    COLUMN_VALUE
               FROM
                    UNNEST ( V_DETAIL_ID_ARRAY1 ) A ( COLUMN_VALUE )
          UNION
          ALL SELECT
                    COLUMN_VALUE
               FROM
                    UNNEST ( V_DETAIL_ID_ARRAY2 ) B ( COLUMN_VALUE )
          UNION
          ALL SELECT
                    COLUMN_VALUE
               FROM
                    UNNEST ( V_DETAIL_ID_ARRAY3 ) C ( COLUMN_VALUE ) )

SQLERRM()

输入

CREATE OR REPLACE EDITIONABLE PACKAGE BODY PCKG_SQLERRM AS
FUNCTION FUN_SQLERRM (branchId       IN VARCHAR2,
                     languageCode   IN VARCHAR2)
      RETURN VARCHAR2
   IS
      roles   VARCHAR2 (4000);
   BEGIN
      SELECT LISTAGG(A_NLS.NAME, ',') WITHIN GROUP(ORDER BY A_NLS.NAME)
        INTO roles
        FROM CTP_ROLE A,
             CTP_ROLE_NLS A_NLS;
        RETURN roles;
   EXCEPTION
      WHEN OTHERS
      THEN
         LOG ('FUN_SQLERRM()', SQLERRM (SQLCODE));
         RETURN roles;
   END FUN_SQLERRM;
end PCKG_SQLERRM;
/

输出

CREATE
OR REPLACE FUNCTION PCKG_SQLERRM.FUN_SQLERRM
RETURN VARCHAR2 IS roles VARCHAR2 ( 4000 ) ;
BEGIN
          SELECT
                    LISTAGG ( A_NLS.NAME ,',' ) WITHIN GROUP (
               ORDER BY
                    A_NLS.NAME ) INTO roles
               FROM
                    CTP_ROLE A ,CTP_ROLE_NLS A_NLS ;
               RETURN roles ;
          EXCEPTION WHEN OTHERS
          THEN
               LOG ( 'FUN_SQLERRM' ,SQLERRM ) ;
          RETURN roles ;
     END ;
     /

系统包函数

系统包函数,例如 SYS.UTL_FILE() 将会迁移为UTIL_FILE().

输入

CREATE OR REPLACE FUNCTION FUNC_ADVANCED_PACKAGE(IN_BINFILE IN SYS.UTL_FILE.FILE_TYPE, 
                      IN_CURSOR  IN REFCURSOR, 
                      IN_PARAM   IN NUMBER 
                      ) RETURN VARCHAR2 IS
    V_PARAM1 VARCHAR(32767) := ''; 
 
  BEGIN
       IF IN_CURSOR%ISOPEN THEN
         IF IN_PARAM = 1 THEN
            V_REC_INDEX := 0;
          LOOP
           FETCH IN_CURSOR
            INTO V_PARAM1;
          EXIT WHEN IN_CURSOR%NOTFOUND;
                  SYS.UTL_FILE.PUT_LINE(IN_BINFILE, REPLACE(REPLACE(V_PARAM1,CHR(13),' '),CHR(10),' ') || CHR(13));
         END LOOP;
        SYS.UTL_FILE.FFLUSH(IN_BINFILE);
        CLOSE IN_CURSOR;
    ELSE
      RETURN '2'; 
    END IF;
    RETURN '0'; 
  EXCEPTION
    WHEN OTHERS THEN
      RETURN '-1';
  END ;
  /

输出

CREATE OR REPLACE FUNCTION FUNC_ADVANCED_PACKAGE ( IN_BINFILE IN UTL_FILE.FILE_TYPE ,
               IN_CURSOR IN SYS_REFCURSOR ,IN_PARAM IN NUMBER )
RETURN VARCHAR2 IS V_PARAM1 VARCHAR( 32767 ) := '' ;
BEGIN
          IF
               IN_CURSOR%ISOPEN
               THEN
                    IF
                         IN_PARAM = 1
                         THEN
                              V_REC_INDEX := 0 ;
                              LOOP
                                   FETCH IN_CURSOR INTO V_PARAM1 ;
                              EXIT WHEN IN_CURSOR%NOTFOUND ;
                              UTL_FILE.PUT_LINE ( IN_BINFILE ,REPLACE( REPLACE( V_PARAM1 ,CHR ( 13 ) ,' ' ) ,
              CHR ( 10 ) ,' ' ) || CHR ( 13 ) ) ;
                         END LOOP ;
                         UTL_FILE.FFLUSH ( IN_BINFILE ) ;
                    CLOSE IN_CURSOR ;
               ELSE
                    RETURN '2' ;
               END IF ;
          RETURN '0' ;
     EXCEPTION WHEN OTHERS
     THEN
          RETURN '-1' ;
     END ;
     /

SPLIT函数

输入

CREATE OR REPLACE FUNCTION split
 (
  p_list varchar2,
  p_sep varchar2 := ';'
 ) return type_split pipelined
 is
  l_idx pls_integer;
  v_list varchar2(32767) := p_list;
 begin
  loop
  l_idx := instr(v_list,p_sep);
  if l_idx > 0 then
  pipe row(substr(v_list,1,l_idx-1));
  v_list := substr(v_list,l_idx+length(p_sep));
  else
  pipe row(v_list);
  exit;
  end if;
  end loop;
  return;
 end ;

输出

CREATE OR REPLACE FUNCTION "SPLIT"
 (
  p_list varchar2,
  p_sep varchar2 := ';'
 ) return type_split pipelined
 is
  l_idx pls_integer;
  v_list varchar2(32767) := p_list;
 begin
  loop
  l_idx := instr(v_list,p_sep);
  if l_idx > 0 then
  pipe row(substr(v_list,1,l_idx-1));
  v_list := substr(v_list,l_idx+length(p_sep));
  else
  pipe row(v_list);
  exit;
  end if;
  end loop;
  return;
 end ;
分享:

    相关文档

    相关产品

文档是否有解决您的问题?

提交成功!

非常感谢您的反馈,我们会继续努力做到更好!

反馈提交失败,请稍后再试!

*必选

请至少选择或填写一项反馈信息

字符长度不能超过200

提交反馈 取消

如您有其它疑问,您也可以通过华为云社区问答频道来与我们联系探讨

跳转到云社区