1 / 15

4.1 Introduction

4.1 Introduction. SQL:1999 Products typically support what might be called “a superset of a subset” of the standard. e.g. IBM DB2. 4.2 Overview. Data definition operations (See Fig. 4.1). 4.2 Overview (Cont.). Data manipulation operations (See Fig. 4.2). Insert Into Temp(P#, Weight)

navarro
Download Presentation

4.1 Introduction

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 4.1 Introduction • SQL:1999 • Products typically support what might be called “a superset of a subset” of the standard. e.g. IBM DB2 Advanced Database System

  2. 4.2 Overview • Data definition operations (See Fig. 4.1) Advanced Database System

  3. 4.2 Overview (Cont.) • Data manipulation operations (See Fig. 4.2) Insert Into Temp(P#, Weight) Select P#, Weight From P Where Color=Color(‘Red’); Delete From Sp Where P#=P#(’P2’); Update S Set Status=Status*2, City=‘Rome’ Where City=‘Paris’; Advanced Database System

  4. 4.3 The Catalog • Information Schema SQL catalog vs. SQL schema Advanced Database System

  5. 4.4 Views • 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

  6. 4.5 Transactions • Statements: Start Transaction, Commit Work, Rollback Work 4.6 Embedded 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

  7. 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

  8. 4.6 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: Delete EXEC SQL Delete From Sp Where :City= (Select City From S Where S.S#=Sp.S#); Exam: Update EXEC SQL Update S Set Status=Status+:Raise Where City=‘London’; Exam: Singleton Select EXEC SQL Select Status, City Into :Rank, :Town From S Where S#=S#(:Givens#); Exam: Insert EXEC SQL Insert Into P(P#, Pname, Weight) Values (:P#, :Pname, :Pwt); Advanced Database System

  9. 4.6 Embedded SQL (Cont.) • Operations Involving Cursors • Declare cursor statement EXEC SQL Declare <cursor name> Cursor For <table expression> [<ordering>]; • Open Statement EXEC SQL Open <cursor name>; • Active set • Fetch Statement EXEC 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

  10. Example (Fig. 4.4) • Current forms of Delete and Update e.g. EXEC SQL Update S Set Status = Status +:Raise Where Current Of X; Advanced Database System

  11. 4.7 Dynamic 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 are 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 statements e.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

  12. 4.7 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: • SQL/CLI standardizes the details of certain routine invocations. • 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

  13. 4.8 SQL Is Not Perfect • There is no product on the market today that supports the relational model in its entirety. Advanced Database System

  14. The End. Advanced Database System

More Related