untitled presentation CPS 616 January-April 1996-7 Computational Science Track on Base Technology for the Information Age: Oracle RDBMS and Web Integration Module 2: Data Access and Progamming in Oracle Instructor: Geoffrey Fox Version 3 March 97 teamed with Gang Cheng, Chao-Wei Ou Syracuse University 111 College Place Syracuse New York 13244-4100 Abstract of: Module 2: Data Access and Progamming in Oracle General Structure of SQL Features of Oracle SQL and Examples The six classes of SQL commands: Data Manipulation Language statements (DML), Data Definition Language statements (DDL), Transaction Control statements, Session Control statements, System Control statements, Embedded SQL statements Programming in PL/SQL with examples and discussion of difference between SQL and PL/SQL Oraperl - Access Oracle RDBMS from Perl Data Access and Progamming in Oracle There are several ways to access and manipulate database objects in Oracle -- the diagram shows SQL, PL/SQl, OCI, Pro*C and OraPerl What we will do in These Lectures We will cover in this module the basic three languages - SQL, PL/SQL and Oraperl Another important approach - embedded SQL in a host programming language such as SQL in C (Pro*C) is not covered here but is highly recommended for serious Oracle programmers Oracle Call Interface (OCI) is used by Oracle system developer Some Basic Terminology for Relational Database Model Data Structure relations (files, tables) tuples (records, rows) attributes (fields, columns) Relation operators scan (select-project) (one or more tables , a condition to satisfy, and an attribute list) sort (reorder) aggregate operators (SUM,AVG,MAX,MIN,...) insert/delete/update modification of tables set operators (union, intersection, difference) join, merge and division operators can be embedded recursively with the characteristics: uniformity of the data and operators as input and output is same data object (feature of 4GL) source of data-flow execution model Examples of Typical Relational Operations The base database is two tables: Examples of SELECT and PROJECT Selection Operation: create a new table by limiting the set of rows of a given table according to a specified criterion Projection Operation: acts on a table by deleting some of its columns Examples of JOIN and UNION Select Operations Join Operation: a SELECT operation that combines rows from two or more tables. Each returned row contains data from more than one table Examples of INTERSECT and recursive embedded Operations Embedded Operations: several relational operators can be embedded together recursively to perform complex operations Structure Query Language (SQL) - I A database language specifies the semantics of various components of a DBMS: structures and operation of a data model, data definition, data access, security, programming language interface, and data administration Industry accepted Standard, first introduced by ANSI in 1986, current SQL92 by ANSI and ISO, new standard SQL3 with enhancements in object-oriented data management is undergoing Portable to all RDBMS systems Built on relational model, simple and powerful Non-procedure language, only specify Òwhat-to-doÓ, not Òhow-to-doÓ (4GL) Extended to object-oriented programming interface This extended model competes with fledging object-oriented database in industry Structure Query Language - II SQL is a very simple, yet powerful database access language, non-procedural and interpretive language Oracle SQL has many extensions to the ANSI/ISO standard SQL language Although some Oracle tools (PL/SQL,Pro*C,Oraperl) and applications simplify or mask the use of SQL, all database operations are performed using SQL Any other data access method would circumvent the security built into Oracle and potentially compromise data security and integrity Data-independent --- two-phase database schema (logical and physical database) SQL in Oracle 7 will do the following: Create tables in the database Store information in tables select exactly the information you need from your database Make changes to your data and the structure of underlying tables, and Combine and calculate data to generate the information you need SQL and Parallelism Parallel Databases are in some sense more succesful and powerful than compilers because SQL is a naturally parallel ÒfunctionalÓ language without dependency problems present in Fortran, C C++ Java Thus whereas one canNOT write a universal parallel compiler which will run all Fortran (say) programs, one can write a universal parallel database server which will run all SQL codes Note there are functional languages such as SISAL used in scientific computing but this has not proven to be efficient and one needs the Òhow-to-do-itÓ features of the languages such as Fortran Data Types and Operators in Oracle SQL varchar2, number, long, date, raw, long raw, rowid, char Operators in Oracle SQL arithmetic operators: +, -, *, / character operators: || (concatenation) comparison operators: =, !=, >, <, >+, <=, IN, NOT IN, ANY, SOME, ALL, between x AND y, exists, x like y, IS NULL logical operators: not, and, or set operators: union, union all, intersect, minus see handout for details Functions in Oracle SQL number functions: abs, ceil, cos, cosh, exp, floor, ln, log, mod, power, round, sign, sin, sinh, sort, tan, tanh, trunc character functions: chr, concat, initcap, lower, lpad, ltrim, replace, rpad, rtrim, soundex, substr, substrb, translate, upper, ascii, intstr, instrb, length, lengthb date functions: add_months, last_day, months_between, new_time, next_day, round, sysdate, trunc conversion functions: chartorowid, convert, hextoraw, rawtohex, rowidtochar, to_char, to_date, to_label, to_number group functions: avg, count, min,max, stddev, sum, variance Classes of Oracle SQL Statements These fall into six categories Data Manipulation Language statements (DML) Data Definition Language statements (DDL) Transaction Control statements Session Control statements System Control statements Embedded SQL statements See handout for details about SQL syntax of each Oracle SQL command Data Manipulation Language statements (DML) Query or manipulate data in existing schema objects. Remember schema are: tablespaces, tables, views, sequences, PL/SQL procedures/functions/packages, synonyms, indexes,clusters. More specifically functionality is: remove rows from tables or views (DELETE) see the execution plan for a SQL statment (EXPLAIN PLAN) add new rows of data into a table or view (INSERT) lock a table or view, temporarily limiting other usersÕ access to it (LOCK TABLE) retrieve data from one or more tables and views (SELECT) change column values in existing rows of a table or view (UPDATE) DML statements are the most frequently used SQL statements by developers DML Examples - I This statement selects all columns of a row whose last_name field value equals to ÔFoxÕ: SELECT * FROM person_info_table WHERE last_name = ÔFoxÕ; This statement inserts a row into the person_info_table table INSERT INTO person_info_table (person_id, last_name, first_name, title, user_name, address) VALUES (99, ÔlinÕ, Ôtseng-huiÕ, ÔgraÕ, ÔthlinÕ, Ô3-211Õ); DML Examples - II This statement updates a column of a particular row UPDATE person_info_table SET first_name = ÔfrankÕ WHERE user_name = ÔthlinÕ; This statement deletes a row from a table DELETE FROM person_info_table WHERE user_name = ÔthlinÕ; Data Definition Language statements (DDL) Define, alter the structure of, and drop schema objects. More specifically: create, alter, and drop schema objects and other database structures, including for DBA the database itself and database users (CREATE,ALTER,DROP) change the names of schema objects (RENAME) delete all the data in schema objects without removing the objectsÕ structure (TRUNCATE) gather statistics about schema objects, validate object structure, and list chained rows within objects (ANALYZE) grant and revoke privileges and roles (used by DBA, GRANT,REVOKE) turn auditing options on and off (AUDIT,NOAUDIT) add a comment to the data dictionary (COMMENT) DDL implicitly commits the preceding transaction, and starts a new transaction DDL Statement Examples Transaction Control Statements Transaction control statements manage the changes made by DML statements, and group DML statements into transaction. More specifically: make a transactionÕs changes permanent (COMMIT) undo the changes in a transaction, either since the transaction started or since a savepoint (ROLLBACK) set a point to which you can roll back (SAVEPOINT) establish properties for a transaction (SET TRANSACTION) Session and System Control Statements Session Control Statements Manage the properties of a particular userÕs session. More specifically: alter the current session by performing a specialized function, such as enabling and disabling the SQL trace facility (ALTER SESSION) enable and disable roles (groups of privileges) for the current session (SET ROLE) System Control Statements used by DBA to change the properties of the Oracle Server instance The only system control command is ALTER SYSTEM Embedded and Recursive Sequel Statements Embedded SQL statements They incorporate DDL, DML, and transaction control statements within a procedural host language program (eg. in C/C++) They are used with the Oracle Precompilers Recursive SQL - When a DDL statement is issued, Oracle implicitly issues recursive SQL statements that modify data dictionary information Users need not be concerned with recursive SQL internally performed by Oracle Oracle 7 Cursors or Named Memory A cursor is a handle or name for an area in memory in which a parsed statement and other information for processing the statement is kept; such an area is also called a private SQL area. Although most Oracle users rely on the automatic cursor handling of the Oracle utilities, the programmatic interface (such as in PL/SQL) offer application designers more control over cursors Shared SQL Statements Oracle automatically notices when applications send identical SQL statements to the database. If two identical statements are issued, the SQL area used to process the first instance of the statement is shared by the subsequent instance of that same statement Shared SQL areas are shared memory areas. It reduces memory usage on the database server, therby increasing system thoughput Note Oracle just shares the compiled SQL -- the query output is NOT shared and in fact compiled SQL is always re-executed. PL/SQL - OracleÕs Procedural Language to SQL Relationship between SQL and PL/SQL PL/SQL is an application development language. while SQL is the basic data access language in Oracle that all other Oracle products and tools use, including PL/SQL engine PL/SQL is a superset of SQL, allowing all the DML statements, cursor operations and the transaction processing statements in SQL SQL is a non-procedural language, only allowing to specify what to do, not how to do. while PL/SQL is the procedual extension of SQL, allowing one to specify both Advantages of PL/SQL Procedural language features! Improved performance - PL/SQL code is stored and shared in a RDBMS server Enhanced productivity - allow modularity and software reuse Portability - across all Oracle platforms ÒSeamlessÓ integration with the RDBMS server - the procedural option Oracle allows several other options distributed, parallel server, parallel query, context server, media server, web server Basic 3GL Programming Language features in PL/SQL Block (modular) program structure Flow control statements such as if-then-else, loops Variables, constants and rich data types Cursors Exception handling Architecture of The PL/SQL ÒEngineÓ PL/SQL Data Types Program Units in PL/SQL PL/SQL Block - an unnamed PL/SQL procedure that groups SQL statements and PL/SQL constructs into a single program unit Stored Procedures - A PL/SQL block stored in the database and called by name from applications Stored Functions - identical to procedures except functions always retun a single value to the caller while procedures do not Stored Packages - a method to encapsulate and store related procedures, functions, variables, and other package constructs together as a unit in the database PL/SQL Stored Procedure A standalone stored procedure has two parts Specification - declare the procedure with the procedure name and its argument list (names and their databtypes) Body - define the procedure with a PL/SQL block consists of SQL and PL/SQL statement Both parts are created in one step using the CREATE PROCEDURE command Basic Structure: How to use Example of PL/SQL Stored Procedure To invoke the stored procedure with the name ÔfoxÕ - get_phoneno_by_name(ÔfoxÕ), it will generate the following two lines: The office phone no. of Geoffrey Fox: 3154432163 The home phone no. of Geoffrey Fox: 3156827945 Note a cursor is used to hold the query results in memory for processing htp.p is the invocation of a procedure p in a package htp to print out characters to stdout When this procedure is created/compiled, all the objects referred in the procedure (e.g. the two tables) must be present and valid in the RDBMS PL/SQL Packages The declaration of a package has two parts package specification - contains the declarations of procedures (and functions), variables, constants, and exceptions that are accessible outside the package package body: defines procedures (and functions), cursors, and exceptions that are declared in the package specification. It also defines those PL/SQL program units and constructs (procedures, variables etc) that are only local to the package body The package specification is created by CREATE PACKAGE command The package body is created by CREATE PACKAGE BODY command Example of A PL/SQL Package Structure CREATE OR REPLACE PACKAGE phone_pkg AS PROCEDURE get_phone_by_no (no IN NUMBER); PROCEDURE get_person_by_name (name IN VARCHAR2); END phone_pkg; / CREATE OR REPLACE PACKAGE BODY phone_pkg AS PROCEDURE get_phone_by_no (no IN NUMBER) is Begin End get_phone_by_no; PROCEDURE get_person_by_name (name IN VARCHAR2) is Begin End get_person_by_name; END phone_pkg; / Cursors in PL/SQL - I PL/SQL implicitly declares a cursor for all SQL DML statements, including queries that return only one row For queries that return more than one row, you must explicitly declare a cursor to process the rows individually A cursor is a handle to or name for a specific private SQL area. In application development, a cursor is a named resource available to a program, which can be specifically used for parsing SQL statements embedded with the application Cursors in PL/SQL - II You can use a cursor to re-execute SQL statements - after each stage of execution, the cursor retains enough information about the statement to re-execute it without starting over again Cursors are ÒstructuredÓ in that know about SQL -- you can open, close and fetch from a cursor Fetch gets get row from results table -- called a cursor as moves through table Closing a cursor means the information currently in the associated private area is lost and its memory is deallocated An Example of PL/SQL Stored Procedure - I A stored procedure to query the phone-list database by a personÕs last or first name may look like this: CREATE PROCEDURE get_phoneno_by_name (name IN VARCHAR2) IS CURSOR person_cur(cname IN VARCHAR2) IS SELECT last_name,first_name,phone_no,phone_type from person_info_table,phone_list_table WHERE (person_info_table.person_id = phone_list_table.person_id) AND ( last_name LIKE (Ô%Õ || LOWER(cname) || Ô%Õ) OR first_name LIKE (Ô%Õ || LOWER(cname) || Ô%Õ)); An Example of PL/SQL Stored Procedure - II lname person_info_table.last_name%TYPE; -- a variable to hold last name fname person_info_table.first_name%TYPE; -- a variable to hold first name phone phone_list_table.phone_no%TYPE; -- a variable to hold phone no. ptype phone_list_table.phone_type%TYPE; -- a variable to hold phone type -- Note format tablename.columnname to reference database variables An Example of PL/SQL Stored Procedure - III BEGIN OPEN person_cur(name); -- open the cursor LOOP -- Fetch each row matching the query into variables repeatedly FETCH person_cur INTO lname,fname,phone,ptype; EXIT WHEN person_cur%NOTFOUND; -- check end of result buffer /* print out the query result */ htp.p(ÔThe Ô||ptype||Õ phone no. ofÔ ||fname||Õ Ô||lname||Õ: Ô||phone); END LOOP; CLOSE person_cur;-- close the cursor after it is done END get_person_by_name; Explanation of Example of PL/SQL Stored Procedure LOWER is a built-in function converting a string into its lower case || concatenates two strings into one. E.g., ÔCPSÕ||Õ616Õ equals ÔCPS616Õ (same as + in Java, . in Perl) pname dbname%TYPE implies PL/SQL variable pname inherits data type of dbname LIKE is a comparison operator such that x LIKE y is TRUE if x does match the pattern y. The character Ô%Õ matches any string of zero or more characters The SQL statement looks at rows whose last_name or first_name field has the input string cname as a substring in lower case Scope and Life time of Procedure and Package Variables, Constants, and Cursors For procedure, their state persists only for the duration of a procedure call and is lost when the procedure execution terminates For package the state of packages declared in the specification or body (i.e., not within a procedure) persists for the duration of the userÕs session (i.e, across trnasactions) every session of a package has its own set of package variables, constants and cursors during a session, the package user can modify the values of the package variables and cursors Overloading of Package Procedure Names - PL/SQL allows two or more procedures in the same package to have the same name. This option is very useful where you need a procedure to accept arguments of different datatypes or different number of arguments Oraperl - Oracle Data Access From a Perl Program Perl With Built-in Functions to Access Oracle RDBMS Oraperl is built by extending the Perl Interpreter through the Iusersubs feature Built on top the Oracle Call Interface Driver Layer Oraperl is public domain software Major Built-in Functions in Oraperl &ora_login() - connect to the database server &ora_open() - open a cursor with a specified SQL statement &ora_bind() - assign the actual value to a substitute variable in the SQL statement &ora_fetch() - execute the SQL statement and fetch rows of the result &ora_close() - close a cursor &ora_do() - execute a SQL statement which doesnÕt require explicit cursor &ora_logoff() - disconnect from the database server An Example in OraPerl - Query a PersonÕs Phone Number By Last or First Name - I #!/usr/local/bin/oraperl # Specify the user name/password and a database server $user_name = Òcps616Ó; $passwd = ÒxyzÓ; $database = $ENV{ÔORACLE_SIDÕ}; # Login to the ORACLE SQL. $lda = &ora_login($database, $user_name, $passwd) || die; $ora_errstr; # Cursor for quering phone number by a personÕs first or last name $phone_cursor = ÒSELECT last_name, first_name, phone_no, phone_type FROM person_info_table a, phone_list_table b WHERE (last_name LIKE (Ô%Õ || LOWER(:1) || Ô%Õ) OR first_name LIKE (Ô%Õ || LOWER(:1) || Ô%Õ)) AND (a.person_id = b.person_id)Ó; An Example in OraPerl - Query a PersonÕs Phone Number By Last or First Name - II # Open cursor and execute the query $csr = &ora_open($lda, $phone_cursor); # Assign actual value in the SQL statement &ora_bind($csr, ÔgeoffreyÕ); # Fetch the query results into array variables and print them out while (($lname,$fname,$phone,$ptype) =&ora_fetch($csr))) { print ÒThe $ptype $phone no. of $fname $lname:$phone\nÓ;} &ora_close($csr); # Close a cursor &ora_logoff($lda); # disconnect the database server More detailed information about SQL, PL/SQL and Oraperl, including examples, can be found in the handout and on-line at http://osprey7.npac.syr.edu :1997/database