Transcription

Embedded SQLWeek 5: Embedded SQLUpdate StatementsCSC343 Introduction to Databases— University of TorontoEmbedded SQL — 1Interactive vs. Non-Interactive SQL Non-interactive SQL: Statements are includedin an application program written in a hostlanguage — such as C, Java, COBOL Interactive SQL: Statements input fromterminal; DBMS outputs to screen Interactive SQL is inadequate for most uses: It may be necessary to process the databefore output; Amount of data returned not known inadvance; SQL has limited expressive power — note: notTuring-complete.CSC343 Introduction to Databases— University of TorontoEmbedded SQL — 3 Traditional applications often need to “embed” SQLstatements inside the instructions of a proceduralprogramming language (C, COBOL, etc.) Programs with embedded SQL use a pre-compiler tomanage SQL statements. Embedded statements arepreceded by ‘ ’ or ‘EXEC SQL’ or some distinguishedtoken Program variables may be used as parameters in theSQL statements (preceded by ‘:’) select statements producing a single row and updatestatements can be embedded easily. The SQL environment offers a predefined variablesqlcode which describes the execution status of anSQL statement ( 0 if it executed successfully).CSC343 Introduction to Databases— University of TorontoEmbedded SQL — 2Application Program Host language: A conventionalprogramming language (e.g., C, Java) thatsupplies control structures, computationalcapabilities, interaction with physicaldevices. SQL: supplies ability to interact withdatabase. Using the facilities of both: the applicationprogram can act as an intermediarybetween the user at a terminal and theDBMS.CSC343 Introduction to Databases— University of TorontoEmbedded SQL — 41

PreparationIntroducing SQL Into the Application Before any SQL statement is executed, itmust be prepared by the DBMS: What indices can be used? In what order should tables be accessed? What constraints should be checked? Decisions are based on schema, table sizes,etc. Result is a query execution plan. Preparation is a complex activity, usuallydone at run time, justified by the complexityof query processing.CSC343 Introduction to Databases— University of TorontoEmbedded SQL — 5 SQL statements can be incorporated into anapplication program in two different ways. Statement Level Interface (SLI): Applicationprogram is a mixture of host languagestatements and SQL statements anddirectives. Call Level Interface (CLI): Applicationprogram is written entirely in host language. SQL statements are values of string variablesthat are passed as arguments to hostlanguage (library) proceduresCSC343 Introduction to Databases— University of TorontoEmbedded SQL — 6Statement Level InterfaceStatement Level Interface SQL statements and directives in theapplication have a special syntax that setsthem off from host language constructse.g., EXEC SQL SQL statement Pre-compiler scans program andtranslates SQL statements into calls tohost language library procedures thatcommunicate with DBMS. Host language compiler then compilesprogram.CSC343 Introduction to Databases— University of TorontoEmbedded SQL — 7 SQL constructs in an application take twoforms: Standard SQL statements (static orembedded SQL): Useful when SQL portionof program is known at compile time Directives (dynamic SQL): Useful when SQLportion of program not known at compiletime. Application constructs SQL statementsat run time as values of host languagevariables that are manipulated by directives Pre-compiler translates statements anddirectives into arguments of calls to libraryprocedures.CSC343 Introduction to Databases— University of TorontoEmbedded SQL — 82

