目录
本文是基于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、直接赋值,需要使用<font color='red'>:=</font>进行赋值
使用的条件:只能在变量的声明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)<
ADD_MONTHS((SELECT MAX(DISTINCT TRUNC(CREATE_TIME)) AS CREATE_TIME FROM '||table_name||'),'||del_times||')';
--立刻执行sql_count,并赋值给del_counts
execute immediate sql_count
into del_counts;
--如果删除的总数大于0就继续删除
if del_counts>0 then
dbms_output.put_line('开始删除');
loop
--删除的sql
sql_del :='DELETE FROM '||table_name||' WHERE TRUNC(CREATE_TIME)<
ADD_MONTHS((SELECT MAX(DISTINCT TRUNC(CREATE_TIME))AS CREATE_TIME FROM '||table_name||'),'||del_times||')and rownum<=50000';
--立刻执行sql_del
execute immediate sql_del;
--统计被删除的数量
flag_count:=flag_count+sql%rowcount;
dbms_output.put_line('已删除'||flag_count||'条');
commit;
--立刻执行sql_count,并赋值给del_counts
execute immediate sql_count
into del_counts;
dbms_output.put_line('剩余'||del_counts||'条');
--为0时退出
exit when del_counts<=0;
end loop;
dbms_output.put_line('删除成功');
end if;
dbms_output.put_line('成功删除'||flag_count||'条');
str_o_flag :='成功删除'||flag_count||'条';
elsif flag_num=1 then
dbms_output.put_line('进入按次数删除的部分');
sql_count := 'SELECT COUNT(*) FROM '||table_name||' WHERE TRUNC(CREATE_TIME) <
(SELECT TRUNC(CREATE_TIME) FROM (SELECT CREATE_TIME,row_number()over(order by CREATE_TIME desc) AS RN FROM(SELECT DISTINCT CREATE_TIME FROM '||table_name||'))WHERE RN='||del_times||')';
--立刻执行sql_count,并赋值给del_counts
execute immediate sql_count
into del_counts;
--如果删除的总数大于0就继续删除
if del_counts>0 then
dbms_output.put_line('开始删除');
loop
--删除的sql
sql_del :='DELETE FROM '||table_name||' WHERE TRUNC(CREATE_TIME) <
(SELECT TRUNC(CREATE_TIME) FROM (SELECT CREATE_TIME,row_number()over(order by CREATE_TIME desc) AS RN FROM(SELECT DISTINCT CREATE_TIME FROM '||table_name||'))WHERE RN='||del_times||')and rownum<=50000';
--立刻执行sql_del
execute immediate sql_del;
--统计被删除的数量
flag_count:=flag_count+sql%rowcount;
dbms_output.put_line('已删除'||flag_count||'条');
commit;
--立刻执行sql_count,并赋值给del_counts
execute immediate sql_count
into del_counts;
dbms_output.put_line('剩余'||del_counts||'条');
--为0时退出
exit when del_counts<=0;
end loop;
dbms_output.put_line('删除成功');
end if;
dbms_output.put_line('成功删除'||flag_count||'条');
str_o_flag :='成功删除'||flag_count||'条';
end if;
--异常处理
exception
when others then
rollback;
str_o_flag := '失败';
str_o_sqlcode := sqlcode;
str_o_sqlerrm := substr(sqlerrm, 1, 512);
commit;
end PRO_LAN_DELETE;