Databases Illuminated Chapter 5 Relational Database Management Systems

Databases Illuminated Chapter 5 Relational Database Management Systems and SQL

History of Relational DBMS & SQL • Relational databases proposed by E. F. Codd in 1970 paper • SQL used in System R, research relational database in early 1970 s-D. D. Chamberlin et. al. at IBM Research Center, San Jose, California • SQL used in Oracle-Ellison, Miner & Oakes, released in late 1970 s • QUEL used in early version of University INGRES, Wong & Stonebraker, UC Berkeley • SQL incorporated into IBM’s SQL/DS in 1981, and DB 2 in 1983 • SQL used in Microsoft SQL Server, My. SQL, Informix, Sybase, Post. Gre. SQL, Microsoft Access, and others

Standards • ANSI and ISO published SQL standards in 1986, called SQL-1 • Minor revision, SQL-89 • Major revision, SQL-2, 1992 • Major revision, SQL-3, 1999, 2003, 2006, 2008, 2011 multi-part revision, includes new data types, objectoriented (OO) facilities, user defined datatypes (UDTs), triggers, support for XML, temporal databases • Most vendors support standard, but have slight variations of their own

Components of SQL • Data Definition Language – DDL – Used to create and modify database structures • Data Manipulation Language – DML – Used to store, access and modify data • Authorization language – Used to grant privileges to users

Relational Database Architecture • • Separate external, logical, internal models Logical level-base tables and indexes Indexes, B+ or B trees – maintained by system Relational views (external level) - derived from base tables • Users see views or base tables, or combination • Internal level - files • SQL supports dynamic database definition-can modify structures easily

Figure 5. 1 Three-Level Architecture for Relational Databases

DDL Commands CREATE DATABASE CREATE SCHEMA CREATE TABLE CREATE INDEX ALTER TABLE RENAME TABLE DROP INDEX Also – CREATE VIEW

CREATE DATABASE, CREATE SCHEMA • CREATE DATABASE dbname; • CREATE SCHEMA schema_name [AUTHORIZATION user_name];

CREATE TABLE base-table-name ( colname datatype [column constraints], [, colname datetype [column constraints …]]. . . , [table constraints] [PARTITION BY…] [storage specifications]);

