Named Calling Notations in PLSQL

Let's walk through the example and see the output when calling the update_employee_info procedure using named notation.

1. Table Creation (Short Version)

CREATE TABLE employees (
emp_id NUMBER(6) PRIMARY KEY,
emp_name VARCHAR2(50),
emp_salary NUMBER(8, 2),
emp_dept VARCHAR2(30) DEFAULT 'IT',
hire_date DATE DEFAULT SYSDATE
);

1. Inserting Sample Data (Short Version)

INSERT INTO employees (emp_id, emp_name, emp_salary, emp_dept, hire_date)
VALUES (101, 'John Doe', 50000, 'Finance', TO_DATE('2024-01-01', 'YYYY-MM-DD'));

INSERT INTO employees (emp_id, emp_name, emp_salary, emp_dept)
VALUES (102, 'Jane Smith', 60000, 'HR');

INSERT INTO employees (emp_id, emp_name, emp_salary)
VALUES (103, 'Robert King', 55000);

INSERT INTO employees (emp_id, emp_name)
VALUES (104, 'Alice Johnson');

This creates the employees table with the necessary fields and inserts sample data into the table.

---

Procedure Definition:

CREATE OR REPLACE PROCEDURE update_employee_info (
p_emp_id IN NUMBER,
p_emp_name IN VARCHAR2,
p_emp_salary IN NUMBER,
p_emp_dept IN VARCHAR2 DEFAULT 'IT'
)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Updating Employee Info:');
DBMS_OUTPUT.PUT_LINE('ID: ' || p_emp_id);
DBMS_OUTPUT.PUT_LINE('Name: ' || p_emp_name);
DBMS_OUTPUT.PUT_LINE('Salary: ' || p_emp_salary);
DBMS_OUTPUT.PUT_LINE('Department: ' || p_emp_dept);
END;
/

1. Example 1: Passing All Parameters (Named Notation)

BEGIN
update_employee_info(
p_emp_id => 101,
p_emp_name => 'John Doe',
p_emp_salary => 50000,
p_emp_dept => 'Finance'
);
END;
/

Output:

Updating Employee Info:
ID: 101
Name: John Doe
Salary: 50000
Department: Finance

1. Example 2: Changing the Order of Parameters (Named Notation)

BEGIN
update_employee_info(
p_emp_name => 'Jane Smith',
p_emp_dept => 'HR',
p_emp_id => 102,
p_emp_salary => 60000
);
END;
/

Output:

Updating Employee Info:
ID: 102
Name: Jane Smith
Salary: 60000
Department: HR

1. Example 3: Omitting Optional Parameter (Relying on Default Value)

BEGIN
update_employee_info(
p_emp_id => 103,
p_emp_name => 'Robert King',
p_emp_salary => 55000
);
END;
/

Output:

Updating Employee Info:
ID: 103
Name: Robert King
Salary: 55000
Department: IT

In this case, since we did not specify the p_emp_dept, it used the default value 'IT'.

1. Example 4: Combining Positional and Named Notation

BEGIN
update_employee_info(104, 'Alice Johnson', p_emp_dept => 'Marketing');
END;
/

Output:

Updating Employee Info:
ID: 104
Name: Alice Johnson
Salary: 0
Department: Marketing

In this case, we passed the employee ID (104), name ('Alice Johnson'), and department ('Marketing'), but the salary was missing because it wasn't provided, and there's no default value for p_emp_salary. It defaults to 0 for a NUMBER datatype.

Summary:

Using named notation helps improve the readability and flexibility of the code, especially when there are multiple parameters with default values. It also ensures that the correct values are passed to the right parameters, regardless of the order in which they are provided.