Chapter 5: Advanced SQL

Accessing SQL from a Programming Language

API (application program interface) to interact with DB server

Application conducts data processing, and makes calls to

  • Connect with the database server
  • Send SQL commands to the database server
  • Fetch tuples of result one-by-one into program variables

There are two approaches to accessing SQL

  1. Dynamic SQL
    • General-Purpose program: connects to and communicates with DB server using functions
    • Program constructs an SQL query as a character string, submit the query, and retrieve result into program variables a tuple at a time.
    • Includes JDBC with Java and ODBC with C, C++, etc.
  2. Embedded SQL
    • Statements are translated in high-level programs at compile time into function calls
    • Function calls connect to DB using an API that provides dynamic SQL facilities

JDBC

JDBC is a Java based API.

  • Supports features for querying and updating data, and for retrieving query results
  • Supports metadata retrieval (querying about relations in DB and querying about names and types of relation attributes)

Also known as the model for communicating with DB:

  • Open a connection, named as conn
    • Using the getConnection method of the DriverManager class
    • Parameters: (URL, user_identifier, password)
  • Create a SQL statement object on the connection conn
    • Using the createStatement method
    • Its statement handler is named as stmt
    • Invoke methods that ship an SQL statement foe execution
  • Update to database
    • Using the stmt.executeUpdate("<SQL>")
  • Execute query and fetch and print results
    • ResultSet object

Note: execute queries using Statement object stmt to send queries and fetch results

  • Using execute.query or execute.update such as **insert/delete/update/create table`
  1. Parameters: SQL statement to be executed, represented as a string
  2. Catch any exceptions or error conditions
  3. Fetch the query result, using the try {...}/catch {...} construct
    • Retrieve the set of tuples in the result into a ResultSet object rset, fetch them on one tuple at a time
    • The next() method tests whether or nor the result set has at least one tuple and if so, fetches it
  4. Close the statement stmt and the connection conn at the end

JDBC Code Details

  • Getting result fields:
    • rs.getString("dept_name") and rs.getString(1) are equivalent, if dept_name is the first argument of select result
  • Dealing with null values
    • rs.wasNULL()

JDBC Subsections

  1. Prepared statements
    • PreparedStatement pStmt = conn.prepareStatement("insert into instructor values(?, ?, ?, ?)");
    • pStmt.setString(1, "88877");
  2. Metadata Features
    • After executing query to get a ResultSet rs
    • ResultSetMetaDate rsmd = rs.getMetaData();
  3. Transaction Control
    • Each SQL statement is treated as a separate transaction that is committed automatically by default
    • Turn off automatic commit on a connection: conn.setAutoCommit(false);
    • Note: transactions must then be committed or rolled back explicitly
      • conn.commit(); and conn.rollback();
  4. Calling functions and procedures
    • CallableStatement cStmt1 = conn.prepareCall("{? = call some function(?)}");
    • CallableStatement cStmt2 = conn.prepareCall("{call some procedure(?, ?)}");

ODBC

Open database connectivity standard

  • Application programs (as client) to communicate with DB server
  • API to
    • Open a connection with database
    • Send queries and updates
    • Get back results

Embedded SQL

Embedded SQL

  • In general-purpose programming languages, e.g. C
  • Executing of general-purpose programming language programs with SQL statement embedded results in DB access

Def. A language to which SQL queries are embedded is referred to as host language. The SQL structures permitted in host language comprise embedded SQL

  • EXEC SQL statement is used to identify embedded SQL request to the preprocessor
    • EXEC SQL <embedded SQL statement>;

Note: this varies by language:

  • In some languages, like COBOL, the semicolon is replaced with END-EXEC
  • In Java embedding use # SQL{...};
  • Before executing any SQL statements, the program must first connect to the database.
    • EXEC SQL connect to <server> user <user_name> using <password>;

Note: server identifies the server to which connection is to be established

  • Variables of the host language can be used within embedded SQL statements. They are preceded by colon to (:) to distinguish from SQL variables, e.g. :credit_amount

Note: variables used as above must be declared within DECLARE section

The syntax for declaring the variables follows the usual host language syntax.

1
2
3
EXEC SQL BEGIN DECLARE SECTION
  int credit_amount;
EXEC SQL END DECLARE SECTION;

Example, from a host language, find the ID and name of students who have completed more than the number of credits stored in variable credit_amount in the host language. (credi_amount is the shared variable defined in the declaration part)

1
2
3
4
5
6
7
8
9
10
11
12
13
EXEC SQL BEGIN DECLARE SECTION;
int credit_amount;
EXEC SQL END DECLARE SECTION;

credit_amount = 100;

EXEC SQL CONNECT TO my_database USER 'username' USING 'password';

EXEC SQL
select ID, name
from student
where tot_cred > :credit_amount
END-EXEC

Cursor in Embedded SQL

To write an embedded SQL query, we use the statement: declare c cursor for <SQL query>. The variable c is used to identify the query.

1
2
3
4
5
6
EXEC SQL
declare c cursor for
select ID, name
from student
where tot_cred > :credit_amount
END-EXEC

When using Embedded SQL for queries, if the query result includes multiple tuples, it cannot be directly passed to the host program through shared variables. In this case, the system allocates a dedicated working area to store the query result relation and uses a cursor to point to this area. The host program retrieves each tuple from the result relation sequentially by using the cursor with OPEN, FETCH, and CLOSE operations.

  • The OPEN statement executes the query and save the results **within a temporary relation`
  • The FETCH statement causes the values of one tuple in the query result to to be replaced on host language variables. Repeated calls to fetch get successive tuples in the query result.
  • The CLOSE statement delete the temporary relation that holds the result of the query

