Сессия

[polecat]

Помогите сделать хотябы одын курсор?!
create table Car(
vladelec_num numeric(2)identity,
Car_num numeric(30)primary key,
Car_name varchar(30),
otdel_num numeric(30) references Otdeli_GIBDD (otdel_num),
licenz_num numeric(30) references Strachovay_Co (licenz_num),
licenz_num_STO numeric(30) references STO (licenz_num_STO),
passport_data varchar(30) references Vladelec (passport_data) )
create table Otdeli_GIBDD(
otdel_num numeric(30)primary key,
nachalnic_family varchar(30)not null,
otdel_adress varchar(30), )
create table Strachovay_Co(
Co_adress varchar(30) not null,
licenz_num numeric(30)primary key,
rod_deyateln varchar(30)not null, )
create table STO(
licenz_num_STO numeric(30)primary key,
assortiment varchar(50) not null, )
create table Vladelec(
vladelec_name varchar(30),
passport_data varchar(30)primary key,
vladelec_age numeric(2), )
insert into Otdeli_GIBDD(otdel_num,nachalnic_fa mily,otdel_adress)
values ('12', 'Петров','Гоголя,12');
insert into Otdeli_GIBDD(otdel_num,nachalnic_fa mily,otdel_adress)
values ('17', 'Иванов','Мавлютова,7');
insert into Otdeli_GIBDD(otdel_num,nachalnic_fa mily,otdel_adress)
values ('3', 'Чебышев','Учительская,12б');
insert into Strachovay_Co(Co_adress,licenz_num, rod_deyateln)
values('Большая Красная','571236178','ОСАГО');
insert into Strachovay_Co(Co_adress,licenz_num, rod_deyateln)
values('Муштари','249756232','ОСАГО ');
insert into STO(licenz_num_STO,assortiment)
values ('5743257','мытье')
insert into STO(licenz_num_STO,assortiment)
values ('9867812','кузовной ремонт');
insert into Vladelec(vladelec_name,passport_dat a,vladelec_age)
values('Васькин','12 01 713220','18');
insert into Vladelec(vladelec_name,passport_dat a,vladelec_age)
values('Фадеев', '02 01 934239','50');
insert into Vladelec(vladelec_name,passport_dat a,vladelec_age)
values('Колезнев','91 15 365230','28');
insert into Vladelec(vladelec_name,passport_dat a,vladelec_age)
values('Бобкова','67 12 721821','19');
insert into Vladelec(vladelec_name,passport_dat a,vladelec_age)
values('Путин','01 01 0000001','48');
insert into Vladelec(vladelec_name,passport_dat a,vladelec_age)
values('Вельган','06 43 5218307','23');
insert into Car(Car_num,Car_name,otdel_num,lice nz_num,licenz_num_STO,passport_data )
values('72321278','Porshe Carrera GT','12','571236178','5743257','12 01 713220');
insert into Car(Car_num,Car_name,otdel_num,lice nz_num,licenz_num_STO,passport_data )
values('91219981','Toyota MarkII','3','249756232','9867812',' 02 01 934239');
insert into Car(Car_num,Car_name,otdel_num,lice nz_num,licenz_num_STO,passport_data )
values('61208758','Toyota Crown','17','571236178','5743257',' 91 15 365230');
insert into Car(Car_num,Car_name,otdel_num,lice nz_num,licenz_num_STO,passport_data )
values('01202364','Toyota Chaser','12','249756232','9867812', '67 12 721821');
insert into Car(Car_num,Car_name,otdel_num,lice nz_num,licenz_num_STO,passport_data )
values('12303124','Nissan Atlas','3','571236178','5743257','0 1 01 0000001');
insert into Car(Car_num,Car_name,otdel_num,lice nz_num,licenz_num_STO,passport_data )
values('0231818','Nissan Terrano','17','249756232','9867812' ,'01 01 0000001');
insert into Car(Car_num,Car_name,otdel_num,lice nz_num,licenz_num_STO,passport_data )
values('1899245','Isuzu Bighorn','12','249756232','9867812' ,'06 43 5218307');
insert into Car(Car_num,Car_name,otdel_num,lice nz_num,licenz_num_STO,passport_data )
values('2898157','Toyota Carina','17','249756232','9867812', '06 43 5218307');
select *from Car;
select *from Otdeli_GIBDD;
select *from Strachovay_Co;
select *from STO;
select *from Vladelec;
---------------------------------------
//представление#1
create view view_1
AS
select vladelec_name, car_name
from vladelec v, car c
WHERE vladelec_name='Путин' and v.passport_data=c.passport_data
go
select *from view_1
----------------------------------------
----------------------------------------
//представление#2
create view view_2
AS
select distinct v.vladelec_name as "Фамилия владельца"
from Otdeli_GIBDD o,Car c, Vladelec v
where otdel_adress='Учительская,12б'
and v.passport_data=c.passport_data and o.otdel_num=c.otdel_num
go
select *from view_2
-------------------------------------------
-------------------------------------------
//триггер
create trigger tab_trigger1
on Vladelec
For INSERT
AS Print 'Вставка строк заперщена' Rollback tran

Select COUNT(*) from Vladelec

//попробуем вставить новую строку (с помощью команды INSERT) и снова посмотрим,
//сколько строк имеется в таблице
Insert into Vladelec values
('Петров','08 01 433089','27')
GO
Select count(*) from Vladelec
--------------------------------------------