SQL Server与MySQL存储过程学习记录之二

by shinichi_wtn 2009-12-18 10:43

学习记录一中,讨论了SQL Server与MySQL各自建立存储过程的方法和过程,并简单做了一下对比。在实际应用中,查询往往是很复杂的,不可能用一简单的一行SQL语言就能满足我们的需求,这个时候,就需要使用SQL编程。

实现SQL编程有两种方式,第一种就是用高级语言进行反复调用,因为我们熟悉一门或多门高级程序语言,比如C,C++,C#,PHP,Java等等,这些都是能调用数据库操作的。比如在C#这样的面向对象编程语言里,万物皆对象,在创建一个数据库调用程序时,首先需要新建一个连接,同时建立一条SQL操作指令,然后打开连接,执行该指令,最后返回结果,关闭连接。

首先以C#调用SQL Server为例,比如我们要运行一条没有返回结果的SQL语句并赋值给sqlStr字符串变量,那么用简单的几行程序就能直接操作数据库了。(需要System.Data和System.Data.SqlClient命名空间)

SqlConnection sqlConn = new SqlConnection(connectStr);
SqlCommand sqlCmd = new SqlCommand(sqlStr, sqlConn);
sqlConn.Open();
sqlCmd.ExecuteNonQuery();
sqlConn.Close();

于是,要进行复杂的SQL操作时,只要保持数据库的连接打开,不断改变SqlCommand命令来实现不同的操作,如动态生成sqlStr。

同样的,使用C#连接MySQL数据库时,需要下载MySQL Connector NET,这是一个封装好的用来连接MySQL的类库,所有命令和连接SQL Server毫无差别,只需把Sql换成MySql即可,如下。(需要MySql.Data和MySql.Data.MySqlClient命名空间)

MySqlConnection sqlConn = new MySqlConnection(connectStr);
MySqlCommand sqlCmd = new MySqlCommand(sqlStr, sqlConn);
sqlConn.Open();
sqlCmd.ExecuteNonQuery();
sqlConn.Close();

但是如果要批量执行SQL语言,通过高级语言循环方式传入不免带来效率上的劣势,因为每次传入的SQL语句都要先编译,然后运行,同时在传送SQL命令的时候也有时间代价(尤其是远程数据库),为何不只传送一条命令,然后在数据库里进行批量执行呢?这也就是存储过程的好处,可以把存储过程看成数据库操作的一个宏,或者批处理。当然,这要数据库支持才行。高兴的是,目前的所有企业级数据库都很好的支持了SQL编程,只是实现细节有差别。下面具体谈谈SQL Server与MySQL的SQL编程方法。

【SQL Server简单编程】

该部分转自http://www.cnblogs.com/Niyowong/archive/2007/08/20/862169.html,如果要学习更高级的内容可以购买人民邮电出版社的《SQL Server 2008基础教程》

1.局部变量

声明单个局部变量

declare @num int

声明多个局部变量

declare   @FirstName nvarchar(20)
                @LastName nvarchar(20)
                @Age int

局部变量赋值
被赋值的局部变量必须是已经声明的。

a.简单赋值方法

declare @UserName varchar(10)
set @UserName = 'Niyo Wong'

b.使用select语句赋值

delcare @NoOfRecords int
set @NoOfRecords = (select count(*) from tableName)
select @NoOfRecords = 20
declare @UserName varchar(20)
declare @UserId varchar(10)
select @UserName = userName from tbl_User where userId = '123401'
select @UserId = max(UserId) from tbl_User

注意:如果查询返回了多个值时,那么只有最后一个值赋给了变量。

c.使用update语句赋值

declare @qyt tinying
update tableName set @qty = fieldName where id = '1'
注意:update无法象select语句一样魏数据提供一些常用的转换,所以在使用update进行赋值时,
最好严格匹配数据类型,否则会产生错误。

2.全局变量

下面列举几个我们在编程中常用的全局变量

a. @@CURSOR_ROWS

返回本次服务器连接中,打开游标取回的数据行的数目。如:

select @@CURSOR_ROWS
declare employee_cursor cursor for
select emplid from tbl_Employee
open employee_cursor
fetch next from employee_cursor
select @@CURSOR_ROWS
close employee_cursor
deallocate employee_cursor

b. @@ERROR

返回上一条语句返回的错误号,执行成功返回0,
一般在insert,update,delete语句之后使用(常结合事务处理)。

c. @@FETCH_STATUS

返回上一次使用游标FETCH操作所返回的状态值。返回值为0表示操作成功,
为-1表示操作失败或者已经超过了游标所能操作的数据行的范围,当到了最后一行数据后,
还要接着取下一列数据,返回-2,表示返回值已经丢失。

d. @@ROWSCOUNT

返回上一条SQL语句所影响到的数据行的数据。常用于检查操作是否达到了目的,
当执行的SQL语句不影响数据库数据时,该变量返回0

