Menu de navegação

[rwarstatParticipante]

Pessoal,
Estou com o seguinte problema: fiz a procedure abaixo e preciso que ela retorne ou uma tabela ou um cursor. Essa procedure será fornecida para um cliente, que irá utilizar ou chamado direto no banco ou através de Forms.

Andei dando uma lida em alguns materiais mas não consegui entender como faria isso.

CREATE OR REPLACE PROCEDURE pr_teste (p_cd_paciente IN VARCHAR2 DEFAULT '0' ,
p_cd_procedimento IN VARCHAR2 DEFAULT '0' ,
p_dt_ini IN VARCHAR2 DEFAULT '0' ,
p_dt_fim IN VARCHAR2 DEFAULT '0' ,
p_sexo IN VARCHAR2 DEFAULT 'T' ,
p_cd_dest_origem IN NUMBER DEFAULT 0 ,
p_idade_ini IN NUMBER DEFAULT 0 ,
p_idade_fim IN NUMBER DEFAULT 0 )
AS
BEGIN
FOR cur_exame
IN (SELECT se.cd_credenciado,
se.nr_atendimento,
se.dt_ano_atend,
se.dt_atendimento,
sei.cd_procedimento,
p.ds_procedimento,
vpl.ds_variavel_pre_laudo,
vrl.result_variavel,
se.cd_paciente,
datediff ('YYYY', pa.dt_nascimento, se.dt_atendimento) idade,
pa.sexo
FROM variavel_resultado_laudo vrl
INNER JOIN
variavel_pre_laudo vpl
ON vrl.lau_cd_credenciado = vpl.cd_credenciado
AND vrl.cd_variavel_pre_laudo = vpl.cd_variavel_pre_laudo
INNER JOIN
laudo l
ON vrl.lau_cd_credenciado = l.cd_credenciado
AND vrl.nr_atendimento = l.nr_atendimento
AND vrl.dt_ano_atend = l.dt_ano_atend
AND vrl.nr_sequencia = l.nr_sequencia
AND vrl.nr_seq_laudo = l.nr_seq_laudo
INNER JOIN
sol_exame_item sei
ON l.cd_credenciado = sei.cd_credenciado
AND l.nr_atendimento = sei.nr_atendimento
AND l.dt_ano_atend = sei.dt_ano_atend
AND l.nr_sequencia = sei.nr_sequencia
INNER JOIN
procedimento p
ON sei.cd_procedimento = p.cd_procedimento
INNER JOIN
sol_exame se
ON se.cd_credenciado = sei.cd_credenciado
AND se.nr_atendimento = sei.nr_atendimento
AND se.dt_ano_atend = sei.dt_ano_atend
INNER JOIN
paciente pa
ON se.cd_paciente = pa.cd_paciente
WHERE se.dt_ano_atend = 2010
AND (se.nr_atendimento BETWEEN 57343 AND 57397 OR se.nr_atendimento = 38)
AND (p_cd_paciente = '0'
OR se.cd_paciente IN
(SELECT TO_NUMBER (TRIM (token))
FROM (SELECT SUBSTR (
t.separador || t.str || t.separador,
INSTR (t.separador || t.str || t.separador,
t.separador,
1,
LEVEL)
+ 1,
INSTR (t.separador || t.str || t.separador,
t.separador,
1,
LEVEL + 1)
- INSTR (t.separador || t.str || t.separador,
t.separador,
1,
LEVEL)
- 1
)
token
FROM (SELECT p_cd_paciente str, ',' separador FROM DUAL)
t
CONNECT BY LEVEL < LENGTH (t.separador || t.str) - LENGTH(REPLACE (t.separador || t.str, t.separador)) + 1) t2 WHERE TRIM (t2.token) IS NOT NULL)) AND (p_cd_procedimento = '0' OR sei.cd_procedimento IN (SELECT TO_NUMBER (TRIM (token)) FROM (SELECT SUBSTR ( t.separador || t.str || t.separador, INSTR (t.separador || t.str || t.separador, t.separador, 1, LEVEL) + 1, INSTR (t.separador || t.str || t.separador, t.separador, 1, LEVEL + 1) - INSTR (t.separador || t.str || t.separador, t.separador, 1, LEVEL) - 1 ) token FROM (SELECT p_cd_procedimento str, ',' separador FROM DUAL) t CONNECT BY LEVEL < LENGTH (t.separador || t.str) - LENGTH(REPLACE (t.separador || t.str, t.separador)) + 1) t2 WHERE TRIM (t2.token) IS NOT NULL)) AND ( (p_dt_ini = '0' AND p_dt_fim = '0') OR TO_CHAR (se.dt_atendimento, 'dd/mm/yyyy') BETWEEN p_dt_ini AND p_dt_fim) AND (p_sexo = 'T' OR pa.sexo = p_sexo) AND (p_cd_dest_origem = 0 OR se.cd_dest_origem = p_cd_dest_origem) AND ( (p_idade_ini = 0 AND p_idade_fim = 0) OR datediff ('YYYY', pa.dt_nascimento, se.dt_atendimento) BETWEEN p_idade_ini AND p_idade_fim)) LOOP DBMS_OUTPUT.put_line( cur_exame.cd_paciente || ' - ' || cur_exame.idade || ' anos -- ' || cur_exame.sexo || ' - ' || cur_exame.cd_credenciado || '-' || cur_exame.nr_atendimento || '/' || cur_exame.dt_ano_atend || ' - ' || cur_exame.cd_procedimento || '' || cur_exame.ds_procedimento || ' - ' || cur_exame.ds_variavel_pre_laudo || ': ' || cur_exame.result_variavel); END LOOP; END;

