第9章TransactSQL程序设计.docx

1、第9章 TransactSQL程序设计第9章 transact-sql程序设计exec sp_addtype test_add,varchar(10),not nulldeclare int_var intselect int_var =12 /*给int_var赋值*/select int_var /*将int_var的值输出到屏幕上*/在一条语句中可以同时对几个变量进行赋值，例如，declare lastname char(8),firstname char(8),birthdate datetimeselect lastname=smith,firstname=david,birthda

2、te=1985-2-20select lastname,firstname,birthdate【例9-1】 使用select语句从customer表中检索出顾客编号为c0002的行，再将顾客的名字赋给变量customer。declare customer varchar(40),curdate datetimeselect customer=customer_name,curdate=getdate()from customerwhere customer_id=c0002【例9-2】 将sell_order表中的transporter_id列值为“t001”、goods_id列值为“g000

3、03”的order_num列的值赋给局部变量order_num。declare order_num floatupdate sell_orderset order_num=order_num*2 /*order_num为局部变量，order_num为sell_order表中的列名称*/where transporter_id=t001 and goods_id=g00003【例9-3】 计算employee表的记录数并赋值给局部变量rows。declare rows intset rows=(select count(*) from employee)select rows【例9-4】 使服务

4、器产生服务，并显示错误号。raiserror(miscellaneous error message,16,1) /*产生一个错误*/if error0select error as last error【例9-5】 捕捉例9-4中服务器产生的错误号，并显示出来。declare my_error intraiserror(miscellaneous error message,16,1)select my_error=errorif my_error0select my_error as last error【例9-6】 使用spid返回当前用户进程的id。select spid as id,

5、system_user as login name,user as user name【例9-7】 在sales数据库中创建jobs表，并在表中插入带有identity列的行，并且使用identity来显示新行中所用的identity值。 create table jobs ( job_id smallint identity (1, 1) not null , job_desc varchar (50) collate chinese_prc_ci_as not null , min_lvl tinyint not null , max_lvl tinyint not null ) on p

6、rimaryinsert into jobs(job_desc,min_lvl,max_lvl)values(accountant,12,125)select identity as identity【例9-8】 创建两个过程：innerproc和outerproc。outerproc过程用来调用innerproc过程，innerproc过程用来显示nestlevel的设置。 定义innerproc为内嵌过程。create procedure innerproc asselect nestlevel as inner levelgo 定义outerproc为外层过程。create proced

