ORACLE存储过程

ORACLE提供了四种类型的可存储的程序：
函数, 过程. 包,触发器
一．声明部分(Declarationsection)
（1）声明部分包含了变量和常量的数据类型和初始值
（2）这个部分是由关键字DECLARE开始
（3）如果不需要声明变量或常量，那么可以忽略这一部分；
二．执行部分(Executablesection)
（1）执行部分是PL/SQL块中的指令部分，
（2）由关键字BEGIN开始，所有的可执行语句都放在这一部分，其他的PL/SQL块也可以放在这一部分。

三．异常处理部分(Exceptionsection)
这一部分是可选的，在这一部分中处理异常或错误。

过程存储过程是一个PL/SQL程序块，接受零个或多个参数作为输入(INPUT)或输出(OUTPUT)、或既作输入又作输出(INOUT)，与函数不同，存储过程没有返回值，存储过程不能由SQL语句直接使用，只能通过EXECUT命令或PL/SQL程序块内部调用。

1.创建存储过程
CREATE[ORREPLACE]PROCEDURE过程名
(参数1{IN/OUT/INOUT}类型,
参数2{IN/OUT/INOUT}类型,
…….
参数N{IN/OUT/INOUT}类型,
)IS/AS
过程体
BEGIN
END存储过程名字
说明：
（1）ORREPLACE关键字可选，但一般会使用，功能为如果同名的过程已存在，则删除同名过程，然后重建，以此来实现修改过程的目的。

（2）过程可以包括多个参数，参数模式有IN/OUT/INOUT三种，默认为IN,也可以没参数。

（3）IS/AS键字也等价
（4）过程体为该过程的代码部分,是一个含有声明部分，执行部分和异常处理部分的PL/SQL块。

但需要注意的是，在过程的声明体中不能使用DECLARE关键字，由IS或AS来代替。

注意事项：
1，存储过程参数不带取值范围，in表示传入，out表示输出
类型可以使用任意Oracle中的合法类型。

2，变量带取值范围，后面接分号
3，在判断语句前最好先用count（*）函数判断是否存在该条操作记录
4，用select。

into。

给变量赋值
5，在代码中抛异常用raise+异常名
2.查看过程
过程创建成功后，既说明编译已经成功，并把它作为一个ORACLE对象存储在数据库中，使用user_source视图查看过程的原程序代码信息，使用user_source可以查询到该数据库对象。

例：下面是VIEW_STU过程的源代码
SQL>DESC user_source
名称是否为空类型
NAMEVARCHAR2(30)
TYPE VARCHAR2(12)
LINE NUMBER
TEXTVARCHAR2(4000)
SQL>SELECT text from user_source where name=”VIEW_STU”
过程作为数据库对象，也可以用DESC命令列出关于过程结构的详细信息。

如下“SQL>CREATE OR REPLACE PROCEDURE test1
(p1INNUMBER,
P2 OUT NYMBER,
P3 OUT DATE)
AS
过程体
SQL>DESC test1
显示结果如下
PROCEDURE test1
参数名称类型输入/输出默认值
P1 NUMBER IN
P2 NUMBEROUT
P3 DDATEOUT
3.调用过程
一旦过程创建成功后，就可以在任何一个PL/SQL程序块中通过过程名直接调用
BEGIN
Proc_name(p1,p2…)
END
4.删除过程
DROP PROCEDURE
参数和模式
一，上面我们创建了DeptMesg过程，并且可以在以下PL/SQL块中调用它：DECLARE
e_name emp.ename%type:=’SMITH’;
BEGIN
DEPTMESG(e_name);
END;
（1）从上面块中声明的变量e_name作为参数传递给DEPTMESG,称为实际参数。

DEPTMESG过程中的p_ename就是形式参数
（2）实际参数包含了在调用时传递给该过程的数值，同时他们也会接受在返回时过程的处理结果。

（3）形式参数只是实参的占位符，过程调用时，实参传递数值给形参，形参被赋予实参的取值
（4）返回过程时，实参被赋予形参的取值。

二．参数模式决定了形参的行为，PL/SQL块中参数模式有IN/ OUT/ IN OUT三种，默认为IN
（1）IN模式参数，输入参数，用于向过程传入一个值
（2）OUT模式参数，输出参数，用于从被调过程中返回一个值
（3）IN/OUT模式参数，用于向过程传入一个初始值，返回更新后的值。

参数规则
如果形式参数是IN模式的参数，实际参数可以是一个具体的值或一个有值的变量；
如果形式参数是OUT模式的参数，实际参数必须是一个变量，当调用过程后，此变量就被赋值了。

可以输出此变量的值来测试过程执行的结果。

如果形式参数是IN OUT模式的参数，则实际参数必须是一个预
先已经赋值的变量。

执行完过程后，该变量被重新赋值，可以输出此变量的值来测试过程执行结果
2.select INTO STATEMENT
将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条记录，否则抛出异常(如果没有记录抛出NO_DATA_FOUND)
例子：
BEGIN
select col1,col2 into 变量1,变量2 FROM typestruct where xxx; EXCEPTION
WHEN NO_DATA_FOUND THEN
xxxx;
END;
...
CREATEORREPLACEPROCEDURE存储过程名
(
--定义参数
is_ymINCHAR(6),
the_countOUTNUMBER,
)
AS
--
PL/SQL块语法
[DECLARE]---declarationstatementsBEGIN---executablestatements[EXCEPTION]---exceptionstatementsEND
每一个PL/SQL块由BEGIN或DECLARE开始，以END结束。

注释由--标示。

PL/SQL块中的每一条语句都必须以分号结束，SQL语句可以使
多行的，但分号表示该语句的结束。

一行中可以有多条SQL语句，他们之间以分号分隔。

变量和常量
变量存放在内存中以获得值，能被PL/SQL块引用。

你可以把变量想象成一个可储藏东西的容器，容器内的东西是可以改变的。

声明变量
变量一般都在PL/SQL块的声明部分声明，PL/SQL是一种强壮的类型语言，这就是说在引用变量前必须首先声明，要在执行或异常处理部分使用变量，那么变量必须首先在声明部分进行声明。

声明变量的语法如下：
Variable_name[CONSTANT]databyte[NOTNULL][:=|DEFAULTexpression]
注意:可以在声明变量的同时给变量强制性的加上NOTNULL约束条件，此时变量在初始化时必须赋值。

给变量赋值
给变量赋值有两种方式：.直接给变量赋值X:=200; Y=Y+(X*20);.通过SQLSELECTINTO或FETCHINTO给变量赋值
SELECTSUM(SALARY),SUM(SALARY*0.1)INTOTOTAL_SALAR
Y,TATAL_COMMISSIONFROMEMPLOYEEWHEREDEPT=10;
5.变量赋值
V_TEST:=123;
常量
常量与变量相似，但常量的值在程序内部不能改变，常量的值在定义时赋予，，他的声明方式与变量相似，但必须包括关键字CONSTANT。

常量和变量都可被定义为SQL和用户定义的数据类型。

这个语句定了一个名叫ZERO_VALUE、数据类型是NUMBER、值为0的常量。