存储过程+包+触发器

--存储过程 procedure
语法：
create [or replace] procedure pro_name
[(参数1[in]|out|in out 类型,参数2...)] is|as
[声明部分]
begin
  执行的语句;
  exception
    执行的语句;
end;
--调用存过
call pro_name();
注：相较于SQL存过的好处:
1.存过只在创建的时候进行编译 以后每次执行不需要编译
  SQL语句每次执行都要编译 所以存过可以提高执行速度
2.当数据库执行复杂操作的时候(多表连接查询) 可以将复杂的事务处理结合
  一起使用这些操作 如果使用SQL需要多次连接数据库 只用存过只需要连接一次
  减少了数据库连接次数
3.存过可以重复利用 减少了开发人员的工作量

1.无参数
truncate table emp_1;
创建一个存过 把emp中的30部门的员工信息emp_1中--insert update dbms
更新emp_1中职位是MANAGER的员工薪资为原来的一半 打印更新前后的工资
create or replace procedure pro_1 is
v_sal number;
begin
  insert into emp_1 select * from emp where deptno=30;
  --commit;
  select sal into v_sal from emp_1 where job='MANAGER';--更新前工资
  dbms_output.put_line('更新前sal是'||v_sal);
  update emp_1 set sal=sal/2 where job='MANAGER';
  dbms_output.put_line('更新后sal是'||v_sal/2);
end;
call pro_1();
--创建一个存过 把emp中职位是CLERK的员工信息插入到emp_1
--更新emp_1中工资最高的人的工资变为原来的一半 打印更新前后的工资
create or replace procedure pro_2 is
v_sal number;
begin
  insert into emp_1 select * from emp where job='CLERK';
  --commit;
  select max(sal) into v_sal from emp_1;
  dbms_output.put_line('更新前sal是'||v_sal);
  update emp_1 set sal=v_sal/2 where sal=v_sal;
  dbms_output.put_line('更新后sal是'||v_sal/2);
end;
call pro_2();
2.有in无out
/*创建一个存过 输入一个部门编号 把部门的员工姓名和工资插入到emp_1中
打印插入了几条数据*/
create or replace procedure pro_3(v_deptno number) is
begin
  insert into emp_1(ename,sal)
         select ename,sal 
         from emp
         where deptno=v_deptno;
  dbms_output.put_line('插入了'||sql%rowcount);
end;
call pro_3(10);
--创建emp_1数据同emp
--创建emp_2格式同emp
/*编写一个存过 输入一个数字和部门编号 要求数据必须是0-9的
如果不是抛出异常1 如果输入部门编号不存在 抛出异常2
当部门的人数小于该数字 将部门的员工信息插入到emp_2中 打印插入了多少条
           大于       将部门的员工姓名删除          打印删除了多少
           等于     更新部门的员工工资/100再3次方   打印更新多少*/
create table emp_1 as select * from emp;
create table emp_2 as select * from emp where 1=2;

create or replace procedure pro_4(v_n number,v_deptno number) is
v_1 number;
err_1 exception;
err_2 exception;
begin
  if v_n<0 or v_n>9 then raise err_1;
  end if;
  select count(1) into v_1 from emp_1
  where deptno=v_deptno;
  if v_1<0 then raise err_2;
    end if;
    if v_n<v_1 then
    insert into emp_2 select * from emp_1 where deptno=v_deptno;
    dbms_output.put_line('插入了'||sql%rowcount);
    elsif v_n>v_1 then
    update emp_1 set ename=null where deptno=v_deptno;--更新空=删除
    dbms_output.put_line('删除了'||sql%rowcount);
    elsif v_n=v_1 then
    update emp_1 set sal=power(sal/100,3) where deptno=v_deptno;
    dbms_output.put_line('更新了'||sql%rowcount);
    end if;
  exception
    when err_1 then
    raise_application_error(-20001,'数字不合规');
    commit;
    when err_2 then
    raise_application_error(-20002,'编号不存在');
end;
call pro_4(3,20);
3.有out
--写一个存过 输入一个编号 输出部门平均工资
create or replace procedure pro_5(v_empno in number,v_sal out number) is
begin
  select avg(sal) into v_sal from emp
  where deptno =(select deptno from emp where empno=v_empno);
end;
--调用
declare
v_d_sal number;
begin
  pro_5(7788,v_d_sal);
  dbms_output.put_line(v_d_sal);
end;
select deptno,avg(sal) from emp group by deptno;

动态SQL
ddl 批量增删改查
语法：
execute immediate '执行的SQL语句'[using|into]
create table test5 as select * from emp;

begin
  execute immediate'drop table test1';
end;

