script writing. objectives: definition of script writing types of script creating/modification and...

30
Chapter 3 Script Writing

Upload: damon-hill

Post on 04-Jan-2016

313 views

Category:

Documents


8 download

TRANSCRIPT

Page 1: Script Writing. Objectives: Definition of script writing Types of script Creating/Modification and running script Execution of script Fundamental of Script

Chapter 3Script Writing

Page 2: Script Writing. Objectives: Definition of script writing Types of script Creating/Modification and running script Execution of script Fundamental of Script

Objectives:Definition of script writingTypes of scriptCreating/Modification and running scriptExecution of scriptFundamental of ScriptSequential and conditional controlIteration process

Page 3: Script Writing. Objectives: Definition of script writing Types of script Creating/Modification and running script Execution of script Fundamental of Script

Facts:Procedural language is called PL/SQLHighly structuredEmbedded languagePowerful exception handlingOther programs may call PL/SQL

3

Page 4: Script Writing. Objectives: Definition of script writing Types of script Creating/Modification and running script Execution of script Fundamental of Script

Script writing:1. Unnamed Blocks2. Procedures3. Functions

4

Page 5: Script Writing. Objectives: Definition of script writing Types of script Creating/Modification and running script Execution of script Fundamental of Script

PL/SQL Basic:Comments

-- (single line)/* */ (Multi line)Rem

5

Page 6: Script Writing. Objectives: Definition of script writing Types of script Creating/Modification and running script Execution of script Fundamental of Script

PL/SQL Basic:• Identifiers

• Constant• Variables• Exceptions• Cursors• Functions/Procedures• Packages

6

Page 7: Script Writing. Objectives: Definition of script writing Types of script Creating/Modification and running script Execution of script Fundamental of Script

Unnamed Blocks:[DECLARE]

[Declarations]BEGIN

[Execution Statements][NULL;]

END;/

7

Page 8: Script Writing. Objectives: Definition of script writing Types of script Creating/Modification and running script Execution of script Fundamental of Script

Example:BEGIN

NULL;END;/

8

Page 9: Script Writing. Objectives: Definition of script writing Types of script Creating/Modification and running script Execution of script Fundamental of Script

Declarations:Variable Type [NOT NULL] [:= | Default]

Value;

OR

Variable Type%TYPE;

9

Page 10: Script Writing. Objectives: Definition of script writing Types of script Creating/Modification and running script Execution of script Fundamental of Script

Data Type:BOOLEANCHARCHARACTERSTRINGVARCHAR2NUMBERDECDECIMALFLOATINT

10

Page 11: Script Writing. Objectives: Definition of script writing Types of script Creating/Modification and running script Execution of script Fundamental of Script

Data Type (More…)INTEGERSMALLINTREALBINARY_INTEGERPOSITIVEDATE

11

Page 12: Script Writing. Objectives: Definition of script writing Types of script Creating/Modification and running script Execution of script Fundamental of Script

EXAMPLE:DECLARE

Is_True BOOLEAN;Flag BOOLEAN := FALSE;B_Date DATE;Today DATE := SYSDATE;Total INTEGER;Sum1 INTEGER := 20;Sum2 INTEGER DEFAULT 20;Credit INTEGER RANGE 10..200;Grade NUMBER(3, 2) NOT NULL :=1.00;A NUMBER :=Sum1 * 3;B B_Date%TYPE;S_ID Student.ID%TYPE;S_Rec Student%TYPE;

12

Page 13: Script Writing. Objectives: Definition of script writing Types of script Creating/Modification and running script Execution of script Fundamental of Script

CONSTANTS:DECLARE

No_Days_Per_Year CONSTANTS INTEGER:=366;Max_No_Student CONSTANTS INTEGER:=22;Registered CONSTANTS BOOLEAN:=FALSE;I_Grade CONSTANTS CHAR := ‘I’;Grade CONSTANTS CHAR DEFAULT ‘o’;

13

Page 14: Script Writing. Objectives: Definition of script writing Types of script Creating/Modification and running script Execution of script Fundamental of Script

NULL Value in Declaration:Max_No_Student INTEGER (4) NOT NULL :=

1000;Zipcode INTEGER NOT NULL :=

21532;

14

Page 15: Script Writing. Objectives: Definition of script writing Types of script Creating/Modification and running script Execution of script Fundamental of Script

Example:DECLARE

TOTAL INTEGER; --?Total INTEGER; --?

BEGINTotal :=24; --Errors

END;/

15

Page 16: Script Writing. Objectives: Definition of script writing Types of script Creating/Modification and running script Execution of script Fundamental of Script

Expression:**+, - (unary)*, /+,->, >=, =, <>, <=, IN, LIKE, IS NULLNOTANDOR

SYSDATE – B_Date/365; --?

16

Page 17: Script Writing. Objectives: Definition of script writing Types of script Creating/Modification and running script Execution of script Fundamental of Script