Static SQLCall Level Interface Application program written entirely in hostlanguage (no precompiler)Examples: JDBC, ODBC SQL statements are values of string variablesconstructed at run time using host languageSimilar to dynamic SQL Application uses string variables asarguments of library routines thatcommunicate with DBMSe.g. executeQuery(“SQL query statement”)CSC343 Introduction to Databases— University of TorontoEmbedded SQL — 9CSC343 Introduction to Databases— University of TorontoVariablesshared by hostand SQL“:” used toset off hostvariables Declaration section for host/SQLcommunication. Colon convention for value (WHERE) andresult (INTO) parameters.CSC343 Introduction to Databases— University of TorontoEmbedded SQL — 10ConnectionsStatusEXEC SQL SELECT C.NumEnrolledINTO :num enrolledFROM Course CWHERE C.CrsCode :crs code;if ( !strcmp (SQLSTATE, “00000”) ) {printf ( “statement failed” )};EXEC SQL BEGIN DECLARE S;unsigned long num enrolled;char crs code;char SQLSTATE [6];EXEC SQL END DECLARE SE; .EXEC SQL SELECT C.NumEnrolledINTO :num enrolledFROM Course CWHERE C.CrsCode :crs code; To connect to an SQL database, use aconnect statementCONNECT TO database name ASconnection name USING user idOut parameterIn parameterEmbedded SQL — 11CSC343 Introduction to Databases— University of TorontoEmbedded SQL — 123

Example: Course DeregistrationTransactions No explicit statement is needed to begin atransaction: A transaction is initiated whenthe first SQL statement that accesses thedatabase is executed. The mode of transaction execution can be setwithSET TRANSACTION READ ONLYISOLATION LEVEL SERIALIZABLE Transactions are terminated with COMMIT orROLLBACK statements.CSC343 Introduction to Databases— University of TorontoEmbedded SQL — 13EXEC SQL CONNECT TO :dbserver;if ( ! strcmp (SQLSTATE, “00000”) ) exit (1); .EXEC SQL DELETE FROM Transcript TWHERE T.StudId :studid AND T.Semester ‘S2000’AND T.CrsCode :crscode;if (! strcmp (SQLSTATE, “00000”) ) EXEC SQL ROLLBACK;else {EXEC SQL UPDATE Course CSET C.Numenrolled C.Numenrolled – 1WHERE C.CrsCode :crscode;if (! strcmp (SQLSTATE, “00000”) ) EXEC SQL ROLLBACK;else EXEC SQL COMMIT;}CSC343 Introduction to Databases Problem:SQL deals with tables (ofarbitrary size); host language programdeals with fixed size buffers How is the application to allocate storagefor the result of a SELECT statement? Solution: Fetch a single row at a time Space for a single row (number and typeof out parameters) can be determinedfrom schema and allocated in application— University of TorontoEmbedded SQL — 14CursorsBuffer Mismatch ProblemCSC343 Introduction to Databases— University of TorontoEmbedded SQL — 15 Result set – set of rows produced by aSELECT statement Cursor – pointer to a row in the result set. Cursor operations: Declaration Open – execute SELECT to determine resultset and initialize pointer Fetch – advance pointer and retrieve nextrow Close – deallocate cursorCSC343 Introduction to Databases— University of TorontoEmbedded SQL — 164

Example of Cursor UseCursors (cont’d)cursorSELECTResult set(or pointers to it)applicationBase tableCSC343 Introduction to Databases— University of TorontoEmbedded SQL — 17EXEC SQL DECLARE GetEnroll INSENSITIVE CURSOR FORSELECT T.StudId, T.Grade— cursor is not a schema elementFROM Transcript TWHERE T.CrsCode :crscode AND T.Semester ‘S2000’; Reference resolved at compile time,EXEC SQL OPEN GetEnroll;Value substituted at OPEN timeif ( !strcmp ( SQLSTATE, “00000”)) {. fail exit. }; .EXEC SQL FETCH GetEnroll INTO :studid, :grade;while ( SQLSTATE “00000”) { process the returned row.EXEC SQL FETCH GetEnroll INTO :studid, :grade;}if ( !strcmp ( SQLSTATE, “02000”)) {. fail exit. }; .EXEC SQL CLOSE GetEnroll;CSC343 Introduction to Databases— University of TorontoEmbedded SQL — 18Cursor Types Insensitive cursor: Result set (effectively)computed and stored in a separate table at OPENtime Changes made to base table subsequent toOPEN (by any transaction) do not affect resultset Cursor is read-only Cursors that are not insensitive: Specification notpart of SQL standard Changes made to base table subsequent toOPEN (by any transaction) can affect result set Cursor is updatableCSC343 Introduction to Databases— University of TorontoEmbedded SQL — 19Insensitive CursorChanges made after openingcursor not seen in the cursorcursorkey1 t t t t t t t tkey3 yyyyyyyykey4 zzzzzzzzzResult SetTuples added afteropening the cursorCSC343 Introduction to Databases— University of Torontokey1key2key3key4key5key6t t t t qq t t t e TableEmbedded SQL — 205

