590 likes | 612 Vues
Outline: SQL in Oracle Oracle database system architecture - Oracle server - Oracle client SQL*Plus PL/SQL. Oracle system architecture Oracle server and Oracle client. data management transaction control recovery security. Oracle server. Oracle client. Oracle client. Oracle client.
E N D
Outline: SQL in Oracle • Oracle database system architecture • - Oracle server • - Oracle client • SQL*Plus • PL/SQL Yangjun Chen ACS-3902
Oracle system architecture • Oracle server and Oracle client data management transaction control recovery security Oracle server Oracle client Oracle client Oracle client interface to manipulate data tools to support development of application Yangjun Chen ACS-3902
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) Yangjun Chen ACS-3902
- SQL*Plus as an interface • Create a table with integrity constraints • CREATE TABLE bank_account • (bank_acct_no VARCHAR2(40), • empid NUMBER(10), • BANK_ROUTE_NO VARCHAR2(40), • BANK_NAME VARCHAR2(50), • CONSTRAINT pk_bank_acct_01, • PRIMARY KEY (bank_acct_no), • CONSTRAINT fk_bank_acct_01 • FOREIGN KEY (empid) REFERENCE employee (empid)); Yangjun Chen ACS-3902
- SQL*Plus as an interface • Create a table with integrity constraints • CREATE TABLE bank_account • (bank_acct_no VARCHAR2(40), • empid NUMBER(10), • BANK_ROUTE_NO VARCHAR2(40), • BANK_NAME VARCHAR2(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); Yangjun Chen ACS-3902
CREATE TABLE employee • (empid NUMBER(10), • lastname VARCHAR2(25), • firstname VARCHAR2(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. Yangjun Chen ACS-3902
- SQL*Plus as an interface • NOT NULL constraints and check constraints • CREATE TABLE employee • (empid NUMBER(10), • lastname VARCHAR2(25), NOT NULL • firstname VARCHAR2(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)); Yangjun Chen ACS-3902
- SQL*Plus as an interface • Adding and modifying columns • ALTER TABLE products • ADD (color VARCHAR2(10)); • ALTER TABLE products • MODIFY (SERIAL# VARCHAR2(25)); • Assume that SERIAL# is an attribute in PRODUCTS with • type VARCHAR2(10). Yangjun Chen ACS-3902
- 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)); Yangjun Chen ACS-3902
- 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’, ‘XL’, ‘XXL’, ‘XXXL)); Yangjun Chen ACS-3902
- 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; Yangjun Chen ACS-3902
- 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; Yangjun Chen ACS-3902
- SQL*Plus as an interface • Dropping constraints • ALTER TABLE products • DROP CONSTRAINT uk_products_01; • ALTER TABLE products • DROP PRIMARY KEY CASCADE; Yangjun Chen ACS-3902
- SQL*Plus as an interface • Dropping Tables • ALTER TABLE products • ALTER TABLE products • DROP CONSTRAINT; • Truncating Tables • TRUNCATE TABLE products Yangjun Chen ACS-3902
- SQL*Plus as an interface • Changing Names of Objects • RENAME products TO objects • CREATE SYNONYM objects FOR products; • CREATE PUBLIC SYNONYM objects FOR products; Yangjun Chen ACS-3902
- 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 store 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. Yangjun Chen ACS-3902
- SQL*Plus as an interface • Creating sequences • CREATE SEQUENCE countdown_20 • START WITH 20 • INCREMENT BY 1 • NOMAXVALUE • CYCLE • ORDER; • CREATE SEQUENCE SOME_NUM • MINVALUE 0 • MAXVALUE 1000 • NOCYCLE 20 19 ... 1 20 19 ... 0 1 ... 1000 Yangjun Chen ACS-3902
- SQL*Plus as an interface • Using sequences • SELECT some_num.currval CURRENT • some_num.nextval NEXT • some_num.currval CURRENT • FROM dual • CURRENT NEXT CURRENT • 1 2 2 Yangjun Chen ACS-3902
- 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; Yangjun Chen ACS-3902
- 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; Yangjun Chen ACS-3902
- SQL*Plus as an interface • Removing sequence • DROP SEQUENCE some_num; Yangjun Chen ACS-3902
- 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 Yangjun Chen ACS-3902
- SQL*Plus as an interface • Creating complex views • CREATE VIEW employee_view • AS (SELECT e.empid empid, e.lastname lastname, e.firstname, firstname, e.salary 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; Yangjun Chen ACS-3902
- 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; Yangjun Chen ACS-3902
nonunique index indexed column contaons null value unique index • - SQL*Plus as an interface • Creating indexes manually • CREATE UNIQUE INDEX employee_lastname_index_01 • ON employee (lastname); • CREATE INDEX employee_lastname_index_01 • ON employee (lastname); Yangjun Chen ACS-3902
unique index on the combination of two columns: lastname, firstname • - SQL*Plus as an interface • Creating indexes manually • CREATE UNIQUE INDEX employee_last_first_index_01 • ON employee (lastname, firstname); Yangjun Chen ACS-3902
- SQL*Plus as an interface • Automatic indexes • Oracle will create a B-tree for an attrubute with ‘primary key’ • constraint or ‘unique’ constraint. Yangjun Chen ACS-3902
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. Yangjun Chen ACS-3902
- PL/SQL • A PL/SQL block normally contains three components: • variable declaration section, • executable section, and • exception section. Yangjun Chen ACS-3902
- 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 VARCHAR2, old_currency IN VARCHAR2) IS my_new_amt number(10) := 0; bad_data exception; BEGIN IF my_new_amt > 3 THEN ... ELSE ... END IF; Decralation section Executable section Yangjun Chen ACS-3902
- PL/SQL ... EXCEPTION WHEN bad_data THEN DBMS_OUTPUT.PUT_LINE(‘Error condition’); END; Exception handler Yangjun Chen ACS-3902
- 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; Yangjun Chen ACS-3902
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. Yangjun Chen ACS-3902
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. Yangjun Chen ACS-3902
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. Yangjun Chen ACS-3902
4. CHARACTER - Another name for the CHAR datatype. 5. VARCHAR - Another name for the VARCHAR2 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. Yangjun Chen ACS-3902
%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 ... Yangjun Chen ACS-3902
%ROWTYPE • Using the %ROWTYPE keyword, you can declare a variable to be of the type same as a table. • DECLARE • my_employee employee%ROWTYPE • BEGIN ... Yangjun Chen ACS-3902
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 ... Yangjun Chen ACS-3902
Constant declaration CREATE FUNCTION find_circle_area ( p_radius IN circle.radius%TYPE RETURN 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; Yangjun Chen ACS-3902
Using SQL Statements in PL/SQL DECLARE my_employee 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; Yangjun Chen ACS-3902
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; Yangjun Chen ACS-3902
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. Yangjun Chen ACS-3902
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; Yangjun Chen ACS-3902
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; Yangjun Chen ACS-3902
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; Yangjun Chen ACS-3902
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 • %found • %rowcount • %isopen Yangjun Chen ACS-3902
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; Yangjun Chen ACS-3902
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 VARCHAR2, high_end in VARCHAR2) IS • SELECT empid, salary • FROM employee; • WHERE UPPER(substr(lastname, 1, 1) BETWEEN UPPER(low_end) • AND UPPER(high_end) Yangjun Chen ACS-3902
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; Yangjun Chen ACS-3902