选择sqldeveloper过程

Question

嗨，我有这样的选择

SELECT
  ADDRESSES.PHONENUMBER1,
  ADDRESSES.PHONENUMBER2,
  FIRMS.NAME,
  ADDRESSES.COUNTRY,
  ADDRESSES.EMAIL,
  ADDRESSES.RECIPIENT
FROM ADDRESSES
INNER JOIN FIRMS
ON FIRMS.RESIDENCEADDRESS_ID = ADDRESSES.ID
WHERE (replace(REPLACE(ADDRESSES.PHONENUMBER1, ' ', ''), '-', '') LIKE '%123456789%')
OR (replace(REPLACE(ADDRESSES.PHONENUMBER1, ' ', ''), '-', '') LIKE '%123456789%')

我想做个手术，结果是

姓名，地址，电子邮件

也需要帮助和替换123456789智慧正确的要求。

谢谢

Answer 1

..this可以工作，但是如果有人知道可以改进的东西的话。让我知道

create or replace PROCEDURE TEST
(
in_ANI in VARCHAR2,
out_all out VARCHAR2
)


AS
    fName VARCHAR2 (40) := '';
    aemail VARCHAR2 (40) := '';


BEGIN

out_all := '';

  SELECT FIRMS.NAME, ADDRESSES.EMAIL
  into fName, aemail
  FROM ADDRESSES
  INNER JOIN FIRMS
  ON FIRMS.RESIDENCEADDRESS_ID = ADDRESSES.ID
  WHERE (replace(REPLACE(ADDRESSES.PHONENUMBER1, ' ', ''), '-', '') LIKE '%'||in_ANI||'%')
  OR (replace(REPLACE(ADDRESSES.PHONENUMBER2, ' ', ''), '-', '') LIKE '%'||in_ANI||'%');


out_ALL := 'Firm:' || fName || '|Email:' || aemail;

    exception
      when TOO_MANY_ROWS then 
        out_ALL := ('Firm:Too Many firms for this phone');

      when NO_DATA_FOUND then 
        out_ALL := ('Firm:No firms for this number');

      when others then 
        raise_application_error(-20011,'Unknown Exception');


END TEST;