Keyset-Driven CursorKeyset-Driven Cursor Example of a cursor that is notinsensitive. Primary key of each row in result set iscomputed at open time. UPDATE or DELETE of a row in base tableby a concurrent transaction betweenOPEN and FETCH might be seen throughcursor. INSERT into base table, however, not seenthrough cursor. Cursor is updatable.CSC343 Introduction to Databases— University of TorontoEmbedded SQL — 21Cursorkey1key3key4Key setTuples added after cursor is open arenot seen, but updates to key1, key3,key4 are seen in the cursor.CSC343 Introduction to DatabasesCursorsFor updatable (not insensitive, not read-only) cursorsUPDATE table-name— base tableSET assignmentWHERE CURRENT OF cursor-nameDELETE FROM table-name— base tableWHERE CURRENT OF cursor-nameRestriction – table-expr must satisfy restrictions ofupdatable view— University of zuuuuuuuuuuuvvvvvvvvvvvBase table— University of TorontoEmbedded SQL — 22ScrollingDECLARE cursor-name [INSENSITIVEINSENSITIVE] [SCROLLSCROLL]CURSOR FOR table-expr[ ORDER BY column-list ][ FOR {READREAD ONLY UPDATE [ OF column-list ] } ]CSC343 Introduction to Databaseskey1key2key3key4key5key6Embedded SQL — 23 If SCROLL option not specified in cursordeclaration, FETCH always moves cursorforward one position If SCROLL option is included in DECLARECURSOR section, cursor can Getbepreviousmovedintuplearbitrary ways around result set:FETCH PRIOR FROM GetEnroll INTO :studid,:grade; Also: FIRST, LAST, ABSOLUTE n, RELATIVE nCSC343 Introduction to Databases— University of TorontoEmbedded SQL — 246

Dynamic SQLStored Procedures Procedure – written in a conventional algorithmiclanguage Included as schema element (stored in DBMS) Invoked by the application Advantages: Intermediate data need not be communicated toapplication (time and cost savings) Procedure’s SQL statements prepared in advance Authorization can be done at procedure level Added security since procedure resides in server Applications that call the procedure need notknow the details of database schema – alldatabase access is encapsulated within theprocedureCSC343 Introduction to Databases— University of TorontoEmbedded SQL — 25strcpy (tmp, “SELECT C.NumEnrolled FROM Course C \WHERE C.CrsCode ?” ) ;EXEC SQL PREPARE st FROM :tmp;EXEC SQL EXECUTE st INTO :num enrolled USING:crs code;placeholder st is an SQL variable; names the SQLstatement tmp,tmp crscode,crscode num enrolled are hostlanguage variables (note colon notation) crscode is an in parameter; supplies valuefor placeholder (?) num enrolled is an out parameter; receivesvalue from C.NumEnrolledCSC343 Introduction to Databases— University of TorontoEmbedded SQL — 26Parameters for Static SQLDynamic SQL PREPARE names SQL statementst andsends it to DBMS for preparation EXECUTE causes the statement namedst tobe executedFor Static SQL:SQL: Names of (host language) parameters are containedin SQL statement and available to pre-compiler. Address and type information in symbol table. Routines for fetching and storing argument valuescan be generated. Complete statement (with parameter values) sent toDBMS when statement is executed.EXEC SQL SELECT C.NumEnrolledINTO:num enrolledFROM Course CWHERE C.CrsCode :crs code;CSC343 Introduction to Databases— University of TorontoEmbedded SQL — 27CSC343 Introduction to Databases— University of TorontoEmbedded SQL — 287