[]´s
Roberto

[Leonardo LitzParticipante]

Olá rwarstat

Para solucionar o teu problema, teremos que criar um TYPE com o RECORD de retorno, e após isso criar um TYPE de tabela deste RECORD.
Este type pode ser criado como global ou dentro do spec de uma package, no exemplo optei por criar global:

CREATE TYPE TYP_OBJ IS RECORD (cd_paciente sol_exame.cd_paciente%TYPE,
idade NUMBER,
sexo paciente.sexo%TYPE,
cd_credenciado sol_exame_item.cd_credenciado%TYPE,
nr_atendimento sol_exame.nr_atendimento%TYPE,
dt_ano_atend sol_exame.dt_ano_atend%TYPE,
cd_procedimento sol_exame_item.cd_procedimento%TYPE,
ds_procedimento procedimento.ds_procedimento%TYPE,
ds_variavel_pre_laudo variavel_pre_laudo.ds_variavel_pre_laudo%TYPE,
result_variavel variavel_resultado_laudo.result_variavel%TYPE);

CREATE TYPE TB_TYP_OBJ IS TABLE OF TYP_OBJ;

Tomei a liberdade de mudar esta procedure para uma Function. Mas vc pode utilizar um parametro de OUT em sua procedure para retornar a tabela:

CREATE OR REPLACE FUNCTION pr_teste (p_cd_paciente IN VARCHAR2 DEFAULT '0' ,
p_cd_procedimento IN VARCHAR2 DEFAULT '0' ,
p_dt_ini IN VARCHAR2 DEFAULT '0' ,
p_dt_fim IN VARCHAR2 DEFAULT '0' ,
p_sexo IN VARCHAR2 DEFAULT 'T' ,
p_cd_dest_origem IN NUMBER DEFAULT 0 ,
p_idade_ini IN NUMBER DEFAULT 0 ,
p_idade_fim IN NUMBER DEFAULT 0 )
RETURN TB_TYP_OBJ
AS

c number := 0;
v_retorno TB_TYP_OBJ;

