views and sequence for comupter science.ppt

this is for btech student.

1. Experiment 11,12Experiment 11,12
VIEWS, SYNONYMS, AND SEQUENCESVIEWS, SYNONYMS, AND SEQUENCES

2. VIEWSVIEWS
•A database view is a A database view is a logical logical oror virtual table virtual table
based on a query.based on a query.
•It is useful to think of a It is useful to think of a viewview as a stored query. as a stored query.
•Views are created through use of a CREATE Views are created through use of a CREATE
VIEW command that incorporates use of the VIEW command that incorporates use of the
SELECT statement. SELECT statement.
•Views are queried just like tables. Views are queried just like tables.

3. VIEWSVIEWS
CREATE VIEW employee_parking CREATE VIEW employee_parking
(parking_space, last_name, (parking_space, last_name,
first_name, ssn) ASfirst_name, ssn) AS
SELECT emp_parking_space, SELECT emp_parking_space,
emp_last_name, emp_first_name, emp_last_name, emp_first_name,
emp_ssn emp_ssn
FROM employeeFROM employee
ORDER BY emp_parking_space;ORDER BY emp_parking_space;
View Created.View Created.

4. VIEWSVIEWS
SELECT *SELECT *
FROM employee_parking;FROM employee_parking;
  
PARKING_SPACE LAST_NAME FIRST_NAME SSNPARKING_SPACE LAST_NAME FIRST_NAME SSN
------------- ---------- ----------- --------------------- ---------- ----------- --------
1 Bordoloi Bijoy 9996666661 Bordoloi Bijoy 999666666
3 Joyner Suzanne 9995555553 Joyner Suzanne 999555555
32 Zhu Waiman 99944444432 Zhu Waiman 999444444
more rows are displayed…more rows are displayed…
  
•Notice that the only columns in the query are those Notice that the only columns in the query are those
defined as part of the view. defined as part of the view.

5. VIEWSVIEWS
•Additionally, we have renamed the columns in the Additionally, we have renamed the columns in the
view so that they are slightly different than the view so that they are slightly different than the
column names in the underlying employee table.column names in the underlying employee table.
•Further, the rows are sorted by Further, the rows are sorted by parking_spaceparking_space
column even though there is no ORDER BY in the column even though there is no ORDER BY in the
SELECT command used to access the view. SELECT command used to access the view.

6. CREATING A VIEWCREATING A VIEW
•CREATE VIEW SyntaxCREATE VIEW Syntax
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW <view name> CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW <view name>
[(column alias name….)] AS <query> [WITH [CHECK OPTION] [READ [(column alias name….)] AS <query> [WITH [CHECK OPTION] [READ
ONLY] [CONSTRAINT]];ONLY] [CONSTRAINT]];
•The OR REPLACE option is used to create a view that The OR REPLACE option is used to create a view that
already exists. This option is useful for modifying an already exists. This option is useful for modifying an
existing view without having to drop or grant the privileges existing view without having to drop or grant the privileges
that system users have acquired with respect to the view .that system users have acquired with respect to the view .
•If you attempt to create a view that already exists without If you attempt to create a view that already exists without
using the OR REPLACE option, Oracle will return the using the OR REPLACE option, Oracle will return the
ORA-00955: ORA-00955: name is already used by an existing objectname is already used by an existing object
error message and the CREATE VIEW command will fail. error message and the CREATE VIEW command will fail.

7. CREATING A VIEWCREATING A VIEW
•The FORCE option allows a view to be created even if a base The FORCE option allows a view to be created even if a base
table that the view references does not already exist.table that the view references does not already exist.
•This option is used to create a view prior to the actual creation This option is used to create a view prior to the actual creation
of the base tables and accompanying data. Before such a of the base tables and accompanying data. Before such a
view can be queried, the base tables must be created and data view can be queried, the base tables must be created and data
must be loaded into the tables. This option can also be used if must be loaded into the tables. This option can also be used if
a system user does not currently have the privilege to create a a system user does not currently have the privilege to create a
view. view.
•The NOFORCE option is the opposite of FORCE and allows The NOFORCE option is the opposite of FORCE and allows
a system user to create a view if they have the required a system user to create a view if they have the required
permissions to create a view, and if the tables from which the permissions to create a view, and if the tables from which the
view is created already exist. This is the default option.view is created already exist. This is the default option.

