untitled presentation CPS 616 January-April 1997 Technologies for the Information Age Oracle RDBMS and Web Integration Module 2: Data Access SQL and Progamming PL/SQL 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 -- assume for later examples that value a21 is same as b21: 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 (remember value of b21 assumed same as a21) 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 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 Definition Language statements (DDL) Data Manipulation Language statements (DML) Transaction Control statements Session Control statements System Control statements Embedded SQL statements See handout for details about SQL syntax of each Oracle SQL command DDL Contrasted to DML Remember schema are: tablespaces, tables, views, sequences, PL/SQL procedures/functions/packages, synonyms, indexes,clusters. We gave plenty of examples of schema in first module on Oracle/Databases The DDL defines the structure and existence of Schema The DML adjusts the data IN schema 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) 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 Data Manipulation Language statements (DML) Query or manipulate data in existing schema objects. 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Õ; 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) 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 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: 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 A cursor is a handle to or name for a specific private SQL area. such an area is also called a 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 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 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) || Ô%Õ)); Note that above we have SQL not PL/SQL and variables are column names from database Argument cname of CURSOR will be supplied when we invoke later! An Example of PL/SQL Stored Procedure - II We now define some PL/SQL variables to reference database entries fetched in previous SELECT command 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 and %TYPE syntax to inherit database type in PL/SQL datatype 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; How to use Example of PL/SQL Stored Procedure On invoking the stored procedure with the name ÔfoxÕ - get_phoneno_by_name(ÔfoxÕ), one 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 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