Loop in pl/sql oracle

Question

I want to know how to go through all the tuples that are stored in a table; how do I do this in PL/SQL Oracle?

From now on, thank you!

Answer 1

I understand you want to wear one cursor. In PL/SQL you can do using FOR.
Suppose you have a table with the Code and Name fields, an example would look like this:

FOR i IN (SELECT Codigo, Nome FROM Tabela)
LOOP
    /*Aqui é possível ler cada campo da tupla usando a variável "i"*/
    DBMS_OUTPUT.PUT_LINE(i.Codigo||' '||i.Nome);
END LOOP;

Answer 2

You can create cursors in two ways:

Explicit cursor:

DECLARE
  --
  CURSOR cr_produto(pr_categoria IN produtos.id_categoria%TYPE) IS
    SELECT produto, preco
      FROM produtos
     WHERE (id_categoria = pr_categoria OR pr_categoria = 0)
     ORDER BY preco DESC;
  rw_produto cr_produto%ROWTYPE;

BEGIN
  FOR rw_produto IN cr_produto(pr_categoria => 0) LOOP
    dbms_output.put_line( rw_produto.nome || ': $' ||  rw_produto.preco );
  END LOOP;
END;

In this example, the SELECT Cursor retrieves data from table products. Instruction FOR LOOP was opened, searched each row in the result set, displayed the product information and closed the cursor.

The following example is equivalent to the above example, called implicit cursor, but uses an internal query in instruction FOR LOOP.

BEGIN
  DECLARE
    vr_categoria produtos.id_categoria%TYPE;
  BEGIN

    vr_categoria := 0;

    FOR rw_produto IN (
          SELECT produto, preco
            FROM produtos
           WHERE (id_categoria = vr_categoria OR vr_categoria = 0)
           ORDER BY preco DESC
      )
    LOOP
       dbms_output.put_line( rw_produto.nome || ': $' ||  rw_produto.preco );
    END LOOP;
  END;
END;

As you can imagine, both have their advantages and disadvantages, in the case of explicit cursor, you can make use in several situations throughout the package, Procedure or Function, without having to assemble a select to each loop of your program. On the other hand, in simple routines, where the same cursor will not be used more than once, you can use the implicit form.