%notfound understanding of--oracle stored Procedures

Source: Internet
Author: User
Tags oracle documentation
Interpretation in document: It returns TRUE if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT into statement returned no Rows. Otherwise, it returns FALSE.
The explanation is more subtle:
%notfound is the logical opposite of%found. %notfound yields FALSE if the last fetch returned a row, or TRUE if the last fetch failed to return a row
Examples of errors:
TableA
ID Name
1 A
2 b

Declare
Cursor V_cur is select name from TableA;

n Varchar2 (10);
Begin
Open v_cur;
Loop
Exit when V_cur%notfound;
Fetch v_cur into N;

Dbms_output.put_line (n);

Close v_cur;
End Loop;
End
Execute the above statement, and the result is:
A
B
B
The last record was found to have been printed two times. The reason is%notfound is to judge the result of the last fetch, the bfetch to the variable n after the execution of exit when%notfound judgment is false demerit, that is, there is a return row, so the judge passed, and then executed the print statement.
Another question was found:
After all the a,b to the cursor has been empty, then the third should be the null value of the fetch, why print out or B?
Because the fetch. The value after the into variable is not modified at the end of the into statement. Just like Select. into if no data will report an exception, but will not put the variable after into empty
Write another piece of code

Declare
Cursor V_cur is select name from TableA where name = ' C ';

n Varchar2 (10);
Begin
Open v_cur;
Loop
Exit when V_cur%notfound;

n:= ' hehe '
Fetch v_cur into N;

Dbms_output.put_line (n);

Close v_cur;
End Loop;
End
The result of executing code:
Hehe
Question: The cursor is an empty cursor, which means that the cursor does not point to any value when it is opened. But why exit when V_cur%notfound, this statement also passed??
Explanation of Oracle Documentation:

Before the%notfound returns NULL. If FETCH never executes successfully, the loop is never exited, because the EXIT when statement Condition is true. To be safe, your might want to use the following EXIT statement instead:

EXIT when C1%notfound OR C1%notfound is NULL;

That is to say, there are three states of V_cur%notfound, True,false,null. So later for the security period can be added whether or not for null judgment

Related Article

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.