SQL in Oracle Outline SQL in Oracle Oracle

SQL in Oracle Outline: SQL in Oracle • Oracle database system architecture - Oracle server - Oracle client • SQL*Plus • PL/SQL Jan. 2008 Yangjun Chen ACS-3902 1

SQL in Oracle system architecture • Oracle server and Oracle client Oracle server Oracle client data management transaction control recovery security Oracle client interface to manipulate data tools to support development of application Jan. 2008 Yangjun Chen ACS-3902 2

SQL in Oracle SQL*Plus • Interface to manipulate Oracle databases • Tool to support the development of application - SQL*Plus as an interface To start SQL*Plus, enter Oracle username and password: $> sqlplus jason/athena (from a command line operating system such UNIX) or click: Start Program Oracle (for Windows - SQL*Plus) Jan. 2008 Yangjun Chen ACS-3902 3

SQL in Oracle - SQL*Plus as an interface Create a table with integrity constraints CREATE TABLE bank_account (bank_acct_no VARCHAR 2(40), empid NUMBER(10), BANK_ROUTE_NO VARCHAR 2(40), BANK_NAME VARCHAR 2(50), CONSTRAINT pk_bank_acct_01, PRIMARY KEY (bank_acct_no), CONSTRAINT fk_bank_acct_01 FOREIGN KEY (empid) REFERENCE employee (empid)); Jan. 2008 Yangjun Chen ACS-3902 4

SQL in Oracle - SQL*Plus as an interface Create a table with integrity constraints CREATE TABLE bank_account (bank_acct_no VARCHAR 2(40), empid NUMBER(10), BANK_ROUTE_NO VARCHAR 2(40), BANK_NAME VARCHAR 2(50), CONSTRAINT pk_bank_acct_01, PRIMARY KEY (bank_acct_no), CONSTRAINT fk_bank_acct_01 FOREIGN KEY (empid) REFERENCE employee (empid) ON DELETE CASCADE); Jan. 2008 Yangjun Chen ACS-3902 5

SQL in Oracle CREATE TABLE employee (empid NUMBER(10), lastname VARCHAR 2(25), firstname VARCHAR 2(25), salary NUMBER(10, 4), home_phone NUMBER(15), CONSTRAINT pk_employee_01 PRIMARY KEY (empid), CONSTRAINT uk_employee_01 UNIQUE (home_phone)); The difference between PRIMARY KEY and UNIQUE is that for a UNIQUE attribute NULL value is allowed. Jan. 2008 Yangjun Chen ACS-3902 6

