PL/SQL language preview

1. A stored procedure

%TYPE, exception handling, and the standalone slash

horizon-dark

CREATE OR REPLACE PROCEDURE raise_salary(p_id NUMBER) IS
  v_salary employees.salary%TYPE;
BEGIN
  SELECT salary INTO v_salary FROM employees WHERE id = p_id;
  v_salary := v_salary * 1.1;
  UPDATE employees SET salary = v_salary WHERE id = p_id;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR(-20001, 'Employee not found');
END;
/

atom-one-dark

CREATE OR REPLACE PROCEDURE raise_salary(p_id NUMBER) IS
  v_salary employees.salary%TYPE;
BEGIN
  SELECT salary INTO v_salary FROM employees WHERE id = p_id;
  v_salary := v_salary * 1.1;
  UPDATE employees SET salary = v_salary WHERE id = p_id;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR(-20001, 'Employee not found');
END;
/

github-dark

CREATE OR REPLACE PROCEDURE raise_salary(p_id NUMBER) IS
  v_salary employees.salary%TYPE;
BEGIN
  SELECT salary INTO v_salary FROM employees WHERE id = p_id;
  v_salary := v_salary * 1.1;
  UPDATE employees SET salary = v_salary WHERE id = p_id;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR(-20001, 'Employee not found');
END;
/

dracula

CREATE OR REPLACE PROCEDURE raise_salary(p_id NUMBER) IS
  v_salary employees.salary%TYPE;
BEGIN
  SELECT salary INTO v_salary FROM employees WHERE id = p_id;
  v_salary := v_salary * 1.1;
  UPDATE employees SET salary = v_salary WHERE id = p_id;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR(-20001, 'Employee not found');
END;
/

nord

CREATE OR REPLACE PROCEDURE raise_salary(p_id NUMBER) IS
  v_salary employees.salary%TYPE;
BEGIN
  SELECT salary INTO v_salary FROM employees WHERE id = p_id;
  v_salary := v_salary * 1.1;
  UPDATE employees SET salary = v_salary WHERE id = p_id;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR(-20001, 'Employee not found');
END;
/

github

CREATE OR REPLACE PROCEDURE raise_salary(p_id NUMBER) IS
  v_salary employees.salary%TYPE;
BEGIN
  SELECT salary INTO v_salary FROM employees WHERE id = p_id;
  v_salary := v_salary * 1.1;
  UPDATE employees SET salary = v_salary WHERE id = p_id;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR(-20001, 'Employee not found');
END;
/

2. Cursors and loops

CURSOR, %FOUND, and a labeled loop

horizon-dark

DECLARE
  CURSOR emp_cur IS SELECT id, name FROM employees;
  v_id employees.id%TYPE;
  v_name employees.name%TYPE;
BEGIN
  OPEN emp_cur;
  <<fetch_loop>>
  LOOP
    FETCH emp_cur INTO v_id, v_name;
    EXIT WHEN emp_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_name);
  END LOOP fetch_loop;
  CLOSE emp_cur;
END;
/

atom-one-dark

DECLARE
  CURSOR emp_cur IS SELECT id, name FROM employees;
  v_id employees.id%TYPE;
  v_name employees.name%TYPE;
BEGIN
  OPEN emp_cur;
  <<fetch_loop>>
  LOOP
    FETCH emp_cur INTO v_id, v_name;
    EXIT WHEN emp_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_name);
  END LOOP fetch_loop;
  CLOSE emp_cur;
END;
/

github-dark

DECLARE
  CURSOR emp_cur IS SELECT id, name FROM employees;
  v_id employees.id%TYPE;
  v_name employees.name%TYPE;
BEGIN
  OPEN emp_cur;
  <<fetch_loop>>
  LOOP
    FETCH emp_cur INTO v_id, v_name;
    EXIT WHEN emp_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_name);
  END LOOP fetch_loop;
  CLOSE emp_cur;
END;
/

dracula

DECLARE
  CURSOR emp_cur IS SELECT id, name FROM employees;
  v_id employees.id%TYPE;
  v_name employees.name%TYPE;
BEGIN
  OPEN emp_cur;
  <<fetch_loop>>
  LOOP
    FETCH emp_cur INTO v_id, v_name;
    EXIT WHEN emp_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_name);
  END LOOP fetch_loop;
  CLOSE emp_cur;
END;
/

nord

DECLARE
  CURSOR emp_cur IS SELECT id, name FROM employees;
  v_id employees.id%TYPE;
  v_name employees.name%TYPE;
BEGIN
  OPEN emp_cur;
  <<fetch_loop>>
  LOOP
    FETCH emp_cur INTO v_id, v_name;
    EXIT WHEN emp_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_name);
  END LOOP fetch_loop;
  CLOSE emp_cur;
END;
/

github

DECLARE
  CURSOR emp_cur IS SELECT id, name FROM employees;
  v_id employees.id%TYPE;
  v_name employees.name%TYPE;
BEGIN
  OPEN emp_cur;
  <<fetch_loop>>
  LOOP
    FETCH emp_cur INTO v_id, v_name;
    EXIT WHEN emp_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_name);
  END LOOP fetch_loop;
  CLOSE emp_cur;
END;
/

3. Bind variables and quoted strings

:bind variables and q'[...]' quoting

horizon-dark

SELECT NVL(:employee_name, 'Unknown') AS name
FROM dual
WHERE description = q'[O'Brien's report]';

atom-one-dark

SELECT NVL(:employee_name, 'Unknown') AS name
FROM dual
WHERE description = q'[O'Brien's report]';

github-dark

SELECT NVL(:employee_name, 'Unknown') AS name
FROM dual
WHERE description = q'[O'Brien's report]';

dracula

SELECT NVL(:employee_name, 'Unknown') AS name
FROM dual
WHERE description = q'[O'Brien's report]';

nord

SELECT NVL(:employee_name, 'Unknown') AS name
FROM dual
WHERE description = q'[O'Brien's report]';

github

SELECT NVL(:employee_name, 'Unknown') AS name
FROM dual
WHERE description = q'[O'Brien's report]';