第二章关系数据库

Size: px

Start display at page:

Download "第二章关系数据库"

苍弥卞
5 years ago
Views:

1 第八章数据库编程

2 本章内容 : 应用系统如何对数据库进行操作嵌入式 SQL 存贮过程 SQL/API(Application Programming Interface) 一组函数和程序从宿主语言主程序中调用一个 SQL DBMS 库, 而 SQL 语句是这个调用的参数目前更多的数据库编程在使用这种方法 ODBC JDBC SQL/CLI: The library for C is called SQL/CLI = Call-Level Interface.

3 SQL 的形式嵌入式 Main(){... exec sql begin declare section; char co[10]; int id; exec sql end declare section; exec sql select company_name from customer where id = :id into co; }

4 SQL 的形式 SQL/API #include sqlcli.h SQLHSTMT hstmt; SQLPrepare(hstmt, Insert Into customer values( ) ); SQLExcute(hstmt);

5 SQL 概述 SQL 的形式嵌入式 SQL 能将 SQL 语句嵌入到高级语言 ( 宿主语言 ) 使应用程序充分利用 SQL 访问数据库的能力宿主语言的过程处理能力一般需要预编译, 将嵌入的 SQL 语句转化为宿主语言编译器能处理的语句

6 System Implementation Host Language + Embedded SQL Preprocessing Host Language + Function calls Host-language compiler SQL library Object-code program

7 嵌入式 SQL 将 SQL 访问数据库的能力, 与宿主语言的过程化处理的能力进行综合将 SQL 语句嵌入宿主语言中产生的问题 : 1) 宿主语言如何识别 SQL 语句? 2)SQL 语句如何识别宿主语言变量? 3) 如何进行数据交换? 4) 集合操作如何转换为记录操作?

8 嵌入式 SQL 形式 SQL 语句加上前缀, 区别于宿主语言的语句 Exec Sql <sql 语句 >; ex.:exec sql Delete From Student; 预编译将具有前缀的语句, 转换成宿主语言的调用语句由宿主语言的编译器生成目标码

9 嵌入式 SQL SQLCA SQLCA SQL Communication Area sql 通讯区域数据库工作单元宿主语言工作单元之间的通讯区域宿主语言 DB: 请求参数 DB 宿主语言 : 执行的状态信息出错信息 DB 宿主语言 :( 查询 ) 执行的结果

10 嵌入式 SQL SQLCA Sqlca.sqlcode (Sybase 数据库 ): error code 0: success < 0 : fail 100: row not found Sqlca.sqlstate: 标准 (ISO) 返回信息码 00000: no error 02000: could not be found

11 嵌入式 SQL 变量 SQL 语句如何识别宿主语言的变量? 主变量 ( 共享变量 ) 宿主语言语句嵌入的 SQL 语句 SQL 语句中主变量前加冒号 : 作为标志宿主变量仅用于宿主语言的语句中

12 嵌入式 SQL 变量主变量的定义 Exec Sql Begin Declare Setction; Exec Sql End Declare Setction;

13 嵌入式 SQL 游标 (Cursor) 数据库操作的结果分为单记录和多记录单记录 : 变量 select into 多记录 : 游标 ( 游标的定义, 打开和关闭 ) 游标的作用 : 将多行数据集转换为单行数据便于宿主语言处理

14 嵌入式 SQL 结构由宿主语言处理数据宿主变量共享变量由内嵌的 SQL 语句处理数据库的访问共享变量

15 嵌入式 SQL 结构 Exec sql Include Sqlca; Exec sql Begin declare Section; Char sno(5); Char cno(3); For(;;){ Exec sql Fetch C1 into :sno,:cno,:grade if(sqlca.sqlcode <> SUCCESS) break; printf( sno:%s,cno:%s:%d,sno,cno,grade); Int grade; } Exec sql End Declare Section; Exec sql Close C1; main(){ } Exec sql Declare C1 Cursor For Select sno,cno,grade From SC; Exec sql Open C1; 使用光标的情况

16 嵌入式 SQL 简单操作说明性语句数据定义语句数据控制语句涉及单行的数据操纵语句

