从SQL SERVER 向ORACLE 8迁移的技术实现方案

核心提示： 3、 FETCH语句语法：FETCH cursor_name INTO variable_name1 [, variable_name2,… ] ;例：FETCH authors_cursor INTO au_lname, au_fname;4、 CLOSE语句语法：CLOS

3、 FETCH语句

语法：

FETCH cursor_name INTO variable_name1 [, variable_name2,… ] ;

例：

FETCH authors_cursor INTO au_lname, au_fname;

4、 CLOSE语句

语法：

CLOSE cursor_name

例：

CLOSE authors_cursor;

5、简单游标提取循环结构与循环终止条件判断

1> 用%FOUND做循环判断条件的WHILE循环

（1）FETCH authors_cursor INTO au_lname, au_fname ；

（2）WHILE authors_cursor%FOUND LOOP

-- Concatenate and display the current values in the variables.
DBMS_OUTPUT.ENABLE;
DBMS_OUTPUT.PUT_LINE( ‘Author: ‘ || au_fname || ‘ ‘ || au_lname) ;
FETCH authors_cursor INTO au_lname, au_fname ；
END LOOP ;

（3）CLOSE authors_cursor ;

2> 用%NOTFOUND做循环判断条件的简单LOOP...END LOOP循环

（1）OPEN authors_cursor;

（2）LOOP

FETCH authors_cursor INTO au_lname, au_fname ；
-- Exit loop when there are no more rows to fetch.
EXIT WHEN authors_cursor%NOTFOUND ;
-- Concatenate and display the current values in the variables.
DBMS_OUTPUT.ENABLE;
DBMS_OUTPUT.PUT_LINE( ‘Author: ‘ || au_fname || ‘ ‘ || au_lname) ;
END LOOP ;

（3）CLOSE authors_cursor ;

3>用游标式FOR循环,如下：

DECLARE
CURSOR c_HistoryStudents IS
SELECT id,first_name,last_name
FROM Students
WHERE major = ‘History’
BEGIN
FOR v_StudentData IN c_HistoryStudents LOOP
INSERT INTO registered_students
(student_id,first_name,last_name,department,course)
VALUES(v_StudentData.ID,v_StudentData.first_name, v_StudentData.last_name,’HIS’,301);
END LOOP;
COMMIT;
END;