PLSQL
- PL/SQL stands for Procedural Language extension of SQL.
- PL/SQL is a combination of SQL along with the procedural features of programming languages.
- It was developed by Oracle Corporation in the early 90ís to enhance the capabilities of SQL.
The PL/SQL Engine:
Oracle uses a PL/SQL engine to processes the PL/SQL statements.
A PL/SQL language code can be stored in the client system (client-side) or in the database (server-side).
PLSQL BLOCKS
Plsql program should be developed in blocks.
Plsql blocks are 2 types
1. Ananymous block
2. Named Blocks / Stored programs (Procedure/Functions/Package)- Ananymous block : A block which has no name, and it will not be stored in the database
Plsql Block Ananymous block
DECLARE
Variable declaration
Cursor declaration
collections
records
BEGIN
statement-1
statement-2
EXCEPTIOn
Handling Errors;
END;
- Named Or Labeled Block (Procedure/Functions/Package) :
A block which has a name (or) name to the block.
This block will be available in the database for re-use.
Plsql named block (procedure/function)
Create or replace proceudre is
Variable declaration
Cursor declaration
collections
records
BEGIN
statement-1
statement-2
EXCEPTION
Handling Errors
END;
/
Declare : It is used to declare variables, cursors, records etc.,
It is optional and not mandatory
Begin : It is used to execute statement like conditions,loops, dml,ddl operations etc.,
It is mandatory
Exception : It is used to handle errors when select/update/delete statements failed to execute
It is optional and not mandatory
End : It is used to end the ananymous block or sub program
It is mandatory
sIMPLE PLSQL PROGRAMS using dbms_output.put_line:
dbms_output : IT is a oracle supplied package
put_line : It is procedure inside the dbms_output package
dbms_output.put_line: it is used to print values of variable or hardcoded values
Syntax: : BEGIN
dbms_output.put_line (variable1||variable2||’value’);
END;
EXAMPLES *
- Print a string or statement
SQL>ed c:\plsql\filename.sql
— Program to print text
SET SERVEROUT ON
BEGIN
dbms_output.put_line (‘Welcome to plsql program’);
END;
/
Save and close the file
SQL> @ c:\plsql\filename.sql; — Output will be displayed
Welcome to plsql program
— Program to assign 2 numbers and find their sum
SQL> ed filename.sql
/* program to
find sum of
2 numbers */
declare
a number := 10;
b number := 20;
c number;
begin
c:=a+b; — adding 2 numbers
dbms_output.put_line (c);
dbms_output.put_line ('Sum is '||c);
dbms_output.put_line ('a='||a||' b='||b||' c='||c);end;
/
SQL> @ filename.sql
30
Sum is 30
a=10 b=20 c=30
Task 1: Develop a program to print below values
Oaktree Hyderabad
Oaktree Secunderabad
task 2: declare variable to assign any 3 numbers and find the sum
Task 3: Declare a variable to assign the sal,
then print the anual salary and tax with your own interest
Output
Ename is xyz
Sal is 5000
Anual sal is 60000
Tax 1000