数据库编程-游标和存储过程

实验内容与步骤

1、利用游标查找所有女业务员的基本情况。

declare @employeeNo char(8) , @employeeName varchar(10), @sex char(1)

declare @birthday datetime , @address varchar(50) , @telephone varchar(20)

declare @hireDate datetime , @department varchar(30), @headShip varchar(10),

declare @salary numeric(8,2)

DECLARE curEmployee CURSOR FOR

SELECT *

FROM Employee

WHERE sex='F' and department='业务科'

OPEN curEmployee

FETCH curEmployee INTO @employeeNo,@employeeName ,@sex ,@birthday , @address ,@telephone ,@hireDate,@department , @headShip ,@salary WHILE(@@FETCH_STA TUS=0)

BEGIN

SELECT @employeeNo,@employeeName ,@sex , @birthday ,

@address ,@telephone ,@hireDate , @department , @headShip , @salary FETCH curEmployee INTO @employeeNo,@employeeName ,@sex ,@birthday , @address ,@telephone ,@hireDate,@department , @headShip ,@salary END

CLOSE curEmployee

DEALLOCATE curEmployee

2、利用存储过程查找“刘刚”的员工编号、订单编号、销售金额。

create procedure proSearchByName @emp_name char(10)as

select a.emp_name,a.emp_no,b.order_no,b.tot_amt

from employee a,sales b

where a.emp_no=b.sale_id and a.emp_name =@emp_name

3、利用存储过程查找姓“李”并且职称为“职员”的员工的员工编号、订单编号、销售金额。

首先将销售主表的金额清0

update sales set tot_amt=0

将销售明细表的值填入到销售主表中

update sales set tot_amt=ordSum

from (select order_no,sum(qty*unit_price) ordSum

      from sale_item

      group by order_no) a

where sales.order_no=a.order_no

create procedure proSearchEmployee

as

begin

  declare @qty smallint,@price numeric(7,2),@sum numeric(10,2)

  declare @orderNo int

  declare @empName char(10),@oldEmpNo char(5),@newEmpNo char(5)

  /*查找员工的定单记录主表*/

  declare empCur cursor for

    select a.emp_name,a.emp_no,b.order_no

    from employee a,sales b

where a.emp_no=b.sale_id and a.emp_name like '李%'

and a.title='职员'

    order by emp_no

  open empCur

  fetch empCur into @empName,@oldEmpNo,@orderNo  

  while (@@fetch_status=0)

  begin

    set @sum=0

set @newEmpNo=@oldEmpNo

/*将定单明细表数据进行汇总*/

    declare myCur cursor for

      select qty,unit_price

      from sale_item

      where order_no=@orderNo

    open myCur

    fetch myCur into @qty,@price

    while (@@fetch_status=0)

    begin

      set @sum=@sum+@qty*@price

      fetch myCur into @qty,@price

    end

    close myCur

    deallocate myCur

    select @empName 销售员, @orderNo 定单编号,@sum  订单总金额

    fetch empCur into @empName,@oldEmpNo,@orderNo

    if @oldEmpNO<>@newEmpNo

    begin     

      set @newEmpNo=@oldEmpNo

    end

  end

  close empCur

  deallocate empCur

end

4、利用存储过程计算出订单编号为指定值的订单的销售金额。 （带一输入参数和一输出参数） （提示：sales表中的tot_amt应该等于sale_item 表中的同一张订单的不同销售产品的qty*unit_price之和）

create procedure proTotamt @order_no char(5) as

select order_no,sum(qty*unit_price)

from sale_item

where order_no=@order_no

group by order_no

create procedure proTotamt @order_no char(5)

as

begin

  declare @qty smallint,@price numeric(7,2),@sum numeric(10,2)

  /*查找定单的客户和销售员*/

  select cust_name,emp_name,order_date

  from employee a,customer b,sales c

  where order_no=@order_no and b.cust_id=c.cust_id

and emp_no=sale_id

/*定义游标*/

  declare myCur cursor for

    select qty,unit_price

    from sale_item

    where order_no=@order_no

  open myCur

  fetch myCur into @qty,@price

  set @sum=0

  while (@@fetch_status=0)

  begin

    set @sum=@sum+@qty*@price

    fetch myCur into @qty,@price

  end

  close myCur

  deallocate myCur

  select @order_no 定单号, @sum  订单总金额

End

5、对于XSCJ数据库，请计算指定学号学生的加权平均分，并将其返回。（如学生选修一门课多次，取最高分）

DELIMITER $

CREATE FUNCTION func_updateAvgWGrade(sname CHAR(20))

RETURNS FLOAT

BEGIN

      

       DECLARE sno CHAR(9);

    DECLARE avg_weight_grade FLOAT;

       SELECT student.sno INTO sno

       FROM student

       WHERE student.sname = sname;

    /*计算加权平均成绩*/

       SELECT SUM(grade*Ccredit) / SUM(Ccredit) INTO avg_weight_grade

       FROM SC,course

       WHERE SC.sno = sno AND SC.cno = course.cno;

 

    INSERT INTO grade VALUES (sno, avg_grade, avg_weight_grade)

    ON DUPLICATE KEY UPDATE grade.avg_grade = avg_grade, grade.avg_weight_grade = avg_weight_grade;

    /*返回结果*/

    RETURN avg_weight_grade;

END$

DELIMITER ;