Whiteboard from July 2nd 2012.

Introduction PL/SQL Programming - 

Sukhjit Singh -

plsql.sukhjit.tv

Orientation - July 2nd 2012

>>About the course

About Language and Setting up your environment

Starting your labs.

..Cover the underlying topic concepts.

This orientation is being recorded and running in lecture mode (for audio).

If you have questions kindly type those in chat room.

Notes

Orientation Notes::

PL/SQL - Procedural Language 

SQL is an interpreted language -- 

Client and Server

..10 statements as a program block --> Put it in the server  (Pre-compiled)

From Client we simply execute it. 

Reduced the traffic between Client and Server

Improve performance.

Application Platform - SAP, PeopleSoft, JDEdward, Siebel...

What will you learn after this course?

PL/SQL Syn

  procedures, functions, triggers, pacakges, cursors

manage these objects

Working with Large Objects - 

Course Blocks

Lang Basics

Declaring Variables

Writing simple statement and executing them

Control Blocks (decision-making and looping)

Data Structures

Collection

Managing Transactions

Cursors

Exception Handling

Packages --> Procedure and Functions

Triggers

Large Objects

18 small units.

..Review the powerpoints and work on exercises.

Learning by Doing.

 

Accounts - 

Unix acct --> 

Oracle acct --> 

Setup files -- are on the website.

Working with Oracle Environment:

Unix acct --> Login --> Start sqlplus

sqlplus - Oracle Id/pwd. (which instructor will provide).

To setup your environment - look up the setup file on the website.

Overview of PL/SQL.

Oracle 4GL - Transact-SQL (MS SQL Server)

Procedural SQL - used for Data Manipulation of record set (through usage of Procedures or Functions).

PL/SQL Block --> Compiled to p-code --> Saved in the data dictionary.

Client access --> Call the procedure --> Loads the p-code 

Procedural statement are executed by PL/SQL engine

SQL Statements (that are part of Procedures) are executed by SQL Engine

Benefits

Performance

Reduced I/O between Client and Server

Code Manageability --> Blocks.

Structured Programming --Modularize your code.

Portability -- code xfer from one Oracle system to another.

Exception Mgmt - Problem recovery

Declaring Variables

Anonymous Block

DECLARE

name CONSTANT varchar2(20) := 'Andrew';

SSN varchar(11) NOT NULL := DEFAULT '111-11-1111';

BEGIN

EXCEPTION

END;

Learn all the datatypes

Scalar

binary integer

pls integer

long raw

date --> 

Timestamp 

%TYPE

name emp.name%TYPE;

Boolean type 

True, false and null

Composite datatype 

RECORD and VARRAYS

Temp variable to store values from the db records to manipulate

Rich Media - Large Objects

text and binary.

Bind Variables - 

Declare varibles in SQL/Plus

Variable a DATE;

PRINT a;

Chapter 1

Chapter 2 - BEGIN and END Block.

PL/SQL Statement

Operators used  in expressions. (pg 2-18)

Interact with variables.(30+ operators)

+, -, /, MOD, *, || etc.

Scope Mgmt -  Nested Blocks - values from inner blocks cannot be seen by outer.  Outer block variable values are accessible by inner blocks.

SQL Functions from chapter 4.

TO_CHAR, TO_DATE.. Datatype conversion functions.

Two exercises - Chapter 1 and 2.

Send two text files (including test runs) --> deanzalabs@yahoo.com

Start watching lecture for chapter 3 and 4.

No review session on 7/4 - 

Meet on July 9th 2012.

Weekly review sessions on Mon and Wed (8:15pm to 10:15pm)

Email all registered students with conf. information.