Learn Oracle PL/SQL www.plsql.co Create table CREATE TABLE table_name (column_name datatype, column_name datatype); CREATE TABLE table_name AS SELECT * FROM table_name2;
Drop table DROP TABLE table_name;
Create view CREATE VIEW view_name AS SELECT * FROM table_name;
Drop view DROP VIEW view_name;
Create user CREATE USER user_name IDENTIFIED BY user_password ;
Drop user DROP USER user_name;
Create index CREATE INDEX index_name ON table_name (column_name);
Drop index DROP INDEX index_name;
Create sequence CREATE SEQUENCE seq_name START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;
Drop sequence DROP SEQUENCE sequence_name;
Create synonym CREATE [PUBLIC] SYNONYM synonym_name FOR object;
Drop synonym DROP [ PUBLIC ] SYNONYM synonym_name;
Create role CREATE ROLE role_name;
Drop role DROP ROLE role_name;
Create function CREATE OR REPLACE FUNCTION function_name RETURN number AS BEGIN RETURN 1; END;
Create function with parameter CREATE OR REPLACE FUNCTION function_name(p_parameter varchar2) RETURN varchar2 AS BEGIN RETURN p_parameter; END;
Drop function DROP FUNCTION function_name;
Create procedure CREATE OR REPLACE PROCEDURE procedure_name AS BEGIN DBMS_OUTPUT.PUT_LINE('Test'); END;
Create procedure with parameter CREATE OR REPLACE PROCEDURE procedure_name (p_parameter number) IS BEGIN DBMS_OUTPUT.PUT_LINE(p_parameter); END;
Drop procedure DROP PROCEDURE procedure_name;
Create trigger CREATE OR REPLACE TRIGGER trigger_name {BEFORE | AFTER | INSTEAD OF} {INSERT | UPDATE | DELETE} [OF column_name] ON table_name [REFERENCING OLD AS old NEW AS new] [FOR EACH ROW] WHEN condition DECLARE -- declare statements BEGIN -- sql statements -- pl/sql statements END;
Drop trigger DROP trigger trigger_name;
Alter trigger ALTER TRIGGER trigger_name COMPILE; ALTER TRIGGER trigger_name RENAME TO new_trigger_name; ALTER TRIGGER trigger_name ENABLE; ALTER TABLE table_name ENABLE ALL TRIGGERS; ALTER TRIGGER trigger_name DISABLE; ALTER TABLE table_name DISABLE ALL TRIGGERS;
Add primary key ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY(coll_name);
Add constraint foreign key ALTER TABLE table1 ADD CONSTRAINT constraint_name FOREIGN KEY (coll_name) REFERENCES table2(coll_name);
Drop constraint key ALTER TABLE table_name DROP CONSTRAINT constraint_name;
Enable a foreign key ALTER TABLE table_name ENABLE CONSTRAINT constraint_name;
Disable foreign key ALTER TABLE table_name DISABLE CONSTRAINT constraint_name;
Insert INSERT INTO table_name VALUES (value1, value2, ...); INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, ...); INSERT INTO table1 (column1, column2) SELECT column1, column2 FROM table2;
Insert All INSERT ALL INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, ...) INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, ...) INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, ...) SELECT * FROM dual;
Update UPDATE table_name SET column_name = value;
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
Delete DELETE FROM table_name; DELETE FROM table_name WHERE condition;
Cartesian Products SELECT * FROM table1, table2;
Inner Join SELECT * FROM table1, table2 WHERE table1.id=table2.id;
Left Join SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id;
Right Join SELECT * FROM table1 RIGHT JOIN table2 ON table1.id=table2.id;
Self Join SELECT * FROM table1 a, table1 b WHERE a.id=b.id;
Full Join SELECT * FROM table1 FULL JOIN table2 ON table1.id=table2.id;
Full Outer Join SELECT * FROM table1 FULL OUTER JOIN table2 ON table1.id=table2.id;
Any SELECT * FROM table_name WHERE column_name = ANY (value1, value2);
Between SELECT * FROM table_name WHERE column_name BETWEEN value1 AND value2;
Exists SELECT * FROM table1 WHERE EXISTS (SELECT * FROM table2);
Not Exists SELECT * FROM table1 WHERE NOT EXISTS (SELECT * FROM table2);
Group By SELECT group_function(col1), col2 FROM table_name GROUP BY col2;
IN SELECT * FROM table_name WHERE column_name IN (value1, value2);
Like SELECT * FROM table_name WHERE column_name LIKE '%expr%';
Order By SELECT * FROM table_name ORDER BY column_name;
Where SELECT * FROM table_name WHERE column_name=value;
The main datatypes: VARCHAR2 NUMBER DATE CHAR PLS_INTEGER BOOLEAN BLOB CLOB
Implicit Cursor Attributes %BULK_ROWCOUNT %BULK_EXCEPTIONS %ISOPEN %FOUND %NOTFOUND %ROWCOUNT
Explicit Cursor DECLARE CURSOR c1 IS SELECT * FROM table_name; v_var c1%ROWTYPE; BEGIN OPEN c1; LOOP FETCH c1 INTO v_var; EXIT WHEN c1%NOTFOUND; END LOOP; CLOSE c1; END;
Collection types Index-by tables(associative arrays) Nested tables Varrays(variable-size arrays)
Collection Methods EXISTS COUNT LIMIT FIRST
LAST PRIOR NEXT EXTEND TRIM DELETE
Collection Exceptions COLLECTION_IS_NULL NO_DATA_FOUND SUBSCRIPT_BEYOND_COUNT SUBSCRIPT_OUTSIDE_LIMIT VALUE_ERROR
Types of records Table-based Cursor-based User-defined
Data Dictionary USER_ALL_TABLES USER_OBJECTS USER_TABLES USER_CONSTRAINTS USER_INDEXES USER_PROCEDURES USER_TRIGGERS ALL_ALL_TABLES ALL_OBJECTS ALL_TABLES ALL_CONSTRAINTS ALL_INDEXES ALL_PROCEDURES ALL_TRIGGERS Resource: www.plsql.co