begin
  for i in 2..5
    loop
      execute immediate'drop table test'||i;
      end loop;
end;
--创建一个存过批量建表
create or replace procedure pro1(v_name varchar,v_1 number,v_2 number)
authid current_user is
begin
  for i in v_1..v_2
    loop
      execute immediate'create table '||v_name||'_'||i||' as select * from emp';
      end loop;
end;
call pro1('emp',11,20);
加上authid current_user表示调用者权限 以调用者权限执行(看角色权限)
没加authid current_user表示定义者权限 以定义者权限执行(只看系统权限)
--删表
create or replace procedure pro2(v_name varchar,v_1 number,v_2 number)
authid current_user is
begin
  for i in v_1..v_2
    loop
      execute immediate'drop table '||v_name||'_'||i;
      end loop;
end;
call pro2('ZXCVBNM',1,1000);
--[using|into]
--批量建表 emp_1-emp_100
call pro1('emp',1,100);
select * from user_tables;
--创建一个存过 向emp_1-emp_100中插入一条数据 打印第一条
create or replace procedure pro3(v1 number,v2 varchar,v3 number)
authid current_user is
v_e emp%rowtype;
begin
  for i in 1..100
    loop
    execute immediate 'truncate table emp_'||i;
    execute immediate 'insert into emp_'||i||
    '(empno,ename,sal) values(:1,:2,:3)'
    using v1,v2,v3;--传值
    execute immediate 'select * from emp_'||i||' where rownum=1'
    into v_e;--取值
    dbms_output.put_line('emp_'||i||'的第一条数据是'||
            v_e.empno||' '||v_e.ename||' '||v_e.sal);
    end loop;
end;

call pro3(1234,'qwer',5678);
如果同时使用using 和into 先into 后using
/*把emp的数据插入到emp1_100 输入一个数字
打印emp_1-50中工资大于这个数的员工姓名*/
create or replace procedure pro4(v1 number)
authid current_user is
v_e varchar(20);
begin
  for i in 1..50
    loop
  execute immediate 'select wm_concat(ename) from emp_'||i||
  ' where sal>:a' into v_e using v1;
  dbms_output.put_line(
  'emp_'||i||'中工资大于'||v1||'的人有'||v_e);
    end loop;
end;
call pro4(3000);
数据字典(视图)
静态:user_*
     all_*
     dba_*
动态 v$
--------------
user_*  存储了当前用户所有的对象信息
all_*   存储了当前用户可以访问的所有对象的信息--不一定是当前用户拥有的
dba_*   存储了整个数据库所有对象的信息

user_tables 所有的表
user_views  所有的视图
user_sequences 所有的序列
user_idexs 所有的索引
user_constraints 所有的约束
user_source 所有的有名块
user_recyclebin 回收站
user_tab_cols 所有表所有列的属性
user_tab_colums 所有表所有列的属性
user_tab_comments 表注释
user_col_comments 列注释
--表注释
comment on table 表名 is '注释';
--列注释
comment on column 表.列 is '注释';
ddl 建表语句

v$session 当前有谁连接我的数据库 terminal是主机名
会话:一个用户从登录到退出
select distinct terminal from v$session;
--查看回收站
select * from user_recyclebin;
--闪回
flashback table 表名 to before drop rename to 新表名;

注:
如果回收站有重名的表 会闪回最近删除的
如果要闪回较早删除的表 可以用object_name
--闪回到修改前(dml)的状态
前提是必须开启行移动:
开启:alter table 表名 enable row movement;
关闭:                disable
--闪回到一个时间戳
select * from emp_1;
insert into emp_1 select * from emp;
alter table emp_1 enable row movement;--先开始行移动再闪回

delete from emp_1 where empno=7654;

flashback table emp_1 to timestamp 
timestamp'2022-4-27 16:10:00.000';
或
flashback table emp_1 to timestamp 
to_timestamp('2022-4-27 16:17','yyyy-mm-dd hh24:mi');
--在用户表找表删除
select 'drop table '||table_name||';' from user_tables
where table_name like'T_BACK_%';

包 package
在大型项目中 有很多的模块 每个模块有自己的存过 函数等
这个东西默认是会放在一起 时间久了 不容易维护
包定义:package
包主体:package body

包定义语法:
create or replace package 包名 is|as
function 1.. return ..
function 1..
..
procedure 1..
procedure 1..
..
type ..
cursor ..
v1..--全局变量
end 包名;
包主体的语法:
create or replace package body 包名 is|as
function 1.. return .. is|as
[声明]--本地变量
begin
  执行的语句;
  return ..
end;
..
procedure 1.. is|as
  存过的具体内容
  ..
end 包名;

