Oracle教程-优快云博客

本文链接：https://blog.youkuaiyun.com/htj10/article/details/114629358

文章目录

Oracle的存储过程（procedure）
索引
Oracle实现像Mysql的自动增长auto_increment
触发器Trigger
- 触发器例子1：update 之前进行安全检查
- 例子2：备份数据
定时器
Oracle日期时间

Oracle的存储过程（procedure）

创建

--存储过程定义 
create or replace procedure proce01(a in varchar2, b in out varchar2) 
as

begin
   -- b := a + b; -- 会将 字符转换为数值 ，注意a为in，所以不能被赋值，这与c里的函数不同
   b := a || b;
   dbms_output.put_line('a:'||a||'   b:'||b);
end proce01;

执行

-- 执行
declare 
     a  varchar2(20):= '01';
     b  varchar2(20):= 'bg';
begin  
    proce01(a ,b);
    dbms_output.put_line('a:'||a||'   b:'||b);
end;

输出结果：
a:01 b:01bg
a:01 b:01bg

删除

-- 删除存储过程
DROP PROCEDURE proce01

索引

-- 创建索引
create index DName_Index on dept(dname);
-- 删除索引
drop index Dname_index;

Oracle实现像Mysql的自动增长auto_increment

例如：

-- 建表：
CREATE TABLE t_user(U_ID NUMBER(8), UNAME VARCHAR2(20), UPASSWORD VARCHAR2(20));

-- 建立序列：
CREATE SEQUENCE SEQ_TUSER  
INCREMENT BY 1
MINVALUE 1                      --最小值  
MAXVALUE 99999999               --最大值由NUMBER(8)  
NOCYCLE                              --不打环   
NOCACHE                              --不缓存  
ORDER;
COMMIT;  

-- 然后建立before的触发器：
CREATE OR REPLACE TRIGGER TRG_ADDTUSER  
BEFORE INSERT ON t_user
FOR EACH ROW
BEGIN
SELECT SEQ_TUSER.NEXTVAL INTO :NEW.U_ID FROM DUAL;
END TRG_ADDTUSER;

-- 测试：
INSERT INTO t_user (UNAME,UPASSWORD) VALUES ('libai','589avf');
INSERT INTO t_user (UNAME,UPASSWORD) VALUES ('zhangsan','ko098');
COMMIT;
select * from t_user;

触发器Trigger

参考：https://blog.youkuaiyun.com/weixin_41649106/article/details/86981325

触发器的应用场景

复杂的安全性检查
数据的确认
数据库审计
数据的备份和审计

触发器例子1：update 之前进行安全检查

准备工作：

create table t_salary(id number(8), sal number(10,2));
insert  into t_salary(id, sal) values(1, 8000);
select * from t_salary;

创建触发器：

/**
涨后的薪水不能低于涨前的薪水
1 :old 和 :new 代表同一条记录
2 :old 代表操作该行之前，这一行的值
 :new 代表操作该行之后，这一行的值
*/
create or replace trigger checkSalary
 before update
 on t_salary
 for each row
declare
 -- local variables here 没有变量声明的话，declare可以省略
begin
 -- if 涨后的薪水 < 涨前的薪水 then 如何表示呢 ？
 if :new.sal < :old.sal then
   raise_application_error(-20002,'涨后的薪水:'|| :new.sal ||'小于涨前的薪水：'||:old.sal);
 end if;
end checkSalary;

测试：

SQL> update t_salary set sal=7000 where id=1;
update t_salary set sal=7000 where id=1
       *
第 1 行出现错误:
ORA-20002: 涨后的薪水:7000小于涨前的薪水：9000
ORA-06512: 在 "SCOTT.CHECKSALARY", line 6
ORA-04088: 触发器 'SCOTT.CHECKSALARY' 执行过程中出错

删除触发器

--删除 触发器
drop trigger checkSalary;

例子2：备份数据

