Oracle PLSQL
TCS INTERNAL
Chapter 1 - Introduction to PLSQL
TCS INTERNAL
PL SQL Introduction
• PLSQL means Procedural Language extension of SQL.
• Constructs like procedures, loops, variables, objects etc are
supported by PLSQL.
Oracle SQL + Procedural Language Features = PL/SQL
• PLSQL programs are divided into blocks.
• Each block can contain SQL and PL/SQL statements organized in
an order to execute business logic.
• PLSQL blocks can be stored in memory for reusability.
-3- TCS INTERNAL
PL/SQL, How does it differ from SQL?
Server Server
SQL SQL SQL
Query1 Query2 Query3 SQL SQL SQL
Query1 Query2 Query3
PL-SQL Block
Client
Client
• In SQL, each SQL statement is processed separately and
results are returned separately
• In PL/SQL, any number of queries can be bundled in a block
and in single network trip the bundle can be processed.
-4- TCS INTERNAL
Main Advantages of PL/SQL
Better Performance
Integration with SQL
Portability
Security
-5- TCS INTERNAL
PL/SQL Program Structure
Starts with the keyword Defines all variables, cursors, subprograms,
DECLARE and other elements to be used in the
program.
Declaration Optional
Consists of the executable PL/SQL
Enclosed between the
statements of the program. Should have at
Executable keywords
least one executable line of code, which may
Commands BEGIN and
be just a NULL command to indicate that
END Mandatory nothing should be executed.
Contains exceptions that handle errors in the
Exception Starts with the keyword
program.
Handling EXCEPTION.
Optional
-6- TCS INTERNAL
PL/SQL Block Structure
• DECLARE – Optional
– Variables, cursors, user-defined
exceptions
• BEGIN – Mandatory
– SQL statements
– PL/SQL statements
• EXCEPTION – Optional
– Actions to perform when
DECLARE
errors occur
• END; – Mandatory BEGIN
EXCEPTION
END;
-7- TCS INTERNAL
PL/SQL Comments
• Comments are explanatory statements which are written in the code
to help others understand the code better.
• All characters available inside the comment are ignored by PL/SQL
compiler.
• PL/SQL supports two types of comments
➢ single-line comment (starts with --)
➢ multi line comment (enclosed within /* and */)
-8- TCS INTERNAL
Sample PL/SQL program
DECLARE
-- variable declaration
message varchar2(20):= 'Hello, World!';
BEGIN
/*
PL/SQL executable statement(s)
*/
dbms_output.put_line(message); -- print message in the console
END;
/
Output :
Hello World
PL/SQL procedure successfully completed.
-9- TCS INTERNAL
Chapter 2 - Data Types and Variables
TCS INTERNAL
PL/SQL Data Types
• PL/SQL variables, constants and parameters must have a
valid data type, which specifies a storage format,
constraints, and valid range of values.
• PL/SQL data types can be broken down into the following
categories:
Scalar: Character/String, Number, Boolean, Date/Time
Reference: REF CURSOR, REF
Composite: records, nested tables, index-by tables, and
varrays.
LOB (Large Object): BLOB, CLOB, BFILE, NCLOB
- 11
- 11 - TCS INTERNAL
-
PL/SQL Variables
⚫ Variables are memory regions used in a PL/SQL block to hold
data.
⚫ Defined in the DECLARATION section of the block, where they
are assigned a specific data type, size and are often initialized
with a value.
⚫ When a variable is declared, PL/SQL allocates memory for the
variable's value and the storage location is identified by the
variable name.
- 12
TCS INTERNAL
-
Declaring PL/SQL Variables
Syntax
variable_name [CONSTANT] datatype [NOT NULL]
[:= | DEFAULT initial_value];
Example
DECLARE
v_hiredate DATE;
v_deptno NUMBER(2) NOT NULL := 10;
v_location VARCHAR2(13) := 'Atlanta';
c_comm CONSTANT NUMBER := 1400;
v_salary NUMBER DEFAULT 0;
- 13
TCS INTERNAL
-
Initializing Variables
A variable can be initialized with a value using
• The DEFAULT keyword
• The assignment operator (:=)
• Values can be directly assigned from database columns to
variables using SELECT..INTO statement.
Syntax
variable_name := expr;
Example:
counter binary_integer := 0;
greetings varchar2(20) DEFAULT 'Happy Learning';
- 14
TCS INTERNAL
-
Variable Scope
⚫ PL/SQL allows nesting of blocks within blocks.
⚫ Scope refers to the accessibility and availability of a
variable within a block.
There are two types of variable scope:
➢Local variables - variables declared in an inner block and
not accessible to outer blocks.
➢Global variables - variables declared in the outermost
block or a package and can be accessed from all the inner
blocks
- 15
TCS INTERNAL
-
Variable Scope Example
DECLARE
-- Global variables
num1 number := 95;
num2 number := 85;
BEGIN
dbms_output.put_line('Outer Variable num1: ' || num1);
dbms_output.put_line('Outer Variable num2: ' || num2);
DECLARE
-- Local variables
num3 number := 195;
num2 number := 185;
BEGIN
dbms_output.put_line('Inner Variable num1: ' || num1);
dbms_output.put_line('Inner Variable num2: ' || num2);
dbms_output.put_line('Inner Variable num3: ' || num3);
END;
END;
/
- 16
TCS INTERNAL
-
Variable Scope Example Output
Output:
Outer Variable num1: 95
Outer Variable num2: 85
Inner Variable num1: 95
Inner Variable num2: 185
Inner Variable num3: 195
PL/SQL procedure successfully completed
Here, num2 is declared as a global and a local variable. Inside the sub
block, num2 is local variable and outside the block it would take the value
of the global variable.
- 17
TCS INTERNAL
-
%TYPE
⚫ %TYPE can be used to declare a variable with a data type that
directly maps to the data type of a
➢ column of a table or
➢ another variable
⚫ %TYPE is mainly useful when declaring variables that will hold
database values. When the data type of a table column changes,
all variables mapped to that column type will automatically
change.
Syntax:
variable_name TABLE_NAME.COLUMN_NAME%TYPE;
- 18
TCS INTERNAL
-
%TYPE Example
⚫ In the given example, Employee is the table name and ename is
the column name. If ename is of the type varchar2(30) then the
variable v_maiden_name will also have the same data type.
⚫ Similarly the variable v_books_sold will always have the same
data type as that of the variable v_books_printed.
v_maiden_name Employee.ename%TYPE ;
v_books_printed NUMBER(6);
v_books_sold v_books_printed%TYPE;
⚫ The advantages of using %TYPE is that there is no need to know
the exact data type of the column ename and if the data type of
ename changes, the variable type will also change automatically.
- 19
- 19 - TCS INTERNAL
-
%ROWTYPE
⚫ %ROWTYPE attribute provides a record type that represents a
row in the table or a record of the query output. It maps the
variable to all columns in the table.
⚫ When any of the table column data type is modified, the change
will be reflected in the structure of the variable the next time it is
run or compiled.
⚫ Table columns in a row and corresponding fields in the record
type will have the same name and data type.
Syntax:
variable_name TABLE_NAME%ROWTYPE;
- 20
- 20 - TCS INTERNAL
-
%ROWTYPE Example
In the below example, Employee is a table with below fields
• empid number(10)
• empname varchar2(30)
• salary number(7)
v_emp_row is a record type variable which has the same column
definition of Employee record. The fields of the variable would be
accessed as shown below
v_emp_row Employee%ROWTYPE;
v_emp_row.empid
v_emp_row.empname
v_emp_row.salary
- 21
- 21 TCS INTERNAL
-
SQL Statements in PL/SQL
• Extract a row of data from the database by using the SELECT
command.
• Make changes to rows in the database by using DML commands.
INSERT
UPDATE
DELETE
• Control a transaction with the COMMIT, ROLLBACK, or SAVEPOINT
command.
- 22
TCS INTERNAL
-
Retrieving Data in PL/SQL
Retrieve data from the database with SELECT.
SELECT statement must use with INTO clause and it must return exactly one
row.
SELECT select_list
INTO {variable_name[, variable_name]...
| record_name}
FROM table
WHERE condition;
Example
DECLARE
v_doc_name wc_doctor.doc_name%TYPE;
v_con_fee wc_doctor.consult_fee%TYPE;
BEGIN
SELECT doc_name,consult_fee
INTO v_doc_name, v_con_fee
FROM wc_doctor
WHERE doc_id = 5011;
dbms_output.put_line('doctor name is '||v_doc_name);
dbms_output.put_line('consult
- 23 fee is '||v_con_fee);
END; - TCS INTERNAL
Updating and Deleting Data
Increase the salary of all employees in the Doctor table who are Analysts
Example:
DECLARE
v_sal_increase wc_doctor.consult_fee%TYPE := 2000;
BEGIN
UPDATE wc_doctor
SET consult_fee = consult_fee + v_sal_increase
WHERE dept_cd = 'RAD';
END;
Delete rows that belong to department 10 from the Doctor table.
DECLARE
v_deptno wc_doctor.dept_cd%TYPE := 'GYN';
BEGIN
DELETE FROM wc_doctor
WHERE dept_cd = v_deptno;
END;
- 24
TCS INTERNAL
-
Program Flow Control in PLSQL
TCS INTERNAL
Conditional Control
The logical flow of statements can be changed using conditional IF
statements and loop control structures.
Conditional IF statements:
IF-THEN-END IF
IF-THEN-ELSE-END IF
IF-THEN-ELSIF-END IF
Syntax Example
IF condition THEN IF V_DEPTNO = 10 THEN
statements; V_JOB := 'SALESMAN';
[ELSIF condition THEN ELSIF V_DEPTNO = 20 THEN
statements;] V_JOB := ‘EXECUTIVE';
[ELSE ELSE
statements;] V_JOB := ‘MANAGER';
END IF; END IF;
- 26
TCS INTERNAL
-
Iterative Control
• Loops repeat a statement or sequence of statements multiple times.
• Loop types:
Simple loop
FOR loop
WHILE loop
• Loops can be Nested to multiple levels.
• Exit loop with the EXIT statement
- 27
TCS INTERNAL
-
Simple Basic Loop
Syntax
LOOP -- delimiter
statement1; -- statements
. . .
EXIT [WHEN condition]; -- EXIT statement
END LOOP; -- delimiter
where: condition is a Boolean variable or
expression (TRUE, FALSE,
or NULL);
DECLARE
v_ordid item.ordid%TYPE := 100;
BEGIN
FOR i IN 1..10 LOOP
INSERT INTO item(ordid, itemid)
VALUES(v_ordid, i);
END LOOP;
END;
- 28
TCS INTERNAL
-
Numeric FOR Loop
Syntax
FOR index in [REVERSE]
lower_bound..upper_bound LOOP
statement1;
statement2;
. . .
END LOOP;
Loop index is implicitly of type NUMBER
It is only defined within the loop
Value may be referenced in an expression, but a new value may not be
assigned to it within the loop
Example
BEGIN
FOR i IN REVERSE 21…30 LOOP
INSERT INTO EMP_CNT(CNT)VALUES (i);
END LOOP;
END;
- 29
TCS INTERNAL
-
WHILE Loop
The sequence of statements will be repeated as long as condition evaluates
to TRUE
WHILE condition LOOP Condition is
statement1; evaluated at the
statement2; beginning of
. . . each iteration.
END LOOP;
Example
DECLARE
ctr NUMBER (3) := 0;
BEGIN
WHILE ctr < 500 LOOP
INSERT INTO temp(message)
VALUES (‘Data inserted successfully’);
ctr := ctr +1 ;
END LOOP;
END;
- 30
TCS INTERNAL
-
Procedure
TCS INTERNAL
What is a Procedure
A Procedure is a named PL/SQL block or Subprogram which performs one
or more specific task.
Similar to a procedure in other programming languages.
Also mentioned as Stored Procedure or simply Proc.
32 TCS INTERNAL
How to create a procedure
CREATE [OR REPLACE] PROCEDURE proc_name [list of
parameters]
IS or AS
Declaration section
BEGIN
Execution section
EXCEPTION
Exception section
END;
33 TCS INTERNAL
What is a Function
A function is a named PL/SQL Block which is similar to a
procedure.
The major difference between a procedure and a function is,
a function must always return a value,
but a procedure may not return a value but will
produce an output.
Also mentioned as Stored Function or simply Func.
34 TCS INTERNAL
How to create a function
CREATE [OR REPLACE] FUNCTION func_name [parameters]
RETURN return_datatype;
IS or AS
Declaration section
BEGIN
Execution section ;
Return return_variable;
EXCEPTION
Exception section
Return return_variable;
END;
Return Datatype: The header section defines the return type of the function. The return datatype can be
any of the oracle datatype like varchar, number etc.
35 TCS INTERNAL
Advantages:
Performance – Multiple SQL statements can be executed together
Productivity - By designing applications around a common set of stored
procedures/functions, you can avoid redundant coding and increase
your productivity.
Maintainability - Once it is validated, a stored procedure/function can be
used with confidence in any number of applications.
Security - For example, you can allow access to a procedure that
updates a database table, but deny access to the table itself.
Replication - Procedures can be replicated (copied) from one database
to another
TCS INTERNAL
THANK YOU
TCS INTERNAL
