PL/SQL is a procedural language designed specifically to embrace SQL statements., Slides of Database Management Systems (DBMS)

Download PL/SQL is a procedural language designed specifically to embrace SQL statements. and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

1 Inroduction to PL/SQL

2 PL/SQL BLOCK

4 Conditional statements

3 Data type and Variables

5 Loop statements

CONTENTS

6 PL/SQL CURSOR

7 PL/SQL Procedure/Function

Implicit Cursors

Explicit Cursors

8 References

PL/SQL BLOCK

It is the main part that contains all the code. The actual input

contains the SQL statements and the instructions that will interact

with the database.

DECLARE - - (optional) /* Here you declare the variables you will use in this block / BEGIN - - (mandatory) / Here you define the executable statements (what the block DOES!)/ EXCEPTION - - (optional) / Here you define the actions that take place if an exception is thrown during the run of this block */ END; - - (mandatory)

DATA TYPE AND VARIABLES Use of Data-Types <variable-name> [not null][: =<initial-value>]; <constant-name> constant : = ; Number – used to store any number Char - is a fixed-length data type that contains a constant number of characters. Varchar2 - is a variable-length data type that does not contain a constant number of characters. Date – used to store dates. The most common data types : more...

Radius: 9. Diameter: 19 Circumference: 59. Area: 283. DECLARE pi constant number := 3.141592654; radius number(5,2); dia number(5,2); circumference number(7, 2); area number (10, 2); BEGIN radius := 9.5; dia := radius * 2 ; circumference := 2.0 * pi * radius; area := pi * radius * radius; dbms_output.put_line ('Radius: ' || radius); dbms_output.put_line ('Diameter: ' || dia); dbms_output.put_line ('Circumference: ' || circumference); dbms_output.put_line ('Area: ' || area); END; A Simple Program to Calculate the Circumference and Area of a Circle OUTPUT

DECLARE num number := 10 ; x num%type := 20 ; name employee.fname%type := 'Ahmad'; P1 project%rowtype; BEGIN dbms_output.put_line ('num: ' || num); dbms_output.put_line ('x: ' || x); dbms_output.put_line ('name: ' || name); SELECT Pname , Plocation INTO P1.pname , P1.plocation FROM project WHERE Pnumber= 10 ; dbms_output.put_line ('P1.Name: ' || P1.pname || CHR(10) || 'P1.Plocation: ' || P1.Plocation);

- - CHR(10) --->> New line

EXCEPTION WHEN NO_DATA_FOUND THEN dbms_output.put_line (SQLERRM); WHEN TOO_MANY_ROWS THEN dbms_output.put_line (SQLERRM); END; num: 10 x: 20 name: Ahmad P1.Name: Computerization P1.pLocation: Stafford OUTPUT Example

Some PL / SQL - operators

Syntax (IF-THEN-ELSIF-ELSE) :

DECLARE grade char(1) := :EnterGrade; BEGIN IF grade = 'A' THEN dbms_output.put_line('Excellent'); ELSIF grade = 'B' THEN dbms_output.put_line('Very good'); ELSIF grade = 'C' THEN dbms_output.put_line('Good'); ELSIF grade = 'D' THEN dbms_output.put_line('Fair'); ELSE dbms_output.put_line('Fail'); END IF; END; IF condition 1 THEN {...statements to execute when condition1 is TRUE...} ELSIF condition THEN {...statements to execute when condition2 is TRUE...} ELSE {...statements to execute when if all of the previous

. conditions are false...} END IF; PL/SQL If

PL/SQL Case Statement

Syntax for the CASE statement:DECLARE

dayOfWeek char(1) ; BEGIN SELECT TO_CHAR(SYSDATE, 'D') INTO dayOfWeek FROM DUAL; CASE dayOfWeek WHEN 1 THEN dbms_output.put_line('Today is Sunday'); WHEN 2 THEN dbms_output.put_line('Today is Monday'); WHEN 3 THEN dbms_output.put_line('Today is Tuesday'); WHEN 4 THEN dbms_output.put_line('Today is Wednesday'); WHEN 5 THEN dbms_output.put_line('Today is Thursday'); WHEN 6 THEN dbms_output.put_line('Today is Friday'); WHEN 7 THEN dbms_output.put_line('Today is Saturday'); ELSE dbms_output.put_line('Error'); END CASE; END;

CASE (Expression)

WHEN condition_1 THEN result_ WHEN condition_2 THEN result_ WHEN condition_3 THEN result_ ELSE result END CASE;

PL/SQL Exit Loop (Basic Loop)

Syntax of exit loop:

DECLARE

sumn number := 0 ; counter number := 1 ; BEGIN LOOP sumn := sumn + counter; counter := counter + 1 ; IF counter > 10 THEN EXIT; END IF; END LOOP; dbms_output.put_line ('The sum is :' || sumn); END;

LOOP statements; EXIT; {or EXIT WHEN condition;} END LOOP; The sum is : 55 OUTPUT

PL/SQL While Loop Syntax of while loop: - - Calculating even numbers between 1 and 100

DECLARE

counter number := 1 ; sumn number := 0 ; BEGIN WHILE counter <= 100 LOOP IF counter MOD 2 = 0 THEN sumn := sumn + counter; END IF; counter := counter + 1 ; END LOOP; dbms_output.put_line ('The sum is:' || sumn); END;

WHILE ; LOOP statements; END LOOP; The sum is : 2550 OUTPUT

PL/SQL Cursor PL/SQL cursor is a pointer that points to a result of a query. It contains information on a select statement and the rows of data accessed by it. A cursor is used to fetch and process the rows returned by the SQL statement, one at a time. There are two types of cursors: explicit and implicit.

**PL/SQL Cursor

1. PL/SQL Implicit Cursors The implicit cursors are automatically generated by Oracle while an SQL statement is executed, if you don't use an explicit cursor for the statement. These are created by default to process the statements when DML statements like INSERT, UPDATE, DELETE etc. are executed.**