Variable of type% ROWTYPE in a join of 2 or more tables

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 Fri, 18 Nov 2005 3:25 pm

Good afternoon guys, I wonder if someone could solve a problem.

For example, if I have a select that does a join on 3 tables, and I want to declare a variable of type% ROWTYPE to me to play the result of select. How can I do?!

With a table I know it's possible, but when you have more than 1 table?!
LC_JK
Location: São Paulo

Poston Fri, 18 Nov 2005 3:41 pm

See if this example doesn't help: about51.html I just created an equal using a JOIN in the cursor. Works beleza!

See my example:
Code: Select all
DECLARE

  --declara o cursor desejado
  CURSOR cur_emp
  IS
  select cim_processos.nro_proc,
       cim_processos.seq_proc,
       cim_processos.ncm,
       cim_faturas.vlr_ini_me,
       cim_faturas.vlr_ini_mn
  from cim_processos x,
       cim_faturas   y
where cim_processos.nro_proc               = y.proc_nro_proc
       and x.seq_proc                       = Y.proc_seq_proc
       and x.regs_codigo_regiao_do_sistema  = Y.pregs_codigo_regiao_do_sistema
       and x.regemgr_sigla_empresa_do_grupo = Y.regemgr_sigla_empresa_do_grupo
       and x.uneg_plta_sigla                = Y.proc_uneg_plta_sigla
       and x.uneg_negc_empg_sigla           = Y.proc_uneg_negc_empg_sigla
       and x.uneg_negc_sigla                = Y.proc_uneg_negc_sigla;


  --declara o array que conterá a linha toda
  rec_emp cur_emp%ROWTYPE;

BEGIN
  --Pega a linha desejada e grava no array
  OPEN cur_emp;
  FETCH cur_emp INTO rec_emp;
  CLOSE cur_emp;

  DBMS_OUTPUT.PUT_LINE( rec_emp.VLR_INI_ME );
END;
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, 18 Nov 2005 4:05 pm

Ah.. Sorry, I missed a detail!!

But what if this select were inside a dynamic sql? that is, if this sql was being assembled within an execute immediate.
LC_JK
Location: São Paulo


  • See also
    Replies
    Views
    Last Post


    Return to PL/SQL

    Who is online

    Users browsing this forum: No registered users and 13 guests