| by Achyut Kendre | No comments

T-PL/SQL in SQL Server

Difference between SQL and PL/SQL - Talent Economy

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

Variables:

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 –

                  print ‘Message’

Convert:  

Convert function allow you to convert from one data type to another data type can be used as follows –

              convert(datatype,value)

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

Conditional Statements:-

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.

Only if:-

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

Looping :-

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
« Prev