Oracle MERGE 用法

 

CREATE OR REPLACE PROCEDURE P_C(X OUT VARCHAR2,Y OUT VARCHAR2, S IN VARCHAR2) AS

  P_STR VARCHAR2(100) := 'ABCDEF';
  STR VARCHAR2(50);
  STR_SQL VARCHAR2(100);
  P_EMAIL_NAME VARCHAR2(50);
  P_EMAIL VARCHAR2(50);

BEGIN

  MERGE INTO T_INVOICE I
  USING (SELECT M.VIP_ZONE_CODE,
                M.VIP_CODE,
                M.INVOICE_YEAR_MONTH,
                M.STANDARD_MONEY_AMT,
                M.ZONE_CODE
           FROM T_MONTHLY_INVOICE M
          WHERE M.VIP_CODE = '123456') V
  ON (I.DEPT_CODE = V.ZONE_CODE)
  WHEN MATCHED THEN
    UPDATE
       SET/* I.DEPT_CODE          = V.ZONE_CODE,*/--此列不能更新，更新会报错--使用merge时要注意：作为查询条件的列（在"condition"中）是不可以被更新的（在merge_update_clause中不可更新）。
           I.CUSTOMER_CODE      = V.VIP_ZONE_CODE,
           I.STANDARD_MONEY_AMT = V.STANDARD_MONEY_AMT


  WHEN NOT MATCHED THEN

    INSERT
      (STANDARD_MONEY_AMT, CUSTOMER_CODE)
    VALUES
      (V.STANDARD_MONEY_AMT, V.VIP_CODE);

   SELECT SUBSTR(P_STR,1,4) INTO STR FROM DUAL;
   DBMS_OUTPUT.put_line(STR);
   
   STR_SQL := 'SELECT T.EMAIL_TEL，T.EMAIL_MAIL  FROM TT_EMAIL T WHERE T.EMAIL_USER = :SS';
   EXECUTE IMMEDIATE STR_SQL  INTO P_EMAIL_NAME ,P_EMAIL
    USING S;  
   DBMS_OUTPUT.put_line(P_EMAIL_NAME||P_EMAIL);
    
   EXCEPTION
     WHEN OTHERS THEN 
       DBMS_OUTPUT.put_line(SQLERRM);
END P_C;