首页 > Oracle自定义函数

Oracle自定义函数

2019独角兽企业重金招聘Python工程师标准>>> hot3.png

Oracle自定义函数

用户定义函数是存储在数据库中的代码块，可以把值返回到调用程序。调用时如同系统函数一样，如max(value)函数，其中，value被称为参数。函数参数有3种类型。

IN 参数类型：表示输入给函数的参数。

OUT 参数类型：表示参数在函数中被赋值，可以传给函数调用程序。

IN OUT参数类型：表示参数既可以传值也可以被赋值。

１、语法格式：

SQL语法方式创建的语法格式为：

CREATE OR REPLACE FUNCTION function_name         /*函数名称*/

(

Parameter_name1,mode1 datatype1,　　　　　　　　　　　 /*参数定义部分*/

Parameter_name2,mode2 datatype2,

Parameter_name3,mode3 datatype3

…

)

RETURN return_datatype　　　　　　　　　　　　　　　　/*定义返回值类型*/

IS/AS

BEGIN

       Function_body　　　　　　　　　　　　　　　　　 /*函数体部分*/

      RETURN scalar_expression                        /*返回语句*/

END function_name;

  

说明：

function_name:：用户定义的函数名。函数名必须符合标示符的定义规则，对其所有者来说，该名在数据库中是唯一的。

parameter：用户定义的参数。用户可以定义一个或多个参数。

mode：参数类型。

datatype：用户定义参数的数据类型。

return_type:：用户返回值的数据类型。

函数返回scalar_expression表达式的值，function_body函数体由pl/sql语句构成。

２、示例

函数代码：

create or replace function T01001_count

return number

is

count_T01001 number;

begin

select count(*) into count_T01001 from T01001;

return(count_T01001);

end T01001_count;                  --记得一定要打分号



调用：

declare

i number;

begin

i:=T01001_count();

dbms_output.put_line(to_char(i));

end;　　　　　　　　　　　　　　　　 --记得一定要打分号

注意：

(1)    如果函数没有参数，那么函数名后不应该要括号；

(2)    创建函数的时候end后面一定要记得写函数名

--没有参数的函数    

create or replace function get_user return varchar2 is   

  v_user varchar2(50);    

begin   

  select username into v_user from user_users;    

  return v_user;    

end get_user;    

   

--测试    

方法一    

select get_user from dual;    

   

方法二    

SQL> var v_name varchar2(50)    

SQL> exec :v_name:=get_user;    

   

PL/SQL 过程已成功完成。    

   

SQL> print v_name    

   

V_NAME    

------------------------------    

TEST    

   

方法三    

SQL> exec dbms_output.put_line('当前数据库用户是：'||get_user);    

当前数据库用户是：TEST    

   

PL/SQL 过程已成功完成。   

--没有参数的函数

create or replace function get_user return varchar2 is

v_user varchar2(50);

begin

select username into v_user from user_users;

return v_user;

end get_user;

--测试

方法一

select get_user from dual;

方法二

SQL> var v_name varchar2(50)

SQL> exec :v_name:=get_user;

PL/SQL 过程已成功完成。

SQL> print v_name

V_NAME

------------------------------

TEST

方法三

SQL> exec dbms_output.put_line('当前数据库用户是：'||get_user);

当前数据库用户是：TEST

PL/SQL 过程已成功完成。

Sql代码 

--带有IN参数的函数    

create or replace function get_empname(v_id in number) return varchar2 as   

  v_name varchar2(50);    

begin   

  select name into v_name from employee where id = v_id;    

   return v_name;    

exception    

  when no_data_found then   

    raise_application_error(-20001, '你输入的ID无效！');    

end get_empname;   

--带有IN参数的函数

create or replace function get_empname(v_id in number) return varchar2 as

v_name varchar2(50);

begin

select name into v_name from employee where id = v_id;

return v_name;

exception

when no_data_found then

raise_application_error(-20001, '你输入的ID无效！');

end get_empname;

 

附：

函数调用限制

1、SQL语句中只能调用存储函数(服务器端),而不能调用客户端的函数

2、SQL只能调用带有输入参数,不能带有输出,输入输出函数

3、SQL不能使用PL/SQL的特有数据类型(boolean,table,record等)

4、SQL语句中调用的函数不能包含INSERT,UPDATE和DELETE语句

 

查看函数院源代码

