CODEWITHSUNDEEP
oracle-databaseplsql
Jane Liu
Jane Liu

Reputation: 61

Beginner PL/SQL script. Does this code look correct?

I'm starting to learn PL/SQL and don't have access to the database currently (so can't check if my code is correct). I'm trying to print out a student's name based on their ID number. So far I have:

SET SERVEROUTPUT ON
DECLARE
  VNAME STUDENT.NAME%TYPE;
BEGIN
  SELECT NAME INTO VNAME
  FROM STUDENT
  WHERE ID = '0123';
  IF SQL%FOUND THEN
  DBMS_OUTPUT.PUT_LINE('Student with ID "0123" has the name ' || VNAME);
  END IF;
END;
/

Does the syntax look correct? It should generate the output "Student with ID 0123 has the name ____"

Upvotes: 0

Views: 89

Answers (2)

Arnab Bhagabati
Arnab Bhagabati

Reputation: 2725

Try SQLFiddle if you donot have access to a database. You can check your queries over there online.

Upvotes: 1

mrjoltcola
mrjoltcola

Reputation: 20842

Try using an exception handler, as pointed out in comments, your IF won't work as you expect.

http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/errors.htm#LNPLS856

SET SERVEROUTPUT ON
DECLARE
  VNAME STUDENT.NAME%TYPE;
BEGIN
 SELECT NAME INTO VNAME
 FROM STUDENT
 WHERE ID = ’0123’;
 DBMS_OUTPUT.PUT_LINE('Student with ID "0123" has the name ' || VNAME);
EXCEPTION
 WHEN NO_DATA_FOUND THEN
   DBMS_OUTPUT.PUT_LINE ('No student record found for that id '); 
END;
/

Since you don't have access to the DB, here is what your original script will do:

DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4

With the exception handler, in SQLPLUS you'll get:

No student record found for that id

PL/SQL procedure successfully completed.

SQL>

Upvotes: 2

Related Questions