Scribd
Upload
72 views22 pages

01 - Introduction To PL SQL

intro pl sql lesson1

Uploaded by

Shamsedin
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
72 views22 pages

01 - Introduction To PL SQL

intro pl sql lesson1

Uploaded by

Shamsedin
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 22

Advanced Computing Training School (ACTS)

Advanced Computing for Human Advancement

Introduction to PL/SQL
- Jayendra Khatod

Copyright CDAC-ACTS

Advanced Computing Training School (ACTS)


Advanced Computing for Human Advancement

Objectives

Introduction
About PL/SQL
PL/SQL Environment
Benefits of PL/SQL
Write PL/SQL Block
Declare PL/SQL variables
Execute a PL/SQL block

Copyright CDAC-ACTS

Advanced Computing Training School (ACTS)


Advanced Computing for Human Advancement

About PL/SQL
PL/SQL is Oracle Corporations procedural
language extension to SQL, the standard data
access language for relational databases.
PL/SQL offers modern software engineering
features such as data encapsulation,
exception handling, information hiding, and
therefore brings state-of-the-art programming
capability.

Copyright CDAC-ACTS

Advanced Computing Training School (ACTS)


Advanced Computing for Human Advancement

About PL/SQL
PL/SQL incorporates many of the advanced
features in programming languages
With PL/SQL, you can use SQL statements to
retrieve Oracle data and PL/SQL control
statements to process the data.

Copyright CDAC-ACTS

Advanced Computing Training School (ACTS)


Advanced Computing for Human Advancement

PL/SQL Environment

PL/SQL engine
PL/SQL
BLOCK

PL/SQL
BLOCK

PL/SQL
SQL

Procedural
statement
executor

SQL statement executor

Oracle server

Copyright CDAC-ACTS

Advanced Computing Training School (ACTS)


Advanced Computing for Human Advancement

Benefits of PL/SQL
You can reuse programs
You can declare variables
You can program with procedural language
control structures
PL/SQL can handle errors

Copyright CDAC-ACTS

Advanced Computing Training School (ACTS)


Advanced Computing for Human Advancement

PL/SQL Block Structure


DECLARE
v_variable
BEGIN
SELECT
INTO
FROM

VARCHAR2(5);

column_name
v_variable
table_name;

EXCEPTION
WHEN exception_name THEN
...
END;
Copyright CDAC-ACTS

Advanced Computing Training School (ACTS)


Advanced Computing for Human Advancement

PL/SQL Block Types


Subprograms (Named Blocks)

Anonymous Block
Procedure

[DECLARE]

BEGIN
--statements
-statements
[EXCEPTION]

END;

PROCEDURE name
IS

Function

[EXCEPTION]

FUNCTION name
RETURN datatype
IS
BEGIN
--statements
-statements
RETURN value;
[EXCEPTION]

END;

END;

BEGIN
--statements
-statements

Copyright CDAC-ACTS

Advanced Computing Training School (ACTS)


Advanced Computing for Human Advancement

Use of Variables
Variables are used for:
Temporary storage of data
Manipulation of stored values
Reusability
Ease of maintenance

Copyright CDAC-ACTS

Advanced Computing Training School (ACTS)


Advanced Computing for Human Advancement

Handling Variables in PL/SQL


Declare and initialize variables in the
declaration section.
Assign new values to variables in the
executable section.
Pass values into PL/SQL blocks through
parameters.
View results through output variables.
Copyright CDAC-ACTS

10

Advanced Computing Training School (ACTS)


Advanced Computing for Human Advancement

Declaring PL/SQL Variables


Syntax
identifier [CONSTANT] datatype [NOT
NULL]
[:= | DEFAULT expr];

Examples
Declare
v_hiredate
v_deptno
v_location
c_comm

DATE;
NUMBER(2) NOT NULL := 10;
VARCHAR2(13) := 'Atlanta';
CONSTANT NUMBER := 1400;
Copyright CDAC-ACTS

11

