数据库编程语法基本格式Word文档下载推荐.docx
《数据库编程语法基本格式Word文档下载推荐.docx》由会员分享,可在线阅读,更多相关《数据库编程语法基本格式Word文档下载推荐.docx(11页珍藏版)》请在冰点文库上搜索。
End$$
2)set
Set变量名=表达式值;
Seta=(selectsnofromstudentwheresno=15121);
Createprocedurep1()
Declareaint;
Seta=(selectgradefromscorewheresno=15121andcno=1);
Selecta;
课堂练习:
1)建表T(S1,S2);
CreatetableT
(S1int,
S2int);
2)定义一个存储过程,声明两个变量a,b,并且设置a,b的初始值为5,将a的值插入表T的S1列,并且当S1>
=b,求出S1*A的结果;
Createprocedurep2()
Begin
Declarea,bintdefault5;
Insertintot(s1)values(a);
Selects1*afromTwheres1>
=b;
3)条件语句
语法:
IF条件判断
THEN执行语句
ELSEIF条件判断
ELSE执行语句
ENDIF;
输入一个数,对num进行判断,
如果num=-1;
往表T中插入数据(num,666);
1往表T中插入数据(num,num+1)
2往表T中插入数据(num,num+2)
其他往表T中插入数据(num,num*5)
Createprocedurep3(innumint)
Declaretempint;
DeletefromT;
InsertintoT(s1)values(num);
Ifnum=-1thenupdateTsets2=666;
Elseifnum=0thenupdateTsets2=s1+1;
ElseupdateTsets2=s1*5;
Endif;
Select*fromT;
课堂练习2
输入参数为0打印学生信息表,按照sno的升序;
输入参数为1,打印学生信息表,按照SNO的降序;
如果是其他,告诉用户,你的输入有误。
A、case语句
CASE变量
WHEN值then语句
ELSE语句
Endcase
Createprocedurep4(innumint)
CASEnum
When0thenselect*fromstudentorderbysnoasc;
When1thenselect*fromstudentorderbysnodesc;
Elseselect’inputerror’;
ENDCASE;
END$$
//JAVA的API,调用存储过程
输入参数
输出参数
DEMO
4)循环
WhileENDWHILE
标准语法:
WHILE条件判断
DO语句;
ENDWHILE;
Declaretempintdefault1;
Whiletemp<
=100do
Settemp=temp+1;
Selecttemp;
Endwhile;
End;
REPEATENDREPEAT
LOOPENDLOOP
LEAVE
第二次课:
补充:
ITERATE语句
跳出循环语句,跳出本次循环,然后直接进入下一个循环。
语法形式
Iteratelabel
课堂习题
在teacher表上创建名为teacher_info1的存储过程,要求:
teacher_info1有3个参数。
输入参数为teacher_id和type,输出参数为info。
存储过程的作用是根据编号teacher_id来查询teacher表中的记录。
如果type的值为1时,将姓名name传给输出参数info;
如果type的值为2时,将年龄传给输出参数info;
如果type的值为其他值,则返回字符串”Error”。
SETA=(SELECTcount(*)fromstudent);
5光标(游标)
查询语句可能查询出多条记录,在存储过程和函数中使用光标/游标来逐条读取查询结果集中的记录。
光标的使用包括声明光标、打开光标、使用光标和关闭光标。
光标必须声明光标、打开光标、使用光标和关闭光标。
光标必须声明在处理程序之前,并且声明在变量和条件之后。
1声明光标
Declarecursor_namecursorforselect_statement;
Cursor_name:
光标名称
Select_statement:
select语句的内容
Declarecur_employeecursorforselectname,agefromemployee;
2打开光标
Opencursor_name
Opencur_employee;
3使用光标
Mysql中使用fetch关键字来使用光标,语法形式
Fetchcur_nameintovar_name[,var_name…];
Cur_name表示光标的名称
Var_name表示将光标中的select语句查询出来的信息存入该参数。
Var_name必须在声明光标前就定义好。
Fetchcur_employeeintoemp_name,emp_age;
4关闭光标
Closecursor_name;
Closecur_employee;
每个光标不再需要时都应该被关闭,使用close语句将会释放光标所使用的全部资源。
在一个光标被关闭后,如果没有重新被打开,则不能被使用。
对于声明过的光标,则不需要再次声明,可直接使用open语句打开。
第二十四章使用游标
MySQL5添加了对游标的支持
只能用于存储过程
由前几章可知,mysql检索操作返回一组称为结果集的行。
都与mysql语句匹配的行(0行或多行),使用简单的SELECT语句,没有办法得到第一行、下一行或前10行,也不存在每次行地处理所有行的简单方法(相对于成批处理他们)
有时,需要在检索出来的行中前进或后退一行或多行。
这就是使用游标的原因。
游标(cursor)是一个存储在MYSQL服务器上的数据库查询,它不是一条SELECT语句,而是被该语句检索出来的结果集。
在存储了游标之后,应用程序可以根据需要滚动或浏览其中的数据。
游标主要用于交互式应用,其中用户需要滚动屏幕上的数据,并对数据进行浏览或做出更改。
使用游标
使用游标涉及几个明确的步骤:
1在能够使用游标前,必须声明(定义)它,这个过程实际上没有检索数据,它只是定义要使用的SELECT语句
2一旦声明后,必须打开游标以供使用。
这个过程用钱吗定义的SELECT语句吧数据实际检索出来
3对于填有数据的游标,根据需要取出(检索)的各行
4在接受游标使用时,必须关闭它如果不明确关闭游标,MySQL将会在到达END语句时自动关闭它
创建游标
游标可用DECLARE语句创建。
DECLARE命名游标,并定义相应的SELECT语句。
根据需要选择带有WHERE和其他子句。
如:
下面第一名为ordernumbers的游标,使用了检索所有订单的SELECT语句
CREATEPROCEDUREprocessorders()
BEGIN
DECLAREordernumbersCURSOR
FOR
SELECTorder_numFROMorders;
END;
存储过程处理完成后,游标就消失,因为它局限于存储过程
打开和关闭游标
DECLAREordernumbersCURSOR
Openordernumbers;
Closeordernumbers;
//CLOSE释放游标使用的所有内部内存和资源,因此,每个游标不需要时都应该关闭
使用游标数据
在一个游标被打开后,可以使用FETCH语句分别访问它的每一行。
FETCH指定检索什么数据(所需的要列),检索出来的数据存储在什么地方。
它还向前移动游标中的内部行指针,使下一条FETCH语句检索下一行,相当于PHP中的each()函数
循环检索数据,从第一行到最后一行
BEGIN--声明局部变量
DECLAREdoneBOOLEANDEFAULT0;
DECLAREoINT;
--当SQLSTATE为02000时设置done值为1
DECLARECONTINUEHANDLERFORSQLSTATE'
02000'
SETdone=1;
--打开游标
--开始循环
REPEAT
--把当前行的值赋给声明的局部变量o中
FETCHordernumbersINTOo;
--当done为真时停止循环
UNTILdoneENDREPEAT;
--关闭游标
语句中定义了CONTINUEHANDLER,它是在条件出现时被执行的代码。
这里,它指出当SQLSTATE'
出现时,SETdone=1。
SQLSTATE'
是一个未找到条件,当REPEAT没有更多的行供循环时,出现这个条件。
DECLARE语句次序用DECLARE语句定义局部变量必须在定义任意游标或句柄之前定义,而句柄必须在游标之后定义。
不遵守此规则就会出错
重复和循环除这里使用REPEAT语句外,MySQL还支持循环语句,它可用来重复执行代码,直到使用LEAVE语句手动退出为止。
通常REPEAT语句的语法使它更适合于对游标进行的循环。
为了把这些内容组织起来,这次吧取出的数据进行某种实际的处理
--声明局部变量
DECLAREdoneBOOLEANDEFAULT0;
DECLAREtDECIMAL(8,2)
DECLAREordernumbersCURSOR
DECLARECONTINUEHANDLERFORSQLSTATE'
--创建一个ordertotals的表
CREATETABLEIFNOTEXISTSordertotals(order_numINT,totalDECIMAL(8,2))
--用上文讲到的ordertotal存储过程并传入参数,返回营业税计算后的合计传给t变量
CALLordertotal(o,1,t)
--把订单号和合计插入到新建的ordertotals表中
INSERTINTOordertotals(order_num,total)VALUES(o,t);
最后SELECT*FROMordertotals就能查看结果了
课堂练习:
1、游标练习:
1)创建员工工资表,包含,员工编号,员工姓名,员工工资,员工住房公积金(8%),员工社保(5%),员工所缴税(区间),员工实发工资。
2)税区间:
0~4999免征税
5000~79993%
8000~1199910%
12000~2999925%
30000~以上40%
创建函数实现输入员工工资,返回员工缴纳的税费。
3)创建存储过程,计算员工的住房公积金,员工社保,使用函数计算员工所缴纳的税费,以及员工实际到手工资,并插入数据库表。
2、JAVA与存储过程
使用MYSQL存储过程,完成用户账号和密码的验证。
若输入账号和密码验证正确,则返回1;
若输入账号和密码错误,返回0;
JAVA对返回的数据进行判断,并打印。
6、存储过程与存储函数一样,都是由sql语句和过程式语句所组成的代码片段,并且可以被应用程序和其他sql语句调用。
区别:
存储函数不能拥有输出参数,因为存储函数自身就是输出参数;
而存储过程可以拥有输出参数。
可以直接对存储函数进行调用,而不需要使用call语句;
而对存储过程的调用,需要使用call语句。
存储函数中必须包含一条return语句,而这条特殊的sql语句不允许包含于存储过程中。
MySQL中,创建存储函数的基本形式如下:
CREATEFUNCTIONsp_name([func_parameter[,...]])
RETURNStype
[characteristic...]routine_body
Return子句用于声明存储函数返回值的数据类型。
示例
在数据库中创建一个存储函数,要求该函数能根据给定的id返回客户的性别,如果数据库中没有给定的id,则返回没有该客户。
调用存储函数
Selectsp_name([func_parameter…])
Selectfn_search
(2);
删除存储函数drop
修改存储函数alter修改存储函数的某些相关特征。
先来一个简单的,创建一个函数将'
2009-06-2300:
00:
00'
这样格式的datetime时间转化为‘2009年6月23日0时0分0秒’这样的格式:
DELIMITER
$$
DROP
FUNCTION
IF
EXISTS
`sp_test`.`getdate`$$
CREATEFUNCTION
getdate(gdatedatetime)
RETURNS
varchar(255)
BEGIN
DECLARE
xVARCHAR(255)DEFAULT'
'
;
SETx=date_format(gdate,'
%Y年%m月%d日%h时%i分%s秒'
);
RETURN
x;
END
解析:
第一句是定义一个结束标识符,因为MySQL默认是以分号作为SQL语句的结束符的,而函数体内部要用到分号,所以会跟默认的SQL结束符发生冲突,所以需要先定义一个其他的符号作为SQL的结束符;
第二句是如果这个函数已经存在了,就删除掉,sp_test是数据库的名字,函数是跟数据库相关联的,getdate是函数的名字;
第三句是创建一个函数,()里是参数的名字和类型,RETURNS定义这个函数返回值的类型;
函数体必须放在BEGINEND之间;
DECLARE是定义函数体的变量,这里定义一个变量x,默认是空,然后SET给x变量赋值;
RETURN是返回值,这里把变量x返回,x的类型必须与第三句中定义的返回类型一致。
调用:
SELECTgetdate('
返回:
2009年06月23日00时00分00秒'
课堂习题:
在food表上创建名为food_price_count的存储过程。
存储过程有3个参数,输入参数为price_info1和price_info2,输出参数为count。
存储过程的作用是查询food表中食品单价高于price_info1且低于price_info2的食品种数,然后由count参数来输出。
并且计算满足条件的单价的总和。