{"id":144,"date":"2014-12-03T21:29:49","date_gmt":"2014-12-04T02:29:49","guid":{"rendered":"http:\/\/www.dev-notes.com\/blog\/2014\/12\/03\/using-pl-sql-to-return-a-list-of-values\/"},"modified":"2014-12-03T21:29:49","modified_gmt":"2014-12-04T02:29:49","slug":"using-pl-sql-to-return-a-list-of-values","status":"publish","type":"post","link":"https:\/\/www.dev-notes.com\/blog\/2014\/12\/03\/using-pl-sql-to-return-a-list-of-values\/","title":{"rendered":"Using PL\/SQL to return a list of values"},"content":{"rendered":"<p>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.<\/p>\n<p>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.<\/p>\n<pre class=\"code\">\ncreate or replace function get_city_list(in_country_ in varchar2)\nreturn varchar2\nas\nret_ varchar2(1000);\nbegin\n\tfor rec in(select city from cities where country=in_country_) loop\n\t\tret_ := ret_ || rec.city || ',';\n\tend loop;\n\treturn ret_;\nend get_city_list;\n<\/pre>\n<p>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.<\/p>\n<pre class=\"code\">\ncreate or replace function get_city_list(in_country_ in varchar2)\nreturn sys_refcursor\nas\n\tret_ sys_refcursor\nbegin\n\topen ret_ for select city from cities where country=in_country_;\n\treturn ret_;\nend get_city_list;\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>This demo PL\/SQL function shows two different approaches to get a list of values from an Oracle database table.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[20],"tags":[],"class_list":["post-144","post","type-post","status-publish","format-standard","hentry","category-oracle"],"_links":{"self":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/posts\/144","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/comments?post=144"}],"version-history":[{"count":0,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/posts\/144\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/media?parent=144"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/categories?post=144"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/tags?post=144"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}