DKGT Technologies : PLSQL course syllabus

1)      Basics of PL/SQL

·         Basics of SQL

·         PL/SQL Introduction

·         Advantages of PL/SQL

·         PL/SQL Architecture

·         PL/SQL Block- Anonymous block, Named block

·         How to use DDL and DML statements in PL/SQL block

·         Introduce Oracle development tools- Toad, SQL Developer, isqlplus window

·         Dynamic SQL

·         PL/SQL literals

·         PL/SQL output statements

2)      SQL Statements in PL/SQL Block

·         Data retrievals – SELECT

·         Data Manipulation Language (DML)– INSERT,UPDATE,DELETE,MERGE

·         Data Definition Language (DDL) – CREATE,ALTER,DROP,RENAME,TRUNCATE

·         Transaction Control Language (TCL) – COMMIT,ROLLBACK,SAVEPOINT

·         Data Control Language (DCL)– GRANT,REVOKE

3)      PL/SQL Data Types

      Scalar Data Type:

·         Number Data Type – NUMBER

·         Character Data Types – CHAR,VARCHAR2,CLOB,LONG

·         Time and Date Data Types –DATE,TIMESTAMP

                    Composite Data Type:

·         PL/SQL table

·         PL/SQL record

                      Reference Data Type

·         %TYPE

·         %ROWTYPE

 

4)      PL/SQL Variables

·         PL/SQL run time variables

·          Bind variables

·         Local & Global variables

5)      PL/SQL comments

6)      Constraints in PL/SQL

·         PRIMARY KEY

·         UNIQUE

·         NOT NULL

·         CHECK

·         FOREIGN KEY

7)      PL/SQL Cursors

·         What is cursor

·         Types of Cursor

·         Implicit Cursor

·         Explicit Cursor

·         Cursor Attributes - %FOUND,%NOTFOUND,%ROWCOUNT,%ISOPEN

·         Parameterized Cursor

·         Inline Cursor

·         Cursor variables – REF CURSOR,SYS_REFCURSOR

9)      PL/SQL Control Statements

·         If

·         Multi if

·         Loop

·         For loop

·         For loop with REVERSE

·         While loop

·         Case

·         Exit

10)   PL/SQL Exceptions

·         Exception concept and Exception advantage

·         Pre defined Exceptions – zero_divide,   no_data_found, too_many_rows, dup_val_on_index, others

·         Non Pre defined Exception – Pragma Exception_init

·         User defined exception – Raise, Raise_application_Error

·         SQLCODE

·         SQLERRM

11)   Collections

·         PL/SQL Tables (or) Associative array (or) Index by Table

·         Nested Table

·         Varray

·         Collection Methods – EXISTS,COUNT,EXTEND,DELETE,FIRST,LAST, PRIOR,NEXT

12)   FORALL LOOP

13)   PL/SQL RECORDS

14)   Named  PL/SQL Blocks

·         Procedures

·         Functions

·         Triggers

·         Sub programs with parameters

·         Difference between procedures and functions and Triggers

15)   PROCEDURES

·         What is Procedure

·         Advantages of Procedure

·         Procedure without Arguments

·         Procedures With Arguments

·         Procedures with Exception

·         Procedure within another procedure

·         Procedure within another function

·         Call procedure from another procedure

·         Execute procedure from sqlplus window

16)   FUNCTIONS

·         What is Function

·         Advantages of Function

·         Functions without Arguments

·         Functions With Arguments

·         Functions with Exception

·         Function within another Function

·         Function within another procedure

·         Call Function  from another Function

·         Execute Function from sqlplus window

17)   TRIGGERS

·         What is Trigger

·         Advantages of Trigger

·         Statement Level Trigger

·         Row Level Trigger

·         Before Trigger

·         After Trigger

·         Instead of Trigger

·         Compound Trigger

·         Trigger Event- INSERT,UPDATE,DELETE

·         Mutating Table Error

18)   PRAGMA AUTONOMOUS_TRANSACTION

19)   PACKAGES

·         What is Packages

·         Package parts- Specification,Body

·         Advantages of packages

·         Package with procedures example

·         Package with functions example

20)   Bulk Collect

23)   Oracle Supplied Packages

·         DBMS_JOB

24)   SQL Loader

25)   SET SERVEROUTPUT ON

26)   Save Exception

27)   General discussion about PLSQL job openings, interview questions, Oracle growth etc…