【Oracle】存储过程之完整篇

转载

mob604757020b64 2018-09-15 18:27:00

1.语法

create [or replace] procedure pro_name[(parameter1,parameter2,...)] is|as
begin
  plsql_sentences;
[exception]
  [dowith_sentences;]
end pro_name;

参数说明：

pro_name:存储过程名称

parameter1:参数

plsql_sentences：PL/SQL语句，它是存储过程功能实现的主体。

dowith_sentences:异常处理语句,也是PL/SQL语句。

上面的parameter1指的是存储过程被调用时候用的参数，内部变量需要在is|as关键字后边定义，并使用;号结束

2.环境准备

2.1 创建表TMP001

字段	描述	类型
id	客户编号	int
name	客户名称	varchar2(100)
gender	客户性别	varchar2(2)
age	客户年龄	int
address	客户地址	varchar2(200)

脚本：

create table TMP001(
id int not null,
name varchar2(100),
gender varchar2(2),
age int,
address varchar2(200)
)

3.具体案例

3.1创建一个简单存储过程，往表TMP001插入一条记录

/********************************************************
功能名称：用于简单存储过程测试
用途   ：插入一条记录到TMP001表中

参数列表
---------------------------------------------------------
参数    IN/OUT   类型    描述
---------------------------------------------------------
无
---------------------------------------------------------

版本号：1.0 
---------------------------------------------------------
作者：Qinys
日期：20180915
说明：创建
---------------------------------------------------------
---------------------------------------------------------
********************************************************/
create or replace procedure proInsertTmp001 is
begin
  insert into TMP001 values(1,'张三','男',29,'云南省昆明市');
  commit;
  dbms_output.put_line('插入记录成功！');
end proInsertTmp001;

上述存储过程放在PL/SQL Developer中编译通过后，即可调用查看结果

调用存储过程

①execute命令调用：execute proInsertTmp001

【Oracle】存储过程之完整篇_传递参数

②使用PL/SQL块

begin

proInsertTmp001;

end;

【Oracle】存储过程之完整篇_传递参数_02

3.2声明一个带IN模式的存储过程

/**********************************************************************
功能名称：用于测试带IN的存储过程
用途   ：插入一条记录到TMP001表中

参数列表
参数列表
-----------------------------------------------------------------------
参数          IN/OUT   类型         描述
-----------------------------------------------------------------------
int_id        IN       INT          客户编号    
var_name      IN       VARCHAR2     客户名称
var_gender    IN       VARCHAR2     客户性别
int_age       IN       INT          客户年龄
var_address   IN       VARCHAR2     客户地址
-----------------------------------------------------------------------

版本号：1.0 
-----------------------------------------------------------------------
作者：Qinys
日期：20180915
说明：创建
-----------------------------------------------------------------------
-----------------------------------------------------------------------
**********************************************************************/
create or replace procedure proInsertTmp002( 
                                            int_id in int,
                                            var_name in varchar2,
                                            var_gender in varchar2,
                                            int_age in  int,
                                            var_address in  varchar2
                                           ) is
begin
  insert into TMP001 values(int_id,var_name,var_gender,int_age,var_address);
  commit;
  dbms_output.put_line('插入记录成功！');
end proInsertTmp002;

调用存储过程

【Oracle】存储过程之完整篇_默认值_03

查看结果

【Oracle】存储过程之完整篇_默认值_04

3.3 声明一个带OUT模式的存储过程

