oracle - 在控制台中告诉我 SELECT 中缺少 INTO 的错误。PL/SQL

Question

此查询在 Oracle SQL 开发人员中，它是一个存储过程，可以接收一个值或数字或 RUC，并在 IF 中验证一个值是否为空，并在 SELECT 中进行搜索。

错误：

SELECT 语句中应包含 INTO 子句

询问：

CREATE OR replace PROCEDURE Otc_sp_rpt_ips(par_ruc         IN VARCHAR2,
                                           par_facturacion IN VARCHAR2)
IS
BEGIN
    IF par_ruc IS NULL THEN
      BEGIN
          SELECT ca.legal_name   "Legal Name/Name",
                 vw.documento_cliente,
                 ph.name         "Phone Number",
                 vw.estado_abonado,
                 icc.iccid,
                 icc.imsi,
                 ip.name         "IP Address",
                 pi.created_when "Created When"
          --pi.object_id , pi.name, ca.type
          FROM   r_om_m2m_pi pi
                 join r_ri_private_ip_addr ip
                   ON pi.ip_address = ip.object_id
                 join r_ri_mobile_phone_number ph
                   ON pi.mobile_phone_number = ph.object_id
                 join r_cbm_billing_acct ba
                   ON pi.billing_account = ba.object_id
                 join r_cim_bsns_cust_acct ca
                   ON ba.parent_id = ca.object_id
                 join r_am_sim icc
                   ON pi.sim_card = icc.object_id
                 join otc_t_abonados_mov vw
                   ON ( vw.num_telefonico = ph.name
                        AND estado_abonado <> 'BAA'
                        AND vw.documento_cliente IN ( par_facturacion ) );
      END;
    ELSE
      BEGIN
          SELECT ca.legal_name   AS "Legal Name/Name",
                 vw.documento_cliente,
                 ph.name         AS "Phone Number",
                 vw.estado_abonado,
                 icc.iccid,
                 icc.imsi,
                 ip.name         AS "IP Address",
                 pi.created_when AS "Created When"
          --pi.object_id , pi.name, ca.type
          FROM   r_om_m2m_pi pi
                 join r_ri_private_ip_addr ip
                   ON pi.ip_address = ip.object_id
                 join r_ri_mobile_phone_number ph
                   ON pi.mobile_phone_number = ph.object_id
                 join r_cbm_billing_acct ba
                   ON pi.billing_account = ba.object_id
                 join r_cim_bsns_cust_acct ca
                   ON ba.parent_id = ca.object_id
                 join r_am_sim icc
                   ON pi.sim_card = icc.object_id
                 join otc_t_abonados_mov vw
                   ON ( vw.num_telefonico = ph.name
                        AND estado_abonado <> 'BAA'
                        AND vw.account_num IN ( par_ruc ) );
      END;
    END IF;
END otc_sp_rpt_ips; 

---示例---- BEGIN OTC_SP_RPT('1790005739001',''); END; ---示例 2---- BEGIN OTC_SP_RPT('','2874695'); END;

标签： oracleplsqldeveloper

Answer 1

如果要从此过程返回选择查询的结果，则必须在代码中使用 sys_refcursor 作为 out 参数，因为没有它，Oracle 无法返回结果。所以你需要使用下面的查询 -

CREATE OR replace PROCEDURE Otc_sp_rpt_ips(par_ruc         IN VARCHAR2,
                                           par_facturacion IN VARCHAR2,
                                           result          OUT SYS_REFCURSOR)
IS
BEGIN
    IF par_ruc IS NULL THEN
      BEGIN
          OPEN RESULT FOR
          SELECT ca.legal_name   "Legal Name/Name",
                 vw.documento_cliente,
                 ph.name         "Phone Number",
                 vw.estado_abonado,
                 icc.iccid,
                 icc.imsi,
                 ip.name         "IP Address",
                 pi.created_when "Created When"
          --pi.object_id , pi.name, ca.type
          FROM   r_om_m2m_pi pi
                 join r_ri_private_ip_addr ip ON pi.ip_address = ip.object_id
                 join r_ri_mobile_phone_number ph ON pi.mobile_phone_number = ph.object_id
                 join r_cbm_billing_acct ba ON pi.billing_account = ba.object_id
                 join r_cim_bsns_cust_acct ca ON ba.parent_id = ca.object_id
                 join r_am_sim icc ON pi.sim_card = icc.object_id
                 join otc_t_abonados_mov vw
                   ON ( vw.num_telefonico = ph.name
                        AND estado_abonado <> 'BAA'
                        AND vw.documento_cliente IN ( par_facturacion ) );
      END;
    ELSE
      BEGIN
          OPEN RESULT FOR
          SELECT ca.legal_name   AS "Legal Name/Name",
                 vw.documento_cliente,
                 ph.name         AS "Phone Number",
                 vw.estado_abonado,
                 icc.iccid,
                 icc.imsi,
                 ip.name         AS "IP Address",
                 pi.created_when AS "Created When"
          --pi.object_id , pi.name, ca.type
          FROM   r_om_m2m_pi pi
                 join r_ri_private_ip_addr ip
                   ON pi.ip_address = ip.object_id
                 join r_ri_mobile_phone_number ph
                   ON pi.mobile_phone_number = ph.object_id
                 join r_cbm_billing_acct ba
                   ON pi.billing_account = ba.object_id
                 join r_cim_bsns_cust_acct ca
                   ON ba.parent_id = ca.object_id
                 join r_am_sim icc
                   ON pi.sim_card = icc.object_id
                 join otc_t_abonados_mov vw
                   ON ( vw.num_telefonico = ph.name
                        AND estado_abonado <> 'BAA'
                        AND vw.account_num IN ( par_ruc ) );
      END;
    END IF;
EXCEPTION
         WHEN OTHERS THEN
              DBMS_OUTPUT.PUT_LINE('Error In Code');
              RETURN;
END otc_sp_rpt_ips; 

从 oracle 12C 开始，还有更多方法可以返回选择查询结果，但您仍然可以使用此方法。