包定义:声明了数据类型 变量 游标 异常处理等;
包主体:是包定义具体的实现 定义了包定义部分所声明的游标和子程序
全局变量 本地变量
/*创建一个包 包含fun1 pro1 pro2
fun1 输入一个员工编号 返回部门名称
pro1 输入一个员工编号 输出部门平均工资和人数
pro2 输入一个名字 打印他的名字和经理的名字*/
--包定义
create or replace package pck_1 is
function fun1(v_empno number) return varchar;
procedure pro1(v_empno number,v_a_sal out number,v_c out number);
procedure pro2(v_ename varchar);
end pck_1;
--包主体
create or replace package body pck_1 is
--输入一个员工编号 返回部门名称
function fun1(v_empno number) return varchar is
  v_dname varchar(20);
begin
  select d.dname into v_dname
  from emp e
  join dept d
  on e.deptno=d.deptno
  and e.empno=v_empno;
  return v_dname;
end;
--pro1 输入一个员工编号 输出部门平均工资和人数
procedure pro1(v_empno number,v_a_sal out number,v_c out number) is
begin
  select avg(sal),count(1) into v_a_sal,v_c
  from emp
  where deptno=(select deptno from emp where empno=v_empno);
  dbms_output.put_line(v_a_sal||' '||v_c);
end;
--pro2 输入一个名字 打印他的名字和经理的名字
procedure pro2(v_ename varchar) is
v_mgr varchar(20);
begin
  select b.ename into v_mgr
  from emp e
  join emp b
  on e.mgr=b.empno
  and e.ename=v_ename;
  dbms_output.put_line(v_ename||','||v_mgr);
end;
end pck_1;
--调用 遵循函数和存过的调用方式
select pck_1.fun1(7654) from dual;
declare
v_a number;
v_b number;
begin
  pck_1.pro1(7788,v_a,v_b);
end;
call pck_1.pro2('SMITH');
/*创建一个包 包含3个函数 1个存过
fun1 输入一个员工编号 返回工资
fun2 输入一个姓名 返回部门名称
fun3 输入一个日期 返回这个日期之前的人数
pro1 把deptno表10部门的部门所在地 插入到emp1的job列*/
create table emp1 as select * from emp where 1=2;
--包定义
create or replace package pck_2 is
function fun1(v_empno number) return number;
function fun2(v_ename varchar) return varchar;
function fun3(v_d date) return number;
procedure pro1;
end pck_2;
--包主体
create or replace package body pck_2 is
--fun1 输入一个员工编号 返回工资
function fun1(v_empno number) return number is
  v_sal number(20);
begin
  select sal into v_sal
  from emp
  where empno=v_empno;
  return v_sal;
end;
--fun2 输入一个姓名 返回部门名称
function fun2(v_ename varchar) return varchar is
v_dname varchar(20);
begin
  select d.dname into v_dname
  from emp e
  join dept d
  on e.deptno=d.deptno
  and e.ename=v_ename;
  return v_dname;
end;
--fun3 输入一个日期 返回这个日期之前的人数
function fun3(v_d date) return number is
v_n number(20);
  begin
    select count(1) into v_n from emp where hiredate<v_d;
    return v_n;
end;
--pro1 把deptno表10部门的部门所在地 插入到emp1的job列
procedure pro1 is
  begin
    insert into emp1(job) select loc from dept where deptno=10;
end;
end pck_2;
--调用
select pck_2.fun1(7788) from dual;

select pck_2.fun2('KING') from dual;

select pck_2.fun3(date'1981-1-1') from dual;

call pck_2.pro1();

select * from emp1;
包的重载
包的子程序名字相同 通过传入不同类型的参数 得到不同的结果
触发器:
分类:DML触发器(增删改查内容) DDL触发器(删表建表)

DML触发器语法：
create or replace trigger 名字
before|after 事前|事后
dml 操作 on 表
[for each row] 加上表示行级出发不加表示语句触发
[declare]
begin
  执行的语句;
end;
行级触发：影响一行数据 触发一次
语句触发：一个语句只触发一次
create table emp_1 as select * from emp;
create table emp_2 as select * from emp;
--针对emp_2创建一个触发器 删除emp_2时,向emp_1中插入一个1
create or replace trigger tri_1
before delete or update or insert
on emp_2
for each row--加这个变行级触发器
begin
  insert into emp_1(empno) values(1);
end;
--语句运行
delete from emp_2 where deptno=20;
--看看情况
select * from emp_1;
/*创建触发器 对emp_2进行插入 向emp_1的ename中插入一个'插入'
在hiredate中插入系统时间*/
create or replace trigger tri_2
before insert
on emp_2
for each row--行级触发器
begin
  insert into emp_1(ename,hiredate) values('插入',sysdate);
