PL/SQL如何返回整个记录 [英] PL/SQL how to return whole record

问题描述

我想做一个假设某个值的函数.然后在三列中搜索此值，如果找到，则返回该值，否则将终止该函数.

I want to do a function that assumes a certain value. Then this value is searched in three columns, if it is found then it is returned, if not then the function will be terminated.

我正在寻求有关解决方法的提示，我想退还全部记录.

I am asking for tips on how I can solve it, I want to return the whole record.

create or replace FUNCTION BUS_PROV_GET_TEST(valueGet IN VARCHAR2)
    RETURN 
         BUS_PROV_CONF%ROWTYPE 
         IS
         V_ROW BUS_PROV_CONF%ROWTYPE ;
    BEGIN
    DECLARE
        V_BUS_PROV  BUS_PRO_CONF.bus_provider%TYPE; 
        V_TRA_NAME   bus_prov_conf.tr_name%TYPE; 
        V_CU_ID bus_prov_conf.cu_id%TYPE;
    BEGIN
         SELECT *
          INTO V_ROW
          FROM BUS_PROV_CONF
         WHERE bus_prov = valueGet
           AND Upper(BUS_PROV) = Upper (valueGet)
          ;
    EXCEPTION
        WHEN no_data_found THEN
            BEGIN 
                SELECT *
                  INTO V_ROW
                  FROM BUS_PROV_CONF
                 WHERE tra_na = valueGet
                   AND Upper(TRA_NA) = Upper (valueGet)
                   ;
            EXCEPTION
            WHEN no_data_found THEN
                BEGIN 
                    SELECT *
                      INTO V_ROW
                      FROM BUS_PROV_CONF
                     WHERE cu_id = valueGet
                       AND Upper(CU_ID) = Upper (valueGet)
                       ;
                EXCEPTION
                    WHEN no_data_found
                    then
                     raise_application_error(-20000, 'Not found');
                END; 
            END;
           END;
    END BUS_PROV_GET_TEST; 

表如下:

create TABLE "BUS_PROV_CONF" 
(
BUS_PROV varchar2(256),
T_NAME varchar2(256),
C_ID varchar2(256),
NOT_TYPE varchar2(256),
PRO_TYPE varchar2(256),
BILL_AC_ID varchar2(256),
MAIL_ADD varchar2(256)
);

SELECT上的星星标有下划线，我得到的是PLS-00382.

The star at the SELECT is underlined and I am getting PLS-00382 .

推荐答案

我认为这里最好的方法是使用游标.我试图重写代码以及这里的内容.试试看.

I think the best approach here is to use cursors. I tried to rewrite the code and here what I have. Try this out.

虽然不确定是否可以运行，但我无法运行它.

Not sure it'd work though, I did not have the possibility to run this.

create or replace FUNCTION BUS_PROV_GET_TEST(valueGet IN VARCHAR2) RETURN bus_prov_conf%ROWTYPE 
IS
  V_ROW bus_prov_conf%ROWTYPE;
  V_BUS_PROV  bus_provi_conf.bus_prov%TYPE; 
  V_TRAD_NAME   bus_prov_conf.trad_name%TYPE; 
  V_CUS_ID bus_prov_conf.cus_id%TYPE;

  cursor c1 is
    SELECT *
      INTO V_ROW
      FROM BUS_PROV_CONF
     WHERE bus_prov = valueGet
       AND Upper(BUS_PROV) = Upper(valueGet);

  cursor c2 is
    SELECT *
      INTO V_ROW
      FROM BUS_PROV_CONF
     WHERE trad_name = valueGet
       AND Upper(TRAD_NAME) = Upper(valueGet);

  cursor c3 is
    SELECT *
      INTO V_ROW
      FROM BUS_PROV_CONF
     WHERE cus_id = valueGet
       AND Upper(CUS_ID) = Upper(valueGet);

  c1_r c1%rowtype;
  c2_r c2%rowtype;
  c3_r c3%rowtype;

BEGIN  
  open c1;
  fetch c1 into c1_r;
  if c1%notfound then
    open c2;
    fetch c2 into c2_r;
    if c2%notfound then
      open c3;
      fetch c3 into c3_r;
      if c3%notfound then
        raise_application_error(-20000, 'Not found');
      else 
        V_ROW := c3_r;
      end if; 
      close c3%rowtype;        
    else
      V_ROW := c2_r;
    end if;
    close c2;
  else
    V_ROW := c1_r;
  end if;
  close c1

  return v_row;
EXCEPTION
  when others then
    if c1%isopen then
      close c1;
    end if;

    if c2%isopen then
      close c2;
    end if;

    if c3%isopen then
      close c3;
    end if;
    raise;
END BUS_PROV_GET_TEST; 

这篇关于PL/SQL如何返回整个记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！