1 / 36

PL/SQL and JDBC

PL/SQL and JDBC. Triggers and Procedures. Triggers. Trigger has Trigger declaration (name,…) Triggering event/statement Trigger restriction if any Trigger type if any Trigger timing if needed Trigger action/body. Trigger events/statements. DML statements

meryl
Download Presentation

PL/SQL and JDBC

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. PL/SQL and JDBC

  2. Triggers and Procedures

  3. Triggers • Trigger has • Trigger declaration (name,…) • Triggering event/statement • Trigger restriction if any • Trigger type if any • Trigger timing if needed • Trigger action/body

  4. Trigger events/statements • DML statements • (INSERT, DELETE, UPDATE) on a table/view • DDL statements • CREATE or ALTER • User events • Logon, logoff, • Database events • Startup, shutdown, etc…

  5. Trigger types • Row triggers • Execute once for each row affected by the trigger statement • If trigger statement is update emp set mgr = 10, then it updates all rows • Trigger body is executed for all rows • Statement triggers • Execute once for the entire trigger statement

  6. Trigger timing • BEFORE trigger • Specified that the trigger ‘body’ is executed BEFORE the trigger statement • AFTER trigger • Specifies that the trigger ‘body’ is executed AFTER the trigger statement

  7. Triggers • Trigger has • Trigger declaration (name,…) • Triggering event/statement • Insert on emp • Trigger restriction if any • Simple predicate (e.g. :new.mgr <> :old.mgr) • Trigger type if any • “for each row” (row-type trigger) • Trigger timing if needed • Before insert • Trigger action/body • PL/SQL block

  8. Statement-type Trigger Example CREATE OR REPLACE TRIGGER emp_alert_trig BEFORE INSERT ON emp BEGIN DBMS_OUTPUT.PUT_LINE('New employees are about to be added'); END; / set serverout on INSERT INTO emp (empno, ename, deptno) SELECT empno + 1000, ename, 40 FROM emp WHERE empno =7900;

  9. Row-type Trigger example Old row referred by :OLD; New row referred by :NEW CREATE OR REPLACE TRIGGER chkmgr BEFORE INSERT ON emp FOR EACH ROW DECLARE mgr_exists number; newmgr number := :NEW.mgr; BEGIN SELECT COUNT(*) INTO mgr_exists FROM EMP WHERE empno = newmgr; IF (mgr_exists = 0) THEN raise_application_error (-20000, 'Manager does not exist'); END IF; END; / show errors; INSERT INTO emp (empno, mgr) values (100, -1);

  10. Triggers on VIEWs • Views are defined as “queries” on one or more tables • Triggers on views are translated as triggers on the underlying views by “INSTEAD OF” keywords. • Oracle fires the trigger instead of executing the trigger statement on the view • Restrictions • Need the “view” to be inherently modifiable. • Primary keys of the underlying tables should be in the view • View should not have group by, distinct, aggregates, etc.

  11. Other useful functionality in PL/SQL • UTL_SMTP • Mailing from pl/sql inside the database • UTL_HTTP • … • HTML_DB (we will have an special class on this later)

  12. Example using UTL_SMTP Send mail program using utl_smtp CREATE OR REPLACE PROCEDURE mail ( sender VARCHAR2, recipient VARCHAR2, message IN VARCHAR2 ) IS crlf VARCHAR2(2):= UTL_TCP.CRLF; connection utl_smtp.connection; mailhost VARCHAR2(30) := ‘cs-pop.bu.edu'; header VARCHAR2(1000); subject varchar2(32) := 'Hello'; BEGIN -- Start the connection. connection := utl_smtp.open_connection(mailhost,25); header:= 'Date: '||TO_CHAR(SYSDATE,'dd Mon yy hh24:mi:ss')||crlf|| 'From: '||sender||''||crlf|| 'Subject: '||subject||crlf|| 'To: '||recipient||crlf; -- Handshake with the SMTP server

  13. SMTP example contd… utl_smtp.helo(connection, mailhost); utl_smtp.mail(connection, sender); utl_smtp.rcpt(connection, recipient); utl_smtp.open_data(connection); -- Write the header utl_smtp.write_data(connection, header); utl_smtp.write_data(connection, crlf ||message); utl_smtp.close_data(connection); utl_smtp.quit(connection); EXCEPTION WHEN UTL_SMTP.INVALID_OPERATION THEN dbms_output.put_line(' Invalid Operation in SMTP transaction.'); WHEN UTL_SMTP.TRANSIENT_ERROR THEN dbms_output.put_line(' Temporary problems with sending email - try again later.'); WHEN UTL_SMTP.PERMANENT_ERROR THEN dbms_output.put_line(' Errors in code for SMTP transaction.'); END; / exec mail ('rkothuri@cs.bu.edu', 'rkothuri@cs.bu.edu', ‘Test message with body');

  14. Interfacing with the Database • PL/SQL • Embedded SQL • JDBC

  15. Embedded SQL SQL is not a general purpose programming language. + Tailored for data retrieval and manipulation + Relatively easy to optimize and parallelize • Can’t write entire apps in SQL alone Options: Make the query language “turing complete” Avoids the “impedance mismatch” but, loses advantages of relational lang simplicity Allow SQL to be embedded in regular programming languages. • The SQL standard defines embeddings of SQL in a variety of programming languages such as Pascal, PL/I, Fortran, C, and Cobol.

  16. Embedded SQL • 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. • EXEC SQL statement is used to identify embedded SQL request to the preprocessor EXEC SQL <embedded SQL statement > END-EXEC Note: this varies by language. E.g. the Java embedding uses # SQL { …. } ;

  17. Example Query From within a host language, find the names and cities of customers with more than the variable amount dollars in some account. • Specify the query in SQL and declare a cursor for it EXEC SQL declare c cursor for select customer-name, customer-cityfrom depositor, customer, accountwhere depositor.customer-name = customer.customer-name and depositor account-number = account.account-numberand account.balance > :amount END-EXEC

  18. Embedded SQL (Cont.) • The open statement causes the query to be evaluated EXEC SQL opencEND-EXEC • The fetch statement causes the values of one tuple in the query result to be placed on host language variables. EXEC SQL fetch c into :cn, :cc END-EXECRepeated calls to fetch get successive tuples in the query result • A variable called SQLSTATE in the SQL communication area (SQLCA) gets set to ‘02000’ to indicate no more data is available • The close statement causes the database system to delete the temporary relation that holds the result of the query. EXEC SQL closec END-EXEC Note: above details vary with language. E.g. the Java embedding defines Java iterators to step through result tuples.

  19. Cursor EXEC SQL opencEND-EXEC c Every fetch call, will get the values of the current tuple and will advance the pointer A while loop to get all the tuples Also, you can move up/down, go to the start, go to end, etc.. Finally, you can update/modify a tuple through a cursor

  20. Updates Through Cursors Can update tuples fetched by cursor by declaring that the cursor is for update declare c cursor for select *from accountwherebranch-name = ‘Perryridge’for update To update tuple at the current location of cursor update accountsetbalance = balance + 100where current of c

  21. Comparisons of PL/SQL and Embedded SQL • PL/SQL is a better choice • Reduce overhead when executing multiple statements, improve performance, and increase productivity • Tight integration with the Oracle server. PL/SQL types are native to Oracle. • Can define Packages to bundle logically related types, objects and procedures/functions

  22. Open DataBase Connectivity(ODBC) standard standard for 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 ODBC ODBC

  23. Architecture Application ODBC driver • A lookup service maps “data source names” (“DSNs”) to drivers • Typically handled by OS • Based on the DSN used, a “driver” is linked into the app at runtime • The driver traps calls, translates them into DBMS-specific code • Database can be across a network • ODBC is standard, so the same program can be used (in theory) to access multiple database systems • Data source may not even be an SQL database! Data Source

  24. ODBC/JDBC • Various vendors provide drivers • MS bundles a bunch into Windows • Vendors like DataDirect and OpenLink sell drivers for multiple OSes • Drivers for various data sources • Relational DBMSs (Oracle, DB2, SQL Server, Informix, etc.) • “Desktop” DBMSs (Access, Dbase, Paradox, FoxPro, etc.) • Spreadsheets (MS Excel, Lotus 1-2-3, etc.) • Delimited text files (.CSV, .TXT, etc.) • You can use JDBC/ODBC clients over many data sources • E.g. MS Query comes with many versions of MS Office (msqry32.exe) • Can write your own Java or C++ programs against xDBC

  25. JDBC • Part of Java, very easy to use • Java comes with a JDBC-to-ODBC bridge • So JDBC code can talk to any ODBC data source • E.g. look in your Windows Control Panel for ODBC drivers! • JDBC tutorial online • http://developer.java.sun.com/developer/Books/JDBCTutorial/

  26. JDBC Basics: Connections • A Connection is an object representing a login to a database // GET CONNECTION Connection con; try { con = DriverManager.getConnection( "jdbc:odbc:bankDB", userName,password); } catch(Exception e){ System.out.println(e); } • Eventually you close the connection // CLOSE CONNECTION try { con.close(); } catch (Exception e) { System.out.println(e); }

  27. JDBC Basics: Statements • You need a Statement object for each SQL statement // CREATE STATEMENT Statement stmt; try { stmt = con.createStatement(); } catch (Exception e){ System.out.println(e); } Soon we’ll say stmt.executeQuery(“select …”);

  28. CreateStatement cursor behavior • Two optional args to createStatement: • createStatement(ResultSet.<TYPE>, ResultSet.<CONCUR>) • Corresponds to SQL cursor features • <TYPE> is one of • TYPE_FORWARD_ONLY: can’t move cursor backward • TYPE_SCROLL_INSENSITIVE: can move backward, but doesn’t show results of any updates • TYPE_SCROLL_SENSITIVE: can move backward, will show updates from this statement • <CONCUR> is one of • CONCUR_READ_ONLY: this statement doesn’t allow updates • CONCUR_UPDATABLE: this statement allows updates • Defaults: • TYPE_FORWARD_ONLY and CONCUR_READ_ONLY

  29. JDBC Basics: ResultSet • A ResultSet object serves as a cursor for the statement’s results (stmt.executeQuery()) // EXECUTE QUERY ResultSet results; try { results = stmt.executeQuery( "select * from branch") } catch (Exception e){ System.out.println(e); } • Obvious handy methods: • results.next() advances cursor to next tuple • Returns “false” when the cursor slides off the table (beginning or end) • “scrollable” cursors: • results.previous(), results.relative(int), results.absolute(int), results.first(), results.last(), results.beforeFirst(), results.afterLast()

  30. ResultSet Metadata • Can find out stuff about the ResultSet schema via ResultSetMetaData ResultSetMetaData rsmd = results.getMetaData(); int numCols = rsmd.getColumnCount(); int i, rowcount = 0; // get column header info for (i=1; i <= numCols; i++){ if (i > 1) buf.append(","); buf.append(rsmd.getColumnLabel(i)); } buf.append("\n"); • Other ResultSetMetaData methods: • getColumnType(i), isNullable(i), etc.

  31. Getting Values in Current of Cursor • getString // break it off at 100 rows max while (results.next() && rowcount < 100){ // Loop through each column, getting the // column data and displaying for (i=1; i <= numCols; i++) { if (i > 1) buf.append(","); buf.append(results.getString(i)); } buf.append("\n"); System.out.println(buf); rowcount++; } • Similarly, getFloat, getInt, etc.

  32. Updating Current of Cursor • Update fields in current of cursor: result.next(); result.updateInt(“assets", 10M); • Also updateString, updateFloat, etc. • Or can always submit a full SQL UPDATE statement • Via executeQuery() • The original statement must have been CONCUR_UPDATABLE in either case! • To write the change back to the table, you specify • updateRow();

  33. Cleaning up Neatly try { // CLOSE RESULT SET results.close(); // CLOSE STATEMENT stmt.close(); // CLOSE CONNECTION con.close(); } catch (Exception e) { System.out.println(e); }

  34. Putting it Together (w/o try/catch) Connection con = DriverManager.getConnection("jdbc:odbc:weblog",userName,password); Statement stmt = con.createStatement(); ResultSet results = stmt.executeQuery("select * from Sailors") ResultSetMetaData rsmd = results.getMetaData(); int numCols = rsmd.getColumnCount(), i; StringBuffer buf = new StringBuffer(); while (results.next() && rowcount < 100){ for (i=1; i <= numCols; i++) { if (i > 1) buf.append(","); buf.append(results.getString(i)); } buf.append("\n"); } results.close(); stmt.close(); con.close();

  35. Example: Updatable cursors conn = DriverManager.getConnection("jdbc:oracle:thin:@training:1521:Oracle", "oratest", "oratest") int i_deptno = 10; String sql = "SELECT empno, sal, comm FROM emp_with_type” + “ WHERE deptno = ?" ; // Specify the resultset as Scroll Sensitive and Updateable PreparedStatement pstmt = conn.prepareStatement(sql, ResultSet.TYPE_ SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); pstmt.setInt(1, i_deptno); ResultSet rset = pstmt.executeQuery(); while (rset.next()) { float i_sal = rset.getFloat(2); float i_comm = rset.getFloat(3); // Populate the resultset column using // the updateFloat() method on the ResultSet object rset.updateFloat(2, (float)(i_sal+(1.25*i_comm))); // Update the corresponding resultset row using the above value. rset.updateRow(); // write the change back to the table } rset.close(); pstmt.close() …..

  36. Interesting links • http://edms-service.web.cern.ch/edms-service/ODF/odf8/eoug2000_paper60_1.0.doc • http://cegt201.bradley.edu/projects/proj2003/equiprd/database.html Note: all of this is changing every minute

More Related