存儲過程之四—游標，存儲過程游標

存儲過程之四—游標，存儲過程游標

　游標在存儲過程和函數中使用。語法如同在嵌入的SQL中。游標是只讀的及不滾動的，只能在一個方向上進行遍歷，不能在記錄之間隨意進退，不能跳過某些記錄，所以每次讀完之後就應該移動到下一個記錄。游標必須在聲明處理程序之前被聲明，並且變量和條件必須在聲明光標或處理程序之前被聲明。

一、游標

　　1、定義

　　　DECLARE 游標名稱 CURSOR FOR 查詢語句

　　　這個語句聲明一個光標。也可以在子程序中定義多個光標，但是一個塊中的每一個光標必須有唯一的名字。

 　　2、OPEN語句

　　　　OPEN 游標名稱

　　　　這個語句打開先前聲明的游標。

　　3、FETCH語句

　　　　FETCH 游標名稱 INTO 變量[,  變量2] ...

　　　　這個語句用指定的打開游標讀取下一行（如果有下一行的話），並且前進游標指針。

 　　4、CLOSE語句

　　　　CLOSE 游標名稱

　　　　這個語句關閉先前打開的游標。如果未被明確地關閉，游標在它被聲明的復合語句的末尾被關閉。

二、實例

　　表結構如下：

-- ----------------------------
-- Table structure for person
-- ----------------------------
DROP TABLE IF EXISTS `person`;
CREATE TABLE `person` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of person
-- ----------------------------
INSERT INTO `person` VALUES ('1', '張三', '21', null);
INSERT INTO `person` VALUES ('2', '李四', '23', null);
INSERT INTO `person` VALUES ('3', '王五', '22', null);
INSERT INTO `person` VALUES ('4', 'zhangsan', '22', 'fdsafds');
INSERT INTO `person` VALUES ('8', 'zhangsan', '22', 'fdsafds');
INSERT INTO `person` VALUES ('9', 'zhangsan', '22', 'fdsafds');
INSERT INTO `person` VALUES ('10', 'wangwu', '23', 'password123');

　　1、游標使用REPEAT 

DROP PROCEDURE IF EXISTS proc_test_cursor;
-- 所有人的年齡和
CREATE PROCEDURE proc_test_cursor(
    OUT total INT(11)
)
BEGIN
    DECLARE t INT DEFAULT 0;
    DECLARE done INT DEFAULT 0;
    DECLARE pcursor CURSOR FOR SELECT age FROM person;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; -- 在 FETCH 語句中引用的游標位置處於結果表最後一行之後。
    SET total = 0;
    OPEN pcursor;
    REPEAT
        FETCH pcursor INTO t; 
        IF NOT done THEN -- 還有記錄
            SET total = total + t;
        END IF;
    UNTIL done END REPEAT;
    CLOSE pcursor; 
END;

CALL proc_test_cursor(@total);
SELECT @total; 
SELECT SUM(age) FROM person; 

　　　兩次查詢的結果一樣，則游標執行正常。

　　2、游標使用while

DROP PROCEDURE IF EXISTS proc_test_cursor_while;
-- id小於某個值的年齡和
CREATE PROCEDURE proc_test_cursor_while(
    IN uid INT(11),
    OUT total INT(11)
)
BEGIN
    DECLARE t INT DEFAULT 0;
    DECLARE done INT DEFAULT 0;
    DECLARE pcursor CURSOR FOR SELECT age FROM person WHERE id < uid;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; -- 在 FETCH 語句中引用的游標位置處於結果表最後一行之後。
    SET total = 0;
    OPEN pcursor; 
    WHILE(NOT done) DO
        FETCH pcursor INTO t;
        IF(NOT done) THEN
            SET total = total + t;    
        END IF;
  end WHILE;
    CLOSE pcursor; 
END;

CALL proc_test_cursor_while(3,@total);
SELECT @total;
SELECT SUM(age) FROM person where id < 3;

　　　　兩次查詢的結果一樣，則游標執行正常。

　　3、游標中使用update語句

DROP PROCEDURE IF EXISTS proc_test_cursor_update;
-- 年齡大於多少的年齡加某個數
CREATE PROCEDURE proc_test_cursor_update( 
    IN avgage INT(11)
)
BEGIN
    DECLARE num INT DEFAULT 0;
    DECLARE t INT DEFAULT 0;
    DECLARE done INT DEFAULT 0;
    DECLARE pcursor CURSOR FOR SELECT id, age FROM person;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; -- 在 FETCH 語句中引用的游標位置處於結果表最後一行之後。
    OPEN pcursor; 
    REPEAT
            FETCH pcursor INTO num, t; 
            IF NOT done THEN -- 還有記錄    
                IF t > avgage THEN -- 年齡大於傳入的年齡值
                    UPDATE person SET age = age + 5 where id = num;
                END IF;
            END IF;
    UNTIL done END REPEAT;
    CLOSE pcursor; 
END;

SET @uage = 20;
SELECT id, username, age FROM person where age > @uage;
CALL proc_test_cursor_update(@uage); 
SELECT id, username, age FROM person where age > @uage;

　　　　兩次查詢的結果一樣，則游標執行正常。

存儲過程中游標是怎使用的

ALTER proc [dbo].[存儲過程名]
as
begin
declare 游標名字 cursor for select 列名 from 表名 where 條件--先申明游標指向查詢出的結果，一列，或者多列都可以，條件自定
declare 變量名 varchar(400)--存儲取到的值
open 游標名 --開啟游標
while @@FETCH_STATUS=0--取值
begin
fetch next FROM 游標名 into 變量名--這樣就將游標指向下一行，得到的第一行值就傳給變量了
-------------------------------------------
--需要執行的操作，例如修改某表中的字段
update 表名
set 列名=值
where （修改表中的列）=變量名
-------------------------------------------
end
close 游標名--關閉游標

deallocate 游標名--釋放游標
end
 

存儲過程 臨時表 游標

准備t4表和test_t4表
t4有數據 test_t4結構和t4一樣 只是沒有數據

[TEST1@orcl#13-7月 -10] SQL>select * from t4;

ID SEX
---------- --------------------
1 男
2 女
3 太監

[TEST1@orcl#13-7月 -10] SQL>desc test_t4;
名稱 是否為空? 類型
----------------------------------------- -------- ----------------------------
ID NUMBER
SEX VARCHAR2(20)

經過測試的：

create or replace procedure p_test
is
v_sql varchar2(30000);
v_tmptable varchar2(30);
v_row t4%rowtype;
cursor c is (select * from t4);
begin
v_tmptable:='t3_tmp_t4';
v_sql:='create global temporary table '||v_tmptable||' (id number(4),sex varchar2(20))';
execute immediate v_sql;

open c;
loop
exit when c%notfound;
fetch c into v_row;
v_sql:='insert into '||v_tmptable||' values('||v_row.id||','''||v_row.sex||''')';
execute immediate v_sql;
end loop;
close c;

-- 驗證臨時表中是否有數據(該臨時表是事務級臨時表，一旦提交事務數據就被清空，所以將臨時表數據插入到實表中)

execute immediate 'insert into test_t4 select * from '||v_tmptable;
commit;
end p_test;

查看結果：
[TEST1@orcl#13-7月 -10] SQL>select * from test_t4;

ID SEX
---------- --------------------
1 男
2 女
3 太監
3 太監

這說明臨時表中的數據插入到test_t4表中了，循環有點小問題 第......余下全文>>