T-PL/SQL in SQL Server
Procedural language SQL provides you small programming constructs using which we can write small small code blocks will help you to manipulate data from database. PL/SQL is a combination of SQL along with the procedural features of programming languages. It is being develop to provide the programming enhancements and capabilities to SQL.
In case of SQL server to write the pl/sql block we use keywords –
Begin SQL Statements or PL SQL Code. End
in TPLSQL we can have two types of variables –
User Defined Variables: –
These are the variables created by the user, can be created using syntax –
declare @variablesname data types
where you can use all SQL server data types.
set Keyword:- set keyword is used to write the value in the variable. We can not assign the value to variable directly, we have to use the set keyword.
e. g. set @num=12
Built in Variables: –
These variables are being provided by the SQL server we can write the value in them, but we can read the value. All built in variables will starts with @@ symbol.
e.g. declare @num int
Output Statement: –
Plsql provides us the statment print using which you can output string or varchar type of data can be used as follows –
Convert function allow you to convert from one data type to another data type can be used as follows –
It will convert the specified value to specified data type.
e. g. Write a program to do addition of two numbers using pls sql –
Begin declare @num1 int declare @num2 int declare @res int set @num1=23 set @num2=45 set @res=@num1 + @num2 print 'Addition is :' + Convert(varchar,@res) END
Imposes conditions on the execution of a Transact-SQL statement. The Transact-SQL statement that follows an IF keyword and its condition is executed if the condition is satisfied: the Boolean expression returns TRUE. The optional ELSE keyword introduces another Transact-SQL statement that is executed when the IF condition is not satisfied: the Boolean expression returns FALSE.
IF Boolean_expression Begin Statements End
Will handle only true part of the condition, begin and end is needed only if there is more than one statement.
If and Else:- IF Boolean_expression Begin sql_statement | statement_block End ELSE Begin sql_statement | statement_block End e. g. Even number program using if and else. begin declare @num int set @num=26 if(@num % 2 =0) begin print 'Even Number' end else begin print 'Odd number' end end
PLSQL provides you the while loop using which we can perform the repeat.
It can be used as follows –
while(condition) Begin Statements End
With while you can use breaks and continues statement to break and continue the loop.
e. g. Addition digits of given number. Begin Declare @num int Declare @dig int Declare @sum int Set @sum=0 Set @num=2222 while(@num <> 0) Begin set @dig= @num % 10 set @sum =@sum + @dig set @num=@num/10 End print 'Addition of Digits:' + convert(varchar,@sum) End
Fetching the table data in variables:-
In certain situation you need to fetch the values of the columns into variables of pl sql block for some kind of processing, for that we need into statement can be used as follows –
specifying variablename=fieldname in select list.
e.g. fetching value into variable example. Assume table:- CustTbl Cust_ID Cust_Name Cust_Address Cust_Phone Begin Declare @nm varchar(30) Declare @ad varchar(30) select @nm=cust_name,@ad=cust_address from custtbl where cust_id=2 print 'Customer Name :' + @nm print 'Customer Address : ' + @ad End
e. g. while loop example Assume following table – Table Name: Employee > ID >Name > Salary >Start_date >City >Region Program:- DECLARE @Counter Int SET @Counter = 1 WHILE @Counter < 4 BEGIN PRINT 'Loop' PRINT 'SubCategory'+ CONVERT(VarChar(10), @Counter) + ':' SELECT Name, ID, Salary FROM Employee WHERE ID = @Counter SET @Counter = @Counter + 1 END