I have a function and 2 types in Oracle, where a type I already did the conversion, put the function and another type I didn't find the best way to translate.
The purpose of this function is to be like a 'table' for a from of a select. How to select * from myfunction().
What I'm really struggling with is how to return a table type, which has been defined as a type. I think of this line of solution so as not to make too many changes
Type 1, already converted.
CREATE TYPE softbus.t_servico_fretamento_viagens AS
(EMPRESA numeric,
SERVICO numeric,
TIPO CHAR(10),
ITEM numeric,
CLIENTE numeric,
TIPO_VEI numeric,
EMPDESTINO numeric,
DESTINO numeric,
PONTOORIGEM numeric,
PONTODESTINO numeric,
CATEGORIA numeric,
EMPROTA numeric,
ROTA CHAR(4),
DATASAIDA numeric,
HORASAIDA numeric,
DATACHEGADA numeric,
HORACHEGADA numeric,
LOCALEMBARQUE VARCHAR(200),
LOCALDESEMBARQUE VARCHAR(200),
MOTORISTA1 numeric,
MOTORISTA2 numeric,
VENDEDOR numeric,
Type 2, not converted.
CREATE TYPE T_SERVICO_FRETA_VIAGENS_TABELA AS TABLE OF T_SERVICO_FRETAMENTO_VIAGENS;
Function, not converted.
create or replace
FUNCTION FUNCAO_BUSCA_FRETA_VIAGENS
(pEMPRESA IN NUMBER, pDATAINICIAL IN NUMBER, pDATAFINAL IN NUMBER, pRETPERIODO IN CHAR, pRETCANC IN CHAR) RETURN T_SERVICO_FRETA_VIAGENS_TABELA IS
/*(EMPRESA NUMBER(10,0),
SERVICO NUMBER(10,0),
TIPO CHAR(10),
ITEM NUMBER(10,0),
CLIENTE NUMBER(10,0),
TIPO_VEI NUMBER(10,0),
EMPDESTINO NUMBER(10,0),
DESTINO NUMBER(10,0),
PONTOORIGEM NUMBER(10,0),
PONTODESTINO NUMBER(10,0),
CATEGORIA NUMBER(10,0),
EMPROTA NUMBER(10,0),
ROTA CHAR(4),
DATASAIDA NUMBER(10,0),
HORASAIDA NUMBER(10,0),
DATACHEGADA NUMBER(10,0),
HORACHEGADA NUMBER(10,0),
LOCALEMBARQUE VARCHAR2(200 BYTE),
LOCALDESEMBARQUE VARCHAR2(200 byte),
MOTORISTA1 NUMBER(10,0),
MOTORISTA2 NUMBER(10,0),
VENDEDOR NUMBER(10,0),
VALOR NUMBER
)*/
TYPE tLIST IS REF CURSOR;
SLIST TLIST;
vLIST T_SERVICO_FRETAMENTO_VIAGENS := T_SERVICO_FRETAMENTO_VIAGENS(0,0,null,0,0,0,0,0,0,0,0,0,null,0,0,0,0,null,null,0,0,0,0);
xLIST T_SERVICO_FRETA_VIAGENS_TABELA := T_SERVICO_FRETA_VIAGENS_TABELA();
wVET NUMBER := 0;
BEGIN
OPEN SLIST FOR
SELECT SFR.EMPRESA, SFR.CODIGO, SFR.TIPO, SFR.CLIENTE, SFR.EMPDESTINO, SFR.DESTINO, ISEF.ITEM, ISEF.TIPO_VEI, ISEF.EMPROTA, ISEF.ROTA, ISEF.CATEGORIASERVICO,
ISEF.PNTREFORIGEM, ISEF.PNTREFDESTINO, ISEF.LOCALEMBARQUE, ISEF.LOCALDESEMBARQUE, ISEF.HORARIO, ISEF.HORARIOCHEGADA,
ISEF.DATASERVICO,ISEF.DATASERVICO + ISEF.QUANTIDADEDIAS -1, ISEF.MOTORISTA1, ISEF.MOTORISTA2, SFR.VENDEDOR, ISEF.VALOR
FROM ITENSSERVICOFRETAMENTO ISEF
INNER JOIN SERVICOSFRETAMENTO SFR ON SFR.EMPRESA = ISEF.EMPRESA AND SFR.CODIGO = ISEF.SERVICO
WHERE SFR.TIPO IN ('Eventual','Receptivo') AND SFR.EMPRESA = pEMPRESA AND ISEF.DATASERVICO BETWEEN pDATAINICIAL AND pDATAFINAL;
LOOP
FETCH sLIST INTO vLIST.EMPRESA, vLIST.SERVICO, vLIST.TIPO, vLIST.CLIENTE, vLIST.EMPDESTINO, vLIST.DESTINO, vLIST.ITEM,
vLIST.TIPO_VEI, vLIST.EMPROTA, vLIST.ROTA, vLIST.CATEGORIA, vLIST.PONTOORIGEM, vLIST.PONTODESTINO,
VLIST.LOCALEMBARQUE, VLIST.LOCALDESEMBARQUE, VLIST.HORASAIDA, VLIST.HORACHEGADA, VLIST.DATASAIDA, VLIST.DATACHEGADA,
vLIST.MOTORISTA1, vLIST.MOTORISTA2, vLIST.VENDEDOR, vLIST.VALOR;
EXIT WHEN sLIST%NOTFOUND;
wVET := wVET + 1;
xLIST.extend;
xLIST(wVET) := vLIST;
END LOOP;
CLOSE SLIST;
FOR wDATA IN PDATAINICIAL..PDATAFINAL
LOOP
OPEN sLIST FOR
SELECT SFR.EMPRESA, SFR.CODIGO, SFR.TIPO, SFR.CLIENTE, SFR.EMPDESTINO, SFR.DESTINO, ISEF.ITEM, ISEF.TIPO_VEI, ISEF.EMPROTA, ISEF.ROTA, ISEF.CATEGORIASERVICO,
ISEF.PNTREFORIGEM, ISEF.PNTREFDESTINO, ISEF.LOCALEMBARQUE, ISEF.LOCALDESEMBARQUE, ISEF.HORARIO, ISEF.HORARIOCHEGADA,
ISEF.DATASERVICO,ISEF.DATASERVICO + ISEF.QUANTIDADEDIAS -1, ISEF.MOTORISTA1, ISEF.MOTORISTA2, SFR.VENDEDOR, ISEF.VALOR
FROM ITENSSERVICOFRETAMENTO ISEF
INNER JOIN SERVICOSFRETAMENTO SFR ON SFR.EMPRESA = ISEF.EMPRESA AND SFR.CODIGO = ISEF.SERVICO
WHERE SFR.TIPO = 'Continuo' AND SFR.EMPRESA = pEMPRESA AND
FUNCAO_VALIDAVIAGEMFRETAMENTO(SFR.EMPRESA, SFR.CODIGO, ISEF.ITEM, wDATA, 'N',pRETCANC) > 0;
LOOP
FETCH sLIST INTO vLIST.EMPRESA, vLIST.SERVICO, vLIST.TIPO, vLIST.CLIENTE, vLIST.EMPDESTINO, vLIST.DESTINO, vLIST.ITEM,
vLIST.TIPO_VEI, vLIST.EMPROTA, vLIST.ROTA, vLIST.CATEGORIA, vLIST.PONTOORIGEM, vLIST.PONTODESTINO,
VLIST.LOCALEMBARQUE, VLIST.LOCALDESEMBARQUE, VLIST.HORASAIDA, VLIST.HORACHEGADA, VLIST.DATASAIDA, VLIST.DATACHEGADA,
vLIST.MOTORISTA1, vLIST.MOTORISTA2, vLIST.VENDEDOR, vLIST.VALOR;
EXIT WHEN SLIST%NOTFOUND;
vLIST.DATASAIDA := WDATA;
vLIST.DATACHEGADA := wDATA;
wVET := wVET + 1;
xLIST.extend;
xLIST(wVET) := vLIST;
END LOOP;
CLOSE SLIST;
IF pRETPERIODO = 'S' THEN
OPEN sLIST FOR
SELECT SFR.EMPRESA, SFR.CODIGO, SFR.TIPO, SFR.CLIENTE, SFR.EMPDESTINO, SFR.DESTINO, ISEF.ITEM, ISEF.TIPO_VEI, ISEF.EMPROTA, ISEF.ROTA, ISEF.CATEGORIASERVICO,
ISEF.PNTREFORIGEM, ISEF.PNTREFDESTINO, ISEF.LOCALEMBARQUE, ISEF.LOCALDESEMBARQUE, ISEF.HORARIO, ISEF.HORARIOCHEGADA,
ISEF.DATASERVICO,SFR.DATACHEGADA, ISEF.MOTORISTA1, ISEF.MOTORISTA2, SFR.VENDEDOR, ISEF.VALOR
FROM ITENSSERVICOFRETAMENTO ISEF
INNER JOIN SERVICOSFRETAMENTO SFR ON SFR.EMPRESA = ISEF.EMPRESA AND SFR.CODIGO = ISEF.SERVICO
WHERE SFR.TIPO = 'Eventual' AND SFR.EMPRESA = pEMPRESA AND ISEF.DATASERVICO < pDATAINICIAL AND SFR.DATACHEGADA >= pDATAINICIAL AND
FUNCAO_VALIDAVIAGEMFRETAMENTO(SFR.EMPRESA, SFR.CODIGO, ISEF.ITEM, wDATA, 'S',pRETCANC) > 0;
LOOP
FETCH sLIST INTO vLIST.EMPRESA, vLIST.SERVICO, vLIST.TIPO, vLIST.CLIENTE, vLIST.EMPDESTINO, vLIST.DESTINO, vLIST.ITEM,
vLIST.TIPO_VEI, vLIST.EMPROTA, vLIST.ROTA, vLIST.CATEGORIA, vLIST.PONTOORIGEM, vLIST.PONTODESTINO,
VLIST.LOCALEMBARQUE, VLIST.LOCALDESEMBARQUE, VLIST.HORASAIDA, VLIST.HORACHEGADA, VLIST.DATASAIDA, VLIST.DATACHEGADA,
vLIST.MOTORISTA1, vLIST.MOTORISTA2, vLIST.VENDEDOR, vLIST.VALOR;
EXIT WHEN SLIST%NOTFOUND;
wVET := wVET + 1;
xLIST.extend;
xLIST(wVET) := vLIST;
END LOOP;
CLOSE SLIST;
END IF;
END LOOP;
RETURN(xLIST);
END FUNCAO_BUSCA_FRETA_VIAGENS;
thought and analyzing the situation, the result of these three sentences are being added in a return vector. So, I made this union all to be returned as a result of a query. Am I on the right path?
create or replace
FUNCTION softbus.funcao_busca_freta_viagens
(pEMPRESA IN NUMERIC, pDATAINICIAL IN NUMERIC, pDATAFINAL IN NUMERIC, pRETPERIODO IN CHAR, pRETCANC IN CHAR)
RETURNS table(
EMPRESA NUMERIC(10,0),
SERVICO NUMERIC(10,0),
TIPO CHAR(10),
ITEM NUMERIC(10,0),
CLIENTE NUMERIC(10,0),
TIPO_VEI NUMERIC(10,0),
EMPDESTINO NUMERIC(10,0),
DESTINO NUMERIC(10,0),
PONTOORIGEM NUMERIC(10,0),
PONTODESTINO NUMERIC(10,0),
CATEGORIA NUMERIC(10,0),
EMPROTA NUMERIC(10,0),
ROTA CHAR(4),
DATASAIDA NUMERIC(10,0),
HORASAIDA NUMERIC(10,0),
DATACHEGADA NUMERIC(10,0),
HORACHEGADA NUMERIC(10,0),
LOCALEMBARQUE VARCHAR(200),
LOCALDESEMBARQUE VARCHAR(200),
MOTORISTA1 NUMERIC(10,0),
MOTORISTA2 NUMERIC(10,0),
VENDEDOR NUMERIC(10,0),
VALOR NUMERIC
)
AS $body$
DECLARE
BEGIN
RETURN QUERY
SELECT SFR.EMPRESA, SFR.CODIGO, SFR.TIPO, SFR.CLIENTE, SFR.EMPDESTINO, SFR.DESTINO, ISEF.ITEM, ISEF.TIPO_VEI, ISEF.EMPROTA, ISEF.ROTA, ISEF.CATEGORIASERVICO,
ISEF.PNTREFORIGEM, ISEF.PNTREFDESTINO, ISEF.LOCALEMBARQUE, ISEF.LOCALDESEMBARQUE, ISEF.HORARIO, ISEF.HORARIOCHEGADA,
ISEF.DATASERVICO,ISEF.DATASERVICO + ISEF.QUANTIDADEDIAS -1, ISEF.MOTORISTA1, ISEF.MOTORISTA2, SFR.VENDEDOR, ISEF.VALOR
FROM ITENSSERVICOFRETAMENTO ISEF
INNER JOIN SERVICOSFRETAMENTO SFR ON SFR.EMPRESA = ISEF.EMPRESA AND SFR.CODIGO = ISEF.SERVICO
WHERE SFR.TIPO IN ('Eventual','Receptivo') AND SFR.EMPRESA = pEMPRESA AND ISEF.DATASERVICO BETWEEN pDATAINICIAL AND pDATAFINAL
UNION ALL
SELECT SFR.EMPRESA, SFR.CODIGO, SFR.TIPO, SFR.CLIENTE, SFR.EMPDESTINO, SFR.DESTINO, ISEF.ITEM, ISEF.TIPO_VEI, ISEF.EMPROTA, ISEF.ROTA, ISEF.CATEGORIASERVICO,
ISEF.PNTREFORIGEM, ISEF.PNTREFDESTINO, ISEF.LOCALEMBARQUE, ISEF.LOCALDESEMBARQUE, ISEF.HORARIO, ISEF.HORARIOCHEGADA,
ISEF.DATASERVICO,ISEF.DATASERVICO + ISEF.QUANTIDADEDIAS -1, ISEF.MOTORISTA1, ISEF.MOTORISTA2, SFR.VENDEDOR, ISEF.VALOR
FROM ITENSSERVICOFRETAMENTO ISEF
INNER JOIN SERVICOSFRETAMENTO SFR ON SFR.EMPRESA = ISEF.EMPRESA AND SFR.CODIGO = ISEF.SERVICO
WHERE SFR.TIPO = 'Continuo' AND SFR.EMPRESA = pEMPRESA AND ISEF.DATASERVICO BETWEEN pDATAINICIAL AND pDATAFINAL AND
FUNCAO_VALIDAVIAGEMFRETAMENTO(SFR.EMPRESA, SFR.CODIGO, ISEF.ITEM, ISEF.DATASERVICO, 'N',pRETCANC) > 0
UNION ALL
SELECT SFR.EMPRESA, SFR.CODIGO, SFR.TIPO, SFR.CLIENTE, SFR.EMPDESTINO, SFR.DESTINO, ISEF.ITEM, ISEF.TIPO_VEI, ISEF.EMPROTA, ISEF.ROTA, ISEF.CATEGORIASERVICO,
ISEF.PNTREFORIGEM, ISEF.PNTREFDESTINO, ISEF.LOCALEMBARQUE, ISEF.LOCALDESEMBARQUE, ISEF.HORARIO, ISEF.HORARIOCHEGADA,
ISEF.DATASERVICO,SFR.DATACHEGADA, ISEF.MOTORISTA1, ISEF.MOTORISTA2, SFR.VENDEDOR, ISEF.VALOR
FROM ITENSSERVICOFRETAMENTO ISEF
INNER JOIN SERVICOSFRETAMENTO SFR ON SFR.EMPRESA = ISEF.EMPRESA AND SFR.CODIGO = ISEF.SERVICO
WHERE SFR.TIPO = 'Eventual' AND SFR.EMPRESA = pEMPRESA AND ISEF.DATASERVICO < pDATAINICIAL AND SFR.DATACHEGADA >= pDATAINICIAL AND
FUNCAO_VALIDAVIAGEMFRETAMENTO(SFR.EMPRESA, SFR.CODIGO, ISEF.ITEM, ISEF.DATASERVICO, 'S',pRETCANC) > 0;
END;
$body$
LANGUAGE PLPGSQL
SECURITY DEFINER
STABLE;