Hi,<\/p>\n
<\/p>\n
Other structures when programming in PL\/SQL are the FOR LOOP<\/strong> and CURSOR<\/strong>. In this post, I’m going to show how to use both of them together.<\/p>\n Well, there are plenty of ways to create a loop in PL\/SQL, one of the most common using an implicit cursor<\/strong>. This is encouraged when the query is not to much complex. I like it because it is very simple, I don’t need to worry to open, fetch and close each cursor. Everything is controlled by the database engine.<\/p>\n <\/p>\n When using an implicit cursor, you don’t have to OPEN CURSOR, FETCH CURSOR and CLOSE CURSOR. When the loop is finished, the current cursor is over automatically.<\/p>\n <\/p>\n Please, use the file create_and_populate_tables_city_and_country<\/a> file to create and populate the tables used in this article<\/p>\n Result<\/strong><\/p>\n <\/p>\n It is also possible to add dynamic values to filter your data in the query inside of the cursor. Check out the following example.<\/p>\n Result<\/strong><\/p>\n <\/p>\n Well, this is pretty much enough to you write your own implicit cursors. Many more could be written, but I’m trying to keep these posts as simple as possible to be practical.<\/p>\n <\/p>\n This post is part of a series about the most common structures in PL\/SQL. You can check the rest of them in the link below.<\/p>\n PL\/SQL for real world – 8 features that you have to know<\/a><\/p><\/blockquote>\nThe basic structure<\/h2>\n
FOR <Loop_index> IN (<Select_statement>)\r\n\r\nLOOP\r\n\r\n <Executable_statements>;\r\n\r\nEND LOOP;<\/pre>\n
Example<\/h2>\n
BEGIN\r\n\r\nFOR C1 IN (select ci.name as name\r\n from city ci\r\n , country co\r\n where ci.country_id = co.id\r\n and co.iso2 = 'BR')\r\nLOOP\r\n\r\n dbms_output.put_line(C1.name);\r\n\r\nEND LOOP;\r\n\r\nEND;<\/pre>\n
\n\n
\n S\u00e3o Paulo
\nRio de Janeiro
\nBelo Horizonte
\nPorto Alegre
\nBras\u00edlia
\nRecife
\nFortaleza
\nSalvador
\nCuritiba
\nCampinas
\nBel\u00e9m
\nGoi\u00e2nia
\nManaus
\nSantos
\nVit\u00f3ria
\nNiter\u00f3i
\nVila Velha
\nVila Velha
\nMacei\u00f3
\nNatal<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\nDynamic values<\/h2>\n
DECLARE\r\n ISO VARCHAR2(3);\r\nBEGIN ISO := 'BO'; \r\n FOR C1 IN (select ci.name as name \r\n from city ci ,\r\n country co \r\n where ci.country_id = co.id and co.iso2 = ISO) \r\n LOOP \r\n dbms_output.put_line(C1.name); \r\n END LOOP; \r\nEND;<\/pre>\n
\n\n
\n Santa Cruz
\nLa Paz
\nSucre
\nCochabamba
\nOruro
\nSanta Ana de Yacuma
\nQuillacollo
\nPotos\u00ed
\nTarija
\nTrinidad
\nCobija
\nSanta Cruz de la Sierra
\nMontero
\nRiberalta
\nVillaz\u00f3n
\nBermejo
\nGuayaramer\u00edn<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n