Announcements

Sample Midterm

posted Jul 23, 2012, 10:24 AM by Sukhjit Singh


Whiteboard - July 9th 2012 (Unedited)

posted Jul 9, 2012, 9:23 PM by Sukhjit Singh

Notes
Day 2 - Intro to PL/SQL @ De Anza College.

Question - Where do I find the practice exercises?

Review >>
Introduction to PL/SQL Environment
Benefits of PL/SQL
Writing procedures and functions.

Declaring Variables
x INTEGER := 55;

NOT NULL 
DEFAULT VALUE

DATATYPES
SCALAR
COMPOSITE
REFERENCE
LOB

BIND VARIABLES <-- Declared in PL/SQL Environment.

Reviewed video lecture:
Difference between Binary_integer and PLS_Integer
What is the difference between Long RAW, LONG and VARCHAR2?
Different types of Timestamp? <-- Illustrate the usage of timestamp datatypes using an example.
usage of %TYPE keyword.


How to create a script?
Use notepad or your favorite text editor -- create your script and save it.
Run it --> Simply copy and paste in your telnet session.

Writing Executable Statements:

DECLARE
--DECLARE VARIABLES
BEGIN
--CODE IS WRITTEIN IN BETWEEN BEGIN AND EXCEPTION.
EXCEPTION
--EXCEPTION PART OF CODE IS EXECUTED IF THERE IS AN ISSUE WITH CODE UNDER BEGIN CLAUSE.
END;

WRITING COMMENTS IN PL/SQL
/* 
 MULTI-LINE COMMENT.
*/
--SINGLE LINE COMMENT

WHEN WRITING PROGRAM BLOCKS - 
BEGIN
x := y || CHR(10) || z;

SQL functions -- 
number, character, data conversion, date etc.
scope of a variables and nested blocks.
Variable declared in outer block is accessible in the inner blocks - but not the other way around.
x integer;
begin
declare
   y integer;
begin
   y := y + x;
end;

 x := x + y; -- error
end;

Operators
All SQL operators
Logical, arithmetic, like, || % etc,

**

Logical 
NOT
true false
false true
null null

AND
1 0 N
1 1 0 N
0 0 0 0
N N 0 N

OR
1 0 N
1 1 1 1
0 1 0 N
N T N N
 
All operations in PL/SQL are solved in Precedence and Associativity.

Programming guidelines.

Chapter 3
Using DML ops in PL/SQL::

Revision to Select statment -- to read values in bind variables or PL/SQL variables.

DECLARE
X emp.ename%TYPE;
BEGIN
SELECT col1, col2
INTO var1, var2 
FROM
WHERE .;
END

Use bind variables for data input and then associate/use in DML Statements:
slide 3.11, 3.12..


Exam questions
MERGE - INSERTS OR UPDATES DATA INTO A NEW TABLE USING DATA FROM AN EXISTING TABLE

USING SOME CONDITIONAL LOGIC.

MERGE INTO <TABLENAME>
USING <SRC TABLE NAME>
ON < CONDITION USING COLUMNS>
WHEN MATCHED THEN
....INSERT/UPDATE STATEMENT
WHEN NOT MATCHED THEN
....INSERT/UPDATE STATEMENT
END;
/

CURSOR ATTRIBUTES (IMPLICIT AND EXPLICIT CURSOR TYPES)
ROWCOUNT
FOUND
NOTFOUND
ISOPEN

WORK WITH INSERT/UPDATE -- COMMIT/ROLLBACK.

WORKING WITH CONTROL STRUCTURES
IF, ELSE AND ELSIF
IF CONDITION
ACTION
ELSE
ACTION
END IF;


IF CONDITION1
ACTION1
ELSIF CONDITION2
ACTION2
ELSIF CONDITION3
ACTION3
ELSE
ACTION4
END IF;

CASE <varname>
WHEN <VAL> THEN ACTION1;
WHEN <VAL2> THEN ACTION2;
WHEN <VAL3> THEN ACTION3;
ELSE <DEFAULT ACTION>
END;

LOOPING CONSTRUCTS
LOOP

EXIT (WHEN CONDITION);
END LOOP;

WHILE (CONDITION) LOOP --PRE-TEST LOOP.

END LOOP;

FOR COUNTER IN LOW..HIGH LOOP


END LOOP;

FOR i in 1..22 loop
--statements.
end loop;

EXIT WHEN CONDITION..

ACTION - WATCH VIDEO LECTURE FOR COMPOSITE DATATYPES - 

Whiteboard notes - July 2nd 2012 (Unedited)

posted Jul 2, 2012, 9:28 PM by Sukhjit Singh

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.


How can I download the Server Concepts Manual?

posted Jun 26, 2011, 2:17 PM by Sukhjit Singh

Go to the following link and 
http://download-west.oracle.com/docs/cd/B10501_01/nav/docindex.htm?remark=homepage 
and download the Oracle Concepts manual and SQL Reference Manual. 

Oracle 9i Documentation is also available at 
http://otn.oracle.com/documentation/oracle9i.html 

How can I format query results?

posted Jun 26, 2011, 2:16 PM by Sukhjit Singh

You can go through the attached document to get started with Formatting get result.

How can I get started with SQLPlus?

posted Jun 26, 2011, 2:14 PM by Sukhjit Singh

Go through the attached document - it should help you get started with PL/SQL.

How do I setup schema for Class Assignments?

posted Jun 26, 2011, 2:11 PM by Sukhjit Singh

Attached is a list of files with instructions to help you setup the schema for lab assignments out of the reader. You also have access to misc files that will be needed for other exercises for the rest of the quarter. 

How to create unix account?

posted Jun 26, 2011, 1:51 PM by Sukhjit Singh

You have to be a registered student to be able to create unix account.

Go to the following link - http://cis.deanza.edu/cis/overview.html

and click on - Create your Bus/CIS Lab account.

1-8 of 8