sql学习-2

游标

create procedure proSearchItem1(@empNo char(5),@status tinyint output)
as
begin
  declare @empName char(10)
  declare @prod_id char(5),@qty int,@price decimal(7,2)
  declare @itemMoney decimal(10,2),@totMoney decimal(10,2)
  create table #myDemo
    (prod_id char(5),
     qty     int,
     unitPrice decimal(7,2),
     itemMoney decimal(10,2))
  select @empName=emp_name from employee
     where emp_no=@empNo
  if @@rowcount<=0
  begin
    set @status=-1
    return
  end
  select '销售员姓名：'+@empName
  select @totMoney=0
  declare mySearch cursor for
     select prod_id,qty,unit_price
     from sales a,sale_item b
     where a.sale_id=@empNo and a.order_no=b.order_no
  open mySearch
  fetch mySearch into @prod_id,@qty,@price
 
  while @@fetch_status=0
  begin
    select @itemMoney=@qty*@price
    insert into #myDemo values(@prod_id,@qty,@price,@itemMoney)
 
    set @totMoney=@totMoney+@itemMoney
    fetch mySearch into @prod_id,@qty,@price
  end
  close mySearch
  deallocate mySearch
  select * from #myDemo
  select '总货款：'+convert(char(10),@totMoney)
  set @status=0
end 

 

触发器

1设置一个触发器，该触发器仅允许“dbo”用户可以删除employee表内数据的，否则出错

create trigger delelteemployee1

on employee

for delete

as

begin

 if user='dbo'

       commit

else

       begin

              print 'not dbo'

              rollback

       end

end

.................

delete

from employee

where emp_no='e0017'

.................

select * from employee

drop trigger delelteemployee1

2设置一个针对employee表的触发器，当有人操作该表值时，触发器将自动将该操作者的名称和操作时间记录在日志表内，以便追踪。

create table mylog

(

    id  int not null primary key,

    uid char(10) not null,

    operateDate datetime not null,

type varchar(20) not null)

…………………………

create  trigger Del on employee

for delete

as

begin

   declare @id int,@maxID int

   select @maxID=isnull(max(id),0) from mylog

   select @maxID=@maxID+1

   insert into mylog values(@maxID,current_user,getdate(),'delete')

end

……………………….

delete from employee where emp_no='E1002'

select *  from mylog

drop trigger Del

………………………….

3针对employee表写一个DELETE触发器

create trigger delemployee

on employee

for delete

as

begin

    declare @emp_no char(5)

    declare mycur cursor for

    select emp_no from deleted

    open mycur

    fetch mycur into @emp_no  

    while @@fetch_status=0

        begin

select order_no from sales where sale_id=@emp_no

              select * from sale_item where order_no in

       (select order_no from sales where sale_id=@emp_no)

         delete from sale_item where  order_no in

              (select order_no from sales where sale_id=@emp_no)

           delete from sales where sale_id=@emp_no

            fetch mycur into @emp_no

            commit

        end

    close mycur

    deallocate mycur

end

............................

delete from employee where emp_no='e0001'

4针对employee表写一个UPDATE触发器

create trigger update_emp

on employee for update as

declare @salary1 int,@salary2 int

 If(select count(*) from employee,inserted

    Where employee.emp_no=inserted.emp_no)

             !=@@rowcount

             Rollback

If(select count(*) from employee,inserted

          Where employee.emp_no=inserted.emp_no)             >1

  begin

    print 'This operation may modify records in the table employee'

rollback

transaction

  end

 if update(emp_no)and @@rowcount=1

 Begin

        update sales

set sales.sale_id=inserted.emp_no

        From sales,deleted,inserted

        Where deleted.emp_no=sales.sale_id

end

if update(salary) 

begin

set  @salary1= (select deleted.salary from deleted )

set  @salary2= (select inserted.salary from inserted )

print 'before modifying:'

print @salary1

print 'after modifying:'

print @salary2

 end

update employee

set salary=4000

where emp_no='E19' select *from employee

存储过程

create procedure proaddemployee
 @emp_no char(5),
 @emp_name char(10),
 @sex char(1) ,
 @dept char(10),
 @title char(6),
 @date_hired datetime,
 @birthday datetime,
 @salary int,
 @addr char(50)   
 as
 insert into employee values (@emp_no,@emp_name,@sex,@dept,@title,@date_hired,@birthday,@salary,@addr)
 go
 exec proaddemployee 'e0018','王有功','m','财务部','职员','08/28/2008','08/21/1981',5000,null
 select *
 from employee
.................................................................
alter procedure proesc
 as
begin
 declare @emp_no char(5),@emp_name char(10),@ccust_id char(5), @cust_name char(20),@scust_id char(5) ,@sale_id char(5) ,@tot_amt numeric(9,2)
 declare @sum numeric(18,2),@pemp_name  char(10),@pcust_name char(20)
 declare @f int
 declare joinesc cursor for
 select emp_no,emp_name,b.cust_id,cust_name,c.cust_id,c.sale_id,tot_amt
 from employee a,customer b,sales c
 where a.emp_no=c.sale_id and b.cust_id=c.cust_id
 order by emp_no,b.cust_id
 open joinesc
 fetch joinesc into @emp_no,@emp_name,@ccust_id, @cust_name,@scust_id,@sale_id,@tot_amt
 set @pemp_name=@emp_name
 set @pcust_name=@cust_name
 set @sum=0
 set @f=0
 while @@fetch_status=0
  begin
    /*set @sum=0
   while @emp_name= @pemp_name and @cust_name= @pcust_name
   begin
   set @sum=@sum+@tot_amt
   fetch joinesc into @emp_no,@emp_name,@ccust_id, @cust_name,@scust_id,@sale_id,@tot_amt
   end
   if @sum=0
   
   begin
   set @sum=@tot_amt
   end
     select @pemp_name,@pcust_name,@sum
   set @pemp_name=@emp_name
   set @pcust_name=@cust_name
   fetch joinesc into @emp_no,@emp_name,@ccust_id, @cust_name,@scust_id,@sale_id,@tot_amt*/
   if @emp_name= @pemp_name and @cust_name= @pcust_name
    begin
    set @sum=@sum+@tot_amt
    end
   else
    begin
    set @f=1
    end
   if @f=1
    begin
    select @pemp_name,@pcust_name,@sum
    set @sum=@tot_amt
    set @pemp_name=@emp_name
    set @pcust_name=@cust_name
    set @f=0
    end
   fetch joinesc into @emp_no,@emp_name,@ccust_id, @cust_name,@scust_id,@sale_id,@tot_amt
    
  end
  
 close joinesc
        Deallocate joinesc
end
         

exec proesc
.............
alter procedure findemployee
as
select sale_id,order_no,sum(tot_amt)
from sales
group by sale_id,order_no
having sale_id in
(select emp_no
from employee
where emp_name='张三')

exec findemployee
...............................
create  procedure searchman
as
begin
 
 declare search cursor for
 select emp_no
 from employee
 where emp_name like '甘%' and title='职员'
 open search
 declare @saleid char(5)
 fetch search into @saleid
 while @@fetch_status=0
 begin 
  select sale_id,order_no,tot_amt
  from sales
  where sale_id=@saleid
    fetch search into @saleid
 end
 close search
 Deallocate search
end

exec searchman

.......................................
create procedure sumtot_amt
as
select sum(qty*unit_price)
from sale_item
group by order_no
having order_no='00001'

exec sumtot_amt
...................................