存储过程

procedure –简单的说就是专门干一件事一段sql语句。

百度百科上是这样解释的，存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，
存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数来调用存储过程。
可以由数据库自己去调用，也可以由java程序去调用。

为什么要写存储过程？

1.效率高

存储过程编译一次后，就会存到数据库，每次调用时都直接执行。而普通的sql语句我们要保存到其他地方(例如：记事本上),
都要先分析编译才会执行。所以想对而言存储过程效率更高。

2.降低网络流量

存储过程编译好会放在数据库，我们在远程调用时，不会传输大量的字符串类型的sql语句。

3.复用性高

存储过程往往是针对一个特定的功能编写的，当再需要完成这个特定的功能时，可以再次调用该存储过程。

4.可维护性高

当功能要求发生小的变化时，修改之前的存储过程比较容易，花费精力少。

5.安全性高

完成某个特定功能的存储过程一般只有特定的用户可以使用，具有使用身份限制，更安全。

1.匿名块：没有名字，运行后不会保存，想要执行相同的功能只能重复书写
2.命名块：create 开头，有名字， 运行一次后会保存在系统中，后续使用时可以直接调用

常见的命名块有：存储过程，存储函数，触发器，包

存储过程创建跟使用步骤

1.创建存储过程

==or replace 替换,第一次创建存储过程时可以不写，修改存储过程用==

create or replace procedure 存储过程名(参数 参数类型 数据类型) is/as
声明摆放位置 ==is/as 后面跟声明，相当于匿名块中的declare==
begin
要实现的操作;
end;

参数 ==表中的列名==
参数类型：
in –传入一个参数–默认值，不写参数类型默认为in
out –传出一个参数
in out –同一个参数，先传入，在传出

2.调用存储过程

  1. call 存储过程名(参数);–通过关键词调用存储过程
  2. begin
    存储过程名(参数);–通过命令快调用存储过程
    end;

一. 无参数的存储过程

不需要传入参数，也不需要传出参数

1.创建存储过程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
create or replace procedure pro_aaa is
  --声明摆放位置--is/as 后面跟声明，相当于匿名块中的declare 
begin
  dbms_output.put_line('南京终于不热了！');
end;
--存储过程创建结束

--调用aaa存储过程
call pro_aaa();
begin
  pro_aaa();
end;
--调用存储过程结束

--删除aaa存储过程
drop procedure pro_aaa;
--删除存储过程结束
1
2
3
4
5
6
7
8
9
10
11
create or replace procedure pro_aaa is--匿名块转化成存储过程的方法》把declare 替换成创建存储过程的语句
  v_no number(2) :=1;
begin
  while v_no < 8
  loop
    dbms_output.put_line(v_no);
    v_no := v_no + 1;
  end loop;
end;

call pro_aaa();

2.传入参数的存储过程

参数的个数、类型可以自定义，但是参数不允许指定长度

例：写一个存储过程，要求打印调用时传入的文字

1
2
3
4
5
6
create or replace procedure pro_wenzi (wenzi in varchar2) is
begin
  dbms_output.put_line(wenzi);
end;

call pro_wenzi('&要传入的文字');

例：写一个存储过程，当我们输入两个任意数值时，打印较大的那个
当我们输入的数值一样时，提示’请输入两个不同的数值’

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
declare
  no1 number:=19;
  no2 number:=19;
begin
  if no1 > no2 then
    dbms_output.put_line(no1);
  elsif no1 < no2 then
    dbms_output.put_line(no2);
  else
    raise_application_error(-20000,'请输入两个不同的数值');
  end if;
end;


create or replace procedure pro_wenzi(no1 in number, no2 in number) is
begin
  if no1 > no2 then
    dbms_output.put_line(no1);
  elsif no1 < no2 then
    dbms_output.put_line(no2);
  else
    raise_application_error(-20000,'请输入两个不同的数值');
  end if;
end;



call pro_wenzi(19,19);

begin
  pro_wenzi(18,19);
end;

例：写一个存储过程，输入员工编号，打印员工姓名

1
2
3
4
5
6
7
8
9
create or replace procedure pro_xingming(no1 number) is
  v_emp emp%rowtype;
begin
  select * into v_emp from emp where empno = no1;
  dbms_output.put_line(v_emp.ename);
end;


call pro_xingming(&员工编号);

3.传出参数的存储过程

得有接收参数的组件

例：写一个存储过程，传出一个员工姓名

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
declare
  v_emp emp%rowtype;
  xm varchar2(20);
begin
  select * into v_emp from emp where empno = 7788;
  xm := v_emp.ename;---------要传出的参数得有赋值的步骤
  dbms_output.put_line(xm);
end;


create or replace procedure pro_xingming2(xm out varchar2) is
  v_emp emp%rowtype;
begin
  select * into v_emp from emp where empno = 7788;
  xm := v_emp.ename;---------要传出的参数得有赋值的步骤
end;

declare
  v_xingming varchar2(100);--声明一个数据类型用于接收传出的参数
begin
  pro_xingming2(v_xingming);---调用中写一个数据类型接收传出的参数，名称可以跟出参 参数 一致，也可以不同
  dbms_output.put_line(v_xingming);
end;

select * from emp;

4.in out先传入再传出

传入的参数跟传出的参数数据类型要一致

例：写一个存储过程，传入一个员工编号，传出一个薪资

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
declare
  no1 number := 7788;
  xz number;
  v_emp emp%rowtype;
begin
  select * into v_emp from emp where empno=no1;
  xz:= v_emp.sal;
  dbms_output.put_line(xz);
