PL/SQL : Procedural Language / Structual Query Language and it is an exrension to SQL.

SQL is not very flexible and it cannot be made to react differently to differing sutuations easily. In  SQL queries we normally tell database what we want but not tell it how to do it.

SQL : give commands, commands complete with ;

PL/SQL : follow the procedure, create programs (use SQL), using If conditions Else Elseif and loops (while loop, for loop, do while, control or check error, exceptions)

eg :

Declare

(to create all variables)

Begin

programs (SQL commands not DDL)

Exception

(execute programs on any error)

End;

note : if there is no need to declare variables, then there‘s no need to write Declare statement.

the minimal :

Begin

Select * from emp;

End;

write using SQL>

method 1 :

SQL>Begin

SQL> Select * from emp;

SQL>End;

SQL>

method 2 :

create a file (like notepad)

give the name as ABC.sal or ABC.pls(Procedural Language Script)

then you can run this file in SQL

PL/SQL allows you to work with the database with the ease of SQL, while giving you the power and flexibility or procedual constructs such as : Variables, Flow Control (If Else Condition), Error Handling, you can create a block of code containing several SQL statements and send them as a single request to the  database. This improves performance.

in SQL :

DDL : Data Denifition Language

eg : Create table/view/sequence, Alter table/view/sequence, Drop table/view/sequence

DML : Data Manipulation Language

eg : Insert, Update, Delete

DCL : Data Control Language

eg : create user, grant user, revoke, 

PL/SQL features

1.variables & constants : objects within PL/SQL that are used to store and manipulate values. Several data types are available, the more common ones beings, VARCHAR2, DATE and BOOLEAN.

2.SQL - All DML(Data manipulation language)type of SQL statements can be used directly whin PL/SQL

3.Flow Control : PL/SQL supports flow control statements such as IF, FOR, WHILE. These allow for conditional actions, branching and iterative(loop)control.

4.Built_in Functions : most functions that are available in SQL can ve used in a PL/SQL statemtns

5.Cursor Management : to process data returned from multiple-row queries. we can modify the data according to 

6.Block Structure : PL/SQL programs are made up of blocks of code. blocks aare used to separate code.

7.Exception Handling : PL/SQL supports an elegant method for handling exceptions (errors) within code. the programmer can define his own exceptions. eg : trying to change the value od PK.

8.Composite Type : PL/SQL allows you to create composite data types, which typically relate to a row on a database table. eg : a single variable of row type can store the data from the while table record(many columns)

9. Sored Code : PL/SQL programs can ve stored within the database in the form of packages, procedures, functions and triggers. so we can run our programs any time we have stored in database in different forms.

eg :

%type : datatype from table columns

Stud_ID Student_table.student_ID%type;

Stud_ID Number; static Datatype

Dynamic datatype (can be changed)

static datatype variable

dynamic datatype variable

eg : variable_name table_name.column_name%type

eg : Stud_ID Student_table.Student_ID%type

variable to whole record/row %rowtype

eg : variable_name table_name%rowtype

eg : Student_rec Student_table%rowtype

display :

Student_rec.Name 

Student_rec.Age

Student_rec.Student_id

variable name datatype

Student_name Varchar(20);

dynamic variable

Student_name Student_table.Name%type

(If someone changes the datatype of name in Student table, you have no need to change your PL/SQL program.)

record or row type variable

Student_row Student_table%rowtype

 Cursor --> store many columns, many records

array of %rowtype variable

Cursor --> select * from emp; (store the data inside memory)

open cursor and get the data from cursor, update the data inside cursor, copu the cursor back to table, close the cursot.

Declare

Begin

Exception

End;

PL/SQL programs

1.functions (return something)

2.procedures (similar to function (can or cannot return ))

3.packages (biggest program)

4.cursors

5.triggers

PL/SQL Fundamentals

basic PL/SQL syntax

1. free format language : no given format, we can write the programs in any way.

2. PL/SQL statements can be written in many lines.

