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;