PL/SQL and JDBC - PowerPoint PPT Presentation

Loading...

PPT – PL/SQL and JDBC PowerPoint presentation | free to view - id: 1e0c89-ZDc1Z



Loading


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation
Title:

PL/SQL and JDBC

Description:

declare c cursor for. select customer-name, customer-city. from depositor, ... CreateStatement cursor behavior. Two optional args ... advances cursor to next ... – PowerPoint PPT presentation

Number of Views:87
Avg rating:3.0/5.0
Slides: 37
Provided by: cspeo
Category:
Tags: jdbc | sql | cursor

less

Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: PL/SQL and JDBC


1
PL/SQL and JDBC
2
Triggers and Procedures
Triggers Procedures
Coded in pl/sql or java,.. Same (coded in pl/sql, java,..)
Implicitly fired by the server when triggering event occurs Explicitly run by a user, application or trigger
Fired by any user By a specific user/application/trigger

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

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
    hh24miss')crlf
  • 'From 'sender''crlf
  • 'Subject 'subjectcrlf
  • 'To 'recipientcrlf

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

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
  • Cant 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 ltembedded SQL statement gt 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-city from depositor, customer,
    account where depositor.customer-name
    customer.customer-name and
    depositor account-number account.account-number
    and account.balance gt amount
  • END-EXEC

18
Embedded SQL (Cont.)
  • The open statement causes the query to be
    evaluated
  • EXEC SQL open c END-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-EXEC Repeated 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 close c 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 open c END-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 account
where branch-name Perryridge for
update To update tuple at the current location of
cursor update account set balance
balance 100 where 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
ODBC
  • 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

23
Architecture
Application
ODBC driver
Data Source
  • 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!

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/JDBC
    Tutorial/

26
JDBC Basics Connections
  • A Connection is an object representing a login to
    a database
  • // GET CONNECTION
  • Connection con
  • try
  • con DriverManager.getConnection(
  • "jdbcodbcbankDB",
  • 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 well say stmt.executeQuery(select )

28
CreateStatement cursor behavior
  • Two optional args to createStatement
  • createStatement(ResultSet.ltTYPEgt,
    ResultSet.ltCONCURgt)
  • Corresponds to SQL cursor features
  • ltTYPEgt is one of
  • TYPE_FORWARD_ONLY cant move cursor backward
  • TYPE_SCROLL_INSENSITIVE can move backward, but
    doesnt show results of any updates
  • TYPE_SCROLL_SENSITIVE can move backward, will
    show updates from this statement
  • ltCONCURgt is one of
  • CONCUR_READ_ONLY this statement doesnt 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
    statements 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 (i1 i lt numCols i)
  • if (i gt 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 lt 100)
  • // Loop through each column, getting the
  • // column data and displaying
  • for (i1 i lt numCols i)
  • if (i gt 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
    odbcweblog",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 lt 100)
  • for (i1 i lt numCols i)
  • if (i gt 1) buf.append(",")
  • buf.append(results.getString(i))
  • buf.append("\n")
  • results.close() stmt.close() con.close()

35
Example Updatable cursors
  • conn DriverManager.getConnection("jdbcoracleth
    in_at_training1521Oracle", "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(sq
    l, 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.25i_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/o
    df8/eoug2000_paper60_1.0.doc
  • http//cegt201.bradley.edu/projects/proj2003/equip
    rd/database.html
  • Note all of this is changing every minute
About PowerShow.com