Oracle中的游标有两种:
- 显式游标
用CURSOR...IS 命令定义的游标,它可以对查询语句(SELECT)返回的多条记录进行处理。
- 隐式游标
是在执行插入(INSERT)、删除(DELETE)、修改(UPDATE)和返回单条记录的查询(SELECT)语句时有PL/SQL自动定义的。
1、显式游标操作
显式游标在块定义部分、包或子程序中声明。当声明了显式游标后,可通过下面三条命令控制显式游标操作。
1)打开游标
2)推进游标
3)关闭游标
--声明显式游标
声明显式游标就是指定游标名和与它关联的SELECT语句,其语法如下:
CURSOR cursor_name[(parameter[,parameter]...)][RETURN return_type] IS select_statement; parameter子句语法:
cursor_parameter_name[IN]datatype[{:=|DEFAULT} expr] cursor_name是游标名,必须符合PL/SQL标识符的命名规范;
return_type 是游标返回的查询结果集类型,它只能是一个PL/SQL记录类型或数据库表的某一列的类型。
select_statement是游标中的SELECT语句,在游标中指定的SELECT语句可以带有UNION、MINUS或INTERSECT子句。select_statement不能含有INTO子句,查询结果是用FETCH语句中的INTO子句送给变量的。 将PL/SQL变量绑定在游标的WHERE子句中
DECLARE
v_auths auths%ROWTYPE;
v_code auths.author_code%TYPE;
CURSOR c_auths IS
SELECT * FROM auths
WHERE author_code=v_code; 因为声明游标时将PL/SQL变量绑定在WHERE子句中,所以游标的声明必须在这些变量的作用域内,同时先声明变量在声明显式游标。 将游标参数绑定在游标的WHERE子句中
DECLARE
CURSOR c_auths(p_code auths.author_code%TYPE;) IS
SELECT * FROM auths
WHERE author_code=p_code; 游标参数的作用域只局限在游标声明所指定的查询中,并且游标可以赋缺省值。 --打开显式游标
处理:先查找绑定在游标中的变量,然后按照该变量的值确定查询结果集。 语法:
OPEN cursor_name [(parameter[,parameter]...)] cursor_name是已声明的游标的名;
parameter是绑定的实参; BEGIN
--在打开游标前为绑定的变量赋值
v_code:='A0001';
--打开游标
OPEN c_auths; 如果打开游标后,在为改变量赋值,查询结果集不发生任何改变。这是因为结果集在游标打开时被确定,同时指向结果集的指针也被确定。 BEGIN
--打开游标时将参数传入
OPEN c_auths('A0001'); --推进显式游标
使用FETCH语句来推进游标,返回查询结果集中的一行,没执行完一条FETCH语句后,显式游标会自动指向查询结果集的下一行。 语法:
FETCH cursor_name INTO list_of_variables;
或
FETCH cursor_name INTO PL/SQL_record; cursor_name是已声明的游标的名
list_of_variables是已声明的变量列表
PL/SQL_record是已定义好的PL/SQL记录
INTO子句中的变量必须与游标SELECT语句中相应表列的类型兼容(一致或可以自动转换) --关闭显式游标
当整个结果集都检索完以后,应当关闭游标,释放游标所占用的资源。 语法:
CLOSE cursor_name 游标处于关闭状态时,推进游标时错误的,错误信息如下:
ORA-1001:Invalid CURSOR
或
ORA-1002:Fetch out of Sequence 如果关闭游标有又执行关闭游标命令,还会提示ORA-1001错误。
2、游标的属性
%FOUND
一个返回布尔值得属性,如果FETCH语句返回了一行,则该属性返回true,否则返回false。
如果在打开游标之前或关闭游标之后使用该属性,会提示ORA-1001错误。当打开游标后没有使用FETCH语句推进,则返回NULL; %NOTFOUND
与%FOUND意思相反,如果FETCH语句返回了一行,则改属性返回false,否则返回true。其它和%FOUND相同。 %ISOPEN
用来确定相关游标是否打开,如果游标已打开,则返回true,否则返回false; %ROWCOUNT
返回游标推进的行数。如果在打开游标之前或关闭游标之后使用该属性,会提示ORA-1001错误。
3、显式游标的推进循环
DECLARE
--声明一个变量,这个变量用来接收游标返回的结果集。
v_Salary Auths.salary%TYPE;
v_Code Auths.author_code%type;
--声明游标,结果集为作家代码A0001到A0006的工资值
CURSOR c_salary IS
SELECT salary,author_code
FROM auths
WHERE author_code<='A0006'
BEGIN
--打开游标,并初始化结果集
OPEN c_salary;
LOOP
--推进游标,将游标的查询结果集中的一行存到变量v_salary中。
FETCH c_salary INTO v_Salary,v_Code;
--当结果集中没有夯实退出循环。
EXIT WHEN c_salary%NOTFOUND;
IF v_Salary<=200 THEN
UPDATE auths SET salary=salary=50;
WHERE author_code=v_Code;
END IF;
END LOOP;
--关闭游标
CLOSE c_salary;
--提交所做修改
COMMIT;
END; PL/SQL还提供了一种简单类型的循环,可以自动控制游标的打开、推进和关闭。 DECLARE
CURSOR c_salary IS
SELECT salary
FROM auths
WHERE author_code<='A0006'
BEGIN
--开始游标FOR循环,隐含地打开c_salary游标。
FOR v_salary IN c_salary LOOP
--一个隐含的FETCH语句在这里被执行
IF v_Salary<=200 THEN
UPDATE auths SET salary=salary=50;
WHERE salary=v_salary.salary;
END IF;
--循环继续前进,一个隐含的v_salary%NOTFOUND被检测。
END LOOP;
--循环结束,c_salary游标的一个隐含的CLOSE操作被执行。
COMMIT;
END; v_salary没有在块的定义部分声明,该变量被PL/SQL编译器隐含地声明了,该变量的类型为c_salary%ROWTYPE,其作用域只在循环内部。在循环开始,游标的c_salary被自动打开。在每一次自动推进后,%FOUND属性用来检查在结果集中是否还有行,当结果集中没有行时,游标被自动关闭。 使用CURRENT OF cursor_name子句作为条件。
DECLARE
--声明游标时在SELECT语句中必须加FOR UPDATE OF 子句
CURSOR c_salary IS
SELECT salary
FROM auths
WHERE author_code<='A0006' FOR UPDATE OF salary;
BEGIN
FOR v_salary IN c_salary LOOP
IF v_Salary<=200 THEN
UPDATE auths SET salary=salary=50;
WHERE CURRENT OF c_salary;
END IF;
END LOOP;
COMMIT;
END;
4、隐式游标处理(SQL游标)
%FOUND
当使用INSERT、DELETE或UPDATE语句处理一行或多行,或换行SELECT INTO 语句返回一行时,返回true,否则返回false;
如果执行SELECT INTO语句时返回多行,则会产生TOO_MANY_ROWS异常,并将控制权转到异常处理部分,%FOUND属性并不返回true,如果执行SELECT INTO语句时返回0行,会产生NO_DATA_FOUND异常,%FOUND属性并不返回false。 %NOTFOUND
与%FOUND属性相反,当使用INSERT、DELETE或者UPDATE语句处理的行数为0时,%NOTFOUND属性返回true,否则返回false。 %ISOPEN
因为在执行了DML语句后,Oracle会自动关闭SQL游标,所以该属性终为false。 %ROWCOUNT
该属性返回执行INSERT、DELETE或者UPDATE语句返回的行数,或返回执行SELECT INTO 语句时查询出的行数,如果INSERT、DELETE、UPDATE或者SELECT INTO语句返回的行数为0,则%ROWCOUNT属性返回0。但如果SELECT INTO语句返回的行数为多行,则产生TOO_MANY_ROWS异常,并将控制权转到异常处理部分,而不是去判断%ROWCOUNT属性。 BEGIN
UPDATE auths
SET entry_date_time=SYSDATE
WHERE author_code='A0001';
IF SQL%NOTFOUND THEN
INSERT INTO auths(author_code,name,entry_date_time) VALUES('A0001','张三',SYSDATE);
END IF;
END; SET SERVEROUTPUT ON
DECLARE
v_birthdate DATE;
BEGIN
SELECT birthdate INTO v_birthdate
FROM auths
WHERE name='张三';
IF SQL%FOUND THEN
DELETE FROM auths WHERE name='张三';
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('该记录不存在');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('存在多条记录');
END;
5、游标变量
这部分内容以后补上。