Explain PL/SQL constants and the %TYPE and %ROWTYPE anchored declarations

How it works

CONSTANT marks a variable as non-reassignable — it must be initialized at declaration and can never be changed afterward. %TYPE anchors a variable's datatype to a column (or another variable), so it tracks that column's definition automatically if it ever changes. %ROWTYPE does the same for an entire row's structure, from a table or a cursor, letting one record variable hold a whole row.

DECLARE
  c_tax_rate       CONSTANT NUMBER := 0.0825;
  v_last_name      employees.last_name%TYPE;
  v_employee_row   employees%ROWTYPE;
BEGIN
  SELECT last_name INTO v_last_name FROM employees WHERE employee_id = 100;
  SELECT * INTO v_employee_row FROM employees WHERE employee_id = 100;
  DBMS_OUTPUT.PUT_LINE(v_last_name || ' salary: ' || v_employee_row.salary);
END;
/

Related Questions