oracle会将函数名及其源代码信息存放到数据字典中user_source 

select text from user_source where name='GET_EMPNAME';



删除函数

drop function get_empname





　　不带任何参数

　　create or replace function get_user return varchar2 is

　　Result varchar2(50);

　　begin

　　select username into Result from user_users;

　　return(Result);

　　end get_user;

　　执行:

　　带in参数的

　　create or replace function get_sal(empname in varchar2) return number is

　　Result number;

　　begin

　　select sal into Result from emp where ename=empname;

　　return(Result);

　　end get_sal;

　　执行: SQL> var sal number

　　SQL> exec :sal:=get_sal('scott');

　　带out参数的函数

　　create or replace function get_info(e_name varchar2,job out varchar2) return number is

　　Result number;

　　begin

　　select sal,job into Result,job from emp where ename=e_name;

　　return(Result);

　　end get_info;

　　执行: SQL> var job varchar2(20)

　　SQL> var dname varchar2(20)

　　SQL> exec :dname:=get_info('SCOTT',:job)

　　带in out参数的函数

　　create or replace function result(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 result;

　　执行: var result1 number;

　　var result2 number;

　　exec :result2:=30

　　exec :result1:=result(100,:result2)

　　eg:

　　1　、一个最简单的自定义函数Fun_test1的定义。

　　create or replace function Fun_test1(p_1 number)--Fun_test1是函数名，有一个输入参数p_1，是number型的。返回值也是number型的

　　return number

　　IS

　　begin

　　if p_1>0 then

　　return 1;

　　elsif p_1=0 then

　　return 0;

　　else

　　return -1;

　　end if;

　　end;

　　--这个函数只是可以知道自定义函数的定义和格式。其实没什么用途。

　　2、Fun_test1自定义函数的调用的存储过程Pro_Fun_test1_1示例：

　　create or replace procedure Pro_Fun_test1_1(

　　p1_in in number,

　　p2_out out number

　　)

　　AS

　　begin

　　p2_out:=Fun_test1(p1_in);

　　end Pro_Fun_test1_1;

　　--一个输入参数，一个输出参数

　　3、Fun_test1自定义函数的调用的存储过程Pro_Fun_test1_2示例：

　　create or replace procedure Pro_Fun_test1_2(

　　p1_in in number,

　　p2_out out number

　　)

　　AS

　　t_1 number;

　　begin

　　select Fun_test1(p1_in)+100 INTO p2_out

　　from bill_org where org_ID=1;

　　end Pro_Fun_test1_2;

　　--自定义函数的调用方法和Oracle的其它内部函数是一样的。

　　二、包的定义和使用入门

　　包一般是过程和函数的集合,对过程和函数进行更好的封装,一般不针对字段。

　　包的构成包括包头和包体。

　　1、包头的定义：

　　包头仅仅只是对包中的方法进行说明,而没有实现

　　语法：

　　create or replace package myPackage_1

　　is

　　procedure syaHello(vname varchar2);--申明了该包中的一个过程

　　end;

　　2、包体的定义：

　　包体是对包头中定义的过程、函数的具体实现。

　　create or replace package body myPackage_1

　　is

　　procedure syaHello(vname varchar2)--对包中定义的过程的实现

　　is

　　begin

　　dbms_output.put_line('Hello '||vname);

　　end;

　　end;

　　要注意的是：

　　create or replace package后面的名称必须和create or replace package body后面的名称一致，

　　如果将create or replace package body后面的名称改为，'MYPACKAGE'

　　否则将会出现诸如下面的错误：

　　必须说明标识符 'MYPACKAGE'

　　3、调用包用的自定义方法：

　　create or replace procedure Pro_test_package(

　　p1_in string

　　)

　　AS

　　begin

　　myPackage_1.syaHello(p1_in);

　　end Pro_test_package;

　　eg2:

　　--没有参数的函数

　　create or replace function get_user return varchar2 is v_user varchar2(50);

　　begin

　　select username into v_user from user_users;

　　return v_user;

　　return v_user;

　　--测试

　　方法一

　　select get_user from dual;

　　方法二

　　SQL> var v_name varchar2(50)

　　SQL> exec :v_name:=get_user;

　　--带有IN参数的函数

　　create or replace function get_empname(v_id in number) return varchar2 as v_name varchar2(50);

　　begin

　　select name into v_name from employee where id = v_id;

　　return v_name;

　　exception

　　when no_data_found then raise_application_error(-20001, '你输入的ID无效!');

　　end get_empname;

　　附：

　　函数调用限制

　　1、SQL语句中只能调用存储函数(服务器端),而不能调用客户端的函数

　　2、SQL只能调用带有输入参数,不能带有输出,输入输出函数

　　3、SQL不能使用PL/SQL的特有数据类型(boolean,table,record等)

　　4、SQL语句中调用的函数不能包含INSERT,UPDATE和DELETE语句

　　查看函数院源代码

　　oracle会将函数名及其源代码信息存放到数据字典中user_source

　　select text from user_source where name='GET_EMPNAME';

　　删除函数

　　drop function get_empname;

　　判断任务过期时间：

　　create or replace function GetUrgentState(m_TaskID varchar2,

　　m_SendTime date,

　　m_flag varchar2)

　　return varchar2 IS

　　myDate date;

　　ExpireTime date;

　　strsql varchar2(200);

　　begin

　　myDate := m_SendTime;

　　strsql := 'select max(EXPIRETIME) from t_wf_supervise where TASKID =''' ||

　　m_TaskID || '''';

　　execute immediate strsql

　　into ExpireTime;

　　--没有到期时间 就是正常状态

　　if ExpireTime is null then

　　if m_flag = 'String' then

　　return '正常';

　　end if;

　　if m_flag = 'Img' then

　　return 'cb_execute.gif';

　　end if;

　　end if;

　　--未发送任务，就是判断当前时间

　　if m_SendTime is null then

　　myDate := sysdate;

　　end if;

　　if ExpireTime < myDate then

　　if m_flag = 'String' then

　　return '超期';

　　end if;

　　if m_flag = 'Img' then

　　return 'cb_limit.gif';

　　end if;

　　end if;

　　--小于3天的任务预警

　　if ExpireTime - myDate < 3 then

　　if m_flag = 'String' then

　　return '预警';

　　end if;

　　if m_flag = 'Img' then

　　return 'cb_warning.gif';

　　end if;

　　else

　　if m_flag = 'String' then

　　return '正常';

　　end if;

　　if m_flag = 'Img' then

　　return 'cb_execute.gif';

　　end if;

　　end if;

　　end;

　　查询其它表数据：

　　create or replace function GetPreNode(m_PreTaskID varchar2) return varchar2 IS

　　nodename varchar2(50);

　　strsql varchar2(200);

　　begin

　　if m_PreTaskID is null then

　　return '';

　　end if;

　　strsql := 'select max(nodename) from t_Wf_Tasklist where TaskID =''' ||

　　m_PreTaskID|| '''';

　　execute immediate strsql

　　into nodename;

　　return nodename;

　　end;

　　格式化标题输出：

　　create or replace function FormatTitle(m_title varchar2,

　　m_length number,

　　m_FillChar varchar2) return varchar2 IS

　　begin

　　if lengthb(m_title) > m_length*2 then

　　return substr(m_title, 0,m_length) || m_FillChar;

　　else

　　return m_title;

　　end if;

　　end;

转载于:https://my.oschina.net/duanyunhu/blog/130703

更多相关：

  • excel高级函数_PowerQuery 进阶之 M 函数学习

    草色新雨中， 松声晚窗里。之前我们学习 Power Query 都是用鼠标就完成了很多复杂的操作。虽然 PowerQuery 已经将大部分常用功能内置成到功能区。基本能完成我们大部分的报表自动化功能。但是总有些复杂的或者个性化的问题是开发团队没有预先想到的，这时我们就需要学习 M 语言。一、M 语言在哪里？M语言的函数公式有三个地...

  • python从0开始学编程课件_小白从零开始学编程(三)--python基本概念

    前言从2020年3月份开始，计划写一系列文档--《小白从零开始学编程》，记录自己从0开始学习的一些东西。第一个系列：python，计划从安装、环境搭建、基本语法、到利用Django和Flask两个当前最热的web框架完成一个小的项目第二个系列：可能会选择Go语言，也可能会选择Vue.js。具体情况待定，拭目以待吧。。。基本概念表达式表...

  • python 函数递归一次增加一次变量_python3--函数(函数，全局变量和局部变量，递归函数)...

    1.1函数1.1.1什么是函数函数就是程序实现模块化的基本单元，一般实现某一功能的集合。函数名：就相当于是程序代码集合的名称参数：就是函数运算时需要参与运算的值被称作为参数函数体：程序的某个功能，进行一系列的逻辑运算return 返回值：函数的返回值能表示函数的运行结果或运行状态。1.1.2函数的作用函数是组织好的，可重复使用的，用来...

  • python深度神经网络量化_基于Python建立深度神经网络！你学会了嘛？

    原标题：基于Python建立深度神经网络！你学会了嘛？图1 神经网络构造的例子(符号说明：上标[l]表示与第l层；上标(i)表示第i个例子；下标i表示矢量第i项)单层神经网络图2 单层神经网络示例神经元模型是先计算一个线性函数(z=Wx+b)，接着再计算一个激活函数。一般来说，神经元模型的输出值是a=g(Wx+b)，其中g是激活函数(...

  • mysql 字段加减_MySQL中你必须了解的函数

    在学习MySQL的时候你会发现，它有非常多的函数，在学习的时候没有侧重。小编刚开始学习的时候也会有这个感觉。不过，经过一段时间的学习之后，小编发现尽管函数有很多，但是常用的却只有那几个。今天小编就把常用的函数汇总一下，为大家能够能好的学习MySQL中的函数。MySQL常使用的函数大概有四类。时间函数、数学函数、字符函数、控制函数。让我...

  • 判断屏幕宽高比是否为16:9

    /*判断屏幕宽高比是否为16:9*/ function isScreen16to9() {return window.screen.height / window.screen.width === 9 / 16; }...

  • 关闭、刷新、跳转、离开当前网页前提示

    /*关闭、刷新、跳转、离开当前网页前提示*/ onbeforeunload = function () {return false; };  ...

  • JavaScript原生代码处理JSON的一些高频次方法合集

    let json = {/**判断JSON格式*/ isJSON: function (str) {if (typeof str == "string") {try {var obj = JSON.parse(str);if (typeof obj == "object" && obj) {return true;} else {...

  • Node.js（nodejs）对本地JSON文件进行增、删、改、查操作（轻车熟路）

      项目结构   index.js //必须要安装否则就别想运行了❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤❤ //npm i body-parser -D & cnpm i express & cnpm i node-xlsx & cnp...

  • day16 递归函数

    一、递归 函数  　　为什么要有函数，提高代码的可读性，避免重复的代码，提高代码的复用性      在函数中能用return的不要print 1、递归的最大深度997 def foo(n):print(n)n+=1foo(n) foo(1) 递归的最大深度 2、修改递归的最大深度     由此我们可以看出，未报错之前能看到的最大数...

  • iOS----------计算一段代码执行时间

    CFAbsoluteTime start = CFAbsoluteTimeGetCurrent(); //在这写入要计算时间的代码 // do something CFAbsoluteTime end = CFAbsoluteTimeGetCurrent(); NSLog(@"%f", end - start); 转载于:ht...

  • LeetCode 228: Summary Ranges

    Given a sorted integer array without duplicates, return the summary of its ranges. For example, given [0,1,2,4,5,7], return ["0->2","4->5","7"]. 代码要求对数组中的元素进行分段。 首先给...

  • bat+sqlcmd 批量执行脚本

    Hello,此BAT脚本能够帮助开发者将某目录下全部SQL脚本按文件名称依次在指定数据库中批量执行。不用忍受powershell invoke-sqlcmd 的笨重。在指执行时多一种选择。 bat文件 @echo off @REM ******** ******** General Batch for Starting SQL...

  • SSL 1460——最小代价问题

    Description 设有一个n×m(小于100)的方格（如图所示），在方格中去掉某些点，方格中的数字代表距离（为小于100的数，如果为0表示去掉的点），试找出一条从A(左上角)到B（右下角）的路径，经过的距离和为最小（此时称为最小代价），从A出发的方向只能向右，或者向下。 Sample Input 4 4 4 10 7 0...

  • 得到windows聚焦图片（windows 10）

    有些Windows聚焦图片确实很漂亮，很希望保留下来，但是Windows聚焦图片总更好，网上有得到聚焦图片的方法，每次都手动去弄真麻烦，于是自己编了一个小程序，自动得到Windows聚焦图片，下面是运行这个小程序得到Windows聚焦图片的效果！ 小工具以及源码下载：http://download.csdn.net/detail/su...

©2024 11GX.COM