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 -