PL/SQL

PL/SQL is Oracle Corporation's procedural extension for SQL and the Oracle relational database.

---

1. PL/SQL Introduction

---

• SELECT..INTO clause
• Data Type
• Blocks
• Constant
• Attribute to Declare Variables(%TYPE,%ROWTYPE)
• Conditional, Control Statements
• Comments
• Bind Variables

---

2. PL/SQL CURSOR

---

• Implicit Cursor
• Explicit Cursor
• Explicit Cursor Attributes
• Cursor For Loop
• Parameterized Cursor
• Implicit Cursor Attributes
• update, delete statements are used in cursor(without using for update, where current of clauses)

---

3. PL/SQL EXCEPTIONS

---

• Details Of Exceptions
• Predefined Exception
• Non Predefined Exception
• User Defined Exception
• Error Trapping Function(SQLCODE,SQLERRM)
• RAISE_APPLICATION_ERROR

---

4. PL/SQL PROCEDURES

---

• Parameter modes(IN,OUT,INOUT)
• NOCOPY
• Autonomous transactions
• Authid current_user

---

5. PL/SQL FUNCTION

---

• DML statements used in function
• select ..into clause used in function
• Cursor used in function
• wm_concat()

---

6. PL/SQL TRIGGERS

---

• Trigger Details
• DML Trigger(before,after,instead of)
• DDL Trigger
• Compound Trigger(11g)
• System Event Trigger
• Logon Trigger
• Trigger Execution Order
• Row Level & Statement Level Trigger & Application
• Old and New qualifiers
• FOLLOWS clause in trigger(11g)

---

7. PL/SQL PACKAGES

---

• State of global variable
• Overloading process
• Forward declarations

---

8. PL/SQL TYPES USED IN PACKAGE

---

• PL/SQL Record
• Collections
• Index by table
• Nested Table
• Varray

---

9. PL/SQL BULK BIND

---

• BULK COLLECT Clause
• INDICES OF Clause
• FORALL Statement
• sql%bulk_rowcount
• BULK exceptions
• Save exceptions
• sql%bulk_exceptions
• DML errors

---

10. PL/SQL REF CURSOR (OR) CURSOR variables

---

• Strong refcursor
• weak refcursor
• sys_refcorsor
• passing refcursor to the sub programs

---

11. PL/SQL REF CURSOR USED IN LOCAL SUBPROGRAMS

---

• Local procedure
• Local function

---

12. PL/SQL UTL_FILE PACKAGES

---

• UTL_FILE Packages

---

13. PL/SQL SQL*LOADER

---

• SQL*Loader

---

14. PL/SQL LOB(Large Objects)

---

• Large Objects

---

15. PL/SQL CURSOR LOCKING MECHANISM

---

• Cursor Locking

---

16. PL/SQL AVOIDING MUTATING ERROR 

---

• Mutating Error

---

17. PL/SQL DYNAMIC SQL

---

• Dynamic SQL