8. CREATING A VIEWCREATING A VIEW
•The WITH READ ONLY option allows creation of a view The WITH READ ONLY option allows creation of a view
that is read-only. You cannot use the DELETE, INSERT, that is read-only. You cannot use the DELETE, INSERT,
or UPDATE commands to modify data for the view. or UPDATE commands to modify data for the view.
•The WITH CHECK OPTION clause allows rows that can The WITH CHECK OPTION clause allows rows that can
be selected through the view to be updated. It also enables be selected through the view to be updated. It also enables
the specification of constraints on values.the specification of constraints on values.
•The CONSTRAINT clause is used in conjunction with the The CONSTRAINT clause is used in conjunction with the
WITH CHECK OPTION clause to enable a database WITH CHECK OPTION clause to enable a database
administrator to assign a unique name to the CHECK administrator to assign a unique name to the CHECK
OPTION. If the DBA omits the CONSTRAINT clause, OPTION. If the DBA omits the CONSTRAINT clause,
Oracle will automatically assign the constraint a system-Oracle will automatically assign the constraint a system-
generated name that will not be very meaningful.generated name that will not be very meaningful.

9. ExampleExample
CREATE VIEW empview7 AS CREATE VIEW empview7 AS
SELECT emp_ssn, emp_first_name, emp_last_nameSELECT emp_ssn, emp_first_name, emp_last_name
FROM employeeFROM employee
WHERE emp_dpt_number=7;WHERE emp_dpt_number=7;
View created.View created.
•A simple query of the A simple query of the empview7empview7 shows the following data. shows the following data.
SELECT *SELECT *
FROM empview7;FROM empview7;
EMP_SSN EMP_FIRST_NAME EMP_LAST_NAMEEMP_SSN EMP_FIRST_NAME EMP_LAST_NAME
--------- ------------------------- ---------------------------------- ------------------------- -------------------------
999444444 Waiman Zhu999444444 Waiman Zhu
999111111 Douglas Bock999111111 Douglas Bock
999333333 Dinesh Joshi999333333 Dinesh Joshi
999888888 Sherri Prescott999888888 Sherri Prescott

10. ExampleExample
•It is also possible to create a view that has exactly the It is also possible to create a view that has exactly the
same structure as an existing database table. same structure as an existing database table.
•The view named The view named dept_viewdept_view shown next has exactly shown next has exactly
the same structure as the same structure as departmentdepartment table. table.
CREATE VIEW dept_view AS CREATE VIEW dept_view AS
SELECT * SELECT *
FROM department;FROM department;
View created.View created.

11. ExampleExample
•We can recreate the view by using the OR REPLACE clause We can recreate the view by using the OR REPLACE clause
to create a view that is to create a view that is read-onlyread-only by specifying a WITH by specifying a WITH
READ ONLY clause. READ ONLY clause.
•The new version of The new version of dept_viewdept_view will restrict data manipulation will restrict data manipulation
language operations on the view to the use of the SELECT language operations on the view to the use of the SELECT
command.command.

CREATE OR REPLACE VIEW dept_view AS CREATE OR REPLACE VIEW dept_view AS
SELECT * SELECT *
FROM department WITH READ ONLY CONSTRAINT FROM department WITH READ ONLY CONSTRAINT
vw_dept_view_read_only;vw_dept_view_read_only;
View created.View created.

12. FUNCTIONS AND VIEWS – A JOIN VIEWFUNCTIONS AND VIEWS – A JOIN VIEW
•In addition to specifying columns from existing In addition to specifying columns from existing
tables, you can use single row functions consisting of tables, you can use single row functions consisting of
number, character, date, and group functions as well number, character, date, and group functions as well
as expressions to create additional columns in views. as expressions to create additional columns in views.
• This can be extremely useful because the system This can be extremely useful because the system
user will have access to data without having to user will have access to data without having to
understand how to use the underlying functions. understand how to use the underlying functions.

