oracle存储过程笔记

最近在写oracle的存储过程，在写得过程中遇到了很多的状况，在此写下笔记以备察看。
基础语法部分：
1.定义存储过程：
CREATE OR REPLACE PROCEDURE 存储过程名字
(
参数1 IN NUMBER,
参数2 IN NUMBER
) IS
变量1 INTEGER :=0;
变量2 DATE DEFAULT null;
BEGIN

END 存储过程名字

2.SELECT INTO STATEMENT
将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条
记录，否则抛出异常(如果没有记录抛出NO_DATA_FOUND)
例子：
SELECT col1,col2 into 变量1,变量2 FROM typestruct where xxx;
3.IF 判断
IF V_TEST=1 THEN
BEGIN
do something
END;
END IF;
或者
IF V_TEST =1 THEN
BEGIN
--DO SOMTHING
END;
ELSIF V_TEST = 2 THEN
BEGIN
--DO SOMTHING
END;
ELSE
BEGIN
--DO SOMTHING
END;
END IF;
4.while 循环
WHILE V_TEST=1 LOOP
BEGIN
XXXX
END;
END LOOP;

5.变量赋值
V_TEST := 123;

6.游标定义及使用语法：
CURSOR cursor_name is SELECT * FROM TABLE_TEST;
FOR var IN cursor_name LOOP
BEGIN
--CONTENT;
END;
END LOOP;


高级主题部分：
7.ORACLE异常处理
BEGIN
SELECT col1,col2 into 变量1,变量2 FROM typestruct where xxx;
EXCEPTION --若前面的代码块中出现异常，那么将进入此处进行异常处理。
WHEN NO_DATA_FOUND THEN --某个类型的异常的名字
BEGIN
--do somthing ;
END；
WHEN OTHERS THEN --所有其它名字的异常，都在此代码块中处理。
BEGIN
--DO SOMTHING
END；
END;

ORACLE的存储过程很多地方都用到 BEGIN END；
代码块。相当于程序语言中的圆括号，标识此段代码是一个整体。
而存储过程的异常处理也与程序语言异常，一个异常若是没有得到处理，那么会被一层一层的往外面抛出。
7.带参数的cursor
CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID;
OPEN C_USER(变量值);
LOOP
FETCH C_USER INTO V_NAME;
EXIT FETCH C_USER%NOTFOUND;
do something
END LOOP;
CLOSE C_USER;

8.动态游标的定义及使用
TYPE mycursor_type IS REF CURSOR;--自定义一个动态游标类型
my_cursor mycursor_type; --定义游标变量

OPEN my_cursor FOR 'SELECT * FROM TABLE_TEST';
LOOP
FETCH my_cursor INTO COL_A,COL_B...;
exit when cursor_data%NOTFOUND;
--CONTENT;
END LOOP;

动态游标对于需要根据动态SQL语句来取得结果集的场合比较有用。对于游标的遍历，相对来说效率较为低。
对游标的遍历方案可以用ORACLE提供的RECORD技术进行替代。

9.自定义RECORD类型。
TYPE recordname IS RECORD(
col1 number,
col2 varchar2(30));
TYPE recordtable IS TABLE OF recordname INDEX BY BINARY_INTEGER;
v_ecord recordtable ;

open dyna_cursor for 'select * from table_test';
fetch dyna_cursor bulk collect
into v_ovthreshod_values;
CLOSE v_ecord;
for i in 1 .. v_ecord.COUNT LOOP
--do somthing
end loop;
将取出来的数据从动态游标里取出放到自定义表结构中。
这种方法对某条数据特殊处理比较容易，比如你需要对最后一条数据作特殊处理，那就只需要判断 I = v_ecord.COUNT 就是最后一条数据了。而游标的话比较麻烦。

10. oracle中队日期类型的值作相互操作时会出现异常。
如：(date1 - date2)*24*3600 = 4110.999999
比较奇怪，所以就采用取出来的日期是字符串，在用TO_DATE函数进行类型转换进行操作。返回结果集正确