【数据库原理、编程与性能】Programming to access a Database

文章目录

• 1. Introduction to Embedded SQL in C

• 1.1 Declare section
• 1.2 Connect/Disconnect

• 1.2.1 Connect
• 1.2.2 Disconnect

• 1.3 Commit / Rollback
• 1.4 Whenever
• 1.5 一个实例：
• 1.6 Host Variable

• 1.6.1 Definition
• 1.6.2 说明
• 1.6.3 用法
• 1.6.4 实例
• 1.6.5 Indicator Var

• 2. Common Embedded SQL statements

• 2.1 Cursor

• 2.1.1 Using Method
• 2.2.2 实例

• 2.2 Select
• 2.3 Delete

• 2.3.1 Two forms of Delete statement
• 2.3.2 Embedded Delete statement
• 2.3.3 实例

• 2.4 Update

• 2.4.1 Two forms of Update statement
• 2.4.2 Forms of Embedded Searched Update statement
• 2.4.3 Forms of Embedded Positioned Update statement

• 2.5 Insert
• 2.6 Whenever

• 3. Programming for Transaction

• 3.1 Transaction property ： referred as “ACID” properties
• 3.2 Transaction的ACID特性遭破坏的可能因素

• 4. Lock

• 4.1 Classification of the Lock
• 4.2 Diagnose Dead lock

1. Introduction to Embedded SQL in C

• 句首：EXEC SQL

EXEC SQL SELECT [ALL|DISTINCT] expression{, expr…} INTO host_variable { , host_variable… } FROM tableref [corr_name] {, tableref[corr_name] …} [WHERE search_condition]

• 分类

• Declaration Statement（说明性语句）
• Executable Statement（可执行语句）

1.1 Declare section

exec sql begin declare section;

···声明部分（host variable）···

exec sql end declare section;

eg:

exec sql begin declare section;
char c_id[5] = "c001", c_name[14];
float c_discnt;	
exec sql end declare section;

1.2 Connect/Disconnect

1.2.1 Connect

连接ORACLE数据库：exec sql connect :user_name identified by :user_pwd;

1.2.2 Disconnect

断开与ORACLE数据库连接：

• exec sql disconnect connect_name;
• exec sql disconnect current;

1.3 Commit / Rollback

断开连接时必须对已成功执行的事务执行Commit语句，对不成功执行得事务执行Rollback语句，否则将断开连接失败。

• 对于成功执行的事务：

• Commit和Disconnect的组合：

exec sql commit work; exec sql disconnect current;

• ORACLE的特殊语法（同时执行Commit和Disconnect）:

exec sql commit release;

• 对于执行失败的事务：

• Rollback和Disconnect的组合：

exec sql rollback work; exec sql disconnect current;

• ORACLE的特殊语法（同时执行Rollback和Disconnect）:

exec sql rollback release;

1.4 Whenever

对于嵌入式SQL有可能出现的错误，我们可以使用Whenever语句进行控制：

exec sql include sqlca//为特定的错误和统计分配空间（SQL Communication Area） exec sql whenever sqlerror goto report_error;//发生错误时跳转至report_error处

1.5 一个实例：

下面的嵌入式SQL程序中，程序会不断提示用户输入一个顾客的cid，显示顾客的名字和应有的折扣作为回答。

#include <stdio.h>
#include "prompt.h"
exec sql include sqlca;

char  cid_prompt [] = "Please enter customer id:"; 
int main(){
    exec sql begin declare section;//声明部分
    char cust_id[5], cust_name[l4];
    float cust_di scnt;
    char user_name[20], user_pwd[20];
    exec sql end declare section;
    exec sql whenever sqlerror goto report_error;//错误跳转
    exec sql whenever not found goto notfound; //查询失败跳转
    strcpy(user_name,  "poneilsql");
    strcpy(user _pwd.   "XXXX"); 
    exec sql connect :user_name identified by :user_pwd;//连接数据库
    while((prompt(cid_prompt, 1, cust_id, 4))  > 0)//输入cust_id
    {
        exec sql select cname, discnt into :cust_name, :cust_discnt 
        from customers where cid = :cust_id;//查询
    	exec sql commit work;//正确执行
    	printf("Customer's name is %s and discount is %5.lf\n", cust_name, cust_discnt);
    	continue;  
    	notfound:   printf("Can't find customer %s", cust_id);//查询失败执行
	}
	exec sql commit release; //事务执行正常，断开连接
	return 0;
	report_error:	print_dberror() ://错误报告
    exec sql rollback release; //事务执行失败，回退并断开连接
    return  1;
}

1.6 Host Variable

1.6.1 Definition

• Input host variable ：由主语言对其赋值，在 SQL 语句中引用。
• Output host variable ：由 SQL 语句对其赋值，在主语言中引用。
• Indicator variable ( 指示变量) ：为整型变量，跟在(I/O) Host Var之后 ，用来“指示”主变量值的可使用条件。

1.6.2 说明

exec sql begin declare section;
char cust_id; c_dis;
exec sql end declare section;

1.6.3 用法

• 在 SQL句中使用时，前面要 加冒号(:)作为标志，且指示变量
  应紧跟在主变量之后。
• 在 Host Language 句中使用， 不必加冒号。

1.6.4 实例

exec sql begin declare section
char cust_id, c_dis;
integer c_ind;
exec sql end declare section
cust_id=‗c002‘;
exec sql select discnt into :c_dis :c_ind from customers
where cid=:cust_id;
if (c_ind==0) printf(―The discount of %s is %d !\n‖, cust_id, c_dis);
……

1.6.5 Indicator Var

跟在某个Host-Varible 之后，指示 SQL 执行后，此Host Varible 值的合法性，其自身值也有三种可能。

