oracle数据库中functions简单的例子

1、工作当中遇到的

CREATE OR REPLACE FUNCTION "GETPKID" (pKName in varchar2) return Number is
  currID Number(11);
PRAGMA AUTONOMOUS_TRANSACTION;
begin
  SELECT keyValue INTO currID FROM gap_key WHERE keyName = pkName FOR UPDATE;

  UPDATE gap_key SET keyValue = keyValue + 1 WHERE keyName = pkName;
  commit;

  RETURN currID;
end getPKID;

2、使用方法

select getpkid('PK_ADPOSITION_PIC') from dual

3、 

SELECT IUPROGRAMAATTENTION(#programaAttentionCount# ,#programaId#) FROM DUAL

 

create or replace function IUPROGRAMAATTENTION(stepValue in number, pkValue in number) return Number is
  currID Number(11) := 0;
PRAGMA AUTONOMOUS_TRANSACTION;
begin
  BEGIN
  SELECT PROGRAMA_ATTENTION_COUNT INTO currID FROM PROGRAMA_ATTENTION_STATISTICS WHERE PROGRAMA_ID = pkValue;

  UPDATE PROGRAMA_ATTENTION_STATISTICS SET PROGRAMA_ATTENTION_COUNT = PROGRAMA_ATTENTION_COUNT + stepValue WHERE PROGRAMA_ID = pkValue;
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
  INSERT INTO PROGRAMA_ATTENTION_STATISTICS(PROGRAMA_ID, PROGRAMA_ATTENTION_COUNT) VALUES (pkValue, stepValue);
  END;
  
  commit;

  RETURN (currID + stepValue);
end IUPROGRAMAATTENTION;

 

4、详解如下（转载）

 

Oracle 自定义函数

函数用于返回特定数据。执行时得找一个变量接收函数的返回值;
语法如下:     create or replace function function_name
(
argu1 [mode1] datatype1,
argu2 [mode2] datatype2, ........
)
return datatype
is
begin

end;
执行 var v1 varchar2(100)
      exec :v1:=function_name
===============================================================================
不带任何参数
===============================================================================
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';