13. ExampleExample
CREATE OR REPLACE VIEW dept_salary CREATE OR REPLACE VIEW dept_salary
(name, min_salary, max_salary, avg_salary) AS (name, min_salary, max_salary, avg_salary) AS
SELECT d.dpt_name, MIN(e.emp_salary), SELECT d.dpt_name, MIN(e.emp_salary),
MAX(e.emp_salary), AVG(e.emp_salary)MAX(e.emp_salary), AVG(e.emp_salary)
FROM employee e, department dFROM employee e, department d
WHERE e.emp_dpt_number=d.dpt_noWHERE e.emp_dpt_number=d.dpt_no
GROUP BY d.dpt_name;GROUP BY d.dpt_name;
View created.View created.
SELECT * SELECT *
FROM dept_salary;FROM dept_salary;
NAME MIN_SALARY MAX_SALARY AVG_SALARYNAME MIN_SALARY MAX_SALARY AVG_SALARY
------------------------- ------------------ ------------------ ------------------------------------------- ------------------ ------------------ ------------------
Admin and Records 25000 Admin and Records 25000 43000 43000 3100031000
Headquarters Headquarters 55000 55000 55000 55000 5500055000
Production Production 25000 25000 43000 43000 3400034000

14. VIEW STABILITYVIEW STABILITY
•A view does not actually store any data. The A view does not actually store any data. The
data needed to support queries of a view are data needed to support queries of a view are
retrieved from the underlying database tables retrieved from the underlying database tables
and displayed to a result table whenever a and displayed to a result table whenever a
view is queried. The result table is only stored view is queried. The result table is only stored
temporarily. temporarily.
•If a table that underlies a view is dropped, If a table that underlies a view is dropped,
then the view is no longer valid. Attempting then the view is no longer valid. Attempting
to query an invalid view will produce an to query an invalid view will produce an
ORA-04063: view "VIEW_NAME" has errors ORA-04063: view "VIEW_NAME" has errors
error message. error message.

15. INSERTING , UPDATING, AND DELETINGINSERTING , UPDATING, AND DELETING
TABLE ROWS THROUGH VIEWSTABLE ROWS THROUGH VIEWS
•You can insert a row if the view in use is one You can insert a row if the view in use is one
that is updateable (not read-only). that is updateable (not read-only).
•A view is updateable if the INSERT command A view is updateable if the INSERT command
does not violate any constraints on the does not violate any constraints on the
underlying tables.underlying tables.
•This rule concerning constraint violations also This rule concerning constraint violations also
applies to UPDATE and DELETE commands. applies to UPDATE and DELETE commands.

16. ExampleExample
CREATE OR REPLACE VIEW dept_view ASCREATE OR REPLACE VIEW dept_view AS
SELECT dpt_no, dpt_name SELECT dpt_no, dpt_name
FROM department;FROM department;
INSERT INTO dept_view VALUES (18, 'Department 18');INSERT INTO dept_view VALUES (18, 'Department 18');
INSERT INTO dept_view VALUES (19, 'Department 20');INSERT INTO dept_view VALUES (19, 'Department 20');
SELECT *SELECT *
FROM dept_view;FROM dept_view;
DPT_NO DPT_NAMEDPT_NO DPT_NAME
------------ -------------------------------- --------------------
7 Production7 Production
3 Admin and Records3 Admin and Records
1 Headquarters1 Headquarters
18 Department 1818 Department 18
19 Department 2019 Department 20

17. ExampleExample
UPDATE dept_view SET dpt_name = 'Department 19‘UPDATE dept_view SET dpt_name = 'Department 19‘
WHERE dpt_no = 19;WHERE dpt_no = 19;
1 row updated.1 row updated.
SELECT *SELECT *
FROM departmentFROM department
WHERE dpt_no >= 5;WHERE dpt_no >= 5;
DPT_NO DPT_NAME DPT_MGRSS DPT_MGR_SDPT_NO DPT_NAME DPT_MGRSS DPT_MGR_S
----------- -------------------- ------------------ ---------------------------- -------------------- ------------------ -----------------
7 Production 999444444 22-MAY-987 Production 999444444 22-MAY-98
18 Department 1818 Department 18
19 Department 1919 Department 19
more rows are displayed…more rows are displayed…

