0% found this document useful (0 votes)
199 views

Oracle PLSQL Quick Reference Card

The document provides an overview of SQL and PL/SQL statements for manipulating and querying data in Oracle databases. It includes summaries of common statements for working with tables, constraints, transactions, variables, conditions, and loops.

Uploaded by

radugabriel919
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
0% found this document useful (0 votes)
199 views

Oracle PLSQL Quick Reference Card

The document provides an overview of SQL and PL/SQL statements for manipulating and querying data in Oracle databases. It includes summaries of common statements for working with tables, constraints, transactions, variables, conditions, and loops.

Uploaded by

radugabriel919
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 2

Deleting Rows Based on Another Table DROP PRIMARY KEY | UNIQUE (column)

Oracle PL/SQL Quick Reference


DELETE FROM table |CONSTRAINT constraint_name [CASCADE] ;
SELECT Statement WHERE column = (SELECT column Pseudocolumns
SELECT [DISTINCT] {*, column [alias],...}FROM FROM table sequence.NEXTVAL sequence.CURRVAL
table
WHERE condtion) ;
[WHERE condition(s)]
Transaction Control Statements PL/SQL Block Type
[ORDER BY {column, exp, alias} [ASC|DESC]]
COMMIT ;SAVEPOINT Anonymous ​Procedure ​ ​Function
Outer joins name ; [DECLARE] PROCEDURE name​ ​FUNCTION name + ​params la
SELECT table1.*,table2.*FROM ROLLBACK [TO SAVEPOINT name] ; ambele​(IN|OUT| IN OUT data_type )
table1,table2
CREATE TABLE Statement IS RETURN datatype IS[DECLARE]
WHERE table1.column(+) = table2.column [DECLARE]
CREATE TABLE [schema.]table
SELECT table1.*,table2.*FROM BEGIN BEGIN BEGIN
(column datatype [DEFAULT expr] [,...]) ;
table1,table2
CREATE TABLE Statement with Subquery --statements --statements --statements[EXCEPTION]
WHERE table1.column = table2.column(+) [EXCEPTION] [EXCEPTION]END ; END ;
CREATE TABLE [schema.]table
Aggregation Selecting END ;
[(column, column...)]AS
SELECT [column,] group_function(column)FROM
subquery Declaring PL/SQL Variables
table
ALTER TABLE​ Statement (Add columns) identifier [CONSTANT] datatype [NOT NULL][:=|DEFAULT
[WHERE condition]
ALTER TABLE table expr] ;
[GROUP BY
ADD (column datatype [DEFAULT expr][, column Assigning Values to Variables
group_by_expression][HAVING
datatype]...) ; identifier := expr ;
group_condition]
NESTED TABLE​ [col_name]STORE AS [table_name]; Base Scalar Datatypes
[ORDER BY column] ;
Changing a column’s type, size and default of a Table VARCHAR2(n) NUMBER(p,s) DATE CHAR(n)LONG
Group function
ALTER TABLE table LONG RAW BOOLEANBINARY_INTEGER PLS_INTEGER
AVG([DISTINCT|ALL]n)
MODIFY (column datatype [DEFAULT expr][, column The %TYPE
COUNT(*|[DISTINCT|ALL]expr)
datatype]...) ; Attributetable_name.column_name%TYPE
MAX([DISTINCT|ALL]expr)
Dropping a Table ;​variable_name%TYPE ;
MIN([DISTINCT|ALL]expr)
DROP TABLE table ; Composite Datatypes
STDDEV([DISTINCT|ALL]n)
Defineing Constraints TABLE RECORD NESTED TABLE VARRAY
SUM([DISTINCT|ALL]n)
CREATE TABLE ​table_name ​( Creating Bind Variables
VARIANCE([DISTINCT|ALL]n)
column1 datatype​ ​constraint​, VARIABLE variable_name datatype
Subquery
.... Displaying Bind Variables
SELECT select_list
);
FROM table
Column constraint level SELECT Statements in PL/SQL
WHERE expr operator(SELECT select_list FROM table);
column [CONSTRAINT constraint_name] constraint_type, SELECT {column_list|*}
single-row comparison operators
Constraint_type INTO {variable_name[,variable_name]...
= > >= < <= <>
PRIMARY KEY REFERENCES table(column) UNIQUECHECK |record_name}F
multiple-row comparison operators (codition)
IN ANY ALL FROM table
Table constraint level​(except NOT NULL) WHERE condition
Multiple-column Subqueries column,...,[CONSTRAINT constraint_name]
SELECT column, column, ​… Implicit Cursor Attributes for DML statements
constraint_type (column,...), SQL%ROWCOUNTS
​FROM table
NOT NULL Constraint (Only Column Level) SQL%FOUNDSQL
WHERE (column, column, ...) IN(SELECT
column, column, ...FROM table %NOTFOUNDSQL
CONSTRAINT table[_column...]_nn NOT NULL ...UNIQUE %ISOPEN
WHERE condition) ; Key Constraint Constrol Structures
Manipulating Data CONSTRAINT table[_column..]_uk UNIQUE (column[,...]) IF Statement Basic Loop
INSERT Statement(one row) PRIMARY Key Constraint IF condition THEN LOOP
INSERT INTO table [ (column [,column...])]VALUES (value CONSTRAINT table[_column..]_pk PRIMARY (column[,...]) statements ; statements;[ELSIF
[,value...]) ; CREATE TABLE Persons (ID int NOT NULL PRIMARY KEY,); condition THEN ...
INSERT Statement with Subquery FOREIGN Key Constraint statements ;] EXIT [WHEN condition];[ELSE
INSERT INTO table [ column(, column) ] CREATE TABLE Orders ( END LOOP
subquery ; PersonID int FOREIGN KEY REFERENCES Persons(PersonID)); statements;]E
UPDATE Statement CHECK constraint ND IF ;
UPDATE table CREATE TABLE Persons (Age int CHECK (Age>=18)); FOR Loop WHILE Loop
SET column = value [, column = value,...][WHERE Adding a Constraint​(except NOT NULL)ALTER FOR conter in [REVERSE] WHILE condition
condition] ; TABLE table LOOPlower..upper LOOP
Updating with Multiple-column Subquery ADD [CONSTRAINT constraint_name ] type (column) ; statement1;statement1;
UPDATE table Adding a NOT NULL constraint statement2;statement2; ...
SET (column, column,...) =(SELECT ALTER TABLE table ... END LOOP ;END
column, column,... MODIFY (column datatype [DEFAULT expr][CONSTRAINT LOOP;
FROM table constraint_name_nn] NOT NULL) ; Creating a PL/SQL ​Record
WHERE condition)WHERE Dropping a Constraint TYPE record_name_type IS
condition; ALTER TABLE table RECORD(field_declaration[,field_declaration]...) ;
Deleting Rows with DELETE Statement DROP CONSTRAINT constraint_name ; record_name record_name_type ;
DELETE [FROM] table ALTER TABLE table Where field_declaration is
[WHERE conditon] ;
field_name TYPE type_name IS ​TABLE ​OF
{field_type|variable%TYPE|table.colum {column_scalr_type|variable%TYPE|table.column%TYPE
n%TYPE|table%ROWTYPE}[[NOT NULL] |variable%ROWTYPE} [NOT
{:=|DEFAULT} expr] NULL][INDEX BY BINARY_INTEGER];
name ​:=​ salary_list​.​FIRST​; identifier type_name ;​Referencing a
Declaring Records with the %ROWTYPE Attribute WHILE name IS NOT ​null​ LOOP PL/SQL
DECLARE --print name; ​name ​:= tablepl_sql_table_name(primary_key_value
salary_list​.​NEXT​(​name​);
record_name reference%ROWTYPE )
Creating a PL/SQL Table
TOO_MANY_ROWS NVL(expr1,expr2)
Using PL/SQL Table Method
DECODE(col/expr,search1,result1[,search2,result2,...
INVALID_CURSORZERO_DIVIDEDUP_VAL_ON_INDEX
table_name.method_name[(parameters)] ,][,default])
Trapping Exceptions Operators
PL/SQL Table Methods
Comparison = > >= < <= <>
EXITS(n) COUNT FIRST LAST PRIOR(n) EXCEPTION
BETWEEN..AND, IN, LIKE, IS NULLLogical
NEXT(n) EXTEND(n,i) TRIM DELETE WHEN exception1 [OR exception2 ...] THEN
AND OR NOT
PL/SQL Table of Records statement1 ;
TYPE table_name_type IS TABLE OF table_name%ROWTYPEINDEX BY statement2 ;
CREATE OR REPLACE ​TRIGGER​ display_salary_changes
BINARY_INTEGER ; ...
BEFORE DELETE OR INSERT OR UPDATE ON customers
table_name table_name_type ; [WHEN OTHERS THENstatement1 ;statement2 ; FOR EACH ROW
Referencing a Table of Records ...] WHEN ​(​NEW​.​ID ​>​ ​0​)
table_name(index).fied DECLARE
Declaring the ​Cursor​ in Declaration Section Trapping User-Defined Exceptions sal_diff number​;
DECLARE BEGIN
CURSOR cursor_name IS select_statement ​;
sal_diff ​:=​ ​:​NEW​.​salary ​-​ ​:​OLD​.​salary​;
record_name cursor_name%ROWTYPE ; exception EXCEPTION ;BEGIN
END​;
Opening and Closing the Cursor ...
OPEN cursor_name ; IF SQL%NOTFOUND THENRAISE exception ;
END IF ; LABELS
CLOSE cursor_name ;
Fetching Data from the Cursor ...EXCEPTION
GOTO [label_name]
Data Dictionary WHEN exception THEN
statement1 ; …
FETCH cursor_name
...END ; <<label_name>>;
INTO [variable1(,variable2,...)
Functions for Trapping ExceptionsSQLCODE return
|record_name] ;
error code​SQLERRM return error message
Explicit Cusor Attributes
RAISE_APPLICATION_ERROR procedure(Executable/Exception
cursor_name%ISOPENcursor_name%NOTFOU
NDcursor_name%FOUNDcursor_name%ROWCO Section)
UNT RAISE_APPLICATION_ERROR ( error_number,
Cursor FOR Loops message [, {TRUE|​FALSE​}]) ;
FOR record_name IN cursor_name LOOP error_number between -20000 to -20999message string
up to 2,048 bytes long
statement1;
TRUE placed on the stack of previous errors.FALSE
...
replaces all previous errors
END LOOP;
Character Functions
Cursor FOR Loops Using Subqueries
LOWER(column|expression)
FOR record_name IN (subqueries) LOOP
UPPER(column|expression
statement1
INITCAP(column|expression)
...
INSTR(column|expression,m)
END LOOP ;
CONCAT(column1|expression1,column2|expression2}SUBSTR(column|
Cursors with Parameters
expression,m,[n])
CURSOR cursor_name [(cursor_parameter_name datatype
LENGTH(column|expression)LPAD(column|expression,n,’string’)
[,...])]
Number Functions
IS select_statement
MOD(m,n)
[FOR UPDATE [OF column_reference][NOWAIT]];
ROUND(column|expression,n)
Parameter Name
TRUNC(column|expression,n)
cursor_parameter_name [IN] datatype [{:=|DEFAULT}expr]
Date Functions
Openning with Parameters
MONTHS_BETWEEN(date1,date2)
OPEN cursor_name(cursor_parameter_name[,...]);
ADD_MONTHS(date,n)
Cursor FOR Loops with parameters
NEXT_DAY(date,’char’)
LAST_DAY(date)
FOR record_name IN cursor_name(cursor_parameter_name
ROUND(date[,’fmt’])
[,...]) LOOP statement1; statement2; ...
TRUNC(date[,’fmt’])
END LOOP;
Conversion Functions
WHERE CURRENT OF clause
TO_CHAR(number|date[,’fmt’])
UPDATE|DELETE ... WHERE CURRENT OF cursor_name ;
TO_NUMBER(char[,’fmt’])
Predefined Exceptions
TO_DATE(char[,’fmt’])
NO_DATA_FOUND

You might also like