CODEWITHSUNDEEP
parametersplsqlprocedure
bombinabirdcage
bombinabirdcage

Reputation: 11

PL/SQL Procedure with parameter

I'm dead tired and my assignment is due in seven hours. I need a kind soul(s) to help me!

So this is my first PL/SQL class. We are starting work on Procedures and Functions.

So here is the one I am stuck on. The question says:

This question uses the wf_countries table.

A. Create a procedure that accepts a country_id as a parameter and displays the name of the country and its capitol city. Name your procedure get_country_info. Save your procedure definition for later use.

B. Execute your procedure from an anonymous block, using country_id 90.

C. Re-execute the procedure from the anonymous block, this time using country_id 95. What happens?

D. Retrieve your procedure code from Saved SQL and modify it to trap the NO_DATA_FOUND exception in an exception handler. Re-execute the procedure using country_id 95 again. Now what happens?

Here is what I wrote for A:

CREATE OR REPLACE PROCEDURE get_country_info
(p_id IN wf_countries.country_id%TYPE,
p_capitol OUT wf_countries.capitol%TYPE,
p_name OUT wf_countries.country_name%TYPE)
IS
BEGIN
SELECT capitol, country_name 
INTO p_capitol, p_name
FROM wf_countries
WHERE country_id=p_id;
END;

I got it to run. But then I realized that if I try to call the procedure by typing:

BEGIN
get_country_info
END;

that I need the country ID. Duh. But I can't just put get_country_info(90) because it is expecting more parameters. I only need to use the country ID as the parameter and DISPLAY the name and capitol.

I'm really frustrated and appreciate any help that you might offer.

Upvotes: 0

Views: 1456

Answers (3)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

Try this:

CREATE OR REPLACE PROCEDURE get_country_info
  (p_id  IN  wf_countries.country_id%TYPE)
IS
  vCapitol  wf_countries.capitol%TYPE;
  vName     wf_countries.country_name%TYPE;
BEGIN
  SELECT capitol,
         country_name 
    INTO vCapitol,
         vName
    FROM wf_countries
    WHERE country_id = p_id;

  DBMS_OUTPUT.PUT_LINE('Name='  || vName || '  Capitol=' || vCapitol);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No data found for COUNTRY_ID=' || p_id);
END;

Upvotes: 0

Tomás
Tomás

Reputation: 555

CREATE OR REPLACE PROCEDURE get_country_info
    (p_id IN wf_countries.country_id%TYPE,
    oCur OUT SYS_REFCURSOR
)
IS
BEGIN
    OPEN oCur FOR
    SELECT capitol, country_name 
      FROM wf_countries
     WHERE country_id=p_id;
END;

Then, when calling:

DECLARE retCursor SYS_REFCURSOR;
BEGIN
    get_country_info(123, retCursor);
END;
/

Upvotes: 0

dariyoosh
dariyoosh

Reputation: 614

According to your problem specification: get_country_info should take only one parameter: country_id.

  • Remove the other two parameters and declare them as local variables inside your procedure

  • For displaying the value, use DBMS_OUTPUT.PUT_LINE

If this is run in SQL*Plus don't forget to run first:

SET SERVEROUTPUT ON

Also for the exception part, have a look at Error Handling

Upvotes: 1

Related Questions