山大數(shù)據(jù)庫系統(tǒng)英語課件05高級SQL

1、Chapter 5: Advanced SQLChapter 5: Advanced SQLAccessing SQL From a Programming Language Dynamic SQLJDBC and ODBCEmbedded SQLFunctions and Procedural ConstructsTriggers Advanced Aggregation FeaturesOLAPJDBC and ODBCAPI (application-program interface) for a program to interact with a database serverAp

2、plication makes calls toConnect with the database serverSend SQL commands to the database serverFetch tuples of result one-by-one into program variablesODBC (Open Database Connectivity) works with C, C+, C#, and Visual BasicOther APIs such as ADO.NET sit on top of ODBCJDBC (Java Database Connectivit

3、y) works with JavaJDBCJDBC is a Java API for communicating with database systems supporting SQL.JDBC supports a variety of features for querying and updating data, and for retrieving query results.JDBC also supports metadata retrieval, such as querying about relations present in the database and the

4、 names and types of relation attributes.Model for communicating with the database:Open a connectionCreate a “statement” objectExecute queries using the Statement object to send queries and fetch resultsException mechanism to handle errorsJDBC Codepublic static void JDBCexample(String dbid, String us

5、erid, String passwd) try Class.forName (oracle.jdbc.driver.OracleDriver); Connection conn = DriverManager.getConnection( jdbc:oracle:thin:2000:univdb, userid, passwd); Statement stmt = conn.createStatement(); Do Actual Work . stmt.close(); conn.close(); catch (SQLException sqle) System.out.println(S

6、QLException : + sqle); JDBC Code (Cont.)Update to databasetry stmt.executeUpdate( insert into instructor values(77987, Kim, Physics, 98000); catch (SQLException sqle) System.out.println(Could not insert tuple. + sqle);Execute query and fetch and print results ResultSet rset = stmt.executeQuery( sele

7、ct dept_name, avg (salary) from instructor group by dept_name);while (rset.next() System.out.println(rset.getString(dept_name) + + rset.getFloat(2);JDBC Code Details Getting result fields:rs.getString(“dept_name”) and rs.getString(1) equivalent if dept_name is the first argument of select result.Dea

8、ling with Null valuesint a = rs.getInt(“a”); if (rs.wasNull() Systems.out.println(“Got null value”);Prepared StatementPreparedStatement pStmt = conn.prepareStatement( insert into instructor values(?,?,?,?);pStmt.setString(1, 88877); pStmt.setString(2, Perry);pStmt.setString(3, Finance); pStmt.setInt

9、(4, 125000);pStmt.executeUpdate(); pStmt.setString(1, 88878);pStmt.executeUpdate();For queries, use pStmt.executeQuery(), which returns a ResultSet WARNING: always use prepared statements when taking an input from the user and adding it to a queryNEVER create a query by concatenating strings which y

10、ou get as inputsinsert into instructor values( + ID + , + name + , + + dept name + , balance + )“What if name is “DSouza”?SQL InjectionSuppose query is constructed usingselect * from instructor where name = + name + Suppose the user, instead of entering a name, enters:X or Y = Ythen the resulting st

11、atement becomes:select * from instructor where name = + X or Y = Y + which is:select * from instructor where name = X or Y = YUser could have even usedX; update instructor set salary = salary + 10000; -Prepared statement internally uses:select * from instructor where name = X or Y = YAlways use prep

12、ared statements, with user inputs as parametersMetadata FeaturesResultSet metadataE.g., after executing query to get a ResultSet rs:ResultSetMetaData rsmd = rs.getMetaData(); for(int i = 1; i = rsmd.getColumnCount(); i+) System.out.println(rsmd.getColumnName(i); System.out.println(rsmd.getColumnType

13、Name(i); How is this useful?Metadata (Cont)Database metadataDatabaseMetaData dbmd = conn.getMetaData();ResultSet rs = dbmd.getColumns(null, univdb, department, %);/ Arguments to getColumns: Catalog, Schema-pattern, Table-pattern,/ and Column-Pattern/ Returns: One row for each column; row has a numbe

14、r of attributes/ such as COLUMN_NAME, TYPE_NAMEwhile( rs.next() System.out.println(rs.getString(COLUMN_NAME), rs.getString(TYPE_NAME); And where is this useful?Transaction Control in JDBCBy default, each SQL statement is treated as a separate transaction that is committed automaticallybad idea for t

15、ransactions with multiple updatesCan turn off automatic commit on a connectionconn.setAutoCommit(false);Transactions must then be committed or rolled back explicitlymit(); orconn.rollback();conn.setAutoCommit(true) turns on automatic commit.Other JDBC FeaturesCalling functions and proceduresCallable

16、Statement cStmt1 = conn.prepareCall(? = call some function(?);CallableStatement cStmt2 = conn.prepareCall(call some procedure(?,?);Handling large object typesgetBlob() and getClob() that are similar to the getString() method, but return objects of type Blob and Clob, respectivelyget data from these

17、objects by getBytes()associate an open stream with Java Blob or Clob object to update large objectsblob.setBlob(int parameterIndex, InputStream inputStream).SQLJJDBC is overly dynamic, errors cannot be caught by compilerSQLJ: embedded SQL in Java#sql iterator deptInfoIter ( String dept name, int avg

18、Sal);deptInfoIter iter = null;#sql iter = select dept_name, avg(salary) from instructor group by dept name ;while (iter.next() String deptName = iter.dept_name(); int avgSal = iter.avgSal(); System.out.println(deptName + + avgSal);iter.close();ODBCOpen DataBase Connectivity(ODBC) standard standard f

19、or application program to communicate with a database server.application program interface (API) to open a connection with a database, send queries and updates, get back results.Applications such as GUI, spreadsheets, etc. can use ODBCWas defined originally for Basic and C, versions available for ma

20、ny languages.ODBC (Cont.)Each database system supporting ODBC provides a driver library that must be linked with the client program.When client program makes an ODBC API call, the code in the library communicates with the server to carry out the requested action, and fetch results.ODBC program first

21、 allocates an SQL environment, then a database connection handle.Opens database connection using SQLConnect(). Parameters for SQLConnect:connection handle,the server to which to connectthe user identifier, password Must also specify types of arguments:SQL_NTS denotes previous argument is a null-term

22、inated string.ODBC Codeint ODBCexample() RETCODE error; HENV env; /* environment */ HDBC conn; /* database connection */ SQLAllocEnv(&env); SQLAllocConnect(env, &conn); SQLConnect(conn, “, SQL_NTS, avi, SQL_NTS, avipasswd, SQL_NTS); . Do actual work SQLDisconnect(conn); SQLFreeConnect(conn); SQLFree

23、Env(env); ODBC Code (Cont.)Program sends SQL commands to database by using SQLExecDirectResult tuples are fetched using SQLFetch()SQLBindCol() binds C language variables to attributes of the query result When a tuple is fetched, its attribute values are automatically stored in corresponding C variab

24、les.Arguments to SQLBindCol()ODBC stmt variable, attribute position in query resultThe type conversion from SQL to C. The address of the variable. For variable-length types like character arrays, The maximum length of the variable Location to store actual length when a tuple is fetched.Note: A negat

25、ive value returned for the length field indicates null valueGood programming requires checking results of every function call for errors; we have omitted most checks for brevity.ODBC Code (Cont.)Main body of program char deptname80;float salary;int lenOut1, lenOut2;HSTMT stmt;char * sqlquery = selec

26、t dept_name, sum (salary) from instructor group by dept_name;SQLAllocStmt(conn, &stmt);error = SQLExecDirect(stmt, sqlquery, SQL_NTS);if (error = SQL SUCCESS) SQLBindCol(stmt, 1, SQL_C_CHAR, deptname , 80, &lenOut1); SQLBindCol(stmt, 2, SQL_C_FLOAT, &salary, 0 , &lenOut2); while (SQLFetch(stmt) = SQ

27、L_SUCCESS) printf ( %s %gn, deptname, salary); SQLFreeStmt(stmt, SQL_DROP);ODBC Prepared StatementsPrepared StatementSQL statement prepared: compiled at the databaseCan have placeholders: E.g. insert into account values(?,?,?)Repeatedly executed with actual values for the placeholdersTo prepare a st

28、atement SQLPrepare(stmt, );To bind parameters SQLBindParameter(stmt, , type information and value omitted for simplicity.) To execute the statement retcode = SQLExecute( stmt); To avoid SQL injection security risk, do not create SQL strings directly using user input; instead use prepared statements

29、to bind user inputsMore ODBC FeaturesMetadata featuresfinding all the relations in the database andfinding the names and types of columns of a query result or a relation in the database.By default, each SQL statement is treated as a separate transaction that is committed automatically.Can turn off a

30、utomatic commit on a connectionSQLSetConnectOption(conn, SQL_AUTOCOMMIT, 0) Transactions must then be committed or rolled back explicitly by SQLTransact(conn, SQL_COMMIT) orSQLTransact(conn, SQL_ROLLBACK)ODBC Conformance LevelsConformance levels specify subsets of the functionality defined by the st

31、andard.CoreLevel 1 requires support for metadata queryingLevel 2 requires ability to send and retrieve arrays of parameter values and more detailed catalog information.SQL Call Level Interface (CLI) standard similar to ODBC interface, but with some minor differences.ADO.NETAPI designed for Visual Ba

32、sic .NET and C#, providing database access facilities similar to JDBC/ODBCPartial example of ADO.NET code in C#using System, System.Data, System.Data.SqlClient; SqlConnection conn = new SqlConnection( “Data Source=, Initial Catalog=”);conn.Open();SqlCommand cmd = new SqlCommand(“select * from studen

33、ts”, conn);SqlDataReader rdr = cmd.ExecuteReader();while(rdr.Read() Console.WriteLine(rdr0, rdr1); /* Prints result attributes 1 & 2 */rdr.Close(); conn.Close();Can also access non-relational data sources such as OLE-DB, XML data, Entity frameworkEmbedded SQLThe SQL standard defines embeddings of SQ

34、L in a 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.The basic form of these languages follows that of the System R embedding of S

35、QL into PL/I.EXEC SQL statement is used to identify embedded SQL request to the preprocessorEXEC SQL END_EXECNote: this varies by language (for example, the Java embedding uses # SQL . ; ) Example QuerySpecify the query in SQL and declare a cursor for it EXEC SQL declare c cursor for select ID, name

36、 from student where tot_cred :credit_amount END_EXECFrom within a host language, find the ID and name of students who have completed more than the number of credits stored in variable credit_amount.Embedded SQL (Cont.)The open statement causes the query to be evaluatedEXEC SQL open c END_EXECThe fet

37、ch statement causes the values of one tuple in the query result to be placed on host language variables.EXEC SQL fetch c into :si, :sn END_EXECRepeated calls to fetch get successive tuples in the query resultA variable called SQLSTATE in the SQL communication area (SQLCA) gets set to 02000 to indica

38、te no more data is availableThe close statement causes the database system to delete the temporary relation that holds the result of the query.EXEC SQL close c END_EXEC Note: above details vary with language. For example, the Java embedding defines Java iterators to step through result tuples.Update

39、s Through CursorsCan update tuples fetched by cursor by declaring that the cursor is for update declare c cursor for select * from instructor where dept_name = Music for updateTo update tuple at the current location of cursor c update instructor set salary = salary + 100 where current of cProcedural

40、 Constructs in SQLProcedural Extensions and Stored ProceduresSQL provides a module language Permits definition of procedures in SQL, with if-then-else statements, for and while loops, etc.Stored ProceduresCan store procedures in the database then execute them using the call statementpermit external

41、applications to operate on the database without knowing about internal detailsObject-oriented aspects of these features are covered in Chapter 22 (Object Based Databases)Functions and ProceduresSQL:1999 supports functions and proceduresFunctions/procedures can be written in SQL itself, or in an exte

42、rnal programming language.Functions are particularly useful with specialized data types such as images and geometric objects.Example: functions to check if polygons overlap, or to compare images for similarity.Some database systems support table-valued functions, which can return a relation as a res

43、ult.SQL:1999 also supports a rich set of imperative constructs, includingLoops, if-then-else, assignmentMany databases have proprietary procedural extensions to SQL that differ from SQL:1999.SQL FunctionsDefine a function that, given the name of a department, returns the count of the number of instr

44、uctors 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; endFind the department name and budget of all departments with more that 1

45、2 instructors.select dept_name, budgetfrom departmentwhere dept_count (dept_name ) 1Table FunctionsSQL:2003 added functions that return a relation as a resultExample: Return all accounts owned by a given customercreate function instructors_of (dept_name char(20)returns table ( ID varchar(5),name var

46、char(20), dept_name varchar(20),salary numeric(8,2)return table(select ID, name, dept_name, salary from instructor where instructor.dept_name = instructors_of.dept_name)Usageselect *from table (instructors_of (Music)SQL ProceduresThe dept_count function could instead be written as procedure:create p

47、rocedure 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 endProcedures can be invoked either from an SQL procedure or from embedded SQL, using the call statement.declare d_count i

48、nteger;call dept_count_proc( Physics, d_count);Procedures and functions can be invoked also from dynamic SQLSQL:1999 allows more than one function/procedure of the same name (called name overloading), as long as the number of arguments differ, or at least the types of the arguments differProcedural

49、ConstructsWarning: most database systems implement their own variant of the standard syntax belowread your system manual to see what works on your systemCompound statement: begin end, May contain multiple SQL statements between begin and end.Local variables can be declared within a compound statemen

50、tsWhileand repeat statements :declare n integer default 0;while n 10 do set n = n + 1end while repeat set n = n 1 until n = 0 end repeatProcedural Constructs (Cont.)For loopPermits iteration over all results of a queryExample: declare n integer default 0; for r as select budget from department where

51、 dept_name = Music do set n = n - r.budget end forProcedural Constructs (cont.)Conditional statements (if-then-else)SQL:1999 also supports a case statement similar to C case statementExample procedure: registers student after ensuring classroom capacity is not exceededReturns 0 on success and -1 if

52、capacity is exceededSee book for detailsSignaling of exception conditions, and declaring handlers for exceptionsdeclare out_of_classroom_seats conditiondeclare exit handler for out_of_classroom_seatsbegin . signal out_of_classroom_seatsendThe handler here is exit - causes enclosing begin.end to be e

53、xitedOther actions possible on exceptionExternal Language Functions/ProceduresSQL:1999 permits the use of functions and procedures written in other languages such as C or C+ Declaring external language procedures and functionscreate procedure dept_count_proc(in dept_name varchar(20), out count integ

54、er)language Cexternal name /usr/avi/bin/dept_count_proccreate function dept_count(dept_name varchar(20)returns integerlanguage Cexternal name /usr/avi/bin/dept_countExternal Language Routines (Cont.)Benefits of external language functions/procedures: more efficient for many operations, and more expr

55、essive power.DrawbacksCode to implement function may need to be loaded into database system and executed in the database systems address space.risk of accidental corruption of database structuressecurity risk, allowing users access to unauthorized dataThere are alternatives, which give good security

56、 at the cost of potentially worse performance.Direct execution in the database systems space is used when efficiency is more important than security.Security with External Language RoutinesTo deal with security problemsUse sandbox techniquesthat is use a safe language like Java, which cannot be used

57、 to access/damage other parts of the database code.Or, run external language functions/procedures in a separate process, with no access to the database process memory.Parameters and results communicated via inter-process communicationBoth have performance overheadsMany database systems support both

58、above approaches as well as direct executing in database system address space.TriggersTriggersA trigger is a statement that is executed automatically by the system as a side effect of a modification to the database.To design a trigger mechanism, we must:Specify the conditions under which the trigger

59、 is to be executed.Specify the actions to be taken when the trigger executes.Triggers introduced to SQL standard in SQL:1999, but supported even earlier using non-standard syntax by most databases.Syntax illustrated here may not work exactly on your database system; check the system manualsTrigger E

60、xample E.g. time_slot_id is not a primary key of timeslot, so we cannot create a foreign key constraint from section to timeslot.Alternative: use triggers on section and timeslot to enforce integrity constraints create trigger timeslot_check1 after insert on sectionreferencing new row as nrowfor eac