本文共 4556 字，大约阅读时间需要 15 分钟。
百度百科的解释：存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。
笔者认为，存储过程类似于java或其他语言中的函数，都是将一些常用的复杂的业务逻辑过程或语句封装起来，通过简单的调用或传参调用，得到想要的结果。而数据库中的存储过程，即SQL语句和控制语句、系统函数等的结合，有很强大的功能。
存储过程编写完成后，可以通过SQLPLUS 、Oracle开发工具或第三方开发工具来调用运行。Oracle 使用CALL 语句来实现对存储过程的调用。
Oracle的存储过程支持传入参数和返回参数，按此种分类方式可以分为4类：
有传入参数，有返回参数 有传入参数，无返回参数 无传入参数，有返回参数 无传入参数，无返回参数按照创建用户和权限，可以分为以下几类：
系统存储过程 系统预设的存储过程，用于实现以下常用的特定的功能。 本地存储过程 一般说的存储过程，指的就是本地存储过程。一般由用户编写，按照业务逻辑编写、封装，提供使用。 临时存储过程 一是本地临时存储过程，以“#”作为其名称的开头部分，存放在tempdb数据库，只有创建的用户可以调用它。 二是全局临时存储过程，以“##”作为其名称的开头部分，存放在tempdb数据库，所有连接上服务器的用户，都可以调用它。优点：
–> 存储过程只有在创建时需要编译，使用时，不需要再进行编译，相比于SQL来说，执行速度更快，资源消耗更少。 –> 不需要重复编写复杂的代码，减少工作量。 –> 存储过程位于服务器上，每次调用时，只需要传存储过程名和参数，减少网络带宽资源消耗。 –> 存储过程中可以使用控制语句，可以完成SQL难以完成甚至无法完成的工作。 缺点： –> 存储过程编写和调试时较为麻烦。 –> 当数据库进行迁移时，存储过程的迁移和维护工作量较大。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) isbegin select stu_name into student_name from student where stu_id = student_id; DBMS_OUTPUT.put_line('compete!');end get_student_name; 存储过程的执行
declarestudent_id student.stu_id%TYPE := 2020050801;student_name student.stu_name%TYPE;beginGET_STUDENT_NAME(student_id,student_name);DBMS_OUTPUT.put_line('学生名称:'||student_name);end; 输出结果
compete!学生名称:zhangsan
终端中可以使用“exec 存储过程名”执行。
变量类型
固定类型
如NUMBER、VARCHAR2、DATE等 引用类型 %TYPE #普通变量 声明方式：username tbname.username%TYPE 因为数据库的数据类型往往可能和预设类型不一致（例如：名称声明为varchar2(20),但是实际名称可能会超过20），所以一般建议使用引用型变量，及时数据库对字段进行了修改，也不会影响存储过程的使用，使程序更具健壮性。 %ROWTYPE #记录型变量 声明方式 rowtb = tbname%ROWTYPE 如果需要使用的字段较多，每一个字段都去声明的话，会非常麻烦，一般可以使用记录型变量，使用时类似于对象使用的方式：rowtb.colname 如果使用的字段较少的情况下，一般不建议使用记录型变量，变量存储的内容更多，会造成系统资源的浪费。 一个记录型变量一次只能存储一行数据，且必须是一条数据，超出或字段缺少都会报错。实例
declarestudent_msg student%ROWTYPE;beginselect * 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;打印输出信息
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循环LOOPWHEN 条件 EXIT执行语句END LOOP
实例
打印 1-5declare num number := 0;begin loop num := num + 1; DBMS_OUTPUT.put_line(num); exit when num>=5; end loop;end;
输出结果
12345
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;
输出结果
2020050801202005080220200508032020050804
什么是游标？
游标是用于存储一个查询返回的多行数据，通过遍历游标，可以逐行访问处理该结果集的数据。语法
游标声明
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才是查询数据库的主要方式。
转载地址：http://drezi.baihongyu.com/