15709_plsql11

7/30/2019 15709_plsql11

1/25

1.Basic structure of sql

2.Introduction to pl/sql

3.Sql vs pl/sql

4.Advantages of pl/sql5.Architecture of pl/sql

6.PL/SQL language elements

7.PL/SQL control statements

www.starthack.com

7/30/2019 15709_plsql11

2/25

BASIC STRUCTURE OF SQL

1. SQL is the language used to manipulate relational database. Sql is

tied very closely with the relational model

2. It is simple and power full language used to create ,manipulate and

retrieve data and structure in the database..

3. SQL is a non-procedural language i.e. it describe what is required

without how result should be computed..

4. It is industry standard language for relational database management

system such as oracle, Microsoft SQL Servers, MS Access

5. ISO and ANSI has published standards for SQL implementation Join

effort by ISO and ANSI has lead to standard version of SQL is called

SQL/86(SQL1), SQL/92(SQL2)

www.starthack.com

7/30/2019 15709_plsql11

3/25

INTRODUCTION TO PL/SQL

1. PL/SQL stands for Procedural Language/Structured Query Language, and it is

an extension of the SQL Language

2. It is superset of the Structured Query Language specialized for use in the

oracle database.

3. Because it is procedural language, it eliminates many restriction of the SQL

language

4. With the use of SQL user can only perform basic operations such as selecting

the information from some prefabricated tables, inserting information into

those tables, updating the information stored into tables and also used to

delete information from these tables

5. PL/SQL extends SQL by adding control structure found in the other procedural

languages

6. PL/SQL is the combination of SQLs languages ease of data manipulation and

the procedural languages ease of programming.www.starthack.com

7/30/2019 15709_plsql11

4/25

7. With PL/SQL, we can use SQL statements to manipulate ORACLE data and

the flow of control statements to process the data. Moreover, we can declare

constants and variables, define subprograms (procedures and functions).

Thus, PL/SQL combines the data manipulating power of SQL with the data

processing power of procedural languages

8. While PL/SQL is just like any other programming language, it has syntax and

rules that determine how programming statements work together.

It is important for you to realize that PL/SQL is not a stand-alone programming

language

9. PL/SQL is a part of the Oracle RDBMS, and it can reside in two environments

the client and the server

10. As a result, it is very easy to move PL/SQL modules between server-sideand client-side applications

11. When the PL/SQL engine is located on the server, the whole PL/SQL block

is passed to the PL/SQL engine on the Oracle server.

www.starthack.com

7/30/2019 15709_plsql11

5/25

7/30/2019 15709_plsql11

6/25

When the PL/SQL engine is located on the client, as it

is in the Oracle Developer Tools, the PL/SQLprocessing is done on the client side.

All SQL statements that are embedded within thePL/SQL block are sent to the Oracle server for further

processing. When PL/SQL block contains no SQL

statement, the entire block is executed on the client

side.

7/30/2019 15709_plsql11

7/25

SQL VS PL/SQL

SQL PL/SQL

SQL does not have any procedural

capabilities

ORACLE has provides all procedural

capabilities in PL/SQL to support datafiltration

SQL statements are passed to oracle

engine(server) one at a time..Therefore

each time for each statement a call is

made to the server resources and these

resources are opened and closed every

time

In PL/SQL it sends the bundle of SQL

statements to the oracle server in the form

of BLOCK and hence calling the server

resources only once for that block even if

that block is having more than one SQL

statements

Due to this, Generating network traffic

resulting in slow processing.

After processing all the statements in a

block ORACLE server closer the resources

results in faster execution of SQLstatements in a PL/SQL block.

In SQL, there is no provision for handling

errors and exception, which means that if

any SQL statements fails to execute, then

oracle gives its own error message anderror code many not be user friendly

In PL/SQL, we can program the block of

statements to handle the errors in such a

way that if any of the statement fails to

execute then we can display user friendlyappropriate message.

7/30/2019 15709_plsql11

8/25

SQL does not support PL/SQL

statements

PL/SQL supports SQL statements in its

block

We cannot store the intermediate results

of a query in variable

PL/SQL supports declaring the variables

so as to store intermediate results for

later use

7/30/2019 15709_plsql11

9/25

7/30/2019 15709_plsql11

10/25

ADVANTAGES OF PL/SQL

Supports the declaration and manipulation of object types and collection

Allowing the calling of external function and procedure

Contains new libraries of built-in packages.A package is a file that groupfunctions, cursors, stored procedures and variables in one place

TRIGGERS: Trigger is a PL/SQL program that is stored in the database an

executed immediately before or after the INSERT, UPDATE,

and DELETE command

CURSORS: Oracle uses workspaces to executes the SQL commands.

Through PL/SQL it is possible to name the workspace and

access its information

PL/SQL allow us to use all the SQL data manipulation commands, tran-

section control commands, SQL functions and operators, thus allowing us to

manipulates data values in a table more flexibly and effectively

7/30/2019 15709_plsql11

11/25

PL/SQL BLOCK STRUCTURE

PL/SQL blocks contain three sections

1. Declare section

2. Executable section

3. Exception-handling section.

The executable section is the only mandatory section

of the block.

Both the declaration and exception-handling sections

are optional.

7/30/2019 15709_plsql11

12/25

