开发者社区> 烟火_> 正文

PL/SQL之--存储过程

简介: 一、存储过程   存储过程是一组为了完成特定功能的SQL 语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。oracle可以把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
)
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 存储过程名;

 

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
PL/SQL之--存储过程
一、存储过程   存储过程是一组为了完成特定功能的SQL 语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。oracle可以把PL/SQL程序储存在数据库中,并可以在任何地方来运行它。
640 0
阿里云ECS云服务器初始化设置教程方法
阿里云ECS云服务器初始化是指将云服务器系统恢复到最初状态的过程,阿里云的服务器初始化是通过更换系统盘来实现的,是免费的,阿里云百科网分享服务器初始化教程: 服务器初始化教程方法 本文的服务器初始化是指将ECS云服务器系统恢复到最初状态,服务器中的数据也会被清空,所以初始化之前一定要先备份好。
14268 0
SqlPlus 中,如何查看某存储过程的内容?
select text from all_source where name = '存储过程名';  例如建立存储过程的语句为: CREATE OR REPLACE PROCEDURE EMP_COUNT AS V_TOTAL NUMBER(10); BEGIN SELECT COUNT(*) INTO V_TOTAL FROM EMP; DBMS_OUTPUT.PUT_LINE
881 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,阿里云优惠总结大概有三种登录方式: 登录到ECS云服务器控制台 在ECS云服务器控制台用户可以更改密码、更换系.
29040 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,大概有三种登录方式:
13548 0
+关注
烟火_
从事java开发多年
113
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
JS零基础入门教程(上册)
立即下载
性能优化方法论
立即下载
手把手学习日志服务SLS,云启实验室实战指南
立即下载