database technology jing shen. topics introductions sql statements view store procedure/functions...
TRANSCRIPT
![Page 1: Database Technology Jing Shen. Topics Introductions SQL Statements View Store Procedure/Functions Triggers Example of Object-Oriented Relational DB](https://reader036.vdocuments.us/reader036/viewer/2022070401/56649f1f5503460f94c37e7d/html5/thumbnails/1.jpg)
Database Technology
Jing Shen
![Page 2: Database Technology Jing Shen. Topics Introductions SQL Statements View Store Procedure/Functions Triggers Example of Object-Oriented Relational DB](https://reader036.vdocuments.us/reader036/viewer/2022070401/56649f1f5503460f94c37e7d/html5/thumbnails/2.jpg)
Topics
• Introductions
• SQL Statements
• View
• Store Procedure/Functions
• Triggers
• Example of Object-Oriented Relational DB
![Page 3: Database Technology Jing Shen. Topics Introductions SQL Statements View Store Procedure/Functions Triggers Example of Object-Oriented Relational DB](https://reader036.vdocuments.us/reader036/viewer/2022070401/56649f1f5503460f94c37e7d/html5/thumbnails/3.jpg)
SQL has several parts
• DDL – Data Definition Language
• DML – Data Manipulation Language
• Embedded SQL – Designed for use within a host language
• Authorization or DCL – Data Control Language
• Integrity
![Page 4: Database Technology Jing Shen. Topics Introductions SQL Statements View Store Procedure/Functions Triggers Example of Object-Oriented Relational DB](https://reader036.vdocuments.us/reader036/viewer/2022070401/56649f1f5503460f94c37e7d/html5/thumbnails/4.jpg)
Define Database and DBMS
• Database: A collection of related data.
• DBMS: A set of programs that enable users to create and maintain a database.
![Page 5: Database Technology Jing Shen. Topics Introductions SQL Statements View Store Procedure/Functions Triggers Example of Object-Oriented Relational DB](https://reader036.vdocuments.us/reader036/viewer/2022070401/56649f1f5503460f94c37e7d/html5/thumbnails/5.jpg)
Data Models
• It is a collection of concepts that can be used to describe the structure of a database.
1. Conceptual Data Model
2. Physical Data Model
3. Representational (or Implementation) Data Model
![Page 6: Database Technology Jing Shen. Topics Introductions SQL Statements View Store Procedure/Functions Triggers Example of Object-Oriented Relational DB](https://reader036.vdocuments.us/reader036/viewer/2022070401/56649f1f5503460f94c37e7d/html5/thumbnails/6.jpg)
Three types of Representational Data Model
• Relational Data Model
• Hierarchical Data Model
• Network Data Model
![Page 7: Database Technology Jing Shen. Topics Introductions SQL Statements View Store Procedure/Functions Triggers Example of Object-Oriented Relational DB](https://reader036.vdocuments.us/reader036/viewer/2022070401/56649f1f5503460f94c37e7d/html5/thumbnails/7.jpg)
DML (Data Manipulation Language)
• INSERT – insert into table_name[(column, [column]…)] {values ( expr[,expr]…) | subquery}
• DELETE – delete [From] table_name [WHERE condition]
• Update – update table_name set field = [, field = , …] [ WHERE condition ];
![Page 8: Database Technology Jing Shen. Topics Introductions SQL Statements View Store Procedure/Functions Triggers Example of Object-Oriented Relational DB](https://reader036.vdocuments.us/reader036/viewer/2022070401/56649f1f5503460f94c37e7d/html5/thumbnails/8.jpg)
Transaction
• A transaction is a sequence of SQL statements that Oracle treats as a single unit.
• COMMIT;
• ROLLBACK;
“autocommit” is set on
![Page 9: Database Technology Jing Shen. Topics Introductions SQL Statements View Store Procedure/Functions Triggers Example of Object-Oriented Relational DB](https://reader036.vdocuments.us/reader036/viewer/2022070401/56649f1f5503460f94c37e7d/html5/thumbnails/9.jpg)
SQL Join
• The INNER JOIN returns all rows from both tables where there is a match.
• Example: SELECT Employees.Name, Orders.Product FROM Employees INNER JOIN Orders ON Employees.ID = Orders.ID
![Page 10: Database Technology Jing Shen. Topics Introductions SQL Statements View Store Procedure/Functions Triggers Example of Object-Oriented Relational DB](https://reader036.vdocuments.us/reader036/viewer/2022070401/56649f1f5503460f94c37e7d/html5/thumbnails/10.jpg)
Joining a table to itself
• Return all the results where the attribute is in the same table.
Example: select s1.s#, s2.s# from s s1, s s2 where s1.city = s2.city and s1.s#<s2.s#;
Get all pairs of supplier numbers such that the two suppliers are located in the same city.
![Page 11: Database Technology Jing Shen. Topics Introductions SQL Statements View Store Procedure/Functions Triggers Example of Object-Oriented Relational DB](https://reader036.vdocuments.us/reader036/viewer/2022070401/56649f1f5503460f94c37e7d/html5/thumbnails/11.jpg)
Continues…
• The LEFT JOIN returns all the rows from the first table (Employees), even if there are no matches in the second table (Orders). Example: SELECT Employees.Name, Orders.Product FROM Employees LEFT JOIN Orders ON Employees.ID = Orders.ID
![Page 12: Database Technology Jing Shen. Topics Introductions SQL Statements View Store Procedure/Functions Triggers Example of Object-Oriented Relational DB](https://reader036.vdocuments.us/reader036/viewer/2022070401/56649f1f5503460f94c37e7d/html5/thumbnails/12.jpg)
Continues…
• The RIGHT JOIN returns all the rows from the second table (Orders), even if there are no matches in the first table (Employee). Example: SELECT Employees.Name, Orders.Product FROM Employees RIGHT JOIN Orders ON Employees.ID = Orders.ID
![Page 13: Database Technology Jing Shen. Topics Introductions SQL Statements View Store Procedure/Functions Triggers Example of Object-Oriented Relational DB](https://reader036.vdocuments.us/reader036/viewer/2022070401/56649f1f5503460f94c37e7d/html5/thumbnails/13.jpg)
View
• A view is logical table
1. Data does not physically exist (no storage)
2. Views are defined in terms of base tables “created by SELECT statement”
![Page 14: Database Technology Jing Shen. Topics Introductions SQL Statements View Store Procedure/Functions Triggers Example of Object-Oriented Relational DB](https://reader036.vdocuments.us/reader036/viewer/2022070401/56649f1f5503460f94c37e7d/html5/thumbnails/14.jpg)
Store Procedures
• It allows for sharing o PL/SQL code by different applications running at different places. Syntax:create [or replace] procedure <proc-name> [(<parameter-list>)] as <declarations> begin
<executable-section>end;
Function: create [or replace] function <func-name> [(<parameter-list>)] return <datatype> as
![Page 15: Database Technology Jing Shen. Topics Introductions SQL Statements View Store Procedure/Functions Triggers Example of Object-Oriented Relational DB](https://reader036.vdocuments.us/reader036/viewer/2022070401/56649f1f5503460f94c37e7d/html5/thumbnails/15.jpg)
Example of how user defined exceptions
Set serveroutput on;declare
incorrect_first_name exception;fname employee.first_name%type;lname employee.last_name%type;cursor a is select last_name, first_name from employee;begin
open a;loop
fetch a into lname, fname;dbms_output.put_line (fname);if (a%notfound) then exit;end if;if (fname = ‘ELINOR’) then
raise incorrect_first_name;end if;
end loop;exception
when incorrect_first_name then dbms-output.put_line (“Wrong spelling.”);end;
![Page 16: Database Technology Jing Shen. Topics Introductions SQL Statements View Store Procedure/Functions Triggers Example of Object-Oriented Relational DB](https://reader036.vdocuments.us/reader036/viewer/2022070401/56649f1f5503460f94c37e7d/html5/thumbnails/16.jpg)
Example of FunctionP4.sql:DECLARE enum customers.cno%type; total number(10,2); status boolean;
function total_emp_sales( emp_no IN employees.eno%type) return number is sales number; begin select sum(price*qty) into sales from orders,odetails,parts where orders.eno = emp_no and orders.ono = odetails.ono and odetails.pno = parts.pno; return (sales); end;
BEGIN enum := 1000; total := total_emp_sales(enum); DBMS_OUTPUT.PUT_LINE('Total sales for employee ' || enum || ' is ' || total);END;/
![Page 17: Database Technology Jing Shen. Topics Introductions SQL Statements View Store Procedure/Functions Triggers Example of Object-Oriented Relational DB](https://reader036.vdocuments.us/reader036/viewer/2022070401/56649f1f5503460f94c37e7d/html5/thumbnails/17.jpg)
Trigger
• It is a mechanism that automatically executes a specified PL/SQL block when a triggering event occurs on a table. The triggering event may be one of insert, delete, or update.
![Page 18: Database Technology Jing Shen. Topics Introductions SQL Statements View Store Procedure/Functions Triggers Example of Object-Oriented Relational DB](https://reader036.vdocuments.us/reader036/viewer/2022070401/56649f1f5503460f94c37e7d/html5/thumbnails/18.jpg)
Example of Triggers
CREATE OR REPLACE TRIGGER delete_orders
BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
DELETE FROM odetails WHERE ono = :old.ono;
END;
/
![Page 19: Database Technology Jing Shen. Topics Introductions SQL Statements View Store Procedure/Functions Triggers Example of Object-Oriented Relational DB](https://reader036.vdocuments.us/reader036/viewer/2022070401/56649f1f5503460f94c37e7d/html5/thumbnails/19.jpg)
Packages
• A package is a group of related PL/SQL objects (variables, constants, types, and cursors), procedures, and functions that is stored in the database.
• It consists of two parts: Package specification and the package body.
![Page 20: Database Technology Jing Shen. Topics Introductions SQL Statements View Store Procedure/Functions Triggers Example of Object-Oriented Relational DB](https://reader036.vdocuments.us/reader036/viewer/2022070401/56649f1f5503460f94c37e7d/html5/thumbnails/20.jpg)
Example of Package
create or replace package process_orders as procedure add_order_details (onum IN odetails.ono%type, pnum IN odetails.pno%type, quantity IN odetails.qty%type); procedure add_order (onum IN orders.ono%type, cnum IN orders.cno%type, enum IN orders.eno%type, receive IN date); procedure ship_order (onum IN orders.ono%type, ship IN date);end;/
![Page 21: Database Technology Jing Shen. Topics Introductions SQL Statements View Store Procedure/Functions Triggers Example of Object-Oriented Relational DB](https://reader036.vdocuments.us/reader036/viewer/2022070401/56649f1f5503460f94c37e7d/html5/thumbnails/21.jpg)
Example of Relational/Object-Oriented Database Structure
![Page 22: Database Technology Jing Shen. Topics Introductions SQL Statements View Store Procedure/Functions Triggers Example of Object-Oriented Relational DB](https://reader036.vdocuments.us/reader036/viewer/2022070401/56649f1f5503460f94c37e7d/html5/thumbnails/22.jpg)
References:
• http://www.w3schools.com/sql/sql_join.asp
• Oracle Book