高级SQL

1. 程序设计语言访问SQL

1.1 概述

必要性

• SQL并没有提供通用语言的全部表达能力，需要通过高级语言C、Java或Python来编写实现
• 非声明式动作不能通过SQL实现，如打印一份报告、与用户实时交互、图形化展示等

方式

• 动态SQL（Dynamic）：通过一组函数（过程式语言）或方法（面向对象语言）来连接到数据库服务器并与之交互，允许程序在运行以字符串的形式构建SQL查询，并以元组的结构将结果存到程序变量中
• 嵌入式SQL（Embedded）：SQL语句在编译时采用预处理器来进行识别，并生成适当的数据库调用代码

1.2 JDBC

1.2.1 JDBC框架

// 引入库
import java.sql.*;
public class JdbcExample {
  public static void main(String[] args) {
    Connection conn = null;
    Statement statement = null;
    ResultSet result = null;
    try {
      // 1. 注册驱动
      Class.forName("com.mysql.cj.jdbc.Driver");
      // 2. 获取连接
      String url = "jdbc:mysql://localhost:3306/your_database";
      String user_name = "your_username";
      String password = "your_password";
      conn = DriverManager.getConnection(url, user_name, password);
      // 3. 获取连接对象
      statement = conn.createStatement();
      // 4. 创建 SQL 语句并执行
      String sql = "SELECT name FROM student;";
      result = statement.executeQuery(sql);
      // 5. 处理结果
      while (result.next()) {
        String name = result.getString("name");
        System.out.println(name);
      }
    } catch (ClassNotFoundException e) {
      e.printStackTrace();
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      // 6. 释放资源
      try {
        if (result != null) result.close();
        if (statement != null) statement.close();
        if (conn != null) conn.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }
}

1.2.2 预备语句

预备语句：用prepareStatement方法定义一个预备语句对象，其中用?占位符代替属性值，以便在执行时通过setXXX方法动态地提供实际值，并在最后使用excuteUpdate方法或executeQuery方法实现赋值

PreparedStatement pStmt = conn.prepareStatement("insert into instructor values(?,?,?,?)");
pStmtsetString(1,"88877");
pStmtsetString(2, "Perry");
pStmt.setString(3, "Finance");
pStmt.setint(4,125000);
pStmt.executeUpdate();
pStmt.setString(1,"88878");
pStmt.executeUpdate();

SQL注入（injection）：通过在输入中插入精心设计的恶意SQL代码，来操控数据库进行未授权的操作（窃取、修改、删除数据等）

• 如果用户输入username为admin，password为'password' OR '1'='1，那么查询语句就会变成：SELECT * FROM users WHERE username = 'admin' AND password = 'password' OR '1' = '1'，这个查询总是会返回真值，从而允许攻击者绕过身份验证，获取系统的访问权限
• 如果用户输入password为'password'; DROP table student;，那么查询语句就会变成SELECT * FROM users WHERE username = 'admin' AND password = 'password'; DROP table student;，这个查询可能会执行多一个操作，即删除student表

String username = request.getParameter("username");
String password = request.getParameter("password");
String sql = "SELECT * FROM users WHERE username = '" + username + "' AND password = '" + password + "'";
// 使用预备语句可以解决SQL注入问题
String sql = "SELECT * FROM users WHERE username = ? AND password = ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setString(1, username);
pstmt.setString(2, password);

1.2.3 元数据

利用Connection接口中的getMetaData方法获得DatabaseMetaData对象，该对象包含很多访问元数据的方法

// 获取数据库的产品名称
String productName = dbMetaData.getDatabaseProductName();
// 获取数据库的版本号
String productVersion = dbMetaData.getDatabaseProductVersion();
// 获取当前连接的用户名
String userName = dbMetaData.getUserName();
// 获取数据库中的表信息
ResultSet tables = dbMetaData.getTables(catalog, schemaPattern, tableNamePattern, types);
// 获取表的列信息
ResultSet columns = dbMetaData.getColumns(catalog, schemaPattern, tableNamePattern, columnNamePattern);
// 获取表的主键信息
ResultSet primaryKeys = dbMetaData.getPrimaryKeys(catalog, schema, table);
// 获取数据库中的所有模式（schema）
ResultSet schemas = dbMetaData.getSchemas();

ResultSetMetaData提供了关于ResultSet对象中数据的结构的信息

// 获取列的数量
int columnCount = resultSetMetaData.getColumnCount();
// 获取指定列的名称
String columnName = resultSetMetaData.getColumnName(column);
// 获取指定列的数据类型
int columnType = resultSetMetaData.getColumnType(column);
// 获取指定列的 SQL 类型名称
String columnTypeName = resultSetMetaData.getColumnTypeName(column);
// 判断指定列是否可以为 null
int nullable = resultSetMetaData.isNullable(column);

1.3 嵌入式SQL

基本格式：EXEC SQL <SQL语句>;

主变量：嵌入式SQL语句中可以使用主语言的程序变量来输入或输出数据，需在主变量名前加冒号:来区分主变量和SQL变量

• 输入主变量：由应用程序对其赋值

• 输出主变量：由SQL语句对其赋值

• 程序首部

  • 定义通信区：使用EXEC SQL INCLUDE SQLCA定义用于在程序和DBMS之间通信的通信区
  • 声明主变量：主变量在EXEC SQL BECIN DECLARE SECTION;和EXEC SQL END DECLARE SECTION;之间进行声明

• 程序体

  • 建立数据库连接：EXEC SQL CONNECT:<用户名> identified by:<用户口令> using:<数据库服务器路径>;
  • 定义游标：EXEC SQL DECLARE <游标名> CURSOR FOR <SELECT语句>;

游标是用来给主语言程序遍历查询结果的元组

#include<stdio.h>
// 定义通信区
exec sql include sqlca;
int main() {
  // 定义主变量
  exec sql begin declare section;
  char cust_id[5];
  char cust_name[14];
  float cust_discnt;
  exec sql end declare section;
  // 错误处理
  exec sql whenever sqlerror goto report_error;
  // 连接数据库
  exec sql connect:"customer" identified by:"xxxxxx" using:"url";
  // 用户输入
  printf("Please enter customer ID: ");
  scanf("%5s", cust_id);
  // 定义游标
  exec sql declare c1 cursor for select cname, discnt from customers where cid = :cust_id;
  // 打开游标
  exec sql open c1;
  // 处理结果集
  while (1) {
    // 获取下一行
    exec sql fetch c1 into :cust_name, :cust_discnt;
    // 检查是否还有结果
    if (sqlca.sqlcode != 0)
      break;
    // 显示查询结果
    printf("Customer's name is %s and discount is %.1f\n",cust_name, cust_discnt);
  }
  // 关闭游标
  exec sql close c1;
  // 断开数据库连接
  exec sql commit release;
  return 0;
  // 错误处理
  report_error:
    // 回滚事务
    exec sql rollback;
    // 断开数据库连接
    exec sql disconnect;
    return 1;
}

2. 业务逻辑

2.1 函数（Function）

接收输入参数并返回一个单一的值

-- 输入系名，返回该系的老师总数
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_count.dept_name
    return d_count;
  end

表函数的返回值是一个表，可以被看作是参数化视图

-- 输入系名，返回该系每个老师的信息
create function instructor_of(dept_name varchar(20))
  returns table(
    ID varchar(5),
    name varchar(5),
    dept_name varchar(20),
    salary numeric(8,2)
  )
  return table(
    select ID,name,dept_name,salary
    from instructor
    where instructor.dept_name = instructor_of.dept_name
  );

2.2 过程（Procedure）

接收输入参数并执行逻辑，是一组SQL语句的集合

create procedure dept_count(
  in dept_name varchar(20), 
  out d_count integer
)
begin
  select count(*) into d_count
  from instructor
  where instructor.dept_name = dept_count.dept_name;
end

2.3 区别

性质	函数	过程
返回值	一个值（标量或表）	零个或多个值，通常保存在输出参数中
参数	只有输入参数，没有输出参数	可以有多个输入参数和输出参数
调用方式	直接在SQL查询中调用	通过call语句调用
适用场景	计算某个表达式	复杂逻辑和组合操作
异常处理	通常不涉及异常处理	支持对异常情况的信号发送，以及对处理异常的句柄的声明

2.4 语言结构

-- while循环
while 布尔表达式 do
  语句序列;
end while

-- repeat循环
repeat
  语句序列;
until 布尔表达式
end repeat

-- for循环
for 计数 in 次数 do
  语句序列
end for

-- if判断
if 布尔表达式 then
  语句序列;
elseif 布尔表达式 then
  语句序列;
else
  语句序列;
end if

-- 异常情况
declare exit handler for sqlstate '异常代码'
  begin
    select '异常输出' as error_message;
  end

3. 触发器

触发器（trigger）：在特定的事件发生时自动执行预定义的语句

用途

• 自动化业务规则和逻辑
• 维护完整性约束
• 记录日志

组成

• 触发事件：触发器在什么事件发生时被激活
• 触发时间：触发器在事件之前还是事件之后执行
• 触发动作：触发器执行的具体操作

类型

• BEFORE 触发器：在执行 INSERT、UPDATE 或 DELETE 操作之前触发
• AFTER 触发器：在执行 INSERT、UPDATE 或 DELETE 操作之后触发
• INSTEAD OF 触发器：用于视图，当对视图执行操作时，代替该操作

数值

• new row：表示对数据表进行 INSERT 或 UPDATE 操作时的新行值
• old row：表示对数据表进行 DELETE 或 UPDATE 操作时的旧行值

-- 定义触发器名称，触发时间，触发事件
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;