数据库系统概念(英文精编版.第六版)ch5 Advanced SQL

Embedded SQL Example (C language)
EXEC SQL INCLUDE SQLCA; EXEC SQL BEGIN DECLARE SECTION; /* declare host variables */ CHAR name(20); CHAR dept_name(10); EXEC SQL END DECLARE SECTION; main() { EXEC SQL DECLARE C1 CURSOR FOR /* declare a cursor */ SELECT name, dept_name FROM instructor; EXEC SQL OPEN C1; /* open the cursor */ while(1) { EXEC SQL FETCH C1 INTO :name, :dept_name; /* fetch */ if (sqlca.sqlcode <> SUCCESS) break; printf(“instructor’s name: %s, deptment’s name: %s\n”, :name, :dept_name); } EXEC SQL CLOSE C1; /* close the cursor */ }
ADO.NET
using System, System.Data, System.Data.SqlClient;
SqlConnection conn = new SqlConnection( “Data Source=<IPaddr>, Initial Catalog=<Catalog>”); /*Open a connection*/ conn.Open(); SqlCommand cmd = new SqlCommand(“select * from students”,conn); /* send SQL commands to the database */ SqlDataReader rdr = cmd.ExecuteReader(); while(rdr.Read()) { Console.WriteLine(rdr[0], rdr[1]); /* Print result attributes 1 & 2 */ } rdr.Close(); conn.Close();
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(); }
教学重点：

数据库函数、过程
数据库触发器
教学难点：

触发器
5.1 Accessing SQL From a Programming Language
Dynamic SQL

Connect to and communicate with a database server using a collection of functions of methods. allows programs to construct and submit SQL queries at run time
present at compile time
An embedded SQL program must be processed by a
special preprocessor prior to compilation.
The SQL standard defines embeddings of SQL in a
Biblioteka Baidu
5.2 Function and Procedure
Advantage of Functions and Procedures
Modularization 模块化

Can be called by different applications If the business rules change, we only need to change the function/procedure, instead of the applications
API (application program interface) for a program to
interact with a database server
ODBC (Open Database Connectivity), is an API
originally developed for the C language, and
variety of programming languages such as C, Java, and Cobol.
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
SQLBindCol(stmt, 1, SQL_C_CHAR, deptname, 80, &lenOut1); SQLBindCol(stmt, 2, SQL_C_FLOAT, &salary, 0 , &lenOut2); /* fetch the values */ while (SQLFetch(stmt) == SQL_SUCCESS) { printf (" %s %g\n", deptname, salary); } } SQLFreeStmt(stmt, SQL_DROP);
Communication Complexity

Less instructions need to be transferred between the application and the database
Advantage of Functions and Procedures
Security
SQLConnect(conn, “db.yale.edu", SQL_NTS, “John", SQL_NTS, “123456", SQL_NTS); /* open a connection with the server */ { …. Do actual work … } SQLDisconnect(conn); SQLFreeConnect(conn); SQLFreeEnv(env); }
catch (SQLException sqle) {
System.out.println("SQLException : " + sqle)} }
try {
/* execute an update */ stmt.executeUpdate( "insert into instructor values (’77987’, ’Kim’, ’Physics’, 98000)");
Chapter 5: Advanced SQL
5.1 Accessing SQL From a Programming
Language
5.2 Function and Procedures 5.3 Triggers
Chapter 5: Advanced SQL
教学目的:

了解数据库API 掌握用SQL编写函数和过程 掌握触发器的概念，用SQL编写触发器
while (rset.next()) { System.out.println( rset.getString(“dept_name") + " " + rset.getFloat(2));
}
5.1.3 Embedded SQL
Embedded SQL statements must be completely
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); /* send SQL commands to the database */ error = SQLExecDirect(stmt, sqlquery, SQL_NTS); if (error == SQL_SUCCESS) { /* bind C language variables to attributes of the query result */
Embedded SQL

Provides a means by which a program can interact with a database server. statements must be completely present at compile time
Dynamic SQL
JDBC (Java Database Connectivity) works with Java
5.1.2 ODBC Code Example
void ODBCexample() { RETCODE error; HENV env; HDBC conn;
SQLAllocEnv(&env); //allocate an SQL environment SQLAllocConnect(env, &conn); //allocate connection handel
subsequently extended to other languages such as
C++, C#, and Visual Basic.
ADO (ActiveX Data Objects)and ADO.NET APIs are
alternatives to ODBC, designed for the Visual Basic and C# languages.
} catch (SQLException sqle) {
System.out.println("Could not insert tuple. " + sqle); }
/* execute an query */
ResultSet rset = stmt.executeQuery( "select dept_name, avg(salary) from instructor group by dept_name"); /* fetch one tuple at a time */

Authorization of functions/procedures can be granted or revoked
Performance

Functions/procedures are optimized
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
5.1.1 JDBC Code Example
public static void JDBCexample(String dbid, String userid, String passwd) {
try { /*load JDBC dirver*/
Class.forName ("oracle.jdbc.driver.OracleDriver"); /* open a connection with the server */