第五章
游标CURSOR
CURSOR 用于提取多行数据集
DECLARE CURSOR
DECLARE
v_major student.major%TYPE; －－定义一个变量
CURSOR c_sutdent IS
－－可以在这里为我们的参数进行赋值然后我们就可以查询到数据，然后将数据放置在游标内
－－赋值数据v_major
SELECT first_name,last_name
FROM students
WHERE major = v_major;
建立一个游标c_student内容为first_name,last_name，要求为major= v_major的内容
＊＊＊＊＊＊＊＊＝＝＝＝＝＝＝＝＝＝＝＝＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
Open Cursor
语法:OPEN c_student;
FETCH FROM CURSOR
FETCH 的两种方式
FETCH cursor_name INTO var1,var2 －－一个一个参数的赋值
FETCH cursor_name INTO record_var －－一个集合内部全部赋值
CLOSE CURSOR
CLOSE cursor_name
游标使用结束后应当关闭
FETCH 关闭后的游标是非法的
关闭一个关闭了的游标是非法的
游标的属性
%FOUND
若前面的FETCH语句返回一行数据，则%FOUND返回TRUE
若游标没有打开就检查%FOUND，则返回ORA-1001
%NOTFOUND
与%FOUND相反
%ISOPEN
检测游标是否打开
%ROWCOUNT
当前游标指针的位移量，到目前为止游标所检索的数据行的个数，若未打开就未引用则返回ORA-1001
游标的属性TEST
假设表
temp_table
只有两行数据
Declare
--Cursor declaration
CURSOR c_TempData IS SELECT * FROM temp_table;
--Record to store the fetched data
v_TempRecord c_TempData%ROWTYPE;
BEGIN
--lacation 1
OPEN c_TempData;
--location 2
FETCH c_TempData INTO v_TempRecord.--Fetch first row
DBMS_OUTPUT.PUT_LINE(v_TempRecord.id||' '||v_TempRecord.name);
--location 3
FETCH c_TempData INTO v_TempRecord.--Fetch second row
DBMS_OUTPUT.PUT_LINE(v_TempRecord.id||' '||v_TempRecord.name);
--location4
FETCH c_TempData INTO v_TempRecord;--Fetch third row
--location 5
CLOSE c_TempData;
--location 6
END;
游标的FETCH循环
1、LOOP
FETCH cursor INTO
EXIT WHEN cursor%NOTFOUND;
END LOOP;
2、WHILE cursor%FOUND LOOP
FETCH cursor INTO
END LOOP ;
3、FOR var IN cursor LOOP
FETCH cursor INTO
END LOOP;
例：
DECLARE
--Cursor declaration
CURSOR c_TempDate IS SELECT * FROM temp_table;
--Record to store the fetched data
v_TempRecord c_TempData%ROWTYPE;
一、BEGIN
--location1
OPEN c_TempDate;
--location2
FETCH c_TempDate INTO v_TempRecord;
--location3
WHILE c_TempDate%FOUND LOOP
FETCH c_TempDate INTO v_TempRecord;
END LOOP;
--location4
CLOSE c_TempDate;
END
二、BEGIN
--location1
OPEN c_TempDate;
--location2
LOOP
FETCH c_TempDate INTO v_TempRecord;
DBMS_OUTPUT.PUT_LINE(v_TempRecord.id||' '||v_TempRecord.name);
END WHEN c_TempDate%NOTFOUND;
END LOOP;
--location3
CLOSE c_TempDate;
END
三、BEGIN
--location1
不要开启
--location2
FOR v_TempRecord IN c_TempDate LOOP
END LOOP;
--location3
不要关闭
带参数的游标
CURSOR 可以带参数
DECLARE
CURSOR c_student(p_major student.major%TYPE)
--IS－－在建立游标的同时建立参数括号内部的数据包含，参数名，和参数类型。
--参数申明的时候类型的约束不可以些（如精度，刻度等） 仅写类型就好。
--尽量使用TYPE OR ROWTYPE定义参数的类型
SELECT *
FROM students
WHERE major = p_major;
BEGIN
OPEN c_student(101);－－打开时候给游标的参数赋值
.....其他一样。
如果是for语句参数怎么附加呢？
FOR c_student(101) INTO v_emp;
=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+
Handle Exception 异常处理
PL/SQL错误
1、编译时
--语法的错误
2、运行时
--例：没有数据，等。。。
运行时的出错处理
EXCEPTION
异常分类：
1、用户自定义
2、Oracle预定义
异常处理快
DECLARE
。。。。
BEGIN
。。。
EXCEPTION
WHEN OTHERS THEN
handler_error(...);
END;
用户自定义异常
DECLARE
e_TooManyStudents EXCEPTION;
BEGIN
...
RAISE e_ToolManyStudents;--抛出异常，RAISE e_ToolManyStudents（异常名称）；
...
EXCEPTION
WHEN e_TooManyStudents THEN--当异常了则
...
END;
预定义的ORACL异常
1、ORA－0001
DUP_VAL_ION_INDEX
2、ORA-0051
TIMEOUT_ON_RESOURCE
3、ORA-1001
INVALID_CURSOR
...
4、ORA-6533
SUBSCRIPT_BEYOND_COUNT
例：
DECLARE
v_emp s_emp%ROWTYPE;--定义参数
e_MyException EXCEPTION;--定义异常
BEGIN
SELECT *
INTO v_emp
FROM s_emp
WHERE id = 100;
IF v_emp.salary<1000 THEN
RAISE e_MYException;
END IF;
DBMS_OUTPUT.PUT_LINE('v_emp.id = '|| v_emp.id);
EXCEPTION --若没有出现异常，则就不执行EXCEPTION下的异常处理内容。
WHEN NO_DATA_FOUND THEN--获取oracle系统预定义异常
DBMS_OUTPUT.PUT_LIEN('v_emp : no data in v_emp !');
WHEN e_MyException THEN--获取自定义异常
DBMS_OUTPUT.PUT_LINE('v_emp : salary is too low!');
UPDATE s_emp SET salary = 1000--更新工资为1000
WHERE id = v_emp.id;
--统一处理异常处理上面处理过的其他异常
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('v_emp: all others exceptin !');
--不可将这个循序为第一位置，必须为最后一位，因为放置的位置与程序的执行流程是有关系的。
END;
处理异常
EXCEPTION
WHEN exception_name1 THEN
sequence_of_statements1;
WHEN exception_name2 THEN
sequence_of_statements2;
[WHEN OTHERS THEN --其他任何异常都执行这段代码
sequence_of_statements3;]
END；
处理所有的异常
1、异常的传递
2、处理所有其他异常
EXCEPTION
WHEN s_ToolManyStudents THEN
WHEN OTHERS THEN
v_ErrCode :=SQLCODE;
v_ErrText :=SUBSTR(SQLERRM,1,200);
INSERT INTO log_file(code,message,info)
VALUES(v_ErrCode,v_ErrText,'ORACLE Error');
END;
SUB PROGRAM子程序
1、匿名块
匿名块不粗那在于数据库中
每次使用时都会进行编译
不能在其他块中相互调用
2、带名块
可存储于数据库中
可以在任何需要的地方调用
procedure,function,package,trigger
PROCEDURE存储过程
创建过程
CREATE [OR REPLACE] PROCEDURE proc_name
［（arg_name[{IN|OUT|IN OUT}] TYPE.
...
arg_name[{IN|OUT|IN OUT}] TYPE)]
{IS|AS}
procedure_body
――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――|―
CREATE OR REPLACE PROCEDURE pro_hello AS --存储过程的名字与创建 |
|
BEGIN |
DBMS_OUTPUT_PUTLINE('hello world'); |
END |
――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――|―
上面的存储过程写完毕需要执行创建
若上面的名字为pro_hello.sql则运行
SQL> @pro_hello.sql
那么创建完毕。存储过程已经存放在数据库 里面了，那么我们要执行这个存储过程就可以这么写
SQL> pro_hello
写成块脚本
BEGIN
--pro_hello无参数，直接执行
pro_hello;
END
上面的存储过程中没有任何数据的定义，以及变量的使用，那么我们在这里进行数据的定义与数据的其他设置
――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――|―
CREATE/*创建*/ OR REPLACE/*重新创建*/ PROCEDURE pro_hello |
(p_id s_emp.id%TYPE) |
AS |
v_emp s_emp%ROWTYPE; |
|
BEGIN |
SELECT ＊ INTO v_emp |
FROM s_emp |
WHERE id =p_id; |
|
DBMS_OUTPUT_PUTLINE('hello world'); |
END |
――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――|―
怎样使用上面的存储过程呢。。。。。。
SQL>pro_hello(1);--此处数字为传入参数
也可以如此写匿名块的脚本
BEGIN
--pro_hello存储过程的执行
pro_hellp(1);
END
如此传值，为IN 模式传递值，就是传递一个值给存储过程，没有返回值虽然他返回的内容是显示数据，但是没有返回值这个与OUT模式的相反
OUT模式的传递 相当于程序中有返回值
――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――|―
CREATE/*创建*/ OR REPLACE/*重新创建*/ PROCEDURE pro_hello |
(p_id IN s_emp.id%TYPE， |
p_name OUT s_emp.first_name%TYPE) |
AS |
v_emp s_emp%ROWTYPE; |
|
BEGIN |
SELECT ＊ INTO v_emp |
FROM s_emp |
WHERE id =p_id; |
|
DBMS_OUTPUT_PUTLINE('hello world'); |
――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――――|―
DECLARE
v_name s_emp.first_name%TYPE;
BEGIN
--pro_hello out执行
pro_hello(1,v_name);
DBMS_OUTPUT.PUT_LINE('hello, ' || v_name);
END;
PROCEDURE的参数模式
IN
在调用过程的时候，实际参数的值被传递给该过程，在过程内部，形参是只可读的。
OUT
在调用过程时，任何的实参将被忽略；在过程内部，形象是只可写的。
IN OUT
是IN与OUT的组合，在调用过程的时候，实参的值可以被传递给该过程；在过程内部，行参也可以被读出也可以被写入，过程结束时，控制会返回给控制环境，面形式参数的内容将赋给调用时的实际参数。
注： 可以使用SHOW ERRORS命令查看错误的信息
文字或常数作为实参
模式为IN OUT 或OUT 参数对应的实际参数必须是变量，不能是常量或表达式。必须要有一个位置可以存储返回的值
对形式参数的约束
1、在调用过程当中，实际参数在将值传递给过程时，也传递了对变量的约束
2、形式参数不能声明约束，但可使用％TYPE来进行约束
指定实参的模式
1、位置标识法
pro_hello(1,v_name);
2、名字标识法
pro_hello(p_name=>v_name,p_id=>1);--可读性强
3、混合
用的第一位必须位置标识法
名字标示法对于参数很多时，可提高程序的可读性
使用缺省参数
1、形参可以指明缺省值
parm_name[mode]type {:=|DEFAULT} init_value
2、位置标示法时，所有的缺省都放在最后面
使用名字标示法则无所谓
3、声明时，所有的缺省值，尽量将缺省值放在参数表的末尾
FUNCTION
1、函数在所有的地方都与过程相似
都有名字
都有统一的形式，：声明，执行与异常处理
可以存储在数据库中，也可以声明在无名块的内部
2、差别
过程调用本身是一个PL／SQL查询
函数调用是表达式的一部分
函数的声明
CREATE [OR REPLACE] FUNCTION func_name
[{arg_name[{IN|OUT|IN OUT|]TYPE;
...
(arg_name[{IN|OUT|IN OUT|}]TYPE)]
RETURN TYPE
{IS|AS}
Func_body
RETURN 语句
1、在函数的主体内部 ，return语句用来将控制通过一个数值返回给调用环境
RETURN <表达式>；
2、在一个函数主体中，可以使用多个返回语句，只有一个会被执行
3、没有返回语句的函数将是一个错误。
函数样式
1、函数可以他哦难过OUT参数来返回多个数值
2、函数可以接收缺省参数
__________________________________________________
CREATE OR REPLACE FUNCTION fun_add( |
p_a NUMBER, |
p_b NUMBER) RETURN NUMBER |
IS |
v_ret number; |
BEGIN |
v_ret := p_a + p_b; |
RETURN v_ret ; |
END |
________________________________________________|_
建立SQL脚本执行
DECLARE
v_ret number;
BEGIN
v_ret := fun_add(2,3);
DBMS_OUTPUT.PUT_LINE(v_ret);
END;
删除过程与函数
DROP PROCEDURE proc_name;
DROP FUNCTION func_name;
子程序的位置
1、Stored subprogram
通过CREATE OR REPLACE 命令创建
以编译后的形式存放在数据库中
2、本地子程序
没有CREATE OR REPLACE 关键字
子程序的定义放在无名块的声明部分
子程序被该无名块使用
＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
Package 包
1、包规范
一个大的声明区
可以声明变量，声明异常，子程序。。。
注意：在包规范中，只有声明没有实现
包头的例子1
CREATE OR REPLACE PACKAGE pak_test AS
PROCEDURE AddStudent(
p_StuID IN students.id%TYPE,
p_Dep IN classes.department%TYPE,
p_Course IN classes.course%TYPE
);
PROCEDURE RemoveStudent(
p_StuID IN students.id%TYPE
);
e_StudentNotRegistered EXCEPTION;
TYPE t_StuIDTable IS TABLE OF students.id%TYPE
INDEX BY BINNARY_INTEGER;
END pak_name;
2、包主体
包主体的例子1
CREATE OR REPLACE PACKAGE BODY pak_test AS
PROCEDURE AddStudent(
p_StuID IN student.id%TYPE,
p_Dep IN classes.department%TYPE,
p_Course IN classes.course%TYPE) IS
BEGIN
...
END AddStudent;
PROCEDURE RemoveStudent(
p_StuID IN students.id%TYPE
) IS
BEGIN
...
END RemoveStudent;
END pak_test;