过程与函数(另外还有包与触发器)是命名的PL/SQL块(也是用户的方案对象),被编译后
存储在数据库中,以备执行。因此,其它PL/SQL块可以按名称来使用他们。所以,可以将商业逻辑、企业规则写成函数或过程保存到数据库中,以便共享。
过程和函数统称为PL/SQL子程序,他们是被命名的PL/SQL块,均
存储在数据库中,并通过输入、输出参数或输入/输出参数与其调用者
交换信息。过程和函数的唯一区别是函数总向调用者返回数据,而过程则不返回数据。在本节中,主要介绍:
1、创建存储过程和函数。
2、正确使用系统级的异常处理和用户定义的异常处理。
3、建立和管理存储过程和函数。
创建函数
1. 创建函数
语法如下:
CREATE [OR REPLACE] FUNCTION function_name
(arg1 [ { IN | OUT | IN OUT }] type1
[DEFAULT value1],
[arg2 [ { IN | OUT | IN OUT }] type2 [DEFAULT value1]],
......
[argn [ { IN | OUT | IN OUT }] typen [DEFAULT valuen]])
[ AUTHID DEFINER | CURRENT_USER ]RETURN return_type
IS | AS
<类型.变量的声明部分>
BEGIN
执行部分
RETURN expression
EXCEPTION
异常处理部分
END function_name;
IN,OUT,IN OUT是形参的模式。若省略,则为IN模式。IN模式的形参只能将实参传递给形参,进入函数内部,但只能读不能写,函数返回时实参的值不变。OUT模式的形参会忽略调用时的实参值(或说该形参的初始值总是NULL),但在函数内部可以被读或写,函数返回时形参的值会赋予给实参。IN OUT具有前两种模式的特性,即调用时,实参的值总是传递给形参,结束时,形参的值传递给实参。调用时,对于IN模式的实参可以是常量或变量,但对于OUT和IN OUT模式的实参必须是变量。
一般,只有在确认function_name函数是新函数或是要更新的函数时,才使用OR REPALCE关键字,否则容易删除有用的函数。
例1、获取某部门的工资总和:
--获取某部门的工资总和
CREATE OR REPLACEFUNCTION get_salary(
Dept_no NUMBER,
Emp_count OUT NUMBER)
RETURN NUMBER IS
V_sum NUMBER;
BEGIN
SELECT SUM(SALARY),
count(*)
INTO V_sum,
emp_count
FROM EMPLOYEES
WHERE DEPARTMENT_ID=dept_no;
RETURN v_sum;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('你需要的数据不存在!');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END get_salary;
2. 函数的调用
函数声明时所定义的参数称为形式参数,应用程序调用时为函数传递的参数称为实际参数。应用程序在调用函数时,可以使用以下三种方法向函数传递参数:
第一种参数传递格式:位置表示法。
即在调用时按形参的排列顺序,依次写出实参的名称,而将形参与实参关联起来进行传递。用这种方法进行调用,形参与实参的名称是相互独立,没有关系,强调次序才是重要的。
格式为:argument_value1[,argument_value2 …]
例2:计算某部门的工资总和:
DECLARE
v_num NUMBER;
v_sum NUMBER;
BEGIN
v_sum := get_salary(10, v_num);
dbms_output.put_line('部门号为:10的工资总和:' || v_sum || ',人数为:' || v_num);
END;
第二种参数传递格式:名称表示法。
即在调用时按形参的名称与实参的名称,写出实参对应的形参,而将形参与实参关联起来进行传递。这种方法,形参与实参的名称是相互独立的,没有关系,名称的对应关系才是最重要的,次序并不重要。
格式为: argument => parameter [,…]
其中:argument 为形式参数,它必须与函数定义时所声明的形式参数名称相同parameter 为实际参数。
在这种格式中,形势参数与实际参数成对出现,相互间关系唯一确定,所以参数的顺序可以任意排列。
例3:计算某部门的工资总和:
DECLARE
v_num NUMBER;
v_sum NUMBER;
BEGIN
v_sum := get_salary(emp_count => v_num, dept_no => 10);
dbms_output.put_line('部门号为:10的工资总和:' || v_sum || ',人数为:' || v_num);
END;
第三种参数传递格式:组合传递。
即在调用一个函数时,同时使用位置表示法和名称表示法为函数传递参数。采用这种参数传递方法时,使用位置表示法所传递的参数必须放在名称表示法所传递的参数前面。也就是说,无论函数具有多少个参数,只要其中有一个参数使用名称表示法,其后所有的参数都必须使用名称表示法。
例4:
CREATE OR REPLACE FUNCTION demo_fun(NAME VARCHAR2, --注意VARCHAR2不能给精度,如:VARCHAR2(10),其它类似
age INTEGER,
sex VARCHAR2) RETURN VARCHAR2 AS
v_var VARCHAR2(32);
BEGIN
v_var := NAME || ':' || to_char(age) || '岁.' || sex;
RETURN v_var;
END;
DECLARE
var VARCHAR(32);
BEGIN
var := demo_fun('user1', 30, sex => '男');
dbms_output.put_line(var);
var := demo_fun('user2',
age => 40,
sex => '男');
dbms_output.put_line(var);
var := demo_fun('user3',
sex => '女',
age => 20);
dbms_output.put_line(var);
END;
无论采用哪一种参数传递方法,实际参数和形式参数之间的数据传递只有两种方法:传址法和传值法。所谓传址法是指在调用函数时,将实际参数的地址指针传递给形式参数,使形式参数和实际参数指向内存中的同一区域,从而实现参数数据的传递。这种方法又称作参照法,即形式参数参照实际参数数据。输入参数均采用传址法传递数据。
传值法是指将实际参数的数据拷贝到形式参数,而不是传递实际参数的地址。默认时,输出参数和输入/输出参数均采用传值法。在函数调用时,ORACLE将实际参数数据拷贝到输入/输出参数,而当函数正常运行退出时,又将输出形式参数和输入/输出形式参数数据拷贝到实际参数变量中。
3. 参数默认值
在CREATE OR REPLACE FUNCTION 语句中声明函数参数时可以使用DEFAULT关键字为输入参数指定默认值。
例5:
CREATE OR REPLACE FUNCTION demo_fun(NAME VARCHAR2,
age INTEGER,
sex VARCHAR2 DEFAULT '男')
RETURN VARCHAR2 AS
v_var VARCHAR2(32);
BEGIN
v_var := NAME || ':' || to_char(age) || '岁.' || sex;
RETURN v_var;
END;
具有默认值的函数创建后,在函数调用时,如果没有为具有默认值的参数提供实际参数值,函数将使用该参数的默认值。但当调用者为默认参数提供实际参数时,函数将使用实际参数值。在创建函数时,只能为输入参数设置默认值,而不能为输入/输出参数设置默认值。
DECLARE
var VARCHAR(32);
BEGIN
var := demo_fun('user1', 30);
dbms_output.put_line(var);
var := demo_fun('user2', age => 40);
dbms_output.put_line(var);
var := demo_fun('user3', sex => '女', age => 20);
dbms_output.put_line(var);
END;
分享到:
相关推荐
pl/sql 编程和oracle的函数查询,非常好用,欢迎下载
oracle pl/sql 存储过程和函数与触发器
《Oracle PL/SQL程序设计(第5版)》基于Oracle数据库11g,从PL/SQL编程、PL/SQL程序结构、PL/SQL程序数据、PL/SQL中的SQL、PL/SQL应用构建、高级PL/SQL主题这6个方面详细系统地讨论了PL/SQL以及如何有效地使用它。...
适合想学习Oracle PL/SQL编程的,有例子,也有最佳实践
12oracle的PL/SQL编程-函数.包.触发器 PPT 12oracle的PL/SQL编程-函数.包.触发器 PPT
pl/sql存储过程,函数,游标,以及存储过程中的基础知识,绝对值得你收藏的经典题目,让你的pl/sql得到最大的锻炼。让你的数据库逻辑更加灵活。
《Oracle PL/SQL程序设计(第5版)》基于Oracle数据库11g,从PL/SQL编程、PL/SQL程序结构、PL/SQL程序数据、PL/SQL中的SQL、PL/SQL应用构建、高级PL/SQL主题这6个方面详细系统地讨论了PL/SQL以及如何有效地使用它。...
讲解oracle中的pl/sql的使用,存储过程,游标,函数,程序包,触发器。等等。
第19章 pl/sql函数 第20章 pl/sql包 第21章 触发器 第22章 使用对象类型 第四部分 pl/sql系统包 第23章 使用大对象 第24章 读写os文件 第25章 开发多媒体应用 第26章 开发web应用 第27章 dbms_sq...
学习pl/sql经典教程 重点: PL/SQL的控制结构 PL/SQL记录和表 过程与函数 异常处理
Oracle PL/SQL编程基础。包括,基本sql语句,函数,存储过程,触发器,包。PL/SQL工具的使,等等oracle相关的基础知识
走进Oracle、认识PL/SQL、数据表的基本操作、表中数据的基本操作、数据的基本查询、查询中函数的使用、数据表的高级查询、索引及视图的使用、数据类型、流程控制、游标、存储过程和函数、触发器、异常处理、事务和锁...
oracle实验指导,pl/sql程序设计指导。
本书是专门为Oracle应用开发人员提供的SQL和PL/SQL编程指南。通过学习本书,读者不仅可以掌握Oracle常用工具Oracle Universal Installer、Net Comfiguration Assistant、SQL Developer、SQL*Plus的作用及使用方法,...
本⽂⽬录: 1. PL/SQL 程序设计简介 2. PL/SQL块结构和组成元素 3. PL/SQL流程控制语句 4. 把游标说透 5. 异常错误处理 6. 把过程与函数说透 7. 程序包的创建与应⽤ 8. 把触发器说透
【实验目的】 1. 熟悉PL/SQL的数据类型和书写规则 2. 熟悉控制结构和游标的使用 3. 编写和运行函数、过程和触发器
本人经过多年的工作整理的Oracle/PL/SQL函数说明,TXT版本的,希望能对你有所帮助。
1、PL/SQL简介 2、PL/SQL基础 3、记录和表 4、在PL/SQL中使用SQL 5、内置SQL函数 6、游标 7、过程和函数 ...