Example:DECLARE

A INTEGER;B BOOLEAN;C INTEGER := 5;

BEGINA :=10;B := C+2 = A;

END;/

17

Page 18: Script Writing. Objectives: Definition of script writing Types of script Creating/Modification and running script Execution of script Fundamental of Script

Load Data into Variables:DECLARE

Field1 Faculty.Name%TYPE;Field2 Faculty.ID%TYPE;Field3 Faculty.Salary%TYPE;

BEGINSELECT Name, ID, SalaryINTO Field1, Field2, Field3FROM FacultyWHERE ID=1111;DBMS_OUTPUT.PUT_LINE (‘Name: ’ || Field1 || ‘ ID: ’ || Field2);

END;/

18

Page 19: Script Writing. Objectives: Definition of script writing Types of script Creating/Modification and running script Execution of script Fundamental of Script

Load Data into Variables:DECLARE

MyName Faculty.Name%TYPE;MySalary Faculty.Salary%TYPE;

BEGINSELECT Name, Salary, SSNINTO MyName, MySalaryFROM ID=11111;

DELETE FROM FacultyWHERE Name = MyName;

END;/

19

Page 20: Script Writing. Objectives: Definition of script writing Types of script Creating/Modification and running script Execution of script Fundamental of Script

Conditional Statement:One way Selection: IF condition THEN

action ;END IF;

Two way Selection: IF condition THEN

action_1; ELSE

action_2;END IF;

20

Page 21: Script Writing. Objectives: Definition of script writing Types of script Creating/Modification and running script Execution of script Fundamental of Script

Conditional Statement:Multiple way Selection:

IF condition THENaction_1;

ELSIF condition THENaction_2;

……ELSE

action_3 END IF;

21

Page 22: Script Writing. Objectives: Definition of script writing Types of script Creating/Modification and running script Execution of script Fundamental of Script

Example:DECLARE

a NUMBER(2) := 22;b NUMBER(2) :=44;c VARCHAR2 (20);

BEGINIF a IS NULL OR b IS NULL THEN

c:=‘Empty’;END IF;IF a>=b THEN

c:=‘a is the largest’;ELSE

c:=‘a is the smallest’;END IF;DBMS_OUTPUT.PUT_LINE(c);

END;/

22

Page 23: Script Writing. Objectives: Definition of script writing Types of script Creating/Modification and running script Execution of script Fundamental of Script

ExampleDECLARE

Score NUMBER;Grade CHAR(1);

BEGIN……..………IF Score>=90 THEN

Grade :=‘A’;ELSIF Score>=80 THEN

Grade: =‘B’;ELSIF Score>=70 THEN

Grade:=‘C’;ELSE

Grade :=‘F’;END IF;DBMS_OUTPUT.PUT_LINE(Grade);

END;/

23

Page 24: Script Writing. Objectives: Definition of script writing Types of script Creating/Modification and running script Execution of script Fundamental of Script

Simple Iteration:LOOP

statements;END LOOP;

24

Page 25: Script Writing. Objectives: Definition of script writing Types of script Creating/Modification and running script Execution of script Fundamental of Script

Example:DECLARE

i BINARY_INTEGER :=0;Num NUMBER:=4;

BEGINLOOP

i:=i+1;Num := Num+2;IF i=5 THEN

EXIT;END IF;DBMS_OUTPUT.PUT_LINE(Num);

END LOOP;END;/

25

Page 26: Script Writing. Objectives: Definition of script writing Types of script Creating/Modification and running script Execution of script Fundamental of Script

WHILE LOOP:WHILE condition LOOP

statement(s);END LOOP;

26

Page 27: Script Writing. Objectives: Definition of script writing Types of script Creating/Modification and running script Execution of script Fundamental of Script

ExampleDECLARE

i BINARY_INTEGER :=0;Num NUMBER:=4;

BEGINWHILE i<>5 LOOP

Num := Num+2;i:=i+1;

END LOOP;DBMS_OUTPUT.PUT_LINE(Num);

END;/

27

Page 28: Script Writing. Objectives: Definition of script writing Types of script Creating/Modification and running script Execution of script Fundamental of Script

FOR LOOP:FOR index IN[REVERSE] exp..exp LOOP

statement(s);END LOOP;

28

Page 29: Script Writing. Objectives: Definition of script writing Types of script Creating/Modification and running script Execution of script Fundamental of Script

ExampleDECLARE

i BINARY_INTEGER ;BEGIN

FOR i IN 1..5 LOOP DBMS_OUTPUT.PUT_LINE(i);

END LOOP;END;/

29

Page 30: Script Writing. Objectives: Definition of script writing Types of script Creating/Modification and running script Execution of script Fundamental of Script

Example – REVERSE:DECLARE

i BINARY_INTEGER ;BEGIN

FOR i IN REVERSE 1..5 LOOP DBMS_OUTPUT.PUT_LINE(i);

END LOOP;END;/

30