18. More ExamplesMore Examples
DELETE dept_view DELETE dept_view
WHERE dpt_no = 18 OR dpt_no = 19;WHERE dpt_no = 18 OR dpt_no = 19;
2 rows deleted.2 rows deleted.
SELECT *SELECT *
FROM department;FROM department;
DPT_NO DPT_NAME DPT_MGRSS DPT_MGR_SDPT_NO DPT_NAME DPT_MGRSS DPT_MGR_S
------------ ------------------------ ------------------ ---------------------------- ------------------------ ------------------ ----------------
7 Production 999444444 22-MAY-987 Production 999444444 22-MAY-98
3 Admin and Records 999555555 01-JAN-013 Admin and Records 999555555 01-JAN-01
1 Headquarters 999666666 19-JUN-811 Headquarters 999666666 19-JUN-81

19. CREATING A VIEW WITH ERRORSCREATING A VIEW WITH ERRORS
•If there are no syntax errors in a CREATE VIEW If there are no syntax errors in a CREATE VIEW
statement, Oracle will create a view even if the statement, Oracle will create a view even if the
view-defining query refers to a non-existent table view-defining query refers to a non-existent table
or an invalid column of an existing table.or an invalid column of an existing table.
•The view will also be created even if the system The view will also be created even if the system
user does not have privileges to access the tables user does not have privileges to access the tables
which a view references.which a view references.
•The new view will be unusable and is categorized The new view will be unusable and is categorized
as “created with errors.” as “created with errors.”
•In order to create such a view, the system user In order to create such a view, the system user
must use the FORCE option of the CREATE must use the FORCE option of the CREATE
VIEW command.VIEW command.

20. CREATING A VIEW WITH ERRORSCREATING A VIEW WITH ERRORS
•In the CREATE VIEW command shown below, In the CREATE VIEW command shown below,
the table named the table named divisionsdivisions does not exist and the does not exist and the
view is created with errors. Oracle returns an view is created with errors. Oracle returns an
appropriate warning message. appropriate warning message.
CREATE FORCE VIEW div_view AS CREATE FORCE VIEW div_view AS
SELECT * SELECT *
FROM divisions;FROM divisions;
Warning: View created with Warning: View created with
compilation errors.compilation errors.
•If we now create a table named divisions, a query If we now create a table named divisions, a query
of the invalid div_view view will execute, and the of the invalid div_view view will execute, and the
view is automatically recompiled and becomes view is automatically recompiled and becomes
valid.valid.

21. DROPPING VIEWDROPPING VIEW
•A DBA or view owner can drop a view with the A DBA or view owner can drop a view with the
DROP VIEW command. The following DROP VIEW command. The following
command drops a view named command drops a view named dept_viewdept_view..
DROP VIEW dept_view;DROP VIEW dept_view;
View dropped.View dropped.

22. A Summary of VIEW Facts A Summary of VIEW Facts
•A view does not store data, but a view does A view does not store data, but a view does
display data through a SELECT query as if the display data through a SELECT query as if the
data were stored in the view.data were stored in the view.
•A view definition as provided by the CREATE A view definition as provided by the CREATE
VIEW statement is stored in the database. VIEW statement is stored in the database.
Further, Oracle develops what is termed an Further, Oracle develops what is termed an
"execution plan" that is used to "gather up" the "execution plan" that is used to "gather up" the
data that needs to be displayed by a view. This data that needs to be displayed by a view. This
execution plan is also stored in the database.execution plan is also stored in the database.
•A view can simplify data presentation as well as A view can simplify data presentation as well as
provide a kind of data security by limiting provide a kind of data security by limiting
access to data based on a "need to know." access to data based on a "need to know."