end;

create or replace procedure pro_xingmingxinzi(xmxz in out number) is
  v_emp emp%rowtype;
begin
  select * into v_emp from emp where empno=xmxz;
  xmxz:= v_emp.sal;
end;

declare
  xz number :=7788;
begin
  dbms_output.put_line(xz);
  pro_xingmingxinzi(xz);
  dbms_output.put_line(xz);
end;

例：写一个存储过程，传入一个数字，传出一个数字

1
2
3
4
5
6
7
8
9
10
11
12
create or replace procedure pro(shuzi in out number) is--2--4
begin
  dbms_output.put_line(shuzi);--3 打印18
  shuzi := shuzi + 10; --4  修改为28
end;

declare
  shuzi2 number :=&数字;--1
begin
  pro(shuzi2);--2--4
  dbms_output.put_line(shuzi2);--5
end;

5.多个参数in,out先传入再传出

例：写一个存储过程，传入一个员工编号，传出一个员工姓名

1
2
3
4
5
6
7
8
9
10
11
12
create or replace procedure pro_emp(xm out varchar2,no1 in number) is
begin
  select ename into xm from emp where empno = no1;
end;


declare
  xm varchar2(100);
begin
  pro_emp(xm,7788);
  dbms_output.put_line(xm);
end;

思考题：在存储过程中 入参与出参 的执行顺序与它俩谁在前面有关系吗？

在存储过程中，先执行入参，后执行出参，==跟位置没关系==。

二、在存储过程中使用游标

无参数传递

1
2
3
4
5
6
7
8
9
10
11
create or replace procedure pro_cur is
  cursor cursor_emp is select * from emp;
begin
  for v_emp in cursor_emp loop
    dbms_output.put_line(v_emp.ename || ' ' || v_emp.sal);
  end loop;
end;

begin
  pro_cur();
end;

传入一个参数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
create or replace procedure pro_cur(bm in int) is
  cursor cursor_emp is select * from emp where deptno = bm;
begin
  for v_emp in cursor_emp loop
    dbms_output.put_line(v_emp.ename || ' ' || v_emp.sal);
  end loop;
end;

begin
  pro_cur(20);
end;


sys_refcursor  --系统游标，这是一个数据类型

例：传入一个游标，打印出员工的姓名与薪资—传入一组数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
create or replace procedure pro_cur(cur in sys_refcursor) is--接收游标并打印
  v_emp emp%rowtype;
begin
  loop
    fetch cur into v_emp;
    exit when cur%notfound;
    dbms_output.put_line('员工姓名为：'||v_emp.ename||'，员工薪资为：'||v_emp.sal);
  end loop;
end;

declare--声明游标并赋值
  cur_a sys_refcursor;--声明游标
begin
  open cur_a for select * from emp;--打开游标
  pro_cur(cur_a);--调用存储过程--传递参数
  close cur_a;--关闭游标
end;

例：传出一个游标，打印出员工的姓名与薪资—传出一组数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
create or replace procedure pro_cur(cur out sys_refcursor) is--打开游标并赋值
begin
  open cur for select * from emp;--打开游标
end;


declare--接收游标并打印
  cur_a sys_refcursor;--声明游标
  v_emp emp%rowtype;
begin
  pro_cur(cur_a);--调用存储过程--打开游标
  loop
    fetch cur_a into v_emp;--传递参数
    exit when cur_a%notfound;
    dbms_output.put_line('员工姓名为：'||v_emp.ename||'，员工薪资为：'||v_emp.sal);
  end loop;
  close cur_a;--关闭游标
end;

注意点：

1.存储过程可以在当前用户下–> all object –> procedures 文件夹中右键查看
2.存储过程写错了没有报错弹窗，但是存储过程名左上角有红色❌
3.错误的存储过程调用时会报错
4.数据类型后面不要跟东西
5.想要打印传入的参数，打印中要写==参数名==

拓展题：

1、有表student(s_no, s_name, s_age, s_grade)，其中s_no-学号，也是主键，是从1开始向上排的（例如：第一个学生学号是1，第二个是2，一次类推）；s_name-学生姓名；s_age-学生年龄；s_grade-年级；这张表的数据量有几千万甚至上亿。一个学年结束了，我要让这些学生全部升一年级，即，让s_grade字段加1。这条sql，写出来如下：
update student set s_grade=s_grade+1
但是我们直接运行这条sql，会因数据量太大会把数据库undo表空间撑爆，从而发生异常。那我们来写个存储过程，进行批量更新，我们每10万条提交一次。

1

2、现有学生表（学号 sid，姓名sname，所在系名xi_name）
选课表（课程号cid，学号 sid，老师tid，可容纳总人数all_student，已选人数checked，剩余可选人数 space_num）
① 编写一个程序，输入学号，返回学生姓名、所在系名，所选课程
② 编写一个程序：向学生表插入一条数据，返回该学生可以选学的课程id
③ 创建选课表历史记录表（check_history），并创建一个触发器，当对选课表进行删、改时，将原数据添加到选课历史记录表中

1

3、有一个玩具售价表toy_price，包含字段（玩具id：toy_id, 玩具名称 toy_name,数量toy_num,单价price）；一个玩具订单表（订单编号order_id ,主键 客户名称：order_name玩具id：toy_id,数量：o_num）
要求编写一个程序：传入一条玩具订单表信息，看该玩具是否有足够的数量，如果有更改玩具售价表中数量，如果没有，弹出‘该玩具数量不足请补货’

1

块，区，段，表空间