Oracle存储过程充电桩

存储过程

第一个存储过程：打印Hello Worrld

create or replace procedure sayHelloWorld
as 
--说明部分
begin
  dbms_output.put_line('Hello World');
  end;

创建一个带参数的存储过程：

给指定的员工涨100元的工资，并且打印涨钱前涨后的薪水

create or replace procedure raisesalary(eno in number)
as
--定义一个变量保存涨前的薪水 
  psal emp.sal%type;
begin 
      select sal into psal from emp where empno = eno;
      update emp set sal = sal+100 where empno = eno;
--需不需要commit？
      --注意：一般不在存储过程或者存储函数中，commit和rollback
      dbms_output.put_line('涨前:'||psal||'  涨后:'||(psal+100));
end;

存储函数

查询某个员工的年收入

create or replace function queryempincome(eno in number)
return number
as 
 --定义变量保存员工的薪水和奖金
 psal emp.sal%type;
 pcomm emp.comm%type;
begin
  --得到该员工的月薪和奖金
  select sal,comm into psal,pcomm from emp where empno = eno;
  --直接返回年收入
  return psal*12+nvl(pcomm,0);
end;

--什么时候用存储过程/存储函数？
  --原则：
    --如果只有一个返回值，用存储函数;否则，就用存储过程

out参数：查询某个员工姓名 月薪和职位

create or replace procedure queryempinform(eno in number,
                                           pename out varchar2,
                                           psal out number,
                                           pjob out varchar2)
as 
begin
     --得到该员工的姓名 月薪和职位
     select ename,sal,job into pename,psal,pjob from emp where empno = eno;
end;

在out参数中使用光标

申明包结构

包头

create or replace package mypackage as
       type empcursor is ref cursor;
       procedure queryEmpList(dno in number,empList out empcursor);
END mypackage;

包体

--包体需要实现包头中声明的所有方法
create or replace package body mypackage as 
       procedure queryEmpList(dno in number,empList out empcursor) AS
        BEGIN
          open empList for select * from emp where deptno = dno;
          END queryEmpList;
          END mypackage;
declare
begin
  dbms_output.put_line('HelloWorld');
  end;

使用基本数据类型

 declare
  --定义基本变量类型
 --基本数据类型
  pnumber number(7,2);
  --字符串变量
  pname varchar2(20);
  --日期变量
  pdate date;
  begin
    pnumber:=1;
    dbms_output.put_line(pnumber);
    pname :='Tom';
    dbms_output.put_line(pname);
    pdate :=sysdate;
    dbms_output.put_line(pdate);
	dbms_output.put_line(pdate+1);
  end;

引用型变量

declare
    pename emp.ename%type;
    psal emp.sal%type;
begin
  select ename,sal into pename,psal from scott.emp where empno =7839;
  dbms_output.put_line(pename||'的薪水是'||psal);
  end;

记录型变量，查询并打印 7839的姓名和薪水

declare 
    emp_rec scott.emp%rowtype;
    begin
      select * into emp_rec from scott.emp where empno =7839;
      dbms_output.put_line(emp_rec.ename||'的薪水是'||emp_rec.sal);
      end;

判断用户从键盘输入的数字

/*

1.如何使用if语句
2.接收一个键盘输入(字符串)
*/

set serveroutput on 
--接收一个键盘输入
--num:地址值，含义是：在该地址上保存了输入的值
accept num prompt '请输入一个数字';

declare
--定义变量保存用户从键盘输入的数字
 pnum number := &num ;
 begin
   --执行if语句进行条件判断
   if pnum = 0 then dbms_output.put_line('您输入的数子是0');
    elsif pnum = 1 then dbms_output.put_line('您输入的数字是1');
    elsif pnum = 2 then dbms_output.put_line('您输入的数字是2');
    else dbms_output.put_line('其他数字');
    end if ;
    end;

循环打印1-10