23. A Summary of VIEW Facts A Summary of VIEW Facts
•A view can display data from more than one table. A view can display data from more than one table.
•Views can be used to update the underlying tables. Views can be used to update the underlying tables.
Views can also be limited to read-only access. Views can also be limited to read-only access.
•Views can change the appearance of data. For Views can change the appearance of data. For
example, a view can be used to rename columns example, a view can be used to rename columns
from tables without affecting the base table.from tables without affecting the base table.
•A view that has columns from more than one table A view that has columns from more than one table
cannot be modified by an INSERT, DELETE, or cannot be modified by an INSERT, DELETE, or
UPDATE command if a grouping function, UPDATE command if a grouping function,
GROUP BY clause is part of the view definition.GROUP BY clause is part of the view definition.

24. A Summary of VIEW Facts A Summary of VIEW Facts
•A view cannot reference the A view cannot reference the nextvalnextval and and currvalcurrval
pseudocolumns created through the use of pseudocolumns created through the use of
sequences.sequences.
•A row cannot be inserted in a view in which the A row cannot be inserted in a view in which the
base table has a column with the NOT NULL or base table has a column with the NOT NULL or
other constraint that cannot be satisfied by the new other constraint that cannot be satisfied by the new
row data.row data.

25. SYNONYMSSYNONYMS
•A A synonym synonym is an is an aliasalias, that is, a form of shorthand , that is, a form of shorthand
used to simplify the task of referencing a database used to simplify the task of referencing a database
object. object.
•Creating SynonymsCreating Synonyms
•The general form of the CREATE SYNONYM The general form of the CREATE SYNONYM
command is:command is:
CREATE [PUBLIC] SYNONYM CREATE [PUBLIC] SYNONYM
synonym_name FOR object_name;synonym_name FOR object_name;

26. SYNONYMSSYNONYMS
•There are two categories of synonyms, There are two categories of synonyms, publicpublic and and
privateprivate..
•A public synonym can be accessed by any system A public synonym can be accessed by any system
user.user.
•The individual creating a public synonym does not The individual creating a public synonym does not
own the synonym – rather, it will belong to the own the synonym – rather, it will belong to the
PUBLIC user group that exists within Oracle.PUBLIC user group that exists within Oracle.
•Private synonyms, on the other hand, belong to the Private synonyms, on the other hand, belong to the
system user that creates them and reside in that system user that creates them and reside in that
user's schema. user's schema.

27. SYNONYMSSYNONYMS
•A system user can grant the privilege to use A system user can grant the privilege to use
private synonyms that they own to other system private synonyms that they own to other system
users.users.
•In order to create synonyms, you will need to have In order to create synonyms, you will need to have
the CREATE SYNONYM privilege.the CREATE SYNONYM privilege.
•This privilege will be granted to you by the DBA.This privilege will be granted to you by the DBA.
•You must have the CREATE PUBLIC You must have the CREATE PUBLIC
SYNONYM privilege in order to create public SYNONYM privilege in order to create public
synonyms.synonyms.

28. SYNONYMSSYNONYMS
•The three advantages to synonym usage. The three advantages to synonym usage.
 
    
 
    
First, a synonym provides what is termed First, a synonym provides what is termed location location
transparencytransparency because the synonym because the synonym name hides the name hides the
actual object name and actual object name and object object owner from the owner from the
user of the synonym. user of the synonym.
 
    
 
    
Second, you can create a synonym for a database Second, you can create a synonym for a database
object and then refer to the synonym in object and then refer to the synonym in application application
code. The underlying object can be code. The underlying object can be moved or moved or
renamed, and a redefinition of the renamed, and a redefinition of the synonym will synonym will
allow the application code to allow the application code to continue to execute continue to execute
without errors. without errors.
 
    
 
    
Third, a public synonym can be used to allow easy Third, a public synonym can be used to allow easy
access to an object for all system users. access to an object for all system users.

