预编译、存储过程

通常我们的一条sql在db接收到最终执行完毕返回可以分为下面三个过程：

1.词法和语义解析

2.优化sql语句，制定执行计划

3.执行并返回结果

我们把这种普通语句称作Immediate Statements。

但是很多情况，我们的一条sql语句可能会反复执行，或者每次执行的时候只有个别的值不同（比如query的where子句值不同，update的set子句值不同,insert的values值不同）。

如果每次都需要经过上面的词法语义解析、语句优化、制定执行计划等，则效率就明显不行了。

所谓预编译语句就是将这类语句中的值用占位符替代，可以视为将sql语句模板化或者说参数化，一般称这类语句叫Prepared Statements或者Parameterized Statements。

预编译语句的优势在于归纳为：一次编译、多次运行，省去了解析优化等过程；此外预编译语句能防止sql注入。

当然就优化来说，很多时候最优的执行计划不是光靠知道sql语句的模板就能决定了，往往就是需要通过具体值来预估出成本代价。

编译：PREPARE stmt_name FROM preparable_stm

传参：stmt_name.set

执行：EXECUTE stmt_name [USING @var_name [, @var_name] ...]

Java EE – use PreparedStatement() with bind variables

String custname = request.getParameter("customerName");String query = "SELECT account_balance FROM user_data WHERE user_name = ? ";PreparedStatement pstmt = connection.prepareStatement( query );pstmt.setString( 1, custname); ResultSet results = pstmt.executeQuery( );

在Java里，常用Statement、PreparedStatement 和CallableStatement三种方式执行查询语句，其中 Statement 用于通用查询， PreparedStatement 用于执行参数化查询，而 CallableStatement则是用于存储过程。

在PreparedStatement对象执行的SQL语句中，参数用？来表示；调用setXXX方法来设置这些参数。预编译的SQL语句不是有具体数值的语句，而是用？来代替具体数据，然后在执行的时候再调用setXX方法把具体的数据传入。同时，这个语句只在第一次执行的时候编译一次，然后保存在缓存中。之后执行时，只需从缓存中抽取编译过了的代码以及新传进来的具体数据，即可获得完整的sql命令。后面每次执行时语句的编译时间。

预处理的步凑：定义预编译语句，sql语句中含有？；再创建预编译PreparedStatement，把sql语句传入；传入参数，setXXX；执行预处理对象。

.NET – use parameterized queries like SqlCommand() or OleDbCommand() with bind variables

String query = "SELECT account_balance FROM user_data WHERE user_name = ?"; try { OleDbCommand command = new OleDbCommand(query, connection);command.Parameters.Add(new OleDbParameter("customerName", CustomerName Name.Text));OleDbDataReader reader = command.ExecuteReader(); // … } catch (OleDbException se) { // error handling }

PHP – use PDO with strongly typed parameterized queries (using bindParam())

Hibernate - use createQuery() with bind variables (called named parameters in Hibernate)

SQLite - use sqlite3_prepare() to create a statement object.

为什么要用存储过程

预编译和存储过程之间的区别是，存储过程的SQL代码被定义并存储在数据库本身中，然后从应用程序调用。也应该避免在存储过程中动态生成SQL。如果无法避免，应该进行输入验证或适当转义，确保存储过程中的输入不能用于SQL代码注入动态生成查询中。

存储过程的使用，好像一直是一个争论。 其威力和优势主要体现在：

1.执行速度。存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般 SQL 语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。

2.当对数据库进行复杂操作时(如对多个表进行 Update,Insert,Query,Delete 时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。这些操作，如果用程序来完成，就变成了一条条的 SQL 语句，可能要多次连接数据库。而换成存储，只需要连接一次数据库就可以了。

3.存储过程可以重复使用,可减少数据库开发人员的工作量。

4.安全性高,可设定只有某此用户才具有对指定存储过程的使用权。

缺点：

1.开发调试、维护：一样由于IDE的问题，存储过程的开发调试要比一般程序困难。业务逻辑答得时候封装性不够。到需要对输入存储过程的参数进行更改，或者要更改由其返回的数据，则仍需要更新程序集中的代码以添加参数，很繁琐

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

3.服务器不能负载均衡。复杂的应用用存储过程来实现，就把业务处理的负担压在数据库服务器上了。没有办法通过中间层来灵活分担负载和压力.均衡负载等。

在互联网公司里，由于产品迭代的速度很快，使用存储过程会影响迭代效率。所以一般不用