Oracle常用脚本

# Oracle常用脚本

# 一、常见操作

-- 枚举翻译
DECODE(T1.IS_TIMELY,0,'否',1,'是')

-- 百分比
to_char(ROUND((sum(CASE WHEN T1.IS_TIMELY = 1 THEN 1 ELSE 0 END)/count(0)) * 100.0 ,2), 'fm999990.00') || '%'

1
2
3
4
5

二、常用SQL操作

字段操作

-- 增加字段
alter table TAB_XXXXX add column_xxxxxx NVARCHAR2(30);
comment on column TAB_XXXXX.column_xxxxxx is 'xxxxxxx';

-- 修改字段长度
alter table TAB_****** modify COLUMN_NAME NVARCHAR2(50);

1
2
3
4
5
6

索引等操作

select /*+leading(表别名) index(表别名 索引名) index(表别名 索引名) */ from  table
/*+LEADING(TABLE)*/  将指定的表作为连接次序中的首表.
/*+USE_NL(TABLE1, TABLE2, TABLE3...)*/ 将指定表与嵌套的连接的行源进行连接,并把指定表作为内部表.
/*+parallel(4)*/ 开启多线程

1
2
3
4

常用函数

NEXT_DAY()函数：获取当前时间的下一周周日的时间。

-- 查询本周一00:00:00的时间
select TRUNC(NEXT_DAY(sysdate-8,1)+1) from dual;  --2021-01-01 00:00:00

-- 查询下周一00:00:00的时间
select TRUNC(NEXT_DAY(sysdate-8,1)+7)+1 from dual;  --2021-01-01 00:00:00

1
2
3
4
5

判断日期是否在周末

SELECT *
FROM mytable
WHERE MOD(TO_CHAR(my_date, 'J'), 7) + 1 IN (6, 7);

1
2
3

# 二、存储过程操作

1、根据表名查询使用到的存储过程

select *
from user_dependencies
where referenced_name=upper('TABLE_NAME_XXXXX_XXXXX')

1
2
3

2、创建存储过程

3、调用存储过程

BEGIN 
   for i in 0 .. 31 loop
    pro_xxxxxx(sysdate-i*2);
   END LOOP;
END;

-- call procedure
CALL pro_xxxxxx(sysdate);

1
2
3
4
5
6
7
8

# 三、JOB操作

# 1、查询JOB

-- 查询用户job列表
SELECT * FROM USER_JOBS;
 
-- 查询dba所有job
SELECT * FROM DBA_JOBS;
 
-- 查询所有job列表
SELECT * FROM ALL_JOBS;

-- 根据存储过程名称查询对应的job信息
select * from dba_scheduler_jobs where upper(job_action) like ('%xxxxxxxxx%') and rownum < 10
select * from all_jobs WHERE what LIKE '%xxxxxxxxx%' and rownum < 10

1
2
3
4
5
6
7
8
9
10
11
12

# 2、创建JOB

/**  
  创建同步的计划任务  
  JOB_PRO_OA_ORGANIZATION_V表示存储过程名(注意：后面有一个分号)  
  定时频率  
  trunc(sysdate)+25/24表示每天凌晨1点执行  
  sysdate+1/24/60表示每分钟执行一次  
  sysdate+5/1440表示每五分钟执行一次  
**/ 
DECLARE
   job_id NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT(
	  JOB => job_id,	-- 执行job的id
      WHAT => '/* job的注释信息 */ BEGIN PROCEDURE_NAME(); END;',	-- 要执行的脚本或者过程
      NEXT_DATE => TO_DATE('2022/09/22 10:47:00','YYYY/MM/DD HH24:MI:SS'),		-- 下一次执行的时间
      INTERVAL => 'TRUNC(SYSDATE+1)+1/24'			-- 执行周期 （此处为 每天1点执行一次 ）
  );
  COMMIT;
END;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

# 3、修改JOB

-- 修改部分JOB信息
begin    
    dbms_job.interval(<JOB_ID>,'sysdate+30/60/24'); -- 修改间隔时间
    dbms_job.what(<JOB_ID>,'PROCEDURE_NAME();'); -- 修改job
    commit; 
end;

-- 修改整个JOB信息
BEGIN
	SYS.DBMS_JOB.CHANGE(
		JOB => 263,		-- 执行job的id
		WHAT => '/* job的注释信息 */ BEGIN PROCEDURE_NAME(); END;',			-- 要执行的脚本或者过程 
		NEXT_DATE => TO_DATE('2021/03/14 01:00:00','YYYY/MM/DD HH24:MI:SS'),		--下一次执行的时间
		INTERVAL => 'TRUNC(SYSDATE+1)+1/24'			-- 执行周期 （此处为 每天1点执行一次 ）
	);
	COMMIT;
END;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

# 4、停用JOB

BEGIN
	-- 禁用 job
	SYS.DBMS_JOB.BROKEN(<JOB_ID>,TRUE);
	-- 提交更改
	COMMIT;
END;

1
2
3
4
5
6

# 5、删除JOB

BEGIN
	-- 禁用job
	SYS.DBMS_JOB.BROKEN(<JOB_ID>,TRUE);
	-- 删除job
	SYS.DBMS_JOB.REMOVE(<JOB_ID>);
	-- 提交更改
	COMMIT;
END;

1
2
3
4
5
6
7
8

# 5、执行JOB

BEGIN
	-- 执行JOB
	DBMS_JOB.RUN(<JOB_ID>);
	COMMIT;
END;

1
2
3
4
5

# 九、问题记录

#Oracle

← ShardingJDBC读写分离与分库分表实战 Oracle应用篇→