11-Oracle函数与局部子程序
创始人
2024-06-01 21:10:51

本文章主要讲解如下内容：

1、函数的创建 2、函数的调用 3、函数的管理

（1）函数的创建

基本语法为 CREATE [OR REPLACE] FUNCTION function_name (parameter1_name [mode] datatype     [DEFAULT|:=value] [, parameter2_name [mode] datatype     [DEFAULT|:=value],…]) RETURN return_datatype AS|IS      /*Declarative section is here */ BEGIN     /*Executable section is here*/ EXCEPTION     /*Exception section is here*/ END [function_name];
注意 在函数定义的头部，参数列表之后，必须包含一个RETURN语句来指明函数返回值的类型，但不能约束返回值的长度、精度、刻度等。如果使用%TYPE，则可以隐含地包括长度、精度、刻度等约束信息； 在函数体的定义中，必须至少包含一个RETURN 语句，来指明函数返回值。也可以有多个RETURN语句，但最终只有一个RETURN语句被执行。

创建一个以部门号为参数，返回该部门最高工资的函数。 
CREATE OR REPLACE FUNCTION return_maxsal
(p_deptno emp.deptno%TYPE)
RETURN emp.sal%TYPE
ASv_maxsal emp.sal%TYPE;
BEGINSELECT max(sal) INTO v_maxsal FROM emp WHERE deptno=p_deptno;RETURN v_maxsal;
EXCEPTION WHEN NO_DATA_FOUND THENDBMS_OUTPUT.PUT_LINE('The deptno is invalid!');
END return_maxsal;
如果需要函数返回多个值，可以使用OUT或IN OUT模式参数。

创建一个函数，以部门号为参数，返回部门名、部门人数及部门平均工资。 
CREATE OR REPLACE FUNCTION ret_deptinfo(
p_deptno dept.deptno%TYPE,
p_num OUT NUMBER,
p_avg OUT NUMBER)
RETURN dept.dname%TYPE
ASv_dname dept.dname%TYPE;
BEGINSELECT dname INTO v_dname FROM dept WHERE deptno=p_deptno;SELECT count(*),avg(sal) INTO p_num,p_avg FROM emp WHERE deptno=p_deptno;RETURN v_dname;
END ret_maxsal;

（2）函数的调用

在SQL语句中调用函数 在PL/SQL中调用函数 注意 函数只能作为表达式的一部分被调用。 示例 通过return_maxsal函数的调用，输出各个部门的最高工资；通过ret_deptinfo函数调用，输出各个部门名、部门人数及平均工资。 

DECLAREv_maxsal emp.sal%TYPE;v_avgsal emp.sal%TYPE;v_num    NUMBER;v_dname  dept.dname%TYPE;
BEGINFOR v_dept IN (SELECT DISTINCT deptno FROM emp) LOOPv_maxsal:=ret_maxsal(v_dept.deptno);v_dname:=ret_deptinfo(v_dept.deptno,v_num,v_avgsal);DBMS_OUTPUT.PUT_LINE(v_dname||' '||v_maxsal||' '|| v_avgsal||' '||v_num);END LOOP;
END;
函数可以在SQL语句的以下部分调用： SELECT语句的目标列； WHERE和HAVING子句； CONNECT BY，START WITH，ORDER BY，GROUP BY子句； INSERT语句的VALUES子句中； UPDATE语句的SET子句中。
如果要在SQL中调用函数，那么函数必须符合下列限制和要求： 在SELECT语句中的函数不能修改（INSERT，UPDATE，DELETE）调用函数的SQL语句中使用的表； 函数在一个远程或并行操作中使用时，不能读/写封装变量； 函数必须是一个存储数据库对象（或存储在包中）； 函数的参数只能使用IN模式； 形式参数类型必须使用数据库数据类型； 返回的数据类型必须是数据库数据类型；

（3）函数的管理

•函数的修改 CREATE OR REPLACE FUNCTION function_name •查看函数及其源代码 查询数据字典视图USER_SOURCE SELECT name,text FROM user_source

   WHERE type='FUNCTION';

•函数重编译 ALTER FUNCTION…COMPILE ALTER FUNCTION ret_maxsal COMPILE; •删除函数 DROP FUNCTION DROP FUNCTION ret_maxsal;