Parameters for Dynamic SQL Dynamic SQL:SQL SQL statement constructed at runtime when symbol table is no longer present Case 1: Parameters are known at compile timestrcpy (tmp, “SELECT C.NumEnrolled FROM Course C \WHERE C.CrsCode ?” ) ;EXEC SQL PREPARE st FROM :tmp;Parameters are named in EXECUTE statement: inparameters in USING; out parameters in INTOclausesEXEC SQL EXECUTE st INTO :num enrolled USING :crs code; EXECUTE statement is compiled using symboltable fetch() and store() routines generatedCSC343 Introduction to Databases— University of TorontoEmbedded SQL — 29Parameters in Dynamic SQL(parameters supplied at runtime) Case 2: Parameters not known at compiletime Example: Statement input from terminal Application cannot parse statement andmight not know schema, so it does nothave any parameter information EXECUTE statement cannot nameparameters in INTO and USING clausesCSC343 Introduction to Databases— University of TorontoEmbedded SQL — 31Parameters for Dynamic SQL(Case 1: parameters known at compile time) Fetch and store routines are executed at clientwhen EXECUTE is executed to communicateargument values with DBMS EXECUTE can be invoked multiple times withdifferent values of in parameters Each invocation uses same query executionplan Values substituted for placeholders by DBMS(in order) at invocation time and statement isexecutedCSC343 Introduction to Databases— University of TorontoEmbedded SQL — 30Parameters in Dynamic SQL(Case 2: parameters supplied at runtime) DBMS determines number and type ofparameters after preparing the statement Information stored by DBMS in a descriptor –a data structure inside the DBMS, whichrecords the name, type, and value of eachparameter Dynamic SQL provides directive GETDESCRIPTOR to get information aboutparameters (e.g., number, name, type) fromDBMS and to fetch value of out parameters Dynamic SQL provides directive SET DESCRIPTORto supply value to in parametersCSC343 Introduction to Databases— University of TorontoEmbedded SQL — 328

Dynamic SQL Calls when Descriptorsare UsedDescriptorstemp “SELECT C.NumEnrolled, C.Name FROM Course C \WHERE C.CrsCode ‘CS305’ ”DBMSDescriptorapplicationGET DESCRIPTOR1. Application uses GET DESCRIPTORto fetch name, type, value2. Then gets value into appropriatehost variable3. Then processes valueCSC343 Introduction to Databases60valueEXEC SQL ALLOCATE DESCRIPTOR ‘desc’; // create descriptorEXEC SQL DESCRIBE OUTPUT st USINGSQL DESCRIPTOR ‘desc’;// populate desc with info// about out parametersnameintegertype“Databases”EXEC SQL EXECUTE st INTOSQL DESCRIPTOR AREA ‘desc’;// execute statement and// store out values in descvaluenametypeEXEC SQL GET DESCRIPTOR ‘desc’ ;// get out valuesNamestring— University of TorontoEmbedded SQL — 33sprintf(my sql stmt,“SELECT * FROM %s WHERE COUNT(*) 1”,table); // table – host var; even the table is known only at runtime!EXEC SQL PREPARE st FROM :my sql stmt;EXEC SQL ALLOCATE DESCRIPTOR ‘st output’;EXEC SQL DESCRIBE OUTPUT st USING SQL DESCRIPTOR‘st output’ The SQL statement to execute is known only at run time At this point DBMS knows what the exact statement is (includingthe table name, the number of out parameters, their types) The above statement asks to create descriptors in st output forall the (now known) out parametersEXEC SQL EXECUTE st INTO SQL DESCRIPTOR ‘st output’;— University of Toronto// prepare statementNumEnrolledExample: Nothing Known at Compile TimeCSC343 Introduction to Databases construct SQL statement in temp EXEC SQL PREPARE st FROM :temp;Embedded SQL — 35 similar strategy is used for in parameters CSC343 Introduction to Databases— University of TorontoEmbedded SQL — 34Example: Getting Meta-Informationfrom a Descriptor// Host var colcount gets the number of out parameters in// the SQL statement described by st outputEXEC SQL GET DESCRIPTOR ‘st output’ :colcount COUNT;COUNT// Set host vars coltype, collength, colname with the type,// length, and name of the colnumber’s out parameter in// the SQL statement described by st outputEXEC SQL GET DESCRIPTOR ‘st output’ VALUE:colnumber;:coltype TYPE,TYPE // predefined integer constants,// such as SQL CHAR, SQL FLOAT, :collength LENGTH,LENGTH:colname NAME;NAMECSC343 Introduction to Databases— University of TorontoEmbedded SQL — 369