/*
删除前，将数据备份
*/
create or replace trigger tri_sal_delete_bk
  before delete
  on t_salary
  for each row
begin
  insert into t_salary_del(id,sal) values(:old.id, :old.sal);
  -- commit; -- 注意不可以有提交，会报错ora-04092
end tri_sal_delete_bk;

注意：DML（delete/update/insert）触发器中不能使用DDL（CREATE，DROP，ALTER）语句，也不能使用事务控制语句（ROLLBACK, COMMIT,SAVEPOINT）。特别注意的是，在触发器的主体中引用的函数（function）/过程（procedure）中也不能有事物控制语句。

例子3：

/**
非工作时间（星期六 星期日， 非9点~18点的区间）禁止写入数据
首先要搞清楚： 触发器的类型--语句级触发器。
不管插入了多少条数据，没有必要对每一行数据都进行校验，只要不在这个时间段内，都不让插入。
*/

create or replace trigger tri_addSalaryCheck
  before insert on t_salary
declare
 -- local variables here
begin
  if to_char(sysdate, 'day') in ('星期六', '星期日') or
      to_number(to_char(sysdate, 'hh24')) not between 9 and 18    then
      -- 禁止insert
      raise_application_error(-20001,'非工作时间禁止插入数据');
  end if;
end tri_addSalaryCheck;

定时器

例子

-- 定时器
-- ----------------------------
-- 创建表 JOBTEST
-- ----------------------------
-- DROP TABLE JOBTEST IF EXISTS ;
CREATE TABLE "JOBTEST" (
"LOCK_STATUS" VARCHAR2(2 BYTE) DEFAULT 'N'  NULL ,
"LOCK_DATE" TIMESTAMP(6)  NULL ,
"UN_LOCK_DATE" TIMESTAMP(6)  NULL 
);
COMMENT ON COLUMN "SCOTT"."JOBTEST"."LOCK_STATUS" IS 'Y 锁定 N 解锁';
COMMENT ON COLUMN "SCOTT"."JOBTEST"."LOCK_DATE" IS '加锁时间';
COMMENT ON COLUMN "SCOTT"."JOBTEST"."UN_LOCK_DATE" IS '解锁时间';

-- 
INSERT INTO "SCOTT"."JOBTEST" VALUES ('Y', TO_TIMESTAMP(' 2018-10-28 14:44:41:000000', 'YYYY-MM-DD HH24:MI:SS:FF6'), null);
INSERT INTO "SCOTT"."JOBTEST" VALUES ('Y', TO_TIMESTAMP(' 2018-10-28 14:44:52:000000', 'YYYY-MM-DD HH24:MI:SS:FF6'), null);
INSERT INTO "SCOTT"."JOBTEST" VALUES ('N', TO_TIMESTAMP(' 2018-10-28 14:45:01:000000', 'YYYY-MM-DD HH24:MI:SS:FF6'), null);

select * from jobtest;

-- Oracle定时器一般包括4步：
-- 1. 创建存储过程
CREATE OR REPLACE PROCEDURE unlockTest IS
BEGIN
	UPDATE JOBTEST
	SET LOCK_STATUS = 'N',
			UN_LOCK_DATE = SYSDATE
	WHERE LOCK_STATUS = 'Y' 
	AND SYSDATE > (LOCK_DATE + 10 /(24 * 60)); 
COMMIT ;
END ; 
-- unlockTest为存储过程的名称
-- BEGIN后面跟着的SQL就是要定时执行的SQL语句
-- 一定要记得在END之前添加COMMIT;(注意有分号)

-- 2. 定义执行时间
DECLARE
	unlockTest_timer number;
BEGIN
	SYS.DBMS_JOB.SUBMIT(
		job => unlockTest_timer, 
		what => 'unlockTest;', 
		next_date => SYSDATE, 
		interval => 'sysdate+10/(24*60)');
