目录
一、存储过程的创建
1、连接plsql
2、找到左边对象下的procedures，点击右键
3、点击新建进入到创建存过页面
4、填写存储过程名称以及输入参数和输出参数，点击确定后生成存储过程的基本框架内容
二、存储过程语法说明
1.存储过程的基本结构
a、带参存储过程语法b、无参存储过程语法三.变量的声明和赋值
变量的声明：
变量的赋值：
四、存储过程语句调试
五、流程控制语句
1、条件控制语句 IF THEN 2、条件控制语句 CASE
五、循环控制语句
1、LOOP 循环
2、While循环
3、FOR循环
六、游标
隐式游标显示游标游标属性
七、学习用例
总结本文是基于oracle编写的存储过程，以下所说的存储过程均为oracle中的存储过程，以及下面所说的存过都是存储过程的简称
一、存储过程的创建
创建存储过程的方式两种，一种是基于PLSQL创建，一种是ORACLE语法创建
PLSQL创建步骤：
1、连接plsql
2、找到左边对象下的procedures，点击右键
3、点击新建进入到创建存过页面
4、填写存储过程名称以及输入参数和输出参数，点击确定后生成存储过程的基本框架内容
ORACLE语法创建：（注意：如果没有参数请把括号去掉，否则会报错）
直接在文本中编写存过的基本框架内容create or replace procedure pro_test(Name in out type, Name in out type, …) is begin
end pro_test;打开plsql或者oracle命令行模式直接复制执行
二、存储过程语法说明
1.存储过程的基本结构
a、带参存储过程语法 CREATE OR REPLACE PROCEDURE 存储过程名字 (--输入参数 IN NUMBER,--输出参数 OUT NUMBER --可以有多个输入参数和输出参数) IS--这个区间可以用来定义变量和创建游标BEGIN--执行游标或者执行语句commit; --对上面的内容进行提交exception//存储过程异常when others then--异常处理方法，可以是打印错误，然后进行回滚等操作，下面操作一样，看自己情况决定rollback;dbms_output.put_line(sqlcode);dbms_output.put_line(substr(sqlerrm, 1, 512));END 存储过程名字;注意：其中参数IN表示输入参数，是参数的默认模式。
OUT表示返回值参数，类型可以使用任意Oracle中的合法类型。
OUT模式定义的参数只能在过程体内部赋值，表示该参数可以将某个值传递回调用他的过程IN OUT表示该参数可以向该过程中传递值，也可以将某个值传出去。
b、无参存储过程语法 CREATE OR REPLACE PROCEDURE 存储过程名字IS--这个区间可以用来定义变量和创建游标BEGIN--执行游标或者执行语句commit; --对上面的内容进行提交exception//存储过程异常when others then--异常处理方法，可以是打印错误，然后进行回滚等操作，下面操作一样，看自己情况决定rollback;dbms_output.put_line(sqlcode);dbms_output.put_line(substr(sqlerrm, 1, 512));END 存储过程名字;三.变量的声明和赋值
变量的声明：
a、按照数据类型进行声明
格式：变量名 变量的数据类型;
样例：v_name varchar2(500);
b、根据表字段进行声明
格式：变量名 表名.字段名%TYPE
样例：v_name userinfo.name%TYPE;
注意：变量的数据类型和表名的字段数据类型一致
c、根据表的行记录进行声明
格式：变量名 表名/游标名%ROWTYPE
样例：v_user_row userinfo%ROWTYPE;
注意：变量的数据类型和表的行数据类型一致
变量的赋值：
a、直接赋值，需要使用:=进行赋值使用的条件：只能在变量的声明a和b才能使用直接赋值先声明在赋值（声明在上面a和b）
样例：v_name:= '小花';声明和赋值同时进行v_name varchar(500) := '小花'b、select 字段 into 变量 from 表名 进行赋值
单个字段复制：
select name into v_name from userinfo;使用条件：也是只限于a和b使用
行记录复制：
select * into v_user_row from userinfo;使用条件：也是只限于c使用c、execute immediate sql语句 into 变量 进行赋值v_name varchar2(500);v_name_sql varchar2(500) :='select name from userinfo where id=1';execute immediate v_name_sql into v_name ;语句含义是like执行v_name_sql 语句并将结果赋值给v_name。
四、存储过程语句调试
如果只是调试存储过程语句可直接用下面语法，在plsql的sql框中直接执行，比如下面介绍的控制语句都可以直接使用，输出结果去输出框查看。
基本能语法：
DECLARE
变量定义
BEGIN
......
END;
五、流程控制语句
1、条件控制语句 IF THEN 基本语法：IF 条件表达式1 THEN语句段1 ELSIF 条件表达式2 THEN语句段2......
ELSIF 条件表达式n语句段n END IF;样例：如果flag_num=0则输出'早上好'，如果flag_num=1 则输出 '中午好'，否则输出'晚上好'，declare flag_num int:= 0;begin if flag_num=0 then dbms_output.put_line('早上好');elsif flag_num=1 then dbms_output.put_line('中午好');else dbms_output.put_line('晚上好');end if;end;2、条件控制语句 CASE 基本语法：CASE WHEN 条件表达式1 THEN语句段1;WHEN 条件表达式2 THEN语句段2;......
ELSE语句段n;END CASE;样例：如果flag_num=0则输出'早上好'，如果flag_num=1 则输出 '中午好'，否则输出'晚上好'declare flag_num int:= 0;begin case when flag_num=0 then dbms_output.put_line('早上好');when flag_num=1 then dbms_output.put_line('中午好');else dbms_output.put_line('晚上好');end case;end;
五、循环控制语句
1、LOOP 循环 基本语法：LOOP EXIT [WHEN 条件表达式] --循环终止条件,为ture时，退出循环，否则再次执行循环体语句段;END LOOP;
样例：
declare i int:= 0;begin loop EXIT WHEN i > 3;i:=i+1;dbms_output.put_line(i);end loop;end;语句解释：初始变量i为0，当i进入loop循环时，i>3则退出循环，i等于i+1,进行打印，然后依次循环，直至i>3时退出循环。
2、While循环
基本语法：WHILE 条件表达式 LOOP语句段;END LOOP;
样例：
declare i int := 0;begin while i < 3 loop i := i + 1;dbms_output.put_line(i);end loop;end;语句解释：初始变量i为0，当i进入loop循环时，i<3进入循环，i等于i+1,进行打印，然后依次循环，直至i不小于3时退出循环。
3、FOR循环
基本语法：（注意：如加上reverse表示倒叙循环执行语句）
FOR 循环变量 in [REVERSE] 初值表达式..终值表达式 LOOP语句段;END LOOP;
样例：
declare i int := 0;begin for i IN reverse 1..5 loop dbms_output.put_line(i);end loop;end;语句解释：初始变量i为0，循环遍历1..5，for循环默认是递增，但是加上reverse 会变成递减，将5赋给i,打印i,直至将1..5全部循环遍历，结束循环，for循环只适用于已知循环次数的遍历
六、游标
游标（Cursor）的定义：游标是SQL的一个内存工作区，由系统或用户以变量的形式定义。游标的作用就是用于临
时存储从数据库中提取的数据块。在某些情况下，需要把数据从存放在磁盘的表中调到计
算机内存中进行处理，最后将处理结果显示出来或最终写回数据库。这样数据处理的速度
才会提高，否则频繁的磁盘数据交换会降低效率。
上面是游标的官方定义，我们可以把游标理解为一个用于封装多条数据的集合。因此对于集合的遍历操作，那么游标也可以使用，下面我们会讲到。
那么游标存在隐式游标和显式游标隐式游标DML操作和单行SELECT语句会使用隐式游标，它们分别是：* 插入操作：INSERT。
* 更新操作：UPDATE。
* 删除操作：DELETE。
* 单行查询操作：SELECT ... INTO ...。
隐式游标游标属性使用样例：
1.创建一个包含name,age,addr三个字段的表
create table userinfo(name varchar2(500);age number(20);addr varchar2(500);)
2.编写插入的代码并用上面的游标属性进行监控
declare begin insert into userinfo (name,age,addr) values('zs',18,'sc');dbms_output.put_line('游标所影响的行数：'||SQL%rowcount);if SQL%NotFound then --没有找到上面执行的sql dbms_output.put_line('SQL%NotFound：true');else dbms_output.put_line('SQL%NotFound：false');end if;if SQL%Found then --找到上面执行的sql dbms_output.put_line('SQL%Found：true');else dbms_output.put_line('SQL%Found：false');end if;if SQL%isopen then --游标是否打开dbms_output.put_line('SQL%isopen：true');else dbms_output.put_line('SQL%isopen：false');end if;end;
3.执行的结果：
游标所影响的行数：1 SQL%NotFound：false SQL%Found：true SQL%isopen：false
4.编写一个查询的代码对游标属性进行监控
declare v_info userinfo %ROWTYPE;begin select * into v_info from TEST_TABLE where name ='zs';dbms_output.put_line('name：'||v_info.name);dbms_output.put_line('age：'||v_info.age);dbms_output.put_line('addr：'||v_info.addr);dbms_output.put_line('游标所影响的行数：'||SQL%rowcount);if SQL%NotFound then dbms_output.put_line('SQL%NotFound：true');else dbms_output.put_line('SQL%NotFound：false');
end if;if SQL%Found then dbms_output.put_line('SQL%Found：true');else dbms_output.put_line('SQL%Found：false');end if;if SQL%isopen then dbms_output.put_line('SQL%isopen：true');else dbms_output.put_line('SQL%isopen：false');end if;end;
6.查询的结果为：
name：zs age：18 addr：sc游标所影响的行数：1 SQL%NotFound：false SQL%Found：true SQL%isopen：false显示游标
1.游标的声明
a.带参数的游标声明CURSOR 游标名 (参数1 数据类型,......) IS SELECT语句;b.不带参数的游标声明CURSOR 游标名IS SELECT语句;注意：SELECT语句是对表或视图的查询语句，甚至也可以是联合查询。可以带WHERE条件、ORDER BY或GROUP BY等子句，但不能使用INTO子句。
2．打开游标
在可执行部分，按以下格式打开游标：OPEN 游标名(参数1,.....) --存在参数或者OPEN 游标名 --不存在参数打开游标时，SELECT语句的查询结果就被传送到了游标工作区。
3．提取数据
FETCH 游标名 INTO 变量;游标打开后有一个指针指向数据区，FETCH语句一次返回指针所指的一行数据，要返回多行需重复执行，可以使用循环语句来实现。控制循环可以通过判断游标的属性来进行。
变量的定义有两种情况1.单个变量：记录单个字段数据，变量名是用来从游标中接收数据的变量，需要事先定义。变量的个数和类型应与SELECT语句中
的字段变量的个数和类型一致。
2.行变量：记录一条数据，一次将一行数据取到记录变量中，需要使用%ROWTYPE事先定义记录变量，这种形式使用起来比较方便，不必分别定义和使用多个变量。
定义方式采用上面变量声明c:
变量名 表名|游标名%ROWTYPE；
4．关闭游标
CLOSE 游标名;显式游标打开后，必须显式地关闭。游标一旦关闭，游标占用的资源就被释放，游标变成无效，必须重新打开才能使用。
游标属性通过游标的属性，来了解游标的执行状态和结果，进而控制游标的执行操作。
隐式游标可以使用名字SQL来访问，但要注意，通过SQL游标名总是只能访问前一个DML操作或单行SELECT操作的游标属性。
通常在刚刚执行完操作之后，立即使用SQL游标名来访问属性。
游标的属性有四种: sql%found （布尔类型，默认值为null）
sql%notfound（布尔类型,默认值为null）
sql%rowcount(数值类型默认值为0)
sql%isopen(布尔类型)因为游标在运行DML语句时打开，完成后关闭，使用SQL%ISOPEN总是为false。所以隐式游标只使用SQL%FOUND,SQL%NOTFOUND,SQL%ROWCOUNT三个属性。
1.SQL%FOUND和SQL%NOTFOUND
在执行任何DML语句前SQL%FOUND和SQL%NOTFOUND的值都是NULL,在执行DML语句后，SQL%FOUND的属性值将是：.TRUE :INSERT --判断是否插入成功.TRUE :DELETE和UPDATE，至少有一行被DELETE或UPDATE.--判断修改和删除成功.TRUE :SELECT INTO至少返回一行 --判断查询成功
当SQL%FOUND为TRUE时,SQL%NOTFOUND为FALSE。
2.SQL%ROWCOUNT
在执行任何DML语句之前，SQL%ROWCOUNT的值都是NULL, 对于SELECT INTO语句，
如果执行成功，SQL%ROWCOUNT的值为1,如果没有成功或者没有操作(如update、insert、
delete为0条），SQL%ROWCOUNT的值为0， 而对于update和delete来说表示游标所检索
数据库行的个数即更新或者删除的行数。
3.SQL%ISOPEN
SQL%ISOPEN是一个布尔值，如果游标打开，则为TRUE, 如果游标关闭，则为FALSE.对于隐式
游标而言SQL%ISOPEN总是FALSE，这是因为隐式游标在DML语句执行时打开，结束时就立即
关闭。
七、学习用例
下面提供几个可以参考学习的用例：for循环结合游标（cursor）样例1：declare v_name varchar2(500) :='zs';cursor cur_user(v_name varchar2) is select * from userinfo where name=v_name;begin for user1 in cur_user(v_name) loop dbms_output.put_line('name：'||user1.name);dbms_output.put_line('age：'||user1.age);dbms_output.put_line('addr：'||user1.addr);end loop;end;
样例1输出结果：
name：zs
age：15
addr：sc
name：zs
age：18
addr：sc for循环结合游标（cursor）样例2 ：declare cursor cur_user is select * from userinfo where name='zs';begin for user1 in cur_user loop dbms_output.put_line('name：'||user1.name);dbms_output.put_line('age：'||user1.age);dbms_output.put_line('addr：'||user1.addr);end loop;end;
输出结果：
name：zs
age：15
addr：sc
name：zs
age：18
addr：sc loop循环结合游标（cursor）样例3：（这一个比较经典，可以将上面游标所讲内容都包含在内）
declare
v_name varchar2(500) :='zs'; --定义的常用变量
cursor cur_user(v_name varchar2) --创建游标is select * from userinfo where name=v_name; --查询语句
v_user cur_user%rowtype; --定义的行变量，用于接收游标中的数据
begin open cur_user(v_name); --打开游标loop --循环开始fetch cur_user into v_user; --从游标中提取数据exit when cur_user%notfound;--判断是游标中是否存在数据，不存在退出循环dbms_output.put_line('name：'||v_user.name);dbms_output.put_line('age：'||v_user.age);dbms_output.put_line('addr：'||v_user.addr);dbms_output.put_line('------------');end loop;close cur_user; --关闭游标end;while循环结合游标（cursor）样例4：declare
v_name varchar2(500) :='zs'; --定义的常用变量
cursor cur_user(v_name varchar2) --创建游标is select * from userinfo where name=v_name; --查询语句
v_user cur_user%rowtype; --定义的行变量，用于接收游标中的数据
begin open cur_user(v_name); --打开游标fetch cur_user into v_user; --从游标中提取数据while(cur_user%found)loop --判断是游标中是否存在数据，存在进入循环dbms_output.put_line('name：'||v_user.name);dbms_output.put_line('age：'||v_user.age);dbms_output.put_line('addr：'||v_user.addr);dbms_output.put_line('------------');fetch cur_user into v_user; --从游标中提取数据end loop;close cur_user; --关闭游标end;样例5：（可根据传入表名参数，分批删除指定表的指定时间段的数据）
create or replace procedure PRO_LAN_DELETE(--标记是那种类型的删除,0是按月份删除，1是按次数删除flag_num in number,--删除的表名table_name in varchar2,--数据保留的次数或者保留数据的几个月，月用负数，表示减几个月del_times in number,str_o_flag out varchar2,str_o_sqlcode out varchar2,str_o_sqlerrm out varchar2) as--删除数据的总数del_counts number;--记录被删除的数量flag_count number:=0;--获取删除数据总数的sql sql_count varchar2(1000);--删除数据的sql sql_del varchar2(1000);begin if flag_num=0 then dbms_output.put_line('进入按月份删除的部分');sql_count:= 'SELECT COUNT(*) FROM '||table_name||' WHERE TRUNC(CREATE_TIME)总结到此这篇关于Oracle存储过程新手入门教程的文章就介绍到这了,更多相关Oracle存储过程入门教程内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家！
您可能感兴趣的文章:Oracle存储过程入门学习基本语法oracle 存储过程和函数例子oracle 在一个存储过程中调用另一个返回游标的存储过程Oracle中返回结果集的存储过程分享Oracle存储过程基本语法介绍Oracle存储过程返回游标实例详解oracle数据库中查看系统存储过程的方法oracle 存储过程详细介绍(创建,删除存储过程,参数传递等) Oracle存储过程及调用Oracle存储过程和存储函数创建方法(详解)