29. Dropping SynonymsDropping Synonyms
•If you own a synonym, you have the right to drop If you own a synonym, you have the right to drop
(delete) the synonym. The DROP SYNONYM (delete) the synonym. The DROP SYNONYM
command is quite simple.command is quite simple.
DROP SYNONYM synonym_name;DROP SYNONYM synonym_name;
•In order to drop a public synonym you must include In order to drop a public synonym you must include
the PUBLIC keyword in the DROP SYNONYM the PUBLIC keyword in the DROP SYNONYM
command. command.
•In order to drop a public synonym, you must have the In order to drop a public synonym, you must have the
DROP PUBLIC SYNONYM privilege. DROP PUBLIC SYNONYM privilege.
DROP PUBLIC SYNONYM DROP PUBLIC SYNONYM
synonym_name;synonym_name;

30. Renaming SynonymsRenaming Synonyms
•Private synonyms can be renamed with the Private synonyms can be renamed with the
RENAME SYNONYM command.RENAME SYNONYM command.
•All existing references to the synonym are All existing references to the synonym are
automatically updated.automatically updated.
•Any system user with privileges to use a synonym Any system user with privileges to use a synonym
will retain those privileges if the synonym name is will retain those privileges if the synonym name is
changed. changed.
•The syntax of the RENAME SYNONYM The syntax of the RENAME SYNONYM
command is like that for the RENAME command command is like that for the RENAME command
for any other database object such as a view or for any other database object such as a view or
table. table.
RENAME old_synonym_name TO RENAME old_synonym_name TO
new_synonym_name;new_synonym_name;

31. Renaming SynonymsRenaming Synonyms
•The RENAME SYNONYM command only The RENAME SYNONYM command only
works for private synonyms.works for private synonyms.
•If we attempt to rename a public synonym If we attempt to rename a public synonym
such as the such as the tblspacestblspaces synonym, Oracle will synonym, Oracle will
return an ORA-04043: return an ORA-04043: object tblspaces object tblspaces
does not existdoes not exist error message as is shown error message as is shown
here.here.
RENAME tblspaces TO ts;RENAME tblspaces TO ts;
ORA-04043: object ORA-04043: object
TBLSPACES TBLSPACES does not existdoes not exist

32. SEQUENCES SEQUENCES
•Oracle provides the capability to generate Oracle provides the capability to generate
sequences of unique numbers, and they are called sequences of unique numbers, and they are called
sequences. sequences.
•Just like tables, views, indexes, and synonyms, a Just like tables, views, indexes, and synonyms, a
sequence is a type of database object. sequence is a type of database object.
•Sequences are used to generate unique, sequential Sequences are used to generate unique, sequential
integer values that are used as primary key values integer values that are used as primary key values
in database tables.in database tables.
•The sequence of numbers can be generated in either The sequence of numbers can be generated in either
ascending or descending order. ascending or descending order.

33. Creating SequencesCreating Sequences
•The syntax of the CREATE SEQUENCE command is The syntax of the CREATE SEQUENCE command is
fairly complex because it has numerous optional fairly complex because it has numerous optional
clauses.clauses.
CREATE SEQUENCE <sequence name>CREATE SEQUENCE <sequence name>
[INCREMENT BY <number>][INCREMENT BY <number>]
[START WITH <start value number>][START WITH <start value number>]
[MAXVALUE <MAXIMUM VLAUE NUMBER>][MAXVALUE <MAXIMUM VLAUE NUMBER>]
[NOMAXVALUE][NOMAXVALUE]
[MINVALUE <minimum value number>][MINVALUE <minimum value number>]
[CYCLE][CYCLE]
[NOCYCLE][NOCYCLE]
[CACHE <number of sequence value to cache>][CACHE <number of sequence value to cache>]
[NOCACHE][NOCACHE]
[ORDER][ORDER]
[NOORDER];[NOORDER];

34. ExampleExample
CREATE SEQUENCE order_number_sequenceCREATE SEQUENCE order_number_sequence
INCREMENT BY 1INCREMENT BY 1
START WITH 1START WITH 1
MAXVALUE 100000000MAXVALUE 100000000
MINVALUE 1MINVALUE 1
CYCLECYCLE
CACHE 10;CACHE 10;
Sequence created.Sequence created.

