PL/SQL Tip of the Month
Date: Wed, 16 Sep 1998 00:59:45 GMT
Message-ID: <6tn2i1$c4o$1_at_nnrp1.dejanews.com>
[Quoted] From the "PL/SQL Pipeline" - a free internet community for Oracle developers worldwide. Visit the PL/SQL Pipeline to discuss technical issues with colleagues, download white papers and utilities, and more. The PL/SQL Pipeline is hosted by best-selling author Steven Feuerstein, and sponsored by RevealNet.
- SEPTEMBER PL/SQL TIP OF THE MONTH
I have been telling PL/SQL developers for years now to never declare a variable or parameter with the same name database elements, such as tables and columns. You can give yourself a serious headache if you do this. For example, suppose I am working with the employee table. It has a column called employee_id. If I write a program like this:
CREATE OR REPLACE PROCEDURE delete_employee (
employee_id IN NUMBER)
IS
BEGIN
DELETE FROM employee
WHERE employee_id = employee_id;
END;
then my program clearly will not work as intended. In fact, it will always
delete all rows from the table. You can fix this problem by changing the name
of the parameter:
CREATE OR REPLACE PROCEDURE delete_employee (
emp_in IN NUMBER)
IS
BEGIN
DELETE FROM employee
WHERE employee_id = emp_in;
END;
But the problem doesn't necessarily go away forever. What if the DBA adds a
column called "emp_in" to the employee table? Suddenly your program (which
still compiles without error) works very differently. What's a developer to
do?
Well, this tip comes from L. Carl. Pedersen, and goes like this: Fully-qualify all references to PL/SQL data structures inside SQL. This rule would transform the delete_employee procedure to the following:
CREATE OR REPLACE PROCEDURE delete_employee (
emp_in IN NUMBER)
IS
BEGIN
DELETE FROM employee
WHERE employee_id = delete_employee.emp_in;
END;
Now, even if the DBA does add that emp_in column, the procedure will still
work as designed, because there isn't any possibility of confusion.
You can, by the way, qualify your PL/SQL references even if you are in an unnamed or anonymous block
-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Wed Sep 16 1998 - 02:59:45 CEST