JAWAHARLAL NEHRU TECHNOLOGICAL UNIVERSITY HYDERABAD

Course Objectives:

• 1. Knowledge on significance of SQL fundamentals.
• 2. Evaluate functions and triggers of PL/SQL
• 3. Knowledge on control structures, packages in PL/SQL and its applications

Course Outcomes:

• 1. Understand importance of PL/SQL basics
• 2. Implement functions and procedures using PL/SQL
• 3. Understand the importance of triggers in database

UNIT - I

PL/SQL Basics: Block Structure, Behavior of Variables in Blocks, Basic Scalar and Composite Data Types, Control Structures, Exceptions, Bulk Operations, Functions, Procedures, and Packages, Transaction Scope.

UNIT - II

Language Fundamentals & Control Structures: Lexical Units, Variables and Data Types, Conditional Statements, Iterative Statements, Cursor Structures, Bulk Statements, Introduction to Collections, Object Types: Varray and Table Collections, Associative Arrays, Oracle Collection API.

UNIT - III

Functions and Procedures: Function and Procedure Architecture, Transaction Scope, Calling Subroutines, Positional Notation, Named Notation, Mixed Notation, Exclusionary Notation, SQL Call Notation, Functions, Function Model Choices, Creation Options, Pass-by-Value Functions, Pass-by-

Reference Functions, Procedures, Pass-by-Value Procedures, Pass-by-Reference Procedures,

Supporting Scripts.

UNIT - IV

Packages: Package Architecture, Package Specification, Prototype Features, Serially Reusable Precompiler Directive, Variables, Types, Components: Functions and Procedures, Package Body, Prototype Features, Variables, Types, Components: Functions and Procedures, Definer vs. Invoker Rights Mechanics, Managing Packages in the Database Catalog, Finding, Validating, and Describing Packages, Checking Dependencies, Comparing Validation Methods: Timestamp vs. Signature.

UNIT - V

Triggers: Introduction to Triggers, Database Trigger Architecture, Data Definition Language Triggers, Event Attribute Functions, Building DDL Triggers, Data Manipulation Language Triggers, Statement- Level Triggers, Row-Level Triggers, Compound Triggers, INSTEAD OF Triggers, System and Database Event Triggers, Trigger Restrictions, Maximum Trigger Size, SQL Statements, LONG and LONG RAW Data Types.

TEXT BOOKS:

• 1. Oracle Database 12c PL/SQL Programming Michael McLaughlin, McGrawHill Education

REFERENCES:

• 1. Benjamin Rosenzweig, Elena Silvestrova Rakhimov, Oracle PL/SQL by example Fifth Edition
• 2. Dr. P. S. Deshpande, SQL & PL / SQL for Oracle 11g Black Book