【Oracle】函数

函数的定义

函数又称为存储函数、存储结构，用户定义的函数可以被SQL语句或PL/SQL程序直接调用。函数与存储过程的不同之处在于函数有一个显式返回值，而存储过程只能依靠OUT或IN OUT返回数据。

函数的创建和调用

语法：

CREATE [OR REPLACE] FOUCTION 函数名称 [(参数名称 参数类型,...)]
RETURN 返回值类型
AS|IS
 声明部分；
BEGIN
 程序部分；
 [RETURN 返回值；]
[EXCEPTION 
  异常处理；]
END [函数名]；

• RETURN语句用于指明函数返回值的类型，但不能约束返回值的长度、精度等。
• 在BEGIN函数体中，必须至少包含一个RETURN语句指明函数返回值。

函数可以在SQL语句的以下部分调用：

• SELECT语句的目标列。
• 在BEGIN函数体中，必须至少包含一个RETURN语句指明函数返回值。

–创建无参函数

create or replace function func_hello
 return varchar2
as
begin
  return 'hello function';
end;

–创建有参函数

create or replace function func_get_name(v_userid tb_users.user_id%type)
return varchar2
as
 v_username tb_users.username%type;
begin
  select username into v_username from tb_users where user_id=v_userid;
  return v_username;
end;

–无参函数的调用

select func_hello from dual; --在sql语句中的调用

– 在pl/sql语句块中的调用

declare
 v_info varchar2(100);
begin
  v_info:=func_hello;
  dbms_output.put_line('返回：'||v_info);
end;

–有参函数的调用

select func_get_name(1) from dual;--在select语句中的调用

–在PL/SQL语句块中的调用

declare
 v_no number;
 v_info varchar2(50);
begin
  v_no:=&no;
  v_info:=func_get_name(v_no);
  dbms_output.put_line('人员名称：'||v_info);
end;

–oracle12c中新增函数定义，低于12c的版本无法使用

–使用WITH子句在SQL中定义及调用函数

WITH FUNCTON fun_isnumber(pram in varchar2)
return varchar2
is
begin
  if(to_number(parm) is not null) then
   return 'y';
  else
    return 'n';
  end if;
 exception
   when others then
     return 'n';
end;

select fun_isnumber('abcd') from dual;

–WITN函数的使用

with function fun_substr(
 string_in in varchar2,
 start_in in pls_integer
 end_in in pls_integer
)
return varchar2
is
begin
  return(substr(string_in,start_in,end_in-string_in+1))
end;

select ename,fun_substr(ename,1,3) from scott.emp;

函数的管理

1. 查看函数及其源代码

select name,text from user_source where type='FUNCTION';

2. 函数的修改
   CREATE OR REPLACE FUNCTION语句既可以新创建函数，也可以重新创建并覆盖原有函数，同时不需要重新设置该函数的权限分配。

3. 函数重新编译
   使用ALTER FUNCTION…COMPILE语句可以重新编译函数

ALTER FUNCTION func_get_dname compile;
--函数已更改

4. 删除函数
   使用DROP FUNCTION语句可以删除函数。

DROP FUNCTION func_hello;