使用while循环打印1-10

 declare 
 pnum number:=1;
 begin 
   while pnum<=10 loop
     dbms_output.put_line(pnum);
     pnum:=pnum+1;
     end loop;
     end;

使用loop循环打印1-10

declare
 pnum number:=1;
 begin 
   loop
     --退出条件：循环变量大于10
     exit when pnum>10;
     --打印该变量的值
     dbms_output.put_line(pnum);
     --循环变量+1
     pnum:=pnum+1;
   end loop;
end;

使用for循环打印1-10

declare
 --定义循环变量
 pnum number :=1;
begin 
  for pnum in 1..10 loop
    dbms_output.put_line(pnum);
  end loop;
end;

光标

查询并打印员工的姓名和薪水

1.光标的属性

/*
 %found %notfound
 %isopen 判断光标是否打开
 %rowcount 影响的行数
   */

declare

 --定义一个光标

 cursor cemp is select ename,sal from scott.emp;
 --为光标定义对应的变量
 pename scott.emp.ename%type;
 psal scott.emp.sal%type;
 begin
   open cemp;
    loop
      fetch cemp into pename,psal;
      --思考：1循环什么时候退出？2fetch不一定能取到记录
      exit when cemp%notfound;
      dbms_output.put_line(pename||'的薪水是'||psal);
      end loop;
   close cemp;
   end;

给员工涨工资，总裁1000，经理800，其他400

declare 
   --定义光标代表给那些员工涨工资
   cursor cemp is select empno,job from scott.emp;
   pempno emp.empno%type;
   pjob emp.job%type;
   begin
    -- rollback;
     --打开光标
     open cemp;
     loop
       --取出一个员工
       fetch cemp into pempno,pjob;
       exit when cemp%notfound;
       --判断员工的职位
       if pjob = 'president' then update emp set sal = sal+1000 where empno = pempno;
       elsif pjob = 'manager' then update emp set sal = sal+800 where  empno=pempno;
       else update emp set sal = sal+400 where empno=pempno;
       end if; 
       end loop;
     close cemp;
     --对于oracle，默认的事务隔离级别是read committed
     --事务的ACID
     commit;
     dbms_output.put_line('涨工资完成');
     end;
select ename ,job,sal from scott.emp;

/*
1.光标的属性
       %found %notfound
       %isopen 判断光标是否打开
       %rowcount 影响的行数
2.光标的限制 默认情况下，Oracle数据库只允许在同一个会话中，打开300个光标

*/

修改光标数的限制

alter system set open_cursors =400 scope = both;
scope的取值：both(当前实例，参数文件同时修改)，memory(只更改当前实例不更改参数文件)，spfile(只更改参数文件，不更改当前实例，数据库需要重启)

declare
   cursor cemp is select empno,job from scott.emp;
   pempno emp.empno%type;
   pjob emp.job%type;
   begin
     open cemp;
     if cemp%isopen then
       dbms_output.put_line('光标已经打开');
       else
         dbms_output.put_line('光标没有打开');
     close cemp;
     end if ;
   end;

带参数光标 查询某个部门中员工的姓名

declare
 --定义带参数的光标
 cursor cemp(dno number) is select ename from scott.emp where deptno = dno;
 pename scott.emp.ename%type;
 begin
   --打开光标;
   open cemp(20);
   loop
     --取出每个员工的姓名
     fetch cemp into pename;
     exit when cemp%notfound;
     dbms_output.put_line(pename);
     end loop;
   close cemp;
   end;

例外

系统例外：no_data_found

declare
 pename scott.emp.ename%type;
 begin
   --查询员工号是1234的员工姓名
   select ename into pename from scott.emp where empno = 1234;
   exception 
     when no_data_found then dbms_output.put_line('没有找到该员工');
     when others then dbms_output.put_line('其他例外');
   end;

系统例外：too_many_rows

declare
 --定义变量
 pename scott.emp.ename%type;
 begin
   --查询所有10号部门的员工姓名
   select ename into pename from scott.emp where deptno = 10;
   exception
     when too_many_rows then dbms_output.put_line('select into 匹配了多行');
     when others then dbms_output.put_line('其他除外');
     end;