PL/SQL block has the following structure:

DECLARE - Declaration statements

BEGIN - Executable statements

EXCETION - Exception-handling statements

END ;

7/30/2019 15709_plsql11

13/25

DECLARATION SECTION

The declaration section is the first section of thePL/SQL block..

It contains definitions of PL/SQL identifiers such as

variables, constants, cursors and so on..

Example

DECLARE

v_first_name VARCHAR2(35) ;v_last_name VARCHAR2(35) ;

v_counter NUMBER := 0 ;

7/30/2019 15709_plsql11

14/25

EXECUTABLE SECTION

The executable section is the next section of the PL/SQL block.

This section contains executable statements that allow you to

manipulate the variables that have been declared in the declaration

section.

BEGIN

SELECT first_name, last_name

INTO v_first_name, v_last_name

FROM student

WHERE student_id = 123 ;DBMS_OUTPUT.PUT_LINE

(Student name : || v_first_name || || v_last_name);

END;

7/30/2019 15709_plsql11

15/25

EXCEPTION-HANDLING SECTION

The exception-handlingsection is the last section of the

PL/SQL block.

This section contains statements that are executed when a

runtime error occurs within a block.

Runtime errors occur while the program is running and cannotbe detected by the PL/SQL compiler.

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE

( There is no student with student id 123 );

END;

7/30/2019 15709_plsql11

16/25

Set serveroutput on

DECLARE

v_first_name VARCHAR2(35);

v_last_name VARCHAR2(35);

BEGIN

SELECT first_name, last_name

INTO v_first_name, v_last_nameFROM student

WHERE student_id = 123;


('Student name: '||v_first_name||' '||v_last_name);

EXCEPTION

WHEN NO_DATA_FOUND THEN


('There is no student with student id 123');

END ;

EXAMPLE

7/30/2019 15709_plsql11

17/25

PL/SQL LANGUAGE ELEMENTS

Operators and Indicators

Identifiers

Literals

Comments

Expression and Comparisons

Data Types and Declarations

7/30/2019 15709_plsql11

18/25

Operator and Indicators

ARITHMETIC OPERATOR :: Used to perform arithmetic operations

Example :- +, -, *, /

EXPRESSION OPERATORS :: Used to create assignment, range and

string concatenation expression.

Example :-

Assignment Operator :: A:=2Range Operator :: 1..4

Concatenate :: || ( dav||college)

LOGICAL OPERATOR:=COMPARISON OPERATOR:=

7/30/2019 15709_plsql11

19/25

Literals It specifies and exact value in a programIt is and explicitNumeric, character, string and Boolean value not represented

by an identifierLiterals are used to initialize constants,

variables and other data values.

Example: 10, -19, 9.99 (Numeric Literals)

a,A,?,,,) etc..are character literals

starthack,tajinder,1234 (String Literals)

TRUE & FALSE (Boolean Literals)

Comments Begin with /* and end with an asterisk-slash */

Example: /* Statements to select rate and quantity into variables and

caluclate value */

7/30/2019 15709_plsql11

20/25

Data Types and Declaration

Some commonly used data types for PL/SQL are :-

NUMBER(store numeric data)

CHAR VARCHAR2(store variable length character data)

DATE

BOOLEAN

PL/SQL CONTROL STATEMENTS

7/30/2019 15709_plsql11

21/25

PL/SQL CONTROL STATEMENTS

We can change the logical flow of statements within the PL/SQL block with

a number of control structure.

Control Structure Can Be ::

Conditional Control

Iterative Control

Sequential Control

Conditional Control :- It allows testing the truth of a condition and

executing section of program depending upon

the condition that may be true or false..

Iterative Control :- It allows executing a section of program repeatedly aslong as a specified condition remain true..

Sequential Control :- It allows ordering the sequence of processing section

of program..

7/30/2019 15709_plsql11

22/25

CONDITIONAL CONTROL

IF THEN Statement

IF THEN ELSE Statement

IF THEN ELSIF Statement

IF THEN Statement ::

Syntax

IF condition THEN

Sequence_of_statements;

END IF;

Example :-

IF a>b THEN

dbms_output.put_lines( a is greater);

END IF;

7/30/2019 15709_plsql11

23/25

IF THEN ELSE ::

Syntax

IF condition THEN

Sequence_of_statements1;ELSE;

Sequence_of_statements2;

END IF;

IF ConditionTRUE FALSE

THEN action ELSE action

EXAMPLE

To illustrate of IF-ELSE construct PL/SQL block to find greate of two numbers

User will enter any two numbers and IF statement will check for the greater

among the entered number.

7/30/2019 15709_plsql11

24/25

SOLUTION :-

DECLAREA NUMBER := &ENTER_A;

B NUMBER := &ENTER_B;

BEGIN

IF A > B THENDBMS_OUTPUT.PUT_LINE(A IS GREATER);

ELSE

DBMS_OUTPUT.PUT_LINE(B IS GREATER);

END IF;

END;

7/30/2019 15709_plsql11

25/25

IF THEN ELSEIF :: The third form of IF statement uses the keyword

ELSIF (not ELSEIF) to introduce additional

Condition, as follows

SYNTAX

IF condition1 THEN


ELSIF condition2 THEN


ELSE


END IF;

15709_plsql11

Documents