Advanced Computing Training School (ACTS)


Advanced Computing for Human Advancement

Declaring PL/SQL Variables


The Guidelines
Follow naming conventions.
Initialize variables designated as NOT
NULL and CONSTANT.
Initialize identifiers by using the
assignment operator (:=) or the
DEFAULT reserved word.
Declare at most one identifier per line.
Two variables can have the same name,
provided they are in different blocks.
Copyright CDAC-ACTS

12

Advanced Computing Training School (ACTS)


Advanced Computing for Human Advancement

Assigning Values to Variables


Syntax
identifier := expr;
Examples
Set a predefined hiredate for new employees.
v_hiredate := '01-JAN-04';
Set the employee name to Paul.

v_ename := 'Paul';

Copyright CDAC-ACTS

13

Advanced Computing Training School (ACTS)


Advanced Computing for Human Advancement

Variable Initialization and Keywords


Using:
Assignment operator (:=)
DEFAULT keyword
NOT NULL constraint

Copyright CDAC-ACTS

14

Advanced Computing Training School (ACTS)


Advanced Computing for Human Advancement

Scalar Variable Declarations


Examples
v_job
v_count
v_total_sal
v_orderdate
c_tax_rate
v_valid

VARCHAR2(9) DEFAULT 'ADMIN';


BINARY_INTEGER := 0;
NUMBER(9,2) := 0;
DATE := SYSDATE + 7;
CONSTANT NUMBER(3,2) := 8.25;
BOOLEAN NOT NULL := TRUE;

Copyright CDAC-ACTS

15

Advanced Computing Training School (ACTS)


Advanced Computing for Human Advancement

The %TYPE Attribute


Declare a variable according to:
A database column definition
Another previously declared
variable
Prefix %TYPE with:
The database table and column
The previously declared variable
name

Copyright CDAC-ACTS

16

Advanced Computing Training School (ACTS)


Advanced Computing for Human Advancement

Declaring Variables with the %TYPE Attribute


Examples
...
v_ename
v_balance
v_min_balance
...

emp.ename%TYPE;
NUMBER(7,2);
v_balance%TYPE := 10;

Copyright CDAC-ACTS

17

Advanced Computing Training School (ACTS)


Advanced Computing for Human Advancement

DBMS_OUTPUT.PUT_LINE
Example
SET SERVEROUTPUT ON
DEFINE p_annual_sal = 60000
DECLARE
v_sal NUMBER(9,2) := &p_annual_sal;
BEGIN
v_sal := v_sal/12;
DBMS_OUTPUT.PUT_LINE ('The monthly salary is ' ||
TO_CHAR(v_sal));
END;

Copyright CDAC-ACTS

18

Advanced Computing Training School (ACTS)


Advanced Computing for Human Advancement

Commenting Code
Prefix single-line comments with two dashes (--).
Place multi-line comments between the symbols
/* and */.
Example
...
v_sal NUMBER (9,2);
BEGIN
/* Compute the annual salary based on the
monthly salary input from the user */
v_sal := &p_monthly_sal * 12;
END;
-- This is the end of the block

Copyright CDAC-ACTS

19

Advanced Computing Training School (ACTS)


Advanced Computing for Human Advancement

SQL Functions in PL/SQL


Available in procedural statements:
Single-row number
Single-row character
Datatype conversion
Same as in SQL
Date, General

Not available in procedural statements:


DECODE
Group functions

Copyright CDAC-ACTS

20

Advanced Computing Training School (ACTS)


Advanced Computing for Human Advancement

Summary
PL/SQL blocks are composed of the following
sections:
Declarative (optional)
Executable (required)
Exception handling (optional)
A PL/SQL block can be an anonymous block, a
procedure, or a function.
DBMS_OUTPUT.PUT_LINE
SQL Functions in PL/SQL
Copyright CDAC-ACTS

21

Advanced Computing Training School (ACTS)


Advanced Computing for Human Advancement

Thank You !

Copyright CDAC-ACTS

22

You might also like