PLSQL 基础教程 五 常用数据库对象简介(函数存储过程包)

本文简要介绍PLSQL中常用的一些数据库对象，主要包括函数、存储过程和包等等，将从相关的简介、语法、用途等等方面进行介绍。

 

1.       函数

简介

函数是Oracle中的一个可以单独存在的数据库对象，主要用于封装一些业务处理逻辑，同时在处理完成相关逻辑之后必须返回一个结果值。

 

创建语法

函数的创建语法如下：

 
--函数的创建语法
CREATEFUNCTION函数名(参数1IN参数1类型,参数2 IN参数2类型, .. .)
       RETURNIS函数的返回值类型;
BEGIN
--业务逻辑处理代码
RETURN函数的返回值(类型与定义的返回类型一致即可);
END函数名;

语法格式说明：

a. 创建函数的关键字为 FUNCTION

b. 函数名可以自定义，不能与系统已有的冲突

c. 函数的参数可以根据需要有多个，也可以没有参数

d. 函数一定有返回值，而且返回值的类型和函数的定义中的返回值类型必须保持一致

 

函数举例

此处已两个简单的函数为例进行说明，一个是带参数的函数，另一个是不带参数的函数：

a. 带参数的函数

--带两个参数的函数
--返回两个参数之和
createorreplacefunction cux_sum(num1number, num2 number)
returnnumberis
 
begin
  return num1 + num2;
end cux_sum;
 
--测试函数的调用
SELECT CUX_SUM(1,2)FROM DUAL

可以看到上述函数的功能是求两个整数参数之和。

带参数函数的调用直接通过 函数名(参数) 调用即可，不同的用户可以在函数名前加上所属用户即 所属用户.函数名(参数) 的形式进行调用。

 

b. 不带参数的函数

--不带参数的函数
CREATEORREPLACEFUNCTION CUX_SUM2
RETURNVARCHAR2IS
BEGIN
  RETURN'Y';
END CUX_SUM2;
 
--测试函数的调用
SELECT CUX_SUM2FROM DUAL;

 

可以看到上述函数的功能是返回一个字符串“Y”。

不带参数的函数的调用直接通过 函数名 调用即可，不同的用户可以在函数名前加上所属用户即所属用户.函数名  的形式进行调用。

  

2.       存储过程

简介

与函数类似，存储过程也是一种可以单独存储在Oracle数据库中的一种对象，也是用于封装一些业务处理逻辑，只是存储过程没有返回值，但是有输出参数。

 

创建语法

 

--存储过程语法
CREATEORREPLACEPROCEDURE存储过程名(参数1INOUT参数1类型,参数2 INOUT参数2类型, .. .) IS
BEGIN
 
    --业务逻辑处理代码  
 
END存储过程名;

语法格式说明：

e. 创建存储过程的关键字为 PROCEDURE

f. 存储过程名可以自定义，不能与系统已有的冲突

g. 存储过程的参数可以根据需要有多个，也可以没有参数,其中 IN 或者OUT是用于标识该参数是输入参数还是输出参数的

h. 存储过程没有返回值，但是可以有输出参数

 

存储过程举例

a. 带参数的存储过程

--存储过程举例
--求两个整数之和
CREATEORREPLACEPROCEDURE CUX_PRO(NUM1INNUMBER,
                                    NUM2INNUMBER,
                                    NUM3OUTNUMBER)IS
BEGIN
 
  --业务逻辑处理代码  
  NUM3 := NUM1 + NUM2;
END CUX_PRO;

由于存储过程没有返回值，因此不能像函数一样直接在SQL语句中使用，只能存在于代码块中(PLSQL代码块的知识后续讲解),此处为了演示方便直接在PLSQL窗口中进行测试调用：

直接通过SQL查询语句调用存储会报错。

 

可以看到该存储过程的逻辑是把输入的两个参数求和的值赋值给力输出参数。

存储过程必须存在于代码块中,带参数的存储的调用直接通过存储过程名(参数) 调用即可，不同的用户可以在函数名前加上所属用户即所属用户. 存储过程名(参数)  的形式进行调用。

不带参数的存储过程调用的时候去掉后边的括号和参数即可。 

3.       函数和存储过程的区别

相同点：

函数和存储过程都是数据库中的数据对象，可以单独存储；

函数和存储过程都是可以处理相关的业务逻辑；

函数和存储过程都是可以存在于PLSQL代码块中的

不同点：

函数有返回值，而存储过程没有；

存储过程有输出参数，而函数没有；

函数可以存在于SQL查询语句中，而存储过程不可以   

4.       包

简介

