sql游标的使用方法_sql面试题50题

SQL数据库中什么是游标？ 　　游标（cursor）是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果。每个游标区都有一个名字,用户可以用SQL语句逐一从游标中记录，并赋给主变量，交由主语言进一步处理。 　　游标是处理结果集的一种机制吧，它可以定位到结果集中的某一行，多数据进行读写，也可以移动游标定位到你所需要的行中进行操作数据。一般复杂的存储过程，都会有游标的出现。 　　游标（cursor）的用处主要有：定位到结果集中的某一行。对当前位置的数据进行读写。可以对结果集中的数据单独操作，而不是整行执行相同的操作。是面向集合的数据库管理系统和面向行的程序设计之间的桥梁。 　　在Oracle中共有两种类型的游标（cursor），分别是隐式游标（cursors）和 显式游标（cursors）：隐式游标（Implicit cursors）: 由Oracle服务器自动创建和管理，用户不能访问隐式游标（cursors）。当必须执行一个SQL语句时，Oracle服务器自动创建一个这样的cursors。显式游标（Explicit cursors）: 由程序员声明和命名，程序员可以访问和控制显式游标（cursors）。 　　隐式游标（Implicit cursors）的属性： 　　

　　隐式游标（Implicit cursors）的属性的说明：在每一个属性之前都要冠以SQL,这是因为这些cursor属性都是PL/SQL 自动创建的隐式 cursor的属性，而用户也不知道隐式 cursor的名字。属性 SQL%FOUND 和SQL%NOTFOUND是互补的（相反的），这两个属性可以被用作循环的退出条件以方便编程。当UPDATE或 DELETE语句没有改变任何数据时，这两个属性很有用，因为在这样的情况下并不返回异常。 　　代码演示： 　　举例解释隐式游标（Implicit cursors）的属性 SQL%ROWCOUNT的具体用法： 　　显式游标（Explicit cursors）的属性： 　　

　　使用显式cursors的步骤： 　　声明显式cursor: 在PL/SQL程序块的声明段中，通过命名并定义相关的查询结构来声明一个显式 cursor。打开（OPEN)显式cursor: OPEN 语句执行显式cursor所定义的查询语句并绑定所引用的变量，查询语句所标识的数据行被称为活动集，并且现在变量可以提取这些数据行。从定义的cursor 中提取（FETCH)数据： 将活动集的当前数据行装入定义的变量（活动集的下一行将变为当前行）。在每次提取之后应该测试活动集中是否还有数据存在。如果没有要处理的数据了，应该关闭cursor。关闭（CLOSE)显式 cursor: CLOSE 语句释放活动集的所有数据行，现在就可以重新打开这个cursor 以建立新的活动集。注意要养成习惯，在处理完 cursor 中的数据之后及时地关闭cursor以释放内存资源。 　　使用OPEN、FETCH和CLOSE 语句来控制一个显式cursor的，cursor(指针）指向活动集中当前的位置，这三个语句完成的操作如下： 　　（1)OPEN语句执行与这个cursor相关的查询语句，标识结果（活动）集，并将“指针”指向活动集的第1行。 　　（2)FETCH语句提取当前行数据，并将指针向下移动一行，直到没有数据行或说明的条件满足为止。 　　（3)CLOSE 语句释放 cursor。

控制显式cursor的流程图

