PL/SQL -- Table of contents

Introduction: PL/SQL Introduction

Note on the important definitions to remember

L command - Lists SQL or PL/SQL from the SQL buffer; I command - Inserts SQL or PL/SQL lines; Del command - Deletes SQL or PL/SQL lines; C command - Changes SQL or PL/SQL lines; SAVE command; GET command

Open Notepad editor; Save the Oracle Buffer; Edit the file; Work with the file

Developing a PL/SQL block; Types of block; Anonymous or unnamed block; Components of a PL/SQL block: Declaration; Body or Execution; Exception; Implicit cursor; Explicit cursor; Parameters in cursor

Implicit vs. explicit cursors; Declaring explicit cursors; Using explicit cursor; Open Cursor; Fetch Cursor; Close Cursor; Declare variable; Using simple loop; Exit statement

Using Explicit Cursor Attributes; Conditional Statements and Process Flow - IF ... ELSE ... END IF; FOR LOOP statement; LOOP statements

Create table; INSERT statement in PL/SQL block; FOR LOOP statement; COMMIT statement in PL/SQL block; Using PL/SQL to access Oracle

How to use parameters in explicit cursor; Declaring Parameters using Explicit cursors; Transaction Processing passing Parameters

Hands-On 08: EXECPTION

Type of Exceptions; Identifying Common Exceptions; Coding the Exception Handler; Pre-defined exception; User-defined exception

Note on the important definitions to remember

Create a table named cust_stat; Write a stored procedure using notepad to populate customers _statistics

Write a procedure to add a record using Procedure Builder; Use input and output parameters

Write a PL/SQL procedure to remove a record using Procedure Builder; Use input and output parameters

Write a PL/SQL function to concatenate the customer's last name and first name separated by comma; Use input parameters

Write a PL/SQL function to return a department name; Use department number as an input parameter; Run the stored procedure; Save the function in the local library; Save the function in the database server

Debugging a PL/SQL using Procedure Builder; Using Breakpoint; Using Stack; Compiling and running debug procedure; Test debug procedure; Introducing TEXT_IO package; PL/SQL Library

Create; copy; alter; drop a table; Create; alter; and drop a view; Create; alter and drop a user; Create a private synonym; Create a public synonym; Grant object and system privileges; Revoke object and system privileges

Directory Views; Security; Tracking Dependency

Create a Package Specification; Create a Package Body; Copy and paste a stored program units' header to a Package Specification; Copy and paste a stored program units to a Package Body; Compile the Package Specification; Compile the Package Body; Store packages locally or on database server

Creating Triggers; Using Triggers; Dropping Triggers