Search the Site

Sponsors

bottom corner

Using PL/SQL to return a list of values

This demo PL/SQL function shows two different approaches to get a list of values from an Oracle database table.

This page is filed under keyword(s): oracle.

We can use an Oracle PL/SQL function to retrieve a list of values from the database. In this article, we will look at two sample methods, one of which returns a character string (ie. varchar2) while the other returns a record set (ie. sys_refcursor). Our application will determine which method is best in our implementation.

In the first example below, we use a for-loop to iterate through a select statement. In each iteration, we concatenate the new data into a varchar2 variable. Finally, at the end of the function, we return the said variable.

create or replace function get_city_list(in_country_ in varchar2)
return varchar2
as
ret_ varchar2(1000);
begin
	for rec in(select city from cities where country=in_country_) loop
		ret_ := ret_ || rec.city || ',';
	end loop;
	return ret_;
end get_city_list;

Alternatively, we can have the function return us a record set. To do so, we declare a sys_refcursor variable, and then in the body of the function we assign a SQL statement for it.

create or replace function get_city_list(in_country_ in varchar2)
return sys_refcursor
as
	ret_ sys_refcursor
begin
	open ret_ for select city from cities where country=in_country_;
	return ret_;
end get_city_list;

Did you find this page useful? Please consider browsing other articles or subscribing to the RSS feed to keep up with latest.

This page is filed under keyword(s): oracle.
Author: C. Peter Chen
Last updated: 3 Dec 2014

bottom corner