Re: PL/SQL help needed

James Garrison <jrg_at_idworld.net>ÀÌ(°¡) ¾Æ·¡ ¸Þ½ÃÁö¸¦ news:379a574b.429212902_at_news.idworld.net¿¡ °Ô½ÃÇÏ¿´½À´Ï´Ù.
> In my declare section I've defined a cursor. If I cut and paste the
> select statement into SQLPLUS it returns the correct number of rows.
> When I run my script opening the cursor with a FOR..IN..LOOP it
> returns every row in the table. I'm trying to use
> DBMS_OUTPUT.PUT_LINE statements to diagnose the error but am unable to
> get it to work correctly. Here's an example:
>
> Delcare
>
> CURSOR some_policies IS
> SELECT p.policy_number, p.policy_date_time
> FROM policy p, register_cur rc
> WHERE p.policy_number = rc.policy_number
> AND p.renewal_code = 1
> AND rc.status_1 = 5;
>
> **********Running the above select statement from the sql> command
> line correctly returns 2 rows*************
>
> BEGIN
> FOR policy_number IN some_policies LOOP
> UPDATE policy p
> SET non_renewal_reason = 'I'
> WHERE p.policy_number = policy_number
> AND P.policy_date_time = policy_date_time;
> COMMIT;
>

BEGIN
FOR p1 IN some_policies LOOP
 UPDATE policy
 SET NON_RENEWAL_REASON = 'I'
 WHERE POLICY_NUMBER = P1.POLICY_NUMBER  AND POLICY_DATE_TIME = P1.POLICY_DATE_TIME; END LOOP;
COMMIT;
> *********This updates every row in the table!************
>
> DBMS_OUTPUT.PUT_LINE (SQL%ROWCOUNT || ' policies.');
>
> **This gives a row count of every row in the table! It should be 2***
>
> DBMS_OUTPUT.PUT_LINE ('Policy number ' || v_policy_no || ' .');
>
> ******How can I map policy number into v_policy_no????*************
>
> END LOOP;
> END;
>
> I have two problems: 1. Why is every row getting returned when I open
> the cursor. 2. How can I display the policy numbers affected by the
> update.
>
> TIA. Please help asap, let me know if you need more detail on the
> problem. The script is much longer than this snippet.
>
Received on Sun Jul 25 1999 - 11:29:37 CDT