Note: above details vary with language.

Example

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
EXEC SQL BEGIN DECLARE SECTION
int credit_amount;
char si, sn;
EXEC SQL END DECLARE SECTION

credit_amount = 100;

EXEC SQL
declare c cursor for
select ID, name
from student
where tot_cred > :credit_amount
END-EXEC

EXEC SQL open c END-EXEC
EXEC SQL fetch c into :si, :sn END-EXEC
EXEC SQL close c END-EXEC

Update Through Embedded SQL

Can update tuples fetched by cursor by declaring that the cursor is for update

1
2
3
4
5
6
7
EXEC SQL
declare c cursor for
select *
from instructor
where dept_name = 'Music'
fo update
END-EXEC

We iterate through the tuples by performing fetch operations on the cursor, after fetching each tuple we execute the following code:

1
2
3
4
5
EXEC SQL
update instructor
set salary = salary + 1000
where current of c
END-EXEC

Functions and Procedures

SQL supports functions and procedures

  • Functions and procedures can be written in SQL itself, or an external programming language (C, Java)
  • Functions written in external languages are useful with specialized data types such as images
  • DBS support table-valued functions which can return a relation as a result.

SQL Functions

For example, for the given the name of a department, returns the count of the number of instructors.

1
2
3
4
5
6
7
8
9
CREATE FUNCTION dept_count(dept_name VARCHAR(20))
RETURNS INTEGER
BEGIN
    DECLARE dept_count INTEGER;
    SELECT COALESCE(COUNT(*), 0) INTO dept_count
    FROM instructor
    WHERE instructor.dept_name = dept_name;
    RETURN dept_count;
END;

SQL Procedures

The dept_count function could be written as procedure.

1
2
3
4
5
6
CREATE PROCEDURE dept_count_proc(IN dept_name VARCHAR(20), OUT d_count INTEGER)
BEGIN
    SELECT COUNT(*) INTO d_count
    FROM instructor
    WHERE instructor.dept_name = dept_count_proc.dept_name;
END

Procedures can be invoked either from an SQL procedure or from embedded SQL, using the call statement. Procedures and functions can be invoked also from dynamic SQL.

1
2
DECLARE d_count INTEGER;
CALL dept_count_proc('Physics', d_count);

Trigger

Def. Trigger is an event-condition-action model based mechanism that is executed automatically as a side effect of modification to DB.

  • Integrity definition, checking, and remedy
  • Specify what events cause the trigger to be executed (insert, delete, update)
  • Under which conditions the trigger execution will proceed
    • Integrity constraints checking
  • Specify actions to be taken when the trigger executes
    • If constraints is violated, remedy actions are taken

Note: as an integrity control mechanism, trigger introduced to SQL-99, but supported even earlier using non-standard syntax.

Syntax illustrated here may not work exactly on your DBS

Triggering Events and Actions

Trigger event can be insert, delete or update.

Note:

  • Triggers on update can be restricted to specific attributes
  • Values of attributes before and after an update can be referenced

Triggers can be activated before an event, which can serve as extra constraints. For example, convert blank grades to null.

1
2
3
4
5
6
7
CREATE TRIGGER set_null_trigger BEFORE UPDATE OF takes
REFERENCING NEW ROW AS nrow
FOR EACH ROW
WHEN (nrow.grade = '')
BEGIN ATOMIC
    SET nrow.grade = null;
END;

Trigger to Maintain Referential Integrity

1
2
3
4
5
6
7
CREATE TRIGGER time_slot_check1 AFTER INSERT ON section
REFERENCING NEW ROW AS nrow
FOR EACH ROW
WHEN nrow.time_slot_id NOT IN(SELECT time_slot_id FROM timeslot)
BEGIN ATOMIC
    ROLLBACK
END;
1
2
3
4
5
6
7
CREATE TRIGGER time_slot_check2 AFTER DELETE ON timeslot
REFERENCING OLD ROW AS orow
FOR EACH orow b
WHEN orow.time_slot_id NOT IN(SELECT time_slot_id FROM timeslot) AND orow.time_slot_id IN(SELECT time_slot_id FROM section)
BEGIN ATOMIC
    ROLLBACK
END;

Statement Level Triggers

Instead of executing a separate action for each affected row, a single action can be executed for all rows affected by a transaction.

  • Use for each statement instead of for each row
  • Use referencing old table or referencing new table to refer to temporary tables (called transition table) containing the affected rows
  • It’s more efficient when dealing with updates a large number of rows

When not to Use Triggers

  • Triggers were used earlier for tasks such as
    • Maintaining summary data (e.g. total salary of each department)
    • Replicating database by recording changes to special relations (called changed or delta relations) and having a separate process that applies the changes over to a replica
  • There are better ways of doing these now
    • Databases today provide built-n materialized view facilities to maintain summary data
    • Databases provide built-in support for replication
  • Encapsulation facilities can be used instead of triggers in many cases
    • Define methods to update fields
    • Carry out actions as part of the update methods instead of through a trigger
数据库系统原理
Chapter 5: Advanced SQL
https://ddccffq.github.io/2025/12/20/数据库系统原理/Advanced_SQL/
作者
ddccffq
发布于
2025年12月20日
许可协议