待研究NC的过程procedure

CREATE OR REPLACE PROCEDURE ANALYZE_TB AS

OWNER_NAME VARCHAR2(100);
V_LOG INTEGER;
V_SQL1 VARCHAR2(800);
V_TABLENAME VARCHAR2(50);

CURSOR CUR_LOG IS
SELECT COUNT(TABLE_NAME) FROM USER_TABLES WHERE TABLE_NAME='ANALYZE_LOG';
--add delete tmp statistics for 9i
CURSOR CUR_TABLE_TEMP IS
SELECT TABLE_NAME FROM USER_TABLES WHERE temporary='Y';

--1

BEGIN

--DBMS_OUTPUT.ENABLE (buffer_size=>100000);
--1.1
BEGIN
OPEN CUR_LOG;
FETCH CUR_LOG INTO V_LOG;
IF V_LOG=0 THEN
EXECUTE IMMEDIATE 'CREATE TABLE ANALYZE_LOG (USER_NAME VARCHAR(20),OP_TIME CHAR(19) DEFAULT to_char(sysdate,''yyyy-mm-dd hh24:mi:ss''),ERROR_TEXT VARCHAR(200),TABLE_NAME VARCHAR(40))';
END IF;

END;

SELECT USER INTO OWNER_NAME FROM DUAL;

V_SQL1:='INSERT INTO ANALYZE_LOG (USER_NAME,ERROR_TEXT,TABLE_NAME) VALUES ('''||OWNER_NAME||''',''ANALYZE BEGIN'',''ALL'')';
EXECUTE IMMEDIATE V_SQL1;
sys.dbms_stats.gather_schema_stats(ownname=>UPPER(OWNER_NAME),estimate_percent => 100,method_opt => 'FOR ALL INDEXED COLUMNS',cascade => TRUE);
V_SQL1:='INSERT INTO ANALYZE_LOG (USER_NAME,ERROR_TEXT,TABLE_NAME) VALUES ('''||OWNER_NAME||''',''ANALYZE END'',''ALL'')';
EXECUTE IMMEDIATE V_SQL1;
commit;

--1.2delete tmptb statitics
BEGIN
OPEN CUR_TABLE_TEMP ;
LOOP
FETCH CUR_TABLE_TEMP INTO V_TABLENAME;
EXIT WHEN CUR_TABLE_TEMP %NOTFOUND;
V_SQL1:= 'ANALYZE TABLE '|| V_TABLENAME ||' delete STATISTICS ';
EXECUTE IMMEDIATE V_SQL1;
END LOOP;

CLOSE CUR_TABLE_TEMP ;
end;

EXCEPTION
WHEN OTHERS THEN
IF CUR_LOG%ISOPEN THEN
CLOSE CUR_LOG;
END IF;
IF CUR_TABLE_TEMP%ISOPEN THEN
CLOSE CUR_TABLE_TEMP ;
END IF;
commit;
end;