[Resolvido] Select com procedure

[NJM 0]

Olá pessoal, estou com um probleminha e gostaria da ajuda de vocês. Tenho um select que traz várias linhas e dentro deste select preciso que execute uma procedure que irá trazer apenas uma linha com varios campos de saida e um de entrada que será usado como identificador para outros selects dentro da procedure, seria mais ou menos isso :

create or replace procedure PR_COBRANCA(

v_cliForn in Pessoa_Relacionamento.Pessoa_Relac_Id%type,

V_PESSOA_RELAC_ID out PESSOA_RELACIONAMENTO.PESSOA_RELAC_ID%TYPE,

V_PESSOA_ID out PESSOA_RELACIONAMENTO.PESSOA_ID%TYPE,

V_TIPO_ENDERECO_ID out ENDERECO.TIPO_ENDERECO_ID%TYPE,

V_LOGRADOURO out ENDERECO.LOGRADOURO%TYPE,

V_TIPO_LOGRADOURO out TIPO_LOGRADOURO.ABREVIATURA%TYPE,

V_NUMERO out ENDERECO.NUMERO%TYPE,

V_COMPLEMENTO out ENDERECO.COMPLEMENTO%TYPE,

V_BAIRRO out ENDERECO.BAIRRO%TYPE,

V_CEP out ENDERECO.CEP%TYPE,

V_CIDADE out CIDADE.NOME%TYPE,

V_UF out ESTADO.UF_ID%type,

V_UF_NOME out ESTADO.NOME%type) is

 

v_erro VarChar2(100);

 

begin

 

-- Busca UF Cliente - Se n¿ existir Faturamento pega UF Comercial

v_erro:= 'Leitura do endereço de cobrançca não Encontrado !!! '|| to_char(v_cliforn);

begin

 

SELECT VP.PESSOA_RELAC_ID, VP.PESSOA_ID, EC.TIPO_ENDERECO_ID, tl.abreviatura, EC.LOGRADOURO, EC.NUMERO, EC.COMPLEMENTO, EC.BAIRRO,

EC.CEP, CC.NOME, UFC.UF_ID, ufc.nome

into V_PESSOA_RELAC_ID, v_pessoa_id, V_TIPO_ENDERECO_ID, v_tipo_logradouro, V_LOGRADOURO, V_NUMERO, V_COMPLEMENTO, V_BAIRRO,

V_CEP, V_CIDADE, V_UF, V_UF_NOME

FROM VW_PESSOA VP, ENDERECO EC, TIPO_LOGRADOURO TL, CIDADE CC, ESTADO UFC

WHERE

VP.PESSOA_RELAC_ID = v_cliForn

AND EC.PESSOA_ID = VP.PESSOA_ID

AND EC.TIPO_ENDERECO_ID IN (1,2)

AND TL.TIPO_LOGRADOURO_ID = EC.TIPO_LOGRADOURO_ID

AND EC.CIDADE_ID = CC.CIDADE_ID

AND CC.UF_ID = UFC.UF_ID

AND ROWNUM=1

ORDER BY EC.TIPO_ENDERECO_ID DESC;

 

exception

when no_data_found then

raise_application_error(-20001,v_erro);

 

WHEN OTHERS THEN

raise_application_error(-20002,'Erro de processamento - '|| v_erro ||' - '|| SQLCODE ||' - '|| SQLERRM);

end;

 

end PR_COBRANCA;

 

como chamar esta procedure de dentro de um select, esta seria a melhor maneira de trazer estas informações?

[Motta 645]

Não, uma Procedure não pode ser chamada por um Select, mas uma Function pode.Porém uma Fuction só retorna um valor, pode até ser um tipo(Type) criado pelo usuário, mas apenas um valor.

 

A chamada como está definida poderia ser chamada por outra Procedure.

 

+ou- isto

 

