chapter 5 structured query language (sql)
DESCRIPTION
Chapter 5 Structured Query Language (SQL). Database Systems: Design, Implementation, and Management Peter Rob & Carlos Coronel. In this chapter, you will learn:. The basic commands and functions of SQL How SQL is used for data manipulation (to add, modify, delete, and retrieve data) - PowerPoint PPT PresentationTRANSCRIPT
Chapter 5 Structured Query Language (SQL)
Database Systems: Design, Implementation, and Management
Peter Rob & Carlos Coronel
In this chapter, you will learn:In this chapter, you will learn:
The basic commands and functions of SQL How SQL is used for data manipulation (to add,
modify, delete, and retrieve data) How to use SQL to query a database to extract
useful information How SQL is used for data administration (to
create tables, indexes, and views) About more advanced SQL features such as
updatable views, stored procedures, and triggers
Introduction to SQLIntroduction to SQL
Ideal database language Create database and table structures Perform basic data management chores (add, delete,
and modify) Perform complex queries to transform data into useful
information
Introduction to SQLIntroduction to SQL SQL (Structured Query Language)
meets ideal database language requirements:
SQL coverage fits into two categories: Data definition
Data manipulation
SQL is relatively easy to learn.
ANSI prescribes a standard SQL.
SQL2 : SQL-92
SQL3 : SQL-98/99 support object-oriented data management
Data Definition CommandsData Definition Commands The Database Model
Simple Database -- PRODUCT and VENDOR tables Each product is supplied by only a single vendor. A vendor may supply many products.
Data Definition CommandsData Definition Commands The Tables and Their Components
The VENDOR table contains vendors who are not referenced in the PRODUCT table. PRODUCT is optional to VENDOR.
Some vendors have never supplied a product
( 0,N )
Data Definition CommandsData Definition Commands The Tables and Their Components
Existing V_CODE values in the PRODUCT table must have a match in the VENDOR table.
A few products are supplied factory-direct, a few are made in-house, and a few may have been bought in a special warehouse sale. That is, a product is not necessarily supplied by a vendor. VENDOR is optional to PRODUCT.
( 0,1 )
PRODUCT
VENDER
CH5_TEXT
Data Definition CommandsData Definition Commands
Create database structure Holds all tables and is a collection of physical files stored
on disk DBMS automatically creates tables to store metadata Database administrator creates structure or schema
Logical group of tables or logical database Groups tables by owner Enforces security
Data Definition CommandsData Definition Commands Creating the Database Structure
CREATE SCHEMA AUTHORIZATION <creator>;
Example:CREATE SCHEMA AUTHORIZATION JONES;
Schema : logical database structurea group of database objects- such as tables and indexes – that are related to each other.
CREATE DATABASE <database name>;
Example:CREATE DATABASE CH5;
A Data Dictionary for the CH5 Database
Table 5.1
Some Common SQL Data Types
Numeric NUMBER(L,D)INTEGERSMALLINTDECIMAL(L,D)
Character CHAR(L)VARCHAR(L)
Date DATE
Data Type Format
Data Definition CommandsData Definition Commands
Creating Table Structures
CREATE TABLE <table name>(<attribute1 name and attribute1 characteristics, attribute2 name and attribute2 characteristics, attribute3 name and attribute3 characteristics, primary key designation, foreign key designation and foreign key requirements>);
Data Definition CommandsData Definition Commands
CREATE TABLE VENDOR(V_CODE INTEGER NOT NULL UNIQUE, V_NAME VARCHAR(35) NOT NULL, V_CONTACT VARCHAR(15) NOT NULL, V_AREACODE CHAR(3) NOT NULL, V_PHONE CHAR(3) NOT NULL, V_STATE CHAR(2) NOT NULL, V_ORDER CHAR(1) NOT NULL, PRIMARY KEY (V_CODE));
CREATE TABLE PRODUCT( P_CODE VARCHAR(10) NOT NULL UNIQUE, P_DESCRIPT VARCHAR(35) NOT NULL, P_INDATE DATE NOT NULL, P_ONHAND SMALLINT NOT NULL, P_MIN SMALLINT NOT NULL, P_PRICE DECIMAL(8,2) NOT NULL, P_DISCOUNT DECIMAL(4,1) NOT NULL, V_CODE SMALLINT,
PRIMARY KEY (P_CODE), FOREIGN KEYFOREIGN KEY (V_CODE) REFERENCES REFERENCES VENDORVENDOR ON DELETE RESTRICT
ON UPDATE CASCADE);
ON DELETE RESTRICTcannot delete a vender as long as there is a product that references that vender
ON UPDATE CASCADEupdate V_CODE in VENDER → update V_CODE in PRODUCT
Data Definition CommandsData Definition Commands Adherence to entity integrity and
referential integrity rules is crucial
SQL Integrity Constraints Entity Integrity
PRIMARY KEY NOT NULL and UNIQUE
Referential Integrity FOREIGN KEY ON DELETE ON UPDATE
SQL Command Coverage
Table 5.3
Data Entry and SavingData Entry and Saving
Enters data into a table
Saves changes to disk
INSERT INTO <table name> VALUES (attribute 1 value, attribute 2 value, … etc.);
COMMIT <table names> ;
Basic Data ManagementBasic Data Management Data Entry
INSERT INTO <table name> VALUES (attribute 1 value, attribute 2 value, … etc.);
INSERT INTO VENDORVALUES(‘21225, ’Bryson, Inc.’, ’Smithson’, ’615’,’223-3234’, ’TN’, ’Y’);
INSERT INTO PRODUCTVALUES(‘11 QER/31’, ’Power painter, 15 psi., 3-nozzle’, ’07/02/1999’, 8.5, 109.99, 0.00, 25595);
Figure 5.3 A form-based Data View and Entry Screen
Basic Data ManagementBasic Data Management Saving the Table Contents
COMMIT <table names>;
COMMIT PRODUCT;
Any changes made to the table contentsare not physically saved on disk until
COMMIT close the database log out of SQL
Listing Table Contents and Other Commands
Listing Table Contents and Other Commands
Allows table contents to be listed
UPDATE command makes data entry corrections ROLLBACK command restores database back to
previous condition if COMMIT hasn’t been used DELETE command removes table row
SELECT <attribute names> FROM <table names>;
Basic Data ManagementBasic Data Management Listing the Table Contents
SELECT * FROM PRODUCT;
SELECT P_CODE, P_DESCRIPT, P_INDATE, P_ONHAND, P_MIN, P-PRICE, P_DISCOUNT, V_CODE FROM PRODUCT;
Figure 5.4 The Contents of the PRODUCT Table
Basic Data ManagementBasic Data Management Making a Correction
UPDATE PRODUCTSET P_INDATE = ‘12/11/96’WHERE P_CODE = ‘13-Q2/P2’;
UPDATE PRODUCTSET P_INDATE = ‘12/11/96’, P_PRICE = 15.99,
P_MIN = 10WHERE P_CODE = ‘13-Q2/P2’;
Basic Data ManagementBasic Data Management Restoring the Table Contents
ROLLBACK
If COMMIT not yet Does not require to specify the table name.
SQL assumes that the database currently in memoryis the one to be restored.
Update integrity in transaction management (Ch.9) COMMIT ROLLBACK
Basic Data ManagementBasic Data Management Deleting Table Rows
DELETE FROM PRODUCTWHERE P_CODE = ‘2238/QPD’;
DELETE FROM PRODUCTWHERE P_MIN = 5;
QueriesQueries Creating partial listings of table contents
SELECT <column(s)>FROM <table name>WHERE <conditions>;
Table 5.4 Mathematical Operators
QueriesQueries
Partial Listing of Table ContentsSELECT <column(s)>
FROM <table name>WHERE <conditions>;
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODEFROM PRODUCTWHERE V_CODE = 21344;
Figure 5.5
Figure 5.6
The Microsoft Access QBE and Its SQL
QBE (Query By Example)query generator
ExamplesExamples Mathematical operators
Mathematical operators on character attributes
Mathematical operators on dates
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODEFROM PRODUCTWHERE V_CODE <> 21344;
SELECT P_CODE,P_DESCRIPT,P_ONHAND,P_MIN,P_PRICEFROM PRODUCTWHERE P_CODE < ‘1558-QWI’;
SELECT P_DESCRIPT,P_ONHAND,P_MIN,P_PRICE,PINDATEFROM PRODUCTWHERE P_INDATE >= ‘01/20/2002’;
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODEFROM PRODUCTWHERE V_CODE <> 21344;
Figure 5.7
QueriesQueries
SELECT P_DESCRIPT, P_ONHAND, P_MIN, P_PRICEFROM PRODUCTWHERE P_PRICE <= 10;
Figure 5.8
QueriesQueries
Using Mathematical Operators on Character Attributes
SELECT P_DESCRIPT, P_ONHAND, P_MIN, P_PRICEFROM PRODUCTWHERE P_CODE < ‘1558-QWI’;
Figure 5.9
QueriesQueries
Using Mathematical Operators on Dates
SELECT P_DESCRIPT, P_ONHAND, P_MIN, P_PRICE, P_INDATE
FROM PRODUCTWHERE P_INDATE >= ‘08/15/1999’;
Figure 5.10
QueriesQueries
Computed Columns Computed Columns New columns can be created through valid
computations or formulas Formulas may contain mathematical operators May contain attributes of any tables specified in FROM
clause
Alias is alternate name given to table or column in SQL statement
SELECT P_DESCRIPT,P_ONHAND,P_PRICE, P_ONHAND*P_PRICE AS TOTVALUE FROM PRODUCT;
P_DESCRIPT P_ONHAND P_PRICE TOTVALUE
OperatorsOperators
Logical: AND, OR, NOT
Rules of precedence Conditions within parenthesis executed first Boolean algebra
Special BETWEEN - defines limits IS NULL - checks for nulls LIKE - checks for similar string IN - checks for value in a set EXISTS - opposite of IS NULL
SELECT *FROM PRODUCTWHERE V_CODE = 21344 OR V_CODE = 24288;
Logical Operators: AND, OR, and NOT
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODEFROM PRODUCTWHERE V_CODE=21344 OR V_CODE=24288;
Figure 5.13
QueriesQueries
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODEFROM PRODUCTWHERE P_PRICE<50 AND P_INDATE>‘07/15/1999’;
Figure 5.14
QueriesQueries
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODEFROM PRODUCTWHERE (P_PRICE<50 AND P_INDATE>07/15/1999’) OR V_CODE=24288;
Figure 5.15
QueriesQueries
QueriesQueries Special Operators
BETWEEN - used to define range limits.
IS NULL - used to check whether an attribute value is null
LIKE - used to check for similar character strings.
IN - used to check whether an attribute value matches a value contained within a (sub)set of listed values.
EXISTS - used to check whether an attribute has a value. - the opposite of IS NULL.
Special Operators
BETWEEN is used to define range limits.
SELECT * FROM PRODUCTWHERE P_PRICE BETWEEN 50.00 AND 100.00;
SELECT *FROM PRODUCTWHERE P_PRICE>50.00 AND P_PRICE<100.00;
QueriesQueries
Special OperatorsIS NULL is used to check whether an
attribute value is null.
SELECT P_CODE, P_DESCRIPT FROM PRODUCTWHERE P_MIN IS NULL;
SELECT P_CODE, P_DESCRIPT FROM PRODUCTWHERE P_INDATE IS NULL;
QueriesQueries
Special OperatorsLIKE is used to check for similar character strings.
SELECT * FROM VENDORWHERE V_CONTACT LIKE ‘Smith%’;
SELECT * FROM VENDORWHERE V_CONTACT LIKE ‘SMITH%’;
% : cn , c=any character, n 0≧
_ : c1
QueriesQueries
Special OperatorsIN is used to check whether an attribute value
matches a value contained within a (sub)set of listed values.
SELECT * FROM PRODUCTWHERE V_CODE IN (21344, 24288);
QueriesQueries
QueriesQueries EXISTS is used to check whether an
attribute has value.
DELETE FROM PRODUCTWHERE P_CODE EXISTS;
SELECT * FROM PRODUCTWHERE V_CODE EXISTS;
Advanced Data Management Commands
Advanced Data Management Commands
Changing Table Structures
ALTER TABLE <table name>MODIFY (<column name> <new column characteristics>);
ALTER TABLE <table name>ADD (<column name> <new column characteristics>);
Changing a Column’s Data Type
ALTER TABLE PRODUCTMODIFY (V_CODE CHAR(5));
Changing Attribute Characteristics
ALTER TABLE PRODUCTMODIFY (P_PRICE DECIMAL(9,2));
Adding a New Column to the Table
ALTER TABLE PRODUCTADD (P_SALECODE CHAR(1));
Advanced Data Management Commands
Advanced Data Management Commands
UPDATE PRODUCTSET P_SALECODE = ‘2’WHERE P_CODE = ‘1546-QQ2’;
Advanced Data Management Commands
Advanced Data Management Commands
UPDATE PRODUCTSET P_SALECODE = ‘1’WHERE P_CODE IN (‘2232/QWE’, ‘2232/QTY’);
Advanced Data Management Commands
Advanced Data Management Commands
UPDATE PRODUCTSET P_SALECODE = ‘2’WHERE P_INDATE < ‘07/10/1999’;
UPDATE PRODUCTSET P_SALECODE = ‘1’WHERE P_INDATE >= ‘08/15/1999’ AND P_INDATE < ‘08/20/1999’;
Advanced Data Management Commands
Advanced Data Management Commands
Selected PRODUCT Table Attributes: Multiple Update Effect
Advanced Data Management Commands
Advanced Data Management Commands
The Arithmetic Operators
Λ
Copying Parts of Tables Need not be identical:
Column names Number of columns
Column characteristics must match
CREATE TABLE PARTPART_CODE CHAR(8) NOT NULL UNIQUE,PART_DESCRIPT CHAR(35),PART_PRICE DECIMAL(8,2),PRIMARY KEY(PART_CODE));
INSERT INTO PART (PART_CODE,PART_DESCRIPT,PART_PRICE)SELECT P_CODE, P_DESCRIPT, P_PRICEFROM PRODUCT;
Advanced Data Management Commands
Advanced Data Management Commands
The Part Attributes Copied from the PRODUCT Table
Deleting a Table from the Database DROP TABLE <table name>;
DROP TABLE PART;
Advanced Data Management Commands
Advanced Data Management Commands
Primary and Foreign Key Designation For reestablish the integrity rules
Forgot to define Imported tables from a different database
ALTER TABLE PRODUCTADD PRIMARY KEY (P_CODE);
ALTER TABLE PRODUCTADD FOREIGN KEY (V_CODE) REFERENCES VENDOR;
ALTER TABLE PRODUCT ADD PRIMARY KEY (P_CODE) ADD FOREIGN KEY (V_CODE) REFERENCES VENDOR;
Advanced Data Management Commands
Advanced Data Management Commands
More Complex Queries and SQL Functions
More Complex Queries and SQL Functions
Ordering a Listing
ORDER BY <attributes>
SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICEFROM PRODUCTORDER BY P_PRICE;
Selected PRODUCT Table Attributes Ordered by(Ascending) P_PRICE
The Partial Listing of the EMPLOYEE Table
Cascading order sequenceSELECT EMP_LNAME,EMP_FNAME,EMP_INITIAL,EMP_AREACODE,EMP_PHONE
FROM EMPLOYEEORDER BY EMP_LNAME, EMP_FNAME, EMP_INITIAL;
Descending orderSELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICEFROM PRODUCTWHERE P_INDATE<‘08/20/1999’ AND P_PRICE<=50.00ORDER BY V_CODE, P_PRICE DESCDESC;
Listing Unique Values
SELECT DISTINCTDISTINCT V_CODEFROM PRODUCT;
More Complex Queries and SQL Functions
More Complex Queries and SQL Functions
FUNCTION OUTPUT
COUNT The number of rows containing the specified attribute.
MIN The minimum attribute value encountered.
MAX The maximum attribute value encountered.
AVG The arithmetic mean (average) for the specified attribute.
SUM The sum of all values for a selected attribute.
Some Basic SQL Aggregate Functions
Querying a Query: Nested Process
COUNT
SELECT COUNT(DISTINCTDISTINCT V_CODE)FROM PRODUCT;
6
COUNT Function Output Examples
MAX and MIN Function Output Examples
MAX
SELECT P_CODE, P_DESCRIPT, P_PRICEFROM PRODUCTWHERE P_PRICE = (SELECT MAX(P_PRICE) FROM PRODUCT);
SUM
SELECT SUM(P_ONHAND*P_PRICE)FROM PRODUCT;
AVG
SELECT P_DESCRIPT, P_ONHAND, P_PRICE, V_CODEFROM PRODUCTWHERE P_PRICE > (SELECT AVG(P_PRICE) FROM PRODUCT)ORDER BY P_PRICE DESC;
More Complex Queries and SQL Functions
More Complex Queries and SQL Functions
AVG Function Output Examples
• Determine whether goods that have been in inventory for a certain length of time should be placed on special sale.
Grouping Data
GROUP BY
SELECT P_SALECODE, MIN(P_PRICE)FROM PRODUCT_2GROUP BY P_SALECODE;
GROUP BY clauseGROUP BY clause The GROUP BY clause is valid only
when used in conjunction with one of the SQL arithmetic functions:COUNT, MIN, MAX, AVG, SUM
SELECT V_CODE, COUNT(DISTINCT(P_CODE))
FROM PRODUCT_2GROUP BY V_CODE;
Otherwise, you will generate a “not a GROUP BY expression” error.
SELECT V_CODE, P_CODE, P_DESCRIPT, P_PRICE
FROM PRODUCT_2GROUP BY V_CODE;
Improper Use of the GROUP BY Clause
ERRORERROR
GROUP BY’s HAVING clauseGROUP BY’s HAVING clause WHERE ( SELECT )
Applies to columns and expressions for individual rows HAVING ( GROUP BY )
Applies to the output of a GROUP BY command
SELECT V_CODE,COUNT(DISTINCT(P_CODE)),AVG(P_PRICE) FROM PRODUCT_2GROUP BY V_CODE;
SELECT V_CODE,COUNT(DISTINCT(P_CODE)),AVG(P_PRICE) FROM PRODUCT_2GROUP BY V_CODE HAVING AVG(P_PRICE)<10;
An Application of the HAVING Clause
Virtual Tables: Creating a View Logical table exists only in memory Can be treated as though it were a real table
CREATE VIEW PRODUCT_3 AS
SELECT P_DESCROPT, P_ONHAND, P_PRICE
FROM PRODUCTWHERE P_PRICE > 50.0;
More Complex Queries and SQL Functions
More Complex Queries and SQL Functions
Creating a ViewCreating a View
SQL Indexes Improve the efficiency of data search Created to meet particular search criteria
CREATE INDEX P_CODEX ON PRODUCT(P_CODE);
When the index field is a primary key whose values must not be duplicated
CREATE UNIQUE INDEX P_CODEXON PRODUCT(P_CODE);
More Complex Queries and SQL Functions
More Complex Queries and SQL Functions
Joining Database Tables
SELECT PRODUCT.P_DESCRIPT,PRODUCT.P_PRICE, VENDOR.V_NAME, VENDOR.V_CONTACT, VENDOR.V_AREACODE, VENDOR.V_PHONEFROM PRODUCT, VENDORWHERE PRODUCT.V_CODE=VENDOR.V_CODE;
More Complex Queries and SQL Functions
More Complex Queries and SQL Functions
The Results of a JOIN
SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONEFROM PRODUCT, VENDORWHERE PRODUCT.V_CODE=VENDOR.V_CODE AND P_INDATE > ‘08/15/1999’;
More Complex Queries and SQL Functions
More Complex Queries and SQL Functions
Procedural languageProcedural language
A term used in contrast to declarative language to describe a language where the programmer specifies an explicit sequences of steps to follow to produce a result.
Common procedural languages include Basic, Pascal, C.
Declarative languages describe relationships between variables in terms of functions or inference rules and the language executor (interpreter or compiler) applies some fixed algorithm to these relations to produce a result.
The most common examples of declarative languages are logic programming languages such as Prolog and functional languages like Haskell.
Procedural SQLProcedural SQL
Shortcomings of SQL SQL doesn’t support execution of a stored set of procedures
based on some logical condition.IF-THEN-ELSE
SQL fails to support the looping operations.DO-WHILE
Solutions Embedded SQL
SQL statements can be inserted within the procedural programming language
Shared Code Critical code is isolated and shared by all application programs. This approach allows better maintenance.
Procedural SQL(PL/SQL)
Procedural SQLProcedural SQL Procedural SQL
Procedural SQL allows the use of procedural code and SQL statements that are stored within the database.
The procedural code is executed by the DBMS when it is invoked by the end user.
End users can use PL/SQL to create: Triggers Stored procedures PL/SQL functions
Procedural SQLProcedural SQL Triggers
A trigger is procedural SQL code that is automatically invoked by the RDBMS upon the occurrence of a data manipulation event.
A trigger is always invoked before or after a data row is selected, inserted, or updated.
A trigger is always associated with a database table.
Each database table may have one or more triggers.
A trigger is executed as part of the transaction that triggered it.
Procedural SQLProcedural SQL Role of triggers
Triggers can be used to enforce constraints that cannot be enforced at the design and implementation levels.
Triggers add functionality by automating critical actions and providing appropriate warnings and suggestions for remedial action.
Triggers can be used to update table values, insert records in tables, and call other stored procedures.
The Revised PRODUCT Table
If P_ONHAND(庫存 ) <= P_MIN(安全存量 )set P_REORDER = “Yes”
The PRODUCT List Output in the Oracle RDBMS
in Oracle :P_REORDER = 1/0 for Yes/No
Procedural SQLProcedural SQL Syntax to create a trigger in ORACLE
CREATE OR REPLACE TRIGGER <trigger_name>[BEFORE/AFTER][DELETE/INSERT/UPDATE OF <column_name>] ON <table_name>[FOR EACH ROW]BEGIN
PL/SQL instructions;……………
END;
Procedural SQLProcedural SQL Creation of the Oracle Trigger for the PRODUCT
Table
CREATE OR REPLACE TRIGGER TRG_PRODUCT_REORDERAFTER INSERT OR UPDATE OF P_ONHAND ON PRODUCTBEGIN
UPDATE PRODUCT
SET P_REORDER = 1
WHERE P_ONHAND <= P_MIN;END;
Creation of the Oracle Trigger for the PRODUCT Table
The PRODUCT Table’s P_REORDER Field isUpdated by the Trigger
UPDATE PRODUCT
SET P_ONHAND = 4 WHERE P_CODE = ’11QER/31’;
The P_REORDER Value Mismatch
UPDATE PRODUCT
SET P_MIN = 7 WHERE P_CODE = ’2232/QWE’;
The Second Version of the PRODUCT_REORDER Trigger
CREATE OR REPLACE TRIGGER TRG_PRODUCT_REORDER
AFTER INSERT OR UPDATE OF P_ONHAND, P_MIN
ON PRODUCTBEGIN
UPDATE PRODUCT
SET P_REORDER = 1
WHILE P_ONHAND <= P_MIN;END;
The Second Version of the PRODUCT_REORDER Trigger
UPDATE PRODUCT
SET P_MIN = 10 WHERE P_CODE = ’23114-AA’;
The P_REORDER Flag Has Not Been Properly Set AfterIncreasing the P_ONHAND Value
UPDATE PRODUCT
SET P_ONHAND = P_ONHAND + P_MIN WHERE P_CODE = ’11QER/31’;
4 25
Never reset it to 0!
The Third Version of the PRODUCT_REORDER Trigger
CREATE OR REPLACE TRIGGER TRG_PRODUCT_REORDERBEFORE INSERT OR UPDATE OF P_ONHAND, P_MIN
ON PRODUCTBEGIN
IF :NEW.P_ONHAND <= NEW.P_MIN THEN
:NEW.P_REORDER = 1;
ELSE
:NEW.P_REORDER = 0;
END IF;END;
The Third Version of the Product Reorder Trigger
After creating the new trigger, we can execute an UPDATE statement to fire it.
UPDATE PRODUCT
SET P_ONHAND = P_ONHAND
Execution of the Third Trigger Version
Procedural SQLProcedural SQL Stored Procedures
A stored procedure is a named collection of procedural and SQL statements.
Stored procedures are stored in the database and invoked by name.
Stored procedures are executed as a unit.
Procedural SQLProcedural SQL Syntax to create a stored procedure
CREATE OR REPLACE PROCEDURE procedure_name (argument IN/OUT data-type, etc) IS/AS BEGIN
DECLARE variable name and data typePL/SQL or SQL statements;
END;
Syntax to invoke a stored procedure
EXEC store_procedure_name(parameter, parameter, …)
Procedural SQLProcedural SQL Stored Procedures
DECLARE is used to specify the variables used within the procedure.
Argument specifies the parameters that are passed to the stored procedure.
IN / OUT indicates whether the parameter is for INPUT or OUTPUT or both.
Data-type is one of the procedural SQL data types used in the RDBMS.
Creating and Invoking A Simple Stored Procedure
The PROD_SALE Stored Procedure
CREATE OR REPLACE PROCEDURE PROD-SALE (CODE IN VARCHAR2, QTYSOLD IN NUMBER)
AS BEGINUPDATE PRODUCT
SET P_ONHAND = P_ONHAND - QTYSOLD
WHERE P_CODE = CODE;
END;
Creation of the PROD_SALE Stored Procedure
Executing the PROD_SALE Stored Procedure
Procedural SQLProcedural SQL PL/SQL Stored Functions
A stored function is a named group of procedural and SQL statements that returns a value.
Syntax to create a function:
CREATE FUNCTION function_name (argument IN data-type, etc)RETURN data-typeAS BEGIN
PL/SQL statements;RETURN (value); ……
END;