BEGIN
FOR cur_exame
IN (SELECT se.cd_credenciado,
se.nr_atendimento,
se.dt_ano_atend,
se.dt_atendimento,
sei.cd_procedimento,
p.ds_procedimento,
vpl.ds_variavel_pre_laudo,
vrl.result_variavel,
se.cd_paciente,
datediff ('YYYY', pa.dt_nascimento, se.dt_atendimento) idade,
pa.sexo
FROM variavel_resultado_laudo vrl
INNER JOIN
variavel_pre_laudo vpl
ON vrl.lau_cd_credenciado = vpl.cd_credenciado
AND vrl.cd_variavel_pre_laudo = vpl.cd_variavel_pre_laudo
INNER JOIN
laudo l
ON vrl.lau_cd_credenciado = l.cd_credenciado
AND vrl.nr_atendimento = l.nr_atendimento
AND vrl.dt_ano_atend = l.dt_ano_atend
AND vrl.nr_sequencia = l.nr_sequencia
AND vrl.nr_seq_laudo = l.nr_seq_laudo
INNER JOIN
sol_exame_item sei
ON l.cd_credenciado = sei.cd_credenciado
AND l.nr_atendimento = sei.nr_atendimento
AND l.dt_ano_atend = sei.dt_ano_atend
AND l.nr_sequencia = sei.nr_sequencia
INNER JOIN
procedimento p
ON sei.cd_procedimento = p.cd_procedimento
INNER JOIN
sol_exame se
ON se.cd_credenciado = sei.cd_credenciado
AND se.nr_atendimento = sei.nr_atendimento
AND se.dt_ano_atend = sei.dt_ano_atend
INNER JOIN
paciente pa
ON se.cd_paciente = pa.cd_paciente
WHERE se.dt_ano_atend = 2010
AND (se.nr_atendimento BETWEEN 57343 AND 57397 OR se.nr_atendimento = 38)
AND (p_cd_paciente = '0'
OR se.cd_paciente IN
(SELECT TO_NUMBER (TRIM (token))
FROM (SELECT SUBSTR (
t.separador || t.str || t.separador,
INSTR (t.separador || t.str || t.separador,
t.separador,
1,
LEVEL)
+ 1,
INSTR (t.separador || t.str || t.separador,
t.separador,
1,
LEVEL + 1)
- INSTR (t.separador || t.str || t.separador,
t.separador,
1,
LEVEL)
- 1
)
token
FROM (SELECT p_cd_paciente str, ',' separador FROM DUAL)
t
CONNECT BY LEVEL <
LENGTH (t.separador || t.str)
- LENGTH(REPLACE (t.separador || t.str,
t.separador))
+ 1) t2
WHERE TRIM (t2.token) IS NOT NULL))
AND (p_cd_procedimento = '0'
OR sei.cd_procedimento IN
(SELECT TO_NUMBER (TRIM (token))
FROM (SELECT SUBSTR (
t.separador || t.str || t.separador,
INSTR (t.separador || t.str || t.separador,
t.separador,
1,
LEVEL)
+ 1,
INSTR (t.separador || t.str || t.separador,
t.separador,
1,
LEVEL + 1)
- INSTR (t.separador || t.str || t.separador,
t.separador,
1,
LEVEL)
- 1
)
token
FROM (SELECT p_cd_procedimento str, ',' separador
FROM DUAL) t
CONNECT BY LEVEL <
LENGTH (t.separador || t.str)
- LENGTH(REPLACE (t.separador || t.str,
t.separador))
+ 1) t2
WHERE TRIM (t2.token) IS NOT NULL))
AND ( (p_dt_ini = '0' AND p_dt_fim = '0')
OR TO_CHAR (se.dt_atendimento, 'dd/mm/yyyy') BETWEEN p_dt_ini AND p_dt_fim)
AND (p_sexo = 'T' OR pa.sexo = p_sexo)
AND (p_cd_dest_origem = 0 OR se.cd_dest_origem = p_cd_dest_origem)
AND ( (p_idade_ini = 0 AND p_idade_fim = 0)
OR datediff ('YYYY', pa.dt_nascimento, se.dt_atendimento) BETWEEN p_idade_ini
AND p_idade_fim))
LOOP

  c := c + 1;

  v_retorno(c).cd_paciente           := cur_exame.cd_paciente;
  v_retorno(c).idade                 := cur_exame.idade;      
  v_retorno(c).sexo                  := cur_exame.sexo;            
  v_retorno(c).cd_credenciado        := cur_exame.cd_credenciado;                  
  v_retorno(c).nr_atendimento        := cur_exame.nr_atendimento;                        
  v_retorno(c).dt_ano_atend          := cur_exame.dt_ano_atend;                              
  v_retorno(c).cd_procedimento       := cur_exame.cd_procedimento;                                    
  v_retorno(c).ds_procedimento       := cur_exame.ds_procedimento;                                          
  v_retorno(c).ds_variavel_pre_laudo := cur_exame.ds_variavel_pre_laudo;                                                
  v_retorno(c).result_variavel       := cur_exame.result_variavel;                                                                                                

END LOOP;

return v_retorno;

END;

Valeu Leonardo Litz

[rwarstatParticipante]

Opa Litz,

Muito obrigado pela ajuda.
A solução caiu feito uma luva para mim. Também alterei aqui de procedure para function, pois assim posso chamar direto de um select.
Estou com problema agora para passar parâmetros para essa função.

SELECT *
FROM the (SELECT CAST (f_busca_result_laudo (p_cd_paciente => '84593') AS tab_result_laudo) FROM DUAL) a


Fiz a chamada acima e retornou
ORA-00907: parêntese direito não encontrado

Abraço,
Roberto

[Leonardo LitzParticipante]

Legal…

Quando vc executa uma função em um select, o parametro deve ser passado de forma posicional, a não ser que o seu banco seja 11g, que já suporta passagem de parametros referenciados em selects…

SELECT *
FROM (SELECT CAST (f_busca_result_laudo ('84593') AS tab_result_laudo) FROM DUAL) a

Creio que para vc fazer um cast nesta variavel vc deva utilizar o type como Object e não como record…

Vlwl Leonardo Litz

[rwarstatParticipante]

Descobri que tinha que passar os parâmetros de forma posicional, já que o meu banco não é 11G. Depois irei testar essa passagem de parâmetros em um 11G que tenho aqui

Valeu Litz !!!

Abraço,
Roberto

[Autor]

Posts