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

核心提示： 8、示例：-- Declare the variables to store the values returned by FETCH.DECLARE @au_lname varchar(40), @au_fname varchar(20)DECLARE authors_cursor CU

8、示例：

-- Declare the variables to store the values returned by FETCH.
DECLARE @au_lname varchar(40), @au_fname varchar(20)
DECLARE authors_cursor CURSOR FOR
SELECT au_lname, au_fname
FROM authors
WHERE au_lname LIKE ‘B%’
ORDER BY au_lname, au_fname
OPEN authors_cursor
-- Perform the first fetch and store the values in variables.
-- Note: The variables are in the same order as the columns
-- in the SELECT statement.
FETCH NEXT FROM authors_cursor INTO @au_lname, @au_fname
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- Concatenate and display the current values in the variables.
PRINT "Author: " + @au_fname + " " + @au_lname
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM authors_cursor INTO @au_lname, @au_fname
END
CLOSE authors_cursor
DEALLOCATE authors_cursor

<2>、ORACLE端语法说明

1、 DECLARE CURSOR语句

语法：

CURSOR cursor_name IS select_statement;

例：

CURSOR authors_cursor IS
SELECT au_lname, au_fname
FROM authors
WHERE au_lname LIKE ‘B%’
ORDER BY au_lname, au_fname;

2、 OPEN语句

语法：

OPEN cursor_name

例：

OPEN authors_cursor;