PL/SQL函数和存储过程-白红宇

前言

活到老，学到老。

基本概念

--ORACLE 提供可以把PL/SQL 程序存储在数据库中，并可以在任何地方来运行它。这样就叫存储过程或函数。过程和函数统称为PL/SQL子程序，他们是被命名的PL/SQL块，均存储在数据库中，并通过输入、输出参数或输入/输出参数与其调用者交换信息。过程和函数的唯一区别是函数总向调用者返回数据，而过程则不返回数据。

函数

创建函数

语法如下：

CREATE [OR REPLACE] FUNCTION function_name
[ (argment [ { IN | IN OUT }] Type,
argment [ { IN | OUT | IN OUT } ] Type ]
[ AUTHID DEFINER | CURRENT_USER ]
RETURN return_type
{ IS | AS }
<类型.变量的说明>
BEGIN
FUNCTION_body
EXCEPTION
其它语句
END;

说明:

--OR REPLACE 为可选. 有了它, 可以或者创建一个新函数或者替换相同名字的函数, 而不会出现冲突。

--函数名后面是一个可选的参数列表, 其中包含 IN, OUT 或 IN OUT 标记. 参数之间用逗号隔开. IN 参数标记表示传递给函数的值在该函数执行中不改变; OUT 标记表示一个值在函数中进行计算并通过该参数传递给调用语句; IN OUT 标记表示传递给函数的值可以变化并传递给调用语句. 若省略标记, 则参数隐含为 IN。

--因为函数需要返回一个值, 所以 RETURN 包含返回结果的数据类型。

例1：不带参数的函数。

屏幕快照 2018-01-14 02.02.37.png

执行该函数：

屏幕快照 2018-01-14 02.03.40.png

例2：获取某部门的工资总和。

屏幕快照 2018-01-14 02.04.59.png

函数调用

--函数声明时所定义的参数称为形式参数，应用程序调用时为函数传递的参数称为实际参数。应用程序在调用函数时，可以使用以下三种方法向函数传递参数。

第一种参数传递格式称为位置表示法，格式为：

argument_value1[,argument_value2 …]

例3：计算某部门的工资总和。

屏幕快照 2018-01-14 02.07.34.png

第二种参数传递格式称为名称表示法，格式为：

argument => parameter [,…]

--其中：argument 为形式参数，它必须与函数定义时所声明的形式参数名称相同。Parameter 为实际参数。

--这种格式中，形式参数与实际参数成对出现，相互间关系唯一确定，所以参数的顺序可以任意排列。

例4：计算某部门的工资总和。

屏幕快照 2018-01-14 02.11.49.png

第三种参数传递格式称为混合表示法：

--即在调用一个函数时，同时使用位置表示法和名称表示法为函数传递参数。采用这种参数传递方法时，使用位置表示法所传递的参数必须放在名称表示法所传递的参数前面。也就是说，无论函数具有多少个参数，只要其中有一个参数使用名称表示法，其后所有的参数都必须使用名称表示法

例5：

屏幕快照 2018-01-14 02.12.49.png

删除函数

DROP FUNCTION [user.]Function_name;

存储过程

创建过程

语法如下：

CREATE [OR REPLACE] PROCEDURE Procedure_name
[ (argment [ { IN | IN OUT }] Type,
argment [ { IN | OUT | IN OUT } ] Type ]
[ AUTHID DEFINER | CURRENT_USER ]
{ IS | AS }
<类型.变量的说明>
BEGIN
<执行部分>
EXCEPTION
<可选的异常错误处理程序>
END;

例6：删除指定员工记录。

屏幕快照 2018-01-14 02.15.42.png

存储过程调用

ORACLE 使用EXECUTE 语句来实现对存储过程的调用：
EXEC[UTE] Procedure_name( parameter1, parameter2…);

删除过程

DROP PROCEDURE [user.]Procudure_name;