SQL- Introduction to PL/SQL

Introduction toIntroduction to
PL/SQLPL/SQL

What is PL/SQLWhat 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.

Why PL/SQLWhy 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

PL/SQL BLOCKPL/SQL BLOCK
STRUCTURESTRUCTURE
DECLARE (optional)
- variable declarations
BEGIN (required)
- SQL statements
- PL/SQL statements or sub-blocks
EXCEPTION (optional)
- actions to perform when errors occur
END; (required)

PL/SQL Block TypesPL/SQL Block Types
Anonymous
DECLARE
BEGIN
-statements
EXCEPTION
END;
Procedure
PROCEDURE <name>
IS
BEGIN
-statements
EXCEPTION
END;
Function
FUNCTION <name>
RETURN <datatype>
IS
BEGIN
-statements
EXCEPTION
END;

PL/SQL Variable TypesPL/SQL Variable Types
• Scalar (char, varchar2, number, date, etc)
• Composite (%rowtype)
• Reference (pointers)
• LOB (large objects)
Note: Non PL/SQL variables include bind variables,
host (“global”) variables, and parameters.

Variable NamingVariable Naming
ConventionsConventions
• 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.

PL/SQL is strongly typedPL/SQL is strongly typed
• All variables must be declared before their use.
• The assignment statement
: =
is not the same as the equality operator
=
• All statements end with a ;

PL/SQL SamplePL/SQL Sample
ProgramProgramVariable g_inv_value number
DECLARE
v_price number(8,2) := 10.25;
v_quantity number(8,0) := 400;
BEGIN
:g_inv_value := v_price * v_quantity;
END;
/
Print g_inv_value
/

PL/SQL SamplePL/SQL Sample
ProgramProgramSet serveroutput on
DECLARE
v_inv_value number(10,2);
v_price number(8,2) := 10.25;
BEGIN
v_inv_value := v_price * v_quantity;
dbms_output.put('The value is: ');
dbms_output.put_line(v_inv_value);
END;
/

PL/SQL Sample ProgramPL/SQL Sample Program
(with user input)(with user input)
Set serveroutput on
Accept p_price Prompt 'Enter the Price: '
DECLARE
v_inv_value number(8,2);
v_price number(8,2);
BEGIN
v_price := &p_price;
v_inv_value := v_price * v_quantity;
dbms_output.put_line('******');
dbms_output.put_line('price * quantity=');
dbms_output.put_line(v_inv_value);
END;
/
Note: PL/SQL not designed for user interface programming

PL/SQL CommentsPL/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 raise
END; -- end of program

SELECT INTOSELECT INTO
SET SERVEROUTPUT ON
DECLARE
v_max_gpa number(3,2);
v_numstudents number(4);
v_lname students.lname%type;
v_major students.major%type;
BEGIN
select max(gpa) into v_max_gpa
from students;
DBMS_OUTPUT.PUT_LINE ('The highest GPA is '||v_max_gpa);
select count(sid) into v_numstudents
from students
where gpa = v_max_gpa;
IF v_numstudents > 1 then
DBMS_OUTPUT.PUT_LINE ('There are '||v_numstudents||' with that GPA');
ELSE
select lname, major into v_lname, v_major
from students
where gpa=v_max_gpa;
DBMS_OUTPUT.PUT_LINE ('The student name is '||v_lname);
DBMS_OUTPUT.PUT_LINE ('The student major is '||v_major);
END IF;
END;
/

COMMON PL/SQL STRINGCOMMON PL/SQL STRING
FUNCTIONSFUNCTIONS
• CHR(asciivalue)
• ASCII(string)
• LOWER(string)
• SUBSTR(string,start,substrlength)
• LTRIM(string)
• RTRIM(string)
• LPAD(string_to_be_padded, spaces_to_pad, |string_to_pad_with|)
• RPAD(string_to_be_padded, spaces_to_pad, |string_to_pad_with|)
• REPLACE(string, searchstring, replacestring)
• UPPER(string)
• INITCAP(string)
• LENGTH(string)

COMMON PL/SQL NUMERICCOMMON PL/SQL NUMERIC
FUNCTIONSFUNCTIONS
• ABS(value)
• ROUND(value, precision)
• MOD(value,divisor)
• SQRT(value)
• TRUNC(value,|precision|)
• LEAST(exp1, exp2…)
• GREATEST(exp1, exp2…)

%ROWTYPE%ROWTYPE
Set serveroutput on
DECLARE
v_student students%rowtype;
BEGIN
select * into v_student
from students
where sid='123456';
DBMS_OUTPUT.PUT_LINE (v_student.lname);
DBMS_OUTPUT.PUT_LINE (v_student.major);
DBMS_OUTPUT.PUT_LINE (v_student.gpa);
END;
/

CURSORSCURSORS
• A cursor is a private set of records
• An Oracle Cursor = VB recordset = JDBC ResultSet
• Implicit cursors are created for every query made in
Oracle
• Explicit cursors can be declared by a programmer
within PL/SQL.

Cursor AttributesCursor Attributes
• cursorname%ROWCOUNT Rows returned so far
• cursorname%FOUND One or more rows
retrieved
• cursorname%NOTFOUND No rows found
• Cursorname%ISOPEN Is the cursor open

