0

We're learning pl sql in class, and I was just wondering if anyone noticed any glaringly obvious errors in my code?

--Gets the phone number of a specific member
set SERVEROUTPUT on
DECLARE
  phone_number  Member_T.Phone%TYPE;
BEGIN
  SELECT Phone
    INTO phone_number
    FROM Member_T
   WHERE MemberCardNumber= 2436322287;

  DBMS_OUTPUT.put_line (phone_number);
END; 


--Uses a cursor to select the members with the top 3 highest account balances
set SERVEROUTPUT on
DECLARE
   CURSOR b_cursor is
      SELECT AccountID, Balance FROM Account_T
         ORDER BY Balance DESC;
         v_member    Account_T.AccountID%type;
         v_balance   Account_T.Balance%type;  
BEGIN
   OPEN b_cursor;
   FOR i IN 1..3 LOOP
      FETCH b_cursor INTO v_accountID,v_balance;
      EXIT WHEN b_cursor%NOTFOUND; 
      INSERT INTO temp VALUES (v_accountID,v_balance);
      COMMIT;
   END LOOP;
   CLOSE b_cursor;
END;
2
Contributors
1
Reply
30
Views
3 Years
Discussion Span
Last Post by jwenting
0

except for the terminal output, you don't usually want that...

At line 6, you're not guarding against the situation where there is no result or more than one result.
This can cause either of 2 errors:
ORA-01422 exact fetch returns more than requested number of rows
ORA-01403 no data found

You might want that, and handle the exception condition in your calling program.
But more likely you'll want to return some specific value, especially in case of NO_DATA_FOUND it's often appropriate to return NULL.

A typical construction would then become:

DECLARE
phone_number Member_T.Phone%TYPE;
BEGIN
SELECT Phone
INTO phone_number
FROM Member_T
WHERE MemberCardNumber= 2436322287;
return phone_number;
exception no_data_found
    return NULL;
END; 
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.