end;
--语句
insert into emp_2 select * from emp_1 where deptno=20;
--看看
select * from emp_1;

触发器的启用和禁用
开启某个
alter trigger 触发器名 enable;
禁用某个
alter trigger 触发器名 disable;
启用全表
alter trigger 表名 enable all trigger;
禁用全表
alter trigger 表名 disable all trigger;
触发器的作用：
1.维护数据库的安全性 一致性和完整性
2.可在写入数据表前 强制检验或转换数据
3.当触发器发生错误时 异常的结果会被撤销
4.部分数据库管理系统可以针对DDL使用触发器 成为DDL触发器
  还可以针对都视图定义替代触发器(instance of)

优点：
1.触发器可通过数据库中的相关表实现级联更改
2.从约束的角度而言 触发器可以定义比sheck更为复杂的约束
3.触发器也可以评估数修改前后表的状态 并根据其差异采取对策
4.一个表中的多个同类触发器(insert update delete)允许采用多个
  不同的对策 以影响同一个修改语句
  
缺点：
1.滥用会造成数据库及应用程序的维护困难 在数据库操作中 可以通过关系
  触发器 存储过程 应用程序等来实现数据库操作 同时 规则 约束 缺省值
  也是保证数据完整性的重要保障 如果对触发器过分的依赖 那么势必会
  影响数据库的结构 同时增加维护的复杂性
2.一个表的触发器越多 对于表的DML操作性能影响越大
3.如果触发频率高 占用内存 减低数据库访问速度
4.相对不灵活 一旦触发马上执行 不能排除特殊情况
5.一定程度上打乱代码结构 相关的代码都需要特别注释 否则造成阅读和维护上的困难
6.过度使用也会造成数据库的维护困难

--触发器中的数值和旧值
:old.列 列用的旧值
:new.列 列中的新值
注：必须是行级触发
      insert  update  delete
:old  没有     有      有
:new   有      有     没有
把emp_2的表中的入职日期改为时间戳类型
truncate table emp_2;
alter table emp_2 modify hiredate timestamp;

create table emp_3 as select * from emp;
/*对emp_3创建触发器 对表进行更新的时候 把更新前后的员工信息
插入到emp_2并在job列表名是更前还是更后 在hiredate插入时间戳*/
create or replace trigger tri_3
before update
on emp_3
for each row
  begin
    insert into emp_2(ename,job,hiredate) values(:old.ename,'前',systimestamp);
    insert into emp_2(ename,job,hiredate) values(:new.ename,'后',systimestamp);
end;

select * from emp_2;
select * from emp_3;

update emp_3 set ename=lower(ename) where deptno=20;
--触发器和自定义异常的联动
create or replace trigger tri_4
before insert
on emp_3
for each row
  begin
    if :new.empno not like '7___' then
      raise_application_error(-20001,'编号错误');
      end if;--做检查了,输入的值是否满足
end;

insert into emp_3(empno) values(1234);
select * from emp_3;
/*对emp_3创建触发器 检查插入到emp_3的工资如果小于1000
则把他的sal改成1000插入 如果不是10,20,30部门的弹窗报错*/
create or replace trigger tri_5
before insert
on emp_3
for each row
  begin
    if :new.sal<1000 then
      :new.sal:=1000;
      end if;
    if
      :new.deptno not in(10,20,30) then
      raise_application_error(-20002,'部门错误');
      end if;
end;
insert into emp_3(sal,deptno) values(3599,50);
--定时器 job
job是oracle的定时任务,也叫定时器 定时作业 作业
数据库定时地自动执行一些脚本 或做数据备份 或做数据提炼
或做数据库的性能优化
包括重建索引等等的工作 需要用到job
--创建job的包
dbms_job
--创建job的语法:一般是T+1
declare
v_jobid number;
begin
  dbms_job.submit(job =>v_jobid,
                  what =>'pro_name|dml语句;',
                  next_date =>sysdate+1/24/60,
                  --第一次执行时间,一分钟后执行
                  interval =>'trunc(sysdate,''mi'')+1/24/60');
                  --间隔的时间 整分执行;
--查看定时任务
select * from user_jobs;
--停止任务
begin
  dbms_job.broken(jobid,true);
  commit;
end;
--删除job
call dbms_job.remove(jobid);
--立即执行
call dbms_job.run(jobid);
--当job执行失败后他会重试
1.每次重试时间是递增的,第一次2分钟,4分钟,8分钟
2.当超过1440分钟 也就是24小时的时候 固定时间的重试为1天
3.超过16此重试后 job就会被标记为broken next_date为4000-1-1
  也就是不在进行job重试