Announcements‎ > ‎

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 - 

Comments