JDBCExample: Using Meta-Information toExtract Attribute Valuechar strdata[1024];Put the value of attributeint intdata;colnumber into thevariable strdata switch (coltype) {case SQL CHAR:EXEC SQL GET DESCRIPTOR ‘st output’ VALUE :colnumber strdata DATADATA;break;case SQL INT:EXEC SQL GET DESCRIPTOR ‘st output’ VALUE:colnumber:intdata DATADATA;;break;case SQL FLOAT: }CSC343 Introduction to Databases— University of TorontoEmbedded SQL — 37 Call-level interface (CLI) for executing SQLfrom a Java program SQL statement is constructed at run time asthe value of a Java variable (as in dynamicSQL) JDBC passes SQL statements to theunderlying DBMS. Can be interfaced to anyDBMS that has a JDBC driver Part of SQL:2003CSC343 Introduction to DatabasesJDBC Run-Time ArchitectureOracleEmbedded SQL — 38Executing a Queryimport java.sql.*;Oracle— University of Toronto-- import all classes in package java.sqlClass.forName (driver name);database// static method of class Class// loads specified tion con DriverManager.getConnection(Url, Id, Passwd); Static method of class DriverManager; attempts toconnect to DBMS If successful, creates a connection object, con, formanaging the connectionStatement stat con.createStatement (); Creates a statement object stat Statements have executeQuery() methodCSC343 Introduction to Databases— University of TorontoEmbedded SQL — 39CSC343 Introduction to Databases— University of TorontoEmbedded SQL — 4010

Executing a Query (cont’d)String query “SELECT T.StudId FROM Transcript T” “WHERE T.CrsCode ‘cse305’ ” “AND T.Semester ‘S2000’ ”;ResultSet res stat.executeQuery (query); Creates a result set object, res. Prepares and executes the query. Stores the result set produced by execution in res(analogous to opening a cursor). The query string can be constructed at run time (asabove). The input parameters are plugged into the querywhenthe string is formed (as above)CSC343 Introduction to Databases— University of TorontoEmbedded SQL — 41Preparing and Executing a Query (cont’d)String crs code, semester; ps.setString(1, crs code); // set value of first in parameterps.setString(2, semester); // set value of second in parameterResultSet res ps.executeQuery ( ); Creates a result set object, res Executes the query Stores the result set produced by execution in reswhile ( res.next ( ) ) {j res.getInt (“StudId”); process output value }CSC343 Introduction to Databases— University of Toronto// advance the cursor// fetch output int-valueEmbedded SQL — 43Preparing and Executing a QueryString query “SELECT T.StudId FROM Transcript T” “WHERE T.CrsCode ? AND T.Semester ?”;placeholdersPreparedStatement ps con.prepareStatement ( query ); Prepares the statement Creates a prepared statement object, ps, containing theprepared statement Placeholders (?) mark positions of in parameters;special API is provided to plug the actual values inpositions indicated by the ?’sCSC343 Introduction to Databases— University of TorontoEmbedded SQL — 42Result Sets and Cursors Three types of result sets in JDBC: Forward-only: not scrollable Scroll-insensitive: scrollable; changesmade to underlying tables after thecreation of the result set are notvisible through that result set Scroll-sensitive: scrollable; updatesand deletes made to tuples in theunderlying tables after the creation ofthe result set are visible through thesetCSC343 Introduction to Databases— University of TorontoEmbedded SQL — 4411

