一、存储过程
存储过程是一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。oracle可以把PL/SQL程序储存在数据库中，并可以在任何地方来运行它。存储过程被称为PL/SQL子程序，是被命名的PL/SQL快，存储在数据库，通过输入、输出参数与调用者交换信息。oracle存储过程不返回数据。
语法：
create or replace procudure 存储过名称(
参数名称 输入输出类型 参数类型，
参数名称 输入输出类型 参数类型
)
is
begin
处理语句;
exceeption;
异常处理语句;
end 存储过名称;
输出输出类型有如下三种：
- IN 定义一个输入参数变量，用于传递参数给存储过程，存储过程无法改变参数值，该参数可以是常量、或是有值的变量。
- OUT 定义一个输出参数变量，用于从存储过程获取数据，该参数必须是一个变量，该变量是否有值不重要。
- IN OUT 定义一个输入、输出参数变量，兼有以上两者的功能，该参数必须是一个变量，该变量必须有值。
输出输出参数类型一般不声明长度，因为对于IN参数，其宽度是由外部决定。 对于OUT 和IN OUT 参数，其宽度是由存储过程内部决定。对于没有说明输入输出类型的参数，默认为IN类型。
二、示例
以下代码person表结构如下：
DROP TABLE person ; CREATE TABLE person ( id NUMBER(11) NOT NULL , username VARCHAR2(255 ) NULL , age NUMBER(11) NULL , password VARCHAR2(255) NULL , PRIMARY KEY (id) ) INSERT INTO person VALUES ('1', '张三', '100', 'zhang123'); INSERT INTO person VALUES ('2', '李四', '20', 'lisi123'); INSERT INTO person VALUES ('3', '王五', '20', 'wang123'); INSERT INTO person VALUES ('4', '赵六', '20', 'zhao123');
1、查询一个（in、out）
create or replace procedure pro_person_getbyid( p_id in number, p_username out varchar2, p_age out number, p_password out varchar2 ) is begin select username, age, password into p_username, p_age, p_password from person where id = p_id; end pro_person_getbyid; -- 调用代码 -------------- declare v_id number; v_username varchar2(255); v_age number; v_password varchar2(255); begin v_id := 1; pro_person_getbyid(v_id, v_username, v_age, v_password); dbms_output.put_line('username:'||v_username||' age:'||v_age||' password:'||v_password); end;
2、查询一个（in、out）使用rowtype
create or replace procedure pro_person_getrow( p_id in number, p_row out person%rowtype, -- rowtype类型变量 p_count out number -- 标记是否找到记录 ) is begin select * into p_row from person where id = p_id; p_count := SQL%ROWCOUNT; exception when no_data_found then p_count := 0; end pro_person_getrow; -- 调用-------------- declare v_id number := 28; v_row person%rowtype; v_count number; begin pro_person_getrow(v_id, v_row, v_count); dbms_output.put_line(v_count); dbms_output.put_line('id:'||v_row.id||' username:'||v_row.username||' age:'||v_row.age||' password:'||v_row.password); end;
3、添加记录（in、out）
create or replace procedure pro_person_insert( p_id number, p_username varchar2, p_age number, p_password varchar2, p_count out number -- 是否添加成功 ) is begin insert into person (id, username, age, password) values(p_id, p_username, p_age, p_password); p_count := SQL%ROWCOUNT; -- SQL%ROWCOUNT为 隐式游标的属性 commit; exception when others then p_count := 0; -- 失败 end pro_person_insert; -- 调用procedure declare v_id number := 28; v_username varchar2(255) := 'xiaoli'; v_age number := 19; v_password varchar2(255) := 'xiao123'; v_count number; begin pro_person_insert(p_id => v_id, p_username => v_username, p_age => v_age, p_password => v_password, p_count => v_count); -- pro_person_insert(v_id , v_username, v_age, v_password, v_count); dbms_output.put_line('影响行数'||v_count); end;
4、更新（in、out）
create or replace procedure pro_person_update( p_id number, p_age number, p_password varchar2, p_count out number ) is begin update person set age = p_age, password = p_password where id = p_id; p_count := SQL%ROWCOUNT; commit; exception when no_data_found then p_count := 0; when others then p_count := -1; end pro_person_update; -- 调用--------------------- declare v_id number := 28; v_age number := 19; v_password varchar2(255) := 'password'; v_count number; begin pro_person_update(v_id, v_age, v_password, v_count); dbms_output.put_line('影响行数'||v_count); end;
5、删除（in、out）
create or replace procedure pro_person_delete( p_id number, p_count out number ) is begin delete from person where id = p_id; p_count := SQL%ROWCOUNT; commit; exception when no_data_found then p_count := 0; when others then p_count := -1; end pro_person_delete; -- 调用---------------- declare v_id number := 28; v_count number; begin pro_person_delete(v_id, v_count); dbms_output.put_line('影响行数'||v_count); end;
6、查询所有（in、out）使用sys_refcursor
create or replace procedure pro_person_findall2( p_cursor out sys_refcursor -- 输出参数为包类型 ) is begin open p_cursor for select * from person; exception when others then DBMS_OUTPUT.PUT_LINE('获取信息发生错误'); end pro_person_findall2; ----调用--------------------------------------------------- declare c_cursor sys_refcursor; r_person person%rowtype; begin pro_person_findall2(c_cursor); --2、打开游标 -- open c_cursor; --此处不需要显示地打开游标，因为调用存储过程的时候返回的游标已经打开了 --3、提取数据 loop fetch c_cursor into r_person; exit when c_cursor%notfound; -- 下面没有数据的时候，退出 dbms_output.put_line('id:'||r_person.id); dbms_output.put_line('username:'||r_person.username); dbms_output.put_line('age:'||r_person.age); end loop; end;
7、查询所有（in、out）使用自定义类型查询
-- 创建一个包类型 create or replace package pkg_const as type r_cursor is ref cursor; end pkg_const; -- 创建存储过程， create or replace procedure pro_person_findall( p_cursor out pkg_const.r_cursor -- 输出参数为包类型 ) is begin open p_cursor for select * from person; exception when others then DBMS_OUTPUT.PUT_LINE('获取信息发生错误'); end pro_person_findall; ----调用------------------------------------ declare c_cursor pkg_const.r_cursor; r_person person%rowtype; begin pro_person_findall(c_cursor); --2、打开游标 -- open c_cursor; --3、提取数据 loop fetch c_cursor into r_person; exit when c_cursor%notfound; -- 下面没有数据的时候，退出 dbms_output.put_line('id:'||r_person.id); dbms_output.put_line('username:'||r_person.username); dbms_output.put_line('age:'||r_person.age); end loop; end;
三、存储过程其他语句
查看存储过程
DESCRIBE 存储过程名;
删除存储过程
DROP PROCEDURE 存储过程名;
