advanced database system - ch04
Post on 14-Feb-2017
32 Views
Preview:
TRANSCRIPT
An Introduction to SQLAn Introduction to SQL
Prof. Yin-Fu HuangProf. Yin-Fu HuangCSIE, NYUST CSIE, NYUST
Chapter 4Chapter 4
Advanced Database System Yin-Fu Huang
4.14.1 IntroductionIntroduction
SQL:1999 Products typically support what might be called “a
superset of a subset” of the standard.e.g. IBM DB2
Advanced Database System Yin-Fu Huang
4.24.2 OverviewOverview
Data definition operations (See Fig. 4.1)
Advanced Database System Yin-Fu Huang
InsertInto Temp(P#, Weight) Select P#, Weight From P Where Color=Color(‘Red’);
DeleteFrom SpWhere P#=P#(’P2’);
Update SSet Status=Status*2, City=‘Rome’ Where City=‘Paris’;
Data manipulation operations (See Fig. 4.2)
4.24.2 Overview (Cont.)Overview (Cont.)
Advanced Database System Yin-Fu Huang
Information SchemaSQL catalog vs. SQL schema
4.34.3 The CatalogThe Catalog
Advanced Database System Yin-Fu Huang
4.44.4 ViewsViews
Example: Create View Good_Supplier As Select S#, Status, City From S Where Status > 15;
Select S#, Status From Good_Supplier Where City=’London’;
Select S#, Status
From S Where Status > 15 And City=’London’;
Advanced Database System Yin-Fu Huang
4.54.5 TransactionsTransactions Statements: Start Transaction, Commit Work,
Rollback Work
4.64.6 Embedded SQLEmbedded SQL
The dual-mode principle is that any SQL statement that can be used interactively can also be embedded in an application program; Its converse is not.
Example (See Fig. 4.3)
Advanced Database System Yin-Fu Huang
EXEC SQL executable and declarative SQL statements host variables Into clauses declare sections host variable Sqlstate
host variables appropriate data type⇒ host variables and SQL columns
⇒ the same name EXEC SQL Whenever <condition> <action>;
⇒ a directive to the SQL compiler loose coupling
Advanced Database System Yin-Fu Huang
4.64.6 Embedded SQL (Cont.)Embedded SQL (Cont.)
It is necessary to provide some kind of bridge between the set-level retrieval capacities of SQL and the row-level retrieval capacities of the host. Such is the purpose of cursors.
Operations Not Involving Cursors
Exam: Singleton Select EXEC SQL Select Status, City
Into :Rank, :TownFrom SWhere S#=S#(:Givens#);
Exam: Insert EXEC SQL Insert Into P(P#, Pname, Weight)
Values (:P#, :Pname, :Pwt);
Exam: Delete EXEC SQL Delete
From SpWhere :City= (Select City From S Where S.S#=Sp.S#);
Exam: Update EXEC SQL Update S
Set Status=Status+:RaiseWhere City=‘London’;
Advanced Database System Yin-Fu Huang
4.64.6 Embedded SQL (Cont.)Embedded SQL (Cont.) Operations Involving Cursors
Declare cursor statementEXEC SQL Declare <cursor name> Cursor
For <table expression> [<ordering>];
Open StatementEXEC SQL Open <cursor name>; Active set
Fetch StatementEXEC SQL Fetch <cursor name>
Into <host variable reference commalist>; Close Statement
EXEC SQL Close <cursor name>; Changing the values of host variables no effect⇒
Advanced Database System Yin-Fu Huang
Current forms of Delete and Updatee.g. EXEC SQL Update S
Set Status = Status +:RaiseWhere Current Of X;
Example (Fig. 4.4)Example (Fig. 4.4)
Advanced Database System Yin-Fu Huang
4.74.7 Dynamic SQL and SQL/CLIDynamic SQL and SQL/CLI The steps of an online application:
1. Accept a command from the terminal 2. Analyze that command 3. Execute appropriate SQL statements on the database 4. Return a message and/or results to the terminal
Dynamic SQL Dynamic SQL consists of a set of “dynamic statements”---which themselves a
re compiled ahead of time---whose purpose is precisely to support the compilation and execution of regular SQL statements that are constructed at run time.
Prepare and Execute statementse.g. DCL Sqlsource Char Varying (65000);
Sqlsource = ‘Delete From Sp Where Qty < Qty(300)’;EXEC SQL Prepare Sqlprepped From :Sqlsource;EXEC SQL Execute Sqlprepped;
Advanced Database System Yin-Fu Huang
4.74.7 Dynamic SQL and SQL/CLI (Cont.)Dynamic SQL and SQL/CLI (Cont.) Call-Level Interfaces
SQL/CLI permits an application written in one of the usual host languages to issue database requests, not via embedded SQL, but rather by invoking certain vendor-provided routines.
Those routines use dynamic SQL to perform the requested database operations on the application’s behalf.
Reasons:1. SQL/CLI standardizes the details of certain routine invocations.2. Those applications can be DBMS-independent.
e.g. char sqlsource [65000];strcpy(sqlsource, “Delete From Sp Where Qty < Qty(300)”);rc = SQLExecDirect(hstmt, (SQLCHAR *)sqlsource, SQL_NTS);
Advanced Database System Yin-Fu Huang
4.84.8 SQL Is Not Perfect
There is no product on the market today that supports the relational model in its entirety.
Advanced Database System Yin-Fu Huang
The End.
top related