系统例外：被0除zero_divide

declare 
 --定义一个基本变量
 pnum number;
 begin
   pnum:=1/0;
   exception
     when zero_divide then dbms_output.put_line('1.0不能做除数');
     dbms_output.put_line('2.0不能做除数');
     when others then dbms_output.put_line('其他');
     end;

系统例外：value_error

declare 
 --定义一个number类型的变量
 pnum number;
 begin
   pnum:='abc';
   exception
     when value_error then dbms_output.put_line('算术或者转换错误');
     when others then dbms_output.put_line('其他例外');
   end;

自定义例外：查询50号部门的员工姓名

declare 
 -- 定义光标，代表50号部门的员工姓名
 cursor cemp is select ename from emp where deptno = 50;
 pename scott.emp.ename%type;
 --自定义例外
 no_emp_found exception;

 begin
   --打开光标
   open cemp;
    fetch cemp into pename;
    if cemp%notfound then
      --跳出例外
      raise no_emp_found;
      end if;
      --关闭光标
      --Oracle自动启动pmon(process monitor)
    close cemp;
  exception
    when no_emp_found then  dbms_output.put_line('没有找到员工');
    when others then  dbms_output.put_line('其他例外');
  end;

案例集锦

瀑布模型

1.需求分析

2.设计

  1）概要设计

  2）详细设计

3.编码 (Coding)

4.测试（Testing）

5.上线

SQL语句

变量：

​    1.初始值是多少

​    2.最终值如何得到

统计每年入职的员工人数

/*
SQL语句
select to_char(hiredate,'yyyy') from emp;
——>光标——>循环——>退出条件：notfound
变量：1初始值 2 如何得到
每年入职的员工人数
count80 number:=0;
count81 number:=0;
count82 number:=0;
count87 number:=0;
*/
declare 
    --定义光标
    cursor cemp is select to_char(hiredate,'yyyy')from scott.emp;
    phiredate varchar2(4);
    --每年入职的员工人数
    count80 number:=0;
    count81 number:=0;
    count82 number:=0;
    count87 number:=0;
begin
    --打开光标
    open cemp;
        loop
              --取出一个员工的入职年份
              fetch cemp into phiredate;
              exit when cemp%notfound;
              --判断入职年份
              if phiredate='1980' then count80 := count80+1;
              elsif phiredate = '1981' then count81 := count81+1;
              elsif phiredate ='1982' then count82 := count82+1;
              else count87 := count87+1;
              end if;
          end loop;
      --关闭游标
      close cemp;
  --输出结构
  dbms_output.put_line('Total:'||(count80+count81+count82+count87));
  dbms_output.put_line('1980:'||count80);
  dbms_output.put_line('1980:'||count81);
  dbms_output.put_line('1980:'||count82);
  dbms_output.put_line('1980:'||count87);
 end;

员工涨工资问题

/*
SQL语句
select empno.sal from emp order by sal;
——>光标——>循环——>退出条件：1.工资总额>5w 2.%notfound

变量：1.初始值 2.如何得到
涨工资的人数
countEmp number:=0;

涨后的工资总额
salTotal number;
1.select sum(sal) into salTotal from emp;
2.涨后的工资总额 = 涨前的工资总额 +sal*0.1;
*/
declare
     cursor cemp is select empno,sal from scott.emp order by sal;
     pempno scott.emp.empno%type;
     psal scott.emp.sal%type;
     --涨工资的人数
     countEmp number := 0;

     --涨后工资总额
     salTotal number;
begin 
  --得到工资总额的初始值
  select sum(sal) into salTotal from scott.emp;

  --打开光标
    open cemp;

    	loop
            --1.工资总额>5w
            exit when salTotal>50000;
            --取一个员工涨工资
            fetch cemp into pempno,psal;
            --2.%notfound
            exit when cemp%notfound;
            --涨工资
            update scott.emp set sal = sal*1.1 where empno = pempno;
            --人数+1
            countEmp:= countEmp+1;
            --2.涨后工资总额=涨前的工资总额+sal*0.1
            salTotal:=salTotal +psal*0.1;
    	end loop;
    --关闭光标
    close cemp;
