Tuesday 27 August 2024

How to update and return the value in plsql (RETURNING Clause to Avoid Unnecessary SQL Statements)

PROCEDURE validate_prc (
    p_guid            VARCHAR2,
    p_invoice_number  OUT  VARCHAR2,
    p_cust_num        OUT  VARCHAR2,
    p_site_use_id     OUT  NUMBER,
    p_click_count     OUT  NUMBER
) IS
    PRAGMA autonomous_transaction;
BEGIN
UPDATE adv_coll_tbl
        SET click_count = click_count+1
WHERE  guid = p_guid
RETURNING
    invoice_number,
            customer_number,
            bill_to_site_use_id,
            click_count
INTO
    p_invoice_number,
            p_cust_num,
            p_site_use_id,
            p_click_count
;
IF SQL%FOUND THEN
dbms_output.put_line(' Success ');
ELSE
dbms_output.put_line(' Error ');
END IF;
END;

No comments:

Post a Comment