局部子程序

嵌套在其他PL/SQL块中的子程序。 只能在其定义的块内部被调用，而不能在其父块外被调用。  使用局部子程序时需要注意： 局部子程序只在当前语句块内有效； 局部子程序必须在PL/SQL块声明部分的最后进行定义； 局部子程序必须在使用之前声明，如果是子程序间相互引用，则需要采用预先声明； 局部子程序可以重载。

        在一个块内部定义一个函数和一个过程。函数以部门号为参数返回该部门的平均工资；过程以部门号为参数，输出该部门中工资低于部门平均工资的员工的员工号、员工名。

     DECLAREv_deptno emp.deptno%TYPE;v_avgsal emp.sal%TYPE;FUNCTION return_avgsal(p_deptno emp.deptno%TYPE)RETURN emp.sal%TYPEASv_sal emp.sal%TYPE; BEGINSELECT avg(sal) INTO v_sal FROM empWHERE  deptno=p_deptno;RETURN v_sal;END return_avgsal;  

    PROCEDURE show_emp(p_deptno emp.deptno%TYPE)AS CURSOR c_emp ISSELECT * FROM emp WHERE deptno=p_deptno;BEGINFOR v_emp IN c_emp LOOPIF v_emp.sal

存储子程序与局部子程序区别在于：

存储子程序己经编译好放在数据库服务器端，可以直接调用，而局部子程序存在于定义它的语句块中，在运行时先进行编译； 存储子程序不能重载，而局部子程序可以进行重载； 存储子程序可以被任意的PL/SQL块调用，而局部子程序只能在定义它的块中被调用。

        在一个PL/SQL块中重载两个过程，一个以员工号为参数，输出该员工信息；另一个以员工名为参数，输出员工信息。利用这两个过程分别查询员工号为7902，7934，以及员工名为SMITH，FORD的员工信息 。

DECLAREPROCEDURE show_empinfo(p_empno emp.empno%TYPE)ASv_emp emp%ROWTYPE; BEGINSELECT * INTO v_emp FROM emp WHERE empno=p_empno;DBMS_OUTPUT.PUT_LINE(v_emp.ename||' '|| v_emp.deptno);EXCEPTION WHEN NO_DATA_FOUND THENDBMS_OUTPUT.PUT_LINE('There is not such an employee!');END show_empinfo; 

PROCEDURE show_empinfo(p_ename emp.ename%TYPE)ASv_emp emp%ROWTYPE; BEGINSELECT * INTO v_emp FROM emp WHERE ename=p_ename;DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '|| v_emp.deptno);EXCEPTION  WHEN NO_DATA_FOUND THENDBMS_OUTPUT.PUT_LINE('There is not such an employee!');WHEN TOO_MANY_ROWS THENDBMS_OUTPUT.PUT_LINE('There are more than one employee!');END show_empinfo;

BEGINshow_empinfo(7902);show_empinfo(7934);show_empinfo('SMITH');show_empinfo('FORD');
END ;
词库加载错误:未能找到文件“E:\highferrum_mysql\Configuration\Dict_Stopwords.txt”。

上一篇：cocoscreator+TS 遇到的问题

下一篇：网络继电器控制器(32 路):Y1-Y32继电器的开关

相关内容

热门资讯

我喜欢你韩语怎么说 sikid...
苗族的传统节日 贵州苗族节日有... 【岜沙苗族芦笙节】岜沙，苗语叫“分送”，距从江县城7.5公里，是世界上最崇拜树木并以树为神的枪手部落...
北京的名胜古迹 北京最著名的景... 北京从元代开始，逐渐走上帝国首都的道路，先是成为大辽朝五大首都之一的南京城，随着金灭辽，金代从海陵王...
长白山自助游攻略 吉林长白山游... 昨天介绍了西坡的景点详细请看链接：一个人的旅行，据说能看到长白山天池全凭运气，您的运气如何？今日介绍...
阿西吧是什么意思 阿西吧相当于... 即使你没有受到过任何外语培训，你也懂四国语言。汉语：你好英语：Shit韩语：阿西吧（아，씨발! ）日...