Oracle存储过程语法详解—及8道案例练习

作者: 夜希辰 | 来源:发表于2020-09-17 09:18 被阅读0次

这企鹅真可爱

本文主要讲解ORACLE数据库的存储过程，如果想学习了解MYSQL数据库的存储过程可参考本篇文章mysql存储过程学习笔记，在网上找过很多ORACLE存储过程的文章，有语法、有练习题类。
都不是很友好吧，所以决定写一篇针对入门的文章，文章结构是参考的一篇mysql存储过程。

文章目录：
一、创建一个简单的存储过程
二、存储过程的变量
三、存储过程参数
四、存储过程条件语句
五、存储过程循环语句
六、存储过程游标的使用
七、8道存储过程——案例实战
八、语法及案例使用数据

备注：数据在文章末尾

什么是存储过程

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

存储过程的特点

1、能完成较复杂的判断和运算
2、可编程行强，灵活
3、SQL编程的代码可重复使用
4、执行的速度相对快一些
5、减少网络之间的数据传输，节省开销

MYSQL和ORACLE存储过程的语法差异还是比较大的，下面我们一起学习ORACLE存储过程的语法吧。

一、创建一个简单的存储过程

1、创建存储过程的简单语法

create procedure 名称  as
begin
.........
end

2、创建一个简单的存储过程

create or replace procedure test1 as
begin
  dbms_output.put_line('hello word');
end

3、调用存储过程

call test1()

运行结果如图

二、存储过程的变量

目录
1、先通过一个简单的例子来学习变量的声明和赋值
2、变量分类
2.1、普通变量
2.2、引用变量
2.3、记录型变量

1、先通过一个简单的例子来学习变量的声明和赋值

create or replace procedure select_emp as
--定义变量my_income
my_income varchar(20);

begin
-- 使用select语句给变量赋值，将emp_test 表中worker_no = '200010'的income赋值给变量my_income
  select  income into my_income from emp_test where worker_no = '200010';

--打印输出变量my_income值
  dbms_output.put_line('工资'||my_income);
end;

总结
(1)、在begin程序体前声明变量，变量必须先声明后使用；
(2)、变量具有数据类型和长度，与ORACLE的数据类型保持一致
(3)、变量可以通过select into的方式赋值，也可以通过：=赋值

通过上面的案例，大家对变量有了简单的了解，下面详细介绍一下变量的分类。

2、变量分类

1、普通数据类型（char,varchar2,date,number,boolean,long)
2、特殊变量类型（引用型变量，记录型变量）
引用型变量，变量的数据类型取决表中的数据类型；记录型变量，变量不是接受一个值，是一行值。

声明变量语法:变量名变量类型（变量长度）

---普通变量         v_name     varcar2(20);
---引用型变量       v_income   emp_test.income%TYPE;
---记录型变量       v_emp      emp_test%ROWTYPE  //表示变量v_emp存的是表中emp_test一整行的数据

2.1、普通变量

当我们想使用普通变量的时候，需要先给普通变量命名，制定变量的类型和长度。我们在赋值的时候可以在声明变量的时候直接赋值:=，也可以在程序中复制，通过select语句赋值。

存储过程参数

【示例】打印人员个人信息，包括：工号、工资、部门

create or replace procedure test1 as
//定义变量
my_number varchar2(20);              --工号
my_income int  := 3000;             --声明变量直接赋值
my_depart varchar2(50);       --部门
begin
---通过SELECT语句给变量赋值
  select '5号部门' into my_depart from dual;

---打印输出
  dbms_output.put_line('姓名'||my_number|| '工资'||my_income||'部门'||my_depart); 
end ;

变量赋值的方式有两种：
1.直接赋值语句 :=
2.语句复制，使用select……into……赋值：（语法select值into变量）

2.2、引用变量

引用变量变量的类型和长度取决于表中字段的类型和长度，通过表名.列名%TYPE指定变量的类型和长度
例如： v_worker emp_test.worker_no%TYPE

【示例】定义引用变量，打印工号为200010员工的个人信息，包括：工资、部门

