![Page 1: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/1.jpg)
PL/SQL
![Page 2: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/2.jpg)
What is PL/SQL Procedural Language – SQL An extension to SQL with design features
of programming languages (procedural and object oriented)
PL/SQL and Java are both supported as internal host languages within Oracle products.
![Page 3: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/3.jpg)
Why PL/SQL Acts as host language for stored procedures
and triggers. Provides the ability to add middle tier business
logic to client/server applications. Provides Portability of code from one
environment to another Improves performance of multi-query
transactions. Provides error handling Full-featured programming language Interpreted language
![Page 4: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/4.jpg)
![Page 5: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/5.jpg)
PL/SQL BLOCK STRUCTUREDECLARE (optional)
- variable declarationsBEGIN (required)
- SQL statements - PL/SQL statements or sub-blocksEXCEPTION (optional)
- actions to perform when errors occurEND; (required)
Comments Enclosed within /* and */ for several lines’ comments -- for single line comments
![Page 6: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/6.jpg)
6
PL/SQL Variables and Data Types
Variable names must follow the Oracle naming standard (Example: current_s_id, not $current_s_id)
Strongly typed language All variables must be declared before their use. The assignment statement : = is not the same as the
equality operator = All statements end with a ;
Variable declaration syntax:variable_name data_type_declaration;
Default value always NULL
![Page 7: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/7.jpg)
Variable Naming Conventions Two variables can have the same name
if they are in different blocks (bad idea) The variable name should not be the
same as any table column names used in the block.
![Page 8: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/8.jpg)
PL/SQL Variables1. Scalar (char, varchar2, number, date,
etc)2. Composite (%rowtype)3. Reference (pointers)4. LOB (large objects)
![Page 9: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/9.jpg)
9
1. Scalar Variables Reference single value such as number, date, string Data types correspond to Oracle 10g database data
types VARCHAR2 CHAR DATE NUMBER
![Page 10: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/10.jpg)
Assigning a value to a variable
Assigning directlyvar_emp_id:= ‘E101’var_salary := 85100;
Assigning from the database, using SELECT querySELECT emp_salary
INTO var_salary FROM employee WHERE emp_id = var_emp_id;
![Page 11: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/11.jpg)
11
2. Composite Variables
Data object made up of multiple individual data elements
Data structure contains multiple scalar variables Composite variable data types include:
RECORD (multiple scalar values similar to a table’s record) TABLE (tabular structure with multiple columns and rows) VARRAY (variable-sized array. Tabular structure that can
expand or contract based on data values)
ARRAY
![Page 12: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/12.jpg)
Placeholders Placeholders are temporary storage
area. PL/SQL Placeholders can be any of
Variables, Constants and Records. Oracle defines placeholders to store data
temporarily, which are used to manipulate data during the execution of a PL SQL block.
![Page 13: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/13.jpg)
PL/SQL placeholders Scalar type
variable constant
Single composite/vector type Record
Used for reading rows from table Can contain multiple types of data
Collections Associative Array Variable-sized Array (VARRAY) Nested Tables
TYPE T_TIME IS RECORD (minutes INTEGER, hours NUMBER(2));current_time_rec T_TIME;Current_time_rec.hours := 12;
![Page 14: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/14.jpg)
PL/SQL placeholders DECLARE
TYPE T_POPULATION IS TABLE OF NUMBER INDEX BY VARCHAR2(64);l_city_population T_POPULATION;l_i number;
BEGINl_city_population('Smallville') := 2000;l_i:= l_city_population('Smallville') ;
END; /
![Page 15: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/15.jpg)
PL/SQL placeholders DECLARE
TYPE T_FOURSOME IS VARRAY(4) OF VARCHAR2(15); l_team T_FOURSOME := T_FOURSOME('John', 'Mary', 'Alberto');
BEGIN l_team.EXTEND; -- Append one null element
l_team(4):='Mike'; -- Set 5th element element DBMS_OUTPUT.PUT_LINE( l_team( l_team.first ) ); -- Print first element
DBMS_OUTPUT.PUT_LINE( l_team( l_team.last ) ); -- Print last element
END; /
![Page 16: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/16.jpg)
PL/SQL placeholders DECLARE
TYPE T_ROSTER IS TABLE OF VARCHAR2(15);l_names T_ROSTER := T_ROSTER('D Caruso', 'J Hamil', 'D Piro', 'R Singh');
l_i number;BEGIN
FOR l_i IN l_names.FIRST .. L_names.LAST LOOP --For first to last element DBMS_OUTPUT.PUT_LINE(l_names(l_i)); END LOOP; END; /
![Page 17: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/17.jpg)
17
3. Reference Variables
Directly reference specific database column or row Assume data type of associated column or row
%TYPE data declaration syntax: variable_name tablename.fieldname%TYPE; %ROWTYPE data declaration syntax: variable_name tablename%ROWTYPE;
![Page 18: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/18.jpg)
Attributes %TYPE & %ROWTYPE %TYPE references type of a variable or a
database column %ROWTYPE references type of a record
structure, table row or a cursor Advantages:
Actual type does not need to be known referenced type had changed -> will be
recompiled automatically
![Page 19: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/19.jpg)
%TYPE & %ROWTYPE Examples
balance NUMBER(7,2);
minimum_balance balance%TYPE := 10.00;
my_dname scott.dept.dname%TYPE;
dept_rec dept%ROWTYPE;
SELECT deptno, dname, loc INTO dept_rec FROM dept WHERE deptno = 30;
variable declarations
using record variable to read a row from a table
![Page 20: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/20.jpg)
20
Writing a PL/SQL Program
1. Write PL/SQL program in Notepad or another text editor
2. Copy and paste program commands from text editor into SQL*Plus
3. Press Enter after last program command
4. Type front slash ( / )5. Then press Enter again
![Page 21: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/21.jpg)
PL/SQL Sample Program
![Page 22: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/22.jpg)
PL/SQL Sample ProgramDECLARE g_inv_value number(2);v_price number(8,2) := 10.25;
v_quantity number(8,0) := 400;BEGINg_inv_value := v_price * v_quantity;
END;/Print g_inv_value/
![Page 23: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/23.jpg)
PL/SQL Sample ProgramSet serveroutput onDECLARE v_inv_value number(10,2);v_price number(8,2) := 10.25;
v_quantity number(8,0) := 400;BEGINv_inv_value := v_price * v_quantity;
dbms_output.put('The value is: '); dbms_output.put_line(v_inv_value);END;/
![Page 24: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/24.jpg)
PL/SQL Comments
DECLARE v_salary number(9,2) := 40000;BEGIN /* this is a multi-line comment that will be ignored by the pl/sql interpreter */ v_salary := v_salary * 2; -- nice raiseEND; -- end of program
![Page 25: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/25.jpg)
CONDITIONAL STATEMENTS IN PL/SQL
![Page 26: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/26.jpg)
PL/SQL Control Structures Conditional Control
Using IF and CASE statementsDECLARE
l_grade CHAR(1) := 'B'; BEGIN
CASE l_grade WHEN 'A' THEN
DBMS_OUTPUT.PUT_LINE('Excellent'); WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('Very
Good'); WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('Good'); WHEN 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair'); WHEN 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor'); ELSE DBMS_OUTPUT.PUT_LINE('No such grade');
END CASE; END;
DECLARE l_sales NUMBER(8,2) := 20000; l_bonus NUMBER(6,2);
BEGIN IF l_sales > 50000 THEN l_bonus := 1500; ELSIF l_sales > 35000 THEN l_bonus := 500;
ELSE l_bonus := 100; END IF; UPDATE employees SET salary = salary + l_bonus;
END;
![Page 27: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/27.jpg)
PL/SQL Control Structures Iterative loops
Simple loop (infinite) WHILE loop FOR loop
Numeric range Reversed
Cursor based
DECLARE
l_i NUMBER := 0;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE (TO_CHAR(l_i));
l_i:=l_i+1;
END LOOP;
WHILE l_i < 10 LOOP
DBMS_OUTPUT.PUT_LINE (TO_CHAR(l_i));
l_i := l_i + 1;
END LOOP;
FOR l_i IN 1..500 LOOP
DBMS_OUTPUT.PUT_LINE (TO_CHAR(l_i));
END LOOP;
FOR l_i IN REVERSE 1..3 LOOP DBMS_OUTPUT.PUT_LINE (TO_CHAR(l_i));
END LOOP;
END;
![Page 28: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/28.jpg)
PL/SQL Control Structures
Oracle Tutorials: PL/SQL
Iterative loops Named loops
Exiting loops EXIT statement
Loop skipping CONTINUE
DECLARE
l_i NUMBER := 0;
l_j NUMBER := 0;
l_s NUMBER :=0;
BEGIN
<<outer_loop>>LOOP
l_i := l_i + 1;
<<inner_loop>>LOOP
l_j := l_j + 1;
l_s := l_s + l_i * l_j;
EXIT inner_loop WHEN (l_j > 5);
EXIT outer_loop WHEN ((l_i * l_j) > 15);
END LOOP inner_loop;DBMS_OUTPUT.PUT_LINE('Sum:'||TO_CHAR(l_s));
IF l_s > 100 THEN EXIT;
END IF;
END LOOP outer_loop;END;
![Page 29: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/29.jpg)
Accessing Data in the Database Selecting at most one row:
SELECT INTO statement
Selecting Multiple rows: Cursors
Inserting and updating
SELECT COUNT(*) INTO variable FROM table;SELECT * INTO record FROM table WHERE …;
INSERT INTO table VALUES (var1, var2, …);
![Page 30: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/30.jpg)
Remember the SELECT INTO…… ?
It only allowed the retrieval of one rowSelect attribute into variable from … where …
Or
Select count(*) into variable from ………
But when we want to retrieve multiple rows we need to use what is called a CURSOR
![Page 31: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/31.jpg)
CursorsTo process an SQL statement, ORACLE
needs to create an area of memory known as the context area
![Page 32: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/32.jpg)
Cursors A cursor is a temporary work area created in
the system memory when a SQL statement is executed
A cursor contains information on a select statement and the rows of data accessed by it.
This area is used to store the data retrieved from the database, and manipulate this data. A cursor can hold more than one row, but can process only one row at a time.
The set of rows the cursor holds is called the active set.
![Page 33: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/33.jpg)
Cursor structure Declare it
This is achieved by a SELECT command And by giving the CURSOR a name
Open it Fetch row(s) from it Close it
![Page 34: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/34.jpg)
34
Declaring the Cursor
DECLARECURSOR low_pay
IS SELECT surname,salaryFROM Personnelwhere salary < 12000;
v_surname personnel.surname%TYPE;v_salary personnel.salary%TYPE;BEGIN…..
Because a cursor is associated with multiple rows they are normally used with LOOP structures
![Page 35: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/35.jpg)
Types Of Cursors
An IMPLICIT cursor is automatically declared by Oracle every time an SQL statement is executed.
The user will not be aware of this happening and will not be able to control or process the information in an implicit cursor
An EXPLICIT cursor is defined by the program for any query that returns more than one row of data.
They must be created when you are executing a SELECT statement that returns more than one row. Even though the cursor stores multiple records, only one record can be processed at a time, which is called as current row. When you fetch a row the current row position moves to next row.
![Page 36: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/36.jpg)
Implicit Cursors The cursor attributes available are %FOUND, %NOTFOUND,
%ROWCOUNT, and %ISOPEN.
![Page 37: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/37.jpg)
Example-Implicit CursorDECLARE var rows number(5);BEGINUPDATE employee SET salary = salary + 1000;IF SQL%NOTFOUND THENdbms_output.put_line('None of the salaries were updated');ELSIF SQL%FOUND THENvar_rows := SQL%ROWCOUNT;dbms_output.put_line('Salaries for ' || var_rows || 'employees are updated');END IF; END;
Implicit cursor
![Page 38: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/38.jpg)
Explicit Cursor An explicit cursor is defined in the
declaration section of the PL/SQL Block. It is created on a SELECT Statement
which returns more than one row.
![Page 39: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/39.jpg)
CURSOR <cursor_name> IS <select_statement>;
cursor_name – A suitable name for the cursor.
select_statement – A select query which returns multiple rows.
![Page 40: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/40.jpg)
How to use Explicit Cursor?
Four steps 1. DECLARE the cursor in the declaration section.2. OPEN the cursor in the Execution Section.3. FETCH the data from cursor into PL/SQL variables or
records in the Execution Section.4. CLOSE the cursor in the Execution Section before you end
the PL/SQL Block.
![Page 41: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/41.jpg)
![Page 42: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/42.jpg)
SyntaxDECLARE
variables;records;declare a cursor;
BEGIN OPEN cursor;FETCH cursor;
process the records;CLOSE cursor;
END;
![Page 43: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/43.jpg)
Example DECLARE emp_rec emp_tbl%rowtype; CURSOR emp_cur IS SELECT * FROM employee WHERE salary > 10; BEGIN OPEN emp_cur; FETCH emp_cur INTO emp_rec;
dbms_output.put_line (emp_rec.first_name || ' ' || emp_rec.last_name); CLOSE emp_cur;
END;
![Page 44: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/44.jpg)
![Page 45: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/45.jpg)
Using explicit cursors with loop
DECLARE CURSOR emp_cur IS SELECT first_name, last_name, salary FROM emp_tbl;
emp_rec emp_cur%rowtype; BEGIN
IF NOT sales_cur%ISOPEN THEN OPEN sales_cur;
END IF; LOOP
FETCH emp_cur INTO emp_rec; EXIT WHEN emp_cur%NOTFOUND;
dbms_output.put_line(emp_cur.first_name || ' ' ||emp_cur.last_name || ' ' ||emp_cur.salary); END LOOP;
END;
![Page 46: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/46.jpg)
Procedures
![Page 47: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/47.jpg)
A subprogram is a program unit/module that performs a particular task. These subprograms are combined to form larger programs. This is basically called the 'Modular design'. A subprogram can be invoked by another subprogram or program which is called the calling program.
A subprogram can be created: At schema level Inside a package Inside a PL/SQL block
![Page 48: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/48.jpg)
PL/SQL subprograms are named PL/SQL blocks that can be invoked with a set of parameters. PL/SQL provides two kinds of subprograms: Functions: these subprograms return a single
value, mainly used to compute and return a value.
Procedures: these subprograms do not return a value directly, mainly used to perform an action.
![Page 49: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/49.jpg)
Creating a Procedure
A procedure is created with the CREATE OR REPLACE PROCEDURE statement.
CREATE [OR REPLACE] PROCEDURE procedure_name [(parameter_name [IN | OUT | IN OUT] type [, ...])] {IS | AS} BEGIN < procedure_body > END procedure_name;
![Page 50: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/50.jpg)
Where, procedure-name specifies the name of the procedure. [OR REPLACE] option allows modifying an existing
procedure. The optional parameter list contains name, mode and
types of the parameters. IN represents that value will be passed from outside and OUT represents that this parameter will be used to return a value outside of the procedure.
procedure-body contains the executable part. The AS keyword is used instead of the IS keyword for
creating a standalone procedure.
![Page 51: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/51.jpg)
Example-1 CREATE OR REPLACE PROCEDURE
greetings AS BEGIN dbms_output.put_line('Hello World!'); END; /
![Page 52: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/52.jpg)
Executing a Standalone Procedure A standalone procedure can be called in
two ways: Using the EXECUTE keyword
EXECUTE greetings; Calling the name of the procedure from a
PL/SQL blockBEGIN greetings; END; /
![Page 53: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/53.jpg)
Deleting a Standalone Procedure DROP PROcedure
BEGIN DROP PROCEDURE greetings; END;
![Page 54: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/54.jpg)
Parameter Modes in PL/SQL Subprograms1. IN-parameters - These types of parameters are
used to send values to stored procedures.2. OUT-parameters - These types of parameters are
used to get values from stored procedures.3. IN OUT-parameters - This is similar to a return type
in functions. These types of parameters are used to send values and get values from stored procedures.
NOTE: If a parameter is not explicitly defined a parameter type, then by default it is an IN type parameter.
![Page 55: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/55.jpg)
![Page 56: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/56.jpg)
![Page 57: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/57.jpg)
Functions
![Page 58: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/58.jpg)
Creating a function CREATE [OR REPLACE] FUNCTION
function_name [(parameter_name [IN | OUT | IN OUT] type [, ...])] RETURN return_datatype
{IS | AS} BEGIN < function_body > END [function_name];
![Page 59: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/59.jpg)
function-name specifies the name of the function. [OR REPLACE] option allows modifying an existing function. The optional parameter list contains name, mode and types of the
parameters. IN represents that value will be passed from outside and OUT represents that this parameter will be used to return a value outside of the procedure.
The function must contain a return statement. RETURN clause specifies that data type you are going to return from the
function. function-body contains the executable part. The AS keyword is used instead of the IS keyword for creating a
standalone function.
![Page 60: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/60.jpg)
CREATE OR REPLACE FUNCTION totalCustomers RETURN number IS total number(2) := 0; BEGIN SELECT count(*) into total FROM customers; RETURN total; END; /
It returns the total number of CUSTOMERS in the customers table.
![Page 61: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/61.jpg)
Calling a functionDECLARE c number(2); BEGIN c := totalCustomers(); dbms_output.put_line('Total no. of
Customers: ' || c); END; /Output:Total no. of Customers: 6 PL/SQL procedure successfully completed.
![Page 62: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/62.jpg)
![Page 63: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/63.jpg)
How to execute a PL/SQL Function?1) Since a function returns a value we can assign it to a variable.employee_name := employer_details_func(); If ‘employee_name’ is of datatype varchar we can store the name of the
employee by assigning the return type of the function to it. 2) As a part of a SELECT statementSELECT employer_details_func FROM dual; 3) In a PL/SQL Statements like,dbms_output.put_line(employer_details_func); This line displays the value returned by the function.
![Page 64: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/64.jpg)
Procedures vs Functions
![Page 65: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/65.jpg)
TriggersTriggers are stored programs, which are automatically executed or
fired when some events occcur. Executed in response to any of the following events:
A database manipulation (DML) statement (DELETE, INSERT, or UPDATE). A database definition (DDL) statement (CREATE, ALTER, or DROP). A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or
SHUTDOWN).
Note: Triggers could be defined on the table, view, schema, or database with which the event is associated.
![Page 66: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/66.jpg)
Triggers can be written for the following purposes: Generating some derived column values automatically Enforcing referential integrity Event logging and storing information on table access Auditing Synchronous replication of tables Imposing security authorizations Preventing invalid transactions
![Page 67: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/67.jpg)
Creating Triggers
![Page 68: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/68.jpg)
CREATE [OR REPLACE] TRIGGER trigger_name: Creates or replaces an existing trigger with thetrigger_name.
{BEFORE | AFTER | INSTEAD OF} : This specifies when the trigger would be executed. The INSTEAD OF clause is used for creating trigger on a view.
{INSERT [OR] | UPDATE [OR] | DELETE}: This specifies the DML operation. [OF col_name]: This specifies the column name that would be updated. [ON table_name]: This specifies the name of the table associated with the trigger. [REFERENCING OLD AS o NEW AS n]: This allows you to refer new and old values for
various DML statements, like INSERT, UPDATE, and DELETE. [FOR EACH ROW]: This specifies a row level trigger, i.e., the trigger would be
executed for each row being affected. Otherwise the trigger will execute just once when the SQL statement is executed, which is called a table level trigger.
WHEN (condition): This provides a condition for rows for which the trigger would fire. This clause is valid only for row level triggers.
![Page 69: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/69.jpg)
Example
![Page 70: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/70.jpg)
Triggering a Trigger
![Page 71: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/71.jpg)
![Page 72: PL/SQL. What is PL/SQL Procedural Language – SQL An extension to SQL with design features of programming languages (procedural and object oriented)](https://reader035.vdocuments.us/reader035/viewer/2022062219/5a4d1af47f8b9ab059980162/html5/thumbnails/72.jpg)
References http://www.plsql-tutorial.com http://www.java2s.com/Tutorial/Oracle/ http://www.tutorialspoint.com/plsql