1 / 90

OCL1 Oracle 10 g : SQL & PL/SQL Session #6

OCL1 Oracle 10 g : SQL & PL/SQL Session #6. Matthew P. Johnson CISDD, CUNY Fall, 2004. Agenda. Last time: Views , Constraints This time: Begin programming for SQL Embedded SQL Pro*C, SQLJ CLI SQL/CLI in C JDBC in Java DBI/DBDs in Perl PSMs Future: PL/SQL, Triggers.

ethelyng
Download Presentation

OCL1 Oracle 10 g : SQL & PL/SQL Session #6

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. OCL1 Oracle 10g:SQL & PL/SQLSession #6 Matthew P. Johnson CISDD, CUNY Fall, 2004 Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  2. Agenda • Last time: Views, Constraints • This time: • Begin programming for SQL • Embedded SQL • Pro*C, SQLJ • CLI • SQL/CLI in C • JDBC in Java • DBI/DBDs in Perl • PSMs • Future: • PL/SQL, Triggers Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  3. Today’s agenda • Go through Join exercises • Discuss Pro*C • First part of lab • Break • Discuss CLI, Embedded SQL, JDBC • Second part of lab Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  4. New topic: SQL Programming • Can write SQL queries in a SQL interpreter • Command prompt • SQL*Plus (sqlplus) in Oracle • mysql in MySQL • Good for experimenting, not for anything non-trivial • Better: use a standard programming language • Host language talks to SQL/DB Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  5. Using two languages • Q: Why not use just one language? • “[W]e can quickly dispense with the idea…” (Ullman, p351) • Q: Why not do everything in the host lang.? • A: What Oracle provides is highly non-trivial • Query interpretation, optimizing • Queries stay constant across host languages • Q: Why not do everything in SQL? • A: Not designed as a general-purpose language • No recursion (no factorial!), not Turing-complete • No, e.g., Swing library • Germ of OO: modularize Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  6. Impedance mismatch problem • Big problem, though: impedance mismatch • Data structures in our app-programming lang. don’t automatically map onto those in SQL • Different types/representations for data • In SQL: tables with scalar fields • In C: scalars, records (containing records…), pointers, arrays • In Java: scalars, objects, references, arrays • In Perl: scalars, lists/arrays, hashes/assoc. Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  7. SQL/host interface in embedded SQL • So Q: how to transfer data between? • A: Shared variables • Some vars in the program can be used by SQL • Prefix var with a : • After query, look here for received data • SQL commands embedded in app. code • Identified by EXEC SQL • Source code is preprocessed before regular compilation • Result is (e.g.) a C program with library calls Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  8. Programs with Embedded SQL prog.pc Host language + Embedded SQL Oracle’s Pro*C Preprocessor Preprocessor prog.c Host Language + function calls Host language compiler Host language compiler gcc a.out Executable Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  9. Interface: SQL / Host Language • Values get passed through shared variables. • Colons precede shared variables in SQL statements • EXEC SQL demarcates every SQL statement • The variable SQLSTATE provides error messages and status reports • “00000” ~ success • “02000” ~ tuple not found • Used in loops EXEC SQL BEGIN DECLARE SECTION; char productName[30]; char SQLSTATE[6]; EXEC SQL END DECLARE SECTION; Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  10. Embedded SQL example • Context: • Product (pname, price, quantity, maker) • Purchase (buyer, seller, store, pname) • Company (cname, city) • Person(name, phone, city) • Goal 1: Insert a new row in Purchase • Goal 2: Look up price of product by name Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  11. Embedded SQL example: insert void simpleInsert() { EXEC SQL BEGIN DECLARE SECTION; char pn[20], cn[30]; /* product-name, company-name */ double p int q; /* price, quantity */ char SQLSTATE[6]; EXEC SQL END DECLARE SECTION; /* get values for name, price and company somehow */ EXEC SQL INSERT INTO Product(pname, price, quantity, maker) VALUES (:pn, :p, :q, :cn); } Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  12. Embedded SQL example: look-up int getWindowsPrice() { EXEC SQL BEGIN DECLARE SECTION; double p; char SQLSTATE[6]; EXEC SQL END DECLARE SECTION; EXEC SQL SELECT price INTO :p FROM Product WHERE Product.name = ‘Windows’; return p; } Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  13. Embedded SQL example: look-up • What about search for arbitrary product? • Q: Will this work? int getPrice(char *name) { EXEC SQL BEGIN DECLARE SECTION; int p; char SQLSTATE[6]; EXEC SQL END DECLARE SECTION; EXEC SQL SELECT price INTO :p FROM Product WHERE Product.name = :name; return p; } Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  14. Embedded SQL example: look-up int getPrice(char *name) { EXEC SQL BEGIN DECLARE SECTION; char n[20]; int p; char SQLSTATE[6]; EXEC SQL END DECLARE SECTION; strcpy(n, name); /* copy name to local var */ EXEC SQL SELECT price INTO :p FROM Product WHERE Product.name = :n; return p; } Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  15. Cursors • For product’s price, looked up single (scalar) value • Q: What if we SELECT multiple fields? • E.g., find all info for some product • A: Just list destination vars separated by commas • Q: What if find multiple rows? • E.g., find all products above a certain price • Use a cursor to step through the results • Each result placed in an array • Using cursors: • Declare the cursor • Open the cursor • Fetch tuples one by one • Close the cursor Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  16. Cursor loop structure • Each time around loop, we • Do a FETCH to obtain next row • Examine SQLSTATE to check success • Can say: • What is NO_MORE_TUPLES? if(NO_MORE_TUPLES) break; #define NO_MORE_TUPLES !(strcmp(SQLSTATE,”02000”)) Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  17. Multiple-row look-up example void productToXML() { EXEC SQL BEGIN DECLARE SECTION; char pn[20], cn[30]; double p; int q; char SQLSTATE[6]; EXEC SQL END DECLARE SECTION; EXEC SQL DECLARE crs CURSOR FOR SELECT pname, price, quantity, maker FROM Product; EXEC SQL OPEN crs; ... Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  18. Multiple look-up example printf(“<allProducts>\n”); while (1) { EXEC SQL FETCH FROM crs INTO :n, :p, :q,:c; if (NO_MORE_TUPLES) break; printf(“<product>\n”); printf(“ <name>%s</name>\n”, n); printf(“ <price>%d</price>\n”, p); printf(“ <quantity>%d</quantity>\n”, q); printf(“ <maker>%s</maker>\n”, c); printf(“</product>\n”); } EXECT SQL CLOSE crs; printf(“</allProducts>\n”); } Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  19. More on Cursors • Cursors can traverse both stored tables and queries • Cursors can modify a relation as well as read it • Cursors can be protected against changes to the underlying relations • Can determine the order in which the cursor will get tuples by the ORDER BY keyword in the SQL query • The cursor can be a scrolling one: can go forward, backward • +n, -n, Abs(n), Abs(-n) Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  20. Cursor on query not table EXEC SQL DECLARE c CURSOR FOR SELECT beer, price FROM Sells WHERE bar = ‘Izzy''s'; EXEC SQL OPEN CURSOR c; while(1) { EXEC SQL FETCH c INTO :theBeer, :thePrice; if (NOT FOUND) break; /* format and print beer and price */ } EXEC SQL CLOSE CURSOR c; Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  21. Modifications with cursors • As we traverse through result set, can modify the current row • Can also modify with arb. WHERE clauses • NB: In regular SQL, usually modify sets of rows (UPDATE WHERE …) • With cursors, we update the last row fetched • Simple example: in RentStab table, we decide we want • to raise (e.g., by 5%) all our prices • Unless price > 2000, in which case they’re deleted Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  22. Modification by cursor example void raisePrices() { EXEC SQL BEGIN DECLARE SECTION; double p; char SQLSTATE[6]; EXEC SQL END DECLARE SECTION; EXEC SQL DECLARE crs CURSOR FOR SELECT price FROM RentStab; EXEC SQL OPEN crs; ... Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  23. Modification by cursor example while (1) { EXEC SQL FETCH FROM crs INTO :p; if (NO_MORE_TUPLES) break; if (p < 2000) EXEC SQL UPDATE RentStab SET price = 1.05*price; WHERE CURRENT OF RentStab; else EXEC SQL DELETE FROM RentStab WHERE CURRENT OF RentStab; } EXECT SQL CLOSE crs; } Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  24. A mention of concurrent access • What if the DB changes while our cursor is looping? • I.e., after we opened the cursor, but while we’re fetching • Should we see the changes? Maybe, maybe not • make these changes invisible by declaring insensitive • Q: How can this be implemented? • One crude way: delay any changes until all open insensitive cursors close • Good idea: indicate read-only cursors so they won’t be held up: EXEC SQL DECLARE crs INSENSITIVE CURSOR FOR SELECT price FROM Product; EXEC SQL DECLARE crs CURSOR FOR SELECT price FROM Product; FOR READ ONLY; Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  25. Scrolling cursors • Usually cursor just walks through rows 1 by 1 • Other options: • NEXT (default) or PREVIOUS • FIRST or LAST • RELATIVE +/-n • RELATIVE 1 ~ NEXT • RELATIVE –1 ~ ? • ABSOLUTE +/-n • ABSOLUTE 1 ~ FIRST (not 0!) • ABSOLUTE –1 ~ LAST • To use these, declare as SCROLL cursor EXEC SQL DECLARE crs SCROLL CURSOR FOR Product; Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  26. Pro*C e.g. • Example script: sample1.pc • See Pro*C tutorial • Pro*C compiler is proc • Must include/oracle/precomp/public • Must link with shared library/oracle/lib/libclntsh.so • Includes makefile proc.mk, but may require modifications sales% cp /oracle/precomp/demo/proc/sample1.pc . sales% proc sample1.pc sales% gcc -osample1 -I/oracle/precomp/public /oracle/lib/libclntsh.so sample1.c sales% sample1 Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  27. Interface: SQL/Host Language • Two languages: SQL, host (C/Java/whatever) • Benefits: • DB code (SQL is portable) • SQL, host language focus on own strengths • SQL code placed in host language code • SQL and host language have diff. data types • “impedance mismatch” • Data transferred with “shared variables” • Use cursors to access/modify data • Error messages placed in SQLSTATE Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  28. Agenda • Previously: Pro*C • Next: • Project part 3 is due next week • More programming for SQL • Embedded SQL • Pro*C, SQLJ • CLI • SQL/CLI in C • JDBC in Java • DBI/DBDs in Perl • PHP (HTML?) • SPs Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  29. Recap: Embedded SQL prog.pc Host language + Embedded SQL Oracle’s Pro*C Preprocessor Preprocessor prog.c Host Language + function calls Host language compiler Host language compiler gcc a.out Executable Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  30. Limitation of embedded SQL • Okay for apps with a fixed set of queries/updates • Maybe very simple kiosks • But consider, say, sqlplus or the sqlzoo website • Processes arbitrary queries from user • Can we do this with embedded SQL? Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  31. Dynamic SQL • In dynamic SQL, query string can be taken as a parameter, passed to DB • Two steps: • Prepare: compiles/optimizes the string • Execute: executes the query • Combine together: EXECUTE IMMEDIATE • But separate if query is executed many times (why?) Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  32. Dynamic SQL • myquery = a SQL variable • not prefixed by : void runQuery() { EXEC SQL BEGIN DECLARE SECTION; char *command; EXEC SQL END DECLARE SECTION; /* command set to some query string */ EXEC SQL PREPARE myquery FROM :command; EXEC SQL EXECUTE myquery; /* or just: */ EXEC SQL EXECUTE IMMEDIATE myquery; } Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  33. Dynamic SQL example • Example script: sample8.pc • See Pro*C tutorial • Goal: find employees from department 10 • Start with query as string sales% proc sample8.pc sales% gcc -I/oracle/precomp/public /oracle/lib/libclntsh.so –osample8 sample8.c sales% sample8 Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  34. Sketch of sqlplus • Something like the sqlplus program could be written as a simple dynamic SQL • future lab idea? EXEC SQL BEGIN DECLARE SECTION; char query[MAX QUERY LENGTH]; EXEC SQL END DECLARE SECTION; /* issue SQL> prompt */ /* read user's text into array query */ EXEC SQL EXECUTE IMMEDIATE :query; /* go back to reissue prompt */ Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  35. Dynamic SQL example 2 • Example script: sample10.pc • See Pro*C tutorial • Goal: recreate sqlplus • Copy-able commands: http://pages.stern.nyu.edu/~mjohnson/dbms/eg/lec18/proc.txt sales% proc sample10.pc sales% gcc -I/oracle/precomp/public /oracle/lib/libclntsh.so –osample10 sample10.c sales% sample10 Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  36. Next topic: SQL/CLI • Pro*C converts EXEC SQL code • --into what? • If we know the API (“Call-Level Interface”), can call library routines by hand • Is this better or worse? • Pros & cons • Won’t cover in depth Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  37. CLI: Java prog.pc Host language + Embedded SQL Oracle’s Pro*C Preprocessor Preprocessor Prog.java Host Language + function calls Host language compiler Host language compiler javac + jar Proj.class Executable Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  38. CLI - Overview • Similar to what really happens in embedded SQL implementations. • Major approaches: • SQL/CLI - standard of ODBC • JDBC - Java database connectivity • See http://cbbrowne.com/info/middleware.html for many options • Advantages over embedded SQL: • Avoid preprocessor-stage, easier to debug • In th., use same program with several DBMS • Disadvantages: • Must keep up to date with API changes • DBMS may have conflicting APIs Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  39. Next topic: JDBC (Java’s CLI) • As expected: Java too can talk to SQL • In some ways: much nicer • JDBC is an interface • Changes very little • Each vendor writes own plug-in • Dev. Strategy: write to API, compile with jar • See http://servlet.java.sun.com/products/jdbc/drivers for 219 (!) JDBC drivers Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  40. JDBC • Load JDBC driver for DBMS: • Obtain a connection: Class.forName("oracle.jdbc.driver.OracleDriver") Connection con = DriverManager.getConnection( “jdbc:oracle:thin:@l5.nyu.edu:1521:STN1", username, passwd); Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  41. JDBC • Obtain a statement object: • Run a query: • Or an update: Statement stmt = con.createStatement(); stmt.executeQuery(“SELECT * FROM table”); stmt.executeUpdate(“INSERT INTO tables” + “VALUES(‘abc’, ‘def’)”); Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  42. Prepared Statements in JDBC • JDBC also supports prepared statements • Obtain a PreparedStatement object: • Now execute: PreparedStatement ps = con.createStatement( “SELECT * FROM table”); ps.executeQuery(); Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  43. Obtaining query results • “Cursor” not used, but same idea • executeQuery() returns a ResultSet: • rs.next() advances to new row, returns false if EOF • getInt(i) returns ith column (if an int!) from current row ResultSet rs = ps.executeQuery(); while (rs.next()) { String val1 = rs.getString(1); int val2 = rs.getInt(2); … } Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  44. Java/JDBC/Oracle example • Example program: SQLRunner.java • Goal: run the SQL query passed • Copy-able commands: http://pages.stern.nyu.edu/~mjohnson/oracle/sqlruninstr.txt C:\proc>c:\j2sdk1.4.2_04\bin\javac SQLRunner.java C:\proc>c:\j2sdk1.4.2_04\bin\java -cp .;C:\OraHome_1\jdbc\lib\ojdbc14.jar SQLRunner "select * from emp" Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  45. Java/JDBC/MySQL example • Example program: MySQLRunner.java • Goal: run the SQL query passed • (Nearly) the same as before! • just using different DB/tables/login • mysql.jar is the MySQL J/Connector jar C:\proc>c:\j2sdk1.4.2_04\bin\javac MySQLRunner.java C:\proc>c:\j2sdk1.4.2_04\bin\java –cp .;mysql.jar MySQLRunner "select * from emp" Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  46. Recap: JDBC prog.pc Host language + Embedded SQL Oracle’s Pro*C Preprocessor Preprocessor Prog.java Host Language + function calls Host language compiler Host language compiler javac + jar Proj.class Executable Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  47. Java & parameter-based SQL • Like SQL/CLI in C, Java also supports parameterized queries (why?) • Prepare structure of query • Then can set values PreparedStatement ps = conn.prepareStatement( "SELECT * FROM table WHERE f1 = ? and f2 = ?"); ps.setString(1 “abc"); ps.setString(2, “def"); ResultSet rs = ps.executeQuery(); ... Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  48. Also: ODBC • Used by Microsoft platforms/tools, others • Access: • Start | Control Panel | Administrative Tools | Data Sources (ODBC) • Similar to JDBC • Won’t cover Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  49. Other combinations • So far: C/Pro*C, Java/JDBC • Q: Only choices? • A: No • “Call-level interface” for C: SQL/CLI • ODBC • Embedded Java: SQL/J • CLI for Perl, PHP, etc. • Stored Procedures (next) • {langs} x {dyn/not} x {SPs/not} x {DBMSs} Matthew P. Johnson, OCL1, CISDD CUNY, F2004

  50. Step back • Recall basic problem: need SQL plus stronger programming lang •  need to connect the two langs • In all these cases (and in the web app case), idea is: put SQL in (traditional-lang) programs • Another way: put programs in SQL • i.e., store programs on the DBMS • “stored procedures” Matthew P. Johnson, OCL1, CISDD CUNY, F2004

More Related