Day 14

Quiz

1:Name the six navigation operations that are possible with cursors.
A1: The six operations are NEXT, PRIOR, FIRST, LAST, ABSOLUTE, and RELATIVE.
2:Can a cursor select data from joined tables?
A2: Yes, a cursor can select data from a joined table. In addition, it can be declared for update as well. However, when updating a cursor like this, only the columns in the table listed in the UPDATE statements are affected. You can update columns in all the tables, but you must do it in separate UPDATE statements.
3:Name the six steps required to use a cursor.
A3: The six steps are
1.
Declare cursor variables.

2.
Declare a cursor and associate it with a SELECT statement.

3.
Open the cursor.

4.
Fetch a row of data from the cursor keyset, and then loop to process the entire keyset.

5.
Close the cursor.

6.
Deallocate the cursor.

Exercise

1:Create a cursor on the Employees table in the Northwind database. Read in each employee's last name, birth date, and hire date. If an employee is more than 40 years old, then print his last name, and hire date. 
use northwind
declare crs_employees cursor
for
select lastname, birthdate, hiredate
from employees
order by lastname
open crs_employees
Declare @Emp_Name varchar(20), @Birth_Date datetime,
        @Hire_Date datetime, @tempstr varchar(50),
    @age int
print 'Employee Name         Birth Date  Age  Hire Date'
print '--------------------- ----------  ---  ---------'
fetch next from crs_employees into @Emp_Name, @Birth_Date,
        @Hire_Date
while @@fetch_status = 0 begin
set @age = datediff(yy,@birth_date,getdate())
if @age > 40
    set @tempstr = convert(char(22),@Emp_Name) +
    convert(char(13), @birth_date,101) +
    convert(char(4), @age) +
    convert(char(10), @hire_date,101)
print @tempstr
fetch next from crs_employees into @Emp_Name, @Birth_Date,
        @Hire_Date
end
close crs_employees
deallocate crs_employees

A1: Results: 
Employee Name         Birth Date  Age  Hire Date
--------------------- ----------  ---  ---------
Buchanan              03/04/1955   45  10/17/1993
Callahan              01/09/1958   42  03/05/1994
Davolio               12/08/1948   52  05/01/1992
Davolio               12/08/1948   52  05/01/1992
Fuller                02/19/1952   48  08/14/1992
Fuller                02/19/1952   48  08/14/1992
Fuller                02/19/1952   48  08/14/1992
Peacock               09/19/1937   63  05/03/1993
Peacock               09/19/1937   63  05/03/1993

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset