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

教学重点：

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

触发器
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
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
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
catch (SQLException sqle) {
System.out.println("SQLException : " + sqle)} }
try {
/* execute an update */ stmt.executeUpdate( "insert into instructor values (’77987’, ’Kim’, ’Physics’, 98000)");
} 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 */
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编写触发器
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 */ }
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
5.1.1 JDBC Code Example
public static void JDBCexample(String dbid, String userid, String passwd) {
try { /*load JDBC dirver*/
Class.forName ("oraclen a connection with the server */
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);
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

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();
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
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
Connection conn = DriverManager.getConnection( "jdbc:oracle:thin:@:2000:univdb", userid, passwd);
Statement stmt = conn.createStatement(); { … Do Actual Work ….} stmt.close(); conn.close(); }
Communication Complexity

Less instructions need to be transferred between the application and the database
Advantage of Functions and Procedures
Security
subsequently extended to other languages such as
C++, C#, and Visual Basic.
ADO (ActiveX Data Objects)and APIs are
alternatives to ODBC, designed for the Visual Basic and C# languages.