Result SetHandling ExceptionsStatement stat con.createStatement (ResultSet.TYPE SCROLL SENSITIVE,ResultSet.CONCUR UPDATABLE ); Any result set type can be declared read-only orupdatable – CONCUR UPDATABLE (assuming SQLquery satisfies the conditions for updatable views) Updatable:Updatable Current row of an updatable result set canbe changed or deleted, or a new row can be inserted.Any such change causes changes to the underlyingdatabase tableres.updateString (“Name”, “John” ); // change the attribute “Name” of// current row in the row buffer.res.updateRow ( ); // install changes to the current row buffer// in the underlying database tableCSC343 Introduction to Databases— University of TorontoEmbedded SQL — 45try {.Java/JDBC code.} catch ( SQLException ex ) { exception handling code.} try/catch is the basic structure within which an SQLstatement should be embedded If an exception is thrown, an exception object, ex, iscreated and the catch clause is executed The exception object has methods to print an errormessage, return SQLSTATE, etc.CSC343 Introduction to DatabasesTransactions in JDBC— University of TorontoEmbedded SQL — 46SQLJ Default for a connection is Transaction boundaries Autocommit mode: each SQL statement is atransaction. To group several statements into a transaction usecon.setAutoCommit (false) Isolation default isolation level of the underlying DBMS To change isolation level usecon.setTransactionIsolationLevel(TRANSACTION SERIALIZABLE) With autocommit off: transaction is committed using con.commit(). next transaction is automatically initiated (chaining) Transactions on each connection committed separatelyCSC343 Introduction to Databases— University of TorontoEmbedded SQL — 47 A statement-level interface to Java A dialect of embedded SQL designedspecifically for Java Translated by precompiler into Java SQL constructs translated into calls to anSQLJ runtime package, which accessesdatabase through calls to a JDBC driver Part of SQL:2003CSC343 Introduction to Databases— University of TorontoEmbedded SQL — 4812

SQLJ ExampleSQLJ Has some of efficiencies of embedded SQL Compile-time syntax and type checking Use of host language variables More elegant than embedded SQL Has some of the advantages of JDBC Can access multiple DBMSs using drivers SQLJ statements and JDBC calls can beincluded in the same programCSC343 Introduction to Databases— University of TorontoEmbedded SQL — 49Example of SQLJ Iterator Similar to JDBC’s ResultSet; provides acursor mechanism#SQL iterator GetEnrolledIter (int studentId,Method names byString studGrade);which to access theGetEnrolledIter iter1;attributes StudentIdand Grade#SQL iter1 {SELECT T.StudentId as “studentId”,T.Grade as “studGrade”FROM Transcript TWHERE T.CrsCode :crsCodeAND T.Semester :semester}; CSC343 Introduction to Databases — University of TorontoEmbedded SQL — 51#SQL {SELECT C.EnrollmentINTO :numEnrolledFROM Class CWHERE C.CrsCode :crsCodeAND C.Semester :semester};CSC343 Introduction to Databases— University of TorontoEmbedded SQL — 50Iterator Example (cont’d)int id;String grade;while ( iter1.next( ) ) {id iter1.studentId();grade iter1.studGrade(); process the values in id and grade };iter1.close();CSC343 Introduction to Databases— University of TorontoEmbedded SQL — 5213

