oracle将字符串中的字符和数字拆分开等功能
将字符串中的字符和数字拆分开
create or replace procedure F_GetNumber1( inString IN VARCHAR2,n_return1 out varchar2,
n_return2 out varchar2) ISDCHAR VARCHAR2(1024);
OUTCHAR VARCHAR2(1024);
j number default 0;
ulen number;
BEGINOUTCHAR:='';DCHAR:=TRIM(inString);IF DCHAR IS NULL THENreturn;END IF;ulen:=length(DCHAR);FOR i in 1..ulen LOOPJ:=ASCII(substr(DCHAR,i,1));IF J=46 OR (J>=48 AND J<=57) THENn_return2:=n_return2||substr(DCHAR,i,1);ELSEn_return1:=n_return1||substr(DCHAR,i,1);END IF;END LOOP;
EXCEPTIONWHEN OTHERS THENnull ;
END F_GetNumber1;
统计所有表的信息
CREATE OR REPLACE PROCEDURE P_SERVICE_TABLE_INFORMATION(iOwner IN VARCHAR2) ASVV_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 ISSELECT Table_Name, Tablespace_NameFROM DBA_TABLES DTWHERE DT.Owner = UPPER(iOwner)AND SUBSTR(Table_Name, 1, 3) <> 'BIN'AND NOT EXISTS (SELECT 1 FROM T_SERVICE_TABLE_INFORMATION TSTIWHERE 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) ASVV_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 ISSELECT Table_Name, Tablespace_Name FROM DBA_TABLES AWHERE 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 ;BEGINEXECUTE IMMEDIATE VV_SqlStr INTO VN_TableRows ;SELECT Bytes INTO VN_TableSize FROM DBA_SEGMENTSWHERE 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 ;EXCEPTIONWHEN OTHERS THENINSERT 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 ISl_result VARCHAR2(4000);
BEGINl_result := '';FOR i IN 1..p_count LOOPl_result := l_result || p_char;END LOOP;RETURN l_result;
END;