PL/SQL by Example

PL/SQL by Example
PL/SQL by Example
English | MP4 | AVC 1364×768 | AAC 44KHz 2ch | 5.5 Hours | 0.98 MB

Learn how to combine the efficiency of SQL and Procedural Language(PL) to build a powerful Application.

Learn to become a PL/SQL developer in just four weeks.

This fast, easy and effective course will take you from zero PL/SQL writing skills to being able to write efficient programs to process data.

PL/SQL is the Oracle Procedural Language extension of SQL. A PL/SQL program can have both SQL statements and procedural statements. In the PL/SQL program, the SQL statements are used to access sets of data stored in a database, while the procedural statements are used to process individual piece of data and control the program flow.

In this course participants will learn

  • Describe the features and syntax of PL/SQL
  • Design PL/SQL anonymous blocks that execute efficiently
  • Use the Oracle supplied PL/SQL packages to generate screen output
  • Create and debug stored procedures and functions
  • Design PL/SQL packages to group related constructs
  • Use PL/SQL programming constructs and conditionally control code flow
  • Create overloaded package subprograms for more flexibility
  • Handle runtime errors using Exceptions
  • Writing manageable code using Records
  • Storing and processing data using Collections
  • Processing data using Implicit and Explicit Cursors
Table of Contents

1 Welcome…
2 How to make best use of the Program…

Software Installation
3 Installing Oracle
4 Installing Java SDK
5 Installing SQL Developer
6 Running scripts necessary for the course
7 Scripts for our Lab Exercises

PLSQL Basics
8 What is PLSQL
9 PLSQL Advantages
10 PLSQL Structure
11 First Example
12 Declaring Variables
13 Comments in PLSQL
14 Scope of Variables
15 IF then ELSE statement
16 CASE Statemet
17 WHILE Loop
18 FOR Loop
19 Exercise 1

Processing Data via PLSQL
20 Reading data from database
21 What is TYPE
22 Inserting data into database
23 Exercise 2

PLSQL Blocks
24 Anonymous Blocks
25 What are Procedures
26 Creating a Procedure
27 Calling a Procedure
28 Procedure with OUT Mode
29 Procedure with IN OUT Mode
30 What are Functions
31 Calling a Function
32 Exercise 3

33 What are Exceptions
34 Exceptions Example
35 User Defined Exceptions
36 System Defined Exceptions list
37 Exercise 4

38 What are Packages
39 Package Specification
40 Package Body
41 Executing sub programs present in Packages
42 Exercise 5

Working with Records
43 What are Records
44 Records Example
45 Working with Record data
46 Passing Records as parameters
47 Inserting data using Records
48 Updating data using Records
49 User defined Record Types
50 User defined Record Example
51 Exercise 6

52 What are Cursors
53 Implicit Cursor
54 Explicit Cursor
55 Retrieving 1 row using Explicit Cursor
56 Retrieving more than 1 row using Explicit Cursor
57 Using Records in Cursors
58 Cursor FOR Loop
59 Cursor Variable and Reference Cursor
60 Exceptions for Cursors
61 Exercise 7

62 What are Collections
63 Collection Terminology
64 Associative Arrays
65 Nested Tables
66 What are VARRAYS
67 Collection Methods
68 MULTISET Operators
69 Collections Summary
70 Exercise 8

71 Scripts necessary to practise Triggers
72 Triggers Introduction
73 Trigger Types
74 Statement Level Trigger
75 Statement Level Trigger with multiple Actions
76 Row Level Trigger
77 OLD and NEW Pseudo Records with an Example
78 Restricting the Trigger based on a Condition using the WHEN clause
79 Restricting the Trigger at a column level using the OF clause
80 EnableDisableDrop a Trigger

Advanced Topics – Bulk Processing
81 Context Switch
82 Bulk Processing Introduction
83 Bulk Processing Example
84 Bulk Processing with LIMIT option
85 Bulk Processing with ROWTYPE
86 Handling Bulk Processing Exceptions

Advanced Topics – Dynamic SQL
87 Dynamic SQL Introduction
88 Dynamic SQL Example
89 Bind variables and Parsing
90 Dynamic SQL with Bind variables
91 Dynamic SQL with Cursors
92 Dynamic SQL with Bulk Collect
93 DBMS_SQL package

Advanced Topics – Table Functions
94 Object Creation
95 Table Functions
96 Pipelined Table Functions

97 Where to go from here…