Real SQL Programming
目录
SQL in Real Programs
Options
Stored Procedures
Advantages of Stored Procedures
Parameters in PSM
SQL in Real Programs
-
We have seen only how SQL is used at the generic query interface --- an environment where we sit at a terminal and ask queries of a database.
-
Reality is almost always different:
conventional programs interacting with SQL.
（我们只是看见了SQL是如何在常规的查询接口中的使用，也就是直接通过终端 对数据库进行交互，但实际上通常是常规程序与SQL语言的交互）
Options
- Code in a specialized language is stored in the database itself (e.g.PSM, PL/SQL).
- SQL statements are embedded in a host language (e.g., C).
- Connection tools are used to allow a conventional language to access a database (e.g., CLI, JDBC, PHP/DB).
（常规程序与SQL语言的交互模式： 数据库储存代码、SQL内嵌、连接工具）
Stored Procedures
-
PSM, or “persistent stored modules, ” allows us to store procedures as database schema elements.（持久储存模块允许我们将储存模式作为数据库模式元素）
-
PSM = a mixture of conventional statements (if, while, etc.) and SQL.
- Lets us do things we cannot do in SQL alone.
数据库中的储存过程和函数的区别在于，函数可以直接通过函数名的引用得到函数值，但是储存过程不行
Advantages of Stored Procedures
- Share Application Logic（共享应用逻辑）
- Shield Database Schema Details（屏蔽数据库细节）
- Provide Security Mechanisms（提供安全机制 ）
- Improve Performance（提升性能）
- Reduce Network Traffic（减少网络流量）
Parameters in PSM
Unlike the usual name-type pairs in languages like C, PSM uses mode- name-type triples, where the mode can be:
-
IN = procedure uses value, does not change value.（传入参数，只能调用不能修改）
-
OUT = procedure changes, does not use.（传出参数，可以在储存过程被修改）
-
INOUT = both
想得到储存过程的返回值，虽然不能直接通过调用储存过程名字，但是可以调用OUT参数