Recursion

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
  

Poston Tue, 06 Jul 2004 5:43 pm

Hello everyone.

I have a question? Can't perform recursion in PL/SQL?

I appreciate if someone can answer me.

Thank you.
alexandreizumi

Poston Wed, 07 Jul 2004 8:23 am

We test and we're about to find out:
Code: Select all
SQL> select * from a;

no rows selected
IE, nothing in my table.

Code: Select all
SQL> create or replace procedure rec (n number) is
  2  begin
  3    insert into a(nome) values (to_char(n));
  4    if n>0
  5    then rec(n-1);
  6    end if;
  7  end;
  8  /

Procedure created.

SQL> exec rec(5);

PL/SQL procedure successfully completed.

SQL> select * from a;

NOME     
----------
5
4
3
2
1
0

6 rows selected.

SQL>
As you can see, the PL/SQL supports recursion. Called the very procedure to insert the rows in the table.
dr_gori
Location: Portland, OR USA

Thomas F. G

Você já respondeu a dúvida de alguém hoje?
http://glufke.net/oracle/search.php?search_id=unanswered

Poston Fri, 26 Aug 2011 9:40 am

/*//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////Implementing A Recursive Solution in Oracle to get data from a table autorelacionada//////set up a table to show the example based on a hipotetica data structure but the solution and able to be applied in///several areas where necessary is made using tables autorelacionadas as product structures, hierarquicos among many other menus///solutions//////Author: Julio.Cesar.Eyras@gmail.com/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////Product Structure (or in English: Bill of materials (BOM)) can be a list of raw materials, pre-assembled,///sub-components, components or parts, and required by each to manufacture a product.
///Can be used for communication between business partners or by simple organisation of a project in order to carry out///a preliminary storage.
///Can be set according to the design intent, varying their types of structure, containing since proposals///focus on engineering or ordering by price, up to a resolution of maintenance. In the industrial process, she is also///known as a formula, recipe or list of ingredients. In electronics, represents a list of components used///in a printed circuit board. Once the project is completed, the GOOD is passed to for shopping or team engineers///, as in the case of electronics, which will get certain items.
///A GOOD is of course, with the upper level hierarchical representing the completed product, they can also be a byproduct///or him.
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////*/--Creating the hierarchical table of simplified form only to demonstrate the concepts involved--Drop table production.BillOfMaterials table;
CREATE TABLE production.BillOfMaterials table (id CHAR (32), id_father (32), description VARCHAR2 (100) NOT NULL, PRIMARY KEY (id) ENABLE NOVALIDATE)--Populating the basic registrant-Truncate table production.BillOfMaterials table;
INSERT INTO production.BillOfMaterials table (id, id_father, description) VALUES (' 1 ', NULL, ' Chinese ' Calhambeke);
INSERT INTO production.BillOfMaterials table (id, id_father, description) VALUES (' 2 ', 7, ' 1.0 ' Engine);
INSERT INTO production.BillOfMaterials table (id, id_father, description) VALUES (' 3 ', 16, ' Door ');
INSERT INTO production.BillOfMaterials table (id, id_father, description) VALUES (' 4 ', 3, ' Padding ' port);
INSERT INTO production.BillOfMaterials table (id, id_father, description) VALUES (' 5 ', 3, ' doorknob ');
INSERT INTO production.BillOfMaterials table (id, id_father, description) VALUES (' 6 ', 3, ' port Glasses ');
INSERT INTO production.BillOfMaterials table (id, id_father, description) VALUES (' 7 ', 1, ' Chassis ');
INSERT INTO production.BillOfMaterials table (id, id_father, description) VALUES (' 8 ', 7, ' Structural Bars ');
INSERT INTO production.BillOfMaterials table (id, id_father, description) VALUES (' 9 ', 7, ' ' wheel shafts);
INSERT INTO production.BillOfMaterials table (id, id_father, description) VALUES (' 10 ', 16, ' Run ');
INSERT INTO production.BillOfMaterials table (id, id_father, description) VALUES (' 11 ', 10, ' Alloy Wheel ');
INSERT INTO production.BillOfMaterials table (id, id_father, description) VALUES (' 14 ', 16 ', Front Glass ');
INSERT INTO production.BillOfMaterials table (id, id_father, description) VALUES (' 15 ', 16, ' Rear Window ');
INSERT INTO production.BillOfMaterials table (id, id_father, description) VALUES (' 16 ', 1, ' Body ');
INSERT INTO production.BillOfMaterials table (id, id_father, description) VALUES (' 12 ', 10, ' Tire 175 ');
INSERT INTO production.BillOfMaterials table (id, id_father, description) VALUES (' 13 ', 10, ' ' wheel screws);

Select * from production.BillOfMaterials table;

--A strange shape SELECT * FROM (SELECT id, id_father, description, SYS_CONNECT_BY_PATH column (id, '. ') tree, CONNECT_BY_ROOT id children FROM production.BillOfMaterials table CONNECT BY PRIOR id_father = id) WHERE id = 1--getting the hierarchically structured data.
SELECT id, id_father, description FROM production.BillOfMaterials table CONNECT BY PRIOR id = id_father START WITH id = 1- -0 By J.C.E.



* Avoid waste, but if you need, you can print the contents of this email quiet! * paper is biodegradable, renewable and comes from planted forests.
These forests are crops that give employment to thousands of people.
The trees planted are fighting the greenhouse effect, because they absorb carbon dioxide during their growth.
* Print is to give life!* Learn more: http://www.imprimiredarvida.org.br
jce
Location: porto alegre - rs



Return to PL/SQL

Who is online

Users browsing this forum: No registered users and 7 guests