17 嵌入式 SQL 简单操作说明性语句 Exec sql Begin Declare Setcion; Exec sql End Declare Setcion;

18 嵌入式 SQL 简单操作涉及变量的处理变量名以 : 标识, 区别于数据库中的对象 ( 表名视图名属性名...)

19 嵌入式 SQL 简单操作查询 Select Into < 共享变量名 >[< 指示变量 >], From Where Group By Having Order By... 指示变量 : 用来指示所指的主变量的值或条件, 例如主变量是否为空值? 是否被截断?

20 嵌入式 SQL 简单操作 Exec sql Select sno,cno,grade Into :sno,:cno,:grade:grade_id From Sc Where sno = :given_sno and cno = :given_cno; <0 示 grade 为 Null 指示变量

21 嵌入式 SQL 游标的使用对象返回多行数据的查询结果集根据查询结果修改或删除单个元组的操作 (Current 形式 )

22 嵌入式 SQL 游标的使用方法定义游标 Exec sql Declare < 游标名 > Cursor For < 查询块 >; 打开游标 Exec sql Open < 游标名 >; // 执行查询移动指针 / 获取数据 // 定位于第一行的前一行 Exec sql Fetch < 游标名 > Into < 共享变量名 > < 指示变量名 >,...

23 嵌入式 SQL 游标的使用判断是否到底 sqlca.sqlcode 关闭游标 Exec sql Close < 游标名 >;

24 嵌入式 SQL 游标的使用 Current 形式的 Update 定义游标 Exec sql Declare < 游标名 > Cursor for < 查询块 > For Update Of < 属性名 >; 打开游标 Fetch 游标 Update Exec sql Update Where Current Of < 游标名 >

25 嵌入式 SQL 游标的使用 Current 形式的 Delete 定义游标 Exec sql Declare < 游标名 > Cursor for < 查询块 >; 打开游标 Fetch 游标 Delete Exec sql delete From Where Current Of < 游标名 >

26 嵌入式 SQL 动态 SQL 运行时生成 SQL 语句, 无法预编译 Exec Sql Prepare Exec Sql execute Exec Sql Execute Immediate

27 存贮过程 SQL99 标准给出了基于 SQL 编程的子程序, 分为两种 : 存贮过程 ( 书上讲到的 ) 函数 ( 书上没有讲到 )

28 PL/SQL: 数据库存贮过程的语言变量常量的定义变量定义 : 变量名数据类型 [[NOT NULL]: 初值表达式常量定义 : 常量名数据类型 CONSTANT:= 常量表达式赋值语句变量名称 := 表达式

29 PL/SQL: 数据库存贮过程的语言基本结构定义部分 DECLARE 变量, 常量, 异常等说明执行部分 BEGIN EXCEPTION END

30 PL/SQL: 数据库存贮过程的语言各种控制语句 IF THEN END IF IF THEN ELSE END IF LOOP END LOOP WHILE END LOOP FOR IN LOOP END LOOP

31 存贮过程的创建, 执行和删除 CREATE Procedure 过程名 ([ 参数 1, 参数 2, ]) AS <PL/SQL> 块 CALL/PERFORM Procedure 过程名 ( 参数列表 ) 或 EXEC SQL CALL Procedure 过程名 DROP PROCEDURE 过程名 ()

32 存贮过程的实例 CREATE PROCEDURE TRANSFER (inaccount INT,outAccount INT,amount FLOAT) AS DECLARE totaldeposit FLOAT BEGIN.. END

33 存贮过程的优点运行效率高. 提供了在服务器端快速执行 SQL 语句的有效途径. 降低了客户机和服务器之间的通信量. 方便实施企业规则例如 :CALL Procedure TRANSFER( , ,1000)

34 存贮过程与函数 Procedure Declarations CREATE PROCEDURE <name>(<arglist>) local declarations; procedure body; Function Declarations CREATE FUNCTION <name> (<parameters>) RETURNS <type> local declarations function body; 函数不能 CALL 只是作为一个表达式放到适合返回值的地方.

35 异常处理实例 Where to go: 1) continue:execute the statement after the one that CREATE FUNCTION GetYear(t VARCHAR[255]) RETURNS raised the exception. INTEGER DECLARE Not_Found CONDITION FOR SQLSTATE ; block is executed next. DECLARE Too_Mamy 3) CONDITION Undo: not executed FOR the SQLSTATE ; BEGIN DECLARE EXIT HANDLER FOR Not_Found,Too_Many 2) Exit:leave the BEGIN END block.the statement after the statement within the block and exit like 2) RETURN NULL;// handler declaration RETURN (SELECT year FROM Movie WHERE title=t); END;

