Oracle自定义函数相关小结

1、为什么用函数

用户定义函数是存储在数据库中的代码块，可以把值返回到调用程序。

和存储过程的区别：

• 作用：函数一般情况下是用来计算并返回一个计算结果；而 存储过程一般是用来完成特定的数据操作（比如修改、插入数据库表或执行某些DDL语句等等）

• 返回值：存储过程程序头部声明时不需描述返回类型，可以用out/in out参数返回零个或多个值；函数程序头部声明时要描述返回类型，而且PL/SQL块中至少要包含一个有效的return语句，通过return语句返回一个值，也可以是通过out类型参数带出的变量。

• 调用：sql语句(DML、SELECT)中不可以调用存储过程，可以调用函数；存储过程可作为一个独立的PL/SQL语句来执行，函数不能独立执行，必须作为表达式的一部分调用。

2、创建函数

语法如下: create or replace function function_name(

　　argu1 [mode1] datatype1,

　　argu2 [mode2] datatype2, ........)

　　return datatype　　is

  变量声明语句

　　begin

  function_body语句

　　end;

　　执行 var v1 varchar2(100)

　　exec :v1:=function_name

注：参数模式 有 in/out/in out

3、常用语法

• 赋值： var v := 10;

• 比较： if(a = 10 )

• 条件语句： 多个if时 if a=.. then .. elsif a=.. then .. end if;

  • decode 函数：

    DECODE(value,if1,then1,if2,then2,if3,then3,...,else)

    例子：select decode(sign(变量1-变量2),-1,变量1,变量2) from dual; --取较小值

  • case when

  • case 
    
    when a='1'  then 'xxxx'
    
    when a='2' then 'ssss'
    
    else 'zzzzz'
    
    end as

• 字符串拼接：CONCAT(str1, str2) 函数或 'str1'||v_str2||'str3'

如果字符串中出现 单引号时 要用两个单引号，比如 'I''m a coder'
双引号的作用是：假如建立对象的时候，对象名、字段名加双引号，则示意 Oracle将严格区分大小写，否则Oracl都默认大写。

• union与union all的区别

  union all是直接连接，取到得是所有值，记录可能有重复 union 是取唯一值，记录没有重复 
  1、UNION 的语法如下： [SQL 语句 1] UNION [SQL 语句 2] 
  2、UNION ALL 的语法如下： [SQL 语句 1] UNION ALL [SQL 语句 2];
  效率：UNION ALL 要比UNION快

4、自定义函数返回表类型

  oracle中的函数可以返回表类型。但是，这个表类型实际上是集合类型（与数组类似）。这个类型不能直接作为 from 的宾语。这个函数中，通过 pipe row () 语句来送出要返回的表中的每一行。调用这个函数的时候，通过 table() 关键字把管道流仿真为一个数据集 
如：

 create or replace function fun1 return table_type1 pipelined as    
 v row_type1;    
 begin    
    for myrow in (select k, v from tb1) loop    
       v := row_type1(myrow.k, myrow.v);    
       pipe row (v);    
    end loop;        
 end; 
 调用： select * from table(fun1);

5、Oracle PLSQL的集合类型

• 使用条件：

  • 单行单列的数据，使用标量变量

  • 单行多列数据，使用记录Record,可以看做是一种用户自定义数据类型。组成类似于多维数组。

  • 单列多行数据，使用集合.类似于编程语言中数组
    pl/sql集合类型包括关联数组Associative array（索引表 pl/sql table）、嵌套表（Nested Table）、变长数组（VARRAY）。

• 三种集合类型区别:

  • Nested table与VARRY既可以被用于PL/SQL，也可以被直接用于数据库中，但是Associative array不行，也就是说，Associative array是不能通过CREATE TYPE语句进行单独创建，只能在PL/SQL块(或Package)中进行定义并使用(即适用范围是PL/SQL Block级别)，而Nested table与VARRAY则可以使用CREATE TYPE进行创建(即适用范围是Schema级别)，它们还可以直接作为数据库表中列的类型。

  • nested tables 和 VARRAYs 的区别在于，VARRAY 和其它数据一起存于表内，而 nested table 存于表外，VARRAY 适合 "small" arrays，nested table 适合 "large" arrays。

转载于:https://my.oschina.net/u/2321402/blog/471555