Oracle SQL and PL/SQL Quick Reference
UPPER() lower() InitCap() LENGTH() ROUND(num, 2) TRUNC(num,2)
SUBS/TR(string, initial (pos: from left, neg: from right), length (optional))
INSTR(string, lookup (case sensitive), starting pos, nth occurrence (optional))
LPAD(string, total length, fill), RPAD(string, total length, fill character)
REPLACE(‘original’, ‘find’, ‘replace’), TRIM(whattotrim FROM string)
MOD(number, divisor) -> return remainder
MONTHS_BETWEEN(date, date), ADD_MONTHS(date, # of months to add)
NEXT_DAY(date, ‘FRIDAY’), LAST_DAY(date) // last day in the month
ROUND(date, ‘MONTH’), TRUNC(date, ‘YEAR’)
TO_NUMBER(value, format) TO_DATE(value, format) TO_CHAR(date, ‘format model’)
start with fm to strip spaces/tabulation YY YYYY YEAR MM MONTH MON
-DY (TLA of week day) DAY DD dth
— HH24:MI:SS AM -> 15:45:32 PM — add character strings by enclosing in double quotes: ” OF “
— th: 14th; sp: fourteen; spth/thsp: fourteenth
L-Local Currency ($) D-Decimalpoint (.) G-group separator (,) (local version)
NVL(possibly null value, replacement value) — data types HAVE to match
NVL2(possibly null value, value IF null, value if NOT null) — data types do not have to match
NULLIF(value 1, value2) — if they match, return null; otherwise, first value.
COALESCE( value1, value2, etc) – returns first non-null value.
subquery: IN, ANY, ALL; — If IN(null, other things) includes a null returns no results.
CREATE TABLE tablename ( fields, constraints )
NOT NULL UNIQUE PRIMARY KEY [FOREIGN KEY] REFERENCES CHECK
ON DELETE CASCADE | SET NULL
SYNTAX: column
column [CONSTRAINT constraint_name]
{PRIMARY KEY | UNIQUE | CHECK(criteria) | REFERENCES table[(column)]}
SYNTAX: table
columns, …
[CONSTRAINT constraint_name constraint_type (column, …),
INSERT INTO table [(column, column…)] VALUES (value[, value…]);
UPDATE table SET column = value [, column = value] [WHERE condition];
DELETE [FROM] table [WHERE condition];
TRUNCATE TABLE table_name; — DDL instead of DML; NO ROLLBACK
CREATE TABLE AS (subquery)
RENAME
DROP TABLE [PURGE]
ALTER TABLE tablename
ADD (column, datatype [DEFAULT expr] ,… ) – can only use NOT NULL if table is empty
MODIFY (same as above)
MODIFY (column [NOT] NULL);
DROP COLUMN (column)
SET UNUSED COLUMN ()
— then later ALTER TABLE
— can increase width or precision of column; cannot decrease unless
- all values are null, table has no rows, OR every value is shorter than the new value.
RENAME COLUMN oldname TO newname
RENAME CONSTRAINT oldname TO newname
CONSTRAINTS:
— cannot modify a constraint;
Can only enable, disable, add not null, add or remove constraints.
ALTER TABLE
ADD [CONSTRAINT ] type ();
DROP CONSTRAINT
ALTER TABLE DROP PRIMARY KEY [CASCADE]; <- drops=”” primary=”” key=”” and=”” connected=”” foreign=””>->
ALTER TABLE DROP UNIQUE (column);
DISABLE CONSTRAINT constraint
ALTER TABLE tablename
ADD [CONSTRAINT table_col_fk]
FOREIGN KEY (fk_col)
REFERENCES [(pk_col)]
ON DELETE [{ | CASCADE | SET NULL}]
FLASHBACK TABLE
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view [(alias, …)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]] (constraints can not be violated)
[WITH READ ONLY [CONSTRAINT constraint]];
DROP VIEW
CREATE SEQUENCE
[INCREMENT BY n] [START WITH n] [MAXVALUE n | NOMAXVALUE}]
[MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}] — when you reach max value, do you start over?
[{CACHE n | NOCACHE}] — default CACHE 20;
ALTER SEQUENCE
.NEXTVAL returns the next sequence value
.CURRVAL returns the current value (must issue NEXTVAL at least once first)
CREATE [UNIQUE] [BITMAP] INDEX ON
CREATE [PUBLIC] SYNONYM FOR object:
CREATE [OR REPLACE] PROCEDURE (
DATE – use CURRENT_DATE instead of SYSDATE for the … wait for it .. Current date.
TIMESTAMP(6) – by default 6 digits of second-related precision.
(0-9) 2014-10-16, 10:02:41.012353
TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH LOCAL TIME ZONE
INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND
BEGIN
MERGE INTO copy_emp c
USING employees e ON (condition)
WHEN MATCHED THEN
UPDATE SET (values)
WHEN NOT MATCHED THEN
INSERT VALUES(values)
END;
TYPE type_name IS RECORD (field_declaration, field declaration, etc)
TYPE ename IS TABLE OF { column_type | variable%TYPE | table.Column%TYPE} [NOT NULL] | table%ROWTYPE INDEX BY { PLS_INTEGER | BINARY_INTEGER | VARCHAR2()];
- cannot be assigned value during DECLARE
-> hashtable
table_name(index) := value
EXISTS(key)
COUNT
FIRST / LAST – smallest/largest
NEXT(key) / PRIOR(key)
DELETE or DELETE(key_to_delete) or DELETE(first_to_delete, last_to_delete)
TYPE type_name IS TABLE OF {column_type | variable%TYPE | table.Column%TYPE} [NOT NULL] | %ROWTYPE
v_name type_name = type_name();
v_name.EXTEND – to add a row
DECLARE
CURSOR cursor_name(parameters) IS select_statement; — use order by to sort
v_record cursor_name%ROWTYPE; — to put it into a record
BEGIN
OPEN cursor_name (parameters);
LOOP
FETCH cursor_name INTO one_or_more_variables_or_a_record.
EXIT WHEN cursor_name%NOTFOUND;
do stuff…
END LOOP
CLOSE cursor_name
SELECT blah FROM blah FOR UPDATE [OF column_reference] [NOWAIT | WAIT n];
— default wait indefinitely; FOR UPDATE OF emp_id only locks tables with emp_id.
WHERE CURRENT OF cursor_name; — allows you to update the current cursor row.
E.G., UPDATE employees SET salary = 25 WHERE CURRENT OF cursor_name;
DECLARE
e_insert_excep EXCEPTION; — declare name of exception
PRAGMA EXCEPTION_INIT(e_insert_excep, -01400); — associate the error # with it
e_invalid_department EXCEPTION; — user defined exception
BEGIN
RAISE e_invalid_department; — must call your own user-defined exceptions
END
EXCEPTION
WHEN e_insert_excep THEN
WHEN e_invalid_department THEN
[ WHEN OTHERS THEN ]
END
SQLERRM – contains error message
SQLCODE – error code #. (NUMBER type) 0 is no exception, 1 is user-defined
To raise an application error, you can use a stored subprogram: raise_application_error (error_number, message[, TRUE|FALSE]
error number -20000 to -20999
message is user-specified
FALSE replaces all previous errors; TRUE keeps them
Can be used in SELECT, UPDATE, INSERT, et cetera IF:
- must be stored in the database
- Accepts only IN parameters with valid SQL data types (not Boolean! Not PLS_integer)
- RETURN only valid SQL Data types
- Parameters must use positional notation; must have EXECUTE permission
- Cannot be called as a DEFAULT or in a CHECK command
IF in a SELECT statement, can not include DML statements (Mutation)
UPDATE or DELETE statement on a table, cannot query or DML the same table. (Mutation)
Cannot include COMMIT or ROLLBACK
Cannot call other subprograms that do the same things.
SELECT name, text FROM user_source WHERE type = ‘FUNCTION’ ORDER BY name, line;
SELECT object_name, object_type from USER_PROCEDURES;
SELECT sequence_name, last_number from USER_SEQUENCES
SELECT index_name, table_owner, table_name, uniqueness FROM user_indexes
SELECT constraint_name, constraint_type, table_name, search_condition, status FROM user_constraints