PL/SQL
INTRODUCTION
============
=>BLOCK STRUCTURE:
DECLARE(OPTIONAL)
VARIABLE DECLARATION,CURSORS,USER DEFINED EXCEPTIONS;
BEGIN
->DML,TCL STATEMENTS;
->SELECT ...INTO CLAUSE;
->CONDITIONAL,CONTROL STATEMENTS;
EXCEPTION(OPTIONAL)
->HANDLING RUNTIME ERRORS;
END;(MANDATORY)
=>DECLARING A VARIABLE:
VARIABLENAME DATATYPE(SIZE);
=>STORING A VALUE INTO VARIABLE:
VARIABLENAME:=VALUE;
=>DISPLAY A MESSAGE/VARIABLE VALUE:
DBMS_OUTPUT.PUT_LINE('MESSAGE');
=>SELECT ...INTO CLAUSE:
SELECT COL1,COL2,...INTO VARIABLENAME FROM TABLENAME WHERE CONDITION;
=>VARIABLE ATTRIBUTES:
1.COLUMN LEVEL ATTRIBUTES(%TYPE):
V_COLNAME TABLENAME.COLNAME%TYPE;
2.ROW LEVEL ATTRIBUTES(%ROWTYPE):
VARIABLENAME(I) TABLENAME%ROWTYPE;
=>CONDITIONAL STATEMENTS:
1.IF:
IF CONDITION THEN STATEMENT;
END IF;
2.IF ELSE:
IF CONDITION THEN STATEMENT;
ELSE STATEMENT;
END IF;
3.ELSIF:
IF CONDITON1 THEN STATEMENT1;
ELSIF CONDITION2 THEN STATEMENT;
ELSIF CONDITION3 THEN STATEMENT;
.
.
ELSE STATEMENT;
END IF;
=>CASE STATEMENT:
CASE VARIBLENAME WHEN VALUE1 THEN STATEMENT1;
WHEN VALUE2 THEN STATEMENT2;
ELSE STATEMENT;
END CASE;
=>CASE CONDITIONAL STATEMENT:
CASE WHEN CONDITION1 THEN STATEMENT1;
WHEN CONDITION2 THEN STATEMENT2;
ELSE STATEMENT;
END CASE;
=>CONTROL STATEMENT:
1.SIMPLE LOOP:LOOP STATEMENT;
END LOOP;
2.WHILE LOOP:WHILE TRUECONDITION
LOOP STATEMENT;
END LOOP;
3.FOR LOOP:FOR INDEXVARIABLENAME IN LOWERBOUND..UPPERBOUND
LOOP STATEMENT;
END LOOP;
=>BIND VARIABLE/NON PL/SQL VARIABLENAME:
1.CREATING A BIND VARIABLENAME:
VARIABLE VARIABLENAME DATATYPE;
2.USING BIND VARIABLE:
:BINDVARIABLENAME:=EXPRESSION;
3.DISPLAY VALUE FROM BIND VARIABLE:
PRINT VARIABLENAME;
=>CURSOR:
1.IMPLICIT CURSOR
2.EXPLICIT CURSOR
2.EXPLICIT CURSOR:
EXPLICIT CURSOR LIFE CYCLE:
1.DECLARE:
CURSOR CUSORNAME IS SELECT * FROM TABLENAME WHERE CONDITION;
2.OPEN:
OPEN CURSORNAME;
3.FETCH:
FETCH CURSORNAME INTO VARIABLENAME1,VARIABLENAME2,...;
4.CLOSE:
CLOSE CURSORNAME;
=>EXPLICIT CURSOR ATTRIBUTES:
1.%NOTFOUND:
CUSORNAME%NOTFOUND;
2.%FOUND:
CURSORNAME%FOUND;
3.%ISOPEN:
CURSORNAME%ISOPEN;
4.%ROWCOUNT:
CUSORNAME%ROWCOUNT;
ELIMINATE EXPLICIT CURSOR LIFE CYCLE:
FOR INDEXVARIABLENAME IN CURSORNAME
LOOP STATEMENT;
END LOOP;
PARAMETERIESED CURSORS:
CURSOR CURSORNAME(P_COLNAME DATATYPE) IS SELECT * FROM TABLENAME WHERE CONDITION;
EXCEPTIONS
==========
PRE DEFINED EXCEPTIONS:
1.NO_DATA_FOUND:
WHEN NO_DATA_FOUND THEN STATEMENT;
2.TOO_MANY_ROWS:
WHEN TOO_MANY_ROWS THEN STATEMENT;
3.ZERO_DIVIDE:
WHEN ZERO_DIVIDE THEN STATEMENT;
4.CURSOR_ALREADY_OPEN:
WHEN CURSOR_ALREADY_OPEN THEN STATEMENT;
5.INVALID_CURSOR:
WHEN INVALID_CURSOR THEN STATEMENT;
6.DUP_VAL_ON_INDEX:
WHEN DUP_VAL_ON_INDEX THEN STATEMENT;
7.INVALID_NUMBER:
WHEN INVALID_NUMBER THEN STATEMENT;
8.VALUE_ERROR:
WHEN VALUE_ERROR THEN STATEMENT;
USER DEFINED EXCEPTIONS:
1.DECLARE:
USERDEFINEDEXCEPTIONNAME EXCEPTION;
2.RAISE:
RAISE USERDEFINEDEXCEPTIONNAME;
3.HANDLING EXCEPTION:
WHEN USERDEFINEDEXCEPTIONNAME THEN STATEMENT;
UN-NAMED EXCEPTION:
SYNTAX:PRAGMA EXCEPTION_INIT(USER DEFINED EXCEPTIONNAME,ERROR NUMBER);
RAISE_APPLICATION_ERROR:
SYNTAX:RAISE_APPLICATION_ERROR(ERROR NUMBER,'MESSAGE');
ERROR TRAPPING FUNCTIONS:
1.SQLCODE:DBMS_OUTPUT.PUT_LINE(SQLCODE);
2.SQLERRM:DBMS_OUTPUT.PUT_LINE(SQLERRM);
PROCEDURES
==========
SYNTAX:CREATE OR REPLACE PROCEDURE PROCEDURENAME(FORMAL PARAMETER);
IS/AS
VARIABLE DECLARATION,CURSOR,USER DEFINED EXCEPTIONS;
BEGIN
EXCEPTION
END;
FORMAL PARAMETER:P_COLNAME DATATYPE
EXECUTION OF A PROCEDURE:EXEC PROCEDURENAME(ACTUAL PARAMETER);
BEGIN
PROCEDURENAME(ACTUAL PARAMETER);
END;
CALL PROCEDURENAME(ACTUAL PARAMETER);
MODE:
1.IN MODE:P_COLNAME IN DATATYPE
2.OUT MODE:P_COLNAME OUT DATATYPE
3.IN OUT MODE:P_COLNAME IN OUT DATATYPE
EXECUTION FOR OUT & IN OUT MODES:
USING BIND VARIABLE:VARIABLE VARIABLENAME DATATYPE;
EXEC PROCEDURNAME('VALUE',:VARIABLENAME);
PRINT VARIABLENAME;
USING ANNONYMOS BLOCK:
BEGIN
PROCEDURENAME(ACTUAL PARAMETER,VARIABLENAME);
END;
AUTHID CURRENT_USER:
CREATE OR REPLACE PROCEDURE PROCEDURENAME(FORMAL PARAMETER AUTHID CURRENT_USER)
FUNCTIONS
=========
SYNTAX: CREATE OR REPLACE FUNCTION FUNCTIONNAME(FORMAL PARAMETER)
RETURN DATATYPE
IS/AS
VARIABLE DECLARATION,CURSOR,USER DEFINED EXCEPTIONS;
BEGIN
RETURN EXPRESSION;
EXCEPTION
END[FUNCTION NAME]
EXECUTION OF FUNCTION: SELECT FUNCTIONNAME(ACTUAL PARAMETER) FROM DUAL;
USING ANNOYMOUS BLOCK:BEGIN
VARIABLENAME:=FUNCTIONNAME(ACTUAL PARAMETER);
END;
TRIGGERS
========
1.STATEMENT LEVEL TRIGGER:
CREATE OR REPLACE TRIGGER TRIGGERNAME
BEFORE/AFTER INSERT/UPDATE/DELETE ON TABLENAME
DECLARE
VARIABLE DECLARATION,CURSOR,USER DEFINED EXCEPTIONS;
BEGIN
EXCEPTION
END;
2.ROW LEVEL TRIGGGER:
CREATE OR REPLACE TRIGGER TRIGGERNAME
BEFORE/AFTER INSERT/UPDATE/DELETE ON TABLENAME
FOR EACH ROW
DECLARE
VARIABLE DECLARATION,CURSOR,USER DEFINED EXCEPTIONS;
BEGIN
EXCEPTION
END;
COMPOUND TRIGGERS
=================
SYNTAX:
CREATE OR REPLACE TRIGGER TRIGGERNAME
FOR INSERT/UPDATE/DELETE ON TABLENAME
COMPOUND TRIGGER
GLOBAL VARIABLE DECLARATION;
BEFORE STATEMENT IS
BEGIN
END[BEFORE STATEMENT];
BEFORE EACH ROW IS
BEGIN
END[BEFORE EACH ROW];
AFTER EACH ROW IS
BEGIN
END[AFTER EACH ROW];
AFTER STATEMENT IS
BEGIN
END[AFTER STATEMENT];
END;
FOLLOWS CLAUSE:
CREATE OR REPLACE TRIGGER TRIGGERNAME
BEFORE/AFTER INSERT/DELETE ON TABLENAME
[FOR EACH ROW]
[WHEN CONDITION]
[FOLLOWS ANOTHER TRIGGERNAME]
[DECLARE]
BEGIN
[EXCEPTION]
END;
MUTATING ERROR IN TRIGGERS:
CREATE OR REPLACE TRIGGER TRIGGERNAME
BEFORE/AFTER INSERT/UPDATE/DELETE ON TABLENAME
FOR EACH ROW
DECLARE
VARIABLE DECLARATION
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
COMMIT;
END;
CALLING A PROCEDURE IN TRIGGERS:
CREATE OR REPLACE TRIGGER TRIGGERNAME
BEFORE/AFTER INSERT/UPDATE/DELETE ON TABLENAME
CALL PROCEDURENAME
WHEN CONDITION USED IN TRIGGERS:
CREATE OR REPLACE TRIGGER TRIGGERNAME
BEFORE/AFTER INSERT/UPDATE/DELETE ON TABLENAME
FOR EACH ROW
WHEN(CONDITION)
BEGIN
END;
ENABLE/DISABLE A SINGLE TRIGGER:
ALTER TRIGGER TRIGGERNAME ENABLE/DISABLE;
ENABLE/DISABLE ON TRIGGERS IN A TABLE:
ALTER TABLE TABLENAME ENABLE/DISABLE ALL TRIGGERS;
SYSTEM TRIGGERS:
1.DATABASE LEVEL
2.SCHEMA LEVEL
SYNTAX:
CREATE OR REPLACE TRIGGER TRIGGERNAME
BEFORE/AFTER CREATE/ALTER/DROP/TRUNCATE/RENAME ON DATABASE/USERNAME.SCHEMA
DECLARE
BEGIN
END;
PACKAGES
========
1.PACKAGE SPECIFICATION:
CREATE OR REPLACE PACKAGE PACKAGENAME
IS/AS
GLOBAL VARIABLE DECLARATION,CONSTANT DECLARATIONS;
CURSOR DECLARATIONS;
TYPES DECLARATIONS;
PROCEDURE DECLARATIONS;
FUNCTIONS DECLARATIONS;
END;
2.PACKAGE BODY:
CREATE OR REPLACE PACKAGE BODY PACKAGENAME
IS/AS
PROCEDURE IMPLEMENTATION;
FUNCTION IMPLEMENTATION;
END;
CALLING PACKAGES SUB-PROGRAMES:
1.CALLING PROCEDURES:
SYNTAX:EXEC PACKAGENAME.PROCEDURENAME(ACTUAL PARAMETERS);
BEGIN
PACKAGENAME.PROCEDURENAME(ACTUAL PARAMETER);
END;
2.CALLING FUNCTIONS:
SYNTAX:SELECT PACKAGENAME.FUNCTIONANAME(ACTUAL PARAMETER) FROM DUAL;
BEGIN
VARIABLENAME:=PACKAGENAME.FUNCTIONNAME(ACTUAL PARAMETER);
END;
STATE OF THE GLOBAL VARIABLE IN PACKAGES:
SYNTAX:CREATE OR REPLACE PACKAGE PACKAGENAME
IS
VARIABLENAME:=VALUE;
PRAGMA SERIALLY_REUSABLE;
END;
SYNTAX:BEGIN
PACKAGENAME.VARIABLENAME=VALUE;
END;
SYNTAX:BEGIN
DBMS_OUTPUT.PUT_LINE(PACKAGENAME.VARIABLENAME);
END;
STATE OF THE CURSORS IN PACKAGES:
SYNTAX:CREATE OR REPLACE PACKAGE PACKAGENAME
IS
CURSOR CURSORNAME IS/AS SELECT * FROM TABLENAME;
PRAGMA SERIALLY_REUSABLE;
END;
SYNTAX:BEGIN
OPEN PACKAGENAME.CUSORNAME;
END;
TYPES USED IN PACKAGES
=================
1.PL/SQL RECORD:
SYNTAX:CREATE OR REPLACE PACKAGE PACKAGENAME
IS
TYPE TYPENAME IS RECORD(ATTRIBUTENAME1 DATATYPE(SIZE),ATTRIBUTENAME2 DATATYPE(SIZE),...);
END;
SYNTAX:CREATE OR REPLACE PACKAGE BODY PACKAGENAME
IS
VARIABLENAME TYPENAME;
END;
2.INDEX BY TABLE/PL/SQL TABLE/ASSOCIATION ARRAY:
SYNTAX: TYPE TYPENAME IS TABLE OF DATATYPE(SIZE)
INDEX BY BINARY_INTEGER;
VARIABLENAME TYPENAME;
3.NESTED TABLE:
SYNTAX: TYPE TYPENAME IS TABLE OF DATATYPE(SIZE)
INDEX BY BINARY_INTEGER;
VARIABLENAME TYPENAME:=TYPENAME();
4.VARRAY:
SYNTAX: TYPE TYPENAME IS VARRAY(MAX.SIZE) OF DATATYPE(SIZE);
VARIABLENAME TYPENAME:=TYPENAME();
BULK-BIND
=========
SYNTAX: FORALL INDEXVARIABLEBENAME IN COLLECTIONVARIABLENAME.FIRST..COLLECTIONVARIABLENAME.LAST
DML STATEMENT WHERE COLNAME=COLLECTIONVARIABLENAME(INDEXVARIABLENAME);
BULK-BIND USED IN SELECT ..INTO CLAUSE:
SYNTAX: SELECT * BULK COLLECT INTO VARIABLENAME WHERE CONDITION;
BULK-BIND USED IN CUSOR FETCH STATEMENT:
SYNTAX: FETCH CURSORNAME BULK COLLECT INTO VARIABLENAME LIMIT VALUE;
TO CALCULATING ELAPSED TIME IN PL/SQL BLOCK:
SYNTAX: VARIABLE:=DBMS_UTILITY.GET_TIME;
BULK COLLECT CLAUSE USED IN DML...RETURNING...INTO CLAUSE:
SYNTAX: UPDATE TABLENAME SET COLNAME=NEWVALUE WHERE COLNAME=OLDVALUE RETURNING COLNAME INTO VARIABLENAME;
SYNTAX: PRINT VARIABLENAME;
BULK DELETE: DELETE FROM TABLENAME WHERE COLNAME=VARIABLENAME(INDEXVARIABLENAME);
BULK INSERT: INSERT INTO TABLENAME VALUES(VARIABLENAME(INDEXVARIABLENAME));
BULK EXCEPTIONS: SQL%BULK_EXCEPTIONS(INDEXVARIABLENAME).ERROR_INDEX;
SQL%BULK_EXCEPTIONS(INDEXVARIABLENAME).ERROR_CODE;
HANDLING BULK EXCEPTIONS: FORALL INDEXVARIABLENAME IN COLLECTIONVARIABLENAME.FIRST..COLLECTIONVARIABLENAME.LAST SAVE EXCEPTIONS
DML STATEMENTS WHERE COLNAME=COLLECTIONVARIABLENAME(INDEXVARIABLENAME);
WHEN OTHERS CLAUSE THEN: VARIABLENAME:=SQL%BULK_EXCEPTIONS.COUNT;
DBMS_UTILITY PACKAGE
====================
1.COMMA_TO_TABLE:
DBMS_UTILITY.COMMA_TO_TABLE(STRINGNAME,BINARY_INTEGER VARIABLENAME,INDEX BY TABLE VARIABLENAME);
2.TABLE_TO_COMMA:
DBMS_UTILITY.TABLE_TO_COMMA(INDEX BY TABLE VARIABELENAME,BINARY_INTEGER VARIABLENAME,STRING VARIABLENAMENAME);
DECLARE:
VARIABLENAME DBMS_UTILITY.UNCL_ARRAY;
REF-CURSORS
===========
1.STRONG REF-CURSOR: TYPE TYPENAME IS REF CURSOR RETURN RECORDTYPE DATATYPE;
VARIABLENAME TYPENAME;
2.WEAK REF-CURSOR: TYPE TYPENAME IS REF CURSOR;
VARIABLENAME TYPENAME;
SYNTAX:BEGIN
OPEN REFCURSORVARIABLENAME FOR SELECT * FROM TABLENAME WHERE CONDITION;
END;
SYS_REFCURSOR: REFCURSORVARIABLENAME SYS_REFCURSOR;
DYNAMIC SQL
===========
SYNTAX: BEGIN
EXECUTE IMMEDIATE 'SQL STATEMENT';
END;
TO RETRIEVE DATA: BEGIN
EXECUTE IMMEDIATE 'SELECT * FROM TABELNAME'INTO VARIABLENAME;
END;
INTRODUCTION
============
=>BLOCK STRUCTURE:
DECLARE(OPTIONAL)
VARIABLE DECLARATION,CURSORS,USER DEFINED EXCEPTIONS;
BEGIN
->DML,TCL STATEMENTS;
->SELECT ...INTO CLAUSE;
->CONDITIONAL,CONTROL STATEMENTS;
EXCEPTION(OPTIONAL)
->HANDLING RUNTIME ERRORS;
END;(MANDATORY)
=>DECLARING A VARIABLE:
VARIABLENAME DATATYPE(SIZE);
=>STORING A VALUE INTO VARIABLE:
VARIABLENAME:=VALUE;
=>DISPLAY A MESSAGE/VARIABLE VALUE:
DBMS_OUTPUT.PUT_LINE('MESSAGE');
=>SELECT ...INTO CLAUSE:
SELECT COL1,COL2,...INTO VARIABLENAME FROM TABLENAME WHERE CONDITION;
=>VARIABLE ATTRIBUTES:
1.COLUMN LEVEL ATTRIBUTES(%TYPE):
V_COLNAME TABLENAME.COLNAME%TYPE;
2.ROW LEVEL ATTRIBUTES(%ROWTYPE):
VARIABLENAME(I) TABLENAME%ROWTYPE;
=>CONDITIONAL STATEMENTS:
1.IF:
IF CONDITION THEN STATEMENT;
END IF;
2.IF ELSE:
IF CONDITION THEN STATEMENT;
ELSE STATEMENT;
END IF;
3.ELSIF:
IF CONDITON1 THEN STATEMENT1;
ELSIF CONDITION2 THEN STATEMENT;
ELSIF CONDITION3 THEN STATEMENT;
.
.
ELSE STATEMENT;
END IF;
=>CASE STATEMENT:
CASE VARIBLENAME WHEN VALUE1 THEN STATEMENT1;
WHEN VALUE2 THEN STATEMENT2;
ELSE STATEMENT;
END CASE;
=>CASE CONDITIONAL STATEMENT:
CASE WHEN CONDITION1 THEN STATEMENT1;
WHEN CONDITION2 THEN STATEMENT2;
ELSE STATEMENT;
END CASE;
=>CONTROL STATEMENT:
1.SIMPLE LOOP:LOOP STATEMENT;
END LOOP;
2.WHILE LOOP:WHILE TRUECONDITION
LOOP STATEMENT;
END LOOP;
3.FOR LOOP:FOR INDEXVARIABLENAME IN LOWERBOUND..UPPERBOUND
LOOP STATEMENT;
END LOOP;
=>BIND VARIABLE/NON PL/SQL VARIABLENAME:
1.CREATING A BIND VARIABLENAME:
VARIABLE VARIABLENAME DATATYPE;
2.USING BIND VARIABLE:
:BINDVARIABLENAME:=EXPRESSION;
3.DISPLAY VALUE FROM BIND VARIABLE:
PRINT VARIABLENAME;
=>CURSOR:
1.IMPLICIT CURSOR
2.EXPLICIT CURSOR
2.EXPLICIT CURSOR:
EXPLICIT CURSOR LIFE CYCLE:
1.DECLARE:
CURSOR CUSORNAME IS SELECT * FROM TABLENAME WHERE CONDITION;
2.OPEN:
OPEN CURSORNAME;
3.FETCH:
FETCH CURSORNAME INTO VARIABLENAME1,VARIABLENAME2,...;
4.CLOSE:
CLOSE CURSORNAME;
=>EXPLICIT CURSOR ATTRIBUTES:
1.%NOTFOUND:
CUSORNAME%NOTFOUND;
2.%FOUND:
CURSORNAME%FOUND;
3.%ISOPEN:
CURSORNAME%ISOPEN;
4.%ROWCOUNT:
CUSORNAME%ROWCOUNT;
ELIMINATE EXPLICIT CURSOR LIFE CYCLE:
FOR INDEXVARIABLENAME IN CURSORNAME
LOOP STATEMENT;
END LOOP;
PARAMETERIESED CURSORS:
CURSOR CURSORNAME(P_COLNAME DATATYPE) IS SELECT * FROM TABLENAME WHERE CONDITION;
EXCEPTIONS
==========
PRE DEFINED EXCEPTIONS:
1.NO_DATA_FOUND:
WHEN NO_DATA_FOUND THEN STATEMENT;
2.TOO_MANY_ROWS:
WHEN TOO_MANY_ROWS THEN STATEMENT;
3.ZERO_DIVIDE:
WHEN ZERO_DIVIDE THEN STATEMENT;
4.CURSOR_ALREADY_OPEN:
WHEN CURSOR_ALREADY_OPEN THEN STATEMENT;
5.INVALID_CURSOR:
WHEN INVALID_CURSOR THEN STATEMENT;
6.DUP_VAL_ON_INDEX:
WHEN DUP_VAL_ON_INDEX THEN STATEMENT;
7.INVALID_NUMBER:
WHEN INVALID_NUMBER THEN STATEMENT;
8.VALUE_ERROR:
WHEN VALUE_ERROR THEN STATEMENT;
USER DEFINED EXCEPTIONS:
1.DECLARE:
USERDEFINEDEXCEPTIONNAME EXCEPTION;
2.RAISE:
RAISE USERDEFINEDEXCEPTIONNAME;
3.HANDLING EXCEPTION:
WHEN USERDEFINEDEXCEPTIONNAME THEN STATEMENT;
UN-NAMED EXCEPTION:
SYNTAX:PRAGMA EXCEPTION_INIT(USER DEFINED EXCEPTIONNAME,ERROR NUMBER);
RAISE_APPLICATION_ERROR:
SYNTAX:RAISE_APPLICATION_ERROR(ERROR NUMBER,'MESSAGE');
ERROR TRAPPING FUNCTIONS:
1.SQLCODE:DBMS_OUTPUT.PUT_LINE(SQLCODE);
2.SQLERRM:DBMS_OUTPUT.PUT_LINE(SQLERRM);
PROCEDURES
==========
SYNTAX:CREATE OR REPLACE PROCEDURE PROCEDURENAME(FORMAL PARAMETER);
IS/AS
VARIABLE DECLARATION,CURSOR,USER DEFINED EXCEPTIONS;
BEGIN
EXCEPTION
END;
FORMAL PARAMETER:P_COLNAME DATATYPE
EXECUTION OF A PROCEDURE:EXEC PROCEDURENAME(ACTUAL PARAMETER);
BEGIN
PROCEDURENAME(ACTUAL PARAMETER);
END;
CALL PROCEDURENAME(ACTUAL PARAMETER);
MODE:
1.IN MODE:P_COLNAME IN DATATYPE
2.OUT MODE:P_COLNAME OUT DATATYPE
3.IN OUT MODE:P_COLNAME IN OUT DATATYPE
EXECUTION FOR OUT & IN OUT MODES:
USING BIND VARIABLE:VARIABLE VARIABLENAME DATATYPE;
EXEC PROCEDURNAME('VALUE',:VARIABLENAME);
PRINT VARIABLENAME;
USING ANNONYMOS BLOCK:
BEGIN
PROCEDURENAME(ACTUAL PARAMETER,VARIABLENAME);
END;
AUTHID CURRENT_USER:
CREATE OR REPLACE PROCEDURE PROCEDURENAME(FORMAL PARAMETER AUTHID CURRENT_USER)
FUNCTIONS
=========
SYNTAX: CREATE OR REPLACE FUNCTION FUNCTIONNAME(FORMAL PARAMETER)
RETURN DATATYPE
IS/AS
VARIABLE DECLARATION,CURSOR,USER DEFINED EXCEPTIONS;
BEGIN
RETURN EXPRESSION;
EXCEPTION
END[FUNCTION NAME]
EXECUTION OF FUNCTION: SELECT FUNCTIONNAME(ACTUAL PARAMETER) FROM DUAL;
USING ANNOYMOUS BLOCK:BEGIN
VARIABLENAME:=FUNCTIONNAME(ACTUAL PARAMETER);
END;
TRIGGERS
========
1.STATEMENT LEVEL TRIGGER:
CREATE OR REPLACE TRIGGER TRIGGERNAME
BEFORE/AFTER INSERT/UPDATE/DELETE ON TABLENAME
DECLARE
VARIABLE DECLARATION,CURSOR,USER DEFINED EXCEPTIONS;
BEGIN
EXCEPTION
END;
2.ROW LEVEL TRIGGGER:
CREATE OR REPLACE TRIGGER TRIGGERNAME
BEFORE/AFTER INSERT/UPDATE/DELETE ON TABLENAME
FOR EACH ROW
DECLARE
VARIABLE DECLARATION,CURSOR,USER DEFINED EXCEPTIONS;
BEGIN
EXCEPTION
END;
COMPOUND TRIGGERS
=================
SYNTAX:
CREATE OR REPLACE TRIGGER TRIGGERNAME
FOR INSERT/UPDATE/DELETE ON TABLENAME
COMPOUND TRIGGER
GLOBAL VARIABLE DECLARATION;
BEFORE STATEMENT IS
BEGIN
END[BEFORE STATEMENT];
BEFORE EACH ROW IS
BEGIN
END[BEFORE EACH ROW];
AFTER EACH ROW IS
BEGIN
END[AFTER EACH ROW];
AFTER STATEMENT IS
BEGIN
END[AFTER STATEMENT];
END;
FOLLOWS CLAUSE:
CREATE OR REPLACE TRIGGER TRIGGERNAME
BEFORE/AFTER INSERT/DELETE ON TABLENAME
[FOR EACH ROW]
[WHEN CONDITION]
[FOLLOWS ANOTHER TRIGGERNAME]
[DECLARE]
BEGIN
[EXCEPTION]
END;
MUTATING ERROR IN TRIGGERS:
CREATE OR REPLACE TRIGGER TRIGGERNAME
BEFORE/AFTER INSERT/UPDATE/DELETE ON TABLENAME
FOR EACH ROW
DECLARE
VARIABLE DECLARATION
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
COMMIT;
END;
CALLING A PROCEDURE IN TRIGGERS:
CREATE OR REPLACE TRIGGER TRIGGERNAME
BEFORE/AFTER INSERT/UPDATE/DELETE ON TABLENAME
CALL PROCEDURENAME
WHEN CONDITION USED IN TRIGGERS:
CREATE OR REPLACE TRIGGER TRIGGERNAME
BEFORE/AFTER INSERT/UPDATE/DELETE ON TABLENAME
FOR EACH ROW
WHEN(CONDITION)
BEGIN
END;
ENABLE/DISABLE A SINGLE TRIGGER:
ALTER TRIGGER TRIGGERNAME ENABLE/DISABLE;
ENABLE/DISABLE ON TRIGGERS IN A TABLE:
ALTER TABLE TABLENAME ENABLE/DISABLE ALL TRIGGERS;
SYSTEM TRIGGERS:
1.DATABASE LEVEL
2.SCHEMA LEVEL
SYNTAX:
CREATE OR REPLACE TRIGGER TRIGGERNAME
BEFORE/AFTER CREATE/ALTER/DROP/TRUNCATE/RENAME ON DATABASE/USERNAME.SCHEMA
DECLARE
BEGIN
END;
PACKAGES
========
1.PACKAGE SPECIFICATION:
CREATE OR REPLACE PACKAGE PACKAGENAME
IS/AS
GLOBAL VARIABLE DECLARATION,CONSTANT DECLARATIONS;
CURSOR DECLARATIONS;
TYPES DECLARATIONS;
PROCEDURE DECLARATIONS;
FUNCTIONS DECLARATIONS;
END;
2.PACKAGE BODY:
CREATE OR REPLACE PACKAGE BODY PACKAGENAME
IS/AS
PROCEDURE IMPLEMENTATION;
FUNCTION IMPLEMENTATION;
END;
CALLING PACKAGES SUB-PROGRAMES:
1.CALLING PROCEDURES:
SYNTAX:EXEC PACKAGENAME.PROCEDURENAME(ACTUAL PARAMETERS);
BEGIN
PACKAGENAME.PROCEDURENAME(ACTUAL PARAMETER);
END;
2.CALLING FUNCTIONS:
SYNTAX:SELECT PACKAGENAME.FUNCTIONANAME(ACTUAL PARAMETER) FROM DUAL;
BEGIN
VARIABLENAME:=PACKAGENAME.FUNCTIONNAME(ACTUAL PARAMETER);
END;
STATE OF THE GLOBAL VARIABLE IN PACKAGES:
SYNTAX:CREATE OR REPLACE PACKAGE PACKAGENAME
IS
VARIABLENAME:=VALUE;
PRAGMA SERIALLY_REUSABLE;
END;
SYNTAX:BEGIN
PACKAGENAME.VARIABLENAME=VALUE;
END;
SYNTAX:BEGIN
DBMS_OUTPUT.PUT_LINE(PACKAGENAME.VARIABLENAME);
END;
STATE OF THE CURSORS IN PACKAGES:
SYNTAX:CREATE OR REPLACE PACKAGE PACKAGENAME
IS
CURSOR CURSORNAME IS/AS SELECT * FROM TABLENAME;
PRAGMA SERIALLY_REUSABLE;
END;
SYNTAX:BEGIN
OPEN PACKAGENAME.CUSORNAME;
END;
TYPES USED IN PACKAGES
=================
1.PL/SQL RECORD:
SYNTAX:CREATE OR REPLACE PACKAGE PACKAGENAME
IS
TYPE TYPENAME IS RECORD(ATTRIBUTENAME1 DATATYPE(SIZE),ATTRIBUTENAME2 DATATYPE(SIZE),...);
END;
SYNTAX:CREATE OR REPLACE PACKAGE BODY PACKAGENAME
IS
VARIABLENAME TYPENAME;
END;
2.INDEX BY TABLE/PL/SQL TABLE/ASSOCIATION ARRAY:
SYNTAX: TYPE TYPENAME IS TABLE OF DATATYPE(SIZE)
INDEX BY BINARY_INTEGER;
VARIABLENAME TYPENAME;
3.NESTED TABLE:
SYNTAX: TYPE TYPENAME IS TABLE OF DATATYPE(SIZE)
INDEX BY BINARY_INTEGER;
VARIABLENAME TYPENAME:=TYPENAME();
4.VARRAY:
SYNTAX: TYPE TYPENAME IS VARRAY(MAX.SIZE) OF DATATYPE(SIZE);
VARIABLENAME TYPENAME:=TYPENAME();
BULK-BIND
=========
SYNTAX: FORALL INDEXVARIABLEBENAME IN COLLECTIONVARIABLENAME.FIRST..COLLECTIONVARIABLENAME.LAST
DML STATEMENT WHERE COLNAME=COLLECTIONVARIABLENAME(INDEXVARIABLENAME);
BULK-BIND USED IN SELECT ..INTO CLAUSE:
SYNTAX: SELECT * BULK COLLECT INTO VARIABLENAME WHERE CONDITION;
BULK-BIND USED IN CUSOR FETCH STATEMENT:
SYNTAX: FETCH CURSORNAME BULK COLLECT INTO VARIABLENAME LIMIT VALUE;
TO CALCULATING ELAPSED TIME IN PL/SQL BLOCK:
SYNTAX: VARIABLE:=DBMS_UTILITY.GET_TIME;
BULK COLLECT CLAUSE USED IN DML...RETURNING...INTO CLAUSE:
SYNTAX: UPDATE TABLENAME SET COLNAME=NEWVALUE WHERE COLNAME=OLDVALUE RETURNING COLNAME INTO VARIABLENAME;
SYNTAX: PRINT VARIABLENAME;
BULK DELETE: DELETE FROM TABLENAME WHERE COLNAME=VARIABLENAME(INDEXVARIABLENAME);
BULK INSERT: INSERT INTO TABLENAME VALUES(VARIABLENAME(INDEXVARIABLENAME));
BULK EXCEPTIONS: SQL%BULK_EXCEPTIONS(INDEXVARIABLENAME).ERROR_INDEX;
SQL%BULK_EXCEPTIONS(INDEXVARIABLENAME).ERROR_CODE;
HANDLING BULK EXCEPTIONS: FORALL INDEXVARIABLENAME IN COLLECTIONVARIABLENAME.FIRST..COLLECTIONVARIABLENAME.LAST SAVE EXCEPTIONS
DML STATEMENTS WHERE COLNAME=COLLECTIONVARIABLENAME(INDEXVARIABLENAME);
WHEN OTHERS CLAUSE THEN: VARIABLENAME:=SQL%BULK_EXCEPTIONS.COUNT;
DBMS_UTILITY PACKAGE
====================
1.COMMA_TO_TABLE:
DBMS_UTILITY.COMMA_TO_TABLE(STRINGNAME,BINARY_INTEGER VARIABLENAME,INDEX BY TABLE VARIABLENAME);
2.TABLE_TO_COMMA:
DBMS_UTILITY.TABLE_TO_COMMA(INDEX BY TABLE VARIABELENAME,BINARY_INTEGER VARIABLENAME,STRING VARIABLENAMENAME);
DECLARE:
VARIABLENAME DBMS_UTILITY.UNCL_ARRAY;
REF-CURSORS
===========
1.STRONG REF-CURSOR: TYPE TYPENAME IS REF CURSOR RETURN RECORDTYPE DATATYPE;
VARIABLENAME TYPENAME;
2.WEAK REF-CURSOR: TYPE TYPENAME IS REF CURSOR;
VARIABLENAME TYPENAME;
SYNTAX:BEGIN
OPEN REFCURSORVARIABLENAME FOR SELECT * FROM TABLENAME WHERE CONDITION;
END;
SYS_REFCURSOR: REFCURSORVARIABLENAME SYS_REFCURSOR;
DYNAMIC SQL
===========
SYNTAX: BEGIN
EXECUTE IMMEDIATE 'SQL STATEMENT';
END;
TO RETRIEVE DATA: BEGIN
EXECUTE IMMEDIATE 'SELECT * FROM TABELNAME'INTO VARIABLENAME;
END;
INTRODUCTION
============
=>BLOCK STRUCTURE:
DECLARE(OPTIONAL)
VARIABLE DECLARATION,CURSORS,USER DEFINED EXCEPTIONS;
BEGIN
->DML,TCL STATEMENTS;
->SELECT ...INTO CLAUSE;
->CONDITIONAL,CONTROL STATEMENTS;
EXCEPTION(OPTIONAL)
->HANDLING RUNTIME ERRORS;
END;(MANDATORY)
=>DECLARING A VARIABLE:
VARIABLENAME DATATYPE(SIZE);
=>STORING A VALUE INTO VARIABLE:
VARIABLENAME:=VALUE;
=>DISPLAY A MESSAGE/VARIABLE VALUE:
DBMS_OUTPUT.PUT_LINE('MESSAGE');
=>SELECT ...INTO CLAUSE:
SELECT COL1,COL2,...INTO VARIABLENAME FROM TABLENAME WHERE CONDITION;
=>VARIABLE ATTRIBUTES:
1.COLUMN LEVEL ATTRIBUTES(%TYPE):
V_COLNAME TABLENAME.COLNAME%TYPE;
2.ROW LEVEL ATTRIBUTES(%ROWTYPE):
VARIABLENAME(I) TABLENAME%ROWTYPE;
=>CONDITIONAL STATEMENTS:
1.IF:
IF CONDITION THEN STATEMENT;
END IF;
2.IF ELSE:
IF CONDITION THEN STATEMENT;
ELSE STATEMENT;
END IF;
3.ELSIF:
IF CONDITON1 THEN STATEMENT1;
ELSIF CONDITION2 THEN STATEMENT;
ELSIF CONDITION3 THEN STATEMENT;
.
.
ELSE STATEMENT;
END IF;
=>CASE STATEMENT:
CASE VARIBLENAME WHEN VALUE1 THEN STATEMENT1;
WHEN VALUE2 THEN STATEMENT2;
ELSE STATEMENT;
END CASE;
=>CASE CONDITIONAL STATEMENT:
CASE WHEN CONDITION1 THEN STATEMENT1;
WHEN CONDITION2 THEN STATEMENT2;
ELSE STATEMENT;
END CASE;
=>CONTROL STATEMENT:
1.SIMPLE LOOP:LOOP STATEMENT;
END LOOP;
2.WHILE LOOP:WHILE TRUECONDITION
LOOP STATEMENT;
END LOOP;
3.FOR LOOP:FOR INDEXVARIABLENAME IN LOWERBOUND..UPPERBOUND
LOOP STATEMENT;
END LOOP;
=>BIND VARIABLE/NON PL/SQL VARIABLENAME:
1.CREATING A BIND VARIABLENAME:
VARIABLE VARIABLENAME DATATYPE;
2.USING BIND VARIABLE:
:BINDVARIABLENAME:=EXPRESSION;
3.DISPLAY VALUE FROM BIND VARIABLE:
PRINT VARIABLENAME;
=>CURSOR:
1.IMPLICIT CURSOR
2.EXPLICIT CURSOR
2.EXPLICIT CURSOR:
EXPLICIT CURSOR LIFE CYCLE:
1.DECLARE:
CURSOR CUSORNAME IS SELECT * FROM TABLENAME WHERE CONDITION;
2.OPEN:
OPEN CURSORNAME;
3.FETCH:
FETCH CURSORNAME INTO VARIABLENAME1,VARIABLENAME2,...;
4.CLOSE:
CLOSE CURSORNAME;
=>EXPLICIT CURSOR ATTRIBUTES:
1.%NOTFOUND:
CUSORNAME%NOTFOUND;
2.%FOUND:
CURSORNAME%FOUND;
3.%ISOPEN:
CURSORNAME%ISOPEN;
4.%ROWCOUNT:
CUSORNAME%ROWCOUNT;
ELIMINATE EXPLICIT CURSOR LIFE CYCLE:
FOR INDEXVARIABLENAME IN CURSORNAME
LOOP STATEMENT;
END LOOP;
PARAMETERIESED CURSORS:
CURSOR CURSORNAME(P_COLNAME DATATYPE) IS SELECT * FROM TABLENAME WHERE CONDITION;
EXCEPTIONS
==========
PRE DEFINED EXCEPTIONS:
1.NO_DATA_FOUND:
WHEN NO_DATA_FOUND THEN STATEMENT;
2.TOO_MANY_ROWS:
WHEN TOO_MANY_ROWS THEN STATEMENT;
3.ZERO_DIVIDE:
WHEN ZERO_DIVIDE THEN STATEMENT;
4.CURSOR_ALREADY_OPEN:
WHEN CURSOR_ALREADY_OPEN THEN STATEMENT;
5.INVALID_CURSOR:
WHEN INVALID_CURSOR THEN STATEMENT;
6.DUP_VAL_ON_INDEX:
WHEN DUP_VAL_ON_INDEX THEN STATEMENT;
7.INVALID_NUMBER:
WHEN INVALID_NUMBER THEN STATEMENT;
8.VALUE_ERROR:
WHEN VALUE_ERROR THEN STATEMENT;
USER DEFINED EXCEPTIONS:
1.DECLARE:
USERDEFINEDEXCEPTIONNAME EXCEPTION;
2.RAISE:
RAISE USERDEFINEDEXCEPTIONNAME;
3.HANDLING EXCEPTION:
WHEN USERDEFINEDEXCEPTIONNAME THEN STATEMENT;
UN-NAMED EXCEPTION:
SYNTAX:PRAGMA EXCEPTION_INIT(USER DEFINED EXCEPTIONNAME,ERROR NUMBER);
RAISE_APPLICATION_ERROR:
SYNTAX:RAISE_APPLICATION_ERROR(ERROR NUMBER,'MESSAGE');
ERROR TRAPPING FUNCTIONS:
1.SQLCODE:DBMS_OUTPUT.PUT_LINE(SQLCODE);
2.SQLERRM:DBMS_OUTPUT.PUT_LINE(SQLERRM);
PROCEDURES
==========
SYNTAX:CREATE OR REPLACE PROCEDURE PROCEDURENAME(FORMAL PARAMETER);
IS/AS
VARIABLE DECLARATION,CURSOR,USER DEFINED EXCEPTIONS;
BEGIN
EXCEPTION
END;
FORMAL PARAMETER:P_COLNAME DATATYPE
EXECUTION OF A PROCEDURE:EXEC PROCEDURENAME(ACTUAL PARAMETER);
BEGIN
PROCEDURENAME(ACTUAL PARAMETER);
END;
CALL PROCEDURENAME(ACTUAL PARAMETER);
MODE:
1.IN MODE:P_COLNAME IN DATATYPE
2.OUT MODE:P_COLNAME OUT DATATYPE
3.IN OUT MODE:P_COLNAME IN OUT DATATYPE
EXECUTION FOR OUT & IN OUT MODES:
USING BIND VARIABLE:VARIABLE VARIABLENAME DATATYPE;
EXEC PROCEDURNAME('VALUE',:VARIABLENAME);
PRINT VARIABLENAME;
USING ANNONYMOS BLOCK:
BEGIN
PROCEDURENAME(ACTUAL PARAMETER,VARIABLENAME);
END;
AUTHID CURRENT_USER:
CREATE OR REPLACE PROCEDURE PROCEDURENAME(FORMAL PARAMETER AUTHID CURRENT_USER)
FUNCTIONS
=========
SYNTAX: CREATE OR REPLACE FUNCTION FUNCTIONNAME(FORMAL PARAMETER)
RETURN DATATYPE
IS/AS
VARIABLE DECLARATION,CURSOR,USER DEFINED EXCEPTIONS;
BEGIN
RETURN EXPRESSION;
EXCEPTION
END[FUNCTION NAME]
EXECUTION OF FUNCTION: SELECT FUNCTIONNAME(ACTUAL PARAMETER) FROM DUAL;
USING ANNOYMOUS BLOCK:BEGIN
VARIABLENAME:=FUNCTIONNAME(ACTUAL PARAMETER);
END;
TRIGGERS
========
1.STATEMENT LEVEL TRIGGER:
CREATE OR REPLACE TRIGGER TRIGGERNAME
BEFORE/AFTER INSERT/UPDATE/DELETE ON TABLENAME
DECLARE
VARIABLE DECLARATION,CURSOR,USER DEFINED EXCEPTIONS;
BEGIN
EXCEPTION
END;
2.ROW LEVEL TRIGGGER:
CREATE OR REPLACE TRIGGER TRIGGERNAME
BEFORE/AFTER INSERT/UPDATE/DELETE ON TABLENAME
FOR EACH ROW
DECLARE
VARIABLE DECLARATION,CURSOR,USER DEFINED EXCEPTIONS;
BEGIN
EXCEPTION
END;
COMPOUND TRIGGERS
=================
SYNTAX:
CREATE OR REPLACE TRIGGER TRIGGERNAME
FOR INSERT/UPDATE/DELETE ON TABLENAME
COMPOUND TRIGGER
GLOBAL VARIABLE DECLARATION;
BEFORE STATEMENT IS
BEGIN
END[BEFORE STATEMENT];
BEFORE EACH ROW IS
BEGIN
END[BEFORE EACH ROW];
AFTER EACH ROW IS
BEGIN
END[AFTER EACH ROW];
AFTER STATEMENT IS
BEGIN
END[AFTER STATEMENT];
END;
FOLLOWS CLAUSE:
CREATE OR REPLACE TRIGGER TRIGGERNAME
BEFORE/AFTER INSERT/DELETE ON TABLENAME
[FOR EACH ROW]
[WHEN CONDITION]
[FOLLOWS ANOTHER TRIGGERNAME]
[DECLARE]
BEGIN
[EXCEPTION]
END;
MUTATING ERROR IN TRIGGERS:
CREATE OR REPLACE TRIGGER TRIGGERNAME
BEFORE/AFTER INSERT/UPDATE/DELETE ON TABLENAME
FOR EACH ROW
DECLARE
VARIABLE DECLARATION
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
COMMIT;
END;
CALLING A PROCEDURE IN TRIGGERS:
CREATE OR REPLACE TRIGGER TRIGGERNAME
BEFORE/AFTER INSERT/UPDATE/DELETE ON TABLENAME
CALL PROCEDURENAME
WHEN CONDITION USED IN TRIGGERS:
CREATE OR REPLACE TRIGGER TRIGGERNAME
BEFORE/AFTER INSERT/UPDATE/DELETE ON TABLENAME
FOR EACH ROW
WHEN(CONDITION)
BEGIN
END;
ENABLE/DISABLE A SINGLE TRIGGER:
ALTER TRIGGER TRIGGERNAME ENABLE/DISABLE;
ENABLE/DISABLE ON TRIGGERS IN A TABLE:
ALTER TABLE TABLENAME ENABLE/DISABLE ALL TRIGGERS;
SYSTEM TRIGGERS:
1.DATABASE LEVEL
2.SCHEMA LEVEL
SYNTAX:
CREATE OR REPLACE TRIGGER TRIGGERNAME
BEFORE/AFTER CREATE/ALTER/DROP/TRUNCATE/RENAME ON DATABASE/USERNAME.SCHEMA
DECLARE
BEGIN
END;
PACKAGES
========
1.PACKAGE SPECIFICATION:
CREATE OR REPLACE PACKAGE PACKAGENAME
IS/AS
GLOBAL VARIABLE DECLARATION,CONSTANT DECLARATIONS;
CURSOR DECLARATIONS;
TYPES DECLARATIONS;
PROCEDURE DECLARATIONS;
FUNCTIONS DECLARATIONS;
END;
2.PACKAGE BODY:
CREATE OR REPLACE PACKAGE BODY PACKAGENAME
IS/AS
PROCEDURE IMPLEMENTATION;
FUNCTION IMPLEMENTATION;
END;
CALLING PACKAGES SUB-PROGRAMES:
1.CALLING PROCEDURES:
SYNTAX:EXEC PACKAGENAME.PROCEDURENAME(ACTUAL PARAMETERS);
BEGIN
PACKAGENAME.PROCEDURENAME(ACTUAL PARAMETER);
END;
2.CALLING FUNCTIONS:
SYNTAX:SELECT PACKAGENAME.FUNCTIONANAME(ACTUAL PARAMETER) FROM DUAL;
BEGIN
VARIABLENAME:=PACKAGENAME.FUNCTIONNAME(ACTUAL PARAMETER);
END;
STATE OF THE GLOBAL VARIABLE IN PACKAGES:
SYNTAX:CREATE OR REPLACE PACKAGE PACKAGENAME
IS
VARIABLENAME:=VALUE;
PRAGMA SERIALLY_REUSABLE;
END;
SYNTAX:BEGIN
PACKAGENAME.VARIABLENAME=VALUE;
END;
SYNTAX:BEGIN
DBMS_OUTPUT.PUT_LINE(PACKAGENAME.VARIABLENAME);
END;
STATE OF THE CURSORS IN PACKAGES:
SYNTAX:CREATE OR REPLACE PACKAGE PACKAGENAME
IS
CURSOR CURSORNAME IS/AS SELECT * FROM TABLENAME;
PRAGMA SERIALLY_REUSABLE;
END;
SYNTAX:BEGIN
OPEN PACKAGENAME.CUSORNAME;
END;
TYPES USED IN PACKAGES
=================
1.PL/SQL RECORD:
SYNTAX:CREATE OR REPLACE PACKAGE PACKAGENAME
IS
TYPE TYPENAME IS RECORD(ATTRIBUTENAME1 DATATYPE(SIZE),ATTRIBUTENAME2 DATATYPE(SIZE),...);
END;
SYNTAX:CREATE OR REPLACE PACKAGE BODY PACKAGENAME
IS
VARIABLENAME TYPENAME;
END;
2.INDEX BY TABLE/PL/SQL TABLE/ASSOCIATION ARRAY:
SYNTAX: TYPE TYPENAME IS TABLE OF DATATYPE(SIZE)
INDEX BY BINARY_INTEGER;
VARIABLENAME TYPENAME;
3.NESTED TABLE:
SYNTAX: TYPE TYPENAME IS TABLE OF DATATYPE(SIZE)
INDEX BY BINARY_INTEGER;
VARIABLENAME TYPENAME:=TYPENAME();
4.VARRAY:
SYNTAX: TYPE TYPENAME IS VARRAY(MAX.SIZE) OF DATATYPE(SIZE);
VARIABLENAME TYPENAME:=TYPENAME();
BULK-BIND
=========
SYNTAX: FORALL INDEXVARIABLEBENAME IN COLLECTIONVARIABLENAME.FIRST..COLLECTIONVARIABLENAME.LAST
DML STATEMENT WHERE COLNAME=COLLECTIONVARIABLENAME(INDEXVARIABLENAME);
BULK-BIND USED IN SELECT ..INTO CLAUSE:
SYNTAX: SELECT * BULK COLLECT INTO VARIABLENAME WHERE CONDITION;
BULK-BIND USED IN CUSOR FETCH STATEMENT:
SYNTAX: FETCH CURSORNAME BULK COLLECT INTO VARIABLENAME LIMIT VALUE;
TO CALCULATING ELAPSED TIME IN PL/SQL BLOCK:
SYNTAX: VARIABLE:=DBMS_UTILITY.GET_TIME;
BULK COLLECT CLAUSE USED IN DML...RETURNING...INTO CLAUSE:
SYNTAX: UPDATE TABLENAME SET COLNAME=NEWVALUE WHERE COLNAME=OLDVALUE RETURNING COLNAME INTO VARIABLENAME;
SYNTAX: PRINT VARIABLENAME;
BULK DELETE: DELETE FROM TABLENAME WHERE COLNAME=VARIABLENAME(INDEXVARIABLENAME);
BULK INSERT: INSERT INTO TABLENAME VALUES(VARIABLENAME(INDEXVARIABLENAME));
BULK EXCEPTIONS: SQL%BULK_EXCEPTIONS(INDEXVARIABLENAME).ERROR_INDEX;
SQL%BULK_EXCEPTIONS(INDEXVARIABLENAME).ERROR_CODE;
HANDLING BULK EXCEPTIONS: FORALL INDEXVARIABLENAME IN COLLECTIONVARIABLENAME.FIRST..COLLECTIONVARIABLENAME.LAST SAVE EXCEPTIONS
DML STATEMENTS WHERE COLNAME=COLLECTIONVARIABLENAME(INDEXVARIABLENAME);
WHEN OTHERS CLAUSE THEN: VARIABLENAME:=SQL%BULK_EXCEPTIONS.COUNT;
DBMS_UTILITY PACKAGE
====================
1.COMMA_TO_TABLE:
DBMS_UTILITY.COMMA_TO_TABLE(STRINGNAME,BINARY_INTEGER VARIABLENAME,INDEX BY TABLE VARIABLENAME);
2.TABLE_TO_COMMA:
DBMS_UTILITY.TABLE_TO_COMMA(INDEX BY TABLE VARIABELENAME,BINARY_INTEGER VARIABLENAME,STRING VARIABLENAMENAME);
DECLARE:
VARIABLENAME DBMS_UTILITY.UNCL_ARRAY;
REF-CURSORS
===========
1.STRONG REF-CURSOR: TYPE TYPENAME IS REF CURSOR RETURN RECORDTYPE DATATYPE;
VARIABLENAME TYPENAME;
2.WEAK REF-CURSOR: TYPE TYPENAME IS REF CURSOR;
VARIABLENAME TYPENAME;
SYNTAX:BEGIN
OPEN REFCURSORVARIABLENAME FOR SELECT * FROM TABLENAME WHERE CONDITION;
END;
SYS_REFCURSOR: REFCURSORVARIABLENAME SYS_REFCURSOR;
DYNAMIC SQL
===========
SYNTAX: BEGIN
EXECUTE IMMEDIATE 'SQL STATEMENT';
END;
TO RETRIEVE DATA: BEGIN
EXECUTE IMMEDIATE 'SELECT * FROM TABELNAME'INTO VARIABLENAME;
END;
INTRODUCTION
============
=>BLOCK STRUCTURE:
DECLARE(OPTIONAL)
VARIABLE DECLARATION,CURSORS,USER DEFINED EXCEPTIONS;
BEGIN
->DML,TCL STATEMENTS;
->SELECT ...INTO CLAUSE;
->CONDITIONAL,CONTROL STATEMENTS;
EXCEPTION(OPTIONAL)
->HANDLING RUNTIME ERRORS;
END;(MANDATORY)
=>DECLARING A VARIABLE:
VARIABLENAME DATATYPE(SIZE);
=>STORING A VALUE INTO VARIABLE:
VARIABLENAME:=VALUE;
=>DISPLAY A MESSAGE/VARIABLE VALUE:
DBMS_OUTPUT.PUT_LINE('MESSAGE');
=>SELECT ...INTO CLAUSE:
SELECT COL1,COL2,...INTO VARIABLENAME FROM TABLENAME WHERE CONDITION;
=>VARIABLE ATTRIBUTES:
1.COLUMN LEVEL ATTRIBUTES(%TYPE):
V_COLNAME TABLENAME.COLNAME%TYPE;
2.ROW LEVEL ATTRIBUTES(%ROWTYPE):
VARIABLENAME(I) TABLENAME%ROWTYPE;
=>CONDITIONAL STATEMENTS:
1.IF:
IF CONDITION THEN STATEMENT;
END IF;
2.IF ELSE:
IF CONDITION THEN STATEMENT;
ELSE STATEMENT;
END IF;
3.ELSIF:
IF CONDITON1 THEN STATEMENT1;
ELSIF CONDITION2 THEN STATEMENT;
ELSIF CONDITION3 THEN STATEMENT;
.
.
ELSE STATEMENT;
END IF;
=>CASE STATEMENT:
CASE VARIBLENAME WHEN VALUE1 THEN STATEMENT1;
WHEN VALUE2 THEN STATEMENT2;
ELSE STATEMENT;
END CASE;
=>CASE CONDITIONAL STATEMENT:
CASE WHEN CONDITION1 THEN STATEMENT1;
WHEN CONDITION2 THEN STATEMENT2;
ELSE STATEMENT;
END CASE;
=>CONTROL STATEMENT:
1.SIMPLE LOOP:LOOP STATEMENT;
END LOOP;
2.WHILE LOOP:WHILE TRUECONDITION
LOOP STATEMENT;
END LOOP;
3.FOR LOOP:FOR INDEXVARIABLENAME IN LOWERBOUND..UPPERBOUND
LOOP STATEMENT;
END LOOP;
=>BIND VARIABLE/NON PL/SQL VARIABLENAME:
1.CREATING A BIND VARIABLENAME:
VARIABLE VARIABLENAME DATATYPE;
2.USING BIND VARIABLE:
:BINDVARIABLENAME:=EXPRESSION;
3.DISPLAY VALUE FROM BIND VARIABLE:
PRINT VARIABLENAME;
=>CURSOR:
1.IMPLICIT CURSOR
2.EXPLICIT CURSOR
2.EXPLICIT CURSOR:
EXPLICIT CURSOR LIFE CYCLE:
1.DECLARE:
CURSOR CUSORNAME IS SELECT * FROM TABLENAME WHERE CONDITION;
2.OPEN:
OPEN CURSORNAME;
3.FETCH:
FETCH CURSORNAME INTO VARIABLENAME1,VARIABLENAME2,...;
4.CLOSE:
CLOSE CURSORNAME;
=>EXPLICIT CURSOR ATTRIBUTES:
1.%NOTFOUND:
CUSORNAME%NOTFOUND;
2.%FOUND:
CURSORNAME%FOUND;
3.%ISOPEN:
CURSORNAME%ISOPEN;
4.%ROWCOUNT:
CUSORNAME%ROWCOUNT;
ELIMINATE EXPLICIT CURSOR LIFE CYCLE:
FOR INDEXVARIABLENAME IN CURSORNAME
LOOP STATEMENT;
END LOOP;
PARAMETERIESED CURSORS:
CURSOR CURSORNAME(P_COLNAME DATATYPE) IS SELECT * FROM TABLENAME WHERE CONDITION;
EXCEPTIONS
==========
PRE DEFINED EXCEPTIONS:
1.NO_DATA_FOUND:
WHEN NO_DATA_FOUND THEN STATEMENT;
2.TOO_MANY_ROWS:
WHEN TOO_MANY_ROWS THEN STATEMENT;
3.ZERO_DIVIDE:
WHEN ZERO_DIVIDE THEN STATEMENT;
4.CURSOR_ALREADY_OPEN:
WHEN CURSOR_ALREADY_OPEN THEN STATEMENT;
5.INVALID_CURSOR:
WHEN INVALID_CURSOR THEN STATEMENT;
6.DUP_VAL_ON_INDEX:
WHEN DUP_VAL_ON_INDEX THEN STATEMENT;
7.INVALID_NUMBER:
WHEN INVALID_NUMBER THEN STATEMENT;
8.VALUE_ERROR:
WHEN VALUE_ERROR THEN STATEMENT;
USER DEFINED EXCEPTIONS:
1.DECLARE:
USERDEFINEDEXCEPTIONNAME EXCEPTION;
2.RAISE:
RAISE USERDEFINEDEXCEPTIONNAME;
3.HANDLING EXCEPTION:
WHEN USERDEFINEDEXCEPTIONNAME THEN STATEMENT;
UN-NAMED EXCEPTION:
SYNTAX:PRAGMA EXCEPTION_INIT(USER DEFINED EXCEPTIONNAME,ERROR NUMBER);
RAISE_APPLICATION_ERROR:
SYNTAX:RAISE_APPLICATION_ERROR(ERROR NUMBER,'MESSAGE');
ERROR TRAPPING FUNCTIONS:
1.SQLCODE:DBMS_OUTPUT.PUT_LINE(SQLCODE);
2.SQLERRM:DBMS_OUTPUT.PUT_LINE(SQLERRM);
PROCEDURES
==========
SYNTAX:CREATE OR REPLACE PROCEDURE PROCEDURENAME(FORMAL PARAMETER);
IS/AS
VARIABLE DECLARATION,CURSOR,USER DEFINED EXCEPTIONS;
BEGIN
EXCEPTION
END;
FORMAL PARAMETER:P_COLNAME DATATYPE
EXECUTION OF A PROCEDURE:EXEC PROCEDURENAME(ACTUAL PARAMETER);
BEGIN
PROCEDURENAME(ACTUAL PARAMETER);
END;
CALL PROCEDURENAME(ACTUAL PARAMETER);
MODE:
1.IN MODE:P_COLNAME IN DATATYPE
2.OUT MODE:P_COLNAME OUT DATATYPE
3.IN OUT MODE:P_COLNAME IN OUT DATATYPE
EXECUTION FOR OUT & IN OUT MODES:
USING BIND VARIABLE:VARIABLE VARIABLENAME DATATYPE;
EXEC PROCEDURNAME('VALUE',:VARIABLENAME);
PRINT VARIABLENAME;
USING ANNONYMOS BLOCK:
BEGIN
PROCEDURENAME(ACTUAL PARAMETER,VARIABLENAME);
END;
AUTHID CURRENT_USER:
CREATE OR REPLACE PROCEDURE PROCEDURENAME(FORMAL PARAMETER AUTHID CURRENT_USER)
FUNCTIONS
=========
SYNTAX: CREATE OR REPLACE FUNCTION FUNCTIONNAME(FORMAL PARAMETER)
RETURN DATATYPE
IS/AS
VARIABLE DECLARATION,CURSOR,USER DEFINED EXCEPTIONS;
BEGIN
RETURN EXPRESSION;
EXCEPTION
END[FUNCTION NAME]
EXECUTION OF FUNCTION: SELECT FUNCTIONNAME(ACTUAL PARAMETER) FROM DUAL;
USING ANNOYMOUS BLOCK:BEGIN
VARIABLENAME:=FUNCTIONNAME(ACTUAL PARAMETER);
END;
TRIGGERS
========
1.STATEMENT LEVEL TRIGGER:
CREATE OR REPLACE TRIGGER TRIGGERNAME
BEFORE/AFTER INSERT/UPDATE/DELETE ON TABLENAME
DECLARE
VARIABLE DECLARATION,CURSOR,USER DEFINED EXCEPTIONS;
BEGIN
EXCEPTION
END;
2.ROW LEVEL TRIGGGER:
CREATE OR REPLACE TRIGGER TRIGGERNAME
BEFORE/AFTER INSERT/UPDATE/DELETE ON TABLENAME
FOR EACH ROW
DECLARE
VARIABLE DECLARATION,CURSOR,USER DEFINED EXCEPTIONS;
BEGIN
EXCEPTION
END;
COMPOUND TRIGGERS
=================
SYNTAX:
CREATE OR REPLACE TRIGGER TRIGGERNAME
FOR INSERT/UPDATE/DELETE ON TABLENAME
COMPOUND TRIGGER
GLOBAL VARIABLE DECLARATION;
BEFORE STATEMENT IS
BEGIN
END[BEFORE STATEMENT];
BEFORE EACH ROW IS
BEGIN
END[BEFORE EACH ROW];
AFTER EACH ROW IS
BEGIN
END[AFTER EACH ROW];
AFTER STATEMENT IS
BEGIN
END[AFTER STATEMENT];
END;
FOLLOWS CLAUSE:
CREATE OR REPLACE TRIGGER TRIGGERNAME
BEFORE/AFTER INSERT/DELETE ON TABLENAME
[FOR EACH ROW]
[WHEN CONDITION]
[FOLLOWS ANOTHER TRIGGERNAME]
[DECLARE]
BEGIN
[EXCEPTION]
END;
MUTATING ERROR IN TRIGGERS:
CREATE OR REPLACE TRIGGER TRIGGERNAME
BEFORE/AFTER INSERT/UPDATE/DELETE ON TABLENAME
FOR EACH ROW
DECLARE
VARIABLE DECLARATION
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
COMMIT;
END;
CALLING A PROCEDURE IN TRIGGERS:
CREATE OR REPLACE TRIGGER TRIGGERNAME
BEFORE/AFTER INSERT/UPDATE/DELETE ON TABLENAME
CALL PROCEDURENAME
WHEN CONDITION USED IN TRIGGERS:
CREATE OR REPLACE TRIGGER TRIGGERNAME
BEFORE/AFTER INSERT/UPDATE/DELETE ON TABLENAME
FOR EACH ROW
WHEN(CONDITION)
BEGIN
END;
ENABLE/DISABLE A SINGLE TRIGGER:
ALTER TRIGGER TRIGGERNAME ENABLE/DISABLE;
ENABLE/DISABLE ON TRIGGERS IN A TABLE:
ALTER TABLE TABLENAME ENABLE/DISABLE ALL TRIGGERS;
SYSTEM TRIGGERS:
1.DATABASE LEVEL
2.SCHEMA LEVEL
SYNTAX:
CREATE OR REPLACE TRIGGER TRIGGERNAME
BEFORE/AFTER CREATE/ALTER/DROP/TRUNCATE/RENAME ON DATABASE/USERNAME.SCHEMA
DECLARE
BEGIN
END;
PACKAGES
========
1.PACKAGE SPECIFICATION:
CREATE OR REPLACE PACKAGE PACKAGENAME
IS/AS
GLOBAL VARIABLE DECLARATION,CONSTANT DECLARATIONS;
CURSOR DECLARATIONS;
TYPES DECLARATIONS;
PROCEDURE DECLARATIONS;
FUNCTIONS DECLARATIONS;
END;
2.PACKAGE BODY:
CREATE OR REPLACE PACKAGE BODY PACKAGENAME
IS/AS
PROCEDURE IMPLEMENTATION;
FUNCTION IMPLEMENTATION;
END;
CALLING PACKAGES SUB-PROGRAMES:
1.CALLING PROCEDURES:
SYNTAX:EXEC PACKAGENAME.PROCEDURENAME(ACTUAL PARAMETERS);
BEGIN
PACKAGENAME.PROCEDURENAME(ACTUAL PARAMETER);
END;
2.CALLING FUNCTIONS:
SYNTAX:SELECT PACKAGENAME.FUNCTIONANAME(ACTUAL PARAMETER) FROM DUAL;
BEGIN
VARIABLENAME:=PACKAGENAME.FUNCTIONNAME(ACTUAL PARAMETER);
END;
STATE OF THE GLOBAL VARIABLE IN PACKAGES:
SYNTAX:CREATE OR REPLACE PACKAGE PACKAGENAME
IS
VARIABLENAME:=VALUE;
PRAGMA SERIALLY_REUSABLE;
END;
SYNTAX:BEGIN
PACKAGENAME.VARIABLENAME=VALUE;
END;
SYNTAX:BEGIN
DBMS_OUTPUT.PUT_LINE(PACKAGENAME.VARIABLENAME);
END;
STATE OF THE CURSORS IN PACKAGES:
SYNTAX:CREATE OR REPLACE PACKAGE PACKAGENAME
IS
CURSOR CURSORNAME IS/AS SELECT * FROM TABLENAME;
PRAGMA SERIALLY_REUSABLE;
END;
SYNTAX:BEGIN
OPEN PACKAGENAME.CUSORNAME;
END;
TYPES USED IN PACKAGES
=================
1.PL/SQL RECORD:
SYNTAX:CREATE OR REPLACE PACKAGE PACKAGENAME
IS
TYPE TYPENAME IS RECORD(ATTRIBUTENAME1 DATATYPE(SIZE),ATTRIBUTENAME2 DATATYPE(SIZE),...);
END;
SYNTAX:CREATE OR REPLACE PACKAGE BODY PACKAGENAME
IS
VARIABLENAME TYPENAME;
END;
2.INDEX BY TABLE/PL/SQL TABLE/ASSOCIATION ARRAY:
SYNTAX: TYPE TYPENAME IS TABLE OF DATATYPE(SIZE)
INDEX BY BINARY_INTEGER;
VARIABLENAME TYPENAME;
3.NESTED TABLE:
SYNTAX: TYPE TYPENAME IS TABLE OF DATATYPE(SIZE)
INDEX BY BINARY_INTEGER;
VARIABLENAME TYPENAME:=TYPENAME();
4.VARRAY:
SYNTAX: TYPE TYPENAME IS VARRAY(MAX.SIZE) OF DATATYPE(SIZE);
VARIABLENAME TYPENAME:=TYPENAME();
BULK-BIND
=========
SYNTAX: FORALL INDEXVARIABLEBENAME IN COLLECTIONVARIABLENAME.FIRST..COLLECTIONVARIABLENAME.LAST
DML STATEMENT WHERE COLNAME=COLLECTIONVARIABLENAME(INDEXVARIABLENAME);
BULK-BIND USED IN SELECT ..INTO CLAUSE:
SYNTAX: SELECT * BULK COLLECT INTO VARIABLENAME WHERE CONDITION;
BULK-BIND USED IN CUSOR FETCH STATEMENT:
SYNTAX: FETCH CURSORNAME BULK COLLECT INTO VARIABLENAME LIMIT VALUE;
TO CALCULATING ELAPSED TIME IN PL/SQL BLOCK:
SYNTAX: VARIABLE:=DBMS_UTILITY.GET_TIME;
BULK COLLECT CLAUSE USED IN DML...RETURNING...INTO CLAUSE:
SYNTAX: UPDATE TABLENAME SET COLNAME=NEWVALUE WHERE COLNAME=OLDVALUE RETURNING COLNAME INTO VARIABLENAME;
SYNTAX: PRINT VARIABLENAME;
BULK DELETE: DELETE FROM TABLENAME WHERE COLNAME=VARIABLENAME(INDEXVARIABLENAME);
BULK INSERT: INSERT INTO TABLENAME VALUES(VARIABLENAME(INDEXVARIABLENAME));
BULK EXCEPTIONS: SQL%BULK_EXCEPTIONS(INDEXVARIABLENAME).ERROR_INDEX;
SQL%BULK_EXCEPTIONS(INDEXVARIABLENAME).ERROR_CODE;
HANDLING BULK EXCEPTIONS: FORALL INDEXVARIABLENAME IN COLLECTIONVARIABLENAME.FIRST..COLLECTIONVARIABLENAME.LAST SAVE EXCEPTIONS
DML STATEMENTS WHERE COLNAME=COLLECTIONVARIABLENAME(INDEXVARIABLENAME);
WHEN OTHERS CLAUSE THEN: VARIABLENAME:=SQL%BULK_EXCEPTIONS.COUNT;
DBMS_UTILITY PACKAGE
====================
1.COMMA_TO_TABLE:
DBMS_UTILITY.COMMA_TO_TABLE(STRINGNAME,BINARY_INTEGER VARIABLENAME,INDEX BY TABLE VARIABLENAME);
2.TABLE_TO_COMMA:
DBMS_UTILITY.TABLE_TO_COMMA(INDEX BY TABLE VARIABELENAME,BINARY_INTEGER VARIABLENAME,STRING VARIABLENAMENAME);
DECLARE:
VARIABLENAME DBMS_UTILITY.UNCL_ARRAY;
REF-CURSORS
===========
1.STRONG REF-CURSOR: TYPE TYPENAME IS REF CURSOR RETURN RECORDTYPE DATATYPE;
VARIABLENAME TYPENAME;
2.WEAK REF-CURSOR: TYPE TYPENAME IS REF CURSOR;
VARIABLENAME TYPENAME;
SYNTAX:BEGIN
OPEN REFCURSORVARIABLENAME FOR SELECT * FROM TABLENAME WHERE CONDITION;
END;
SYS_REFCURSOR: REFCURSORVARIABLENAME SYS_REFCURSOR;
DYNAMIC SQL
===========
SYNTAX: BEGIN
EXECUTE IMMEDIATE 'SQL STATEMENT';
END;
TO RETRIEVE DATA: BEGIN
EXECUTE IMMEDIATE 'SELECT * FROM TABELNAME'INTO VARIABLENAME;
END;
