Oracle中自定义函数与动态执行SQL

        Oracle已经提供了许多可用函数，但是在特定需求上，我们还是需要设计满足要求的自定义函数。得空收集各类资料整理，还需多多使用加以熟悉。

一、函数语法

Create [or replace] function funtionName(parameter1 mode1 dataType1,parameter2 mode2 dataType2,….) 
Return returnDataType
Is/as
--定义使用变量、返回变量;
Begin
  Function_body;
  Return expression;
End functionName;--结束函数的声明，也可以直接写end不加函数名。

说明：
function_name：用户定义的函数名。函数名必须符合标示符的定义规则，对其所有者来说，该名在数据库中是唯一的。
parameter：用户定义的参数。用户可以定义一个或多个参数。
mode：参数类型。参数的模式有3种:(如果没有注明, 参数默认的类型为 in),in: 为只读模式, 在函数中, 参数的值只能被引用, 不能被改变;out: 为只写模式, 只能被赋值, 不能被引用;in out:  可读可写。
dataType：用户定义参数的数据类型。
returnDataType：表示返回值类型。
Function_body：函数主体由pl/sql语句构成。
expression：函数返回expression表达式的值。

注意：函数可以没有输入输出参数，但一定要有返回（return）的数据类型，因此必须有内部变量存储return的数据；

二、实例

根据输入的数字，返回两个数的和，若除数为0，则抛出自定义异常。

create or replace function testdivision(js1 in number, js2 in number)
  return number is
  v_re number;
  customize_exp EXCEPTION; --自定义异常
begin
  if js2 = 0 then
    raise customize_exp;
    --raise_application_error(-20006,'不能除0');
  else
    v_re := js1 / js2;
    return v_re;
  end if;
exception
  --捕获异常
  when customize_exp then
    dbms_output.put_line('customize error!'); --打印参数
    raise_application_error(-20006, '不能除0'); --异常提示
  when others then
    return 0;
end;

--删除函数
DROP FUNCTION testdivision;
--查看当前用户无效函数
SELECT object_name FROM user_objects WHERE status='INVALID' AND object_type='FUNCTION';
--查看函数代码
SELECT * FROM user_source WHERE name=upper('testdivision');

三、执行动态SQL

       在一般的sql操作中，sql语句基本上都是固定的，如：SELECT t.empno,t.ename  FROM scott.emp t WHERE t.deptno = 20;但有的时候，从应用的需要或程序的编写出发，都可能需要用到动态SQl，如：当 from 后的表 不确定时，或者where 后的条件不确定时，都需要用到动态SQL。例如根据表名查询记录数：

create or replace function count_rows(table_name in varchar2,owner in varchar2 default null)
return number authid current_user IS
  num_rows number;
  stmt varchar2(2000);
begin
  --获取某表的记录条数
  if owner is null then
    stmt := 'select count(*) from "' || table_name || '"';
  else
    stmt := 'select count(*) from "' || owner || '"."' || table_name || '"';
  end if;
  execute immediate stmt
    into num_rows;
  return num_rows;
exception
  when others then
    return 0;
end;

执行动态语句的话需要显式的授权，加上AUTHID CURRENT_USER。
AUTHID DEFINER （定义者权限）：指编译存储对象的所有者。也是默认权限模式。
AUTHID CURRENT_USER（调用者权限）：指拥有当前会话权限的模式，这可能和当前登录用户相同或不同(alter session set current_schema 可以改变调用者Schema)。

EXECUTE IMMEDIATE用法例子：

1. 给动态语句传值(USING 子句)

 declare
  l_depnam varchar2(20) := 'testing';
  l_loc    varchar2(10) := 'Dubai';
  begin
  execute immediate 'insert into dept values  (:1, :2, :3)'
    using 50, l_depnam, l_loc;
  commit;
 end;

2. 从动态语句检索值(INTO子句)

 declare
  l_cnt    varchar2(20);
 begin
  execute immediate 'select count(1) from emp'
    into l_cnt;
  dbms_output.put_line(l_cnt);
 end;

3. 动态调用例程.例程中用到的绑定变量参数必须指定参数类型.黓认为IN类型,其它类型必须显式指定

 declare
  l_routin   varchar2(100) := 'gen2161.get_rowcnt';
  l_tblnam   varchar2(20) := 'emp';
  l_cnt      number;
  l_status   varchar2(200);
 begin
  execute immediate 'begin ' || l_routin || '(:2, :3, :4); end;'
    using in l_tblnam, out l_cnt, in out l_status;

  if l_status != 'OK' then
     dbms_output.put_line('error');
  end if;
 end;

4. 将返回值传递到PL/SQL记录类型;同样也可用%rowtype变量

 declare
    type empdtlrec is record (empno  number(4),ename  varchar2(20),deptno  number(2));
    empdtl empdtlrec;
 begin
    execute immediate 'select empno, ename, deptno '||'from emp where empno = 7934'
    into empdtl;
 end;

5. 传递并检索值.INTO子句用在USING子句前

 declare
   l_dept    pls_integer := 20;
   l_nam     varchar2(20);
   l_loc     varchar2(20);
 begin
    execute immediate 'select dname, loc from dept where deptno = :1'
       into l_nam, l_loc
       using l_dept ;
 end;

6. 含有IN查询条件的动态语句

execute immediate 'select count(*) from "' || owner || '"."' || table_name || '"  where name in(''张三'',''李四'',''王五'')'
    into num_rows;

在Oracle字符串('....')中使用''(两个连续的单引号'')可表示为单引号'

select '''' from dual;
结果：'