Ever get confused about when to use a bind variable versus a substitution variable in Oracle PL/SQL?
Attached images provides a clear overview of the different variable types and their use cases and Non-PL/SQL variables like Bind and Host Variables.
Different kinds of variables in Oracle based on where they are used and how they are managed.
1. PL/SQL Variables
DECLARE
v_salary NUMBER := 50000;
BEGIN
DBMS_OUTPUT.PUT_LINE('Salary is ' || v_salary);
END;
/
2.Bind Variables
VARIABLE g_salary NUMBER
BEGIN
:g_salary := 60000;
END;
/
PRINT g_salary
3. Substitution Variables
SELECT * FROM employees
WHERE department_id = &dept_id;
4. System Variables
BEGIN
DBMS_OUTPUT.PUT_LINE('Current User: ' || USER);
DBMS_OUTPUT.PUT_LINE('Today: ' || SYSDATE);
END;
/
5. Cursor Variables (REF CURSOR)
DECLARE
TYPE emp_cur IS REF CURSOR;
v_emp_cursor emp_cur;
BEGIN
OPEN v_emp_cursor FOR SELECT * FROM employees;
-- Process the cursor here
END;
/
Short Memory Tip:
Colon : → Bind Variable
Ampersand & → Substitution Variable
Comments
Post a Comment