Oracle--存储过程（重要）

存储过程

之前的PL/SQL块, 匿名的PL/SQL 没有名字, 数据库不会存储, 每一次执行: 都要经过编译-运行
存储过程: 都是命名的PL/SQL, 存储在数据库中,作为数据库的一个对象,类似表, 只需要第一次把这个存储过程
编译(把这个存储过程保存到数据库), 后面调用存储过程, 不需要进行再编译, 直接运行
-- 关键字: Procedure

语法：

create [or replace] procedure 存储过程名
        [(参数1 model 类型1，参数2  model 类型2,.....)]
   is/as
        [变量。游标声明]
   begin  --相当于java中的{
          --执行语句
   [exception
        --异常处理
   ]
   end; 

注意: 存储过程: 变量的声明, 游标声明,...  写在is/as 之后, 没有declare

参数列表:  参数名   model(IN: 输入参数, out:输出参数, inout: 输入输出参数, 数据的转换)    数据类型

无参的存储过程：例：

--编写一个无参的存储过程,  查询7788员工姓名,薪水
--选中这个存储过程, 点执行,  编译存储过程, 并且把存储过程保存到数据库
-- 我们还需要查看这个存储过程是否有错误, 有错误,一定需要修改, 修改之后一定在一次编译
create or replace procedure  proc_findNameAndSal 
is
  --定义变量
  v_ename emp.ename%type;
  v_sal emp.sal%type;
begin
  select ename , sal into v_ename,v_sal from emp where empno = 7788;
  
  dbms_output.put_line(v_ename||v_sal);
end;

运行存储过程：

 --第一种调用方式，通过pl/sql运行
begin
  proc_findNameAndSal;
end;
--第二种方式: 在小黑屏,dos命令窗口:
dbms_output.put_line() 默认不显示, 打开显示
set serverout on;
exec 存储过程名;

有参的存储过程：例：

　　存储过程的参数 model
　　in 输入参数: 这个参数只能由调用者传递过程, 这个参数不能再存储过程内部修改 ,默认model 就是in

--查询指定员工编号的姓名薪水
create or replace procedure  proc_findNameAndSal2(eno in number) 
is
  --定义变量
  v_ename emp.ename%type;
  v_sal emp.sal%type;
begin
  --eno := 7788;  --错误的
  select ename , sal into v_ename,v_sal from emp where empno = eno;
  
  dbms_output.put_line(v_ename||v_sal);
end;

调用：

begin
  proc_findNameAndSal2(7902);
end;

存储过程授权

--授予存储过程的执行权限 给其他用户
-- 做实验, 一定把用户先断开,再连接
-- grant  execute on 存储过程名  to 用户名；
grant  execute on proc_findNameAndSal to zhangsan;
--撤销权限 
revoke execute on proc_findNameAndSal from zhangsan;

删除存储过程

--语法: drop  procedure  存储过程名;
drop procedure proc_findNameAndSal;

传值方式：

--给某个部门加薪，最多一次加薪不能超出1000
--  部门参数,  加薪:钱也是参数  in
create or replace procedure  proc_addSal(dno emp_back3.deptno%type, sal1 emp_back3.sal%type) 
is
begin
   if  sal1 > 1000 then
     dbms_output.put_line('加薪超过标准,不能加薪');
   else
      update emp_back3 set sal = sal + sal1  where deptno =  dno;
      commit;
   end if;
end;
-- 执行
begin
  --传递参数: 第一种方式: 按位置传参
  --proc_addSal(10,501);
  
  --第二种方式: 按参数名 
  --语法: 参数名 => 值
   --proc_addSal(dno => 10,sal1 => 501);
   proc_addSal( sal1 => 501, dno => 20);
end;

out 参数

输出参数, 返回给调用者, 这个参数必须要在存储过程内部赋值

--编写一个存储过程, 根据用户编号,查询员工的姓名, 薪水(返回给调用)
create or replace procedure proc_findNameAndSal2( eno emp.empno%type,
  p_ename out emp.ename%type, p_sal out emp.sal%type
)
as
  --定义变量
begin
   select ename , sal into p_ename , P_sal from emp where empno = eno;
end;

--带输出参数的存储过程的调用, 必须定义变量来接收
declare
   --接收输出参数的变量
   ename emp.ename%type;
   sal emp.sal%type;
begin
   --把输出参数的结果赋值给变量
   proc_findNameAndSal2(7566,ename,sal);
   dbms_output.put_line(ename||', '||sal);
end;

使用游标

-- 查询指定部门的员工信息,
-- 部门编号:输入参数   员工信息(多行数据), 使用游标  输出参数,  动态游标(SYS_REFCURSOR)
-- 返回游标给调用者,一定不能再存储过程中关闭游标, 关闭游标, 相当于 清空数据
create  or  replace  procedure  proc_findEmpsByDno(
   dno emp.deptno%type, emps out sys_refcursor
)
is
begin
   --打开游标,绑定sql语句
   if emps%isopen then
      null;
    else 
       open emps for select * from emp where deptno = dno;
    end if;
end;
--调用 返回游标的存储过程
declare
  --定义一个系统动态游标,接收
  c_empsCursor  sys_refcursor;
  v_emp emp%rowtype;
begin
  proc_findEmpsByDno(20,c_empsCursor);
  --提取数据:  不要在打开游标, 应该在存储过程中,已经打开游标
  --直接提取数据
  loop
    fetch c_empsCursor into v_emp;
    exit when c_empsCursor%notfound;
    dbms_output.put_line(v_emp.ename||', '||v_emp.job||', '||v_emp.sal);
  end loop;
  --关闭游标
  close c_empsCursor;
end;

例题：.查找scott用户的emp表中的所有数据,如果job为MANAGER
工资大于2500，则下调百分之10;如果小于2500 则增加百分之10,
如果job为SALESMAN 工资小于1500 则上调百分之10;
最后符合条件的人.插入到test表中.并且打印符合条件的人数的个数.
Test表结构如下:
　　Ename varchar2
　　Sal1 number --原来的工资
　　Sal2 number --修改之后的工资

create or replace procedure  proc_findJobsalCreatetable(c_emp out sys_refcursor)
is
begin
  --打开游标,绑定sql语句
   if c_emp%isopen then
      null;
    else 
       open c_emp for  select * from emp;
    end if;
end;
-- 运行
declare 
 --定义一个系统动态游标,接收
  v_emp sys_refcursor;
  myemp emp%rowtype;
  v_sal emp.sal%type;
  -- 统计符合条件的个数
  num1 number := 0; -- MANAGER
  num2 number := 0;
begin
  proc_findJobsalCreatetable(v_emp);
  loop 
     fetch v_emp into myemp;
     exit when v_emp%notfound;
     if myemp.job = 'MANAGER' then
        if myemp.sal >= 2500 then
          v_sal := myemp.sal*0.9;
        elsif myemp.sal < 2500 then
          v_sal := myemp.sal*1.1;
        end if;
         dbms_output.put_line(myemp.ename||' ,'||myemp.job||' ,'||v_sal);
         -- 将数据插入到test表中
         insert into test values(myemp.ename,myemp.sal,v_sal);
         num1 := num1+1;
 
     elsif myemp.job = 'SALESMAN' then
        if myemp.sal < 1500 then
          v_sal := myemp.sal*1.1;
        else
           v_sal := myemp.sal;
        end if;
         dbms_output.put_line(myemp.ename||' ,'||myemp.job||' ,'||v_sal);
         -- 将数据插入到test表中
         insert into test values(myemp.ename,myemp.sal,v_sal);
         num2 := num2+1;
          
     end if;
  end loop;
     dbms_output.put_line('统计符合MANAGER的个数:'||num1);
     dbms_output.put_line('统计符合SALESMAN的个数:'||num2);
  close v_emp;
end;
-- 创建test表
create table test(
  Ename varchar2(30),
  Sal1 number,
  Sal2 number
);