35. Accessing Sequence Values Accessing Sequence Values
•Sequence values are generated through the use of two Sequence values are generated through the use of two
pseudocolumnspseudocolumns named named currvalcurrval and and nextvalnextval. .
•A pseudocolumn behaves like a table column, but A pseudocolumn behaves like a table column, but
psuedocolumns are not actually stored in a table. psuedocolumns are not actually stored in a table.
•We can select values from pseudocolumns but cannot We can select values from pseudocolumns but cannot
perform manipulations on their values.perform manipulations on their values.
•The first time you select the The first time you select the nextvalnextval pseudocolumn, pseudocolumn,
the initial value in the sequence is returned.the initial value in the sequence is returned.
•Subsequent selections of the Subsequent selections of the nextvalnextval pseudocolumn pseudocolumn
will cause the sequence to increment as specified by will cause the sequence to increment as specified by
the INCREMENT BY clause and will return the the INCREMENT BY clause and will return the
newly generated sequence value. newly generated sequence value.

36. Accessing Sequence Values Accessing Sequence Values
• The The currvalcurrval pseudocolumn returns the current value pseudocolumn returns the current value
of the sequence, which is the value returned by the of the sequence, which is the value returned by the
last reference to nextval. last reference to nextval.
•ExampleExample
CREATE TABLE sales_order (CREATE TABLE sales_order (
order_number NUMBER(9)order_number NUMBER(9)
CONSTRAINT pk_sales_order PRIMARY KEY,CONSTRAINT pk_sales_order PRIMARY KEY,
order_amount NUMBER(9,2));order_amount NUMBER(9,2));

37. Accessing Sequence Values Accessing Sequence Values
•The INSERT commands shown below insert three rows into The INSERT commands shown below insert three rows into
the the sales_ordersales_order table. The INSERT commands reference table. The INSERT commands reference
the the order_number_sequence.nextvalorder_number_sequence.nextval pseudocolumn. pseudocolumn.
INSERT INTO sales_order INSERT INTO sales_order
VALUES(order_number_sequence.nextval, VALUES(order_number_sequence.nextval,
155.59 );155.59 );
INSERT INTO sales_order INSERT INTO sales_order
VALUES(order_number_sequence.nextval, VALUES(order_number_sequence.nextval,
450.00 );450.00 );
INSERT INTO sales_orderINSERT INTO sales_order
VALUES(order_number_sequence.nextval, VALUES(order_number_sequence.nextval, 16.95);16.95);

38. Accessing Sequence Values Accessing Sequence Values
SELECT *SELECT *
FROM sales_order;FROM sales_order;
  
ORDER_NUMBER ORDER_AMOUNTORDER_NUMBER ORDER_AMOUNT
------------ ------------------------ ------------
1 155.591 155.59
2 4502 450
3 16.953 16.95

39. Accessing Sequence Values Accessing Sequence Values
•Use of Use of currval.currval.
CREATE TABLE order_details (CREATE TABLE order_details (
order_numberorder_number NUMBER(9),NUMBER(9),
order_roworder_row NUMBER(3),NUMBER(3),
product_descproduct_desc VARCHAR2(15),VARCHAR2(15),
quantity_orderedquantity_ordered NUMBER(3),NUMBER(3),
product_priceproduct_price NUMBER(9,2),NUMBER(9,2),
CONSTRAINT pk_order_details CONSTRAINT pk_order_details
PRIMARY KEY (order_number, order_row),PRIMARY KEY (order_number, order_row),
CONSTRAINT fk_order_number FOREIGN KEY CONSTRAINT fk_order_number FOREIGN KEY
(order_number) REFERENCES sales_order);(order_number) REFERENCES sales_order);

40. Accessing Sequence Values Accessing Sequence Values
•The order_details table has a FOREIGN KEY The order_details table has a FOREIGN KEY
reference to the sales_order table through the reference to the sales_order table through the
order_number column.order_number column.
DELETE FROM sales_order;DELETE FROM sales_order;
INSERT INTO sales_order INSERT INTO sales_order
VALUES ( order_number_sequence.nextval, 200.00 );VALUES ( order_number_sequence.nextval, 200.00 );
INSERT INTO order_detailsINSERT INTO order_details
VALUES ( order_number_sequence.currval, 1, 'End VALUES ( order_number_sequence.currval, 1, 'End
Table',1, 100.00);Table',1, 100.00);
INSERT INTO order_detailsINSERT INTO order_details
VALUES ( order_number_sequence.currval, 2, 'Table VALUES ( order_number_sequence.currval, 2, 'Table
Lamp',2, 50.00);Lamp',2, 50.00);

