CODEWITHSUNDEEP
oracle-databasestored-procedures
Don W.
Don W.

Reputation: 560

Oracle Stored Procedure with IN parameter Issue

I have created a stored procedure in Oracle - see below

create or replace PROCEDURE REMOVE_CUSTOMER
(
    cus_id IN NUMBER
) 
AS
BEGIN 
    DELETE FROM CUSTOMER WHERE CUSTOMER.CUS_ID = cus_id; 
END;

I executed it like below. 

DECLARE
  CUS_ID NUMBER;
BEGIN
  CUS_ID := 192981;
  REMOVE_CUSTOMER(CUS_ID => CUS_ID);
END;

It supposes to delete customer 192981 only. However, all customers in the table got deleted. Can anybody tell me what it was wrong with the pl/sql?

Upvotes: 0

Views: 171

Answers (1)

user5683823
user5683823

Reputation:

The statement within the procedure:

DELETE FROM CUSTOMER WHERE CUSTOMER.CUS_ID = cus_id;

The query parser must identify what cus_id is on the right-hand side. If the table has a column by that name, that's the first choice. This is why everything is deleted; the RHS is interpreted to mean customer.cus_id.

If no such column existed in the table, the second guess would be a variable defined in the procedure. But that is only the second choice, not the first.

Best practice is to use DIFFERENT names for procedure variables, perhaps prefix the column name with p_ (for parameter) or i_ (for input): p_cus_id.

You CAN use the same name for your procedure variable, but then you must fully qualify it in the SQL statement:

where customer.cus_id = remove_customer.cus_id

Actually you don't need to qualify in the left-hand side; this will work:

where cus_id = remove_customer.cus_id

By contrast, what you do in the anonymous block (when you call the procedure) doesn't cause problems. It is still a bad practice to use the column name as the name of the variable declared in the anonymous block, but when you call the stored procedure from the anonymous block, there can be no confusion as to which CUS_ID is the input to the stored procedure; it can't be a column name from a table, and it can't be the variable from the SP (which is "in scope" only in the SP, it is not visible to the caller - the anonymous block).

Upvotes: 1

Related Questions