In my procedure I want to insert in table order and update table salesman

create or replace PROCEDURE place_order(
    PURCH_AMT   in varchar,
    CUSTOMER_ID in varchar,
    SALESMAN_ID in varchar
) AS
order_id varchar(50);
ORD_DATE varchar(50);

--INICIO
begin
    --VARS
        order_id := sys_guid();
        ORD_DATE := SYSDATE;
    --INSERT da order
        insert into ORDERS values (order_id, PURCH_AMT, ORD_DATE, CUSTOMER_ID, SALESMAN_ID);
        DBMS_OUTPUT.put_line('out put with variable salesman_id');
        DBMS_OUTPUT.put_line(SALESMAN_ID);
    --UPDATE sales do salesman
        update salesman
        set n_sales = n_sales   1
        where salesman.SALESMAN_ID = SALESMAN_ID;
    --COMMIT
        COMMIT;
    EXCEPTION
        WHEN OTHERS THEN
            ROLLBACK;
            RAISE;     
        
end place_order;

But when I run it it updates all the salesman

When I change the update on the procedure and hard code the salesman ID it works

DBMS_OUTPUT.put_line('output with harcoded 5001');
        DBMS_OUTPUT.put_line(5001);
    --UPDATE sales do salesman
        update salesman
        set n_sales = n_sales   1
        where salesman.SALESMAN_ID = '5001';

CodePudding user response：

This is wrong:

create or replace PROCEDURE place_order(
    PURCH_AMT   in varchar,
    CUSTOMER_ID in varchar,
    SALESMAN_ID in varchar          --> this
) AS
<snip>
    --UPDATE sales do salesman
        update salesman
        set n_sales = n_sales   1
        where salesman.SALESMAN_ID = SALESMAN_ID;
                       -----------   -----------
                         this

Never name parameters the same as column names. As you did it, where clause was evaluated to something similar to where 1 = 1 which means update ALL rows in that table.

Rename parameters; usually, we prefix them, e.g.

create or replace PROCEDURE place_order(
    PAR_PURCH_AMT   in varchar2,
    PAR_CUSTOMER_ID in varchar2,
    PAR_SALESMAN_ID in varchar2
) AS
<snip>
    --UPDATE sales do salesman
        update salesman
        set n_sales = n_sales   1
        where salesman.SALESMAN_ID = PAR_SALESMAN_ID;

(Also, Oracle recommends us to use varchar2 instead of varchar)