PL/SQL cursor traversing records in % rowtype

Source: Internet
Author: User
Tags oracle cursor

From http://tshfang.nipei.com/

"

How to traverse all records without entering a specific value for each query. Then we use ORACLE cursor
Cursors are classified into static cursors and referenced cursors (dynamic cursors)
Static cursor: The result set defined by the user (implicit cursor and display cursor) remains unchanged.
Reference cursor: result set changes
Implicit cursor: an implicit cursor is automatically used for DML operations. We can use an implicit cursor to determine the SQL statement execution result.
Automatically declare and process. In the session area, enable the cursor. It is automatically disabled after processing. Returns a single row query.
Implicit cursor usage:
Declare
% Notfound -- the execution row is not found.
% Found -- find the execution line
% Rowcount -- number of rows affected by the cursor
% Isopen -- whether the current cursor is open
Let's take a look at the previous article through the cursor.ArticleExample
Traverse data through loops:
1. loop when Loop

 Declare  
Cursor Mycur Is Select * From Hr.jobs;
Onerow hr.jobs % Rowtype;
Begin
Open Mycur;
Loop
Fetch Mycur Into Onerow;
Dbms_output.put_line (onerow. job_id | ' ' | Onerow. job_title );
Exit When Mycur % Notfound;
End Loop;
Close Mycur;
End ;

2. While Loop

 Declare 
Cursor Mycur Is Select * From Hr.jobs;
Onerow hr.jobs % Rowtype;
Begin
Open Mycur;
Fetch Mycur Into Onerow;
While (Mycur % Found)
Loop
Dbms_output.put_line (onerow. job_id | ' ' | Onerow. job_title );
Fetch Mycur Into Onerow;
End Loop;
Close Mycur;
End ;

3. For Loop

 Declare  
Cursor Mycur Is Select * From Hr.jobs;
Onerow hr.jobs % Rowtype;
Begin
For Onerow In Mycur Loop
Dbms_output.put_line (onerow. job_id | ' ' | Onerow. job_title );
End Loop;
End ;

The result is as follows:
Ad_pres President
Ad_vp administration vice president
Ad_asst Administration Assistant
Fi_mgr Finance Manager
Fi_account accountant
Ac_mgr Accounting Manager
Ac_account public accountant
Sa_man Sales Manager
Sa_rep Sales Representative
Pu_man Purchasing Manager
Pu_clerk purchasing clerk
St_man Stock Manager
St_clerstock clerk
Sh_clerk shipping clerk
It_prog programmer
Mk_man Marketing Manager
Mk_rep marketing representative
Hr_rep human resources representative
Pr_rep Public Relations Representative

Author: tshfang

"

If "exit when" is not used in the first method, put an if statement in the loop to put exit.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.