遊標，遊標是什麼

遊標，遊標是什麼

declare  
      object_name varchar2(24);
      type ref_cursor_type is ref cursor;
      cursor_object  ref_cursor_type;
begin
      
      open cursor_object for
      '
        select regexp_substr(''3h,4c,5e,6b,7a,8g'', ''[^,]+'', 1, level) a
        from dual
        connect by level <= regexp_count(''3h,4c,5e,6b,7a,8g'', '','') + 1
      ';
  
   loop
        begin
            fetch  cursor_object into object_name;
                  exit when cursor_object%notFound;

            if  object_name = '4c' then
               dbms_output.put_line('good:'||object_name);
            end if;
        end;
    end loop;
end;

SQL遊標怎使用

A. 在簡單的遊標中使用 FETCH
下例為 authors 表中姓以字母 B 開頭的行聲明了一個簡單的遊標，並使用 FETCH NEXT 逐個提取這些行。FETCH 語句以單行結果集形式返回由 DECLARE CURSOR 指定的列的值。

USE pubs
GO
DECLARE authors_cursor CURSOR FOR
SELECT au_lname FROM authors
WHERE au_lname LIKE "B%"
ORDER BY au_lname

OPEN authors_cursor

-- Perform the first fetch.
FETCH NEXT FROM authors_cursor

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM authors_cursor
END

CLOSE authors_cursor
DEALLOCATE authors_cursor
GO

au_lname
----------------------------------------
Bennet
au_lname
----------------------------------------
Blotchet-Halls
au_lname
----------------------------------------

B. 使用 FETCH 將值存入變數
下例與上例相似，但 FETCH 語句的輸出儲存於局部變數而不是直接返回給用戶端。PRINT 語句將變數組合成單一字串並將其返回到用戶端。

USE pubs
GO

-- 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, @a......餘下全文>>
 
SQL遊標怎使用

A. 在簡單的遊標中使用 FETCH
下例為 authors 表中姓以字母 B 開頭的行聲明了一個簡單的遊標，並使用 FETCH NEXT 逐個提取這些行。FETCH 語句以單行結果集形式返回由 DECLARE CURSOR 指定的列的值。

USE pubs
GO
DECLARE authors_cursor CURSOR FOR
SELECT au_lname FROM authors
WHERE au_lname LIKE "B%"
ORDER BY au_lname

OPEN authors_cursor

-- Perform the first fetch.
FETCH NEXT FROM authors_cursor

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM authors_cursor
END

CLOSE authors_cursor
DEALLOCATE authors_cursor
GO

au_lname
----------------------------------------
Bennet
au_lname
----------------------------------------
Blotchet-Halls
au_lname
----------------------------------------

B. 使用 FETCH 將值存入變數
下例與上例相似，但 FETCH 語句的輸出儲存於局部變數而不是直接返回給用戶端。PRINT 語句將變數組合成單一字串並將其返回到用戶端。

USE pubs
GO

-- 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, @a......餘下全文>>