--定义引用变量，打印工号为200010员工的个人信息，包括：工资、部门
create or replace procedure test2 as
----一、定义变量
my_income emp_test.income%TYPE;--工资,引用型变量
my_depart emp_test.department%TYPE;--部门,引用型变量
begin
  select income,department into my_income,my_depart from emp_test where worker_no='200010';
  dbms_output.put_line( '工资'||my_income||'部门'||my_depart);  
end ;

总结：使用普通变量定义方式，需要知道表中列的类型，而使用引用类型，不考虑列的类型，适用于数据库定义的更新。

2.3、记录型变量

记录型变量接受表中的一整行记录，语法：变量名称表名%ROWTYPR
例如：v_emp emp_test%ROWTYPE
表示变量v_emp存的是表中emp_test一整行的数据

【示例】定义记录型变量，打印工号为200010员工的个人信息，包括：工资、部门

--定义记录型变量，打印工号为200010员工的个人信息，包括：工资、部门
create or replace procedure test3 as
----一、定义变量
v_emp emp_test%ROWTYPE;---v_emp记录型变量，接受表中的一整行记录

begin
  select * into v_emp from emp_test where worker_no='200010';
  dbms_output.put_line( '工资'||v_emp.income||'部门'||v_emp.department);  
end ;

总结：记录型变量只能存储一个完整的行数据，记录型变量使用场景：如果有一个表，有100个字段，如果程序要使用这100个字段，使用引用型变量一个个声明，会特别麻烦，记录型变量可以方便解决这个问题。

三、存储过程参数

目录：
1、基本语法
1.1、存储过程的传入参数IN
1.2、存储过程的传出参数out

1、基本语法

create procedure 名称([IN|OUT|INOUT] 参数名 参数数据类型 )
begin
.........
end

存储过程的参数类型有：IN,OUT,INOUT，下面分别介绍这个三种类型：

1.1、存储过程的传入参数IN

（1）传入参数，类型为in,表示该参数的值必须在调用存储过程时指定，如果不显示指定为in,那么默认就是in类型。
（2）IN类型参数一般只用于传入，在调用过程中一般不作为修改和返回

通过一个实例来演示：
需求：编写存储过程，传入员工工号，根据工号输出该员工的工资

CREATE OR REPLACE PROCEDURE test_income(worker_id varchar2) as
my_income  VARCHAR2(100); 
begin
select income into  my_income from emp_test where worker_no = worker_id;
dbms_output.put_line(my_income);
end;

//调用存储过程
call test_income(200013)

1.2、存储过程的传出参数out

(1) out类型参数只能接收赋值，不能给其他变量赋值。
(2) 输出模式的参数，用于输出值，会忽略传入的值,在子程序内部可以对其进行修改。
(3) 调用时参数需要使用变量.

需求：调用存储过程时，传入worker_id，返回该用户的工资income

CREATE OR REPLACE PROCEDURE test_out(worker_id in varchar2,my_income out emp_test.income%TYPE) as
begin
select income into  my_income from emp_test where worker_no = worker_id;
dbms_output.put_line(my_income);
end;

-- 调用
declare
my_income   int;
begin
  test_out(200010,my_income);
end;

运行如图

1.3、存储过程的可变参数in out

(1) 与out类型相比不同是默认初始化参数不为null，传的是什么就是什么。
(2) 调用时，参数需要使用变量.

create or replace procedure pro_in_out(p_num in out number)
is
begin
  dbms_output.put_line(p_num);
  p_num:=10;
end;

-- 调用
declare
test number:=1;
begin
  pro_in_out(test);
  dbms_output.put_line(test);
end;

运行如图

关于存储过程 in、out、in out 参数的使用方法还模糊的可以看这篇文章《Oracle 存储过程 in、out、in out 参数的使用方法》，把文章中的代码执行一遍就理解含义。

四、存储过程条件语句

目录
1、基本语法结构
2、存储过程条件语句—案例
2.1、简单条件语句—应用案例
2.2、多条件语句—应用案例

1、基本语法结构

