【初学者，未完待续，持续更新中】
一、什么是存储过程
所谓存储过程(Stored Procedure)，就是一组用于完成特定数据库功能的SQL语句集，该SQL语句集经过编译后存储在数据库系统中。在使用时候，用户通过指定已经定义的存储过程名字并给出相应的存储过程参数来调用并执行它，从而完成一个或一系列的数据库操作。
存储过程和函数也是一种PL/SQL块，是存入数据库的PL/SQL块。但存储过程和函数不同于已经介绍过的PL/SQL程序，我们通常把PL/SQL程序称为无名块，而存储过程和函数是以命名的方式存储于数据库中的。和PL/SQL程序相比，存储过程有很多优点，具体归纳:
- 存储过程和函数以命名的数据库对象形式存储于数据库当中。存储在数据库中的优点是很明显的，因为代码不保存在本地，用户可以在任何客户机上登录到数据库，并调用或修改代码。
- 存储过程和函数可由数据库提供安全保证，要想使用存储过程和函数，需要有存储过程和函数的所有者的授权，只有被授权的用户或创建者本身才能执行存储过程或调用函数。
- 存储过程和函数的信息是写入数据字典的，所以存储过程可以看作是一个公用模块，用户编写的PL/SQL程序或其他存储过程都可以调用它(但存储过程和函数不能调用PL/SQL程序)。一个重复使用的功能，可以设计成为存储过程。
- 像其他高级语言的过程和函数一样，可以传递参数给存储过程或函数，参数的传递也有多种方式。存储过程可以有返回值，也可以没有返回值，存储过程的返回值必须通过参数带回;函数有一定的数据类型，像其他的标准函数一样，我们可以通过对函数名的调用返回函数值。
- 存储过程和函数需要进行编译，以排除语法错误，只有编译通过才能调用。
二、存储过程结构
1、第一个存储过程
create or replace procedure proc1( -- -----创建语句，proc1是该存储过程的名称
para1 varchar2,
para2 out varchar2,
para3 in out varchar2
) as -- -----------------------------------或者is关键字，表明后面将跟随一个PL/SQL体
v_name varchar2(20);
begin -- ----------------------------------开始
v_name :='zhang3';
para3 := v_name;
dbms_output.put_line('para3:'||para3);
exception -- -----------------------------处理异常
where 1 < 0 then
para1 := 'li4';
end; -- -----------------------------------结束- 创建语句：create or replace procedure 存储过程名
- 如果没有or replace，则仅仅是新建一个存储过程。如果系统中存在该存储过程，则会报错。
- create or replace procedure 如果系统中没有此存储过程，则新建一个；如果有，则删除原来的，并重新创建。
- 存储过程名定义：包括存储过程名 参数列表（参数名 参数传递方式 参数类型）
- 参数名不能重复
- 参数传递方式：IN, OUT, IN OUT（下面有具体示例）
- IN：输入参数，按值传递方式；
- OUT：输出参数，可以理解为按引用传递方式。可作为存储过程的输出结果，供外部调用者使用；
- IN OUT：既可以作输入参数，也可以作输出参数。
- 参数的数据类型只需要指明类型名即可，不需要指定宽度。
- 参数的宽度由外部调用者决定。
- 存储过程可以有参数，也可以没有参数。
- 变量声明块：紧跟着的as（is）关键字，可以理解为pl/sql的declare关键字，用于声明变量。
- 变量声明块用于声明该存储过程需要用到的变量，其作用域为该存储过程。
- 此处声明的变量必须指定宽度。
- 遵循PL/SQL的变量声明规范
- 过程语句块：从BEGIN关键字开始。存储过程的具体逻辑在这里实现。
- 异常处理块：关键字为EXCEPTION，用于处理语句产生的异常。可选。
- 结束块：由END关键字结束。
2、存储过程的参数传递方式及默认值
存储过程参数传递方式有三种：IN, OUT, IN OUT
- IN
- 语法：参数名 IN 数据类型 DEFAULT 值;
- 定义一个输入参数变量，用于传递参数给存储过程。在调用存储过程时，主程序的实际参数可以是常量、有值变量或表达式等。
- DEFAULT 关键字为可选项，用来设定参数的默认值。如果在调用存储过程时不指明参数，则参数变量取默认值。
- 在存储过程中，输入变量接收主程序传递的值，但不能对其进行赋值。
- 如果存储过程的参数没有指定参数传递类型，默认为IN。
- OUT
- 语法：参数名 OUT 数据类型;
- 定义一个输出参数变量，用于从存储过程获取数据，即变量从存储过程中返回值给主程序。
- 在调用存储过程时，主程序的实际参数只能是一个变量，而不能是常量或表达式。
- 在存储过程中，参数变量只能被赋值而不能将其用于赋值，在存储过程中必须给输出变量至少赋值一次。
- IN OUT
- 语法：参数名 IN OUT 数据类型 DEFAULT 值;
- 定义一个输入、输出参数变量，兼有以上两者的功能。
- 在调用存储过程时，主程序的实际参数只能是一个变量，而不能是常量或表达式。
- DEFAULT 关键字为可选项，用来设定参数的默认值。
- 在存储过程中，变量接收主程序传递的值，同时可以参加赋值运算，也可以对其进行赋值。
- 在存储过程中必须给变量至少赋值一次。
【示例】
create or replace procedure say_hi(
to_whom in varchar2 default '张三',
who out varchar2
)as
who_name varchar(20);
begin
who_name := '李四';
who := who_name;
dbms_output.put_line('Say Hi to '||to_whom);
end;
create or replace procedure invoke_say_hi
as
who varchar2(20);
whom varchar2(20);
begin
whom := '小明';
say_hi(whom, who);
say_hi(who => who);
dbms_output.put_line(who||'say hi to '||whom);
end;
参数的值由调用者传递，传递的参数的个数、类型和顺序应该和定义的一致。如果顺序不一致，可以采用以下调用方法:
过程名(参数名 => 参数的值, 参数名 => 参数的值, ...);
如上面的示例： say_hi(who => who); =>运算符左侧是参数名，右侧是参数表达式.
存储过程参数宽度：无法在存储过程的定义中指定存储参数的宽度，也就导致了我们无法在存储过程中控制传入变量的宽度。这个宽度是完全由外部传入时决定的。
3、删除存储过程
语法：DROP PROCEDURE 存储过程名;
示例：drop procedure hello_world;
4、查看存储过程
可以通过对数据字典的访问来查询存储过程或函数的有关信息，如果要查询当前用户的存储过程或函数的源代码，可以通过对USER_SOURCE数据字典视图的查询得到。
- 查看存储过程的脚本
- 如查询存储过程hello_world的脚本：Select text from user_source where name = ‘HELLO_WORLD’;
- 这里的过程名必须大写
- 查看存储过程的状态
- 如：select status from user_objects where object_name = ‘HELLO_WORLD’;
- 说明：
- VALID表示该存储过程有效，即通过编译；
- INVALID表示存储过程无效或需要重新编译。
- 当Oracle调用一个无效的存储过程或函数时，首先试图对其进行编译，如果编译成功则将状态置成VALID并执行，否则给出错误信息。
- 当一个存储过程编译成功，状态变为VALID，会不会在某些情况下变成INVALID？结论是完全可能的。比如一个存储过程中包含对表的查询，如果表被修改或删除，存储过程就会变成无效INVALID。所以要注意存储过程和函数对其他对象的依赖关系。
三、游标
1、普通游标
游标定义：cursor [游标名] is [sql语句]
普通游标把整个查询已经写死,调用时不可以作任何改变
注意：这里的is不能用as代替
create or replace procedure cursor_sample
as
user_name varchar2(20);
user_id varchar2(20);
cursor select_user is select t.user_name, t.user_id from t_user t;
begin
open select_user;
loop
fetch select_user into user_name, user_id;
exit when select_user%notfound;
dbms_output.put_line('UserId：'||user_id||' UserName: '||user_name);
end loop;
close select_user;
end;2、动态查询游标
动态查询游标，查询条件的参数由变量决定。
create or replace procedure cursor_sample2
as
vuser_id varchar2(20);
vuser_name varchar2(20);
cursor get_user_by_id is select t.user_id, t.user_name from t_user t where t.user_id = vuser_id;
begin
vuser_id := 'root2';
open get_user_by_id;
loop
fetch get_user_by_id into vuser_id, vuser_name;
exit when get_user_by_id%notfound;
dbms_output.put_line('UserName:'||vuser_name||' UserId:'||vuser_id);
end loop;
close get_user_by_id;
end;3、游标变量
先定义了一个引用游标类型，然后再声明了一个游标变量. 然后再用open for 来打开一个查询。
需要注意的是它可以多次使用，用来打开不同的查询。
create or replace procedure cursor_sample3
as
vuser_name varchar2(20);
vuser_id varchar2(20);
type cursor_type is ref cursor;
select_user cursor_type;
begin
open select_user for select t.user_name, t.user_id from t_user t;
loop
fetch select_user into vuser_name, vuser_id;
exit when select_user%notfound;
dbms_output.put_line('UserName:'||vuser_name||' ||UserId:'||vuser_id);
end loop;
close select_user;
dbms_output.put_line('***********************************************');
dbms_output.put_line('****************第二次使用游标**********************');
open select_user for select t.user_name, t.user_id from t_user t where t.user_id = 'root2';
loop
fetch select_user into vuser_name, vuser_id;
exit when select_user%notfound;
dbms_output.put_line('UserName:'||vuser_name||' ||UserId:'||vuser_id);
end loop;
close select_user;
end;4、游标循环的方法
游标的%found和%notfound属性。
能从游标中取出记录，得到的结果为%found，取不到记录为%notfound。
在打开一个游标之后，马上检查它的%found或%notfound属性，它得到的结果即不是true也不是false，而是null。
必须执行一条fetch语句后，这些属性才有值。
三种循环方法：
- loop循环
-
loop fetch select_user into vuser_name, vuser_id; exit when select_user%notfound; 【Do something】; end loop; close select_user;
-
- while循环
-
loop fetch select_user into vuser_name, vuser_id; while select_user%found loop dbms_output.put_line('UserName:'||vuser_name||' ||UserId:'||vuser_id); fetch select_user into vuser_name, vuser_id; end loop; close select_user;说明：我们知道了一个游标打开后，必须执行一次fetch语句，游标的属性才会起作用。所以使用while 循环时，就需要在循环之前进行一次fetch动作。
而且数据处理动作必须放在循环体内的fetch方法之前。循环体内的fetch方法要放在最后。否则就会多处理一次。这一点也要非常的小心。
使用while来循环处理游标是最复杂的方法。
-
- for循环
-
create or replace procedure cursor_sample4 as vuser_name varchar2(20); vuser_id varchar2(20); cursor select_user is select t.user_name, t.user_id from t_user t; begin for v_pos in select_user loop vuser_name := v_pos.user_name; vuser_id := v_pos.user_id; dbms_output.put_line('UserName:'||vuser_name||'UserId'||vuser_id); end loop; end;说明：for循环是比较简单实用的方法。
首先，它会自动open和close游标。解决了你忘记打开或关闭游标的烦恼。
其次，自动定义了一个记录类型及声明该类型的变量，并自动fetch数据到这个变量中。 我们需要注意v_pos 这个变量无需要在循环外进行声明，无需要为其指定数据类型。 它应该是一个记录类型，具体的结构是由游标决定的。这个变量的作用域仅仅是在循环体内。 把v_pos看作一个记录变量就可以了，如果要获得某一个值就像调用记录一样就可以了。 如v_pos.user_name由此可见，for循环是用来循环游标的最好方法。高效，简洁，安全。
-
四、异常处理
语法：exception when [异常名] then [dosomething] …
When others then [dosomething];
create or replace procedure exception_sample
as
vuser_name varchar2(20);
vuser_id varchar2(20);
vsqlcode varchar2(10);
vsqlerrm varchar2(1000);
begin
select t.user_name, t.user_id into vuser_name, vuser_id from t_user t where 1 = 0;
exception
when others
then
vsqlcode := sqlcode;
vsqlerrm := sqlerrm;
dbms_output.put_line('Exception, code:'||vsqlcode||' Error message:'||sqlerrm);
end;另外：若在代码中需要抛出异常时，用raise+异常名
create or replace procedure exception_sample2
as
vuser_name varchar2(20);
vuser_id varchar2(20);
yourexception exception; --自定义异常
cursor select_user is select t.user_name, t.user_id from t_user t;
begin
open select_user;
loop
fetch select_user into vuser_name, vuser_id;
exit when select_user%notfound;
if vuser_id = 'root2' then
raise yourexception; --抛出异常
end if;
end loop;
exception
when yourexception then
dbms_output.put_line('人品异常');
when others then
dbms_output.put_line(sqlcode||sqlerrm);
end;已经命名的异常
命名的系统异常 产生原因
ACCESS_INTO_NULL 未定义对象
CASE_NOT_FOUND CASE 中若未包含相应的 WHEN ，并且没有设置ELSE 时
COLLECTION_IS_NULL 集合元素未初始化
CURSER_ALREADY_OPEN 游标已经打开
DUP_VAL_ON_INDEX 唯一索引对应的列上有重复的值
INVALID_CURSOR 在不合法的游标上进行操作
INVALID_NUMBER 内嵌的 SQL 语句不能将字符转换为数字
NO_DATA_FOUND 使用 select into 未返回行，或应用索引表未初始化的
TOO_MANY_ROWS 执行 select into 时，结果集超过一行
ZERO_DIVIDE 除数为 0
SUBSCRIPT_BEYOND_COUNT 元素下标超过嵌套表或 VARRAY 的最大值
SUBSCRIPT_OUTSIDE_LIMIT 使用嵌套表或 VARRAY 时，将下标指定为负数
VALUE_ERROR 赋值时，变量长度不足以容纳实际数据
LOGIN_DENIED PL/SQL 应用程序连接到 oracle 数据库时，提供了不正确的用户名或密码
NOT_LOGGED_ON PL/SQL 应用程序在没有连接 oralce 数据库的情况下访问数据
PROGRAM_ERROR PL/SQL 内部问题，可能需要重装数据字典& pl./SQL系统包
ROWTYPE_MISMATCH 宿主游标变量与 PL/SQL 游标变量的返回类型不兼容
SELF_IS_NULL 使用对象类型时，在 null 对象上调用对象方法
STORAGE_ERROR 运行 PL/SQL 时，超出内存空间
SYS_INVALID_ID 无效的 ROWID 字符串
TIMEOUT_ON_RESOURCE Oracle 在等待资源时超时五、过程内部块
我们知道了存储过程的结构，语句块由begin开始，以end结束。这些块是可以嵌套。
在语句块中可以嵌套任何以下的块：Declare … begin … exception … end;
示例：
create or replace procedure innerblock_sample
as
vuser_name varchar2(20);
vuser_id varchar2(20);
cursor select_user is select t.user_name, t.user_id from t_user t;
begin
open select_user;
loop
fetch select_user into vuser_name, vuser_id;
exit when select_user%notfound;
dbms_output.put_line('UserName:'||vuser_name||'UserId'||vuser_id);
end loop;
close select_user;
declare
vuser_level varchar2(20);
cursor get_level is select t.user_level from t_user t;
begin
open get_level;
loop
fetch get_level into vuser_level;
exit when get_level%notfound;
dbms_output.put_line('UserLevel:'||vuser_level);
end loop;
close get_level;
exception when others then
dbms_output.put_line(sqlcode||sqlerrm);
end;
exception when others then
dbms_output.put_line(sqlcode||sqlerrm);
end;六、基本语法
1. 基本结构
CREATE OR REPLACE PROCEDURE 存储过程名字
(
参数1 IN NUMBER,
参数2 IN NUMBER
) IS
变量1 INTEGER :=0;
变量2 DATE;
BEGIN
--执行体
END 存储过程名字;2. SELECT INTO STATEMENT
将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条记录，否则抛出异常(如果没有记录抛出NO_DATA_FOUND)
例子：
BEGIN
SELECT col1,col2 into 变量1,变量2 FROM typestruct where xxx;
EXCEPTION
WHEN NO_DATA_FOUND THEN
xxxx;
END;3. IF 判断
IF V_TEST = 1 THEN
BEGIN
do something
END;
END IF;4. while 循环
WHILE V_TEST=1 LOOP
BEGIN
XXXX
END;
END LOOP;
5. 变量赋值
V_TEST := 123;
6. 用for in 使用cursor
IS
CURSOR cur IS SELECT * FROM xxx;
BEGIN
FOR cur_result in cur LOOP
BEGIN
V_SUM :=cur_result.列名1+cur_result.列名2
END;
END LOOP;
END;7. 带参数的cursor
CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID;
OPEN C_USER(变量值);
FETCH C_USER INTO V_NAME;
EXIT WHEN FETCH C_USER%NOTFOUND;
CLOSE C_USER;
8. 用pl/sql developer debug
连接数据库后建立一个Test WINDOW,在窗口输入调用SP的代码,F9开始debug,CTRL+N单步调试
七、关于oracle存储过程的若干问题备忘
1、在oracle中，数据表别名不能加as，如：
select a.appname from appinfo a;-- 正确
select a.appname from appinfo as a;-- 错误也许，是怕和oracle中的存储过程中的关键字as冲突的问题吧
2、在存储过程中，select某一字段时，后面必须紧跟into，如果select整个记录，利用游标的话就另当别论了。
select af.keynode into kn from APPFOUNDATION af
where af.appid=aid and af.foundationid=fid;-- 有into，正确编译
select af.keynode from APPFOUNDATION af
where af.appid=aid and af.foundationid=fid;-- 没有into，编译报错，提示：Compilation Error: PLS-00428: an INTO clause is expected in this SELECT statement
3、在利用select…into…语法时，必须先确保数据库中有该条记录，否则会报出”no data found”异常。
可以在该语法之前，先利用select count(*) from 查看数据库中是否存在该记录，如果存在，再利用select…into…
4、在存储过程中，别名不能和字段名称相同，否则虽然编译可以通过，但在运行阶段会报错
--正确
select keynode into kn from APPFOUNDATION where appid=aid and foundationid=fid;
--错误
select af.keynode into kn from APPFOUNDATION af
where af.appid=appid and af.foundationid=foundationid;
-- 运行阶段报错，提示ORA-01422:exact fetch returns more than requested number of rows
5、在存储过程中，关于出现null的问题
假设有一个表A，定义如下：
create table A(
id varchar2(50) primary key not null,
vcount number(8) not null,
bid varchar2(50) not null -- 外键
);
如果在存储过程中，使用如下语句：
select sum(vcount) into fcount from A where bid='xxxxxx';
如果A表中不存在bid=”xxxxxx”的记录，则fcount=null(即使fcount定义时设置了默认值，如：fcount number(8):=0依然无效，fcount还是会变成null)，这样以后使用fcount时就可能有问题，所以在这里最好先判断一下：
if fcount is null then
fcount:=0;
end if;这样就一切ok了。
6、Hibernate调用oracle存储过程
this.pnumberManager.getHibernateTemplate().execute(
new HibernateCallback() {
public Object doInHibernate(Session session)
throws HibernateException, SQLException {
CallableStatement cs = session
.connection()
.prepareCall("{call modifyapppnumber_remain(?)}");
cs.setString(1, foundationid);
cs.execute();
return null;
}
});
扫一扫
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
