PL / SQL Tables / Arrays / Table-Variables

Coloque aqui tutoriais (por enquanto, sobre qualquer assunto relacionado a Oracle) e apostilas.
Post Reply
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

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

Table variables are also known as arrays or index-by tables, pl / sql tables.

The syntax is:

Select all

   DECLARE 
   TYPE type_name IS TABLE OF 
      (column_type  | 
      variable%TYPE | 
      table.column%TYPE 
         [NOT NULL] 
            INDEX BY BINARY INTEGER;

Select all

 
-- Para declarar uma PL/SQL table faça assim: 
   nome_da_var type_name;   --> type mostrado acima

Select all

-- Setando valores para PL/SQL table: 
   nome_da_var(n).nome_do_campo := 'Seu texto'; 
-- Onde 'n' é o valor do índice
Using PL / SQL Table Methods:

Select all

Use essa sintaxe 
 
   table_name[ (parametros)] 
 
EXISTS(n)   Retorna TRUE se o n elemento existe. 
 
COUNT       Número de linhas da plsql table 
 
FIRST       Primeiro e último da tabela 
LAST        Retorna NULL se está vazia 
 
PRIOR(n)    Retorna o índice que precede n na plsql table 
 
NEXT(n)     Retorna o índice que sucede n na plsql table 
 
EXTEND(n,i) Adiciona n copias do 'i' elemento para a plsql table 
            default de i é NULL, defaults de n é 1 
 
TRIM(n)     Remove o n elemento do fim da plsql table 
            defaults de n é 1 
 
DELETE(m,n) Apaga os elementos entre m...n 
            default de m é n 
            default de n é TODOS elementos 
 
Nota Extend / Trim entraram a partir do Oracle 8.
Examples:

Select all

   DECLARE 
   -- declara o tipo 
   TYPE MyTrip_table_type IS TABLE OF 
       business_trips.bt_cost%Type 
       INDEX BY BINARY INTEGER; 
 
   --declara a TABLE variable com este tipo 
   myTrips MyTrip_table_type; 
 
   BEGIN 
      myTrips(1) := 'Teste'; 
      UPDATE business_trips 
      SET bt_cost = bt_cost * 1.2 
      WHERE bt_id_pk = myTrips(1) 
   END 
   /
Last edited by dr_gori on Sat, 20 Jan 2007 2:58 pm, edited 1 time in total.
User avatar
rcruz
Rank: Programador Pleno
Rank: Programador Pleno
Posts: 32
Joined: Tue, 04 Jan 2005 10:52 am
Location: Candido Mota

This works on forms ???? If it works solves my probelma. I need to save all the values ??of a tabular block to compose a sum, since I want to add fields that are already summarized and are not database.

Att.
Robson
marthaeloiza
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 23
Joined: Mon, 21 Jan 2008 2:07 pm
Location: São Paulo
Att
Martha Eloiza Fuchida

Hello Robson, it works yes in the forms .....
has 2 way, you declare in the triguer that you go user or create a procedure.
In the same way you use PL / SQL, you may be using Forms.

Martha Fuchida
LixX
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 25
Joined: Thu, 21 Jan 2010 4:40 pm
Location: Porto Alegre - RS

** Resuscitation topic **

I wanted to create a 2-field plane, type ...

Select all

TYPE total_table_type IS TABLE OF (VARCHAR2(100), NUMBER) INDEX BY VARCHAR2(100);
(This code does not work but determines what I would need)
the array would have 2 fields in each position ... can you do this?

[] 's
adriana.arraes
Rank: Estagiário Júnior
Rank: Estagiário Júnior
Posts: 2
Joined: Wed, 02 Jun 2010 7:04 pm
Location: São Paulo - SP
Adriana C. S. Arraes

LIXX,
to do what you need "2-field PL Table", you can first create a record that contains the two columns you need:

Select all

TYPE rec_type IS RECORD (nome VARCHAR2(100), cpf NUMBER);


And then create a table:

Select all

TYPE table_type IS TABLE OF rec_type INDEX BY VARCHAR2(100);
Example:

Select all

DECLARE 
  -- 
  TYPE rec_type IS RECORD (nome VARCHAR2(100), cpf NUMBER); 
  -- 
  TYPE table_type IS TABLE OF rec_type INDEX BY VARCHAR2(100); 
  total_table_type table_type; 
  -- 
  idx VARCHAR2(100); 
  -- 
BEGIN 
  -- 
  total_table_type('maria').nome := 'Maria do Carmo Pereira'; 
  total_table_type('maria').cpf  := '25699957800'; 
  -- 
  total_table_type('jose').nome  := 'Jose Paulo da Silva'; 
  total_table_type('jose').cpf   := '14586954201'; 
  -- 
  idx := total_table_type.FIRST; 
  -- 
  LOOP 
    -- 
    EXIT WHEN idx IS NULL; 
    -- 
    dbms_output.put_line(total_table_type(idx).nome||' - CPF '||total_table_type(idx).cpf); 
    -- 
    idx := total_table_type.next(idx); 
    -- 
  END LOOP; 
  -- 
END;

Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 6 guests