/**********************************************************************
功能名称：用于测试带OUT的存储过程
用途   ：调用存储过程，并返回调用结果

参数列表
-----------------------------------------------------------------------
参数          IN/OUT   类型         描述
-----------------------------------------------------------------------
int_id        IN       INT          客户编号    
var_name      IN       VARCHAR2     客户名称
var_gender    IN       VARCHAR2     客户性别
int_age       IN       INT          客户年龄 默认值为：18
var_address   IN       VARCHAR2     客户地址 默认值为：China
flag          OUT      VARCHAR2     1-成功 0-失败
msg           OUT      VARCHAR2     返回错误代码
-----------------------------------------------------------------------

版本号：1.0 
-----------------------------------------------------------------------
作者：Qinys
日期：20180915
说明：创建
-----------------------------------------------------------------------
-----------------------------------------------------------------------
**********************************************************************/
create or replace procedure proInsertTmp004( 
                                            int_id     in int,
                                            var_name   in varchar2,
                                            var_gender in varchar2,
                                            int_age    in int default 18,
                                            var_address in varchar2 default 'China',
                                            flag out varchar2,
                                            msg  out varchar2 
                                           ) is
begin
  proInsertTmp003(int_id,var_name,var_gender,int_age,var_address);
  commit;
  --主体程序运行完毕
  flag :='1';
--异常处理
exception
  when others then 
    rollback;
  flag :=0;
  msg :='错误原因'|| sqlcode || ',' || sqlerrm(sqlcode);   
end proInsertTmp004;

我们正常调用存储过程proInsertTmp004

【Oracle】存储过程之完整篇_默认值_05

在output可以看到结果

【Oracle】存储过程之完整篇_版本号_06

非正常调用

【Oracle】存储过程之完整篇_传递参数_07

output结果

【Oracle】存储过程之完整篇_传递参数_08

3.4 声明一个带OUT模式的存储过程

/**********************************************************************
功能名称：根据flag标志，若为True，则返回平方，反之，则返回平方根
用途   ： 用于测试IN OUT参数的存储过程

参数列表
-----------------------------------------------------------------------
参数          IN/OUT   类型         描述
-----------------------------------------------------------------------
num           IN OUT   NUMBER       输入的值
flag          IN       boolean      只有True与False两种类型
-----------------------------------------------------------------------

版本号：1.0 
-----------------------------------------------------------------------
作者：Qinys
日期：20180915
说明：创建
-----------------------------------------------------------------------
-----------------------------------------------------------------------
**********************************************************************/
create or replace procedure pro_square(
                                      num in out number,
                                      flag in boolean) is
                                      
i int :=2;
begin
  if flag then 
    num:=power(num,i);
  else
    num:=sqrt(num);
  end if;
end pro_square;

调度存储过程

declare
var_temp number;
var_num number;
bool_flag boolean;
begin
 var_temp := 4;
 var_num := var_temp;
 bool_flag := false; 
 pro_square(var_num,bool_flag);
 if bool_flag then 
   dbms_output.put_line(var_temp||'的平方是:'||var_num);
 else 
   dbms_output.put_line(var_temp||'的平方根是:'||var_num);
 end if;
end;

调度结果是：

【Oracle】存储过程之完整篇_传递参数_09

3.5 创建带有默认值的存储过程

/**********************************************************************
功能名称：用于测试带默认值的存储过程
用途   ：插入一条记录到TMP001表中

参数列表
-----------------------------------------------------------------------
参数          IN/OUT   类型         描述
-----------------------------------------------------------------------
int_id        IN       INT          客户编号    
var_name      IN       VARCHAR2     客户名称
var_gender    IN       VARCHAR2     客户性别
int_age       IN       INT          客户年龄 默认值为：18
var_address   IN       VARCHAR2     客户地址 默认值为：China
-----------------------------------------------------------------------

版本号：1.0 
-----------------------------------------------------------------------
作者：Qinys
日期：20180915
说明：创建
-----------------------------------------------------------------------
-----------------------------------------------------------------------
**********************************************************************/
create or replace procedure proInsertTmp003( 
                                            int_id in int,
                                            var_name in varchar2,
                                            var_gender in varchar2,
                                            int_age in int default 18,
                                            var_address in varchar2 default 'China'
                                           ) is
begin
  insert into TMP001 values(int_id,var_name,var_gender,int_age,var_address);
  commit;
  dbms_output.put_line('插入记录成功！');
end proInsertTmp003;