oracle数据库存储函数,Oracle数据库存储过程

一、定义

所谓存储过程，就是一组用于完成特定数据库功能的SQL语句集，该SQL语句集经过编译后存储在数据库系统中。使用的时候，用户通过指定已经定义的存储过程名字并给相应的存储过程参数来调用并执行它，从而完成一个或者一系列的数据库操作。

存储过程中可以包含逻辑控制语句和数据操纵语句，它可以接受参数、输出参数、返回单个或多个结果集以及返回值。

存储过程和函数也是一种pl/sql块，是存入数据库的pl/sql块。但存储过程和函数不同于普通的pl/sql程序块。我们通常把pl/sql程序成为无名块，而存储过程和函数是以命名的方式存储于数据库中的。和pl/sql程序相比，存储过程有很多优点：

存储过程和函数以命名的数据库对象形式存储于数据库当中。存储在数据库中的优点是很明显的。因为代码不保存在本地，用户可以在任何客户机上登录到数据库，并调用或修改代码。

存储过程和函数可由数据库提供安全保证，要想使用存储过程和函数，需要有存储过程和函数的所有者的授权。只有被授权的用户或者创建者本身才能执行存储过程或调用函数。

二、基本语法

CREATE [OR REPLACE] PROCEDURE 存储过程名[(参数[IN|OUT|IN OUT] 数据类型...)]

{AS|IS}

[说明部分]

BEGIN

可执行部分

[EXCEPTION

错误处理部分]

END [过程名];

1、可选关键字OR REPLACE 表示如果存储过程已经存在，则用新的存储过程覆盖，通常用户存储过程的重建。

2、参数部分用于定义多个参数(如果没有参数，就可以省略)。参数有三种形式：IN、OUT和IN OUT。如果没有指明参数的形式，则默认为IN。

3、关键字AS也可以写成IS，后跟过程的说明部分，可以在此定义过程的局部变量。

4、编写存储过程可以使用任何文本编辑器或直接在SQLPLus环境下进行，编写好的存储过程必须要在SQLPLus环境下进行编译，生成编译代码，原代码和编译代码在编译过程中都会被存入数据库。编译成功的存储过程就可以在ORacle环境下进行调用了。

5、一个存储过程在不需要时可以删除。删除存储过程的人是过程的创建者或者拥有DROP ANY PROCEDURE系统权限的人。删除存储过程的语法如下：

DROP PROCEDURE 存储过程名；

6、如果要重新编译一个存储过程，则只能是过程的创建者或者拥有ALTER ANY PROCEDURE系统权限的人。语法如下：

ALTER PROCEDURE 存储过程名 COMPILE；

7、执行(或调用)存储过程的人是过程的创建者或是拥有EXECUTE ANY PROCEDURE系统权限的人，或是被拥有者授予EXECUTE 权限的人。执行的方法如下：

方法1：

EXECUTE 模式名.存储过程名[(参数...)];

方法2：

BEGIN

模式名.存储过程名[(参数...)];

END;

8、传递的参数必须与定义的参数类型、个数和顺序一致(如果参数定义了默认值，则调用时可以省略参数)。参数可以是变量、常量或表达式。

9、as和is的区别：在存储过程(procedure)和函数(function)中两者都可以使用，但是使用IS将无法使用调试模式调试该存储函数；在视图中只能用AS不能用IS，在游标(CURSOR)中只能用IS不能用AS。

10、oracle中的decode函数

含义解释：

decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)

该函数的含义如下：

IF 条件=值1 THEN

RETURN(翻译值1)

ELSIF 条件=值2 THEN

RETURN(翻译值2)

......

ELSIF 条件=值n THEN

RETURN(翻译值n)

ELSE

RETURN(缺省值)

END IF

decode(字段或字段的运算，值1，值2，值3)

这个函数运行的结果是，当字段或字段的运算的值等于值1时，该函数返回值2，否则返回值3

当然值1，值2，值3也可以是表达式，这个函数使得某些sql语句简单了许多

示例：

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

sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1

例如：

变量1=10，变量2=20

则sign(变量1-变量2)返回-1，decode解码结果为“变量1”，达到了取较小值的目的。

11、nvl函数

NVL函数的格式如下：NVL(expr1,expr2)

含义是：如果oracle第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第一个参数本来的值。

NVL2函数的格式如下：NVL2(expr1,expr2, expr3)

含义是：如果该函数的第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第三个参数的值。

NULLIF(exp1,expr2)函数的作用是如果exp1和exp2相等则返回空(NULL)，否则返回第一个值。

Coalese函数的作用是的NVL的函数有点相似，其优势是有更多的选项。

格式如下：

Coalesce(expr1, expr2, expr3….. exprn)

表示可以指定多个表达式的占位符。所有表达式必须是相同类型，或者可以隐性转换为相同的类型。

返回表达式中第一个非空表达式，如有以下语句： 　　SELECT COALESCE(NULL,NULL,3,4,5) FROM dual 　　其返回结果为：3

如果所有自变量均为 NULL，则 COALESCE 返回 NULL 值

12、max函数

对字符型数据的最大值，是按照首字母由A～Z的顺序排列，越往后，其值越大。当然，对于汉字则是按照其全拼拼音排列的，若首字符相同，则比较下一个字符，以此类推。

14、untion all

union all，解释为联合所有。

Union解释为联合。

union或者Union all实现把前后两个select集合的数据联合起来，组成一个结果集查询输出。这就要求联合前后的结果集，需要分别有相同的输出字段的数目，并且对应的字段类型要相同。

SELECT column1, column2 from table1 union (all) select column1, column2 from table2

以上语句要求量表的column1字段类型相同，column2类型相同。而且每个查询的数目都是一样的。UNION ALL和UNION的差别就在ALL上面，第一个叫联合所有，说明会显示前后两个查询所有的数据，而UNION没有ALL(所有)这个单词，实现将前后两个查询的数据联合到一起后，去掉重复的数据显示