create or replace procedure exemplo is
v_cliForn Pessoa_Relacionamento.Pessoa_Relac_Id%type;
V_PESSOA_RELAC_ID PESSOA_RELACIONAMENTO.PESSOA_RELAC_ID%TYPE;
...
V_UF_NOME ESTADO.NOME%type;

begin
...
v_cliForn := 123456;
-- chama a função para o cliente 123456 e joga o retorno nas variáveis.
PR_COBRANCA(
v_cliForn,
V_PESSOA_RELAC_ID,
...
V_UF_NOME);
...
end;

 

Ajudou ?

[NJM 0]

Olá Motta, obrigado por responder meu post, é sempre de grande ajuda as suas resposta, mas, contínuo com uma dúvida, de acordo com o seu exemplo como eu faço a chamada pelo select e referencio as colunas da procedure. Ex. select pedido, cliente_id, exemplo(cliente_id, relac_id, endereco, .... ) from tb_pedido where pedido_id=12;. Como você. disse talvez seria melhor converter para uma função que retornasse um campo "type", mas com varias colunas, que é o que eu preciso, você. pode me ajudar?

 

 

Obrigado pela atenção.

[Motta 645]

Eu não entendi bem o problema , por que você quer uma procedure ?

 

Por que não faz o Select simplesmente nas procedures que forem preciso ?

[NJM 0]

Olá Motta, é o seguinte, eu tenho uma aplicação de relatório onde só posso ter uma query, e preciso retornar o endereço de cobranca (logradouro, cidade, etc) que pode ser nenhum ou vários e deve trazer o 1o., caso não tenha endereço de cobranca deve retornar o endereço comercial, a query para retornar o endereço é bem simples usando o rownum, mas ele deve se juntar a cada linha da outra query, pois não tenho a opção de fazer um outro select, então pensei fazer a procedure ou a function para trazer esta linha pra mim. Com uma view não deu certo, pois quando eu coloco no where o "id do cliente" ele não traz nada por causa do rownum e usar o não posso usar o rownum no select principal.

 

Espero que você. me compreenda e novamente o meu obrigado pela atenção.

[Motta 645]

Uma solução simples seria retornar o endereço numa string só , um tripão.

 

A Function concatenaria cada parte do endereço numa só e retornaria tudo num string única.

---

Outras soluções depende de como isto vai ser usado, criação de Types podem fazer com que só Oracle entenda os dados não permitindo o uso por uma aplicação na camada de front-end por exemplo.

---

Uma VIEW chamada também seria uma solução, retornando apenas um endereço, algo assim , sendo chave a chave da tabela endereços.

 

Esta VIEW seria uma tabela com apenas um endereço por cliente, nas queries um simples join resolveria.

create or replace view v_enderecos as
SELECT VP.PESSOA_RELAC_ID, VP.PESSOA_ID, EC.TIPO_ENDERECO_ID, tl.abreviatura, EC.LOGRADOURO, EC.NUMERO, EC.COMPLEMENTO, EC.BAIRRO,
EC.CEP, CC.NOME, UFC.UF_ID, ufc.nome
FROM VW_PESSOA VP, ENDERECO EC, TIPO_LOGRADOURO TL, CIDADE CC, ESTADO UFC
WHERE EC.PESSOA_ID = VP.PESSOA_ID
AND EC.TIPO_ENDERECO_ID IN (1,2)
AND TL.TIPO_LOGRADOURO_ID = EC.TIPO_LOGRADOURO_ID
AND EC.CIDADE_ID = CC.CIDADE_ID
AND CC.UF_ID = UFC.UF_ID
and ec.chave = (select max(ec2.chave) -- obter só um dos endereços por cliente
                from ec.pessoa_id = ec2.pessoa_id)

[NJM 0]

Grande Motta, matou .. A função retornando a PK e fazendo o join dentro do select para mim será melhor solução do que a view, pois daí fica livre para em um futuro caso precise alterar ou fazer alguma implementação de teste.

 

 

Obrigado.