Home

Description

When using PL/SQL code, it is important to remember that variables declared multiple times in nested scope will override the original declaration. This means that if a variable is declared in an outer scope and then again in a nested scope, the nested declaration will take precedence. This can lead to unexpected results if the programmer is not aware of the precedence rules. It is important to ensure that variables are only declared once in a given scope to avoid any potential issues.

Key Benefits

• Local Scope: Variables not declared multiple times in nested scope allow for local scope, meaning that variables declared within a certain scope are only accessible within that scope and not outside of it. This helps to prevent unintended side effects from occurring when variables are modified in one scope and unintentionally affect another.
• Easier Debugging: Variables not declared multiple times in nested scope also make debugging easier, as it is easier to trace back the source of a bug when variables are declared in a more organized and structured way.
• More Readable Code: Variables not declared multiple times in nested scope also make code more readable, as it is easier to understand the purpose of a variable when it is declared in a consistent manner.

Non-compliant Code Example

DECLARE
  CURSOR curs_customer(customer_name VARCHAR2) RETURN customer%ROWTYPE;         --Non compliant code (Variable declare more than one time)

  CURSOR curs_customer(customer_name VARCHAR2) RETURN customer%ROWTYPE IS SELECT * FROM CUSTOMERS WHERE name = customer_name; 
BEGIN
  NULL;
END;

Compliant Code Example

DECLARE
  CURSOR curs_customer1(customer_name VARCHAR2) RETURN customer%ROWTYPE;        --Compliant code (Variable declare once)

  CURSOR curs_customer2(customer_name VARCHAR2) RETURN customer%ROWTYPE IS SELECT * FROM CUSTOMERS WHERE name = customer_name; 
BEGIN
  NULL;
END;