7、ure outerproc asselect nestlevel as outer levelexec innerprocgo 执行outerproc。execute outerprocgo【例9-9】 使用“+”将goods表中高于9000元的商品价格增加15元。select goods_name,unit_price,(unit_price+15) as nowpricefrom goodswhere unit_price9000【例9-10】 从表department中读取manager列的各记录的实际长度。select datalength(rtrim(manager) as data

8、length，len(rtrim(manager) as lenfrom departmentselect soundex(1),soundex(a),soundex(计算机),soundex(abc)，soundex (abcd)，soundex(a12c),soundex(a数字)select difference(red,read),difference(soundex(ac),soundex(zc),difference(soundex(abc),soundex(abcd)select charindex(,，red,white,blue) select patindex(%abc%,

9、abc123)，patindex(123,abc123) 【例9-11】 在同一表达式中使用sin、atan、rand、pi、sign函数。select sin(23.45),atan(1.234),rand(),pi(),sign(-2.34)【例9-12】 用ceiling和floor函数返回大于或等于指定值的最小整数值和小于或等于指定值的最大整数值。select ceiling(123),floor(321),ceiling(12.3),ceiling(-32.1),floor(-32.1)【例9-13】 round函数的使用。select round(12.34512,3),round

10、(12.34567,3),round(12.345,-2),round(154.321,-2)select getdate()【例9-14】 使用datediff函数来确定货物是否按时送给客户。select goods_id,datediff(dd,send_date,arrival_date)from purchase_order【例9-15】 使用datename函数返回员工的出生日期的月份（mm)名称。select employee_name,(datename(mm,birth_date)from employeeselect suser_sname(0x01)【例9-16】 使用ob

11、ject_name函数返回已知id号的对象名。select object_name(469576711)【例9-17】 利用object_id函数，根据表名返回该表的id号。select name from sysindexeswhere id=object_id(customer)【例9-18】 创建一个用户定义函数datetoquarter，将输入的日期数据转换为该日期对应的季度值。如输入2006-8-5，返回3q2006，表示2006年3季度。create function datetoquarter(dqdate datetime)returns char(6)asbeginretur

12、n(datename(q,dqdate)+q+datename(yyyy,dqdate)end【例9-19】 创建用户定义函数goodsq，返回输入商品编号的商品名称和库存量。create function goodsq(goods_id varchar(30)returns tableasreturn(select goods_name,stock_quantity from goods where goods_id =goods_id)【例9-20】 根据输入的订单编号，返回该订单对应商品的编号、名称、类别编号、类别名称。create function good_info(in_o_id

13、varchar(10)returns goodinfo table( o_id char(6), g_id char(6), g_name varchar(50), c_id char(6), c_name varchar(20)asbegindeclare g_id varchar(10),g_name varchar(30)declare c_id varchar(10),c_name varchar(30)select g_id=goods_id from sell_orderwhere order_id1=in_o_idselect g_name=goods_name,c_id=cla

14、ssification_id from goods where goods_id=g_idselect c_name=classification_name from goods_classification where c_id=classification_idinsert goodinfovalues(in_o_id,g_id,g_name,c_id,c_name)returnendselect dbo.datetoquarter (2006-8-5)select * from dbo.goodsq(g00002)select * from dbo.good_info(s00002)dr

15、op functon datetoquarter【例9-21】 显示sales数据库中customer表的编号为c0001的联系人姓名。use salesgodeclare linkman_name char(8)beginselect linkman_name=(select linkman_name from customerwhere customer_id like c0001)select linkman_nameend【例9-22】 语句块嵌套举例。declare errorcode int,nowdate datetimebeginset nowdate=getdate()ins

16、ert sell_order(order_date,send_date,arriver_date,custom_id)values(nowdate,nowdate+5,nowdate+10,c0002)select errorcode=errorif errorcode0beginraiserror(当表sell_order插入数据时发生错误!,16,1)returnendend-this is a comment.whole line will be ignored.select employee_name，address -查询所有姓钱的员工from employeewhere emplo

17、yee_name like 钱%/*this is a commnetall these lines will be ignored.*/* list all employees.*/select * from employee/* -list all employees.select * from employee*/【例9-23】 判断表goods中supplier_id为s001的商品的平均单价是否大于9799。if (select avg(unit_price) from goodswhere supplier_id=s001)$9799.0select supplier_id为s00

18、1的商品的平均单价比9799大elseselect supplier_id为s001的商品的平均单价比9799小【例9-24】 用exists确定表department中是否存在“陈晓兵”。declare lname varchar(40),msg varchar(255)select lname=陈晓兵if exists(select * from department where manager=lname)beginselect msg=有人名为+lnameselect msgendelse begin select msg=没有人名为+lname select msgend【例9-25

19、】 嵌套ifelse语句的使用。if (select sum(order_num) from sell_order)50print 他们是最佳的客户elseif (select sum(order_num) from sell_order)30print 必须与他们保持联络elseprint 再想想办法吧!【例9-26】 使用简单case函数将goods表中的商品分类重命名，以使之更易理解。selectcase classification_idwhen p001 then 笔记本计算机when p002 then 激光打印机when p003 then 喷墨打印机when p004 then

20、 交换机else 没有这种品牌end as classification，goods_name as goods name，unit_price as pricefrom goodswhere unit_price is not null【例9-27】 根据goods表中库存货物数量与订货量之差，使用case搜索函数判断该商品是否进货。select goods_name as 商品名称,casewhen stock_quantity-order_quantity3 and stock_quantity-order_quantity10 then 货物充足end as 进货判断from good

21、s【例9-28】 使用goto语句改变程序流程。declare x intselect x=1label_1：select xselect x=x+1while xy returnelsereturnwaitfor delay 00:01:00select * from gradewaitfor time 23:00backup database studentsdb to studentsdb_bkp【例9-30】 将goods表中库存数最大的商品每次订购两件，计算如此需要多少次订购才能使库存数不够一次订购。declare count int,maxstockid char(6),maxst

22、ock floatset count=0set maxstock=(select max(stock_quantity) from goods)set maxstockid=(select goods_id from goods where stock_quantity=maxstock)select maxstockid,maxstockwhile (maxstock(select order_quantity from goods where goods_id=maxstockid)beginupdate goods set order_quantity=order_quantity+2

23、where goods_id=maxstockidset count=count+1endselect count【例9-31】 对于goods表，如果平均库存少于12，while循环就将各记录库存增加5%，再判断最高库存是否少于或等于25，是则while循环重新启动并再次将各记录库存增加5%。当循环不断地将库存增加直到最高库存超过25时，然后退出while循环。在while中使用break或continue控制循环体的执行。/*执行循环，直到库存平均值超过12*/while(select avg(stock_quantity) from goods)25beginprint 库存太多了bre

24、akendelsecontinueend【例9-32】 计算s=1!+2!+10!。declare s int,n int,t int,c int/*s存储阶乘和，n 为外层循环控制变量，c为内层循环控制变量，t为n 的阶乘值*/set s=0set n=1while n=10beginset c=1set t=1while c=nbeginset t=t*cset c=c+1endset s=s+tset n=n+1endselect s,nexecute sp_executesql nselect * from sales.dbo.employee【例9-33】 创建一个视图，使用go命令

25、将create view语句与批处理中的其他语句（如use、select语句等）隔离。use salesgo -批处理结束标志create view employee_infoasselect * from employeego -create view语句与其他语句隔离select * from employee_infogo【例9-34】 使用sql-92标准的游标声明语句声明一个游标，用于访问sales数据库中的goods表的信息。use salesgodeclare goods_cursor cursorfor select * from goodsfor read only【例9-3

26、5】 为customer表定义一个全局滚动动态游标，用于访问顾客的编号、姓名、地址、电话信息。declare cur_customer cursorglobal scroll dynamicforselect customer_id,customer_name,address,telephonefrom customer【例9-36】 打开例9-35中声明的游标，读取游标中的数据。open cur_customerfetch next from cur_customer /*取第一个数据行*/while fetch_status = 0 /* 检查fetch_status是否还有数据可取*/b

27、eginfetch next from cur_customerendclose cur_customerdeallocate cur_customer【例9-37】 定义游标cur_customer，通过cur_customer更新customer表中的customer_name和linkman_name列。declare cur_customer cursorforselect * from customerfor update of customer_name,linkman_name /*该两列可更新*/open cur_customer /*打开cur_customer游标*/fet

28、ch next from cur_customer /*将第一行数据放入缓冲区，以便更新操作*/update customerset customer_name=南方体育用品公司,linkman_name=李强where current of cur_customerclose cur_customer /*关闭cur_customer游标*/ delete from customerwhere current of cur_customerdeclare cur_var cursor /*定义游标变量*/declare cur_customer cursorfor select * from customer /*定义游标*/set cur_var=cur_customer /*设置游标与游标变量的关联*/declare cur_var cursor /*定义游标变量*/set cur_var=cursor scroll keyset forselect * from customer /*创建游标并与游标变量的关联*/