数据库的存储过程

一时失言乱红尘 2022-06-11 04:30 219阅读 0赞

由于昨天去面试的时候被面试官问了两个关于数据库的问题，今天特地的回来学习了一下关于数据库的两个知识点：存储过程和视图，这里只对数据库的存储过程进行描述。

1、存储过程的**概念**：

存储过程是使用SQL语句和流程控制语句编写的模块，存储过程经编译和优化后存储在数据库服务器端的数据库中，使用是调用即可；

2、使用存储过程的**优点**：

相对于直接使用SQL语句，直接调用存储过程有以下好处：

(1)减少网络通信量。调用一个行数不多的存储过程与直接调用SQL语句的网络通信量可能不会有很大的差别，可是如果存储过程包含上百行SQL语句，那么其性能绝对比一条一条的调用SQL语句要高得多。

(2)执行速度更快。有两个原因：首先，在存储过程创建的时候，数据库已经对其进行了一次解析和优化。其次，存储过程一旦执行，在内存中就会保留一份这个存储过程，这样下次再执行同样的存储过程时，可以从内存中直接调用。

(3)更强的适应性：由于存储过程对数据库的访问是通过存储过程来进行的，因此数据库开发人员可以在不改动存储过程接口的情况下对数据库进行任何改动，而这些改动不会对应用程序造成影响。

(4) 布式工作：应用程序和数据库的编码工作可以分别独立进行，而不会相互压制。

3、使用存储过程的**缺点**：

(1)如果更改范围大到需要对输入存储过程的参数进行更改，或者要更改由其返回的数据，则您仍需要更新程序集中的代码以添加参数、更新 GetValue() 调用，等等，这时候估计比较繁琐了。

(2)可移植性差

由于存储过程将应用程序绑定到 SQL Server，因此使用存储过程封装业务逻辑将限制应用程序的可移植性。如果应用程序的可移植性在您的环境中非常重要，则将业务逻辑封装在不特定于 RDBMS 的中间层中可能是一个更佳的选择。

(3)大量采用存储过程进行业务逻辑的开发致命的缺点是很多存储过程不支持面向对象的设计，无法采用面向对象的方式将业务逻辑进行封装，从而无法形成通用的可支持复用的业务逻辑框架。

(4)代码可读性差,相当难维护.

4、声明格式：

CREATE PROCEDURE<过程名>(<参数>)

<局部声明>

<过程体>;

其中，参数是可选的

5、调用格式：

CALL <过程名>(<参数列表>)

6、实例

创建存储过程：

CREATE PROCEDURE user_procedure(INOUT name VARCHAR(20), INOUT pwd VARCHAR(64))
      BEGIN
      declare a integer;
      select count(*) from user where name=name AND  password=pwd;

调用存储过程：

set @name='xiaoyue';
    set @pwd='123456';
    CALL `test_two`.`user_procedure`(@name,@pwd);

7、补充：  
上面的主要是对于MySQL的存储过程的创建和使用，下面继续描述一下SQL server的存储过程的实例和C\#调用存储过程的实例代码  
SQL Server中创建存储过程格式：

CREATE PROCEDURE Procedure_Name  
    
        --Procedure_Name为存储过程名(不能以阿拉伯数字开头)，在一个数据库中触发器名是唯一的。名字的长度不能超过个字。PROCEDURE可以简写为PROC。
         
        @Param1 Datatype,@Param2 Datatype 
        
        --@Param1和@Param2为存储过程的参数，Datatype为参数类型,多个参数用逗号隔开,最多允许个参数。
        
    AS --存储过程要执行的操作 
    
    BEGIN  
        --BEGIN跟END组成一个代码块，可以写也可以不写，如果存储过程中执行的SQL语句比较复杂，用BEGIN和END会让代码更加整齐，更容易理解。
    END
    GO --GO就代表结操作完毕　　
    
    
    exec Procedure_Name [参数名] --调用存储过程Procedure_Name。
    
    drop procedure Procedure_Name --删除存储过程Procedure_Name，不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程
    
    show procedure status --显示数据库中所有存储的存储过程基本信息，包括所属数据库，存储过程名称，创建时间等
    
    show create procedure Procedure_Name --显示存储过程Procedure_Name的详细信息
    
    exec sp_helptext Procedure_Name --显示你这个Procedure_Name这个对象创建文本

使用实例：

CREATE PROCEDURE [dbo].[Procedure]
    	@SNum CHAR(6) ,
    	@Pwd NVARCHAR(16)
    AS
    BEGIN
    	SELECT COUNT(*) FROM dbo.tb_Students WHERE SNum=@SNum AND SPwd=@Pwd;
    END

.Net调用存储过程的实例：

public class CStoreProcedure
        {
           public string connString = "server=*.*.*.*;database=db_SGMS;uid=******;pwd=*******";
            public void Get(string snum,string pwd)
            {
                SqlConnection conn = new SqlConnection(connectionString: connString);
                conn.Open();
    
                SqlCommand sc = new SqlCommand();
                sc.Connection = conn;
                sc.CommandText = "[dbo].[Procedure]";
                sc.CommandType = System.Data.CommandType.StoredProcedure;
                sc.Parameters.Add("@SNum", sqlDbType: System.Data.SqlDbType.VarChar).Value = snum;
                sc.Parameters.Add("@Pwd", sqlDbType: System.Data.SqlDbType.Char).Value = pwd;
                int i= (int)sc.ExecuteScalar();
                Console.WriteLine(i);
                conn.Close();
            }
         
        }