oracle将字符串中的字符和数字拆分开等功能

 将字符串中的字符和数字拆分开

create or replace procedure F_GetNumber1( inString IN VARCHAR2,n_return1 out varchar2,
n_return2 out varchar2) IS

DCHAR   VARCHAR2(1024);
OUTCHAR VARCHAR2(1024);
j   number default 0;
ulen number;
BEGIN
  OUTCHAR:='';
  DCHAR:=TRIM(inString);
  IF DCHAR IS NULL THEN
    return;
  END IF;
  ulen:=length(DCHAR);
  FOR i in 1..ulen LOOP
   J:=ASCII(substr(DCHAR,i,1));
   IF J=46 OR (J>=48 AND J<=57) THEN
      n_return2:=n_return2||substr(DCHAR,i,1);
   ELSE
   n_return1:=n_return1||substr(DCHAR,i,1);
   END IF;
  END LOOP;
EXCEPTION
 WHEN OTHERS THEN
   null ;
END F_GetNumber1;

 统计所有表的信息

CREATE OR REPLACE PROCEDURE P_SERVICE_TABLE_INFORMATION(iOwner IN VARCHAR2) AS



  VV_TableName    VARCHAR2(32);
  VV_CurrentSpace VARCHAR2(32);
  --VN_TableRows    NUMBER(16);
  --VN_TableSize    NUMBER(16);
  VD_CreateDate   DATE;
  --VV_SqlStr       VARCHAR2(256);

  CURSOR C_SERVICE_TABLE IS
    SELECT Table_Name, Tablespace_Name
      FROM DBA_TABLES DT
     WHERE DT.Owner = UPPER(iOwner)
       AND SUBSTR(Table_Name, 1, 3) <> 'BIN'
       AND NOT EXISTS (SELECT 1 FROM T_SERVICE_TABLE_INFORMATION TSTI
                        WHERE TSTI.Table_Owner = DT.Owner AND TSTI.Table_Name = DT.Table_Name);

BEGIN
  --循环
  FOR FL_1 IN C_SERVICE_TABLE LOOP
    --变量赋值
    VV_TableName := FL_1.Table_Name ;
    VV_CurrentSpace := FL_1.Tablespace_Name ;
    VD_CreateDate := TRUNC(SYSDATE) ;

    --插入业务表
    INSERT INTO T_SERVICE_TABLE_INFORMATION(Table_Owner, Table_Name, Table_Rename, Plan_Space, Current_Space, Table_Type, Service_Type, Table_Rows, Table_Size, Create_Date, Update_Date)
         VALUES(UPPER(iOwner), VV_TableName, '0', '0', VV_CurrentSpace, '0', '0', 0, 0, VD_CreateDate, VD_CreateDate);
    COMMIT;
    --BEGIN
    --EXCEPTION
    --  WHEN OTHERS THEN
    --
    --END;
  END LOOP;

END P_SERVICE_TABLE_INFORMATION;

存过统计表的大小，行数信息

CREATE OR REPLACE PROCEDURE P_TABLE_STAT(iOwner IN VARCHAR2) AS

  VV_TableName  VARCHAR2(32);
  VV_Tablespace VARCHAR2(32);
  VN_TableRows  NUMBER(16);
  VN_TableSize  NUMBER(16);
  VD_ExecDate   DATE;
  VV_SqlStr     VARCHAR2(256);

  CURSOR C_TABLE_STAT IS
    SELECT Table_Name, Tablespace_Name FROM DBA_TABLES A
     WHERE Owner = iOwner AND SUBSTR(Table_Name, 1, 3) <> 'BIN' ;

BEGIN
  --清除表数据
  --EXECUTE IMMEDIATE 'TRUNCATE TABLE T_TABLE_STAT';
  --循环
  FOR FL_1 IN C_TABLE_STAT LOOP
    --变量赋值
    VV_TableName := FL_1.Table_Name ;
    VV_Tablespace := FL_1.Tablespace_Name ;
    VD_ExecDate := TRUNC(SYSDATE) ;
    VV_SqlStr := 'SELECT COUNT(9) FROM '||iOwner||'.'||VV_TableName ;

    BEGIN

      EXECUTE IMMEDIATE VV_SqlStr INTO VN_TableRows ;

      SELECT Bytes INTO VN_TableSize FROM DBA_SEGMENTS
       WHERE Owner = iOwner AND Segment_Name = VV_TableName AND Segment_Type = 'TABLE' ;

      INSERT INTO T_TABLE_STAT(Tab_Owner, Tab_Name, Tab_Space, Tab_Rows, Tab_Size, Exec_Date)
           VALUES(iOwner, VV_TableName, VV_Tablespace, VN_TableRows, VN_TableSize, VD_ExecDate) ;
      COMMIT ;

    EXCEPTION
      WHEN OTHERS THEN
        INSERT INTO T_TABLE_STAT(Tab_Owner, Tab_Name, Tab_Space, Tab_Rows, Tab_Size, Exec_Date)
             VALUES(iOwner, VV_TableName, VV_Tablespace, '', '', VD_ExecDate) ;
        COMMIT ;

    END ;

  END LOOP ;

END P_TABLE_STAT ;

Oracle自定义函数，重复字符串

CREATE OR REPLACE FUNCTION repeat_char(p_char CHAR, p_count NUMBER)
RETURN VARCHAR2 IS
  l_result VARCHAR2(4000);
BEGIN
  l_result := '';
  FOR i IN 1..p_count LOOP
    l_result := l_result || p_char;
  END LOOP;
  RETURN l_result;
END;

数字转为二进制

create or replace function number_2_bit(v_num number) return varchar is
  v_rtn varchar(2000);
  v_n1  number;
  v_n2  number;
begin
  v_n1 := v_num;
  loop
    v_n2 := mod(v_n1, 2);
    v_n1 := trunc(v_n1 / 2);
    --dbms_output.put_line(v_n2 || ' ' || v_n1);
    v_rtn := to_char(v_n2) || v_rtn;
    exit when v_n1 = 0;
  end loop;
  return v_rtn;
end;