Identifiers • No SQL keywords • Table name unique within the database • Column name unique within the table • In Oracle, identifiers must be at most 30 characters long, begin with an alphabetic character, and contain only alphanumeric characters (but _, $ and # are permitted)

Datatypes • Each column must have a datatype specified • Available datatypes vary from DBMS to DBMS • Standards include various numeric types, fixed-length and varyinglength character strings, bit strings, and user-defined types • Oracle types include CHAR(N), VARCHAR 2(N), NUMBER(N, D), DATE, BLOB (binary large object), IDENTITY, and others • SQL Server includes types of NUMERIC, BINARY, CHAR, VARCHAR, DATETIME, MONEY, IMAGE, and others • Microsoft Access supports several types of NUMBER, as well as TEXT, MEMO, DATE/TIME, HYPERLINK, YES/NO, and others

Constraints Column constraints – in-line constraints NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, REF, CHECK, and DEFAULT Table constraints – out-of-line constraints all of the above except NOT NULL

Creating the Tables for the University Database Figure 5. 2

Indexes • Can create any number of indexes for tables • Stored in same file as base table • Facilitate fast retrieval of records with specific values in a column • Keep track of what values exist for the indexed columns, and addresses of records with those values • B+ trees or B trees used – see Appendix A for review of concepts • Overhead – system must maintain index

CREATE INDEX Command CREATE [UNIQUE] INDEX indexname ON basetablename (colname [order] [, colname [order]]. . . ) [CLUSTER] ; Ex. CREATE INDEX Student_last. Name_ix ON Student (last. Name); • UNIQUE specification enforces unique values for indexed column or combination of columns • Except when specified, column values need not be unique • Order is ASC(default) or DESC • Can have major and minor orders • CLUSTER specification keeps records with same value for indexed field together • Oracle automatically indexes primary key columns

ALTER TABLE Command • To add a new column ALTER TABLE basetablename ADD columnname datatype; Ex. ALTER TABLE Student ADD birthdate DATE; – Cannot specify NOT NULL, since existing records have no value for this field • To drop a column ALTER TABLE basetablename DROP COLUMN columnname; Ex. ALTER TABLE Student DROP COLUMN birthdate; • To change a column’s properties ALTER TABLE basetablename MODIFY COLUMN colname [new specifications]; • To rename a column ALTER TABLE Student RENAME COLUMN colname TO new-colname;

Other Changes to Tables • To add a constraint ALTER TABLE basetablename ADD CONSTRAINT constraint_defn; • To drop a constraint ALTER TABLE basetablename DROP CONSTRAINT constraint_name; • To rename a table: RENAME TABLE old-table-name TO new-table-name; Ex: RENAME TABLE FACULTY TO TEACHERS; • To drop a table: DROP TABLE basetablename; Ex. DROP TABLE CLASS; • To drop an index: DROP INDEX indexname; Ex. DROP INDEX Student_last. Name_ix;

Worker & Projects Example • Worker table has foreign key that refers to Dept • Dept table has foreign key that refers to Worker • In theory, each must precede the other. • Figure 5. 3 shows how to create these tables

Figure 5. 3 DDL & Inserts for Worker and Project Example

SQL DML • Non-procedural, declarative language • Can be interactive, can be embedded in host language, or can be stand-alone programming language (SQL/PSMs) • Basic commands SELECT UPDATE INSERT DELETE

SELECT Statement SELECT [DISTINCT] col-name [AS newname], [, col-name. . ]. . . FROM table-name [alias] [, table-name]. . . [WHERE predicate] [GROUP BY col-name [, col-name]. . . [HAVING predicate]] or, [ORDER BY col-name [, col-name]. . . [ OFFSET offset { ROW | ROWS } ] [ FETCH { FIRST | NEXT } [ { rowcount | percent PERCENT } ] { ROW | ROWS } { ONLY | WITH TIES }]; • Powerful command – equivalent to relational algebra’s SELECT, PROJECT, JOIN and more… • Can be applied to one or more tables or views • Can display one or more columns (renaming if desired) • Predicate is optional, and may include usual operators and connectives • Can put results in order by one or more columns • Can group together records with the same value for column(s) • Can also use predefined functions • See list of examples, Section 5. 4. 1 -5. 4. 3

UPDATE Operator UPDATE SET [WHERE tablename columnname = expression [, columnname = expression]. . . predicate]; • Used for changing values in existing records • Can update, zero, one, many, or all records in a table with each UPDATE statement • For null value, use SET columnname = NULL • can use a sub-query to identify records to be updated • cannot update an IDENTITY column

INSERT Operator INSERT INTO VALUES tablename [(colname [, colname]. . . )] (constant [, constant]. . . ); • Used for inserting new records, one at a time • Not necessary to name columns if values are supplied for all columns, in proper order • To insert null value for a column, specify only the other columns or write null as the value • Can specify DEFAULT if column has a default defined • Can specify values for some columns, in any order, as long as values match order • Cannot insert values for IDENTITY columns

Sequences in Oracle CREATE SEQUENCE seqname [START WITH n] [INCREMENT BY n]; • Default for n is 1 • Useful for generating automatic numbering for records • not tied to a particular table • seqname. NEXTVAL -generates new value • seqname. CURRVAL -returns current value • To drop a sequence: DROP SEQUENCE seqname

DATE and SYSDATE • • Oracle uses default format 'dd-mon-yy' for dates SYSDATE returns the current system date and time TRUNC(SYSDATE) – sets time part to 00: 00 TO_CHAR(datevalue, format string) returns part of date specified by format string Ex. TO_CHAR(birth. Date, 'YYYY') returns year of birth. Date • TO_DATE (string, format string) converts an appropriate string into a DATE format Ex. TO_DATE('01/01/2015', 'MM/DD/YYYY') TIMESTAMP-similar to DATE, but accurate to fractions of a second – typical format is 'MM/DD/YYYY: HH: MI: SS. FF'

DELETE Operator DELETE FROM WHERE tablename predicate; • Used for deleting existing records from database • Can delete zero, one, many, or all records • Operation may not work if referential integrity would be lost • Can use a sub-query to target records to be deleted • If you delete all records from a table, its structure still remains, and you can insert into it later

Relational Views • Can be subsets of base tables, or subsets of joins, or contain calculated data • Reasons for views – Allow different users to see the data in different forms – Provide a simple authorization control device – Free users from complicated DML operations – If database is restructured, view can keep the user's model constant

Create View CREATE VIEW viewname [(viewcolname, viewcolname]. . . )] AS SELECT colname [, colname]. . . FROM basetablename [, basetablename]. . . WHERE condition; • • Can create vertical subset of table, choosing only certain columns, with no WHERE, called valueindependent view Can choose only certain rows, using WHERE, called value-dependent view Can use a join of tables to create view of combination Can use functions or subqueries in SELECT

Using Views • Can write new SQL statements using view name in FROM line • Can create a view of a view • Can sometimes insert/update a view – requires that the primary key be in the view – Actually updates underlying table • Can modify existing view by using CREATE OR REPLACE VIEW [viewname] AS …

Active Databases-Constraints • DBMS monitors database to prevent illegal states, using constraints and triggers • Constraints – can be specified when table is created, or later – IMMEDIATE MODE: constraint checked when each INSERT, DELETE, UPDATE is performed – DEFERRED MODE: postpones constraint checking to end of transaction – write SET CONSTRAINT name DEFERRED – Can use ALTER TABLE table-name DISABLE CONSTRAINT constraint –name; – and later ALTER TABLE table-name ENABLE CONSTRAINT constraint – name;

Triggers • More flexible than constraints • Use ECA model: – event, some change made to the database – condition, a logical predicate – action, procedure done when the event occurs and the condition is true, also called firing the trigger • Can be fired before or after insert, update, delete • Trigger can access values it needs as : OLD. and : NEW. – prefix : OLD refers to values in a tuple deleted or to the values replaced in an update – prefix : NEW refers to the values in a tuple just inserted or to the new values in an update. • Can specify whether trigger fires just once for each triggering statement, or for each row that is changed by the statement

Trigger Syntax CREATE OR REPLACE TRIGGER trigger_name [BEFORE/AFTER] [INSERT/UPDATE/DELETE] ON table_name [FOR EACH ROW] [WHEN condition] BEGIN trigger body END; • Can disable triggers using ALTER TRIGGER name DISABLE; • Later write ALTER TRIGGER name ENABLE; • Can drop triggers using DROP TRIGGER name; • See examples Figure 5. 5 Enabling and Disabling Constraints

Figure 5. 6 A Tables for Triggers Figure 5. 6 B Trigger for Student Enrolling in a Class

Figure 5. 6 C Trigger for Student Dropping Classes Figure 5. 6 D Trigger for Student Changing Classes

Ending Transactions • COMMIT makes permanent changes in the current transaction • ROLLBACK undoes changes made by the current transaction

Temporal Databases in Oracle • include some representation of time • keep past records as well as current data • valid time- time a value is valid in the real world-business time or application time Example: Dates an insurance policy is valid • transaction time or system time-time changes were made to the database Can be required as evidence of transactions • Bitemporal relations store both types

User-Valid Time table CREATE TABLE table_name(. . . start_time TIMESTAMP, end_time TIMESTAMP, … PERIOD FOR user_valid_time (start_time, end_time)); • Insert values as usual, providing dates for time columns • Can select records valid on a certain date SELECT column_names FROM table_name WHERE date_value BETWEEN start_time AND end_time; • Can select records currently valid SELECT columns FROM table_name AS OF PERIOD FOR user_valid_time (SYSTIMESTAMP); • Can add valid time to existing tables ALTER TABLE table_name ADD PERIOD FOR user_valid_time;

Figure 5. 10 A The Initial Assign Table Figure 5. 10 B The Updated Assign Table

Transaction Time • each transaction is assigned a timestamp • All changes to the database are given a System Change Number (SCN) • system has to be configured to implement flashback technology • SELECT can include AS OF TIMESTAMP clause • Select can specify versions VERSIONS BETWEEN { SCN | TIMESTAMP } start AND end or VERSIONS PERIOD FOR user_valid_time [ BETWEEN TIMESTAMP start AND end ]

SQL/PSM • Persistent Stored Modules-used to create internal routines within database space • Can be saved with database schema and invoked • Oracle uses PL/SQL, accessed within SQL*Plus • Provides complete programming language facilities-constants, declared variables, assignment statements, control structures, error handling, etc. • Can also use Java in internal routines in Oracle

PL/SQL Procedures • Create procedures-can write in SQL*Plus window: CREATE [OR REPLACE] PROCEDURE procedure_name (formal parameter_list) {AS/IS} declarations of local variables and constants BEGIN executable statements [EXCEPTION exception handling] END; / • • Mode of parameters may be IN, OUT, or IN OUT To compile, after END; enter forward slash (/) To debug, write show errors, make corrections, and recompile To execute EXECUTE procedure_name(actual_ parameter_ list);

Figure 5. 11 PL/SQL Procedure to Find a Student Name

PL/SQL Functions • Create functions-can write in SQL*Plus window: CREATE [OR REPLACE] FUNCTION function_name (parameter list) RETURNS SQLdatatype {AS/IS} declarations of local variables BEGIN function code (must include a RETURN statement) [EXCEPTION exception handling] END; / • Mode of parameters is IN only, no OUT or IN OUT • A function is invoked by using its name, typically in an assignment statement • Example: SET new. Val = My. Function(val 1, val 2);

Elements of PL/SQL • Declarations identifier datatype [: = initial value]; • Body of code –SQL DML (but not DDL) statements SELECT. . INTO variables FROM. . . WHERE…; UPDATE. . SET…= ; –assignment statements for declared variables var : = value; –control statements IF…THEN…ELSE…END IF; CASE…WHEN…END CASE; FOR…LOOP…END LOOP; WHILE… DO… END WHILE, REPEAT…UNTIL…END REPEAT; FOR …DO… END FOR – output statements DBMS_OUTPUT. PUT_LINE(output_expression); –error handling

Cursors • • Needed when processing multiple records Symbolic pointer to a multiset Retrieves one row at a time Declaring a cursorname IS query; • Opening the cursor, which executes the query OPEN cursorname; • Fetching the next row (usually in a loop) FETCH cursorname INTO hostvariables; • Closing the cursor CLOSE cursorname;

Figure 5. 12 Using a Cursor in PL/SQL

Error Handling in PL/SQL EXCEPTION WHEN exception 1 THEN … WHEN exception 2 THEN … … WHEN OTHERS THEN … END; • Examples of predefined exceptions NO_DATA_FOUND, DUP_VAL-ON-INDEX • Usual code for other exceptions when others then DBMS_OUTPUT. PUT_LINE('Error-' || SQLERRM); • User-defined exceptions-declare, give condition, give action exception_name EXCEPTION; if (exception_condition) then RAISE exception_name… WHEN exception_name THEN action;

Embedded SQL • SQL can be embedded in host languages, such as Visual Basic, C, C++, Java, Ada, … • Client-side programs • Host language provides control structures; SQL used for database access • SQL statements preceded by EXEC SQL, end with ; • Executable SQL statement can appear wherever a host language executable statement can appear • Pre-compiler for DB compiles SQL separately from program; creates access module • Host language statements compiled as usual • Program-DBMS data exchange done using shared variables

Shared Variables • Declared in SQL declaration section. Ex: EXEC SQL BEGIN DECLARE SECTION; char stu. Number[5]; int stu. Credits; char SQLSTATE[6]; EXEC SQL END DECLARE SECTION; • SQLSTATE can used for error conditions – '00000' means no error, while '02000' means no tuple found for query – Value of SQLSTATE should be tested in host language, as in WHILE(SQLSTATE='00000') or UNTIL(SQLSTATE='02000') • SQLCA, SQL communications area, can be used instead – Ex. #include <sqlca. h> - in host program – Error codes returned, can test with EXEC SQL WHENEVER <condition> <action>;

Single-row Embedded SQL SELECT stu. Number = 'S 1001'; EXEC SQL SELECT Student. last. Name, Student. first. Name, Student. major, Student. credits INTO : stu. Last. Name, : stu. First. Name, : stu. Major, : stu. Credits FROM Student WHERE Student. stu. Id = : stu. Number; • INTO line lists shared variables declared previously • In SQL statements, use colon before shared variables to distinguish them from database attributes; they may have the same or different names from attributes • In the host language, use the shared variables without colon, ex if(stu. Credits>120)…

Insert in Embedded SQL • • Assign values to program shared variables Use SQL INSERT, listing shared variables (now preceded by colon) as values Ex: stu. Number ='S 1050'; stu. Last. Name = 'Lee'; stu. First. Name = 'Daphne'; stu. Major = 'English'; stu. Credits = 0; EXEC SQL INSERT INTO Student (stu. Id, last. Name, first. Name, major, credits) VALUES(: stu. Number, : stu. Last. Name, : stu. First. Name, : stu. Major, : stu. Credits);

Delete in Embedded SQL • Use program shared variables to identify target tuple(s) • Use SQL DELETE, identifying tuple(s) in WHERE line using shared variables (preceded by colon) Ex: stu. Number = 'S 1015'; EXEC SQL DELETE FROM Student WHERE stu. Id = : stu. Number;

Update in Embedded SQL • Use program shared variables to identify target tuple(s) • Use SQL UPDATE, identifying tuple(s) in WHERE line using shared variables preceded by colon Ex: stu. Major = 'History'; EXEC SQL UPDATE Student SET CREDITS = CREDITS + 3 WHERE major = : stu. Major;

Error Handling using WHENEVER • Can check each SQL transaction individually, or do error-handling for entire program using WHENEVER EXEC SQL WHENEVER [NOT FOUND/SQLERROR/SQLWARNING] [CONTINUE/DO/DO BREAK/GO TO. . . ];

Using Cursors • • Impedance mismatch: SQL SELECT can retrieve multiple rows, while host language usually requires one row at a time Declare a cursor: EXEC SQL DECLARE cursorname [INSENSITIVE] [SCROLL] CURSOR FOR query [FOR {READ ONLY | UPDATE OF attribute. Names}]; • • Query is regular SQL query, using attributes names (not the shared variables) Opening the cursor executes the SQL query EXEC SQL OPEN cursorname; • Set up a loop in host language Ex. WHILE (SQLSTATE = '00000') • To retrieve each row of the results, use FETCH EXEC SQL FETCH cursorname INTO hostvariables; • hostvariables are shared variables; preceded by colon • At end, close the cursor EXEC SQL CLOSE cursorname;

Embedded SQL Cursor Example EXEC SQL DECLARE CSCstu. Cursor CURSOR FOR SELECT stu. Id, last. Name, first. Name, major, credits FROM Student WHERE major='CSC'; EXEC SQL OPEN CSCstu. Cursor; WHILE (SQLSTATE = '00000') EXEC SQL FETCH CSCstu. Cursor INTO : stu. Number, : stu. Last. Name, : stu. First. Name, : stu. Major, : stu. Credits //Process each record retrieved END; //of WHILE EXEC SQL CLOSE CSCstu. Cursor;

Update and Delete Using a Cursor • Must declare cursor for update: FOR UPDATE OF variablename • Once cursor is open and active, current of cursor refers to the tuple it is positioned at • To update: EXEC SQL UPDATE tablename SET attributename = newvalue WHERE CURRENT OF cursorname; • To delete: EXEC SQL DELETE FROM tablename WHERE CURRENT OF cursorname;

Dynamic SQL • Can create a graphical front end that accepts queries dynamically • At run time, user prompted to enter an SQL command • Command stored as string • PREPARE statement used to parse and compile string and assign it to variable • EXECUTE command executes the code • Ex char user. String[ ]='UPDATE Student SET credits = 36 WHERE stu. Id= S 1050'; EXEC SQL PREPARE user. Command FROM : user. String; EXEC SQL EXECUTE user. Command;

Application Programming Interfaces (APIs) • DBMS can provide a library of functions available to host languages using an API • ODBC/JDBC- standardized connectivity using a common interface, allows common code to access different databases • Most vendors provide ODBC or JDBC drivers that conform to the standard • Other APIs – ADO. NET-for Microsoft. NET framework – Database Interface Module (DBI)-for Perl – PHP Data Objects (PDO)-for PHP • Requires four components: application, driver manager, driver, and data source (database)

ODBC/JDBC Components • Application, using the standard API – – – • Initiates the connection with the database Submits data requests as SQL statements to the DBMS Retrieves the results Performs processing Terminates the connection Driver manager – Loads and unloads drivers at the application’s request – Passes the ODBC or JDBC calls to the selected driver • Database driver – Links the application to the data source – Translates the ODBC or JDBC calls to DBMS-specific calls – Handles data translation needed because of any differences between the DBMS’s data language and the ODBC/JDBC standard – Controls error handling differences that arise between the data source and the standard. • Data source: database (or other source), DBMS and platform; provides the data

Figure 5. 15 ODBC/JDBC Architecture

JDBC • API-standard for relational database access from Java • Includes a set of Java classes and interfaces • Oracle and other vendors have extended functionality • Applications are platform independent • Can run on a variety of servers and DBMSs.

JDBC Application Steps • At the start of the application, import the Java classes import java. sql. *; • For Oracle database add import oracle. jdbc; • Load the JDBC drivers. For Oracle, write Class. for. Name(“oracle. jdbc. driver. Oracle. Driver”); • Connect to the database using the Driver. Manager class • Driver Manager Get. Connection method creates a connection object, conn, which is used for all communication conn = Driver. Manager. get. Connection("jdbc: oracle: oci 8: url”, ”your. Id”, ”yourpassword”); • Use SQL to interact with the database and Java for the logic in the application program • Close the connection object to disconnect from the database conn. close();

The Connection Object • Has 3 JDBC classes for communicating with database – Statement -for SQL statements with no parameters – Prepared. Statement – Callable. Statement -for executing stored procedures precompiled SQL statement – to be executed many times • Has 3 methods to create instances of these classes – create. Statement returns a new Statement object – prepare. Statement takes an SQL statement, precompiles it, and stores it in a Prepared. Statement object – prepare. Call for call to a stored procedure; has methods for handling input and output parameters and executing the procedure; returns a Callable. Statement • Statement object is used for executing SQL statements – Methods execute. Update, execute. Query, etc. – execute. Query method executes an SQL statement and returns a Result. Set object – Result. Set class has many useful methods; includes a cursor to present one row at a time – Within a row, columns can be retrieved using a get method

The Statement Object • Program connects to database and creates a Connection object, conn = Driver. Manager. get. Connection(“jdbc: oracle: oci 8”, ”Jones”, ”sesame”); • Uses conn to create a Statement object stmt = conn. create. Statement(); • Uses execute. Query method on stmt object (a SELECT); returns output of the query to Results. Set object, rset = stmt. execute. Query(“select stu. Id, last. Name, first. Name from Student”);

Figure 5. 16 Basic Elements of a Java Program Using JDBC with a Statement Object

The Statement Object and Result Set • Iterates through tuples using next method for Result. Set class while (rset. next()) • Retrieves individual column values of each tuple using get. String method for Result. Set with column numbers System. out. println(rset. get. String(1)+” “ + rset. get. String(2)+” “ +rset. get. String(3)); • Closes rset, stmt, and conn objects using close methods rset. close(); stmt. close(); conn. close(); • Can insert, delete, update using execute. Update method on Statement objects

The Prepared. Statement Object • Used for parameterized SQL queries • Pre-compiles query with some attributes defined for parameterized input • Program creates Connection object conn, Prepared. Statement object pstmt, and Results. Set object rset Connection conn = null; Prepared. Statement pstmt = null; Result. Set rset = null;

The Prepared. Statement Object • Creates sql query string with question mark (“? ”) for parameters String sql = "select f. name from faculty f, class c where c. schedule = ? “ + “and c. room = ? and c. fac. Id = f. fac. Id"; • Uses prepare. Statement method on conn to create and precompile pstmt = conn. prepare. Statement(sql); • Uses set. TYPE method to specify data type of parameterized attributes pstmt. set. String(1, sch); pstmt. set. String(2, room); • Executes query, creates rset, and processes it as usual rset = pstmt. execute. Query(); …

Figure 15. 17 Using a Prepared. Statement to Execute a Parameterized Query

The Callable Statement Object • Used to execute a stored procedure, which may have input and output parameters • Input parameters set with the set. TYPE method of Callable. Statement class • Output parameters defined with register. Out. Parameter method of Callable. Statement • Program creates Callable. Statement object cstmt Callable. Statement cstmt = null;

Figure 5. 17

The Callable. Statement Object and Parameters • Uses prepare. Call method of Connection. Object class with name of the procedure and question marks for input and output parameters. cstmt = conn. prepare. Call("call high. Grade(? , ? )"; • Uses set. String to specify type and provide name of input parameter cstmt. set. String(1, class. Num); • Uses register. Out. Parameter method to define output parameter cstmt. register. Out. Parameter(2, java. sql. Types. VARCHAR); • Uses execute. Query method for Callable. Statement to execute stored procedure cstmt. execute. Query();

Figure 5. 18 Using a Callable. Statement to Execute a Stored Procedure

System Catalog • Also called system data dictionary • Contains metadata • Automatically updated when new database objects created – stores schema • Oracle data dictionary provides three views: USER, ALL, and DBA. • View invoked by using the appropriate term as a prefix for the object(s) named in the FROM clause in a query – USER view provides a user with information about all the objects created by that user – ALL view provides information about objects user has permission to access in addition to the one the user has created. – DBA view provides information about all database objects; available to the database administrator

Using Oracle Catalog • Can write catalog queries directly in SQL*Plus • Can also use JDBC API for querying • Examples: DESCRIBE STUDENT; DESCRIBE USER_CONSTRAINTS; SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME FROM USER_CONSTRAINTS; SELECT TABLE_NAME FROM USER_TABLES; SELECT VIEW_NAME FROM USER_VIEWS; SELECT TRIGGER_NAME, TRIGGER_EVENT, TRIGGER_TYPE FROM USER_TRIGGERS; SELECT * FROM USER_TAB_COLUMNS; SELECT COLUMN_NAME, DATA_TYPE FROM USER_TAB_COLUMNS WHERE TABLE_NAME = ‘STUDENT’;