1. create or replace procedure cities (
2. vname in varchar2,
3. vuni out varchar2
4. ) as
5. cursor c is
6. select city_name, uni_name from uni ;
7. cx c%rowtype;
8. begin
9. select uni_name into vuni from uni;
10. open c;
11. loop
12. fetch c into cx;
13. exit when c%notfound;
14. if (vname = 'Almaty') then
15. select distinct cx.uni_name into vuni from uni where cx.city_name = vname;
16. elsif (vname = 'Nur-Sultan') then
17. select distinct cx.uni_name into vuni from uni where cx.city_name = vname;
18. elsif (vname = 'Aktau') then
19. select distinct cx.uni_name into vuni from uni where cx.city_name = vname;
20. else
21. select distinct cx.uni_name into vuni from uni where cx.city_name = vname;
22. end if;
23. end loop;
24. close c;
25. end;
Check all SELECT statement outside of cursor.
The most suspicious looks the first one at line #9:
select uni_name into vuni from uni;
Unless UNI table contains only one row, this will return too_many_rows.
Other than that, there are several SELECT DISTINCT statements. If DISTINCT doesn't do its job, then either your data is wrong, or your code is wrong. Maybe you need to add yet another condition into the WHERE clause.
Whichever statement returns the error, the simplest option is to use one of aggregate functions, such as
select max(cx.uni_name) ...
as it'll return only one value, but - that's most probably the last option you should use.
[EDIT]
Wait a minute; yes, you got various errors, but - looking more closely to your code, it doesn't make much sense. First of all, it should have been a function, not a procedure:
create or replace function f_cities (par_vname in varchar2)
return uni.uni_name%type
is
retval uni.uni_name%type;
begin
select u.uni_name
into retval
from u.uni
where u.city_name = par_vname;
return retval;
end;
If it has to be a procedure, then
create or replace procedure cities
(par_vname in varchar2,
par_vuni out varchar2
)
is
begin
select u.uni_name
into par_vuni
from u.uni
where u.city_name = par_vname;
end;
As of your code:
select statements are so strange; you're selecting a cursor variable value into the OUT parameter, from the same table cursor's select is based onifs are useless. It is the in parameter that says with city it is; you don't have to hardcode those values, as that procedure might become a real monster if there were many more cites in the tableShortly, I suggest you use a function I posted above.
[EDIT #2]
Based on your comment: there can be several universities in a city. Therefore, you can't return a scalar value but something else, such as ref cursor or an array.
Suppose this is the table you have:
SQL> create table uni
2 (city_name varchar2(20),
3 uni_name varchar2(20));
Table created.
SQL> insert into uni (city_name, uni_name)
2 select 'Almaty', 'Uni 1' from dual union all
3 select 'Almaty', 'Uni2' from dual union all
4 select 'Nur-Sultan', 'Uni 4' from dual union all
5 select 'Aktau', 'Uni 3' from dual union all
6 select 'Aktau', 'Uni 9' from dual;
5 rows created.
SQL>
Now you could do the following:
SQL> create or replace function f_uni (par_city_name in varchar2)
2 return sys.odcivarchar2list
3 as
4 rc sys.odcivarchar2list;
5 begin
6 select uni_name
7 bulk collect into rc
8 from uni
9 where city_name = par_city_name;
10 return rc;
11 end;
12 /
Function created.
SQL> select f_uni('Almaty') from dual;
F_UNI('ALMATY')
--------------------------------------------------------------------------------
ODCIVARCHAR2LIST('Uni 1', 'Uni2')
or
SQL> create or replace function f_uni (par_city_name in varchar2)
2 return sys_refcursor
3 as
4 rc sys_refcursor;
5 begin
6 open rc for select uni_name
7 from uni
8 where city_name = par_city_name;
9 return rc;
10 end;
11 /
Function created.
SQL> select f_uni('Almaty') from dual;
F_UNI('ALMATY')
--------------------
CURSOR STATEMENT : 1
CURSOR STATEMENT : 1
UNI_NAME
--------------------
Uni 1
Uni2
SQL>