存储过程详解
一,概念:存储过程是一组预编译的sql语句,常和游标、索引和视图等一起使用,功能强大但实际工作中使用不多。
二,优缺点:
1.存储过程预编译,执行速度较快。将代码逻辑存放在数据库中,便于代码移植,减少网络流量
2.需要专门的数据库人员维护,现实中一般由开发人员维护且不能适应频繁的需求变动,所以存储过程一般在项目后期,用在数据统计一类大数据流量算法逻辑要求比较高的环境
三,基础知识:
基本结构:Begin End
属性:a dept.dname%type
赋值:(:=)
获取键盘:(&)
查询:Select xxx into 变量列表 from 表名;
判断*2:If 条件 then 语句;elsif 条件 then 语句 Else Case 变量 When 值 then
循环*3:Loop 语句 Exit when 条件 Endloop While 条件 loop语句 End loopFor 临时变量 in 起始值 结束值 loop语句;End loop;
调用:Execute immediate sql 语句字符串
Into 变量列表;
Using 参数列表;
异常:Exctption When 异常类型 then 处理语句 When others then
四,语法:
Create [or replace] procedure
过程名(参数列表)
Is|as
声明部分
Begin
执行语句
Exception
异常处理
End
存储过程参数
In:用于接收调用程序的值,默认的参数模式
Out:用于向调用程序返回值。
In out:既又
四,几个实例:
4.1基础查询(带条件):
注释:sqlserver中 参数用@表示如 @username char(20)
createor replace procedure p1 (
id in number(20),
str in varchar(20)
)
no Integer :=0;
Begin
exception
when no_data_found then -- catches all ’no data found’ errors
End
4.2 判断
4.2.1 if else判断
create or replace procedure p2( a in number(20))
is
begin
if a <5 then
dbms_output.put_line('小于5');--控制台输出
elseif a < 10 then ...
else ...
end if;
end;
4.2.2 case判断
create or replace procedure p3( a in number(20))
is
begin
case a
when 1 then ...
when 2 then ...
when 3 then ...
else ...
end case;
end
4.3循环
4.3.1do while
create or replace procedure p4
is
a integer;
begin
a:=0;
loop
...
a:=a+1;
exit when a>10;
end loop;
end;
4.3.2 while
create or replace procedure p5
is
a integer;
begin
a:=0;
while a<300 loop
...
a:=a+1;
end loop;
end;
4.3.3 for in (遍历游标,资源来自博客园shawn)
create or replace procedure p6
is
cursor csr_dept is select name from dept;
row_dept csr_dept%rowtype
begin
for row_dept in csr_dept loop
dbms_output.put_line(row_dept.name);
end loop;
end;