36 Components of Exception handler in PSM A list of exception conditions that invoke the handler when raised. Code to be executed when one of the associated exceptions is raised. An indication of where to go after the handler has finished its work. DELARE <where to go> HANDLER FOR <condition list> <statement>

37 存贮过程和游标的一个实例 CREATE PROCEDURE JoeGouge( ) DECLARE thebeer CHAR(20); DECLARE theprice REAL; DECLARE NotFound CONDITION FOR SQLSTATE ; DECLARE c CURSOR FOR (SELECT beer, price FROM Sells WHERE bar = Joe s Bar ); Used to hold beer-price pairs when fetching through cursor c Returns Joe s menu

38 游标的一个实例 : 过程体 BEGIN OPEN c; menuloop: LOOP FETCH c INTO thebeer, theprice; IF NotFound THEN LEAVE menuloop END IF; IF theprice < 3.00 THEN UPDATE Sells SET price = theprice+1.00 WHERE bar = Joe s Bar AND beer = thebeer; END IF; END LOOP; CLOSE c; END; Check if the recent FETCH failed to get a tuple If Joe charges less than $3 for the beer, raise it s price at Joe s Bar by $1.

39 ODBC 工作原理请求连接数据库发送 SQL 语句为结果分配存贮空间获取执行结果或错误信息进行数据处理并提交结果事务提交或回滚断开连接用户应用程序 ODBC API 标准接口驱动程序管理器 DRIVER 1 DB2 DRIVER 2 Oracle DRIVER3 Sybase

40 ODBC Data Structures C connects to the database by structs of the following types: 1. Environments : represent the DBMS installation. ( 环境句柄 ) 2. Connections : logins to the database. 3. Statements : SQL statements to be passed to a connection. 4. Descriptions : records about tuples from a query, or parameters of a statement.

41 应用程序句柄之间的关系 ODBC 应用程序环境句柄连接句柄数据源语句句柄描述符句柄

42 ODBC 的工作流程配置数据源初始化环境建立连接分配语句句柄有执行 SQL 语句有否结果无结果集处理结束

43 Handles 句柄 Function SQLAllocHandle(T,I,O) is used to create these structs, which are called environment, connection, and statement handles. T = type, e.g., SQL_HANDLE_STMT. I = input handle = struct at next higher level (statement < connection < environment). O = (address of) output handle.

44 Example: SQLAllocHandle SQLAllocHandle(SQL_HANDLE_STMT, mycon, &mystat); mycon is a previously created connection handle. mystat is the name of the statement handle that will be created.

45 Preparing and Executing SQLPrepare(H, S, L) causes the string S, of length L, to be interpreted as a SQL statement and optimized; the executable statement is placed in statement handle H. SQLExecute(H) causes the SQL statement represented by statement handle H to be executed.

46 Example: Prepare and Execute SQLPrepare(myStat, SELECT beer, price FROM Sells WHERE bar = Joe s Bar, SQL_NTS); SQLExecute(myStat); This constant says the second argument is a null-terminated string ; i.e., figure out the length by counting characters.

47 Direct Execution If we shall execute a statement S only once, we can combine PREPARE and EXECUTE with: SQLExecuteDirect(H,S,L); As before, H is a statement handle and L is the length of string S.

48 Fetching Tuples When the SQL statement executed is a query, we need to fetch the tuples of the result. A cursor is implied by the fact we executed a query; the cursor need not be declared. SQLFetch(H) gets the next tuple from the result of the statement with handle H.

