《存储函数和过程》由会员分享，可在线阅读，更多相关《存储函数和过程（22页珍藏版）》请在金锄头文库上搜索。
1、存储函数和过程Inspur group2存储函数和过程存储函数和过程2010-7-29Inspur group本节内容： ?创建存储过程和函数。 ?正确使用系统级的异常处理和用户定义的异常处理。3存储函数和过程存储函数和过程2010-7-29Inspur group?过程和函数统称为 PL/SQL 子程序，他们是被命名的 PL/SQL 块，均存储在数据库中均存储在数据库中，并通过输入、输出参数或输入 / 输出参 数与其调用者交换信息。?过程和函数的唯一区别是函数总向调用者返回数据，而过程则 不返回数据。唯一区别是函数总向调用者返回数据，而过程则 不返回数据。4 创建函数创建函数 1. 建立内嵌
2、函数建立内嵌函数2010-7-29Inspur group语法如下： CREATE OR REPLACE FUNCTION function_name (argment IN| IN OUT type, argment IN | OUT | IN OUT type RETURN return_type IS | AS BEGIN FUNCTION_body EXCEPTION 其它语句 END;5创建函数创建函数2010-7-29Inspur group例例 1. 获取某部门的工资总和：获取某部门的工资总和：CREATE OR REPLACE FUNCTION hr.get_salary( d
3、ept_no NUMBER, emp_count OUT NUMBER ) RETURN NUMBER IS v_sum NUMBER; BEGIN SELECT SUM(salary),count(*) INTO v_sum,emp_count FROM hr.employees WHERE department_id = dept_no; RETURN v_sum; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(您需要的数据不存在！); WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE(
4、程序运行错误，请使用游标！); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(发生其它错误！); END;CREATE OR REPLACE FUNCTION hr.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 hr.employees WHERE department_id = dept_no; RETURN v_sum; EX
5、CEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(您需要的数据不存在！); WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE(程序运行错误，请使用游标！); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(发生其它错误！); END;6 创建函数创建函数 2.内嵌函数的调用内嵌函数的调用2010-7-29Inspur group函数声明时所定义的参数称为形式参数，应用程序调用时为函数传递的参 数称为实际参数应用程序在调用函数时，可以使用以下三种方法向函数传 递参数：第一种
6、参数传递格式称为位置表示法，格式为：第一种参数传递格式称为位置表示法，格式为： argument_value1,argument_value2 例：计算某部门的工资总和例：计算某部门的工资总和DECLARE v_num NUMBER; v_sum NUMBER; BEGIN v_sum :=get_salary(50, v_num); DBMS_OUTPUT.PUT_LINE(50 号部门工资总和： | v_sum|，人数： |v_num); END;DECLARE v_num NUMBER; v_sum NUMBER; BEGIN v_sum :=get_salary(50, v_num);
7、 DBMS_OUTPUT.PUT_LINE(50 号部门工资总和： | v_sum|，人数： |v_num); END;7 创建函数创建函数 2.内嵌函数的调用内嵌函数的调用 第二种参数传递格式称为名称表示法，格式为： argument = parameter , 其中： argument 为形式参数，它必须与函数定义时所声明的形式 参数名称相同。 Paramet er为实际参数。 在这种格式中，形势参数与实际参数成对出现，相互间关系唯一确 定，所以参数的顺序可 以任意排列。2010-7-29Inspur group8 创建函数创建函数 2.内嵌函数的调用内嵌函数的调用2010-7-29Ins
8、pur group第三种参数传递格式称为混合表示法： 即在调用一个函数时，同时使用位置表示法和名称表示法为函数传递参 数。 采用这种 参数传递方法时，使用位置表示法所传递的参数必须放在名称 表示法所传递的参数前面。也 就是说，无论函数具有多少个参数，只要 其中有一个参数使用名称表示法，其后所有的参数 都必须使用名称表示 法。无论函数具有多少个参数，只要 其中有一个参数使用名称表示法，其后所有的参数 都必须使用名称表示 法。9 创建函数创建函数 2.内嵌函数的调用内嵌函数的调用2010-7-29Inspur groupCREATE OR REPLACE FUNCTION hr.demo_fun(
9、 Name VARCHAR2, 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 := hr.demo_fun( user1 , 30, sex = 男 ); DBMS_OUTPUT.PUT_LINE(var); Var := hr.demo_fun( user2 , age = 40, sex = 男
10、 ); DBMS_OUTPUT.PUT_LINE(var); Var := hr.demo_fun( user3 , sex = 女 , age = 20); DBMS_OUTPUT.PUT_LINE(var); END;CREATE OR REPLACE FUNCTION hr.demo_fun( Name VARCHAR2, Age INTEGER, Sex VARCHAR2) RETURN VARCHAR2 AS V_var VARCHAR2(32); BEGIN V_var := name|： |TO_CHAR(age)| 岁， |sex; RETURN v_var; END; /*调
11、用*/ DECLARE Var VARCHAR(32); BEGIN Var := hr.demo_fun( user1 , 30, sex = 男 ); DBMS_OUTPUT.PUT_LINE(var); Var := hr.demo_fun( user2 , age = 40, sex = 男 ); DBMS_OUTPUT.PUT_LINE(var); Var := hr.demo_fun( user3 , sex = 女 , age = 20); DBMS_OUTPUT.PUT_LINE(var); END;例：混合表示法例：混合表示法10 创建函数创建函数 总结：参数传递总结：参数传
12、递2010-7-29Inspur group?无论采用哪一种参数传递方法，实际参数和形式参数之间的数据 传递只有两种方法： 传址法和传值法。传址法和传值法。 ?所谓传址法是指在调用函数时，将实际参数的地址指针传递给形 式参数， 使形式参数和实际参数指向内存中的同一区域，从而实 现参数数据的传递。这种方法又称作 参照法，即形式参数参照实 际参数数据。输入参数均采用传址法传递数据。输入参数均采用传址法传递数据。 ?传值法是指将实际参数的数据拷贝到形式参数，而不是传递实际 参数的地址。默认时，输出参数和输入默认时，输出参数和输入 / 输出参数均采用传值法。输出参数均采用传值法。 在函数调用时， OR
13、ACLE 将实际参数数据拷贝 到输入 / 输出参 数，而当函数正常运行退出时，又将输出形式参数和输入 / 输出形 式参数数据拷贝到实际参数变量中。11 创建函数创建函数 3. 参数默认值参数默认值2010-7-29Inspur group在 CREATE OR REPLACE FUNCTION 语句中声明函数参数时可以 使用 DEFAULT 关键字为输入参数指定默认值。CREATE OR REPLACE FUNCTION hr.demo_fun2( Name VARCHAR2, Age INTEGER, Sex VARCHAR2 DEFAULT 男 ) RETURN VARCHAR2 AS V
14、_var VARCHAR2(32); BEGIN V_var := name| ： |TO_CHAR(age)| 岁， |sex; RETURN v_var; END;CREATE OR REPLACE FUNCTION hr.demo_fun2( 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;12 创建函数创建函数 3. 参数默
15、认值参数默认值2010-7-29Inspur group在 CREATE OR REPLACE FUNCTION 语句中声明函数参数时可以 使用 DEFAULT 关键字为输入参数指定默认值。CREATE OR REPLACE FUNCTION hr.demo_fun2( 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;CREATE OR REPLACE FUNCTION hr.demo_fun2( 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;13 创建函数创建函数 3. 参数默认值参数默认值2010-7-29Inspur groupDECLARE Var VARCHAR(32); BEGIN Var := hr.demo_fun2(user1 , 30