控制显式cursor的流程图 　　1、声明cursor 　　声明cursor的语法： 　　声明一个显式cursor的注意事项归纳如下：在cursor声明中不能包含 INTO 子句，因为这一子句将出现在随后的FETCH语句中。如果需要按特定的顺序来处理行，要在查询中使用 ORDER BY子句显式cursor可以是任何有效的select语句，包括连接、子查询等。 　　代码演示： 　　2、打开cursor 　　打开cursor的语法： 　　打开cursor语句所执行的操作总结如下：为一个上下文区域动态地分配内存。对SELECT语句进行语法分析。绑定输入变量（通过输入变量的内存地址为输入变量设置值）。标识活动集（生成满足查询条件的数据行的集合，即满足查询条件的所有数据行）。当执行OPEN语句时，并没有执行提取活动集中的数据行并存入变量的操作。从cursor中提取数据行并存入变量是由FETCH语句完成的。将指针定位在（指向）活动集中的第1行。 　　3、从cursor中提取数据（FETCH语句） 　　语法： 　　从cursor中提取数据的注意事项归纳如下：在FETCH语句的INTO子句中包含的变量个数要与cursor中的SELECT语句中的列数相同。每个变量（的数据类型）与对应位置的列（的数据类型）相匹配。也可以为cursor定义一个记录并在FETCH INTO子句中引用这一记录。测试cursor中是否还包含数据行。如果FETCH语句没有提取到任何值（数据），在活动集中没有数据行要处理，PL/SQL并不报错。 　　现将FETCH语句所执行的操作总结如下：读取当前行的数据并装入PL/SQL的输出变量中。将cursor的指针移向所标识的活动集中的下一行。 　　代码演示： 　　4、关闭cursor 　　关闭cursor的语法： 　　关闭cursor的注意事项归纳如下：在完成了数据行的处了理之后，关闭cursor如果需要的话，重新打开cursor在cursor关闭之后，不要试图从该cursor中提取数据 　　%ISOPEN属性 　　只有当cursor打开时才能从中提取数据行 在进行提取之前，使用 %ISOPEN的cursor属性来测试cursor是否打开。 　　代码演示： 　　%NOTFOUND 和 %ROWCOUNT 属性 　　使用 cursor 的%ROWCOUNT属性提取准确数量的数据行使用 cursor 的%NOTFOUND属性决定什么时候退出循环 　　利用循环及属性控制cursor多次提取 　　使用循环从显式cursor中处理若干行每重复一次提取一行使用显式cursor属性来测试每次提取的成功代码演示： 　　演示如何利用循环及cursor属性来控制和操作cursor。 　　cursor与记录 　　通过提取数据值并装入一个PL/SQL 记录来处理活动集中的数据行。 　　代码演示： 　　查询部门中emp哪些人是真正的经理，手下有下属的真正经理。 　　使用的是DB2数据库第一种： 　　BEGIN 　　declare co_id int; 　　declare it_id int; 　　declare done int default true; 　　declare cur1 cursor for SELECT CONTACTID FROM A_CONTACT WHERE CONTACTWAYID =’74’ ; 　　declare cur2 cursor for SELECT CONTACTWAYID FROM B_WAY WHERE CONTACTWAYID NOT IN (select CONTACTWAYID from A_CONTACT ) FETCH FIRST 1 ROWS ONLY ; 　　declare continue handler for not found set done=false; 　　open cur1; 　　while done do 　　fetch cur1 into co_id; 　　if done =true then 　　open cur2; 　　while done DO 　　fetch cur2 into it_id; 　　if done=true then 　　UPDATE A_CONTACT set CONTACTWAYID=it_id WHERE CONTACTID=co_id; 　　commit; 　　end if; 　　end while; 　　close cur2; 　　set done=true; 　　end if; 　　end while; 　　close cur1; 　　end 　　报错信息：[BEGIN – 0 row(s), 0.000 secs] [Error Code: -303, SQL State: 42806] DB2 SQL Error: SQLCODE=-303, SQLSTATE=42806, SQLERRMC=null, DRIVER=4.13.127 　　第二种： 　　BEGIN 　　DECLARE no_more_record INT DEFAULT 0; 　　DECLARE p_id int(18); 　　DECLARE o_id int(18); 　　DECLARE curl CURSOR FOR SELECT CONTACTID FROM A_CONTACT WHERE CONTACTWAYID =’74’ ; 　　DECLARE curl2 CURSOR FOR SELECT CONTACTWAYID FROM B_WAY WHERE CONTACTWAYID NOT IN (select CONTACTWAYID from A_CONTACT ) ; 　　DECLARE CONTINUE HANDLER FOR NOT found SET no_more_record =1; 　　OPEN curl; 　　FETCH curl INTO p_id; 　　WHILE no_more_record <> 1 DO 　　OPEN curl2; 　　FETCH curl2 INTO o_id; 　　WHILE no_more_record <> 1 DO 　　UPDATE A_CONTACT set CONTACTWAYID=o_id WHERE CONTACTID=p_id; 　　FETCH curl2 INTO o_id; 　　END WHILE; 　　CLOSE curl2; 　　SET no_more_record =0; 　　FETCH curl INTO p_id; 　　END while ; 　　CLOSE curl; 　　END 　　报错信息： 19:58:37 [BEGIN – 0 row(s), 0.000 secs] [Error Code: -104, SQL State: 42601] DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=int;LT 0; 　　DECLARE p_id;NVARCHAR2, DRIVER=4.13.127 　　第三种： 　　BEGIN 　　DECLARE WID DECIMAL(18); 　　DECLARE CID DECIMAL(18); 　　DECLARE STOP INT DEFAULT 0; 　　DECLARE NAME1 CURSOR FOR SELECT CONTACTID FROM A_CONTACT WHERE CONTACTWAYID =’74’ ; 　　DECLARE NAME2 CURSOR FOR SELECT CONTACTWAYID FROM B_WAY WHERE CONTACTWAYID NOT IN (select CONTACTWAYID from A_CONTACT ) FETCH FIRST 1 ROW ONLY ; 　　DECLARE CONTINUE HANDLER FOR NOT FOUND SET STOP = 1; 　　OPEN NAME1 ; 　　out_loop:LOOP 　　FETCH NEXT FROM NAME1 INTO CID; 　　IF STOP = 1 THEN 　　LEAVE out_loop; 　　END IF; 　　OPEN NAME2; 　　INNER_LOOP:LOOP 　　FETCH NEXT FROM NAME2 INTO WID; 　　IF STOP = 1 THEN 　　LEAVE INNER_LOOP; 　　END IF ; 　　UPDATE A_CONTACT set CONTACTWAYID=CID WHERE CONTACTID=Wid; 　　END LOOP INNER_LOOP; 　　CLOSE NAME2; 　　SET STOP = 0 ; 　　END LOOP out_loop ; 　　CLOSE NAME1; 　　END 　　报错信息：[BEGIN – 0 row(s), 0.000 secs] [Error Code: -104, SQL State: 42601] DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=out_loop:LOOP 　　FETCH NEXT;P = 1; 　　OPEN NAME1 ; 　　;<psm_fetch>, DRIVER=4.13.127 　　第四种： 　　begin 　　declare Wid DECIMAL(18) ; 　　DECLARE Wdone INT DEFAULT FALSE; 　　DECLARE Wname CURSOR FOR ( SELECT CONTACTID FROM A_CONTACT WHERE CONTACTWAYID =’74’ ) ; 　　DECLARE CONTINUE HANDLER FOR NOT FOUND SET Wdone = TRUE ; 　　OPEN Wname; 　　WHILE NOT Wdone DO 　　FETCH Wname into Wid; 　　IF NOT Wdone THEN 　　BEGIN 　　declare Nid DECIMAL(18); 　　DECLARE Ndone INT DEFAULT FALSE; 　　DECLARE Nname CURSOR FOR ( SELECT CONTACTWAYID FROM B_WAY WHERE CONTACTWAYID NOT IN (select CONTACTWAYID from A_CONTACT ) ) ; 　　DECLARE CONTINUE HANDLER FOR NOT FOUND SET Ndone = TRUE ; 　　OPEN Nname; 　　WHILE NOT Ndone DO 　　FETCH Nname into Nid; 　　IF NOT Ndone THEN 　　UPDATE A_CONTACT set CONTACTWAYID=Nid WHERE CONTACTID=Wid; 　　END IF; 　　END WHILE ; 　　CLOSE Nname; 　　END ; 　　END IF; 　　END WHILE; 　　CLOSE Wname ; 　　END 　　报错信息：[BEGIN – 0 row(s), 0.000 secs] [Error Code: -303, SQL State: 42806] DB2 SQL Error: SQLCODE=-303, SQLSTATE=42806, SQLERRMC=null, DRIVER=4.13.127 　　游标（cursor）是数据库代码中的一个对象，它能够单独处理行或“遍历”行。 　　这样做的目的是什么？ 　　是因为在程序开发中，我们常常会连接到数据库，需要对结构化数据库中的记录进行处理。 　　其目的就是把执行sql语句后的结果通过循环的方式，一条一条的取出，以便于对数据集中的每条记录执行操作，直到到达数据集的末尾。 　　使用游标我们可以查看集合中的每条记录。