3. keywords cannot split in many lines.

4. identifiers (variables) must start with an alpha-character, can be up to 30 characters in length and cannot be a reserved word.

5. character and date literal (data) are enclosed in single quotes. (‘‘)

6. each statement must end with a semicolon (;)

eg : Stud_name := ‘ABC‘; (to store data)

       Stud_name = ‘ABC‘; (in IF condition)

all PL/SQL programs are made up of blocks. a PL/SQL program must contain at least one block. a block is made up of PL/SQL statements enclosed within the keywords, BEGIN and END, eg :

BEGIN

INSERT INTO table (col) VALUES(‘XX‘);

END;

for a variable declaration you can also include a DECLARE section, eg :

DECLARE

v_number NUMBER;

BEGIN

v_number := 10;

END;

for exception handling use the EXCEPTION keyword, eg :

DECLARE

v_number NUMBER;

BEGIN

v_number := 10;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.put_line(‘Error‘);

END;

eg :

DECLARE

Stud_ID Number;

BEGIN

Stud_ID := 100;

DBMS_OUTPUT.put_line(‘Student ID is : ‘  | | Stud_ID);

END;

(when you execute your program, it will show you the value of Stud_ID. you have to set serveroutput on first.)

SQL> Set serveroutput on / off

SQL> Show serveroutput (display the serveroutput state)

the PL/SQL block - multiple block

a PL/SQL program can consist of several in-line blocks, eg :

DECLARE

v_number NUMBER;

BEGIN

v_number := 10;

EXCEPTION 

WHEN OTHERS THEN 

DBMS_OUTPUT.put_line(‘Error 1‘);

END;

BEGIN

v_number := 20;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.put_line(‘Error 2‘);

END;

BEGIN

v_number := 30;

EXCEPTION

WHEN OTHERS THEN

DBMS.OUTPUT.put_line(‘Error 3‘);

END;

note : the blocks with only one declare must be in one file.

PL/SQL blocks can contain nested (inside) blocks. eg :

DECLARE

BEGIN

...outer block statements

DECLATE

... inner block declarations

BEGIN

...inner block statements

END;

... outer block statements

EXCEPTION

... outer block statements

END;

note : you can write nested blocks under BEGIN and EXCEPTION but not DECLARE.

there are several types of blocks :

Anonymous block : have no name and are generally stored in a host file or entered directly into SQL *Plus and executed just once.

Named blocks : very much the same as an anonymous block except the block is given a label.

Subprograms : Packages, Procedures and Functions are blocks of codes stored within the database. these blocks are executed via a specific call via the block name.

Triggers : these are also named blocks that are stored within the database. triggers are executed implicitly whenever the triggering event occurs. (Insert, update or delete)

Goto labele

eg :

Named blocks : name is enclosed in << and >> :

<<ABC>>

DECLARE

Stud_ID Number;

BEGIN

Stud_ID := 100;

if Stud_ID >100

goto <<XYZ>>

else

DBMS_OUTPUT.put_line(‘ ‘);

END;

<<my_block>>

DECLARE

v_number NUMBER;

BEGIN

v_number := 100;

END;

Subprograms : function to square a number :

CREATE OR REPLACE FUNCTION square(p_number IN NUMBER)

IS

BEGIN

RETURN p_number *2;

END;

Triggers : these are also named blocks that fire (execute) in response to a database event. (Insert, update or delete)

CREATE OR REPLACE TRIGGER audit

BEFORE DELETE ON items

FOR EACH ROW

BEGIN

INSERT INTO audit_table(item, description)

VALUES(:old.item,:old.description);

END;

Triggers : named PL/SQL blocks

Execute or run at event : insert, update, delete.

before insert, update, delete

after insert, update, delete

before delete on emp;

delete from emp;

before update on emp;

update from emp;

after insert on emp;

scope and visibility

install virtual box

instal second

install windows xp --> start program database 11g

conn scott/tiger

select * from tab;

disc 

exit