PL/SQL之--存储过程

一、存储过程

　　存储过程是一组为了完成特定功能的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)isbegin  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 -- 标记是否找到记录)isbegin  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 -- 是否添加成功)isbegin   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;-- 调用proceduredeclare  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)isbegin  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)isbegin  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 -- 输出参数为包类型)isbegin   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 -- 输出参数为包类型)isbegin   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 存储过程名;