Functions, Stored Procedures and Triggers in T-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 –
- User Defined
- 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
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
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 –
|ProductStockTbl productstockid |
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