e. @@VERSION

返回版本号

3.结构语句

a.条件结构
if.... else ...如:

if((select count(*) from table1) > 0)
begin
declare @num int
set @num = (select max(no) from tabl2)
if(@num >(select count(*) from table1))
begin
print '@num >(select count(*) from table1)'
end
else
if(@num = (select count(*) from table1))
begin
   print '@num = (select count(*) from table1)'
end
end
else
begin
print 'No of record below zero'
end

b.循环结构
while 语句。如:

declare @count int
set @count = 0
while(@count < 10)
begin
print @count
set @count = @count + 1
end

在循环中常用的语句有break和continue,
break为跳出while,而continue为跳出当前循环,进入下一循环。
有时候也用到return和goto语句,下面我们将讲这两个语句。

c.case语句
case语句又叫条件分支语句。如:

select case userType
when '1' then 'admin'
when '2' then 'general user'
else 'other' end 'userType'
from tbl_user

或者

select 'userType' = case
when USERiD = '1' then 'admin'
when userName = 'Lucy' then 'admin'
when userType = '1' then 'admin'
when userType = '2' then 'general user'
else 'other' end
from tbl_user

注意:case语句中when匹配成功后,就到end,不会匹配下一个when,
所以如果有一条记录,userid = '1' 并且usertype = '2',
则返回uertype是‘admin'而不是’general user'

d.return语句
立即退出程序,return后面的语句将不执行。return 后常跟一个整形表达式作为返回值。

e.goto语句(最好不用goto语法)
跳转到跟在goto后面的标签位置。如

declare @count int
              @value int
set @count = (select count(*) from table)
if(@count = 0)
begin
    set @value = 0
    goto Flag
end
set @value = @count + 10
Flag:
print @value

【MySQL简单编程】

MySQL编程与SQL Server类似,也要包含在所谓的区块BEGIN和END里,同时END后面必须要有分号(SQL Server不需要分号)。下面具体讲解变量定义、条件、循环的基本语法。

1、变量定义

declare varname vartype;

比如 declare a int--定义一个整型变量a,也可以用default来声明默认赋值,如 declare a int default 5

2、结构语句

a.定义区块

BEGIN
--sqlStr
END;

或自定义区块名称

LABEL:BEGIN
--sqlStr
END LABEL;

这样就可以用LEAVE LABEL;跳出区块,执行区块以后的代码

b.条件语句

if 条件 then
--my statement
else
--my statement
end if;

c.循环语句

[label:] WHILE expression DO
--sqlStr
END WHILE [label] ;

[label:] LOOP
--sqlStr
END LOOP [label];

[label:] REPEAT
--sqlStr
UNTIL expression
END REPEAT [label];

下面写一个综合的程序如下

create procedrue p_test()
begin
declare a int;
set a=0;
while a<10 do
set a = a+1;
end while;
select a;
end;

可以看到,使用MySQL编程和SQL Server基本思想是一致的,语法有较大的区别。SQL Server不管是条件、循环等区块仍然要使用begin与end作为起始和结束的标识,而MySQL则直接用END something(如while,if,loop),当然,由于自己接触MySQL并不长,没有使用SQL Server那么熟练,所以写这些也是非常基础的部分,而在实际问题中会千变万化,所以还是需要更多的实践来不断提高。

(仅用于Gavatar)

  Country flag

biuquote
  • Comment
  • Preview
Loading

About

shinichi_wtnI'm Shinichi_wtn

程序员@Microsoft

[More...]

Recent Tweets

Twitter May 5, 23:54
哈尔滨自驾 https://t.co/TtWGg5kjYZ

Twitter May 3, 11:23
天气不错 https://t.co/79nakVNCMv

Twitter May 2, 20:37
哈尔滨北方森林动物园,据说是国内占地面积最大的森林动物园 https://t.co/XsSeFkXNd7

Twitter April 18, 16:00
北京周五的沙尘暴和泥雨,车脏得惨不忍睹,据说这两天所有洗车店都排长队。今天也出门洗车,先找了2家店都排了10+车(有一家还要拿号😓),最后在西山森林公园附近找到了一家排队不多的店,总算干净了🙂 https://t.co/4qPsEyHogy

Twitter April 17, 22:45
等了1年多,柯南最新的剧场版《绯色的子弹》最终上映了,第一次和日本同步上映,还是不错的,刚回北京就去看了 https://t.co/dcyX6qeQLw

Twitter April 10, 22:31
苏州出差一周 https://t.co/epmSiQ7YNT

Twitter April 4, 23:52
清明第二天古北水镇半日游,天气不错,人不少,主要就是出门走走😃 https://t.co/KgLXW9dqYw

Twitter April 2, 00:08
短暂的花期🌸 https://t.co/IoTEKbXTCG


Follow me on twitter >>

Month List