SQL----视图，存储过程

前几天学了一些对数据库和表的操作，接下来就学了ADO.net。经过这一个多星期的学习也初步的理解了ADO.net，这两天我们又学了SQL的视图，存储过程，触发器和事务。我觉得单单用SQL语句学习这些概念很好理解，但是一旦和ADO.net联系起来理解就有点难了。我这两天学存储过程，触发器和事务脑子乱乱的，乱归乱还是要总结理顺一下这两天学习的知识。

一，视图

视图就是一张虚拟表，拥有表的所有特性，视图是在已经存在的物理表上创建的。一般情况下，对视图只进行一个查询操作。下面是创建视图的格式：

create[alter] view v_one

select_statement

视图的结果会随着物理表的变化而变化，只要是表就能给这个表定义一个视图。下面是一个创建视图的例子：

create view v_one

select * from employee where salary=(select max(salary) from employee)

二,存储过程

存储过程是一个特殊的函数，要么没有返回值，要么返回值只能是0,1。存储过程实现了一次编译，多次运行的效果。下面是创建存储过程的格式：

create procedure [存储过程名] 参数列表

sql_statement

修改存储过程时可以用一下格式：

create[alter] proc [存储过程名] 参数列表

sql_statement

下面对几种存储过程一一举例理解

第一种是有参数的存储过程：

create procedure p_one @i int,@name varchar(20)

select * from employee where salary > @i

第二种是有返回值的存储过程：

create procedure p_four @sex varchar(4)
    as
    declare @i int
    select @i=count(*) from employee where sex = @sex
    if(@i>9)
    begin
        return 1
    end
    else
    begin
        return 0
    end
    
    declare @j int
    execute @j = p_four '男'
    print @j

默认情况下,存储过程的参数类型为输入参数，也就是值传递。但是在一些情况下，需要将结果带出来，就需要用带出参数。如下这种情况：

create procedure p_four @name varchar(20) output
  as 
  set @name = '傲视九重天'
    
  declare @name varchar(20)
  execute p_four @n output
   print @name

第三种既有输入参数又有输出参数，下面这种情况就是另一个综合情况下的实例：

 create procedure p_five @sex varchar(4),@sexcount int output
    as

    select @sexount=count(*) from employee where sex = @sex
    if(@sexcount>9)
    begin
        return 1
    end
    else
    begin
        return 0
    end
    
    
    declare @result int
    declare @count int
     
    execute @result = p_seven '男',@count output
    
    print @result
    print @count

我们或许会遇到查询数据库时要求分页查询，首先要看一下数据库中存储过程p_pager

View Code

   create procedure p_pager @pageno int,@pagesize int,@count int output
    as
    select @count = count(*) from employee
    Select top (@pagesize) * from employee where eid not in (select top ((@pageno-1)*@pagesize) eid from employee order by eid desc) order by eid desc
    go
    declare @pagesize int
    declare @pageno int
    declare @count int
    execute p_pager 1,10,@count output
    print @count

下面就是一个用ADO.net应用存储过程做出的一个分页的小程序：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Com.HS.DBHelper;
using System.Data;
using System.Data.SqlClient;
namespace TestHelper
{
    class Program
    {
        public static int count;
        public static int totalpage;
        static void Main(string[] args)
        {
            pager(1, 7);
            Console.WriteLine("请输入页码数");
            int pageno = 1;
            while (pageno != totalpage + 1)
            {

                pageno = Convert.ToInt32(Console.ReadLine());
                if (pageno >= totalpage + 1)
                {
                    Console.WriteLine("你不识数啊");
                    break;
                }
                pager(pageno, 7);

            }

        }
        static void pager(int pageno, int pagesize)
        {
            string strcon = "server=.;database=blog;uid=sa;pwd=1";
            SqlHelper helper = new SqlHelper(strcon);
            SqlParameter[] param = {
                                    new SqlParameter("@pageno",SqlDbType.Int,4),
                                    new SqlParameter("@pagesize",SqlDbType.Int,4),
                                    new SqlParameter("@count",SqlDbType.Int,4)
                                   };
            param[0].Value = pageno;
            param[1].Value = pagesize;
            param[2].Direction = ParameterDirection.Output;
            try
            {
                SqlDataReader reader = helper.ExecuteQuery("p_pager", CommandType.StoredProcedure, param);
                using (reader)
                {
                    while (reader.Read())
                    {
                        Console.WriteLine(reader["eid"] + "\t" + reader["ename"]);
                    }
                }
                //得到总记录数
                count = Convert.ToInt32(param[2].Value);
                //计算总页数
                if (count % pagesize != 0)
                {
                    totalpage = count / pagesize + 1;
                }
                else
                {
                    totalpage = count / pagesize;
                }
                Console.WriteLine("共有" + count + "条记录");
                Console.WriteLine("共有" + totalpage + "页");
                Console.WriteLine("当前在第" + pageno + "页");
            }
            catch (Exception)
            {

            }
            finally
            {
                helper.Close();
            }
        }
    }
}

三，触发器
触发器一种特殊的存储过程。是一个被动执行的函数，不能够显示的调用。触发器一般情况下建立在表上，一个表上最多只能有4个触发器。触发器不能触发表的查询操作。创建格式为：

        create trigger t_name
       on 表
      for|instead of [insert,delete,update]

sql_statement
值得注意的是一个表上，有且只能有一个instead of 触发器。下面是一个SQL触发器的实例：

alter trigger t_one 
   on employee
   for delete
   as
    declare @eid int
    declare @ename varchar(20)
    select @eid = eid,@ename =ename  from deleted
    insert into b_employee values(@eid,@ename)
   go
   
   delete from employee where eid=56

四，事务
事务其实很好理解他的含义，主要是原子性和安全性。简单一点说就好比几件事要么都做成功要么都不成功。
下面是基于ADO.NET下写的一个关于手的小程序有助于理解什么是事务：

using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;
public class StudyADO02
{
    public static void Main()
    {
        string strcon = "server=.;database=blog;uid=sa;pwd=1";
        SqlConnection con = new SqlConnection(strcon);
        SqlCommand cmd = new SqlCommand();    
        
        cmd.Connection = con;
        //创建一个事务        
        //把事务关联到cmd对象上
        StringBuilder sb = new StringBuilder();
        sb.Append(@"
            insert into employee values('yyyy','www',32,567,'wwwww');
            insert into employee values('yyyy',1,32,567,'wwwww');
        ");
        cmd.CommandText = sb.ToString();
    
        using(con)
        {    
            con.Open();
            SqlTransaction tran = con.BeginTransaction();
            using(cmd)
            {
                try
                {
                  cmd.Transaction = tran;
                    cmd.ExecuteNonQuery();
                    tran.Commit();
                }
                catch (Exception)
                {
                    Console.WriteLine("事务失败");
                    try
                    {
                        tran.Rollback();
                    }
                    catch (Exception)
                    {
                    }
                    
                }
                
            }
        }
        
    }
}

posted on 2012-08-14 20:21 兰@net 阅读(411) 评论(0) 编辑收藏举报