49 Accessing Query Results When we fetch a tuple, we need to put the components somewhere. Each component is bound to a variable by the function SQLBindCol. This function has 6 arguments, of which we shall show only 1, 2, and 4: 1 = handle of the query statement. 2 = column number. 4 = address of the variable.

50 Example: Binding Suppose we have just done SQLExecute(myStat), where mystat is the handle for query SELECT beer, price FROM Sells WHERE bar = Joe s Bar Bind the result to thebeer and theprice: SQLBindCol(myStat, 1,, &thebeer,, ); SQLBindCol(myStat, 2,, &theprice,, );

51 Example: Fetching Now, we can fetch all the tuples of the answer by: while ( SQLFetch(myStat)!= SQL_NO_DATA { } /* do something with thebeer and theprice */ CLI macro representing SQLSTATE = = failed to find a tuple.

52 JDBC Java Database Connectivity (JDBC) is a library similar to SQL/CLI, but with Java as the host language. Like CLI, but with a few differences for us to cover.

53 Making a Connection import java.sql.*; The JDBC classes Class.forName(com.mysql.jdbc.Driver); Connection mycon = DriverManager.getConnection( ); Loaded by forname URL of the database your name, and password go here. The driver for mysql; others exist

54 Statements JDBC provides two classes: 1. Statement = an object that can accept a string that is a SQL statement and can execute such a string. 2. PreparedStatement = an object that has an associated SQL statement ready to execute.

55 Creating Statements The Connection class has methods to create Statements and PreparedStatements. Statement stat1 = mycon.createstatement(); PreparedStatement stat2 = mycon.createstatement( SELECT beer, price FROM Sells + WHERE bar = Joe s Bar ); createstatement with no argument returns a Statement; with one argument it returns a PreparedStatement.

56 Executing SQL Statements JDBC distinguishes queries from modifications, which it calls updates. Statement and PreparedStatement each have methods executequery and executeupdate. For Statements: one argument: the query or modification to be executed. For PreparedStatements: no argument.

57 Example: Update stat1 is a Statement. We can use it to insert a tuple as: stat1.executeupdate( ); INSERT INTO Sells + VALUES( Brass Rail, Bud,3.00)

58 Example: Query stat2 is a PreparedStatement holding the query SELECT beer, price FROM Sells WHERE bar = Joe s Bar. executequery returns an object of class ResultSet we ll examine it later. The query: ResultSet menu = stat2.executequery();

59 Accessing the ResultSet An object of type ResultSet is something like a cursor. Method next() advances the cursor to the next tuple. The first time next() is applied, it gets the first tuple. If there are no more tuples, next() returns the value false.

60 Accessing Components of Tuples When a ResultSet is referring to a tuple, we can get the components of that tuple by applying certain methods to the ResultSet. Method getx (i ), where X is some type, and i is the component number, returns the value of that component. The value must have type X.

61 Example: Accessing Components Menu = ResultSet for query SELECT beer, price FROM Sells WHERE bar = Joe s Bar. Access beer and price from each tuple by: while ( menu.next() ) { thebeer = Menu.getString(1); theprice = Menu.getFloat(2); /*something with thebeer and theprice*/}

62 小结 : 数据库编程嵌入式 SQL: 把 SQL 嵌入到传统编程语言中. 存贮过程 / 函数 : 提供一种标准的面向 SQL 的编程语言. 数据库之间互相连接的规范 ( 一组函数 ): ODBC,JDBC

Fun Time (1) What happens in memory? 1 i n t i ; 2 s h o r t j ; 3 double k ; 4 char c = a ; 5 i = 3; j = 2; 6 k = i j ; H.-T. Lin (NTU CSIE) Referenc

References (Section 5.2) Hsuan-Tien Lin Deptartment of CSIE, NTU OOP Class, March 15-16, 2010 H.-T. Lin (NTU CSIE) References OOP 03/15-16/2010 0 / 22 Fun Time (1) What happens in memory? 1 i n t i ; 2

第二章 关系数据库

第二章关系数据库