Oracle存储过程基础语法总结

Oracle存储过程基础语法总结

1、基础概念

（1）什么是存储过程？

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

笔者认为，存储过程类似于java或其他语言中的函数，都是将一些常用的复杂的业务逻辑过程或语句封装起来，通过简单的调用或传参调用，得到想要的结果。而数据库中的存储过程，即SQL语句和控制语句、系统函数等的结合，有很强大的功能。

（2）存储过程的使用方式？

存储过程编写完成后，可以通过SQLPLUS 、Oracle开发工具或第三方开发工具来调用运行。Oracle 使用CALL 语句来实现对存储过程的调用。

（3）Oracle存储过程的种类？

Oracle的存储过程支持传入参数和返回参数，按此种分类方式可以分为4类：
有传入参数，有返回参数
有传入参数，无返回参数
无传入参数，有返回参数
无传入参数，无返回参数

按照创建用户和权限，可以分为以下几类：
系统存储过程
系统预设的存储过程，用于实现以下常用的特定的功能。
本地存储过程
一般说的存储过程，指的就是本地存储过程。一般由用户编写，按照业务逻辑编写、封装，提供使用。
临时存储过程
一是本地临时存储过程，以“#”作为其名称的开头部分，存放在tempdb数据库，只有创建的用户可以调用它。
二是全局临时存储过程，以“##”作为其名称的开头部分，存放在tempdb数据库，所有连接上服务器的用户，都可以调用它。

（4）存储过程的优缺点？

优点：
–> 存储过程只有在创建时需要编译，使用时，不需要再进行编译，相比于SQL来说，执行速度更快，资源消耗更少。
–> 不需要重复编写复杂的代码，减少工作量。
–> 存储过程位于服务器上，每次调用时，只需要传存储过程名和参数，减少网络带宽资源消耗。
–> 存储过程中可以使用控制语句，可以完成SQL难以完成甚至无法完成的工作。
缺点：
–> 存储过程编写和调试时较为麻烦。
–> 当数据库进行迁移时，存储过程的迁移和维护工作量较大。

2、存储过程的编写

（1）基本语法

	CREATE OR REPLACE PROCEDURE P_NAME[( 变量名 IN 变量类型，变量名 OUT 变量类型)] AS/IS
	#变量声明
	BEGIN
	#业务逻辑、SQL语句
	END [P_NAME];

实例

create or replace procedure get_student_name(student_id   in student.stu_id%TYPE,
                                             student_name out student.stu_name%TYPE) is
begin
  select stu_name into student_name from student where stu_id = student_id;
  DBMS_OUTPUT.put_line('compete!');
end get_student_name;

存储过程的执行

declare
student_id student.stu_id%TYPE := 2020050801;
student_name student.stu_name%TYPE;

begin

GET_STUDENT_NAME(student_id,student_name);

DBMS_OUTPUT.put_line('学生名称:'||student_name);

end;

输出结果

compete!
学生名称:zhangsan

终端中可以使用“exec 存储过程名”执行。

（2） 变量

变量类型

固定类型
如NUMBER、VARCHAR2、DATE等
引用类型
%TYPE #普通变量
声明方式：username tbname.username%TYPE
因为数据库的数据类型往往可能和预设类型不一致（例如：名称声明为varchar2(20),但是实际名称可能会超过20），所以一般建议使用引用型变量，及时数据库对字段进行了修改，也不会影响存储过程的使用，使程序更具健壮性。
%ROWTYPE #记录型变量
声明方式
rowtb = tbname%ROWTYPE
如果需要使用的字段较多，每一个字段都去声明的话，会非常麻烦，一般可以使用记录型变量，使用时类似于对象使用的方式：rowtb.colname
如果使用的字段较少的情况下，一般不建议使用记录型变量，变量存储的内容更多，会造成系统资源的浪费。
一个记录型变量一次只能存储一行数据，且必须是一条数据，超出或字段缺少都会报错。

实例

declare
student_msg student%ROWTYPE;

begin

select * into student_msg from student where stu_id='2020050801';
DBMS_OUTPUT.put_line('学生名称是：'||student_msg.stu_name||'分数是:'||student_msg.score);