包 PACKAGE，也是数据库中的一种对象，一般主要用来管理一组函数和存储过程的单元，我们可以把某一类型或者某一个功能模块的函数和存储过程统一定义在一个包中，便于维护和管理。

 

创建语法

包的创建分为两部分，包头和包体

包头的定义：

--包头定义语法
CREATEORREPLACEPACKAGE包名IS
 
 
  --主要用于申明变量、函数、存储过程等等
 
END包名;

包体的定义： 

CREATEORREPLACEPACKAGEBODY包名IS
 
  --相关的变量赋值、函数和存储过程的具体实现逻辑等等
 
BEGIN
  -- INITIALIZATION
  <STATEMENT>;
END包名;

包举例

我们将之前创建的函数和存储过程定义到一个新建的包中，以此来演示包的用法：

--包使用举例
--包头定义
CREATEORREPLACEPACKAGE CUX_TEST_PKGIS
 
  --定义函数
  FUNCTION CUX_SUM(NUM1NUMBER, NUM2 NUMBER) RETURNNUMBER;
 
  --定义存储过程
  PROCEDURE CUX_PRO(NUM1INNUMBER, NUM2INNUMBER, NUM3OUTNUMBER);
 
END CUX_TEST_PKG;
 
--包体定义
CREATEORREPLACEPACKAGEBODY CUX_TEST_PKGIS
 
  FUNCTION CUX_SUM(NUM1NUMBER, NUM2 NUMBER) RETURNNUMBERIS
  BEGIN
    RETURN NUM1 + NUM2;
  END CUX_SUM;
 
  PROCEDURE CUX_PRO(NUM1INNUMBER, NUM2INNUMBER, NUM3OUTNUMBER)IS
  BEGIN
 
    --业务逻辑处理代码  
    NUM3 := NUM1 + NUM2;
  END CUX_PRO;
 
END CUX_TEST_PKG;

使用了包之后，再次调用函数和存储过程的话，就按照 包名.函数名(参数)和 包名.存储过程名(参数) 进行调用即可

此处已函数为例进行演示：

SELECT CUX_TEST_PKG.CUX_SUM(1,4) FROM DUAL;

 

 

 

5. 附录

 

附录

a. 本系列教程为个人原创，基于实际工作中的使用情况及个人理解，仅供学习交流之用，有不足之处还望批评指正，希望共同提高

b. 本节相关脚本

--带两个参数的函数
--返回两个参数之和
CREATE OR REPLACE FUNCTION CUX_SUM(NUM1 NUMBER, NUM2 NUMBER) 
RETURN NUMBER IS
BEGIN
  RETURN NUM1 + NUM2;
END CUX_SUM;


--测试函数的调用
SELECT CUX_SUM(1,2) FROM DUAL;

--不带参数的函数
CREATE OR REPLACE FUNCTION CUX_SUM2
RETURN VARCHAR2 IS
BEGIN
  RETURN 'Y';
END CUX_SUM2;


--测试函数的调用
SELECT CUX_SUM2 FROM DUAL;



--存储过程举例
--求两个整数之和
CREATE OR REPLACE PROCEDURE CUX_PRO(NUM1 IN NUMBER,
                                    NUM2 IN NUMBER,
                                    NUM3 OUT NUMBER) IS
BEGIN

  --业务逻辑处理代码   
  NUM3 := NUM1 + NUM2;
END CUX_PRO;


--存储过程不能直接在SQL查询中调用
--select CUX_PRO(1,2,3) from dual


--包使用举例
--包头定义
CREATE OR REPLACE PACKAGE CUX_TEST_PKG IS

  --定义函数
  FUNCTION CUX_SUM(NUM1 NUMBER, NUM2 NUMBER) RETURN NUMBER;
  
  --定义存储过程
  PROCEDURE CUX_PRO(NUM1 IN NUMBER, NUM2 IN NUMBER, NUM3 OUT NUMBER);
  
END CUX_TEST_PKG;

--包体定义
CREATE OR REPLACE PACKAGE BODY CUX_TEST_PKG IS

  FUNCTION CUX_SUM(NUM1 NUMBER, NUM2 NUMBER) RETURN NUMBER IS
  BEGIN
    RETURN NUM1 + NUM2;
  END CUX_SUM;

  PROCEDURE CUX_PRO(NUM1 IN NUMBER, NUM2 IN NUMBER, NUM3 OUT NUMBER) IS
  BEGIN
  
    --业务逻辑处理代码   
    NUM3 := NUM1 + NUM2;
  END CUX_PRO;

END CUX_TEST_PKG;

SELECT CUX_TEST_PKG.CUX_SUM(1,4) FROM DUAL;