Oracle Pl/sql Cheat Sheet printable pdf download

Oracle PL/SQL Quick Reference

WHERE condition) ;

IS ‘text’ ;

Manipulating Data

Dropping a comment from a table

SELECT Statement

INSERT Statement(one row)

COMMENT ON TABLE table | COLUMN table.column IS ‘’ ;

SELECT [DISNCT] {*, column [alias],...}

INSERT INTO table [ (column [,column...])]

Data Dictionary

FROM table

VALUES

(value [,value...]) ;

ALL_OBJECTS

USER_OBJECTS

[WHERE condition(s)]

INSERT Statement with Subquery

ALL_TABLES

USER_TABLES

[ORDER BY {column, exp, alias} [ASC|DESC]]

INSERT INTO table [ column(, column) ]

ALL_CATALOG

USER_CATALOG or CAT

Cartesian Product

subquery ;

ALL_COL_COMMENTS

USER_COL_COMMENTS

SELECT table1.*, table2.*,[...]

UPDATE Statement

ALL_TAB_COMMENTS

USER_TAB_COMMENTS

FROM table1,table2[,...]

UPDATE table

Defineing Constraints

Equijoin(Simple joins or inner join)

SET column = value [, column = value,...]

CREATE TABLE [schema.]table

SELECT table1.*,table2.*

[WHERE condition] ;

(column datatype [DEFAULT expr][NOT NULL]

FROM

table1,table2

Updating with Multiple-column Subquery

[column_constraint],...

WHERE table1.column = table2.column

UPDATE table

[table_constraint][,...]) ;

Non-Equijoins

SET (column, column,...) =

Column constraint level

SELECT table1.*, table2.*

(SELECT column, column,...

column [CONSTRAINT constraint_name] constraint_type,

FROM table1, table2

FROM table

Constraint_type

WHERE table1.column

WHERE condition)

PRIMARY KEY

REFERENCES table(column)

UNIQUE

BETWEEN table2.column1 AND table2.column2

WHERE condition ;

CHECK (codition)

Outer joins

Deleting Rows with DELETE Statement

Table constraint level(except NOT NULL)

SELECT table1.*,table2.*

DELETE [FROM] table

column,...,[CONSTRAINT constraint_name]

FROM

table1,table2

[WHERE conditon] ;

constraint_type (column,...),

WHERE table1.column(+) = table2.column

Deleting Rows Based on Another Table

NOT NULL Constraint (Only Column Level)

SELECT table1.*,table2.*

DELETE FROM table

CONSTRAINT table[_column...]_nn NOT NULL ...

FROM

table1,table2

WHERE column = (SELECT column

UNIQUE Key Constraint

WHERE table1.column = table2.column(+)

FROM table

CONSTRAINT table[_column..]_uk UNIQUE (column[,...])

Self joins

WHERE condtion) ;

PRIMARY Key Constraint

SELECT alias1.*,alias2.*

Transaction Control Statements

CONSTRAINT table[_column..]_pk PRIMARY (column[,...])

FROM

table1 alias1,table1 alias2

COMMIT ;

FOREIGN Key Constraint

WHERE alias1.column = alias2.column

SAVEPOINT name ;

CONSTRAINT table[_column..]_fk

Aggregation Selecting

ROLLBACK [TO SAVEPOINT name] ;

FOREIGN KEY (column[,...])

SELECT [column,] group_function(column)

CREATE TABLE Statement

REFERENCES table (column[,...])[ON DELETE CASCADE]

FROM table

CREATE TABLE [schema.]table

CHECK constraint

[WHERE condition]

(column datatype [DEFAULT expr] [,...]) ;

CONSTRAINT table[_column..]_ck CHECK (condition)

[GROUP BY group_by_expression]

CREATE TABLE Statement with Subquery

Adding a Constraint(except NOT NULL)

[HAVING group_condition]

CREATE TABLE [schema.]table

ALTER TABLE table

[ORDER BY column] ;

[(column, column...)]

ADD [CONSTRAINT constraint_name ] type (column) ;

Group function

AS subquery

Adding a NOT NULL constraint

AVG([DISTINCT|ALL]n)

Datatype

ALTER TABLE table

COUNT(*|[DISTINCT|ALL]expr)

VARCHAR2(size) CHAR(size)

NUMBER(p,s)

DATE

MODIFY (column datatype [DEFAULT expr]

MAX([DISTINCT|ALL]expr)

LONG

CLOB

RAW

LONG RAW

[CONSTRAINT constraint_name_nn] NOT NULL) ;

MIN([DISTINCT|ALL]expr)

BLOB

BFILE

Dropping a Constraint

STDDEV([DISTINCT|ALL]n)

ALTER TABLE Statement (Add columns)

ALTER TABLE table

SUM([DISTINCT|ALL]n)

ALTER TABLE table

DROP CONSTRAINT constraint_name ;

VARIANCE([DISTINCT|ALL]n)

ADD (column datatype [DEFAULT expr]

ALTER TABLE table

Subquery

[, column datatype]...) ;

DROP PRIMARY KEY | UNIQUE (column) |

SELECT select_list

Changing a column’s type, size and default of a Table

CONSTRAINT constraint_name [CASCADE] ;

FROM table

ALTER TABLE table

Disabling Constraints

WHERE expr operator(SELECT select_list FROM table);

MODIFY

(column datatype [DEFAULT expr]

ALTER TABLE table

single-row comparison operators

[, column datatype]...) ;

DISABLE CONSTRAINT constraint_name [CASCADE] ;

Dropping a Table

Enabing Constraints

multiple-row comparison operators

DROP TABLE table ;

ALTER TABLE table

ANY

ALL

Changing the Name of an Object

ENABLE CONSTRAINT constraint_name ;

Multiple-column Subqueries

RENAME old_name TO new_name ;

Data Dictionary

SELECT column, column, ...

Trancating a Table

ALL_CONSTRAINTS

USER_CONSTRAINTS

FROM table

TRUNCATE TABLE table ;

ALL_CONS_COLUMNS

USER_CONS_COLUMNS

WHERE (column, column, ...) IN

Adding Comments to a Table

Creating a View

(SELECT column, column, ...

COMMENT ON TABLE table | COLUMN table.column

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view

FROM table

Rev. January 18,2001

Oracle Pl/sql Cheat Sheet

Related Articles

Related Categories