end;

输出结果

学生名称是：zhangsan分数是:100

声明方式
变量名 变量类型(变量长度)
例如：username varchar2(30) ;

赋值方式
第一种：
直接赋值： :=
如：username varchar2(30) := ‘张三’;
第二种：
语句赋值： select 值 into 变量
如：select ‘张三’ into username from dual;

（3）控制语句

打印输出信息

DBMS_OUTPUT.PUT_LINE('HELLO WORLD');

选择控制语句

	IF 条件 
		THEN 执行语句
	ELSIF 条件 
		THEN 执行语句
	ELSE
	 	执行语句
	END IF

注意：ELSIF的写法
实例

declare
  row_count number(10);

begin

  select count(1) into row_count from student;

  dbms_output.put_line('数据笔数为:' || row_count);

  if row_count >= 1 and row_count <= 3 
    then
      dbms_output.put_line('数据笔数在1~3之间为:' || row_count);
  elsif row_count >= 4 and row_count <= 6 
    then
      dbms_output.put_line('数据笔数在4~6之间为:' || row_count);
  else
      dbms_output.put_line('数据笔数大于6笔，为:' || row_count);
  end if;
end;

循环控制语句
LOOP循环

LOOP
WHEN 条件 EXIT
执行语句
END LOOP

实例
打印 1-5

declare
  num number := 0;

begin
  loop
    num := num + 1;
    
    DBMS_OUTPUT.put_line(num);
    
    exit when num>=5;        
  end loop;

end;

输出结果

1
2
3
4
5

WHILE循环

WHILE 条件 LOOP
执行语句
END LOOP;

实例

declare
  num number := 0;

begin
 while num<5 loop
   num := num + 1;
   dbms_output.put_line(num);
   end loop;

end;

FOR 值 IN 列表 LOOP
执行语句
END LOOP;

实例

begin
  for stu in (select * from student)
    loop
      dbms_output.put_line(stu.stu_id);      
    end loop;

end;

输出结果

2020050801
2020050802
2020050803
2020050804

（4）游标

什么是游标？
游标是用于存储一个查询返回的多行数据，通过遍历游标，可以逐行访问处理该结果集的数据。

语法

游标声明
CURSOR 游标名[(参数列表)] IS 查询语句；

游标的打开
OPEN 游标名；

游标的取值
FETCH 游标名 INTO 变量列表;

游标的关闭
CLOSE 游标名;

属性

游标的属性	返回值类型	说明
%ROWCOUNT	整型	获得FETCH语句返回的数据行数
%FOUND	布尔型	最近的FETCH语句返回一行数据为真，否则为假
%NOTFOUND	布尔型	与%FOUND属性返回值相反
%ISOPEN	布尔型	游标已经打开值为真，否则为假

其中，%NOTFOUND在游标中找不到元素的时候返回TRUE，通常用来判断退出循环。默认值为FALSE

实例

declare

  cursor curs is
    select stu_id, stu_name, score from student;
  student_id    student.stu_id%TYPE;
  student_name  student.stu_name%TYPE;
  student_score student.score%TYPE;

begin
  open curs;

  loop
    exit when curs%NOTFOUND;
    fetch curs
      into student_id, student_name, student_score;
  
    dbms_output.put_line('学号:' || student_id || ',学生姓名:' || student_name ||
                         ',学生分数:' || student_score);
  end loop;

  close curs;
end;

输出结果

学号:2020050801,学生姓名:zhangsan,学生分数:100
学号:2020050802,学生姓名:lisi,学生分数:90
学号:2020050803,学生姓名:wangwu,学生分数:98.5
学号:2020050804,学生姓名:zhaoliu,学生分数:92.6
学号:2020050804,学生姓名:zhaoliu,学生分数:92.6

好了，Oracle的存储过程基本用法已经介绍完了，实用性还是非常强的，后续工作和实际使用过程中会复杂的多，但是基础掌握很重要。但是实际工作中，操作数据库还是尽量减少存储过程的使用，毕竟SQL才是查询数据库的主要方式。