oracle cursor

-------- begining of the structure of test --------
/*
userId    userName
1    John1
2    John2
3    John3
4    John4
5    John5
*/

---------------- part 1 ----------------
declare
    cursor v_cursor is select * from test;
    v_record test%rowtype;

begin
    open v_cursor;
   
    fetch v_cursor into v_record;
    while v_cursor%FOUND LOOP
        dbms_output.put_line(v_record.userid || ',' ||v_record.username);
        fetch v_cursor into v_record;
    end loop;
   
    close v_cursor;
end;

-- result
/*
1,John1
2,John2
3,John3
4,John4
5,John5
*/

 

---------------- part 2 ----------------
declare
    cursor v_cursor(userId number, userName varchar2) is select userid, userName from test where userId = 1;
    v_userId test.userid%type;
    v_userName test.username%type;
   
begin
    -- 这里temp是随便输入的一个字串，
    -- 可能是由于上面游标是针对字段定义的.
    open v_cursor(1,'temp');
   
    loop
        fetch v_cursor into v_userId, v_userName;
        exit when v_cursor%NOTFOUND;
        dbms_output.put_line(v_userId || '-' || v_userName);              
    end loop;
   
    close v_cursor;
end;

---------------- part 3 ----------------
--由于不需要open cursor，因为最高效。
declare
    cursor v_cursor(userName varchar2) is select userName from test where userId = 1; 
    
begin  
    for v_temp_cursor in v_cursor('temp') loop
        dbms_output.put_line(v_temp_cursor.username);
    end loop;
end;

---------------- ISOPEN ----------------
declare
    cursor v_cursor(username varchar2) is select userName from test where userId = 1;
    v_userName test.username%type;
   
begin
    if v_cursor%ISOPEN then
       dbms_output.put_line('cursor is opened');
    else
       open v_cursor('temp');
    end if;
   
    fetch v_cursor into v_userName;
    close v_cursor;
   
    dbms_output.put_line(v_userName);
END;

---------------- ROWCOUNT ----------------
declare   
    cursor v_cursor(username varchar2) is select userName from test where userId > 1;
    v_userName test.username%type;

begin
    open v_cursor('temp');
   
    loop
        fetch v_cursor into v_userName;
        exit when v_cursor%NOTFOUND OR v_cursor%NOTFOUND IS null;
        dbms_output.put_line('record number is '||v_cursor%ROWCOUNT);
    end loop;
   
    close v_cursor;
END;

---------------- 更新数据 ----------------
declare
    cursor v_cursor is select username from test for update;
    v_userName test.username%type;

begin
    open v_cursor;
   
    fetch v_cursor into v_userName;
    while v_cursor%FOUND Loop
        update test set username =  v_userName || userId where current of v_cursor;
        fetch v_cursor into v_userName;
    end loop;
   
    close v_cursor;
end;

---------------- 隐式游标 ----------------
begin
    for v_cursor in(select username from test) loop
        dbms_output.put_line(v_cursor.username);
    END LOOP;
end;

转载于:https://www.cnblogs.com/aspsmile/archive/2008/08/28/1278499.html