–进制转换函数
CREATE OR REPLACE FUNCTION F_CONV(INVAL IN VARCHAR2(32767),INSCALE IN NUMBER,OUTSCALE IN NUMBER,LEFTPAD IN NUMBER DEFAULT NULL )
RETURN VARCHAR2(32767)
AUTHID DEFINER
IS
V_TMP NUMBER(38,0) := 0;
V_STR VARCHAR2(100) := ‘0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz!#$%&’’()*+,-./:;<=>?@[]^_`{|}~’;
V_RESULT VARCHAR2(128) := ‘’;
V_SUB CHAR(1);
V_POS INT;
BEGIN
IF INVAL IS NULL OR INSCALE IS NULL OR OUTSCALE IS NULL THEN
RETURN NULL;
END IF;
IF INSCALE = OUTSCALE THEN
RETURN INVAL;
END IF;
IF INSCALE < 2 OR INSCALE > LENGTH(V_STR) OR OUTSCALE < 2 OR OUTSCALE > LENGTH(V_STR) THEN
RETURN NULL;
END IF;

IF INSCALE = 10 THEN
V_TMP := TO_NUMBER(INVAL);
ELSIF INSCALE = 16 THEN
V_TMP := TO_NUMBER(INVAL,‘xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx’);
ELSE
FOR I IN 1…LENGTH(INVAL) LOOP
IF INSCALE <= 26 THEN
V_SUB := UPPER(SUBSTR(INVAL,I,1));
ELSE
V_SUB := SUBSTR(INVAL,I,1);
END IF;
V_POS := INSTR(V_STR,V_SUB) - 1;
V_TMP := V_TMP + V_POS * POWER(INSCALE,LENGTH(INVAL) - I);
END LOOP;
END IF;

IF V_TMP = 0 THEN
RETURN ‘0’;
ELSIF OUTSCALE = 10 THEN
V_RESULT := V_TMP;
ELSIF OUTSCALE = 16 THEN
V_RESULT := UPPER(TO_CHAR(V_TMP,‘fmxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx’));
ELSE
WHILE V_TMP <> 0 LOOP
V_RESULT := V_RESULT||SUBSTR(V_STR,MOD(V_TMP,OUTSCALE)+1,1);
V_TMP := TRUNC(V_TMP / OUTSCALE);
END LOOP;
SELECT REVERSE(V_RESULT) INTO V_RESULT FROM DUAL;
END IF;

IF LEFTPAD IS NOT NULL AND LEFTPAD > LENGTH(V_RESULT) THEN
V_RESULT := LPAD(V_RESULT,LEFTPAD,‘0’);
END IF;

RETURN V_RESULT;
END;

–查询备份作业
select
a.NAME as “任务名称”,
a.USERNAME as “创建者”,
a.CREATETIME as “创建时间”,
a.VALID as “生效状态”,
a.DESCRIBE as “作业描述”,
b.NAME as “步骤名称”,
case when b.“TYPE” in (1,5,6) and left(b.COMMAND,1)=‘0’ then ‘完全备份’
when b.“TYPE” in (1,5,6) and left(b.COMMAND,1)=‘1’ then ‘差异增量备份’
when b.“TYPE”=0 then ‘SQL语句’ end as “步骤类型”,
b.COMMAND as “执行命令”,
case when c.“TYPE”=0 then ‘只执行一次’
when c.“TYPE”=1 then ‘按天的频率来执行’
when c.“TYPE”=2 then ‘按周的频率来执行’
when c.“TYPE”=3 then ‘在一个月的某一天执行’
when c.“TYPE”=4 then ‘在一个月的第一周第几天执行’
when c.“TYPE”=5 then ‘在一个月的第二周的第几天执行’
when c.“TYPE”=6 then ‘在一个月的第三周的第几天执行’
when c.“TYPE”=7 then ‘在一个月的第四周的第几天执行’
when c.“TYPE”=8 then ‘在一个月的最后一周的第几天执行’ end as “调度类型”,
c.FREQ_INTERVAL as “执行频率（和调度类型有关）”,
F_CONV(c.FREQ_SUB_INTERVAL,10,2) as “执行频率（当 TYPE=2 时，表示的是某一个星期的星期几执行,从最低位开始算起，依次表示周日、周一…周五、周六）”,
c.FREQ_MINUTE_INTERVAL as “一天内每隔多少分钟执行一次,0 表示一天内执行一次”,
c.STARTTIME as “作业调度的起始时间”,
c.ENDTIME as “作业调度结束时间”,
c.DURING_START_DATE as “作业被调度的有效日期范围的起始日期”,
c.DURING_END_DATE as “作业被调度的有效日期范围的结束日期”
from “SYSJOB”.“SYSJOBS” A
left join “SYSJOB”.“SYSJOBSTEPS” B
on a.id=b.jobid
left join “SYSJOB”.“SYSJOBSCHEDULES” C
on a.id=c.jobid;