Commit;
End;
-- DECLARE 用来定义unlockTest_timer 的定时器编号
-- SYS.DBMS_JOB.SUBMIT中的job 指的是定时器编号，在DECLARE 中已经声明
-- SYS.DBMS_JOB.SUBMIT中的what 指的是要执行的存储过程，也就是SQL语句
-- SYS.DBMS_JOB.SUBMIT中的next_date 指的是下次执行时间
-- SYS.DBMS_JOB.SUBMIT中的interval 指的是每次执行时间的间隔时间

-- 3. 查看在执行的定时器(job是编号)
SELECT job, next_date, next_sec, failures, broken FROM user_jobs;

-- 4. 删除定时器
Begin
  dbms_job.remove(23); -- 23就是上面查出来的定时器编号
Commit;
End;

Oracle日期时间

转载：https://www.cnblogs.com/fubinhnust/p/9925720.html

-- Oracle时间
SELECT SYSDATE FROM  dual;  -- 系统时间   
SELECT SYSTIMESTAMP FROM  dual;  -- 当前系统时间戳
SELECT CURRENT_TIMESTAMP FROM  dual;  -- 与时区设置有关，返回的秒是系统的，返回的日期和时间是根据时区转换过的
SELECT current_date FROM  dual;  -- 是对CURRENT_TIMESTAMP准确到秒的四舍五入
select SYSDATE ,systimestamp,current_date,current_timestamp from dual;


-- 时间差计算
DECLARE
START_DATE DATE;
END_DATE DATE;
BEGIN
  START_DATE := SYSDATE - 1 ;
  END_DATE := SYSDATE  ;
  dbms_output.put_line('相差天数 :' || ROUND(TO_NUMBER(END_DATE - START_DATE)));
  dbms_output.put_line('相差小时数 :' || ROUND(TO_NUMBER(END_DATE - START_DATE) * 24));
  dbms_output.put_line('相差分钟数 :' || ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60));
  dbms_output.put_line('相差秒数 :' || ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60 * 60));
  dbms_output.put_line('相差毫秒数 :' || ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60 * 60 * 1000));
END;


-- 字符串转时间类型
SELECT to_date('2018-08-23 00:00:00','yyyy-mm-dd hh24:mi:ss') FROM dual;

-- 时间类型转字符串
select to_char(sysdate,'yyyy-mm-dd hh24::mi:ss') from dual;
select to_char(sysdate,'yyyy-mm-dd') from dual;


-- 时间日期加减
select sysdate,add_months(sysdate,12) from dual; --加1年
select sysdate,add_months(sysdate,1) from dual; --加1月
select sysdate,to_char(sysdate+7,'yyyy-mm-dd HH24:MI:SS') from dual; --加1星期
select sysdate,to_char(sysdate+1,'yyyy-mm-dd HH24:MI:SS') from dual; --加1天
select sysdate,to_char(sysdate+1/24,'yyyy-mm-dd HH24:MI:SS') from dual; --加1小时
select sysdate,to_char(sysdate+1/24/60,'yyyy-mm-dd HH24:MI:SS') from dual; --加1分钟
select sysdate,to_char(sysdate+1/24/60/60,'yyyy-mm-dd HH24:MI:SS') from dual; --加1秒
--------------------------------------------------------------------------------------
select sysdate,add_months(sysdate,-12) from dual; --减1年
select sysdate,add_months(sysdate,-1) from dual; --减1月
select sysdate,to_char(sysdate-7,'yyyy-mm-dd HH24:MI:SS') from dual; --减1星期
select sysdate,to_char(sysdate-1,'yyyy-mm-dd HH24:MI:SS') from dual; --减1天
select sysdate,to_char(sysdate-1/24,'yyyy-mm-dd HH24:MI:SS') from dual; --减1小时
select sysdate,to_char(sysdate-1/24/60,'yyyy-mm-dd HH24:MI:SS') from dual; --减1分钟
select sysdate,to_char(sysdate-1/24/60/60,'yyyy-mm-dd HH24:MI:SS') from dual; --减1秒