TSQL PROGRAMMING
TSQL (Transact SQL) Programming is an Procedural Language Extension to SQL which is known as PL/SQL in Oracle.Tsql blocks are of two types
Anonymous block
Named blocks
Anonymous block
They are unnamed block of code for execution which can be written at a point where they are to be executed.They can be written on a Query Window and execute
Named block
Where the program blocks are given a name for identification. These will be stored on database which provides reusability of code.
Declare Statement
It is used to declare a variable
Syntax
Declare @varname datatype
Eg:
Declare @x int
Declare @s varchar(30), @d datetime
Set statement
It is used to assign values to a variables
Set @varname=value
Set @x=100
Note: to declare a variable In Tsql programming you have to use the keyword declare , using this keyword you can declare all the variable in a single line and you cannot break @declaration statemen into multiple lines.
To declare variable in multiple line you must repeat the keyword declare in every line
Using the keyword set you can assign a value to only one variable and you cannot assign value to multiple variables .To do this separate with comma( ,)
Printing output:
A tsql programming to print output to the output window, you have to use the statement “Print” that has the following syntax
Print ‘output’
Print @varname
Creating block of statement
To treat set of statement with one block , enclose those statements in the keyword begin and end
Write a program to print sum of two integers
begin
declare @a int,@b int,@s int
set @a=50
set @b=60
set @s=@a+@b
print 'sum is ' +cast(@s as varchar)
end
data base programming
to do sql programming sql statement must be embed in TSQL block
all dml statement
all tcl statement
all drl command
in tsql programming , it is not possible to take inputs into variables directly from keyboard but we can take values from database table as input for variable and for this use the select statement with the following syntax
select @varname=<colname>,@varname2=<colname>,…. From <tablename> [where <condition>]
eg:
select @eno=empno, @ename=ename from emp where empno=7678
select @sal=sal from emp where empno=@eno
Conditional statement
If (condition)
Begin
Statement
End
If (condition)
Begin
Statement
End
Else
Begin
Statement
End
IF(Condition)
Begin
Statement
End
Else if(condition)
Begin
Statement
End
Else if(condition)
Begin
Statement
End
Else
Begin
Statement
End
Case
Syntax:
Case
When <condition1> then <value1>
When <condition2> then <value2>
Else
<Else value>
End
declare @a int,@b int,@c int
begin
set @a=55
set @b=122
set @c=22
if @a>@b and @a >@c
print 'a is bigger'
else if @b>@c
print 'b is bigger'
else
print 'c is bigger'
end
write a program to print weak name of the current system data
begin
declare @week int
set @week =DATEPART(dw,getdate())
if @week=1
print 'sunday'
else if @week=2
print 'monday'
else if @week=3
print 'tuesaday'
else if @week=4
print 'wednesaday'
else if @week=5
print 'thursaday'
else if @week=6
print 'friday'
else
print 'sat'
end
The same program can also be written in case statement as following
begin
declare @week int
set @week =DATEPART(dw,getdate())
select case @week
when 1 then 'sunday'
when 2 then 'monday'
when 3 then 'tue'
when 4 then 'wed'
when 5 then 'thu'
when 6 then 'frid'
else 'sat'
end
end
other way of writing
begin
declare @week int
set @week =DATEPART(dw,getdate())
select case
when @week=1 then 'sunday'
when @week=2 then 'monday'
when @week=3 then 'tue'
when @week=4 then 'wed'
when @week=5 then 'thu'
when @week=6 then 'frid'
else 'sat'
end
end
while loop
while(condition)
begin
statement
end
comment in TSQL
Single line of comments
(- -)
Multiple lines of comments
(/* dfasdfas */
Write a program to print 1 to 10 numbers
begin
declare @n int
set @n=1
while @n<=10
begin
print @n
set @n=@n+1
end
end
declare @x int
set @x=0
while @x<10
begin
set @x=@x+1
if @x=6
break
print @x
end
declare @x int
set @x=0
while @x<10
begin
set @x=@x+1
if @x=6 continue
print @x
end
write a program to increment salaries of employees based on job by accepting empno
president 20%
manager 25%
analyst 20%
salesman 15%
other 10%
begin
declare @eno int, @j varchar(20), @s money
set @eno=7902
select @j=job , @s=SAL FROM EMP WHERE EMPNO=@eno
if @j='PRESIDENT'
set @s=@s+@s*30/100
else if @j='manager'
set @s=@s+@s*25/100
else if @j='ANALYST'
set @s=@s+@s*20/100
else if @j='salesman'
set @s=@s+@s*15/100
else
set @s=@s+@s*10/100
update EMP set SAL=@s where EMPNO=@eno
end
NOTE:
HERE we are getting the record for only one eno, but we are not validating for all employees. To OVERCOME this we have cursors
declare
@a int,@b varchar(100)
begin
select @a=COUNT(*) from DEPT
if @a>0
begin
print 'table have record'
select @b=dname from DEPT where DEPTNO=10
print 'name of 10th department is' +@b
end
else
print 'no records'
end
Anonymous | Named |
Block without names | Blocks with name |
Are saved in operating system file | But the named blocks are saved in database |
Every time compile and execute | Named blocks are one time compilation and everytime execution |
These blocks can’t be reused | Named block are reused |
Are not secured | Are secured |
No comments:
Post a Comment
Thank you for visiting my blog