《游标、存储过程和触发器.docx》由会员分享，可在线阅读，更多相关《游标、存储过程和触发器.docx（3页珍藏版）》请在三一文库上搜索。
1、-利用游标选取也无可员工的编号、姓名、性别、部门和薪水字段，并以格式化的方式输出由表中的信息 declare emp_no char(8,emp_name ,sex char(1,dept char(4 declare salary numeric(8,2,text char(100 declare scroll emp_cur cursor for select employeeNo,employeeName,sex,department,salary from employee where department=lk务科order by employeeNO SELECT TEXT=业务科
2、员工情况列表= print text select text=编号 姓名 性别 部门 薪水printtext select text= print text open emp_cur fetch emp_cur into emp_no,emp_name,sex,dept,salary while(fetch_status=0 begin select text=emp_no+ +emp_name+ +sex+ +dept+ +convert(char(10,salary print text fetch emp_cur fetch emp_cur intoemp_no,emp_name,sex
3、,dept,salary end close emp_curO游标 */ deallocate emp_cur/*释放游标*/ -带输入参数的存储过程：统计某业务员的销售总 金额并返回其结果-创建存储过程create procedure sales_tot3 ( E_no char(8, p_tot int output as select p_tot=sum(orderSum from OrderMaster where salerNo=E_no -秋行存储过程 declare tot_amt int exec sales_tot3 E2008003,tot_amt output selec
4、确售总额=tot_amt -食用油表和循环语句编写一 个存储过程emp_top,根据业务员姓名，查询该业务员在销售工作中的-客户信息及每一位客户的销售记录，并输出该业务员的销售总金额。-创建存储过程createprocedure emp_tot( v_emp char(10, v_emp_name char(10 as begin declare sv_emp_name varchar(10,v_custname varchar(10,p_tot int declare sum int,count int,order_no varchar(10 set sum=0,count=0 declar
5、e get_tot cursor for select employeeName,CustomerNo,b.OrederNo,oRDERsUM FROM Employee a,OrderMaster b where a.Employee=v_emp_name and a.EmployeeNo=b.SalerNo open get_tot fetch get_tot into sv_emp_name,v_custnaem,order_no,p_tot while(fetch_status=0 begin select# 务员=sv_emp_name户=v_custname,订 单编号=order
6、_no,订单金额=p_tot select sum=sum+p_tot selectcount=count+1 fetch get_tot into sv_emp_name,v_custname,order_no,p_totend close get_tot deallocate get_tot if count=0 select 0 else seleCt 务员销售总金额 二sum end go -执行存储过程exec emp_tot张小娟-插入触发器：当用户像表插入数据是，触发器自动将该操作这的名称和操作时间记录在一张表 内，以便追踪 -建立追踪表 create table TreceEm
7、ployee( userid char(10 not nul, OperateDate datetime not null, OperateType char(10 not null, Constraint traceemployeepk primary key(uiser,OperateDate 建立触发器 create trigger emploteeinsert on Employee for insert as if exist(select * from Inserted Inserted into traceemployee values(user,getdate(,INSERT执
8、行 insert employee values(E20050030,喻人杰，M,会计,科长,19970415,19610206,8000南京市青海 路,13879106120-实当6 (2)创建一个游标，主航线是表 customer的记录，要求 按客户右边+ -珞户名称珞户地址+- +-客户电话+-珞户 邮编 +- declare customID int,customerNamevarchar(10,address varchar(50,phone varchar(10,zip varchar(50,text char(100 declare emp_cur cursor for sele
9、ct customerNo,customerName,address,telephone,zip from Customer order by asc open emp_cur fetch emp_cur into customID,customerName,address,phone,zip while(fetch_status=0 begin select 16乂1=客户编号+ +customID+客户姓名+客户地址+address+客户电 话+ +phone+客户由B编+ +zip print text fetch emp_cur into customID,customerName,a
10、ddress,phone,zip end close emp_cur deallocate emp_cur - (6)利用存储过程，给employee表添加一条业务部门员工信息 create procedure output( as select customerName,orderSum,employeeName from Customer a,OrderMaster b,Employee c where a.customerNo=b.customerNo and b.salerNo=c.employeeNo -(9请使用游标和讯坏语句编写一个存储过程，根据客户 编号，查询该客户的名称-、地
11、址以及所由于该客户有关的销售记录，销售记录按 商 品分组输出。create procedure proSearchCustomer ( customNo int as begin declare customerName varchar(50,address varchar(50,orderNo char(12,productNochar(9,quantity int,price numeric(7,2 delcare get_tot cursor for slelectcustomerNmae,address,orderNo,productNo,quantity,price from Cus
12、tomer a,OrderDetail b,OrderMaster c where b.orderNo=c.orderNo and a.customerNo=c.customerNo group by productNo open get_tot fetch get_tot intocustomerName,address,orderNo,productNo,quantity,pricewhile(fetch_status=0 begin selectcustomerName,address,orderNo,productNo,quantity,price fetch get_tot into customerName,address,orderNo,productNo,quantity,price end close get_tot deallocate get_tot