Explicit Cursor ControlExplicit Cursor Control
• Declare the cursor
• Open the cursor
• Fetch a row
• Test for end of cursor
• Close the cursor
Note: there is a FOR LOOP available with an implicit fetch

Sample Cursor ProgramSample Cursor Program
DECLARE
CURSOR students_cursor IS
SELECT * from students;
v_student students_cursor%rowtype;
/* instead we could do v_student students%rowtype */
BEGIN
DBMS_OUTPUT.PUT_LINE ('******************');
OPEN students_cursor;
FETCH students_cursor into v_student;
WHILE students_cursor%found LOOP
DBMS_OUTPUT.PUT_LINE (v_student.last);
DBMS_OUTPUT.PUT_LINE (v_student.major);
FETCH students_cursor into v_student;
END LOOP;
CLOSE students_cursor;
END;
/

Sample Cursor ProgramSample Cursor Program
(same program without composite variable)(same program without composite variable)
DECLARE
CURSOR students_cursor IS
SELECT last, major from students;
v_Last students.last%type;
v_major students.major%type;
BEGIN
OPEN students_cursor;
FETCH students_cursor into v_last, v_major;
WHILE students_cursor%found LOOP
DBMS_OUTPUT.PUT_LINE (v_last);
DBMS_OUTPUT.PUT_LINE (v_major);
FETCH students_cursor into v_last, v_major;
END LOOP;
CLOSE students_cursor;
END;
/

When is PL/SQL handyWhen is PL/SQL handy
• When something is too complicated for SQL
• When conditional branching and looping are needed
• Example
• Write a PL/SQL program that assigns email address to each employee or student in a table.
Following these rules:
- email address should be all lower case
- email address should default to first initial plus the first seven letters of the last name
- email can be no longer than eight characters
- if email is already used than use first initial plus middle initial plus first
six letters of last name
- if the previous address is taken use the first two letters of the first name
and the first six letters of the last name.
- if the previous address is taken use first six letters of last name + 01 or 02 …etc

Stored ProceduresStored Procedures
• PL/SQL code stored in the database and executed when called
by the user.
• Called by procedure name from another PL/SQL block or using
EXECUTE from SQL+. For example EXEC SQR(50)
• Example:
Create procedure SQR (v_num_to_square IN number)
AS
v_answer number(10);
BEGIN
v_answer := v_num_to_square * v_num_to_square;
dbms_output.put_line(v_answer);
END;
/

FunctionFunction
• PL/SQL user defined function stored in the database and
executed when a function call is made in code: example x :=
SQUARED(50)
• Example:
Create or Replace Function SQUARED
(p_number_to_square IN number)
RETURN number
IS
v_answer number(10);
BEGIN
v_answer := p_number_to_square * p_number_to_square;
RETURN(v_answer);
END;
/

Another Stored Procedure ExampleAnother Stored Procedure Example
Create or replace procedure mytabs
AS
CURSOR table_cursor IS
Select table_name from user_tables;
v_tablename varchar2(30);
BEGIN
open table_cursor;
fetch table_cursor into v_tablename;
while table_cursor%found loop
dbms_output.put_line(v_tablename);
fetch table_cursor into v_tablename;
end loop;
close table_cursor;
END;

TriggersTriggers
• PL/SQL code executed automatically in response to a database
event, typically DML.
• Like other stored procedures, triggers are stored in the database.
• Often used to:
o enforce complex constraints, especially multi-table constraints.
Financial posting is an example of this.
o Trigger related actions
o implement auditing “logs”
o pop a sequence when creating token keys
• Triggers do not issue transaction control statements (such as
commit). Triggers are part of the SQL transaction that invoked
them.
• USER_TRIGGERS provides a data dictionary view of triggers.

Log Trigger ExampleLog Trigger Example
CREATE OR REPLACE TRIGGER LOGSTUDENTCHANGES
BEFORE INSERT OR DELETE OR UPDATE of Major ON STUDENTS
FOR EACH ROW
DECLARE
v_ChangeType CHAR(1);
v_sid varchar2(10);
BEGIN
IF INSERTING THEN
V_ChangeType := 'I';
v_sid := :new.sid;
ELSIF UPDATING THEN
V_ChangeType := 'U';
v_sid := :new.sid;
ELSE
V_ChangeType := 'D';
v_sid := :old.sid;
END IF;
INSERT INTO MAJ_AUDIT (change_type, changed_by, timestamp,
SID, old_major, new_major)
VALUES (v_ChangeType, USER, SYSDATE, v_sid, :old.major, :new.major);
END LOGSTUDENTCHANGES;

UpperCase TriggerUpperCase Trigger
ExampleExample
CREATE OR REPLACE TRIGGER UPPERCASE
BEFORE INSERT OR UPDATE ON STUDENTS
FOR EACH ROW
DECLARE
BEGIN
:new.lastname:=UPPER(:new.lastname);
:new.firstname:=UPPER(:new.firstname);
END UPPERCASE;
/

ThankThank You !!!You !!!
For More Information click below link:
Follow Us on:
http://vibranttechnologies.co.in/sql-classes-in-mumbai.html

SQL- Introduction to PL/SQL

More Related Content

What's hot

Similar to SQL- Introduction to PL/SQL

More from Vibrant Technologies & Computers

Recently uploaded

SQL- Introduction to PL/SQL