1 / 59

Outline: SQL in Oracle Oracle database system architecture - Oracle server - Oracle client

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.

nmilligan
Download Presentation

Outline: SQL in Oracle Oracle database system architecture - Oracle server - Oracle client

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

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

  2. 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

  3. 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

  4. - 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

  5. - 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

  6. 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

  7. - 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

  8. - 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

  9. - 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

  10. - 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

  11. - 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

  12. - 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

  13. - 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

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

  15. - 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

  16. - 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

  17. - 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

  18. - 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

  19. - 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

  20. - 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

  21. - SQL*Plus as an interface • Removing sequence • DROP SEQUENCE some_num; Yangjun Chen ACS-3902

  22. - 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

  23. - 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

  24. - 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

  25. 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

  26. 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

  27. - 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

  28. 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

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

  30. - 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

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

  32. - 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

  33. 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

  34. 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

  35. 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

  36. 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

  37. %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

  38. %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

  39. 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

  40. 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

  41. 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

  42. 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

  43. 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

  44. 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

  45. 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

  46. 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

  47. 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

  48. 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

  49. 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

  50. 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

More Related