(1）条件语句基本结构

if() then...else...end if;

(2）多条件判断语句

if()   then...
elsif()   then...
else ...
end if;

2、存储过程条件语句—案例

2.1、简单条件语句—应用案例

编写存储过程，如果员工工号worker_no是偶数则返回工资income,否则返回部门department

create or replace procedure test_worker(worker_id varchar2) as
my_income varchar(20);
my_department varchar(20);
begin
  if(mod(to_number(worker_id),2)=0)  then
      select income into my_income  from emp_test where worker_no =worker_id;
      dbms_output.put_line(my_income);
  else
      select department into my_department  from emp_test where worker_no =worker_id;
      dbms_output.put_line(my_department);
   end if;
end;

//调用存储过程
call test_worker(200013);
call test_worker(200012)

运行如图

2.2、多条件语句—应用案例

创建一个存储过程，以员工号为参数，修改该员工的工资。
若该员工属于10号部门，则工资增加150；
若属于20号部门，则工资增加200；
若属于30号部门，则工资增加250；
若属于其他部门，则增加300。

CREATE OR REPLACE PROCEDURE add_income(worker_id varchar2) as
--1、定义变量部门 my_department
my_department varchar(20);
--2、查出变量值
begin
select  department into my_department  from emp_test  where worker_no =worker_id;
if (my_department='10号部门') then
  update emp_test set  income = income+150 where worker_no =worker_id;
  --commit;
elsif (my_department='20号部门') then
   update emp_test set  income = income+200 where worker_no =worker_id;
elsif (my_department='30号部门') then
   update emp_test set  income = income+250 where worker_no =worker_id;
else
   update emp_test set  income = income+300 where worker_no =worker_id;
   commit;
   end if;
end;

//调用存储过程
call add_income(200010);
call add_income(200015)
---执行后结果
select *  from emp_test

五、存储过程循环语句

oracle中的循环语句大致分三种：While、For、Loop

目录
1、while语句的基本结构
2、LOOP语句的基本结构
3、for语句的基本结构及案例

1、while语句的基本结构及案例

while(条件)   loop
     ……
end loop;

示例：使用循环语句，向表emp_test中插入十条数据，仅给工号字段插入数据，其它字段不插入数据，插入工号为12001、12002、12003、12004、12005至120010

create or replace procedure test_inset as
my_worker int;
begin
  my_worker :=0;
  while my_worker<10 loop
    my_worker :=my_worker+1;
    insert into emp_test(worker_no) values('1200'||to_char(my_worker));
    commit;
  end loop;
end;

//调用存储过程
call test_inset();
select *  from  emp_test

2、LOOP语句的基本结构及案例

  LOOP
    Exit When(退出条件);
   ……
  END LOOP

示例：使用LOOP循环，打印输出0至5的数字

create or replace procedure loop_test is
i number;
begin
  i := 0;
   LOOP
    Exit When(i > 5);
    Dbms_Output.put_line(i);
    i := i + 1;
  END LOOP;
end ;

----调用存储过程
call loop_test()

运行如图

3、for语句的基本结构及案例

for （） in （）
  loop
    ……
  end loop;

示例：使用FOR循环，打印输出0至5的数字

create or replace procedure for_test is
i number;
begin
  i:=0;
  for i in 1..5 loop
  dbms_output.put_line(i);
  end loop;
end ;

----调用存储过程
call for_test()

运行结果如图

六、存储过程游标的使用

目录
1、什么是游标
2、语法
3、案例

1、什么是游标

用于临时存储一个查询返回的多行数据,通过遍历游标，可以逐行访问处理该结果集的数据
游标的使用方式：声明——>打开——>读取——>关闭

2、语法

//游标声明：
cursor 游标名[(参数列表)] is 查询语句；
//游标打开：
open 游标名；
//游标取值：
fetch 游标名 into 变量列表；
//游标关闭：
close 游标名；

3、案例

示例1：编写存储过程，使用游标，把emp_test表中20号部门的员工工号逐一打印

create or replace procedure cur_test as
my_workerno varchar(20);
---游标声明：
cursor cur_worker is select  worker_no from emp_test where department='20号部门';

begin
--游标打开：
open cur_worker;
LOOP
  ---获取游标中的数据
  fetch cur_worker into my_workerno;--提取cursor,提取结果集中的记录
  
  --退出循环条件
  Exit When cur_worker%notfound;
  dbms_output.put_line('my_workerno:'||my_workerno);
  
end loop;
close cur_worker;

end;

---调用存储过程
call cur_test()

运行结果

七、8道存储过程——案例实战

--建表：
create table emp_test(
worker_no  varchar2(50),---员工工号
income  int,---员工工资
department varchar2(50)--部门
)；

---插入数据
insert into emp_test
select '200010',5000,'10号部门'  from dual union all
select '200011',5000,'20号部门'  from dual union all
select '200012',5000,'30号部门'  from dual union all
select '200013',5000,'40号部门'  from dual union all
select '200014',5000,'20号部门'  from dual union all
select '200015',5000,'40号部门'  from dual

1、创建一个存储过程，以员工号为参数，输出该员工的工资

CREATE OR REPLACE PROCEDURE test_income(worker_id varchar2) as
my_income  VARCHAR2(100); 
begin
select income into  my_income from emp_test where worker_no = worker_id;
dbms_output.put_line(my_income);
end;

//调用存储过程
call test_income(200013)

2、创建一个存储过程，以员工号为参数，修改该员工的工资。若该员工属于10号部门，则工资增加150；若属于20号部门，则工资增加200；若属于30号部门，则工资增加250；若属于其他部门，则增加300。

CREATE OR REPLACE PROCEDURE add_income(worker_id varchar2) as
--1、定义变量部门 my_department
my_department varchar(20);
--2、查出变量值
begin
select  department into my_department  from emp_test  where worker_no =worker_id;
if (my_department='10号部门') then
  update emp_test set  income = income+150 where worker_no =worker_id;
  --commit;
elsif (my_department='20号部门') then
   update emp_test set  income = income+200 where worker_no =worker_id;
elsif (my_department='30号部门') then
   update emp_test set  income = income+250 where worker_no =worker_id;
else
   update emp_test set  income = income+300 where worker_no =worker_id;
   commit;
   end if;
end;

//调用存储过程
call add_income(200010);
call add_income(200015)
---执行后结果
select *  from emp_test

3、编写存储过程，如果员工工号worker_no是偶数则返回工资income,否则返回部门department

create or replace procedure test_worker(worker_id varchar2) as
my_income varchar(20);
my_department varchar(20);
begin
  if(mod(to_number(worker_id),2)=0)  then
      select income into my_income  from emp_test where worker_no =worker_id;
      dbms_output.put_line(my_income);
  else
      select department into my_department  from emp_test where worker_no =worker_id;
      dbms_output.put_line(my_department);
   end if;
end;

//调用存储过程
call test_worker(200013);
call test_worker(200012)

4、创建一个存储过程，用来统计表emp_test表中行数数量

---创建一个存储过程，用来统计表emp_test表中行数数量
create or replace procedure test_count_line as
my_line int;
begin
  select count(1) into my_line from emp_test;
  dbms_output.put_line('表emp_test行数为：'||my_line);
end;

//调用存储过程
call test_count_line()

5、根据员工工号，输出员工的性别（F男性，M女性），部门。输出格式如下：员工200013为男性，在40号部门

---新增性别字段，F男性，M女性
alter table emp_test  add sex varchar(2);
---插入数据
update emp_test set sex='F' where department in('40号部门','10号部门');
update emp_test set sex='M' where department not in('40号部门','10号部门');
---在PLSQL中执行上面语句之后记住提交

create or replace procedure test_sex_dpat(worker_id varchar2) as
my_sex varchar2(20);
my_department varchar2(30);
begin
  select  sex,department into my_sex,my_department  from  emp_test where worker_no =worker_id ;
  if (my_sex='F') then
    dbms_output.put_line('员工'||worker_id||'为男性，在'||my_department);
  else
    dbms_output.put_line('员工'||worker_id||'为女性，在'||my_department);
  end if;
end;

6、使用WHILE ... LOOP ... END LOOP 语法，输出1到5的数字

create or replace procedure test_while as
my_number int;
BEGIN
  my_number := 0; 
  WHILE my_number < 5 LOOP-------当my_number 小于5时执行下面的循 环语句，否则终止程序
    my_number := my_number + 1;
    DBMS_OUTPUT.PUT_LINE(my_number);
  END LOOP;
END;

----调用存储过程
call test_while()

7、向表emp_test中插入十条数据，仅给工号字段插入数据，其它字段不插入数据，插入工号为12001、12002、12003、12004、12005至120010

create or replace procedure test_inset as
my_worker int;
begin
  my_worker :=0;
  while my_worker<10 loop
    my_worker :=my_worker+1;
    insert into emp_test(worker_no) values('1200'||to_char(my_worker));
    commit;
  end loop;
end;

//调用存储过程
call test_inset();
select *  from  emp_test

8、创建一个存储过程，以员工号为参数，返回该员工所在的部门的平均工资。

create or replace procedure test8(i_worker_no emp_test.worker_no%TYPE) as
avg_income int;
begin
  select  avg(income)  into avg_income from emp_test where worker_no=i_worker_no;
  dbms_output.put_line(i_worker_no||'平均工资'||avg_income);
end;

//调用存储过程
call test8('40号部门')

八、语法及案例使用数据

数据

--建表：
create table emp_test(
worker_no  varchar2(50),---员工工号
income  int,---员工工资
department varchar2(50)--部门
)；

---插入数据
insert into emp_test
select '200010',5000,'10号部门'  from dual union all
select '200011',5000,'20号部门'  from dual union all
select '200012',5000,'30号部门'  from dual union all
select '200013',5000,'40号部门'  from dual union all
select '200014',5000,'20号部门'  from dual union all
select '200015',5000,'40号部门'  from dual

后期会新增案例，及游标使用.。
一直以为自己懂了会了，还是要写文章总结才能掌握更深刻，也在写文章中明确找到了自己的不足。
坚持写文章，加油！

Oracle存储过程语法详解—及8道案例练习

什么是存储过程

存储过程的特点

一、创建一个简单的存储过程

二、存储过程的变量

1、先通过一个简单的例子来学习变量的声明和赋值

2、变量分类

2.1、普通变量

2.2、引用变量

2.3、记录型变量

三、存储过程参数

1、基本语法

1.1、存储过程的传入参数IN

1.2、存储过程的传出参数out

1.3、存储过程的可变参数in out

四、存储过程条件语句

1、基本语法结构

2、存储过程条件语句—案例

2.1、简单条件语句—应用案例

2.2、多条件语句—应用案例

五、存储过程循环语句

1、while语句的基本结构及案例

2、LOOP语句的基本结构及案例

3、for语句的基本结构及案例

六、存储过程游标的使用

1、什么是游标

2、语法

3、案例

七、8道存储过程——案例实战

八、语法及案例使用数据

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

大数据爬虫Python AI Sql

数据蛙数据分析每周作业

Oracle存储过程语法详解—及8道案例练习

什么是存储过程

存储过程的特点

一、创建一个简单的存储过程

二、存储过程的变量

1、先通过一个简单的例子来学习变量的声明和赋值

2、变量分类

2.1、普通变量

2.2、引用变量

2.3、记录型变量

三、存储过程参数

1、基本语法

1.1、存储过程的传入参数IN

1.2、存储过程的传出参数out

1.3、存储过程的可变参数in out

四、存储过程条件语句

1、基本语法结构

2、存储过程条件语句—案例

2.1、简单条件语句—应用案例

2.2、多条件语句—应用案例

五、存储过程循环语句

1、while语句的基本结构及案例

2、LOOP语句的基本结构及案例

3、for语句的基本结构及案例

六、存储过程游标的使用

1、什么是游标

2、语法

3、案例

七、8道存储过程——案例实战

八、语法及案例使用数据

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

大数据 爬虫Python AI Sql

数据蛙数据分析每周作业

大数据爬虫Python AI Sql