commit;
dbms_output.put_line('人数:'||countEmp||'涨后工资总额:'||salTotal);
end;

涉及两张表的员工涨工资问题

/*
SQL语句
1.有哪些部门
    select deptno from dept -->光标 -->循环 -->退出条件：notfound
2.部门中员工的薪水
    select sal from emp where deptno = ? -->带一个参数的光标-->循环-->退出条件：notfound

变量：1.初始值 2.如何得到
每个段的员工数
count1 number;
count2 number;
count3 number;
每个部门的工资总额：
saltotal number;
1.select sum(sal) into saltotal from emp where deptno =???
2.累加    
*/
declare
     --部门光标
     cursor cdept is select deptno from scott.dept ;
     pdeptno dept.deptno%type;

     --部门中员工的薪水
     cursor cemp(dno number) is select sal from scott.emp where deptno = dno;
     psal scott.emp.sal%type;
     --每个段的员工数
     count1 number;
     count2 number;
     count3 number;
    --每个部门的工资总额：
     saltotal number;
     begin 
       --打开部门光标
       open cdept;
           loop
             --取出一个部门
             fetch cdept into pdeptno;
             exit when cdept%notfound;

            --初始化工作
             count1:=0;count2:=0;count3:=0;
             --得到部门的工资总额
             select sum(sal) into saltotal from scott.emp where deptno =pdeptno;

                 --取部门中员工的薪水
                 open cemp(pdeptno);
                     loop
                       --取一个员工的薪水
                       fetch cemp into psal;
                       exit when cemp%notfound;

                       --判断薪水的范围
                       if psal<3000 then count1:=count1+1;
                       elsif psal>3000 and psal<6000 then count2:=count2+1;
                       else count3 :=count3+1;
                       end if;
                      end loop;
                  close cemp;
               --保存当前部门的结果
               insert into msg values(pdeptno,count1,count2,count3,nvl(saltotal,0));
        	end loop;
    close cdept;
   end;
       select * from msg;
       create table msg(
       pdeptno varchar2(20),
       count1 number,
       count2 number,
       count3 number,
       saltotal number
       );

成绩统计

/*
SQL语句
   1.得到有哪些系
   select dno,dname from dep -->光标-->循环-->退出条件：notfound
   2.得到系中，选修了“大学物理”课程学生的成绩
   select grade from sc where cno =(select cno from course where cname=??) and sno in (select sno from student where dno=??);
   -->带参数的光标 -->循环-->退出条件：notfound
   变量：1.初始值 2.如何得到
   每个分数段的人数
   count1 number ;count2 number; count3 number;
   每个系选修了“大学物理”学生的平均成绩
   avggrade number;
   1.算术运算
   2.select avg(grade) into avggrade from sc where cno = (select cno from course where cname=??)
and sno in (select sno from student where dno =??);
*/
declare
    --系的光标
    cursor cdept is select dno,dname from dep;
    pdno dep.dno%type;
    pdname dep.dname%type;
    
    --成绩光标
    cursor cgrade(coursename varchar2,deptno number) is 
                             select grade from sc where cno =(select cno from course where cname=coursename) 
                             and sno in (select sno from student where dno=depno);
    pgrade sc.grade%type;
    --每个分数段的人数
    count1 number;count2 number;count3 number;
    --每个系选修了“大学物理”学生的平均成绩
    avggrade number;
    --课程名称
    pcourseName varchar2 :='大学物理';

