CODEWITHSUNDEEP
sqlplsqlerror-handlingprocedurepls-00103
Kristina
Kristina

Reputation: 15

Text parameter in procedure?

I have one task to solve.

Create a procedure that will list all branches from the locations table based on the specified country_name from the countries table. The listing will be country_name, city, street_address, state_province.Treat the case if there is no branch in the country.Call the procedure in PL / SQL for ‘Zambia‘.

I wrote this, but I have a mistake somewhere. Can you please help me? Thank you.

SET SERVEROUTPUT ON;
CREATE OR REPLACE PROCEDURE POBOCKY(C_NAME IN COUNTRIES.COUNTRY_NAME VARCHAR2(50)%TYPE)
IS CITY_P LOCATIONS.CITY VARCHAR2(30)%TYPE;
S_ADDRESS LOCATIONS.STREET_ADDRESS VARCHAR2(40)%TYPE;
S_PROVINCE LOCATIONS.STATE_PROVINCE VARCHAR2(25)%TYPE;
BEGIN
SELECT COUNTRY_NAME, CITY, STREET_ADDRESS, STATE_PROVINCE INTO
C_NAME, CITY_P, S_ADDRESS, S_PROVINCE FROM COUNTRIES
FULL OUTER JOIN LOCATIONS ON COUNTRIES.COUNTRY_ID = LOCATIONS.COUNTRY_ID
GROUP BY COUNTRY_NAME;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line('Error');
RAISE;
END POBOCKY;

Upvotes: 0

Views: 303

Answers (1)

Littlefoot
Littlefoot

Reputation: 142713

  • if you're inheriting datatypes from columns, then remove explicit datatype
  • if parameter is IN, you can't select into it
  • full outer join? Why? Shouldn't it be inner join ...
    • ... along with WHERE clause so that you'd filter rows by city name passed as a parameter?
  • if there are several rows that can be returned, consider using a loop
    • it also means that you don't have to worry about local variables and (most frequent) exceptions such as no_data_found and too_many_rows
  • it is a good habit to use table aliases and precede column names with them; the way you put it, it is impossible to know which column belongs to which table (so I won't be guessing)

Shortly, something like this:

create or replace procedure pobocky
  (c_name in countries.country_name%type)
is 
begin
  for cur_r in 
    (select city, street_address, state_province 
     from countries join locations on countries.country_id = locations.country_id
     where coutry_name = c_name
    )
  loop
    dbms_output.put_line(c_name ||', '|| cur_r.city ||', '|| cur_r.street_address ||', '||
      cur_r.state_province);
  end loop;
end pobocky;

Upvotes: 1

Related Questions