Block Structure Of Pl/SQL
A “SUB BLOCK” in PL/SQL is a nested block of code within a larger PL/SQL block that helps in organizing complex scripts. This structure allows for better readability and maintainability of the code. By using sub-blocks, developers can compartmentalize logic, making debugging and testing more efficient. Additionally, sub-blocks support exception handling specific to their operations, enhancing error management. Implementing sub-blocks in PL/SQL ensures robust, modular, and scalable database applications.
1.SQL Stored Procedure
2.Stored Functions
3.Packages
4.Triggers
What is Stored Procedure in SQL:
A Stored Procedure Is A Database Object Which Contains Precompiled Queries. Stored Procedures Are A Block Of Code Designed To Perform A Task Whenever We Called And May Be Or May Not Be Return A Value.
Why We Need SQL Procedure:
Whenever We Want To Execute A SQL Query From An Application The Sql Query Will Be First Parsed (I.e. Complied) For Execution Where The Process Of Parsing Is Time Consuming Because Parsing Occurs Each And Every Time, We Execute The Query Or Statement.
To Overcome The Above Problem, We Write Sql Statements Or Query Under Stored Procedure And Execute, Because A Stored Procedure Is A Pre-complied Block Of Code Without Parsing The Statements Gets Executed Whenever The Procedures Are Called Which Can Increase The Performance Of An Application.
Advantages Of Stored Procedure:
•As There Is No Unnecessary Compilation Of Queries, This Will Reduce Burden On Database.
•Application Performance Will Be Improved
•User Will Get Quick Response
•Code Reusability & Security.
SQL Procedure Syntax:
Create Or Replace Procedure <procedure_name>
[ Parameter Name [mode Type] Datatype,….]
Is
<variable Declaration>;
Begin
<exec Statements>;
[ Exception Block
<exec-statements>;]
End;
How to Execute Procedure in Sql
Syntax1:
Execute / Exec <procedure_name>;
Syntax2:(Anonymous Block)
Begin
<procedure_name>;
End;ements>;]
End;
Examples On Store Procedure Without Paramaters:
Ex1:
Create Or Replace Procedure My_proc
Is
Begin
Dbms_output.put_line (‘welcome To Procedures….’);
End My_proc;
How to Execute Procedure in SQL
Syntax1:
Ex: Exec My_proc;
Syntax2:
Ex: Begin
My_proc;
End;
Ex2: Write A Procedure To Display Sum Of Two Numbers.
Create Or Replace Procedure Add_proc
Is
A Number: =10;
B Number: =20;
Begin
Dbms_output.put_line (‘sum Of Two Numbers = ‘||(A+b));
End Add_proc;
Examples On Procedures With Parameters:
Ex3:
Create Or Replace Procedure Add_proc (A Number, B Number)
Is
Begin
Dbms_output.put_line (‘sum Of Two Numbers = ‘||(A+b));
End Add_proc;
To Execute Above Store Procedure:
Exec Add_proc (10,60);
Exec Add_proc (&a, &b);
Ex4: Write A Procedure To Accept Employee Number And Display Corresponding Employee Net Salary.
Create Or Replace Procedure Emp_proc (Tempno Emp.empno%type)
Is
Tsal Emp.sal%type;
Tcomm Emp.comm%type;
Netsal Number;
Comm_null Exception;
Begin
Select Sal, Comm Into Tsal, Tcomm From Emp Where Empno=tempno;
If Tcomm Is Null Then
Raise Comm_null;
End If;
Netsal: =tsal+tcomm;
Dbms_output.put_line (‘given Employee Net Salary = ‘||netsal);
Exception
When Comm_null Then
Raise_application_error (-20001,’given Employee Is Not Getting Commission.’);
When No_data_found Then
Raise_application_error (-20002, ‘such Employee Number Is Not Exist.’);
End Emp_proc;
SQL Procedure Return Values Through Parameter Modes:
There Are Three Types Of Parameters Modes.
In -> It Accepts Input Into Stored Procedure(Default)
Out -> It Returns Output Through Stored Procedure
In Out -> Both Accepting And Also Return.
Ex. On “in” Parameters:
Ex5:
Create Or Replace Procedure Add_proc (A In Number, B In Number)
Is
Begin
Dbms_output.put_line (‘sum Of Two Numbers = ‘||(A+b));
End Add_proc;
Exec Add_proc (90,30);
Ex6:
Create A Sp To Input Empno And Display That Employee Name, Sal From Emp Table?
Sql> Create Or Replace Procedure Sp1(P_empno In Number)
Is
V_ename Varchar2(10);
V_sal Number (10);
Begin
Select Ename, Sal Into V_ename, V_sal From Emp Where Empno=p_empno;
Dbms_output.put_line(V_ename||’,’||v_sal);
End;
/
Procedure Created.
Sql> Execute Sp1(7788);
Scott,3000
Ex On “out” Parameters:
Ex7:
Sql> Create Or Replace Procedure Sp2(X In Number, Y Out Number)
Is
Begin
Y: =x*x*x;
End;
/
Procedure Created.
Sql> Execute Sp2(5);
Error At Line 1:
Ora-06550: Line 1, Column 7:
Pls-00306: Wrong Number Or Types Of Arguments In Call To ‘sp2’.
Note: To Overcome The Above Problem Then We Follow The Following 3 Steps,
Step1: Declare Referenced /bind Variable For “Out” Parameters In SQL Store Procedure:
Syntax:
Var[iable] <ref.variable Name> <dt>[size];
Step2: To Add A Referenced /bind Variable To A Procedure in SQL:
Syntax:
Execute <pname> (Value1, Value2, ……:<ref.variable Name>….);
Step3: Print Referenced Variables:
Syntax:
Print <ref.variable Name>;
Execution Plan Of “Out” Parameters In Sql Store Procedure:
Sql> Var Ry Number;
Sql> Execute Sp2(5,:ry);
Pl/sql Procedure Successfully Completed.
Sql> Print Ry;
Ry
———-
125
Ex8:
Create A SQL Store Procedure To Input Empno As A “In” Parameter And Returns That Employee Provident Fund, Professional Tax At 10%,20% On Basic Salary By Using “Out” Parameters?
Sql> Create Or Replace Procedure Sp3(P_empno In Number, Pf Out Number, Pt Out Number)
Is
V_sal Number (10);
Begin
Select Sal Into V_sal From Emp Where Empno=p_empno;
Pf: = V_sal*0.1;
Pt: = V_sal*0.2;
End;
/
Procedure Created.
Sql> Var Rpf Number;
Sql> Var Rpt Number;
Sql> Execute Sp3(7788,:rpf,:rpt);
Pl/sql Procedure Successfully Completed.
Sql> Print Rpf Rpt;
Ex9:
Create Or Replace Procedure Add_proc (A In Number, B In Number, C Out Number)
Is
Begin
C: =a+b;
End Add_proc;
Output:
Var R Number;
Execute Add_proc (10,20,:r);
Print R;
Ex. On “in Out” Parameters:
Ex10:
Sql> Create Or Replace Procedure Sp4(X In Out Number)
As
Begin
X: = X*x;
End;
/
Procedure Created.
Sql> Execute Sp4(5);
Error At Line 1:
Ora-06550: Line 1, Column 11:
Pls-00363: Expression ‘5’ Cannot Be Used As An Assignment Target.
Note: To Overcome The Above Problem Then We Follow The Following 4 Steps,
Step1: Declare Referenced Variable For “Out” Parameters In SQL Store Procedure:
Syntax:
Var[iable] <ref.variable Name> <dt>[size];
Step2: Assign A Value To Referenced Variable:
Syntax:
Execute <ref.variable Name> := <value>;
Step3: To Add A Referenced Variable To A SQL Stored Procedure:
Syntax:
Execute <pname> (:<ref.variable Name>……);
Step4: Print Referenced Variables:
Syntax:
Print <ref.variable Name>;
Output:
Sql> Var Rx Number;
Sql> Execute :rx := 10;
Sql> Execute Sp4(:rx);
Sql> Print Rx;
Note: All sql stored procedure Names Are Stored In User_objects. Select Object_name From User_objects;
Ex:
Select Object_name From User_objects Where Object_type=’procedure’;
Note: Procedure Bodies Are Stored In User_source.
Ex:
Select Text From User_source Where Name=’emp_proc’;
Dropping Procedures:
Syntax:
Sql> Drop Procedure <procedure_name>;
Ex: Drop Procedure My_proc;
Difference Between Store Procedure Functions and Triggers
Store Procedure
Procedures do not return a value, but they can modify data or perform actions.
Procedures can insert, update, or delete data in tables.
Procedures can take input parameters, which are values passed to the procedure when it is executed.
Procedures can return output parameters, which are values returned by the procedure after execution.
Functions
Functions return a value, which can be used in queries or other procedures.
Functions cannot modify data in tables, but they can perform calculations or data transformations.
Functions can take input parameters, which are values passed to the function when it is executed.
Functions do not return output parameters, but they can return a single value.
Triggers
Triggers are automatically executed in response to a specific event, such as inserting, updating, or deleting data.
Triggers do not take input parameters, as they are triggered by an event rather than being explicitly called.
Triggers can modify data in tables, but they are typically used to enforce business rules or maintain data integrity.
Triggers do not return a value, as they are executed in response to an event rather than being called as a function.
Key Differences Between Store Procedure and Functions
Procedures do not return a value, while functions return a value.
Procedures can modify data, while functions cannot.
Procedures are typically used for data manipulation or complex operations, while functions are used for calculations or data transformations.
For complete SQL tutorial please click here
Practice SQL online please click
