浅谈存储过程
2016-10-01 本文已影响265人
新手村的0级玩家
【1】什么是过程？过程与函数的区别？什么是存储过程？？？
函数是带返回值的过程
过程是没有返回值的函数
存储过程：存储在数据库中的封装了若干条SQL语句的过程（MySql不支持不存储的匿名过程）
【2】存储过程的创建语法
MySQL:
create procedure procedureName()
begin
-- 存储过程的编程
-- SQL statements
end;
SQLServer：
create procedure procedurename
@参数1名 类型 ,
@参数2 类型
as
begin
-- 存储过程的编程
-- SQL statements
end
查看所有的procedure ： show procedure status;
可给存储过程传递参数
MySQL：
create procedure procedureName([in/out/inout] 参数名 参数类型.....)
其中的in/out/inout 是可选的，也就是说可有可无
in 默认缺省是in类型，为了函数内部使用的，调用时传值 call proName(参数值)
out 外输出参数，函数处理后，外部可调用(select @参数名)调用：call proName(@参数名)
SQLServer 无括号
inout 兼顾二者的特点
注意：函数调用时，
in类型给传值，call proName(参数值)
out类型给传变量名（函数内部手动初始化）call proName(@参数名)
inout类型 需要传一个已经定义了的变量
set @变量名:=变量初始值;
call proName（@变量名）
如：
MySQL：
create procedure p1(width int,height int)
begin
select concat('area is',width*height ) as area;
if width>height then
select 'you fat';
elseif width<height then
select 'you small';
else
select 'you ===';
end if;
end;
【3】存储过程调用（MySQL） call procedureName()
【4】存储过程的编程（MySQL）
- 存储过程是可以编程的（ 在begin 和 end 之间 ），可以有 变量，表达式，控制结构
（1）变量：
-
定义：用declare来声明变量
-
格式：
declare 变量名 变量类型 default 默认初始值;
也可以给变量赋默认值 如 declare gnum int; -
赋值：set 变量名=值（或表达式）； 如set gnum=gnum+old.much;
或者 set 变量名:=值，如 set gnum:=gunm+old.num; -
使用：直接使用
（2）表达式： +-*/%都可以正常的使用
（3）控制结构：
-
1)分支机构
①if双分支
if search_condition then statement_list
[elseif search_condition then statement_list] ...
[else statement_list]
end if;
②case多分支
case 变量
when 常量 then 表达式;
when 常量 then 表达式;
when 常量 then 表达式;
else 表达式(都未中 相当于default);
end case;
- 2)循环结构
①while循环
while search_condition DO
...
statement_list
end while;
②repeat循环（相当于 do while）
repeat statement_list
until search_condition
end repeat;
【5】实例（ C#调用SQL Server2008存储过程，并封装结果 ）
需求
1.查询全部的学生信息
2.插入一条学生的信息
1.创建学生表
为了方便演示，假设学生只有三个属性 ：学号，姓名，专业
/*
*@function：学生表
*@author：Stone6762
*/
create table student
(
id char(9) unique,
name char(50),
major char(64),
primary key (id),
);
2.创建存储过程
-- =============================================
-- Author: Stone6762
-- Create date: 2016-10-1 08:12:05
-- Description: 获取全部的学生信息
-- 无参的存储过程的创建
-- =============================================
CREATE PROCEDURE Stu_GetAll
AS
BEGIN
SELECT id,name,major FROM student ORDER BY id;
END
-- =============================================
-- Author: Stone6762
-- Create date: 2016-10-1 09:31:08
-- Description: 学生表的插入
-- 有参的存储过程的创建
-- =============================================
CREATE PROCEDURE Stu_Insert
@Nid varchar(9) ,
@Nname varchar(50),
@Nmajor varchar(7)
AS
BEGIN
insert into student(id,name,major)values(@Nid,@Nname,@Nmajor);
END
3. C# 链接数据库，调用存储过程，并将数据封装
public class MySSDBSP
{
struct Student
{
public string id;
public string name;
public string major;
}
//因为会多次调用SqlConnection，故此将其写入构造函数里
private SqlConnection myConnection = null;
public OFWZ3()
{
//读取配置文件里的数据库连接信息
//server=服务器名;uid=账号;pwd=密码;database=要连接的数据库;Trusted_Connection=no
StreamReader sr = new StreamReader("文件名路径\\Config.txt", Encoding.UTF8);
string connInfor = sr.ReadLine().Trim();
sr.Close();
myConnection = new SqlConnection(connInfor);
}
#region 操作学生表
/// 将学生的信息存储到数据库中
protected bool MyStuSave2DB(Student s)
{
//1.获取链接-->构造函数已经做了
//2.建立可以执行SQL语句的SqlCommand
SqlCommand MyCommand = new SqlCommand("Stu_Insert", myConnection); //定义一个数据库操作指令
//3.指明调用的是存储过程
MyCommand.CommandType = CommandType.StoredProcedure;//设置该语句是读取存储过程的
//4.给存储过程传递参数
SqlParameter id = new SqlParameter("@Nid", s.id);
SqlParameter name = new SqlParameter("@Nname", s.name);
SqlParameter major = new SqlParameter("@Nmajor", s.major);
MyCommand.Parameters.Add(id);
MyCommand.Parameters.Add(name);
MyCommand.Parameters.Add(major);
//5.执行存储过程
try//异常处理
{
myConnection.Open();
MyCommand.ExecuteNonQuery();
myConnection.Close();
}
catch (Exception ex)
{
throw new Exception(ex.ToString());
}
return true;
}
/// 读取学生表中所有的数据
protected Student[] getAllStuFromDB()
{
//1.获取链接--->构造函数已经做了
//2..建立可以执行SQL语句的SqlCommand
SqlCommand MyCommand = new SqlCommand("Stu_GetAll", myConnection); //定义一个数据库操作指令
//3.指明调用的是存储过程
MyCommand.CommandType = CommandType.StoredProcedure;//设置该语句是读取存储过程的
//4.设置数据适配器
SqlDataAdapter SelectAdapter = new SqlDataAdapter();//定义一个数据适配器
SelectAdapter.SelectCommand = MyCommand;//定义数据适配器的操作指令
//5.执行存储过程
try
{
myConnection.Open();//打开数据库连接
SelectAdapter.SelectCommand.ExecuteNonQuery();//执行数据库查询指令
myConnection.Close();//关闭数据库
}
catch (Exception e)
{
throw new Exception(e.ToString());
}
//6.将结果存储到DataSet里
DataSet MyDataSet = new DataSet();//定义一个数据集
SelectAdapter.Fill(MyDataSet);//填充数据集
//7.解析DataSet中的数据，并将其封装到Student数组里
DataTable dt = MyDataSet.Tables[0];//获取查询的结果表（因为只有一个）
return getStuFroTable(dt);//将DataTable里存储的学生信息封装到结构体数组里
}
/// 将DataTable里存储的学生信息封装到结构体数组里
private Student[] getStuFroTable(DataTable dt)
{
Student[] students = new Student[dt.Rows.Count];
for (int i = 0; i < dt.Rows.Count; i++)//遍历行
{
students[i].id = dt.Rows[i]["id"].Trim();
students[i].name = ((string)dt.Rows[i]["name"]).Trim();
students[i].major = ((string)dt.Rows[i]["major"]).Trim();
}
return students;
}
#endregion
}
