Chapter 5 Advanced SQL¶
5.1 Accessing SQL from a Programming Language¶
- Not all queries can be expressed in SQL, since SQL does not provide the full expressive power of a general-purpose language. 不是所有的查询都能用 SQL 语言表达。
- Non-declarative actions -- such as printing a report, interacting with a user, or sending the results of a query to a graphical user interface -- cannot be done from within SQL. 用户交互是图形界面，语音、图像，数据库不具备这方面的功能。
从高级语言（如 C）访问数据库，主要是下面两种方式：
(1) API(Application Program Interface) -- A general-purpose program can connect to and communicate with a database server using a collection of functions. 函数库
(2) Embedded SQL-- provides a means by which a program can interact with a database server.
把 SQL 语句嵌入到语言内
- The SQL statements are translated at compile time into function calls.
- At runtime, these function calls connect to the database using an API that provides dynamic SQL facilities.
5.1.1 JDBC¶
JDBC is a Java API for communicating with database systems supporting SQL.
public static void JDBCexample(String dbid, String userid, String passwd)
{
try {
Connection conn = DriverManager.getConnection(
"jdbc:oracle:thin:@db.yale.edu:2000:univdb", userid, passwd);
Statement stmt = conn.createStatement();
… Do Actual Work ….
stmt.close();
conn.close();
}
catch (SQLException sqle) {
System.out.println("SQLException : " + sqle);
}
}
大致的流程如下
- Open a connection
- Create a “statement” object
- Execute queries using the Statement object to send queries and fetch results
- Exception mechanism to handle errors
Example
Update to database
try
{
stmt.executeUpdate(
"insert into instructor values(’77987’, ’Kim’, ’Physics’, 98000)");
}
catch (SQLException sqle)
{
System.out.println("Could not insert tuple. " + sqle);
}
Execute query and fetch and print results
ResultSet rset = stmt.executeQuery(
"select dept_name, avg (salary)
from instructor
group by dept_name");
while (rset.next()) {
System.out.println(rset.getString("dept_name") + " " + rset.getFloat(2));
}
Getting result fields:
rset.getString(“dept_name”) and rset.getString(1) equivalent if dept_name is the first argument of select result.
Dealing with Null values
Prepared Statement¶
PreparedStatement pStmt = conn.prepareStatement(
"insert into instructor values(?,?,?,?)");
pStmt.setString(1, "88877");
pStmt.setString(2, "Perry");
pStmt.setString(3, "Finance");
pStmt.setInt(4, 125000);
pStmt.executeUpdate();
pStmt.setString(1, "88878");
pStmt.executeUpdate();
这里空格是占位符，表示执行时需要提供四个参数。setString, setInt 就是把第几个占位符设置为参数，并 executeUpdate 进行插入。
在从用户获取输入并将其添加到查询时，始终应使用预编译语句。这是因为预编译语句可以有效防止SQL注入攻击。
不要通过字符串拼接构建查询
不要通过字符串拼接的方式构建SQL查询，例如：
这种方式存在严重的安全隐患，容易导致SQL注入攻击。例如，如果name的值是"D'Souza"，那么生成的SQL语句将是：
这会导致SQL语法错误，因为单引号没有正确转义。
SQL Injection¶
假设一个查询是这样构造的：
这里，name 是用户输入的值。
如果用户输入的是 X' or 'Y'='Y，那么最终生成的SQL语句会变成：
这条SQL语句的意思是：选择所有名字为X或者条件'Y'='Y'（永远为真）的记录。因此，这个查询将返回表中所有的记录，而不是仅仅返回名字为X的记录。
用户甚至可以输入更复杂的字符串，例如:
这会导致生成的SQL语句变为：
在这种情况下，不仅会执行原始的查询，还会执行额外的更新操作，将所有讲师的薪水增加10000。这是一个非常危险的行为，可能会对数据库造成严重破坏。
为了避免 SQL 注入攻击，应该始终使用预编译语句，并将用户输入作为参数传递。例如：
PreparedStatement pstmt = conn.prepareStatement("select * from instructor where name = ?");
pstmt.setString(1, name);
ResultSet rs = pstmt.executeQuery();
这种方式下，用户输入的内容会被正确地转义和处理，不会改变原始SQL语句的结构，从而有效地防止SQL注入攻击。
Metadata Features¶
(1) ResultSet metadata
提供了关于ResultSet对象中数据的信息。这包括列的数量、列名、列的数据类型等。这对于动态处理查询结果非常有用，因为你可能不知道返回的具体列或它们的数据类型。
After executing query to get a ResultSet rs
ResultSetMetaData rsmd = rs.getMetaData();
for(int i = 1; i <= rsmd.getColumnCount(); i++) {
System.out.println(rsmd.getColumnName(i));
System.out.println(rsmd.getColumnTypeName(i));
}
(2) Database metadata
提供了关于整个数据库的信息，包括数据库产品名称、版本、支持的功能等。例如，你可以查询数据库是否支持事务、存储过程或某种SQL语法。
DatabaseMetaData dbmd = conn.getMetaData();
ResultSet rs = dbmd.getColumns(null, "univdb", "department", "%");
// Arguments to getColumns: Catalog, Schema-pattern, Table-pattern,
// and Column-Pattern
// Returns: One row for each column; row has a number of attributes
// such as COLUMN_NAME, TYPE_NAME
while(rs.next()) {
System.out.println(rs.getString("COLUMN_NAME"),
rs.getString("TYPE_NAME");
}
Transaction Control in JDBC¶
（1）默认情况下每个SQL语句都被视为一个独立的事务，并且会自动提交（auto-commit模式）。这意味着一旦执行完一条SQL语句，其结果就会立即保存到数据库中。
但是对于需要多个更新步骤才能完成的复杂事务来说，这种自动提交机制并不是一个好的选择。如果其中一个步骤失败，而其他步骤已经提交，那么整个事务将处于不一致的状态。
（2）关闭自动提交
可以通过调用conn.setAutoCommit(false)来关闭连接的自动提交功能。这样，所有的SQL语句都不会自动提交，而是被暂存起来，直到显式地调用提交或回滚方法。
这样做可以确保只有当所有相关的SQL语句都成功执行后，才会作为一个整体提交到数据库，从而保证数据的一致性。
（3）显式提交或回滚
提交：当所有必要的SQL语句都执行完毕并且没有错误时，可以调用conn.commit()来提交事务。这会将所有暂存的操作永久保存到数据库中。
回滚：如果在执行过程中发生错误或者需要取消当前事务，可以调用conn.rollback()来回滚事务。这会撤销所有暂存的操作，使数据库恢复到事务开始前的状态。
（4）重新开启自动提交
如果不再需要手动控制事务，可以调用conn.setAutoCommit(true)来重新开启自动提交功能。之后，每个 SQL 语句又会回到自动提交的状态。
Other JDBC features¶
（1） 在JDBC中，可以通过CallableStatement对象来调用数据库中的存储函数和存储过程。
- 调用函数：
这里使用了prepareCall方法，并传入一个包含问号占位符的SQL语句。第一个问号表示函数的返回值，第二个问号表示函数的参数。
- 调用过程：
同样使用prepareCall方法，但这里的SQL语句用于调用存储过程，问号表示过程的输入参数。
（2）JDBC 还提供了处理大型二进制对象（BLOB）和字符大对象（CLOB）的方法。
-
获取BLOB和CLOB对象：
getBlob()和getClob()方法类似于getString()方法，但它们分别返回Blob和Clob类型的对象。 -
从这些对象中获取数据：可以通过
getBytes()方法从Blob对象中获取字节数据。 -
更新大型对象：可以将一个打开的流与Java的
Blob或Clob对象关联起来，以便更新大型对象。
5.1.2 SQLJ¶
JDBC is overly dynamic, errors cannot be caught by compiler 错误可以被编译器发现
SQLJ: embedded SQL in Java
#sql iterator deptInfoIter ( String dept name, int avgSal);
deptInfoIter iter = null;
#sql iter = { select dept_name, avg(salary) as avgSal from instructor
group by dept name };
while (iter.next()) {
String deptName = iter.dept_name();
int avgSal = iter.avgSal();
System.out.println(deptName + " " + avgSal);
}
iter.close();
嵌入都要 #sql 标识，最后会被编译器转化为 Java 的类。
5.1.3 ODBC¶
Each database system supporting ODBC provides a "driver" library that must be linked with the client program.
When client program makes an ODBC API call, the code in the library communicates with the server to carry out the requested action, and fetch results.
一个ODBC的基本框架
int ODBCexample()
{
RETCODE error;
HENV env; /* environment */
HDBC conn; /* database connection */
SQLAllocEnv(&env);
SQLAllocConnect(env, &conn);
SQLConnect(conn, “db.yale.edu", SQL_NTS, "avi", SQL_NTS, "avipasswd", SQL_NTS);
{ …. Do actual work … }
SQLDisconnect(conn);
SQLFreeConnect(conn);
SQLFreeEnv(env);
}
ODBC program first allocates an SQL environment, then a database connection handle.
Opens database connection using SQLConnect().
SQL_NTS denotes previous argument is a null-terminated string.
ODBC的程序的主体内容
char deptname[80];
float salary;
int lenOut1, lenOut2;
HSTMT stmt;
char * sqlquery = "select dept_name, sum (salary) from instructor group by dept_name";
SQLAllocStmt(conn, &stmt);
error = SQLExecDirect(stmt, sqlquery, SQL_NTS);
if (error == SQL SUCCESS) {
SQLBindCol(stmt, 1, SQL_C_CHAR, deptname , 80, &lenOut1);
SQLBindCol(stmt, 2, SQL_C_FLOAT, &salary, 0 , &lenOut2);
while (SQLFetch(stmt) == SQL_SUCCESS) {
printf (" %s %g\n", deptname, salary);
}
}
SQLFreeStmt(stmt, SQL_DROP);
使用SQLExecDirect()：程序通过调用SQLExecDirect()函数直接向数据库发送SQL命令。这个函数用于执行一个简单的SQL语句，不需要预先准备。
使用SQLFetch()：当SQL命令执行后，查询结果以元组（tuple）的形式存储在数据库中。SQLFetch()函数用于从结果集中获取下一个元组，并将其加载到应用程序的内存中。
使用SQLBindCol()：该函数用于将C语言中的变量与查询结果集中的列（属性）进行绑定。这样，当调用SQLFetch()获取元组时，查询结果的属性值会自动存储到对应的C语言变量中。
-
ODBC语句变量：表示当前正在处理的SQL语句。
-
属性位置：指定查询结果集中要绑定的列的位置（从1开始计数）。
-
类型转换：定义从SQL数据类型到C语言数据类型的转换规则。
-
变量地址：提供C语言变量的内存地址，以便存储查询结果。
-
对于可变长度类型（如字符数组）：
最大长度：指定变量的最大长度。
实际长度存储位置：提供一个指针，用于存储实际获取的数据长度。如果返回负值，则表示该字段为NULL值。
ODBC Prepared Statements¶
SQL语句预编译：在数据库中预先编译好的SQL语句。
可以包含占位符：例如，在插入数据时，可以使用insert into account values(?, ?, ?)这样的形式，其中的问号（?）就是占位符，用于后续绑定实际值。
重复执行：预编译后的SQL语句可以多次执行，并在每次执行时用实际值替换占位符。
通过下面的语句准备 Statement
其中，stmt是一个指向SQL语句的指针，<SQL String>是具体的SQL语句字符串。
然后绑定相应参数
这里，stmt是之前准备好的SQL语句指针，<parameter#>表示占位符的位置编号
需要执行的时候使用 SQLExecute 函数执行预编译并绑定好参数的SQL语句
retcode是返回代码，用来检查执行结果是否成功。
Other ODBC Features¶
元数据特性（Metadata features）
- 查找数据库中的所有关系：通过元数据特性，可以获取数据库中所有的表、视图等关系的信息。
- 查找查询结果或数据库关系中的列名和类型：可以获取查询结果集中各列的名称和数据类型，或者直接获取数据库中某个表或视图的列信息。
事务处理机制
默认情况下，每个SQL语句被视为一个独立的事务并自动提交：这意味着每执行一条SQL语句，系统会立即将其作为一个单独的事务进行提交，确保数据的一致性。
关闭连接上的自动提交
使用 SQLSetConnectOption函数，将SQL_AUTOCOMMIT选项设置为0，可以关闭当前连接的自动提交功能。
当自动提交关闭后，事务需要显式地提交或回滚：
- 提交事务
- 回滚事务
5.1.4 Embedded SQL¶
A language to which SQL queries are embedded is referred to as a host language, and the SQL structures permitted in the host language comprise embedded SQL.
SQL标准定义了如何在多种编程语言中嵌入SQL，包括C、C++、Java、Fortran和PL/1等。这些编程语言被称为宿主语言（host language），而嵌入其中的SQL结构则构成了嵌入式SQL。
The basic form of these languages follows that of the System R embedding of SQL into PL/1.
嵌入式SQL的基本形式遵循System R将SQL嵌入PL/1的方式。
EXEC SQL statement is used in the host language to identify embedded SQL request to the preprocessor
this varies by language
n some languages, like COBOL, the semicolon is replaced with END-EXEC
In Java embedding uses # SQL { …. };
(1) Before executing any SQL statements, the program must first connect to the database. This is done using:
Here, server identifies the server to which a connection is to be established.
(2) Host Variables used as above must be declared within DECLARE section, as illustrated below. The syntax for declaring the variables, however, follows the usual host language syntax.
(3) To write an embedded SQL query, we use the
statement. The variable c is used to identify the query
(4) The open statement for our example is as follows:
This statement causes the database system to execute the query and to save the results within a temporary relation. The query uses the value of the host-language variable credit-amount at the time the open statement is executed.
(5) The fetch statement causes the values of one tuple in the query result to be placed on host language variables.
Repeated calls to fetch get successive tuples in the query result
(6) A variable called SQLSTATE in the SQL communication area (SQLCA) gets set to '02000' to indicate no more data is available
Note
(7) The close statement causes the database system to delete the temporary relation that holds the result of the query.
Example
Embedded SQL without cursor
main( )
{
EXEC SQL INCLUDE SQLCA; //声明段开始
EXEC SQL BEGIN DECLARE SECTION;
char account_no [11]; //host variables(宿主变量)声明
char branch_name [16];
int balance;
EXEC SQL END DECLARE SECTION;//声明段结束
EXEC SQL CONNECT TO bank_db USER Adam Using Eve;
scanf (“%s %s %d”, account_no, branch_name, balance);
//开始SQL操作
EXEC SQL insert into account
values (:account_no, :branch_name, :balance);
If (SQLCA.sqlcode ! = 0)
printf ( “Error!\n”);
else
printf (“Success!\n”);
}
select single record
EXEC SQL select balance into :balance:mask // 指示变量=0 正常
from account // 指示变量<0 NULL
where account_number = :account_no; // 指示变量>0 截断
Embedded SQL with cursor
main( )
{
EXEC SQL INCLUDE SQLCA;
EXEC SQL BEGIN DECLARE SECTION;
char customer_name[21];
char account_no [11];
int balance;
EXEC SQL END DECLARE SECTION;
EXEC SQL CONNECT TO bank_db USER Adam Using Eve;
EXEC SQL DECLARE account_cursor CURSOR for
select account_number, balance
from depositor natural join account
where depositor.customer_name = : customer_name;
scanf (“%s”, customer_name);
EXEC SQL open account_cursor;
for (; ;)
{
EXEC SQL fetch account_cursor into :account_no, :balance;
if (SQLCA.sqlcode!=0)
break;
printf( “%s %d \ n”, account_no, balance);
}
EXEC SQL close account_cursor;
}
Delete or update current record
5.2 Procedural Constructs in SQL¶
SQL provides a module language, Permits definition of procedures in SQL, with if-then-else statements, for and while loops, etc.
We can store procedures in the database, then execute them using the call statement
It also permits external applications to operate on the database without knowing about internal details
5.2.1 SQL Functions¶
Define a function that, given the name of a department, returns the count of the number of instructors in that department.
create function dept_count (dept_name varchar(20))
returns integer
begin
declare d_count integer;
select count (* ) into d_count
from instructor
where instructor.dept_name = dept_name
return d_count;
end
这样我们就有了一个函数用于得到某个部门的人数，我们可以这样使用。
我们也可以返回一个表格
create function instructors_of (dept_name char(20))
returns table (
ID varchar(5),
name varchar(20),
dept_name varchar(20),
salary numeric(8,2)
)
return table
(select ID, name, dept_name, salary
from instructor
where instructor.dept_name = instructors_of.dept_name)
使用的时候这样使用即可
5.2.2 SQL Procedures¶
The dept_count function could instead be written as procedure:
create procedure dept_count_proc (in dept_name varchar(20),out d_count integer)
begin
select count(*) into d_count
from instructor
where instructor.dept_name = dept_count_proc.dept_name
end
Procedures 可以通过CALL语句来调用，既可以嵌入到其他SQL程序中，也可以在动态SQL中使用。
5.2.3 Procedural Constructs¶
Most database systems implement their own variant of the standard syntax below , read your system manual to see what works on your system
Compound statement: begin … end,
- May contain multiple SQL statements between begin and end.
- Local variables can be declared within a compound statements
While and repeat statements :
declare n integer default 0;
while n < 10 do
set n = n + 1
end while
repeat
set n = n – 1
until n = 0
end repeat
For loop, Permits iteration over all results of a query
declare n integer default 0;
for r as
select budget from department
where dept_name = ‘Music’
do
set n = n - r.budget
end for
Conditional statements (if-then-else)
if boolean expression
then statement or compound statement
elseif boolean expression
then statement or compound statement
else statement or compound statement
end if
5.2.4 External Language Functions/Procedures¶
Declaring external language procedures and functions
create procedure dept_count_proc(in dept_name varchar(20),out count integer)
language C
external name ’ /usr/avi/bin/dept_count_proc’
create function dept_count(dept_name varchar(20))
returns integer
language C
external name ‘/usr/avi/bin/dept_count’
优点：
-
更高的效率：对于许多操作来说，外部语言例程比传统的SQL语句执行得更快。
-
更强的表达能力：外部语言通常提供了更多的编程功能和灵活性，使得复杂的计算和逻辑处理变得更加容易。
缺点：外部语言例程的代码可能需要被加载到数据库系统的地址空间中执行。这可能会带来以下风险：
- 意外破坏数据库结构：如果外部代码出现错误，可能会不小心修改或删除重要的数据库数据。
- 安全风险：允许用户访问未经授权的数据。如果外部代码没有正确地进行权限检查，可能会导致敏感信息泄露。
*【在使用外部语言例程时，为了应对安全问题，可以采用以下两种方法：
- 沙箱技术：选择一种安全的语言（如Java），这种语言不能被用来访问或破坏数据库代码的其他部分。通过这种方式，即使外部代码中存在漏洞或恶意行为，也无法对数据库系统造成损害。
- 独立进程运行：将外部语言函数或过程放在一个与数据库主进程隔离的单独进程中执行。这样，外部代码就无法直接访问数据库进程的内存，从而避免了数据泄露和篡改的风险。参数和结果则通过进程间通信的方式进行传递。
需要注意的是，这两种方法都会带来一定的性能开销，因为它们增加了额外的安全防护层。然而，许多数据库系统同时支持上述两种方法以及直接在数据库系统地址空间内执行的方法，以满足不同场景下的需求和权衡。(Many database systems support both above approaches as well as direct executing in database system address space.)】
5.3 Triggers¶
A trigger is a statement that is executed automatically by the system as a side effect of a modification to the database.
Trigger - ECA rule
- E: Event (insert, delete, update）
- C: Condition
- A: Action
To design a trigger mechanism, we must:
- Specify the conditions under which the trigger is to be executed. 说明什么时候 trigger 会被执行
- Specify the actions to be taken when the trigger executes. 说明 trigger 会怎么操作
Trigger Example
银行对于大额交易需要上报，写一个这样的trigger
trigger 这样写
create trigger account_trigger after update of account on balance
referencing new row as nrow
referencing old row as orow
for each row
when nrow.balance - orow.balance > =200000 or
orow.balance -nrow.balance >=50000
begin
insert into account_log values (nrow.account-number, nrow.balance-orow.balance , current_time() )
end
Triggers on update can be restricted to specific attributes
- E.g., after update of takes on grade
Values of attributes before and after an update can be referenced
- referencing old row as : for deletes and updates
- referencing new row as : for inserts and updates
Triggers can be activated before an event, which can serve as extra constraints. 比如说举个例子，设置空成绩为 null
create trigger setnull_trigger before update of takes
referencing new row as nrow
for each row
when (nrow.grade = ‘ ‘)
begin atomic
set nrow.grade = null;
end;
Statement Level Triggers
create trigger grade_trigger after update of takes on grade
referencing new table as new_table
for each statement
when exists( select avg(grade)
from new_table
group by course_id, sec_id, semester, year
having avg(grade)< 60 )
begin
rollback
end
Statement level Triggers and Row Level Tirggers
在数据库管理系统中，触发器（Triggers）用于定义当特定事件发生时自动执行的代码。根据触发器激活的粒度不同，可以将其分为两种主要类型：语句级触发器（Statement Level Triggers）和 行级触发器（Row Level Triggers）。这两种触发器的主要区别在于它们何时被激活以及如何处理数据。
(1) 激活时机
- 语句级触发器（Statement Level Triggers）
在整个SQL语句执行完毕后，无论该语句影响了多少行记录，语句级触发器只会被激活一次。这种类型的触发器适用于需要在整个操作完成后进行检查或执行某些逻辑的情况。
- 行级触发器（Row Level Triggers）
对于每个受影响的行，都会单独激活一次行级触发器。例如，在一个更新操作中，如果有5行记录被修改，则行级触发器将被调用5次。
行级触发器更适合需要针对每一行数据进行特定处理的场景。
(2) 数据访问方式
- 语句级触发器
可以引用整个操作涉及的数据作为一个集合。比如，可以通过REFERENCING NEW TABLE AS或REFERENCING OLD TABLE AS来访问所有新插入、删除或更新后的数据集。
更适合执行聚合计算或者对整个操作的影响进行评估。
- 行级触发器
主要通过REFERENCING NEW ROW AS或REFERENCING OLD ROW AS来访问当前操作所影响的单行数据。
更适合需要对每条记录分别进行操作或检查的场景。
(3) 使用场景示例
- 语句级触发器
示例：在一个学生成绩表中，如果某课程的成绩更新后导致平均成绩低于某个阈值，则回滚整个更新操作。这种情况使用语句级触发器比较合适，因为它可以根据整个操作的结果来决定是否撤销更改。
- 行级触发器
示例：当学生注册一门课程时，自动为其创建相应的考试记录。在这种情况下，对于每个新增的学生记录，都需要单独创建一条对应的考试记录，因此行级触发器更为适用。
Triggers were used earlier for tasks such as
- Maintaining summary data (e.g., total salary of each department)
- Replicating databases by recording changes to special relations (called change or delta relations) and having a separate process that applies the changes over to a replica 实现数据库副本同步
There are better ways of doing these now:
- Databases today provide built in materialized view facilities to maintain summary data 现代数据库提供了内置的物化视图功能，可以自动维护和更新汇总数据。与触发器相比，这种方法更高效、更易于管理。
- Databases provide built-in support for replication，现代数据库系统通常提供内置的复制机制，能够自动同步主数据库和副本之间的数据变化，无需手动编写触发器和额外的处理过程
Encapsulation facilities can be used instead of triggers in many cases
- Define methods to update fields 定义一些方法来更改
- Carry out actions as part of the update methods instead of through a trigger 直接讲要做的事情放到 update 中即可
Risk of unintended execution of triggers, for example, when
- loading data from a backup copy 当你从备份恢复数据时，可能无意中触发了某些触发器，导致不必要的操作或数据不一致。
- replicating updates at a remote site在进行跨站点的数据同步时，触发器可能会被意外激活，影响数据的一致性和完整性
在执行上述操作之前，可以先禁用触发器，以避免非预期的行为。完成相关操作后再重新启用触发器。
Other risks with triggers:
- Error leading to failure of critical transactions that set off the trigger 如果触发器中存在错误逻辑，它可能会中断依赖该触发器的关键事务，从而导致数据处理失败或系统异常。
- Cascading execution 触发器可能引发级联效应，即一个触发器的执行可能会触发另一个触发器，形成连锁反应。这可能导致难以预测和控制的行为，增加系统复杂性并可能引起性能问题。
评论区
对你有帮助的话请给我个赞和 star =>
欢迎跟我探讨！！！