✂️ Text Tools ☰
| Newlines → Commas | |
| List → IN Clause | |
| IN Clause → List | |
| Newlines → ',' | |
| Count Lines, Words, Chars |
🧩 DML ☰
| ☆SELECT |
SELECT * FROM employees;
|
| ☆INSERT |
INSERT INTO dept (id, name) VALUES (1, 'HR');
|
| ☆UPDATE |
UPDATE employees SET salary = 5000 WHERE id = 100;
|
| ☆DELETE |
DELETE FROM employees WHERE id = 100;
|
| ☆INSERT SELECT |
INSERT INTO archive_employees SELECT * FROM employees WHERE status = 'INACTIVE';
|
| ☆MERGE |
MERGE INTO employees e USING (SELECT 100 AS id, 7000 AS salary FROM dual) s ON (e.id = s.id) WHEN MATCHED THEN UPDATE SET e.salary = s.salary WHEN NOT MATCHED THEN INSERT (id, salary) VALUES (s.id, s.salary);
|
| ☆DELETE EXISTS |
DELETE FROM employees e WHERE EXISTS (SELECT 1 FROM dept d WHERE d.id = e.dept_id AND d.status = 'CLOSED');
|
| ☆UPDATE JOIN |
UPDATE employees e SET e.salary = e.salary * 1.1 WHERE e.dept_id IN (SELECT d.id FROM dept d WHERE d.location = 'NY');
|
| ☆INSERT ALL |
INSERT ALL INTO dept (id, name) VALUES (1, 'HR') INTO dept (id, name) VALUES (2, 'IT') SELECT * FROM dual;
|
🏗️ DDL ☰
| ☆CREATE TABLE |
CREATE TABLE dept (id NUMBER, name VARCHAR2(20));
|
| ☆ALTER ADD COLUMN |
ALTER TABLE employees ADD hire_date DATE;
|
| ☆MODIFY COLUMN |
ALTER TABLE employees MODIFY salary NUMBER(10,2);
|
| ☆RENAME COLUMN |
ALTER TABLE employees RENAME COLUMN ename TO full_name;
|
| ☆DROP TABLE |
DROP TABLE dept;
|
| ☆CREATE SEQUENCE |
CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1;
|
| ☆CREATE SYNONYM |
CREATE SYNONYM emp_syn FOR hr.employees;
|
| ☆TRUNCATE TABLE |
TRUNCATE TABLE employees;
|
| ☆COMMENT ON COLUMN |
COMMENT ON COLUMN employees.salary IS 'Monthly gross salary';
|
📊 Aggregation ☰
| ☆Count Employees |
SELECT COUNT(*) FROM employees;
|
| ☆Salary Stats per Dept |
SELECT department_id, COUNT(*), AVG(salary), MAX(salary), MIN(salary) FROM employees GROUP BY department_id;
|
| ☆LISTAGG (Names by Dept) |
SELECT department_id, LISTAGG(last_name, ', ') WITHIN GROUP (ORDER BY last_name) AS names FROM employees GROUP BY department_id;
|
| ☆RANK() by Salary |
SELECT empno, salary, RANK() OVER (ORDER BY salary DESC) AS rank FROM employees;
|
| ☆DENSE_RANK() by Dept |
SELECT empno, deptno, salary, DENSE_RANK() OVER (PARTITION BY deptno ORDER BY salary DESC) AS dept_dense_rank FROM employees;
|
| ☆Total Salary (SUM) |
SELECT SUM(salary) FROM employees;
|
| ☆Average Salary (AVG) |
SELECT AVG(salary) FROM employees;
|
| ☆Max Salary per Dept |
SELECT department_id, MAX(salary) FROM employees GROUP BY department_id;
|
🔗 Joins ☰
| ☆INNER JOIN |
SELECT e.name, d.name FROM emp e JOIN dept d ON e.dept_id = d.id;
|
| ☆LEFT JOIN |
SELECT e.name, d.name FROM emp e LEFT JOIN dept d ON e.dept_id = d.id;
|
| ☆FULL OUTER JOIN |
SELECT e.name, d.name FROM emp e FULL OUTER JOIN dept d ON e.dept_id = d.id;
|
| ☆SELF JOIN |
SELECT e.name, m.name FROM employees e JOIN employees m ON e.manager_id = m.id;
|
| ☆ANTI-JOIN |
SELECT d.name FROM dept d LEFT JOIN emp e ON d.id = e.dept_id WHERE e.id IS NULL;
|
| ☆OUTER JOIN (+) |
SELECT e.name, d.name FROM emp e, dept d WHERE e.dept_id = d.id(+);
|
| ☆CROSS JOIN |
SELECT a.name, b.name FROM emp a CROSS JOIN dept b;
|
| ☆SELF JOIN (ALT) |
SELECT e1.name, e2.name AS mgr FROM emp e1 JOIN emp e2 ON e1.mgr_id = e2.id;
|
🧹 String Functions ☰
| ☆SUBSTR |
SELECT SUBSTR(name, 1, 3) FROM employees;
|
| ☆INSTR |
SELECT INSTR(name, 'e') FROM employees;
|
| ☆REPLACE |
SELECT REPLACE('abcabc', 'a', '*') FROM dual;
|
| ☆LPAD |
SELECT LPAD('123', 5, '0') FROM dual;
|
| ☆LTRIM / RTRIM |
SELECT LTRIM(RTRIM(' Hello ')) FROM dual;
|
🧱 PL/SQL Blocks ☰
| ☆Anonymous Block |
BEGIN DBMS_OUTPUT.PUT_LINE('Hello World'); END;
|
| ☆DECLARE + SELECT INTO |
DECLARE v_count NUMBER; BEGIN SELECT COUNT(*) INTO v_count FROM employees; DBMS_OUTPUT.PUT_LINE(v_count); END;
|
| ☆Procedure |
CREATE OR REPLACE PROCEDURE greet IS BEGIN DBMS_OUTPUT.PUT_LINE('Hi'); END;
|
| ☆Function |
CREATE FUNCTION double_salary (sal NUMBER) RETURN NUMBER IS BEGIN RETURN sal * 2; END;
|
| ☆Exception Block |
BEGIN NULL; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error occurred'); END;
|
❗ Exception Handling ☰
| ☆WHEN OTHERS |
EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
|
| ☆VALUE_ERROR |
EXCEPTION WHEN VALUE_ERROR THEN DBMS_OUTPUT.PUT_LINE('Invalid value: ' || SQLERRM);
|
| ☆NO_DATA_FOUND |
EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('No rows returned');
|
⚙️ Triggers ☰
| ☆BEFORE INSERT |
CREATE TRIGGER trg_bi BEFORE INSERT ON emp FOR EACH ROW BEGIN NULL; END;
|
| ☆AFTER DELETE |
CREATE TRIGGER trg_ad AFTER DELETE ON emp FOR EACH ROW BEGIN NULL; END;
|
| ☆SALARY CHECK |
CREATE OR REPLACE TRIGGER trg_sal_check BEFORE INSERT ON emp FOR EACH ROW BEGIN IF :NEW.salary < 0 THEN RAISE_APPLICATION_ERROR(-20002, 'Negative Salary'); END IF; END;
|
🔧 Built-in Functions ☰
| ☆SYSDATE |
SELECT SYSDATE FROM dual;
|
| ☆NVL |
SELECT NVL(commission_pct, 0) FROM employees;
|
| ☆DECODE |
SELECT DECODE(status, 'A', 'Active', 'I', 'Inactive', 'Unknown') FROM dual;
|
📅 Date Functions ☰
| ☆ADD_MONTHS |
SELECT ADD_MONTHS(SYSDATE, 6) FROM dual;
|
| ☆TRUNC (YEAR) |
SELECT TRUNC(SYSDATE, 'YEAR') FROM dual;
|
| ☆LAST_DAY |
SELECT LAST_DAY(SYSDATE) FROM dual;
|
🔄 Cursors ☰
| ☆DECLARE CURSOR |
CURSOR c_emp IS SELECT * FROM employees;
|
| ☆OPEN / FETCH / CLOSE |
OPEN c1; FETCH c1 INTO var1; CLOSE c1;
|
| ☆FOR LOOP |
FOR rec IN c_emp LOOP DBMS_OUTPUT.PUT_LINE(rec.name); END LOOP;
|
🔁 Transaction Control ☰
| ☆COMMIT |
COMMIT;
|
| ☆ROLLBACK |
ROLLBACK;
|
| ☆SAVEPOINT |
SAVEPOINT stage1;
|
| ☆ROLLBACK TO |
ROLLBACK TO stage1;
|
| ☆SET TRANSACTION |
SET TRANSACTION READ ONLY;
|
🔐 Access Control ☰
| ☆GRANT SELECT |
GRANT SELECT ON employees TO hr_user;
|
| ☆REVOKE UPDATE |
REVOKE UPDATE ON salaries FROM temp_user;
|
| ☆CREATE ROLE |
CREATE ROLE analyst_role;
|
| ☆GRANT ROLE |
GRANT analyst_role TO hr_user;
|
📘 Data Dictionary ☰
| ☆USER_TABLES |
SELECT * FROM USER_TABLES;
|
| ☆USER_TAB_COLUMNS |
SELECT * FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'EMPLOYEES';
|
| ☆USER_CONSTRAINTS |
SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'EMPLOYEES';
|
| ☆ALL_OBJECTS |
SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE = 'TRIGGER';
|
📈 Analytics ☰
| ☆ROW_NUMBER by Dept |
SELECT empno, name, salary, ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY salary DESC) AS rn FROM employees;
|
| ☆LAG / LEAD |
SELECT name, salary, LAG(salary) OVER (ORDER BY salary) AS prev_sal, LEAD(salary) OVER (ORDER BY salary) AS next_sal FROM employees;
|
| ☆FIRST_VALUE |
SELECT deptno, name, FIRST_VALUE(name) OVER (PARTITION BY deptno ORDER BY salary DESC) AS top_earner FROM employees;
|
| ☆Running Total |
SELECT name, salary, SUM(salary) OVER (ORDER BY salary ROWS UNBOUNDED PRECEDING) AS running_total FROM employees;
|
| ☆PIVOT |
SELECT * FROM (SELECT deptno, job, salary FROM employees) PIVOT (SUM(salary) AS total FOR job IN ('CLERK', 'MANAGER', 'ANALYST'));
|
| ☆UNPIVOT |
SELECT deptno, metric, val FROM dept_stats UNPIVOT (val FOR metric IN (min_sal, max_sal, avg_sal));
|
| ☆NTILE Quartiles |
SELECT name, salary, NTILE(4) OVER (ORDER BY salary) AS quartile FROM employees;
|
📦 Bulk Processing ☰
| ☆BULK COLLECT |
DECLARE TYPE t_ids IS TABLE OF NUMBER; v_ids t_ids; BEGIN SELECT id BULK COLLECT INTO v_ids FROM employees; END;
|
| ☆BULK COLLECT LIMIT |
OPEN c; LOOP FETCH c BULK COLLECT INTO v_rows LIMIT 100; EXIT WHEN v_rows.COUNT = 0; END LOOP; CLOSE c;
|
| ☆FORALL INSERT |
FORALL i IN 1..v_ids.COUNT INSERT INTO audit_log (emp_id) VALUES (v_ids(i));
|
| ☆FORALL UPDATE |
FORALL i IN 1..v_ids.COUNT UPDATE employees SET salary = salary * 1.1 WHERE id = v_ids(i);
|
| ☆%ROWTYPE Record |
DECLARE v_emp employees%ROWTYPE; BEGIN SELECT * INTO v_emp FROM employees WHERE id = 100; END;
|
⚡ Dynamic SQL ☰
| ☆EXECUTE IMMEDIATE (DDL) |
BEGIN EXECUTE IMMEDIATE 'CREATE TABLE tmp_t (id NUMBER)'; END;
|
| ☆EXECUTE IMMEDIATE + INTO |
EXECUTE IMMEDIATE 'SELECT name FROM employees WHERE id = :1' INTO v_name USING 100;
|
| ☆RETURNING INTO |
EXECUTE IMMEDIATE 'UPDATE employees SET salary = salary * 1.1 WHERE id = :1 RETURNING salary INTO :2' USING 100 RETURNING INTO v_sal;
|
| ☆Dynamic Ref Cursor |
OPEN rc FOR 'SELECT * FROM ' || v_table;
|
🎁 Packages ☰
| ☆Package Spec |
CREATE OR REPLACE PACKAGE emp_pkg AS PROCEDURE raise_salary(p_id NUMBER, p_pct NUMBER); FUNCTION get_salary(p_id NUMBER) RETURN NUMBER; END emp_pkg;
|
| ☆Package Body |
CREATE OR REPLACE PACKAGE BODY emp_pkg AS PROCEDURE raise_salary(p_id NUMBER, p_pct NUMBER) IS BEGIN UPDATE employees SET salary = salary * (1 + p_pct/100) WHERE id = p_id; END; FUNCTION get_salary(p_id NUMBER) RETURN NUMBER IS v_sal NUMBER; BEGIN SELECT salary INTO v_sal FROM employees WHERE id = p_id; RETURN v_sal; END; END emp_pkg;
|
| ☆Call Package |
BEGIN emp_pkg.raise_salary(100, 10); END;
|
| ☆Package Constant |
CREATE OR REPLACE PACKAGE config_pkg AS c_tax_rate CONSTANT NUMBER := 0.18; END config_pkg;
|
🚀 Performance & Tuning ☰
| ☆EXPLAIN PLAN |
EXPLAIN PLAN FOR SELECT * FROM employees WHERE deptno = 10; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
|
| ☆INDEX Hint |
SELECT /*+ INDEX(e emp_dept_idx) */ * FROM employees e WHERE deptno = 10;
|
| ☆PARALLEL Hint |
SELECT /*+ FULL(e) PARALLEL(e, 4) */ COUNT(*) FROM employees e;
|
| ☆CREATE INDEX |
CREATE INDEX emp_dept_idx ON employees (deptno);
|
| ☆REBUILD INDEX |
ALTER INDEX emp_dept_idx REBUILD ONLINE;
|
| ☆Gather Stats |
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'EMPLOYEES');
|
| ☆Top SQL by Time |
SELECT sql_id, executions, ROUND(elapsed_time/1e6, 1) AS sec FROM v$sql ORDER BY elapsed_time DESC FETCH FIRST 10 ROWS ONLY;
|
🔒 Sessions & Locks ☰
| ☆Active Sessions |
SELECT sid, serial#, username, status, sql_id FROM v$session WHERE status = 'ACTIVE' AND type = 'USER';
|
| ☆Blocking Sessions |
SELECT sid, blocking_session, event, seconds_in_wait FROM v$session WHERE blocking_session IS NOT NULL;
|
| ☆Kill Session |
ALTER SYSTEM KILL SESSION '123,45678' IMMEDIATE;
|
| ☆Locked Objects |
SELECT s.username, o.object_name, l.locked_mode FROM v$lock l JOIN dba_objects o ON l.id1 = o.object_id JOIN v$session s ON l.sid = s.sid WHERE l.type = 'TM';
|
| ☆Session SQL Text |
SELECT s.sid, q.sql_text FROM v$session s JOIN v$sql q ON s.sql_id = q.sql_id WHERE s.username IS NOT NULL;
|
| ☆Long Operations |
SELECT sid, opname, sofar, totalwork, ROUND(sofar/totalwork*100,1) AS pct_done FROM v$session_longops WHERE sofar < totalwork;
|
💾 Storage & Objects ☰
| ☆Tablespace Usage |
SELECT tablespace_name, ROUND(used_percent, 1) AS pct_used FROM dba_tablespace_usage_metrics ORDER BY used_percent DESC;
|
| ☆Invalid Objects |
SELECT owner, object_name, object_type FROM dba_objects WHERE status = 'INVALID';
|
| ☆Recompile Object |
ALTER PROCEDURE proc_name COMPILE;
|
| ☆Recompile Schema |
EXEC DBMS_UTILITY.COMPILE_SCHEMA('HR');
|
| ☆Largest Segments |
SELECT segment_name, segment_type, ROUND(bytes/1048576) AS mb FROM dba_segments WHERE owner = 'HR' ORDER BY bytes DESC FETCH FIRST 20 ROWS ONLY;
|
| ☆Object Counts |
SELECT object_type, COUNT(*) FROM dba_objects WHERE owner = 'HR' GROUP BY object_type ORDER BY 2 DESC;
|
⏰ Scheduler & Jobs ☰
| ☆Create Job |
BEGIN DBMS_SCHEDULER.CREATE_JOB(job_name => 'nightly_stats', job_type => 'PLSQL_BLOCK', job_action => 'BEGIN DBMS_STATS.GATHER_SCHEMA_STATS(''HR''); END;', start_date => SYSTIMESTAMP, repeat_interval => 'FREQ=DAILY;BYHOUR=2', enabled => TRUE); END;
|
| ☆Run Job Now |
EXEC DBMS_SCHEDULER.RUN_JOB('nightly_stats');
|
| ☆Enable / Disable |
EXEC DBMS_SCHEDULER.ENABLE('nightly_stats'); -- or DISABLE
|
| ☆Drop Job |
EXEC DBMS_SCHEDULER.DROP_JOB('nightly_stats');
|
| ☆Job Run History |
SELECT job_name, status, actual_start_date, run_duration FROM user_scheduler_job_run_details ORDER BY actual_start_date DESC FETCH FIRST 20 ROWS ONLY;
|
🌳 Hierarchical Queries ☰
| ☆CONNECT BY |
SELECT empno, name, LEVEL FROM employees START WITH manager_id IS NULL CONNECT BY PRIOR empno = manager_id;
|
| ☆ORDER SIBLINGS |
SELECT empno, name, LEVEL FROM employees START WITH manager_id IS NULL CONNECT BY PRIOR empno = manager_id ORDER SIBLINGS BY name;
|
| ☆Recursive CTE |
WITH org(empno, name, mgr, lvl) AS (SELECT empno, name, manager_id, 1 FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.empno, e.name, e.manager_id, o.lvl + 1 FROM employees e JOIN org o ON e.manager_id = o.empno) SELECT * FROM org;
|
| ☆SYS_CONNECT_BY_PATH |
SELECT SYS_CONNECT_BY_PATH(name, ' > ') AS path FROM employees START WITH manager_id IS NULL CONNECT BY PRIOR empno = manager_id;
|
| ☆Leaf Nodes |
SELECT name FROM employees WHERE CONNECT_BY_ISLEAF = 1 START WITH manager_id IS NULL CONNECT BY PRIOR empno = manager_id;
|
🧾 JSON ☰
| ☆JSON_VALUE |
SELECT JSON_VALUE(doc, '$.name') AS name FROM t_json;
|
| ☆JSON_TABLE |
SELECT jt.* FROM t_json j, JSON_TABLE(j.doc, '$.items[*]' COLUMNS (id NUMBER PATH '$.id', name VARCHAR2(50) PATH '$.name')) jt;
|
| ☆JSON_OBJECT |
SELECT JSON_OBJECT('id' VALUE id, 'name' VALUE name) FROM employees;
|
| ☆JSON_ARRAYAGG |
SELECT JSON_ARRAYAGG(name) FROM employees WHERE deptno = 10;
|
| ☆IS JSON Check |
SELECT * FROM t_json WHERE doc IS JSON;
|