41. Accessing Sequence Values Accessing Sequence Values
SELECT * SELECT *
FROM sales_order;FROM sales_order;
  
ORDER_NUMBER ORDER_AMOUNTORDER_NUMBER ORDER_AMOUNT
------------ ------------------------ ------------
5 2005 200
  
SELECT * SELECT *
FROM order_details;FROM order_details;
  
ORDER_NUMBER ORDER_ROW PRODUCT_DESC QUANTITY_ORDERED PRODUCT_PRICEORDER_NUMBER ORDER_ROW PRODUCT_DESC QUANTITY_ORDERED PRODUCT_PRICE
--------- -------- ---------- ------------- ---------- --------- -------- ---------- ------------- ----------
5 1 End Table 1 5 1 End Table 1
100100
5 2 Table Lamp 2 505 2 Table Lamp 2 50

42. Altering a SequenceAltering a Sequence
•A sequence is usually altered when it is desirable A sequence is usually altered when it is desirable
to set or eliminate the values of the MINVALUE to set or eliminate the values of the MINVALUE
or MAXVALUE parameters, or to change the or MAXVALUE parameters, or to change the
INCREMENT BY value, or to change the number INCREMENT BY value, or to change the number
of cached sequence numbers. of cached sequence numbers.
•The ALTER SEQUENCE command shown here The ALTER SEQUENCE command shown here
changes the MAXVALUE of the changes the MAXVALUE of the
order_number_sequence to 200,000,000.order_number_sequence to 200,000,000.
ALTER SEQUENCE order_number_sequence ALTER SEQUENCE order_number_sequence
MAXVALUE 200000000;MAXVALUE 200000000;
Sequence altered.Sequence altered.

43. Altering a SequenceAltering a Sequence
•When specifying a MINVALUE clause, the When specifying a MINVALUE clause, the
specified value should be less than the specified value should be less than the
MAXVALUE where a sequence generates MAXVALUE where a sequence generates
ascending numbers.ascending numbers.
•In the case of a descending sequence, the In the case of a descending sequence, the
MAXVALUE should be less than the MAXVALUE should be less than the
MINVALUE. MINVALUE.

44. Viewing Sequence PropertiesViewing Sequence Properties
•You may need to review the names and properties of You may need to review the names and properties of
your sequences.your sequences.
•You can do this by querying the USER_SEQUENCES You can do this by querying the USER_SEQUENCES
system view with a SELECT command.This view is part system view with a SELECT command.This view is part
of the database's data dictionary.of the database's data dictionary.
SELECT * FROM USER_SEQUENCES;SELECT * FROM USER_SEQUENCES;
  
SEQUENCE_NAME MIN_VAL MAX_VALUE INCRE C O CACHE_SIZE Last_NSEQUENCE_NAME MIN_VAL MAX_VALUE INCRE C O CACHE_SIZE Last_N
---------------- ------ ---------- ----- -- -- -------------------- ------ ---------- ----- -- -- ---- ---- ----
----------
ORDER_NUMBER_SEQUENCEORDER_NUMBER_SEQUENCE 1 200000000 1 Y N 10 6 1 200000000 1 Y N 10 6

45. Dropping a SequenceDropping a Sequence
•DROP SEQUENCE command is used to drop DROP SEQUENCE command is used to drop
sequences that need to be recreated or are no longer sequences that need to be recreated or are no longer
needed. needed.
•The general format is shown here along with an The general format is shown here along with an
example that drops the example that drops the order_number_sequenceorder_number_sequence
object.object.
DROP SEQUENCE <sequence name>;DROP SEQUENCE <sequence name>;
  DROP SEQUENCE order_number_sequence;DROP SEQUENCE order_number_sequence;
Sequence dropped.Sequence dropped.

46. ENDEND