浅谈存储过程

作者信息

【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
    }