begin 
  open cdept;
      loop
        --取一个系的信息
        fetch cdept into pdno,pdname;
        exit when cdept%notfound;
        --初始化的工作
        count1:=0;count2:=0;count3:=0;
        --系的平均成绩
      	select avg(grade) into avggrade from sc where cno = 
                             (select cno from course where cname=pcourseName)
                             and sno in (select sno from student where dno =pdno);
                                 --取系中，选修了大学物理的学生成绩
            open cgrade(pcourseName,pdno);
                loop
                      --取一个学生的成绩
                      fetch grade into pgrade;
                      exit when cgrade%notfound;
                      --判断成绩的范围
                      if pgrade<60 then count1:=count1+1;
                      elsif pgrade>=60 and pgrade<85 then count2:=count2+1;
                      else  count3:=count3+1;
                      end if ;
                 end loop;
            close cgrade;
         --保存当前的结构
         insert into msg1 values(pcourseName,pdname,count1,count2,count3,avggrade);
       end loop;
	 close cdept;
  commit
  dbms_output.put_line('统计完成');
  end;

触发器

数据库触发器是一个与表相关联的、存储的PL/SQL程序

​ 每当一个特定的数据操作语句(insert、update、delete)在指定的表上发出时，Oracle自动地执行触发器中定义的语句序列。

第一个触发器

create trigger saynewemp
after insert 
on emp
declare
begin
  dbms_output.put_line('成功插入新员工')
end;
  select * from emp;
  insert into emp(empno,ename,sal,deptno) values(1001,'Toom',3000,10);

触发器的具体应用场景

·复杂的安全性检查

·数据的确认

·数据库的审计

·数据的备份和同步

创建触发器的语法

create [or replace] tigger 触发器名

{before|after}

{delete|insert|update|of列名}

on 表名

[for each row [when(条件)]]

plsql块

触发器类型

语句级触发器（无for each row）

​ 对应的是表

​ 在指定的操作语句操作之前或之后执行一次，不管这条语句影响了多少行

行级触发器（有for each row）

​ 对应的是行

​ 触发语句作用的每一条记录都被触发。在行级触发器中使用：old和：new伪记录变量，识别值的状态。

触发器案例一：复杂的安全性检查

禁止在非工作时间插入新员工

/*
1.周末：to_char(sysdate,'day')in ('星期六','星期日')
2.上班前，下班后：to_number(tochar(sysdate,'hh24')) not between 9 and 18
*/
create or replace trigger securityemp
before insert
on emp
begin
  if to_char(sysdate,'day')in ('星期六','星期日') or
    to_number(tochar(sysdate,'hh24')) not between 9 and 18 then
    --禁止insert新员工
    raise_application_error(-20001,'禁止在非工作时间插入新员工');
    end if;
end;

触发器案例二：数据的确认

涨工资不能越涨越少

/*
伪记录变量
   :old 和 :new 代表同一条记录
   :old 表示操作该行之前，这一行的值
   :new 表示操作该行之后，这一行的值
*/
create or replace tigger checksalary
before update
on emp
for each row 
begin
  if :new.sal<:old.sal
    then
    raise_application_error(-20002,'涨后的薪水不能少于涨前的薪水,涨后的薪水'||:new.sal||'涨前的薪水：'|
    |:old.sal);
    end if; 
  end;

触发器案列三：数据库的审计–>基于值的审计

给员工涨工资，当涨后的薪水超过6000块钱的时候，审计该员工的信息

 --创建表，用于保存审计信息
 create table audit_info
 (
   information varchar2(200)
 );
  create or replace trigger do_audit_emp_salary
  after update
  on emp
  for each row
    begin
      --当涨后的薪水大于6000，插入审计信息
      if:new.sal>6000 then
      insert into audit_info values(:new.empno||' '||:new.ename||' '||:new.sal);
      end if;
      end;

触发器案例四：数据的备份与同步

利用触发器实现数据的同步部分

      
/*
      当给员工涨完工资后，自动备份新的工资到备份表中
*/
create or replace trigger sync_salary
after update 
on emp
for each row
  begin
   --当主表更新后，自动更新备份表
   update emp_back set sal =:new.sal where empno =:new.empno; 
    end;