将字符串中的字符和数字拆分开
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;