SQL in Oracle - SQL*Plus as an interface NOT NULL constraints and check constraints CREATE TABLE employee (empid NUMBER(10), lastname VARCHAR 2(25), NOT NULL firstname VARCHAR 2(25), NOT NULL salary NUMBER(10, 4), CHECK(salary < 50000 home_phone NUMBER(15), CONSTRAINT pk_employee_01 PRIMARY KEY (empid), CONSTRAINT uk_employee_01 UNIQUE (home_phone)); Jan. 2008 Yangjun Chen ACS-3902 7

SQL in Oracle - SQL*Plus as an interface Adding and modifying columns ALTER TABLE products ADD (color VARCHAR 2(10)); ALTER TABLE products MODIFY (SERIAL# VARCHAR 2(25)); Assume that SERIAL# is an attribute in PRODUCTS with type VARCHAR 2(10). Jan. 2008 Yangjun Chen ACS-3902 8

SQL in Oracle - SQL*Plus as an interface Modifying integrity constraints ALTER TABLE products MODIFY (color NOT NULL); ALTER TABLE products ADD (CONSTRAINT pk_products_01 PRIMARY KEY (product#)); ALTER TABLE products ADD (CONSTRAINT fk_products_01 FOREIGN KEY REFERENCES (AVAIL_COLOR. color)); Jan. 2008 Yangjun Chen ACS-3902 9

SQL in Oracle - SQL*Plus as an interface Modifying integrity constraints ALTER TABLE products ADD (UNIQUE (serial#)); ALTER TABLE products ADD (size CHECK (size in ‘P’, “S’, ‘M’, ‘L’, ‘XXL’, ‘XXXL)); Jan. 2008 Yangjun Chen ACS-3902 10

SQL in Oracle - SQL*Plus as an interface Enabling or disabling constraints ALTER TABLE products ENABLE CONSTRAINT pk_products_01; ALTER TABLE products ENABLE CONSTRAINT uk_products_03; Jan. 2008 Yangjun Chen ACS-3902 11

SQL in Oracle - SQL*Plus as an interface Enabling or disabling constraints ALTER TABLE products DISABLE PRIMARY KEY; ALTER TABLE products DISABLE UNIQUE (serial#); ALTER TABLE products DISABLE PRIMARY KEY CASCADE; Jan. 2008 Yangjun Chen ACS-3902 12

SQL in Oracle - SQL*Plus as an interface Dropping constraints ALTER TABLE products DROP CONSTRAINT uk_products_01; ALTER TABLE products DROP PRIMARY KEY CASCADE; Jan. 2008 Yangjun Chen ACS-3902 13

SQL in Oracle - SQL*Plus as an interface Dropping Tables ALTER TABLE products DROP CONSTRAINT; Truncating Tables TRUNCATE TABLE products Jan. 2008 Yangjun Chen ACS-3902 14

SQL in Oracle - SQL*Plus as an interface Changing Names of Objects RENAME products TO objects CREATE SYNONYM objects FOR products; CREATE PUBLIC SYNONYM objects FOR products; Jan. 2008 Yangjun Chen ACS-3902 15

SQL in Oracle - SQL*Plus as an interface Sequences A sequence is a special database object that generates integers according to specified rules at the time the sequence was created. - In some cases, the primary key is not important to use for accessing data to stored in a table. Example: A doctor’s office may have a client tracking system that assigns each new patient a unique integer ID to identify their records. - Using a sequence to generate primary keys automatically. Jan. 2008 Yangjun Chen ACS-3902 16

SQL in Oracle - SQL*Plus as an interface Creating sequences Yangjun Chen ACS-3902 0 1. . . Jan. 2008 1 20 19. . . CREATE SEQUENCE SOME_NUM MINVALUE 0 MAXVALUE 1000 NOCYCLE 20 19. . . CREATE SEQUENCE countdown_20 START WITH 20 INCREMENT BY 1 NOMAXVALUE CYCLE ORDER; 1000 17

SQL in Oracle - SQL*Plus as an interface Using sequences SELECTsome_num. currval CURRENT some_num. nextval NEXT some_num. currval CURRENT FROM dual CURRENT NEXT CURRENT 1 Jan. 2008 2 2 Yangjun Chen ACS-3902 18

SQL in Oracle - SQL*Plus as an interface Using sequences INSERT INTO expense(expense_no, empid, amt, submit_date) VALUE(some_num. nextval, 59495, 456. 34, ‘ 21 -nov-99’); UPDATE product SET product_num = some_num. currval WHERE serial_num = 3498583945; Jan. 2008 Yangjun Chen ACS-3902 19

SQL in Oracle - SQL*Plus as an interface Modifying a sequence definition ALTER SEQUENCE countdown_20 INCREMENT BY 4; ALTER SEQUENCE countdown_20 NOCYCLE; ALTER SEQUENCE some_num MAXVALUE 10000; Jan. 2008 Yangjun Chen ACS-3902 20

SQL in Oracle - SQL*Plus as an interface Removing sequence DROP SEQUENCE some_num; Jan. 2008 Yangjun Chen ACS-3902 21

SQL in Oracle - SQL*Plus as an interface Views Creating simple views CREATE VIEW employee_view AS (SELECT empid, lastname, firstname, salary FROM employee WHERE empid = 59495) UPDATE employee_view SET salary = 99000 WHERE empid = 59495 Jan. 2008 Yangjun Chen ACS-3902 22

SQL in Oracle - SQL*Plus as an interface Creating complex views CREATE VIEW employee_view AS (SELECT e. empid, e. lastname, e. firstname, e. salary, a. address, a. city, a. state, a. zipcode FROM employee e, employee_address a WHERE e. empid = a. empid) CREATE VIEW employee_view AS (SELECT empid, lastname, firstname, salary FROM employee WHERE empid = 59495) WITH CHECK OPTION; Jan. 2008 Yangjun Chen ACS-3902 23

SQL in Oracle - SQL*Plus as an interface Modifying views CREATE OR REPLACE VIEW employee_view AS (SELECT empid, lastname, firstname, salary FROM employee WHERE empid = user) WITH CHECK OPTION; Removing views DROP VIEW employee_view; Jan. 2008 Yangjun Chen ACS-3902 24

SQL in Oracle - SQL*Plus as an interface Creating indexes manually CREATE UNIQUE INDEX employee_lastname_index_01 ON employee (lastname); unique index CREATE INDEX employee_lastname_index_01 ON employee (lastname); nonunique indexed column contaons null value Jan. 2008 Yangjun Chen ACS-3902 25

SQL in Oracle - SQL*Plus as an interface Creating indexes manually CREATE UNIQUE INDEX employee_last_first_index_01 ON employee (lastname, firstname); unique index on the combination of two columns: lastname, firstname Jan. 2008 Yangjun Chen ACS-3902 26

SQL in Oracle - SQL*Plus as an interface Automatic indexes Oracle will create a B-tree for an attrubute with ‘primary key’ constraint or ‘unique’ constraint. Jan. 2008 Yangjun Chen ACS-3902 27

SQL in Oracle PL/SQL • PL/SQL is a special language available for developers to code stored procedures that seamlessly integrate with database objects access via the language of database objects, SQL. • PL/SQL procedure: a series of statements accepting and/or returning zero or more variables. • PL/SQL function: a series of statements accepting zero or more variables and returning one value. A PL/SQL procedure or a PL/SQL function is called a PL/SQL block. Jan. 2008 Yangjun Chen ACS-3902 28

SQL in Oracle - PL/SQL A PL/SQL block normally contains three components: • variable declaration section, • executable section, and • exception section. Jan. 2008 Yangjun Chen ACS-3902 29

SQL in Oracle - PL/SQL There are two kinds of blocks in Oracle: named and unnamed or anonymous blocks. Named block CREATE FUNCTION convert_money ( AMOUNT IN NUMBER, convert_currency IN VARCHAR 2, old_currency IN VARCHAR 2) IS my_new_amt number(10) : = 0; Decralation section bad_data exception; BEGIN IF my_new_amt > 3 THEN. . . ELSE. . . Executable section END IF; Jan. 2008 Yangjun Chen ACS-3902 30

SQL in Oracle - PL/SQL. . . EXCEPTION WHEN bad_data THEN DBMS_OUTPUT. PUT_LINE(‘Error condition’); END; Exception handler Jan. 2008 Yangjun Chen ACS-3902 31

SQL in Oracle - PL/SQL Unnamed block DECLARE my_new_amt number(10) : = 0; bad_data exception; BEGIN IF my_new_amt > 3 THEN. . . ELSE. . . END IF; EXCEPTION WHEN bad_data THEN DBMS_OUTPUT. PUT_LINE(‘Error condition’); END; Jan. 2008 Yangjun Chen ACS-3902 32

SQL in Oracle • Datatypes used in PL/SQL There are two kinds of datatypes: database datatypes and nondatabase types. Database datatypes There are several datatypes that can be used in PL/SQL that correspond to the datatypes used on the database, i. e. , the datatypes used for defining a table. 1. NUMBER(size[, precision]) - used to any number. NUMBER(10), NUMBER(10, 5) 2. CHAR(size), VARCHAR(size) - used to store alphanumeric text strings. The CHAR datatype pads the value stored to the full length of the variable with blanks. Jan. 2008 Yangjun Chen ACS-3902 33

SQL in Oracle 3. DATE - Used to store dates. 4. LONG - Stores large blocks of text, up to 2 gigabytes in length. 5. LONG RAW - Stores large blocks of data stored in binary format. RAW - Stores smaller blocks of data stored in binary format. 6. BLOB, CLOB, NCLOB BFILE - Large object datatype. Jan. 2008 Yangjun Chen ACS-3902 34

SQL in Oracle Nondatabase datatypes 1. DEC, DECIMAL, REAL, DOUBLE_PRECISION - These numeric datatypes are a subset of the NUMBER datatype that is used for variable declaration in PL/SQL. 2. INTEGER, INT, SMALLINT, NATURAL, POSITIVE, NUMERIC - These numeric datatypes are a subset of the NUMBER datatype that is used for variable declaration in PL/SQL. 3. BINARY_INTEGER, PLS_INTEGER - These datatypes store integers. A variable in either format cannot be stored in the database without conversion first. Jan. 2008 Yangjun Chen ACS-3902 35

SQL in Oracle 4. CHARACTER - Another name for the CHAR datatype. 5. VARCHAR - Another name for the VARCHAR 2 datatype. 6. BOOLEAN - Stores a TRUE/FALSE value. 7. TABLE/RECORD - Tables can be used to store the equivalent of an array, while records store variables with composite datatypes. Jan. 2008 Yangjun Chen ACS-3902 36

SQL in Oracle %TYPE • Using the %TYPE keyword, you can declare a variable to be of the type same as an attribute. DECLARE my_employee_id employee. empid%TYPE BEGIN. . . DECLARE my_salary employee. salary%TYPE : = 0; my_lastname employee. lastname%TYPE : = ‘SMITH’; BEGIN. . . Jan. 2008 Yangjun Chen ACS-3902 37

SQL in Oracle %ROWTYPE • Using the %ROWTYPE keyword, you can declare a variable to be of the type same as a table. DECLARE my_employee%ROWTYPE BEGIN. . . Jan. 2008 Yangjun Chen ACS-3902 38

SQL in Oracle DECLARE TYPE t_employee IS RECORD ( my_empid employee. empid%TYPE, my_lastname employee. lastname%TYPE, my_firstname employee. firstname%TYPE, my_salary employee. firstname%TYPE); my_employee t_employee; BEGIN. . . Jan. 2008 Yangjun Chen ACS-3902 39

SQL in Oracle Constant declaration CREATE FUNCTION find_circle_area ( p_radius RETURN IN circle. radius%TYPE NUMBER IS my_area number(10) : = 0; pi constant number(15, 14) : = 3. 14159265358; BEGIN my_area : = (p_radius*p_radius)*pi; Return (my_area); END; Jan. 2008 Yangjun Chen ACS-3902 40

SQL in Oracle Using SQL Statements in PL/SQL DECLARE my_employee%ROWTYPE; my_lastname VARCHAR(30) : = ‘SAMSON’; my_firstname VARCHAR(30) : = ‘DELILAN’; my_salary NUMBER(10) : = 49500; BEGIN SELECT * INTO my_employee FROM employee WHERE empid = 49594; UPDATE employee SET salary = my_employee. salary + 10000 WHERE empid = my_employee. empid; Jan. 2008 Yangjun Chen ACS-3902 41

SQL in Oracle INSERT INTO employee (empid, lastname, firstname, salary) VALUE (emp_sequence. nextval, my_lastname, my_firstname, my_salary); my_employee. empid : = 59495; DELETE FROM employee WHERE empid = my_empid; END; Jan. 2008 Yangjun Chen ACS-3902 42

SQL in Oracle CURSOR concept • A cursor is an address in memory where a SQL statement is processed. • There are two kinds of cursors: explicit and implicit An explicit cursor is named address (via a variable) An implicit cursor is unnamed address. Jan. 2008 Yangjun Chen ACS-3902 43

SQL in Oracle CURSOR concept • Explicit cursor - an named address where an SQL statement is processed. DECLARE high_pctinc constant number(10, 5) : = 1. 20; med_pctinc constant number(10, 5) : = 1. 10; low_pctinc constant number(10, 5) : = 1. 05; my_salary employee. salary%TYPE; my_empid employee. empid%TYPE; CURSOR employee_crsr IS SELECT empid, salary FROM employee; Jan. 2008 Yangjun Chen ACS-3902 44

SQL in Oracle BEGIN OPEN employee_crsr; LOOP FETCH employee_crsr INTO my_empid, my_salary; EXIT WHEN employee_crsr%NOTFOUND; IF my_empid = 59697 OR my_empid = 76095 THEN UPDATE employee SET salary = my_salary*high_pctinc WHERE empid = my_empid; Jan. 2008 Yangjun Chen ACS-3902 45

SQL in Oracle ELSEIF my_empid = 39294 OR my_empid = 94329 THEN UPDATE employee SET salary = my_salary*low_pctinc WHERE empid = my_empid; ELSE UPDATE employee SET salary = my_salary *mid_pctinc WHERE empid = my_empid; END IF; END LOOP; END; Jan. 2008 Yangjun Chen ACS-3902 46

SQL in Oracle CURSOR concept • Implicit cursor - an unnamed address where an SQL statement is processed. Therefore, there is no declaration for an implicit cursor variable. Whenever an SQL is evaluated, an implicit cursor is automatically associated with it. Such an implicit cursor can be manipulated using the cursor attributes: %notfound %rowcount %isopen Jan. 2008 Yangjun Chen ACS-3902 47

SQL in Oracle • Implicit cursor DECLARE my_empid employee. empid%TYPE : = 59694; my_salary employee. salary%TYPE : = 99000; my_lastname employee. lastname%TYPE : = ‘RIDDINGS’; BEGIN UPDATE employee SET salary = my_salary WHERE = my_empid; IF sql%NOTFOUND THEN INSERT INTO EMPLOYEE (empid, lastname, salary) VALUE(my_empid, my_lastname, my_salary); END IF; END; Jan. 2008 Yangjun Chen ACS-3902 48

SQL in Oracle • Parameters and explicit cursors DECLARE high_pctinc constant number(10, 5) : = 1. 20; med_pctinc constant number(10, 5) : = 1. 10; low_pctinc constant number(10, 5) : = 1. 05; my_salary employee. salary%TYPE; my_empid employee. empid%TYPE; CURSOR employee_crsr (low_end in VARCHAR 2, high_end in VARCHAR 2) IS SELECT FROM empid, salary employee; WHERE UPPER(substr(lastname, 1, 1) BETWEEN UPPER(low_end) AND UPPER(high_end) Jan. 2008 Yangjun Chen ACS-3902 49

SQL in Oracle BEGIN OPEN employee_crsr(‘A’, ‘M’); LOOP FETCH employee_crsr INTO my_empid, my_salary; EXIT WHEN employee_crsr%NOTFOUND; IF my_empid = 59697 OR my_empid = 76095 THEN UPDATE employee SET salary = my_salary*high_pctinc WHERE empid = my_empid; ELSEIF my_empid = 39294 OR my_empid = 94329 THEN UPDATE employee SET salary = my_salary*low_pctinc WHERE empid = my_empid; ELSE UPDATE employee SET salary = my_salary *mid_pctinc WHERE empid = my_empid; END IF; END LOOP; END; Jan. 2008 Yangjun Chen ACS-3902 50

SQL in Oracle - Error handling • In Oracle PL/SQL, there are three types of exceptions: predefined exceptions, user-defined exceptions, and internal exceptions. Predefined exceptions Oracle has designed several ‘built-in’ exceptions used to handle common situations that may occur on the database. Example: statement returns no data. When a statement expecting one piece of data receives more than one piece of data. Jan. 2008 Yangjun Chen ACS-3902 51

SQL in Oracle - Error handling Predefined exceptions Some of the predefined cursors are represented using the following key words: invalid_cursor - Occurs when an attempt is made to close a nonopend cursor_already_open - Occurs when an attempt is made to open a nonclosed cursor. dup_val_on_index - Unique or primary-key constraint violation. No_data_found - No rows were selected or changed by the SQL operation. Jan. 2008 Yangjun Chen ACS-3902 52

SQL in Oracle too_many_rows - More than one row was obtained by a single-row subsequently, or in another SQL statement operation where Oracle was expecting one row. zero_divide - An attempt was made to divide by zero. rowtype_mismatch - The datatypes of the record to which data from the cursor is assigned are incompatible. Invalid_number - An alphanumeric string was referenced as a number. EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT. PUT_LINE(‘No data is found’); END Jan. 2008 Yangjun Chen ACS-3902 53

SQL in Oracle - Error handling User-defined exceptions In addition to predefined exceptions, there can be created a whole host of user-defined exceptions that handle situations that may arise in the code. To handle exceptions, a PL/SQL code should contain: exception declaration, exception testing, and exception handling. Jan. 2008 Yangjun Chen ACS-3902 54

SQL in Oracle - Error handling User-defined exceptions DECLARE my_empid employee. empid%TYPE : = 59694; my_emp_record employee%ROWTYPE : = 99000; my_salary_null EXCEPTION; BEGIN SELECT * FROM employee INTO my_emp_record WHERE empid = my_empid; IF my_emp_record. salary IS NULL THEN RAISE my_salary_null; END IF; Jan. 2008 Yangjun Chen ACS-3902 55

SQL in Oracle EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT. PUT_LINE(‘No data is found’); WHEN my_salary_null THEN DBMS_OUTPUT. PUT_LINE(‘Salary column was null for employees’); END; Jan. 2008 Yangjun Chen ACS-3902 56

SQL in Oracle - Error handling Internal exceptions • All the Oracle errors are represented by alphanumeric string of the form: ORA-xxxx. Example: ORA-1400. • A predefined exception is an association of a name with an Oracle alphanumeric string representing an error. • One can extend the list of predefined exceptions by associating a new name with an Oracle alphanumeric string representing an error, using pragam exception_init keywords. Jan. 2008 Yangjun Chen ACS-3902 57

SQL in Oracle DECLARE my_emp_record employee%ROWTYPE; salary_null exception; pragam exception_init (salary_null, -1400); BEGIN my_emp_record. empid : = 59485; my_emp_record. lastname : = ‘RICHARD’; my_emp_record. firstname : = ‘JEAN-MARIE’; my_emp_record. salary : = 65000; INSERT INTO employee(empid, lastname, firstname, salary) VALUE(my_emp_record. empid, my_emp_record. lastname, my_emp_record. firstname, my_emp_record. salary); Jan. 2008 Yangjun Chen ACS-3902 58

SQL in Oracle EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT. PUT_LINE(‘No data is found’); WHEN salary_null THEN DBMS_OUTPUT. PUT_LINE(‘Salary column was null for employees’); END; Jan. 2008 Yangjun Chen ACS-3902 59