Oracle存储过程

Oracle存储过程

存储过程（Stored Procedure）是在大型数据库系统中，
一组为了完成特定功能的 SQL 语句集，
经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

--存储过程是数据库中的一个重要对象，
--任何一个设计良好的数据库应用程序都应该用到存储过程。

--存储过程【procedure】： 其实就是一堆sql的集合，在数据库服务器端编译好，供其他程序调用

存储过程就是一个命名了的PLSQL块。用户可以通过java程序调用存储过程中PLSQL块。

存储过程的好处：
1）提高数据的安全性；
2）提高程序的执行效率；
3）提高程序的可重用性；

创建存储过程语法：

create [or replace] PROCEDURE 过程名[(参数名 in/out 数据类型)]
 --声明部分
as/is
begin
 --PLSQL 子程序体；
end [过程名];

--给某个员工加薪，输出加薪之前的 和之后 的工资

create or replace procedure pro_add_sal(eno  in number  ,money in number)
as
    --定义变量
    oldsal emp.sal%type;   
    newsal emp.sal%type;

begin
   --查询出原来的工资
   select sal into oldsal from emp where empno = eno;
   dbms_output.put_line('涨薪之前的工资：'||oldsal);

   --加薪
   update emp set sal = sal + money where empno = eno;
   commit;

   --查询加薪之后的工资
   select sal into newsal from emp where empno = eno;
   dbms_output.put_line('涨薪之后的工资：'||newsal);

end;

--调用存储过程：有两种方法
--第一种：了解: 这种方式如果有返回值的时候，获取不了
call pro_add_sal(7788,100);

--第二种：在plsql中调用存储过程:好处：可以接收返回值

declare

begin
   pro_add_sal(7788,200);
end;

create or replace procedure addSal1(eno in number) 
is
pemp emp%rowtype;

begin
      select * into pemp from emp where empno = eno;
      update emp set sal = sal + 100 where empno = eno;
      dbms_output.put_line('涨工资前' || pemp.sal || '涨工资后' ||(pemp.sal + 100));

end addSal1;


--调用
begin
 -- Call the procedure
 addsal1(7902);
 commit;
end;

--定义一个存储过程,计算1-10的和,并将计算结果输出到控制台
create or replace procedure proc_sum(p_sum out number) as
  --声明一个临时变量用于存储求和结果
  temp number := 0;
begin 
  --将1-10赋值给变量p_num
  for p_num in 1..10
  loop
    --求1-10的和
    temp := temp + p_num;
   end loop;
  --将求出的和赋值给变量p_sum
  p_sum := temp;
end;


--调用定义好的求和存储过程,获取求和结果
declare
  --声明一个输出参数
  total number(10);
begin
  --调用求和存储过程,并将输出参数传入
  proc_sum(total);
  --在控制台输出和
  dbms_output.put_line(total);
end;

--既可以把数据传入存储过程里面，也可以把数据从存储过程中传出。in out同时使用
--定义一个存储过程,计算1-num的和,并将计算结果输出到控制台
create or replace procedure proc_sum(p_sum in out number) as
  --声明一个临时变量用于存储求和结果
  temp number := 0;
begin 
  --将1-p_sum赋值给变量p_num
  for i in 1..p_sum
  loop
    --求1-p_sum的和
    temp := temp + i;
   end loop;
  --将求出的和赋值给变量p_sum
  p_sum := temp;
end;



--调用定义好的求和存储过程,获取求和结果
declare
  --声明一个输出参数
  total number(10):=&total;
begin
  --调用求和存储过程,并将输出参数传入
  proc_sum(total);
  --在控制台输出和
  dbms_output.put_line(total);
end;

删除存储过程

drop procedure 过程名;

如果存储过程的参数类型是一个游标。
注意: 将输出参数声明为游标的格式为:
变量名 out sys_refcursor

--定义存储过程,实现根据部门编号查询该部门的员工信息,员工信息需要通过输出参数传出(输出参数的类型为游标)
create or replace procedure proc_emp_dept(p_deptno dept.deptno%type,emp_cursor out sys_refcursor) 
as

begin
  --打开游标,并给游标类型的输出参数进行赋值
  open emp_cursor for select * from emp where deptno=p_deptno;

end;

--调用存储过程获取员工信息
declare 

  --声明游标变量
  emp_cursor sys_refcursor;
  --声明行变量
  p_emp emp%rowtype;

begin
  --调用存储过程
  proc_emp_dept(10,emp_cursor);
  loop
    --将游标中的每一行数据赋给行变量
    fetch emp_cursor into p_emp;
    --设置一个条件跳出loop循环
    exit when emp_cursor%notfound;
    dbms_output.put_line('姓名'||p_emp.ename||',工资:'||p_emp.sal);
  end loop;

  --关闭游标
  close emp_cursor;

end;