列名の優先順位
ローカル変数または仮パラメータのいずれかと列の両方に属する名前をSQL文で参照すると、列名が優先されます。
注意:
変数名またはパラメータ名が列名として解釈されると、データが誤って削除、変更または挿入されることがあります。
例B-2では、last_name
という名前がローカル変数と列の両方に属しています(名前は大/小文字が区別されません)。そのため、WHERE
句に含まれるlast_name
の参照は両方とも列に解決され、すべての行が削除されます。
例B-3では、変数に異なる名前を付けることで、例B-2の問題を解決しています。
例B-4では、ブロックにラベルを付け、変数名をブロック名で修飾することで、例B-2の問題を解決しています。
例B-5のファンクションdept_name
には、表DEPARTMENTS
の列名が名前になっている仮パラメータとローカル変数があります。パラメータおよび変数の名前をファンクション名で修飾して、列名と区別しています。
例B-2 列名に解釈されるために意図していない結果を招く変数名
DROP TABLE employees2;
CREATE TABLE employees2 AS
SELECT LAST_NAME FROM employees;
DECLARE
last_name VARCHAR2(10) := 'King';
BEGIN
DELETE FROM employees2 WHERE LAST_NAME = last_name;
DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows.');
END;
/
結果:
Deleted 107 rows.
例B-3 異なる変数名の使用による例B-2の修正
DROP TABLE employees2;
CREATE TABLE employees2 AS
SELECT LAST_NAME FROM employees;
DECLARE
v_last_name VARCHAR2(10) := 'King';
BEGIN
DELETE FROM employees2 WHERE LAST_NAME = v_last_name;
DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows.');
END;
/
結果:
Deleted 2 rows.
例B-4 ブロック・ラベルの使用による例B-2の修正
DROP TABLE employees2;
CREATE TABLE employees2 AS
SELECT LAST_NAME FROM employees;
<<main>>
DECLARE
last_name VARCHAR2(10) := 'King';
BEGIN
DELETE FROM employees2 WHERE last_name = main.last_name;
DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows.');
END;
/
結果:
Deleted 2 rows.
例B-5 名前解決でのサブプログラム名
DECLARE
FUNCTION dept_name (department_id IN NUMBER)
RETURN departments.department_name%TYPE
IS
department_name departments.department_name%TYPE;
BEGIN
SELECT department_name INTO dept_name.department_name
-- ^column ^local variable
FROM departments
WHERE department_id = dept_name.department_id;
-- ^column ^formal parameter
RETURN department_name;
END dept_name;
BEGIN
FOR item IN (
SELECT department_id
FROM departments
ORDER BY department_name) LOOP
DBMS_OUTPUT.PUT_LINE ('Department: ' || dept_name(item.department_id));
END LOOP;
END;
/
結果:
Department: Accounting
Department: Administration
Department: Benefits
Department: Construction
Department: Contracting
Department: Control And Credit
Department: Corporate Tax
Department: Executive
Department: Finance
Department: Government Sales
Department: Human Resources
Department: IT
Department: IT Helpdesk
Department: IT Support
Department: Manufacturing
Department: Marketing
Department: NOC
Department: Operations
Department: Payroll
Department: Public Relations
Department: Purchasing
Department: Recruiting
Department: Retail Sales
Department: Sales
Department: Shareholder Services
Department: Shipping
Department: Treasury