• = 0 ， not null, the value is assigned to the host-varible.
• > 0 ， a truncated value is assigned to the host-varible.
• = -1 ， is null, the host-varible value is not a meaningful.

2. Common Embedded SQL statements

2.1 Cursor

2.1.1 Using Method

• 定义游标 (declare cursor)

exec sql declare <cursor_name> cursor for <Subquery> [Read only | For Updata of colname];

• 打开游标 (open cursor)

exec sql OPEN <cursor_name>;

• 推进游标 (fetch cursor)，并将当前指向的buffer 数据放入主变量中

exec sql FETCH < cursor_name > INTO <host_var1> {,<host_var2>…};

• 关闭游标 (close cursor)

exec sql CLOSE < cursor_name >;

2.2.2 实例

a GROUP BY Select statement listing agent ID values and sum of dollar orders by these agents for any customer ID provided by the user.

#define TRUE 1
#include <stdio.h>
#include ―prompt.h
exec sql include sqlca;
exec sql begin declare section;
char cust_id[5], agent_id[4];
double dollar_sum;
exec sql end declare section;

int main()
{
    char cid_prompt[]="Please input customer id:";
    exec sql declare agent_dollars cursor for
        select aid,sum(dollars) from orders where cid = :cust_id group by aid;
    exec sql whenever sqlerror goto report-error; /* error condition */
    exec sql connect to testdb; /* for DB2 login */
    exec sql whenever not found goto finish; /* not found condition */
    while (prompt(cid_prompt,1,cust_id,4))>0) /*input cid get cname,discnt*/
    {
        exex sql open agent_dollars;
        while (TRUE) {
            exec sql fetch agent_dollars into :agent_id, :dollar_sum;
            printf("%s %11.2f\n", agent_id, dollar_dum);
        }
        finish: exec sql close agent_dollars ;
        exec sql commit work;
    }
    exec sql disconnect current ; /* Disconnect from database */
    return 0; /* Indicate success of program */
    report-error : print_dberror(); /* Print out error message */
    exec sql rollback work ;
    exec sql disconnect current ;
    return 1; /* Indicate failure of program */
}

2.2 Select

EXEC SQL SELECT [ALL|DISTINCT] expression{, expr…}
INTO host_variable { , host_variable… }
FROM tableref [corr_name] {, tableref[corr_name] …}
[WHERE search_condition ]

2.3 Delete

2.3.1 Two forms of Delete statement

• Searched Delete
• Positioned Delete(use cursor|我认为所有的Positioned ···都是为了保存被修改前的数据)

2.3.2 Embedded Delete statement

exec sql DELETE FROM tablename [corr_name]
[WHERE search_condition | WHERE CURRENT OF cursor_name];

2.3.3 实例

Delete all customers from the customers table who live in Duluth and have made no orders.

• In a Searched Delete

exec sql delete from customers c where c.city = ‗Duluth‘ and
not exists (select * from orders o where o.cid =c.cid) ;

• In a Positioned Delete

exec sql declare delcust cursor for
select cid from customers c where c.city = ‗Duluth‘ and
not exists (select * from orders o where o.cid = c.cid) for update of cid;
exec sql whenever not found goto skip;
exec sql open delcust;
while (TRUE) {exec sql fetch delcust into :cust_id;
exec sql delete from customers where current of delcust;}
skip : ……

2.4 Update

2.4.1 Two forms of Update statement

• Searched Update
• Positioned Update — using a cursor

2.4.2 Forms of Embedded Searched Update statement

exec sql UPDATE tablename [corr_name]
SET colname1 = expr { , colname2 = expr… }[WHERE search_condition];

2.4.3 Forms of Embedded Positioned Update statement

exec sql UPDATE tablename SET columnname=expr { , column=expr… }
WHERE CURRENT OF cursor_name ;

2.5 Insert

exec sql INSERT INTO tablename [ (columnname { , columnname…} ) ]
{VALUES (expr { , expr } ) | Subquery } ;

2.6 Whenever

exec sql WHENEVER condition action ;

• condition

• sqlerror
• not found
• sqlwarning

• action

• continue
• goto label
• stop
• do function

3. Programming for Transaction

• 用户定义的一组 DB 操作序列。这些操作：
• 要么全部操作成功，并永久性更新到DB 中
• 要么操作失败，放弃本次所有操作对DB 的更新， DB 恢复到未操作前的状态
• Database Transaction 一个不可分割的数据库运行单位（SQL是程序的书写单位）

3.1 Transaction property ： referred as “ACID” properties

• Atomicity: 事务是 DB 的逻辑运行单位。 ( 原子性)
• Consistency: 事务执行效果使DB从一致性稳态A 变到一致性稳态B 。 ( 一致性)
• Isolation: 事务内部的操作及所用数据，对其他并发事物是隔离的，不能互相干扰。 ( 隔离性)
• Durability: 事务一旦提交，它对DB 的Data 更新是永久性的，后续操作或故障对其执行结果没有任何影响。 ( 持续性)

3.2 Transaction的ACID特性遭破坏的可能因素

• 多个Transaction并行时，不同事物交叉访问相同Data，需控制并发；
• Transaction在运行时被强行中止，此时应做好恢复机制；

4. Lock

4.1 Classification of the Lock

• Read access lock（ R lock / share lock ）；
• Update or Write lock（W lock / exclusive lock）；

4.2 Diagnose Dead lock

• with Precedence Graph in RDBMS;
  Node—transaction; Edge—request Lock; Loop circuit—Dead Lock2)
• Checking with Counter in program;（在死锁中止的情况下进行重试事务）
• Solve the dead lock both in DBMS and program: Killing one of the transaction.