| by Achyut Kendre | No comments

Functions, Stored Procedures and Triggers in T-PL/SQL

PL SQL

SQL Server Functions are useful objects in SQL Server databases. A SQL Server function is a code snippet that can be executed on a SQL Server.These are the database objects consist of code block of code that can be executed indecently and can be used again and again.

Functions in T -PL/SQL always returns a single value or table. When function returns a single value we call it as scalar function and when function returns a table we call it as table valued function.

Two types of functions –

  1. User Defined
  2. Built in Functions

Functions can be used in following things –

  • In Transact-SQL statements such as SELECT
  • In applications calling the function
  • In the definition of another user-defined function
  • To parameterize a view or improve the functionality of an indexed view
  • To define a column in a table
  • To define a CHECK constraint on a column
  • To replace a stored procedure

User Define Functions can be created using following syntax –

Create Function FunctionName(paramters) returns return data type
  as
   Begin
       Statements
   End

e. g. PL SQL functions to add two numbers.

Create function AddNum(@a as int,@b as int) returns int
as
Begin
  return @a+@b
End

Calling Function –

Begin
Declare @c int
set @c=dbo.addnum(12,23)
print 'Value is' + convert(varchar,@c)
End

e.g. Function to find student name using rollno.

Declaration:-
alter function getroll(@r as int)returns varchar(30)
as
begin
declare @nm varchar(30)
select @nm=sname from student where sno=@r
return @nm
end

Calling:-
begin
declare @sn varchar(30)
set @sn=dbo.getroll(4)
print 'Student Name' + convert(varchar,@sn)
end

Stored Procedures in T PL SQL: –

These are the precompiled code block, stored in a database as object, which can be used again and again. Why stored procedures are popular?

  • Self executable code block (can be called from font end application)
  • Stored as a database object in database.
  • Compiled once and executed again and again.
  • Maintains internal transaction.
  • Avoid SQL Injections (More Secure as compare to Plain SQL Query)

To create the stored procedure we use the following syntax –

Create procedure procedurename(parameters)
as
Begin
  Statements
End

Where parameters can be three types –
In: – it is default type, means when do not specify any parameter type, it will be by default in used for getting the value of parameter inside the procedure.
Out: – it is used for generating value from stored procedure indirectly we can say stored procedure can return multiple values using multiple out type of parameters.


To execute the stored procedure we use the execute command with procedure name and while sending the parameters we do not use ().

e. g.  Create Procedure to add two numbers.

Create proc procaddnum(@a as int,@b as int)
as 
Begin
 print 'Addition is ' + convert(varchar,(@a+@b))
End
Exec procaddnum 12,23
e.g. 
create proc procsq(@num as int,@sq as int out)
as
begin
 set @sq= @num * @num
end

begin
declare @s int
exec procsq 12,@s out
print 'Squre is:' + convert(varchar,@s)
end

e.g.
Create proc incnum(@n as int out)
as
Begin
 set @n=@n + 10
End

Begin
Declare @v int
set @v=12
exec incnum @v out
print 'Value is' + convert(varchar,@v)
end

e.g.

Create procedure which accept the employeeid and display the total salary of that employee.

Create procedure which will display the employeenames and total salary from specified department

alter proc procacc(@empid as int)
as
begin
declare @empn varchar(30)
declare @bs as bigint
declare @hr as bigint
declare @t1 as bigint
declare @d1 as bigint
declare @de as bigint
declare @gros as bigint
select  @empn=emp_name,@bs=Basic_sal,@hr=hra,@t1=ta,@d1=da,@de=deduc from emptbl inner join empsaltbl on  emptbl.emp_id=empsaltbl.emp_id where  emptbl.emp_id=@empid set @gros = (@bs+@hr+@t1+@d1)-@de
print 'empname'+ @empn + 'grosssal'+ convert(varchar,@gros)
end

Begin
exec procacc 1
End

Triggers: –

These are the code blocks will be executed automatically when you perform any DML operation on the table. To create the triggers we use –

Create/Alter trigger triggername
on 
tablename
 for insert/update/delete
as
begin
   Code
end

Trigger provides us three temporary tables

inserted :- this holds temp vaules inserted by insert query.

deleted :- this holds temp record deleted using deleted query

updated :- this holds temp records updated using updated query.

e. g. Create a trigger to create the backup of the product if user delete the product from product table.

Create trigger bkup
on 
producttbl
for delete
as
begin
 declare @pid bigint
 declare @pn varchar(30)
select @pid=productid,@pn=productname from deleted
insert into productbktbl values(@pid,@pn)
end

e.g.
alter trigger t1
on emp
for insert
as 
begin
declare @di as bigint
declare @esa as bigint

select @esa=esal,@di=did from emp inserted
if((select count(did) from dept1 where did=@di)=0)
begin
insert into dept1 values(@di,@esa) 
end
else
begin
update dept1 set dsal=dsal+@esa where dept1.did=@di
end
end

e. g. Assume following table structure and write the trigger –

ProductTbl
 ProductId  ProductName  		ProductStockTbl   productstockid  
productid
stockqty  		SalesTbl  
Salesid
 custname
productid qty  

Create trigger on salestbl for insert in which check the available stock before insert , if stock is not available give message insufficient stock otherwise update product stock by sub sales qty from stock qty.

 create trigger sttrig
  on salestbl
  for insert
  as
  begin
    declare @st bigint
    delcare @sst bigint
    declare @pid bigint
    select @pid= productid,@st=qty from inserted
    select @sst=stockqty from productstocktbl where productid=@pid
    if @sst <=@st
      begin
            update productstocktbl set stockqty=stockqty - @st  where productid = @pid
       end
   else
         begin
             print 'Insucfff sotck'
        end
 end
« Prev