开发Oracle 函数
函数用于返回特定的数据。如果在应用程序中,经常需要执行SQL语句来返回特定数据,那么可以基于这些操作建立特定的函数。建立函数的语法如下: CREATE [OR REPLACE] FUNCTION function_name (argument1 [model] datatype1, argument2 [model] datatype2, ...
函数用于返回特定的数据。如果在应用程序中,经常需要执行SQL语句来返回特定数据,那么可以基于这些操作建立特定的函数。建立函数的语法如下:
CREATE [OR REPLACE] FUNCTION function_name (argument1 [model] datatype1, argument2 [model] datatype2, ....) RETURN datatype IS|AS PL/SQL Block;
如上所示,function_name用于指定函数的名称;argument1、argument2等则用于指定函数的参数,注意当指定参数的数据类型时,不能指定其长度;RETURN子句用于指定函数返回值的数据类型;IS|AS用于开始一个PLS/QL块。注意,当建立函数时,在函数头部必须要带有RETURN子句,在函数体内必须要包含一条RETURN语句。另外,当建立函数时,既可以指定输入参数(IN),也可以指定输出参数(OUT),以及输入输出参数(IN OUT)。下面通过实例说明建立函数,以及使用各种参数模式的方法。
1、建立函数,不带任何参数
当建立函数时,既可以带有参数,也可以不带参数。下面以建立用于显示当前数据库用户名的函数为例,说面建立该种函数的方法。示例如下:
CREATE OR REPLACE FUNCTION get_user RETURN VARCHAR2 IS v_user VARCHAR2(100); BEGIN SELECT username INTO v_user FROM user_users; RETURN v_user; END;
建立了函数get_user之后,就可以在应用程序中调用该函数了。因为函数有返回值,所以它只能作为表达式的一部分来调用。调用示例如下:
示例一:使用变量接收函数返回值
SQL> var v1 varchar2(100); SQL> exec :v1:=get_user; PL/SQL procedure successfully completed v1 --------- SCOTT SQL> print v1; v1 --------- SCOTT
示例二:在SQL语句中直接调用函数
SQL> select get_user from dual; GET_USER -------------------------------------------------------------------------------- SCOTT
示例三:使用包DBMS_OUTPUT调用函数
SQL> set serveroutput on; SQL> exec dbms_output.put_line('当前数据库用户:'||get_user); 当前数据库用户:SCOTT PL/SQL procedure successfully completed
2、建立函数:带有IN参数
当建立函数时,通过使用输入参数,可以将应用程序的数据传递到函数中,最终通过执行函数可以将结果返回到应用程序中。下面以建立返回雇员工资的函数为例,说明建立带有输入参数函数的方法。示例如下:
CREATE OR REPLACE FUNCTION get_sal(name IN VARCHAR2) RETURN NUMBER AS v_sal emp.sal%TYPE; BEGIN SELECT sal INTO v_sal FROM emp WHERE upper(ename)=upper(name); RETURN v_sal; EXCEPTION WHEN NO_DATA_FOUND THEN raise_application_error(-20000,'该雇员不存在'); END;
在建立了get_sal之后,就可以在应用程序中调用该函数了。调用该函数的示例如下:
示例一:输入存在的雇员
SQL> var sal NUMBER; SQL> exec :sal:=get_sal('scott'); PL/SQL procedure successfully completed sal --------- 1200 SQL> print sal; sal --------- 1200
示例二:输入不存在的雇员
SQL> exec :sal:=get_sal('mary'); begin :sal:=get_sal('mary'); end; ORA-20000: 该雇员不存在 ORA-06512: 在 "SCOTT.GET_SAL", line 11 ORA-06512: 在 line 2
3、建立函数:带有OUT参数
一般情况下,函数只需要返回单个数据。如果希望使用函数同同时返回多个数据,例如同时返回雇员名和工资那么就需要使用输出参数了。为了在函数中使用输出参数,必须要指定OUT参数模式。下面以建立用于返回雇员所在部门名和岗位的函数为例,说明建立带有OUT参数函数的方法。示例如下:
CREATE OR REPLACE FUNCTION get_info (name VARCHAR2,title OUT VARCHAR2) RETURN VARCHAR2 AS deptname dept.dname%TYPE; BEGIN SELECT a.job,b.dname INTO title,deptname FROM emp a,dept b WHERE a.deptno=b.deptno AND upper(a.ename)=upper(name); RETURN deptname; EXCEPTION WHEN NO_DATA_FOUND THEN raise_application_error(-20000,'该雇员不存在'); END;
当建立了函数get_info 后,就可以在应用程序中调用该函数了。注意,因为该函数,带有OUT参数,所以不能在SQL语句中,调用该函数,而必须要定义变量接收OUT参数和函数的返回值。在SQL*PLUS中调用函数get_info的示例如下:
SQL> var job varchar2(20) SQL> var dname varchar2(20) SQL> exec :dname:=get_info('scott',:job); PL/SQL procedure successfully completed dname --------- ANALYST job --------- ANALYST
如上所示:除了输出函数自定义返回的值,也返回了OUT参数的返回值。
4、建立IN OUT参数
建立函数时,不仅可以指定IN和OUT参数,也可以指定IN OUT参数。IN OUT 参数,也被成为输入输出参数。使用这种参数时,在调用函数之前需要通过变量给该种参数传递数据,在调用结束之后Oracle会将函数的部分结果通过该变量传递给应用程序。下面以计算两个数值相除的结果的函数Result为例,说明在函数中使用IN OUT参数的方法。示例如下:
CREATE OR REPLACE FUNCTION result_num (num1 NUMBER,num2 IN OUT NUMBER) RETURN NUMBER IS v_result NUMBER(6); v_remainder NUMBER; BEGIN v_result:=num1/num2; v_remainder:=MOD(num1,num2); num2:=v_remainder; RETURN v_result; EXCEPTION WHEN ZERO_DIVIDE THEN raise_application_error(-20000,'不能除0'); END;
注意,因为该函数带有IN OUT 参数,所以不能再SQL语句中调用该函数,而必须使用变量为IN OUT参数传递数值并接收数据。另外,还需要定义变量接收函数返回值。调用该函数的示例如下:
SQL> var result2 number; SQL> var result1 number; SQL> var result2 number; SQL> exec :result2:=30; PL/SQL procedure successfully completed result2 --------- 30 SQL> exec :result1:=result_num(100,:result2); PL/SQL procedure successfully completed result1 --------- 3 result2 --------- 10
上一篇: SQL查询超时的设置方法(关于timeout的处理)_MySQL
下一篇: mysql 怎么查询库名
推荐阅读
-
适合后台管理系统开发的12个前端框架(小结)
-
Oracle基础学习笔记
-
C++模板基础之函数模板与类模板实例详解
-
iTOP-4412开发板-QtE-智能网关测试使用文档
-
JavaScript实用库:Lodash源码数组函数解析(四)dropRight、dropWhile、dropRightWhile、baseWhile
-
何为IS 类函数?Excel IS 类函数介绍及其应用实例
-
函数入门知识Excel中的错误值解析可谓是函数基本功
-
Excel 单元格内容截取函数 left 的语法格式及应用实例介绍
-
Excel中利用RANK函数的联合引用特性进行多列数据统一排名
-
Excel公式的错误值认识及IF+IS函数的容错组合的使用