Call "SF_DEL_DB_BAK_EXPIRED"('DAMENG','/dbbak/dmbak','',7);

create or replace function SF_DEL_DB_BAK_EXPIRED(
DBNAME varchar(128),
BAKDIR varchar(256),
PARALLEL_DIR varchar(256),
EXPIRED_DAY int )
return INT as

DECLARE
V_CNT INT;
V_I INT;
V_PATH VARCHAR(256);
V_TIME INT;
V_FLAG INT;
V_NUM INT;

TYPE T_REC IS
RECORD (
ID INT,
NAME VARCHAR(128),
TIME VARCHAR(128),
PATH VARCHAR(256));
TYPE T_REC_ARR
IS
ARRAY T_REC[];
BAK_INFO T_REC_ARR;

begin
V_NUM=0;
/初始化备份链表/
SF_BAK_LST_INIT();
SF_BAK_LST_SET_N_PATH(1);
SF_BAK_LST_SET_PATH(0, BAKDIR);
SF_BAK_LST_SET_PARALLEL_DIR(PARALLEL_DIR);
SF_BAK_LST_COLLECT(DBNAME, '', 1);
V_CNT = SF_GET_BAK_LST_NUM(DBNAME);
/判断某库是否存在备份/
IF V_CNT > 0 THEN
BAK_INFO = NEW T_REC[V_CNT];
PRINT V_CNT;
ELSE RETURN -1;
END IF;
/指向第一个备份/
SF_BAK_LST_GET_FIRST();
/指向某一个备份，获取其中某些信息存入数组BAK_INFO/
FOR V_I IN 1..V_CNT
LOOP
V_PATH=SF_BAK_GET_CUR_PATH();
IF V_PATH IS NOT NULL THEN
BAK_INFO[V_I].ID =V_I;
BAK_INFO[V_I].NAME = SF_BAK_GET_NAME(V_PATH);
BAK_INFO[V_I].TIME = SF_BAK_GET_TIME(V_PATH);
BAK_INFO[V_I].PATH = V_PATH;
END IF;
/移向下一个备份/
SF_BAK_LST_GET_NEXT();
END LOOP;
/用游标隐式打开从ARRAY BAK_INFO取这些值/
FOR R IN (SELECT * FROM ARRAY BAK_INFO)
LOOP
IF DAYS_BETWEEN(CURDATE(),R.TIME) >= EXPIRED_DAY THEN
V_FLAG = SF_DEL_BAK(DBNAME,'',R.NAME,1);
END IF;

	IF V_FLAG=0 THEN
		V_NUM=V_NUM+1;
	END IF;		

END LOOP;
/有一个失败，全部失败/
IF V_NUM >= 1 THEN
RETURN 0;
ELSE RETURN 1;
END IF;
/销毁备份链表/
SF_BAK_LST_DEINIT();
end;