数据库编程

技术探索
🗨️字数统计=1.3k字 ⏳阅读时长≈4分钟

数据库编程就是用编程方法对数据库进行操纵的技术，SQL编程技术可以有效克服SQL语言实现复杂应用方面的不足（没有逻辑控制能力），提高应用系统和数据库管理系统间的互操作性。

使用 SQL 编程来访问和管理数据库中数据的方式主要有：

  • 嵌入式 SQL （ESQL）
  • 过程化 SQL （PL/SQL）
  • 存储过程和自定义函数
  • 开放数据库互连（ODBC）
  • Java 数据库连接 （JDBC）

一、嵌入式 SQL

1. 嵌入式 SQL 的处理过程

嵌入式 SQL 就是将 SQL 语句嵌入程序语言中，被嵌入的程序设计语言比如 Java 就称为宿主语言 / 主语言。

数据库对 嵌入式 SQL 一般采取预编译方法处理，识别出嵌入式 SQL 语句，将其转换为主语言调用语句。

为了能快速区分 SQL 语句与主语言语句，所有 SQL 语句必须加前缀。

  • 主语言 C

    1
    EXEC SQL <SQL语句>;

  • 主语言 Java

    1
    # SQL {<SQL语句>};

2. 嵌入式 SQL 语句与主语言之间的通信

SQL 语句负责操纵数据库，宿主语言负责控制逻辑流程。这时程序中会含有两种不同计算模型的语句，它们之间该如何通信呢？

  • SQL 语句执行后，系统将包含描述系统当前状态和运行环境的各种数据送到 SQL 通信区，应用程序从 SQL 通信区取出这些状态信息，据此决定接下来执行的语句

  • 主语言向 SQL 提供参数，主要用主变量实现

    SQL 语句中使用的宿主语言的程序变量称为主变量

  • 将 SQL 语句的处理结果交给宿主语言处理，主要用主变量和游标实现

    游标是面向集合的，一条 SQL 语句可以产生或处理多条记录；而主语言是面向记录的，一组主变量一次只能存放一条记录。为此引入游标来协调这两种不同的处理方式

3. 游标

使用游标的四个步骤：

  • 声明游标，这个过程没有实际检索出数据；

    1
    2
    3
    4
    DECLARE 游标名 CURSOR FOR SELECT语句;

    declare mycursor cursor for
            select col1 from mytable;

  • 打开游标；

    1
    open 游标名

  • 推进游标，取出数据；

    1
    FETCH 游标名 INTO 主变量 [指示变量] [,主变量 [指示变量]]

    [ ] 表示可选的

    主变量就是 select 语句查询的对应列名

    1
    2
    fetch mycursor into ret;
    select ret;

  • 关闭游标；

    1
    close 游标名

二、过程化 SQL

1. 定义和执行

定义

1
DEClARE 变量/游标/常量/异常等

执行

1
2
3
4
5
BEGIN
	SQL语句、过程化SQL的流程控制语句
EXCEPTION
	异常处理部分
END;

2. 流程控制

① 条件控制语句

IF 语句

1
2
3
IF 条件 THEN
	SQL语句;
END IF;

IF - ELSE 语句

1
2
3
4
5
IF 条件 THEN
	SQL语句;
ELSE
	SQL语句;
END IF;

嵌套的 IF 语句

THEN和ELSE子句中还能再包含 IF 语句

② 循环控制语句

LOOP

1
2
3
LOOP
	SQL 语句;
END LOOP;

WHILE-LOOP

1
2
3
WHILE 条件 LOOP
	SQL 语句;
END LOOP;

FOR-LOOP

1
2
3
FOR count IN [REVERSE] 循环下界1 循环上界2 LOOP
	SQL 语句;
END LOOP;

小于循环下界或者大于循环上界就退出循环，指定了 REVERSE 则反之


三、存储过程

存储过程是由 过程化 SQL 编写的过程，这个过程经编译和优化后存储在数据库服务器中，因此称它为存储过程，使用时只要调用即可

使用存储过程的好处：

  • 代码封装，保证了一定的安全性；
  • 代码复用；
  • 由于是预先编译，因此具有很高的性能。

命令行中创建存储过程需要自定义分隔符，因为命令行是以 ; 为结束符，而存储过程中也包含了分号，因此会错误把这部分分号当成是结束符，造成语法错误。可以使用 delemiter 自定义结束符

包含 in 输入参数（默认）、out 输出参数 和 inout 输入/输出参数 三种参数。

给变量赋值都需要用 select into 语句。

创建存储过程：

1
2
create procedure myprocedure(参数)
as 过程化SQL块

执行存储过程：

1
CALL/PERFORM PROCEDURE 过程名(参数1,参数2...);

删除存储过程：

1
DROP PROCEDURE 过程名();

存储过程示例：

从账户1转账到账户2，Account(Accountnum 账户号码, Total 余额)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
# 参数：转入账户、转出账户、转账额度
create or replace procedure transfer(inAccount int, outAccount int, amount float)
as declare
	totalDepositOut float; // 转出账户的额度
	totalDepositIn float; //转入账户的额度
	inAccountnum int; // 转入账户号码
BEGIN
	select Total into totalDepositOut from Account where Accountnum = outAccount
	IF totalDepositOut IS NULL THEN //如果转出账户没有存款
    	ROLLBACK;
    	RETURN;
    END IF;
	IF totalDepositOut < amount THEN // 转出账户余额 < 需转额度
		ROLLABCK;
		RETURN;
	END IF;
	SELECT Accountnum INTO inAccount From Account
	WHERE Accountnum = inAccount; //给参数 转入账户 赋值
	IF inAccountnum IS NULL THEN // 转入账户不存在
		ROLLBACK;
		RETURN;
	END IF;
	UPDATE Account SET Total = Total - amount 
	WHERE Accountnum = outAccount; //修改转出账户余额
	UPDATE Account Set Total = Total + amount
	WHERE Accountnum = inAccount; //修改转入账户余额
	COMMIT;
END;
分享到