Oracle Database
SQL & PL/SQL Fundamentals
Oracle Database: SQL & PL/SQL Fundamentals Oracle Database is a relational database management system with a rich SQL dialect and PL/SQL — a procedural extensio…
Oracle Database: SQL & PL/SQL Fundamentals
Oracle Database is a relational database management system with a rich SQL dialect and PL/SQL — a procedural extension of SQL for writing stored procedures, functions, triggers, and packages.
Oracle SQL Essentials
-- DUAL: Oracle's built-in one-row table for expressions
SELECT SYSDATE FROM DUAL;
SELECT 1 + 1 FROM DUAL;
SELECT USER FROM DUAL;
-- String functions
SELECT UPPER('hello'), LOWER('WORLD'), LENGTH('abc') FROM DUAL;
SELECT SUBSTR('Oracle Database', 1, 6) FROM DUAL; -- 'Oracle' (1-indexed)
SELECT INSTR('Oracle Database', 'a') FROM DUAL; -- first 'a' position
SELECT TRIM(' hello ') FROM DUAL;
SELECT LPAD('42', 5, '0') FROM DUAL; -- '00042'
-- Number functions
SELECT ROUND(3.145, 2) FROM DUAL; -- 3.15
SELECT TRUNC(3.9) FROM DUAL; -- 3 (truncate, not round)
SELECT MOD(10, 3) FROM DUAL; -- 1
SELECT POWER(2, 10) FROM DUAL; -- 1024
SELECT ABS(-5), CEIL(3.1), FLOOR(3.9) FROM DUAL;
-- Date functions
SELECT SYSDATE FROM DUAL; -- current date+time
SELECT SYSTIMESTAMP FROM DUAL; -- with timezone
SELECT ADD_MONTHS(SYSDATE, 3) FROM DUAL;
SELECT MONTHS_BETWEEN(DATE '2024-12-31', DATE '2024-01-01') FROM DUAL;
SELECT LAST_DAY(SYSDATE) FROM DUAL;
SELECT TRUNC(SYSDATE, 'MM') FROM DUAL; -- first day of month
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
SELECT TO_DATE('2024-03-15', 'YYYY-MM-DD') FROM DUAL;
-- NVL / COALESCE (NULL handling)
SELECT NVL(commission, 0) FROM employees;
SELECT COALESCE(col1, col2, 'default') FROM table1;
SELECT NVL2(commission, 'Has commission', 'No commission') FROM employees;Advanced SQL
-- Analytic (Window) functions
SELECT
employee_id,
salary,
department_id,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS overall_rank,
LAG(salary, 1) OVER (ORDER BY hire_date) AS prev_salary,
LEAD(salary, 1) OVER (ORDER BY hire_date) AS next_salary,
SUM(salary) OVER (PARTITION BY department_id) AS dept_total,
AVG(salary) OVER (ORDER BY hire_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM employees;
-- CONNECT BY (hierarchical queries)
SELECT
LEVEL,
LPAD(' ', (LEVEL-1)*2) || last_name AS name_tree,
employee_id,
manager_id
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY last_name;
-- MERGE (upsert)
MERGE INTO target_table t
USING source_table s ON (t.id = s.id)
WHEN MATCHED THEN
UPDATE SET t.value = s.value, t.updated_at = SYSDATE
WHEN NOT MATCHED THEN
INSERT (id, value, created_at) VALUES (s.id, s.value, SYSDATE);
-- Pivot
SELECT * FROM (
SELECT department_id, job_id, salary FROM employees
)
PIVOT (
SUM(salary) FOR job_id IN ('IT_PROG' AS it_prog, 'SA_REP' AS sa_rep)
);PL/SQL
-- Anonymous block
DECLARE
v_name employees.last_name%TYPE;
v_salary NUMBER(10, 2);
BEGIN
SELECT last_name, salary
INTO v_name, v_salary
FROM employees
WHERE employee_id = 100;
DBMS_OUTPUT.PUT_LINE('Name: ' || v_name || ', Salary: ' || v_salary);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee not found');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/
-- Stored procedure
CREATE OR REPLACE PROCEDURE give_raise(
p_emp_id IN employees.employee_id%TYPE,
p_pct IN NUMBER,
p_new_sal OUT employees.salary%TYPE
) AS
BEGIN
UPDATE employees
SET salary = salary * (1 + p_pct / 100)
WHERE employee_id = p_emp_id
RETURNING salary INTO p_new_sal;
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20001, 'Employee ' || p_emp_id || ' not found');
END;
/
-- Function
CREATE OR REPLACE FUNCTION get_annual_salary(p_emp_id IN NUMBER)
RETURN NUMBER AS
v_sal NUMBER;
BEGIN
SELECT salary * 12 INTO v_sal FROM employees WHERE employee_id = p_emp_id;
RETURN v_sal;
END;
/
-- Cursor
DECLARE
CURSOR c_emp IS
SELECT employee_id, last_name, salary FROM employees WHERE department_id = 60;
BEGIN
FOR rec IN c_emp LOOP
DBMS_OUTPUT.PUT_LINE(rec.last_name || ': ' || rec.salary);
END LOOP;
END;
/