ODBCSequence of Procedure CallsNeeded for ODBC Call level interface that is databaseindependent Related to SQL/CLI, part of SQL:1999 Software architecture similar to JDBCwith driver manager and drivers Not object oriented Low-level: application mustspecifically allocate and deallocatestorageCSC343 Introduction to Databases— University of TorontoEmbedded SQL — 53SQLAllocEnv(&henv);// get environment handleSQLAllocConnect(henv, &hdbc); // get connection handleSQLConnect(hdbc, db name, userId, password); // connectSQLAllocStmt(hdbc, &hstmt);// get statement handleSQLPrepare(hstmt, SQL statement); // prepare SQL statementSQLExecute(hstmt);SQLFreeStmt(hstmt);// free up statement spaceSQLDisconnect(hdbc);SQLFreeEnv(henv);// free up environment spaceCSC343 Introduction to Databases Cursors Statement handle (for example hstmt) is used asname of cursor Status Processing Each ODBC procedure is actually a function thatreturns statusRETCODE retcode1;Retcode1 SQLConnect ( ) Transactions Can be committed or aborted withSQLTransact (henv, hdbc, SQL COMMIT)— University of TorontoEmbedded SQL — 54CursorsODBC FeaturesCSC343 Introduction to Databases— University of TorontoEmbedded SQL — 55 Fundamental problem with database technology:impedance mismatch — traditional programminglanguages process records one-at-a-time (tupleoriented); SQL processes tuple sets (set-oriented). Cursors solve this problem: A cursor accesses theresult of a query in a set-oriented way, returns tuplesfor the program to process one-by-one. Syntax of cursor definition:declare CursorName [ scroll ]cursor for SelectSQL[ for read only update [ of Attribute {,Attribute}] ]CSC343 Introduction to Databases— University of TorontoEmbedded SQL — 5614

Example of Embedded SQLOperations on Cursors To execute the query associated with a cursor:open CursorName To extract one tuple from the query result:fetch [ Position from ] CursorName intoFetchList To free the cursor, discarding the query result:close CursorName To access the current tuple (when a cursor readsa relation, in order to update it):current of CursorName(in a where clause)CSC343 Introduction to Databases— University of TorontoEmbedded SQL — 57Dynamic SQL When applications do not know at compile-time theSQL statement to execute, they need dynamic SQL. Major problem: managing the transfer of parametersbetween the program and the SQL environment. For direct execution:execute immediate SQLStatement For execution preceded by the analysis of thestatement:prepare CommandName from SQLStatementfollowed by:execute CommandName [ into TargetList ][ using ParameterList ]CSC343 Introduction to Databases— University of TorontoEmbedded SQL — 59void DisplayDepartmentSalaries(char DeptName[]){ char FirstName[20], Surname[20];long int Salary; declare DeptEmp cursor forselect FirstName, Surname, Salaryfrom Employeewhere Dept :DeptName; open DeptEmp; fetch DeptEmp into :FirstName, :Surname, :Salary;printf(“Department %s\n”,DeptName);while (sqlcode 0){ printf(“Name: %s %s ”,FirstName,Surname);printf(“Salary: %d\n”,Salary); fetch DeptEmp into :FirstName, :Surname,:Salary; } close DeptEmp;}CSC343 Introduction to Databases— University of TorontoEmbedded SQL — 58Procedures SQL-2 allows for the definition of procedures, alsoknown as stored procedures. Stored procedures are part of the schemaprocedure AssignCity(:Dep char(20),:City char(20))update Departmentset City :Citywhere Name :Dep SQL-2 does not support the the definition ofcomplex procedures Most systems offer SQL extensions that supportcomplex procedures (e.g., Oracle PL/SQL).CSC343 Introduction to Databases— University of TorontoEmbedded SQL — 6015

Procedure in Oracle PL/SQLProcedure Debit(ClientAcct char(5),Withdr int) isOldAmount integer; NewAmount integer;Threshold integer;beginselect Amount,Overdraft into OldAmount, Threshfrom BankAcct where AcctNo ClientAcctfor update of Amount;NewAmount : OldAmount - WithDr;if NewAmount Threshthen update BankAcctset Amount NewAmountwhere AcctNo ClientAcct;else insert into ;end if;end Debit;CSC343 Introduction to Databases— University of TorontoEmbedded SQL — 6116

preceded by ‘ ’ or ‘EXEC SQL’ or some distinguished token Program variables may be used as parameters in the SQL statements (preceded by ‘:’) select statements producing a single row and update statements can be embedded easily. The SQL environment offers a predefined va