Abstract * Foil Index for this file
Given by Gang Cheng, C.W. Ou, Geoffrey C. Fox at CPS616 Basic Information Track for Computational Science on Winter-Spring Semester 97. Foils prepared 5 March 1996
Abstract * Foil Index for this file
| 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 |
This table of Contents
Abstract
Full HTML Index
| Instructor: Geoffrey Fox |
| Version 3 March 97 |
| teamed with Gang Cheng, Chao-Wei Ou |
| Syracuse University |
| 111 College Place |
| Syracuse |
| New York 13244-4100 |
Full HTML Index
| 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 |
Full HTML Index
| There are several ways to access and manipulate database objects in Oracle -- the diagram shows SQL, PL/SQl, OCI, Pro*C and OraPerl |
Full HTML Index
| 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 |
Full HTML Index
Data Structure
|
Relation operators
|
Full HTML Index
| The base database is two tables -- assume for later examples that value a21 is same as b21: |
Full HTML Index
| 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 |
Full HTML Index
Addon(SQL:Querying Multiple Tables Through Joins)
| Join Operation: a SELECT operation that combines rows from two or more tables. Each returned row contains data from more than one table |
Full HTML Index
| Embedded Operations: several relational operators can be embedded together recursively to perform complex operations |
| (remember value of b21 assumed same as a21) |
Full HTML Index
Addon(SQL:what is SQL?)
| 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
|
Full HTML Index
| 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:
|
Full HTML Index
| varchar2, number, long, date, raw, long raw, rowid, char |
Operators in Oracle SQL
|
| see handout for details |
Full HTML Index
| 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 |
Full HTML Index
These fall into six categories
|
| See handout for details about SQL syntax of each Oracle SQL command |
Full HTML Index
Remember schema are:
|
| 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 |
Full HTML Index
Define, alter the structure of, and drop schema objects. More specifically:
|
| DDL implicitly commits the preceding transaction, and starts a new transaction |
Full HTML Index
Addon(SQL:Update a Row and Delete a Row)
Full HTML Index
| Query or manipulate data in existing schema objects. |
More specifically functionality is:
|
| DML statements are the most frequently used SQL statements by developers |
Full HTML Index
Addon(SQL:Query by Name and Insert a Row)
This statement selects all columns of a row whose last_name field value equals to 'Fox':
|
This statement inserts a row into the person_info_table table
|
Full HTML Index
Addon(SQL:Create Tables)
This statement updates a column of a particular row
|
This statement deletes a row from a table
|
Full HTML Index
Transaction control statements manage the changes made by DML statements, and group DML statements into transaction. More specifically:
|
Full HTML Index
Relationship between SQL and PL/SQL
|
Full HTML Index
| 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
|
Full HTML Index
Full HTML Index
Full HTML Index
| 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 |
Full HTML Index
A standalone stored procedure has two parts
|
| Basic Structure: |
Full HTML Index
The declaration of a package has two parts
|
Full HTML Index
CREATE OR REPLACE PACKAGE phone_pkg AS
|
| END phone_pkg; |
| / |
CREATE OR REPLACE PACKAGE BODY phone_pkg AS
|
| END phone_pkg; |
| / |
Full HTML Index
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 |
| 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 |
Full HTML Index
| 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
|
| Closing a cursor means the information currently in the associated private area is lost and its memory is deallocated |
Full HTML Index
Addon(PL/SQL Query Examples -- Query by Name)
| 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 =
|
| 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! |
Full HTML Index
Addon(PL/SQL:Query Examples)
| We now define some PL/SQL variables to reference database entries fetched in previous SELECT command |
lname person_info_table.last_name%TYPE;
|
phone phone_list_table.phone_no%TYPE;
|
| -- Note format tablename.columnname to |
| reference database variables and %TYPE syntax to inherit database type in PL/SQL datatype |
Full HTML Index
Addon(PL/SQL:Query Examples)
| BEGIN |
| OPEN person_cur(name); -- open the cursor |
LOOP -- Fetch each row matching
|
| FETCH person_cur INTO lname,fname,phone,ptype; |
EXIT WHEN person_cur%NOTFOUND;
|
| /* 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; |
Full HTML Index
On invoking the stored procedure with the name 'fox' - get_phoneno_by_name('fox'), one will generate the following two lines:
|
| 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 |
Full HTML Index
| 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 |
Full HTML Index
| For procedure, their state persists only for the duration of a procedure call and is lost when the procedure execution terminates |
For package
|
Overloading of Package Procedure Names - PL/SQL allows two or more procedures in the same package to have the same name.
|
Full HTML Index
| 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 |
Full HTML Index
Full HTML Index
| #!/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
|
| AND (a.person_id = b.person_id)"; |
Full HTML Index
| # 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 |
Full HTML Index