OCA: Oracle Database 11g Administrator Certified Associate Study Guide- P8

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

Thêm vào BST

Báo xấu

177
lượt xem 30
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

OCA: Oracle Database 11g Administrator Certified Associate Study Guide- P8: There is high demand for professionals in the information technology (IT) industry, and Oracle certifications are the hottest credential in the database world. You have made the right decision to pursue certification, because being Oracle Database 11g certified will give you a distinct advantage in this highly competitive market.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: OCA: Oracle Database 11g Administrator Certified Associate Study Guide- P8

Review Questions 281 13. The following table describes the DEPARTMENTS table: Column Name dept_id dept_name mgr_id location_id Key Type pk Nulls/Unique NN FK Table Datatype NUMBER VARCHAR2 NUMBER NUMBER Length 4 30 6 4 Default Value None None None 99 Which of the following INSERT statements will raise an exception? A. INSERT INTO departments (dept_id, dept_name, location_id) VALUES(280,’Security’,1700); B. INSERT INTO departments VALUES(280,’Security’,1700); C. INSERT INTO departments VALUES(280,’Corporate Giving’,266,1700); D. None of these statements will raise an exception. 14. Refer to the DEPARTMENTS table structure in question 13. Two SQL statements are shown here. Choose the best option that describes the SQL statements. 1.INSERT INTO departments (dept_id, dept_name, mgr_id) VALUES(280,’Security’,1700); 2.INSERT INTO departments (dept_id, dept_name, mgr_id, location_id) VALUES(280,’Security’,1700, NULL); A. Statements 1 and 2 insert the same values to all columns in the table. B. Statements 1 and 2 insert different values to at least one column in the table. C. The location_id column must be included in the column list of statement 1. D. A NULL value cannot be inserted explicitly in statement 2.
282 Review Questions 15. The SALES table contains the following data: SELECT channel_id, COUNT(*) FROM sales GROUP BY channel_id; C COUNT(*) - ---------- T 12000 I 24000 How many rows will be inserted into the NEW_CHANNEL_SALES table with the following SQL statement? INSERT FIRST WHEN channel_id =’C’ THEN INTO catalog_sales (prod_id,time_id,promo_id ,amount_sold) VALUES (prod_id,time_id,promo_id,amount_sold) WHEN channel_id =’I’ THEN INTO internet_sales (prod_id,time_id,promo_id ,amount_sold) VALUES (prod_id,time_id,promo_id,amount_sold) WHEN channel_id IN (‘I’,’T’) THEN INTO new_channel_sales (prod_id,time_id,promo_id ,amount_sold) VALUES (prod_id,time_id,promo_id,amount_sold) SELECT channel_id,prod_id,time_id,promo_id,amount_sold FROM sales; A. 0 B. 12,000 C. 24,000 D. 36,000 16. How many rows will be counted in the last SQL statement that follows? SELECT COUNT(*) FROM emp; 120 returned INSERT INTO emp (emp_id) VALUES (140); SAVEPOINT emp140;
Review Questions 283 INSERT INTO emp (emp_id) VALUES (141); INSERT INTO emp (emp_id) VALUES (142); INSERT INTO emp (emp_id) VALUES (143); TRUNCATE TABLE employees; INSERT INTO emp (emp_id) VALUES (144); ROLLBACK; SELECT COUNT(*) FROM emp; A. 121 B. 0 C. 124 D. 143 17. Which is the best option that describes the following SQL statement? 1.UPDATE countries 2.CNT_NAME = UPPER(CNT_NAME) 3.WHERE country_code BETWEEN 1 and 99; A. The statement is missing the keyword SET, but the statement will work just fine because SET is an optional keyword. B. The BETWEEN operator cannot be used in the WHERE clause used in an UPDATE statement. C. The function UPPER(CNT_NAME) should be changed to UPPER(‘CNT_NAME’). D. The statement is missing keyword SET; hence, the statement will fail. 18. The table ORDERS has 35 rows. The following UPDATE statement updates all 35 rows. Which is the best option? UPDATE orders SET ship_date = TRUNC(ship_date) WHERE ship_date != TRUNC(ship_date) A. When all rows in a table are updated, the LOCK TABLE orders IN EXCLUSIVE MODE statement must be executed before the UPDATE statement. B. No other session can query from the table until the transaction ends. C. Since all rows are updated, there is no need for any locking, and hence Oracle does not lock the records. D. The statement locks all the rows until the transaction ends.
284 Review Questions 19. Which of the following INSERT statements will raise an exception? A. INSERT INTO EMP SELECT * FROM NEW_EMP; B. INSERT FIRST WHEN DEPT_NO IN (12,14) THEN INSERT INTO EMP SELECT * FROM NEW_EMP; C. INSERT FIRST WHEN DEPT_NO IN (12,14) THEN INTO EMP SELECT * FROM NEW_EMP; D. INSERT ALL WHEN DEPT_NO IN (12,14) THEN INTO EMP SELECT * FROM NEW_EMP; 20. What will the salary of employee Arsinoe be at the completion of the following SQL statements? UPDATE emp SET salary = 1000 WHERE name = ‘Arsinoe’; SAVEPOINT Point_A; UPDATE emp SET salary = salary * 1.1 WHERE name = ‘Arsinoe’; SAVEPOINT Point_B; UPDATE emp SET salary = salary * 1.1 WHERE name = ‘Berenike’; SAVEPOINT point_C; ROLLBACK TO SAVEPOINT point_b; COMMIT; UPDATE emp SET salary = 1500 WHERE name = ‘Arsinoe’; SAVEPOINT point_d; ROLLBACK TO point_d; COMMIT; A. 1000 B. 1100 C. 1111 D. 1500
Answers to Review Questions 285 Answers to Review Questions 1. D. When inserting from another table using a subquery, the VALUES clause should not be included. Options B and C are invalid syntaxes for the INSERT statement. 2. E. If a transaction is not currently open, any INSERT, UPDATE, MERGE, DELETE, SELECT FOR UPDATE, or LOCK statement will implicitly begin a transaction. 3. B, C. Option A will error out because when using columns in set, a subquery must be used as in option C. Option D is wrong because AND is used instead of a comma to separate col- umns in the SET clause. 4. A, D. COMMIT, ROLLBACK, and any DDL statement end a transaction—DDL is automatically committed. INSERT, UPDATE, and DELETE statements require a commit or rollback. 5. A. Option A uses a correlated subquery to match the correct employee. Option B selects all the rows in the subquery and hence will generate an error. Option C is not valid syntax. Option D will update all the rows in the table since the UPDATE statement does not have a WHERE clause. The WHERE clause preset belongs to the subquery. 6. B. In an UPDATE statement, the WHERE clause should come after the SET clause. 7. D. When deleting a row from a table, do not use column names. To change column values to NULL, use the UPDATE statement. 8. C. The FROM keyword in the DELETE statement is optional. Statement 3 is first building a subquery with the necessary condition and deleting the rows from the subquery. 9. B. When two savepoints are created with the same name, Oracle erases the older savepoint. In the code segment, the DELETE and the first INSERT are not rolled back. 10. C. When updating more than one column in a single UPDATE statement, separate the col- umns by a comma; do not use the AND operator. 11. D. Option A is updating the wrong table. Option B has the right syntax but will update all the rows in the EMPLOYEE table since there is no WHERE clause for the UPDATE statement. Since the WHERE clause is in the subquery, all the rows that do not belong to department 22 will be updated with a NULL. Options C and D are similar, except for the AND keyword instead of WHERE. 12. D. The first INSERT statement and the last INSERT statement will be saved in the database. The ROLLBACK TO A statement will undo the second and third inserts. 13. B. Option B will raise an exception because there are not enough column values for the implicit column list (all columns). 14. B. Since the location_id column is defined with a default value of 99, statement 1 will insert 99 for location_id. In statement 2, a NULL is explicitly inserted into the location_id column; Oracle will not replace the NULL with the default value defined.
286 Answers to Review Questions 15. B. The FIRST clause tells Oracle to execute only the first WHEN clause that evaluates to TRUE for each row. Since no rows have a channel_id of C, no rows would be inserted into the catalog_sales table; 24,000 rows have channel_id of I, so control would pass to the sec- ond WHEN clause 24,000 times, and the internet_sales table would get 24,000 rows. Since the second WHEN clause evaluates to TRUE and the INSERT FIRST option is specified, these rows would not make it to the third WHEN clause and would not be inserted into the new_ channel_sales table. Had the INSERT ALL option been used, these 24,000 rows would also get inserted into the new_channel_sales table; 12,000 rows have a channel_id of T, so control would pass all the way to the third WHEN clause for these rows, and 12,000 rows would get inserted into new_channel_sales. 16. C. The TRUNCATE statement is DDL and performs an implicit commit. After the TRUNCATE statement on the employees table, there are 124 rows in the emp table. The one row that got inserted was removed when the ROLLBACK statement was executed. 17. D. You must have the SET keyword in an UPDATE statement. The BETWEEN operator and any other valid operators are allowed in the WHERE clause. 18. D. When DML operations are performed, Oracle automatically locks the rows. You can query (read) the rows, but no other DML operation is allowed on those rows. When you read the rows, Oracle constitutes a read-consistent view using the undo segments. 19. B. The keywords INSERT INTO are required in single-table INSERT statements but are not valid in multiple-table INSERT statements. 20. D. The final rollback (to point_d) will roll the changes back to just after setting the salary to 1500.
Chapter Creating Tables and 6 Constraints OraCle DaTabase 11g: sQl FunDamenTals I exam ObjeCTIves COvereD In ThIs ChapTer: Using DDL Statements to Create and Manage Tables ÛÛ NÛ Categorize the main database objects NÛ Review the table structure NÛ List the data types that are available for columns NÛ Create a simple table NÛ Explain how constraints are created at the time of table creation NÛ Describe how schema objects work
An Oracle database has many different types of objects. Related objects are logically grouped together in a schema, which consists of various types of objects. The basic types of objects in an Oracle Database are tables, indexes, constraints, sequences, and synonyms. Though this chapter discusses tables and constraints, I will start the chapter with an over- view of the main database objects in Oracle. The table is the basic structure of data storage in Oracle. A table has columns as part of the definition and stores rows of data. In a relational database, the data in various tables may be related. A constraint can be considered as a rule or policy defined in the database to enforce data integrity and business rules. In this chapter, I will discuss creating tables and using constraints. Since the table is the most important type of object in an Oracle Data- base, it is important to know how to create tables and constraints on tables. Database Objects Overview Data in the Oracle Database is stored in tables. A table is the main database object. Many other database objects, whether or not they store data, are generally based on the tables. Let’s review the main database objects in Oracle that are relevant for this certification exam: Table A table is defined with columns and stores rows of data. A table should have at least one column. In Oracle, a table normally refers to a relational table. You can also create object tables. Object tables are created with user-defined datatypes. Temporary tables (called global temporary tables in Oracle) are used to hold temporary data specific to a transaction or session. A table can store a wide variety of data. Apart from storing text and numeric infor- mation, you can store date, timestamp, binary, or raw data (such as images, documents, and information about external files). A table can have virtual columns. As the name indicates, these types of columns do not consume storage space on disk; the database derives values in virtual columns from normal columns. Tables are discussed in the next sections of this chapter. View A view is a customized representation of data from one or more tables and/or views. Views are used as a window to show information from tables in a certain way or to restrict the information. Views are queries stored in the database that select data from one or more tables. They also provide a way to restrict data from certain users, thus providing an addi- tional level of security.
Database Objects Overview 289 Sequence A sequence is a way to generate continuous numbers. Sequences are useful for generating unique serial numbers or key values. The sequence definition is stored in the data dictionary. Sequence numbers are generated independently of other database objects. Synonym A synonym is an alias for any table, view, sequence, or other accessible database object. Because a synonym is simply an alias, it requires no storage other than its definition in the data dictionary. Synonyms are useful because they hide the identity of the underlying object. The object can even be part of another database. A public synonym is accessible to all users of the database, and a private synonym is accessible only to its owner. Index An index is a structure associated with tables used to speed up the queries. An index is an access path to reach the desired row faster. Oracle has B-tree and bitmap indexes. Creating/dropping indexes does not affect the storage of data in the underlying tables. You can create unique or nonunique indexes. Unique indexes are created automatically by Oracle when you create a primary key or a unique key constraint in a table. A composite index has more than one column in the index. Views, sequences, synonyms, and indexes are discussed in Chapter 7, “Creating Schema Objects.” Oracle 11g has a wide array of database objects to suit various application requirements. These objects are not discussed in this book because they are not part of the certifica- tion exam at this time. Some of the other database objects that may be used in application development are clusters, dimensions, directories, functions, Java sources/classes, libraries, materialized views, and types. To learn more about the various Oracle 11g database schema objects, please refer to the Oracle documentation called “Oracle Database Administrators Guide 11g Release 1 (11.) Part Number B28310-04,” which is available online at www.oracle .com/pls/db111/db111.homepage. Schema Objects A schema is a collection of related database objects grouped together. For example, a schema can have tables, views, triggers, synonyms, and PL/SQL programs such as procedures. A schema is owned by a database user and has the same name as the user. If the database user does not own any database objects, then no schema is associated with the user. A schema is a logical grouping of database objects. A database user can have only one schema associated and is created when you create any database object. They may include any or all the basic database objects discussed earlier. Oracle 11g may also include the following types of structures in the schema. These objects are listed here only to give you an overview of schemas; creating and managing these objects are not part of the certification exam at this time. For the certification exam, prepare to know the schema objects discussed in this chapter and in Chapter 7.
290 Chapter 6 N Creating Tables and Constraints Materialized view Materialized views are objects used to summarize and replicate data. They are similar to views but occupy storage space. Materialized views are mainly used in data-warehouse environments where data needs to be aggregated and stored so that queries and reports run faster. Materialized views can also be used to replicate data from another database. Dimension A dimension is a logical structure to define the relationship between columns in a table. Dimensions are defined in the data dictionary and do not occupy any storage space. The columns in a dimension can be from a single table or from multiple tables. An example of a dimension would be the relationship between country, state, and city in a table that stores address information. Cluster A cluster is a method of storing data from related tables at a common physical location. You can share the storage of rows in related tables for performance reasons if the access to the rows in the tables always involves join operations on the tables. For example, if you have an orders table and a customers table in the schema, you can query the orders table always joining the customers table, because that’s where you get the customer name associated with the customer ID. A cluster may be created for the orders and customers tables so that the rows associated with the same customer are stored in the same physical storage area (block). Database storage and blocks are discussed in Chapter 8, “Introducing Oracle 11g Components and Architecture.” Database links A database link is a schema object that enables you to access an object from a different database. SQL queries can reference tables and views belonging to the remote database by appending @db_link_name to the table or view. For example, to access the CUSTOMER_ORDERS table using a database link named LONDON_SALES, you would use CUSTOMER_ORDERS@LONDON_SALES. Triggers A trigger is a stored PL/SQL program that gets executed when a specified condi- tion occurs. A trigger can be defined on a table to “fire” when an insert, update, or delete operation occurs on the table. A trigger may also be defined on the database to “fire” when certain database conditions occur, such as starting the database, or when a database error occurs. Java objects Oracle Database 11g includes Java objects such as Java classes, Java sources, and Java resources. Java stored programs can be created using the different Java object types. PL/SQL programs PL/SQL stored programs include procedures, functions, and packages. A procedure is a PL/SQL programmatic construct. A function is similar to a procedure but always returns a value. A package is a grouping of related PL/SQL objects. Built-in Datatypes When creating tables, you must specify a datatype for each column you define. Oracle 11g is rich with various datatypes to store different kinds of information. By choosing the
Built-in Datatypes 291 appropriate datatype, you will be able to store and retrieve data without compromising its integrity. A datatype associates a predefined set of properties with the column. The datatypes in Oracle 11g can be classified into five major categories. Figure 6.1 shows the categories and the datatype names. F I g u r e 6 .1 Oracle built-in datatypes Character Numeric Row ID CHAR NUMBER ROWID VARCHAR2 BINARY_FLOAT UROWID CLOB BINARY_DOUBLE LONG FLOAT NCHAR NVARCHAR2 NCLOB Date and Time Binary DATE TIMESTAMP RAW TIMESTAMP WITH TIME ZONE LONG RAW TIMESTAMP WITH LOCAL TIME ZONE BLOB INTERVAL YEAR TO MONTH BFILE INTERVAL DAY TO SECOND Chapter 1, “Introducing SQL,” introduced four basic datatypes: CHAR, VARCHAR2, NUMBER, and DATE. Here, I will review those datatypes and describe the other datatypes that you can specify while creating a table. Character Datatypes Seven character datatypes can be used for defining columns in a table: NÛ CHAR NÛ NCHAR NÛ VARCHAR2 NÛ NVARCHAR2 NÛ CLOB NÛ NCLOB NÛ LONG
292 Chapter 6 N Creating Tables and Constraints Character datatypes store alphanumeric data in the database character set or in the Unicode character set. You define the database character set when you create the database. The character set determines which languages can be represented in the database. For example, US7ASCII is a 7-bit ASCII character set that can represent the English language and any other language that uses the English alphabet set. WE8ISO8859P1 is an 8-bit character set that can support multiple European languages such as English, German, French, Albanian, Spanish, Portuguese, Irish, and so on, because they all use a similar writing script. Unicode, the Universal Character Set, allows you to store any language character using a single character set. The Unicode character set supported by Oracle is either 16-bit encod- ing (UTF-16) or 8-bit encoding (UTF-8). You can choose the Unicode datatypes to be used in the database while creating the database. The default is the AL16UTF16 character set, which is UTF-16 encoding. CHAR The syntax for the CHAR datatype is as follows: CHAR [( [BYTE | CHAR ] ) ] The CHAR datatype is fixed-length, with the maximum size of the column specified in parentheses. You can also include the optional keyword BYTE or CHAR inside the parentheses along with the size to indicate whether the size is in bytes or in characters. BYTE is the default. For single-byte-database character sets (such as US7ASCII), the size specified in bytes and the size specified in characters are the same. If the column value is shorter than the size defined, trailing spaces are added to the column value. Specifying the size is optional, and the default size is 1 byte. The maximum allowed size in a CHAR datatype column is 2,000 bytes. Here are few examples of specifying a CHAR datatype column: employee_id CHAR (5) employee_name CHAR (100 CHAR) employee_sex CHAR NCHAR The syntax for the NCHAR datatype is as follows: NCHAR [ ( ) ] The NCHAR datatype is similar to CHAR, but it is used to store Unicode character-set data. The NCHAR datatype is fixed-length, with a maximum size of 2,000 bytes and a default size of a character. The size in the NCHAR datatype definition is always specified in characters. Trailing spaces are added if the value inserted into the column is shorter than the column’s maxi- mum length. Here is an example of specifying an NCHAR datatype column: emp_name NCHAR (100)
Built-in Datatypes 293 Several built-in Oracle 11g functions have options to represent NCHAR data. An NCHAR string may be represented by prefixing the string with N, as in this example: SELECT emp_name FROM employee_records WHERE emp_name = N’John Smith’; VARCHAR2 or VARCHAR The syntax for the VARCHAR2 datatype is as follows: VARCHAR2 ( [BYTE | CHAR] ) VARCHAR2 and VARCHAR are synonymous datatypes. VARCHAR2 specifies vari- able-length character data. A maximum size for the column should be defined; Oracle 11g will not assume any default value. Unlike CHAR columns, VARCHAR2 columns are not blank-padded with trailing spaces if the column value is shorter than its maximum specified length. You can specify the size in bytes or characters; by default, the size is in bytes. The range of values allowed for size is from 1 to 4,000 bytes. For storing variable-length data, Oracle recommends using VARCHAR2 rather than VARCHAR, because the behavior of the VARCHAR datatype may change in a future release. NVARCHAR2 The syntax for the NVARCHAR2 datatype is as follows: NVARCHAR2 () The NVARCHAR2 datatype is used to store Unicode variable-length data. The size is specified in characters, and the maximum size allowed is 4,000 bytes. If you try to insert a value into a character datatype column that is larger than its maximum specified size, Oracle will return an error. Oracle will not chop or truncate the inserted value to store it in the database column. CLOB The syntax for the CLOB datatype is as follows: CLOB CLOB is one of the Large Object datatypes provided to store variable-length character data. The maximum amount of data you can store in a CLOB column is based on the block size of the database. CLOB can store up to (4GB–1)*(database block size). You do not spec- ify the size with this datatype definition. NCLOB The syntax for the NCLOB datatype is as follows: NCLOB
294 Chapter 6 N Creating Tables and Constraints NCLOB is one of the Large Object datatypes and stores variable-length Unicode charac- ter data. The maximum amount of data you can store in a NCLOB column is (4GB–1)* (database block size). You do not specify the size with this datatype definition. LONG The syntax for the LONG datatype is as follows: LONG Using the LONG datatype is discouraged in Oracle Database 11g. It is provided only for backward compatibility. You should use the CLOB datatype instead of LONG. LONG col- umns can store up to 2GB of character data. There can be only one LONG column in the table definition. A LONG datatype column can be used in the SELECT clause of a query, the SET clause of the UPDATE statement, and the VALUES clause of the INSERT statement. You can also create a NOT NULL constraint on a LONG column. LONG datatype columns cannot appear in the following: NÛ The WHERE, GROUP BY, or ORDER BY clauses NÛ A SELECT clause if the DISTINCT operator is used NÛ A SELECT list of subqueries used in INSERT statements NÛ A SELECT list of subqueries used with the UNION, INTERSECT, or MINUS operator NÛ A SELECT list of queries with the GROUP BY clause Numeric Datatypes Four built-in numeric datatypes can be used for defining numeric columns in a table: NÛ NUMBER NÛ BINARY_FLOAT NÛ BINARY_DOUBLE NÛ FLOAT Numeric datatypes are used to store integer and floating-point numbers. The NUMBER datatype can store all types of numeric data, but BINARY_FLOAT and BINARY_DOUBLE give better performance with floating-point numbers. FLOAT is a subtype of NUMBER. NUMBER The syntax for the NUMBER datatype is as follows: NUMBER [ ( [, ] )] You can represent all non-Oracle numeric datatypes such as float, integer, decimal, double, and so on, using the NUMBER datatype. The NUMBER datatype can store both fixed-point and floating-point numbers. Oracle 11g introduced two new datatypes to sup- port floating-point numbers—specifically, BINARY_FLOAT and BINARY_DOUBLE.
Built-in Datatypes 295 BINARY_FLOAT The syntax for the BINARY_FLOAT datatype is as follows: BINARY_FLOAT The BINARY_FLOAT datatype represents a 32-bit floating-point number. There is no precision defined in the definition of this datatype because it uses binary precision. BINARY_FLOAT uses 5 bytes for storage. A floating-point number can have a decimal point anywhere or can have no decimal point. Oracle stores NUMBER datatype values using decimal precision, whereas floating-point numbers (BINARY_FLOAT and BINARY_DOUBLE) are stored using binary precision. Oracle has three special values that can be used with floating-point numbers: INF: Positive infinity -INF: Negative infinity NaN: Not a Number (NaN is not the same as NULL) BINARY_DOUBLE The syntax for the BINARY_DOUBLE datatype is as follows: BINARY_DOUBLE The BINARY_DOUBLE datatype represents a 64-bit floating-point number. BINARY_ DOUBLE uses 9 bytes for storage. All the characteristics of BINARY_FLOAT are appli- cable to BINARY_DOUBLE. FLOAT The syntax for the FLOAT datatype is as follows: FLOAT [(precision)] The FLOAT datatype is a subtype of NUMBER and is internally represented as NUMBER. There is no scale for FLOAT numbers; only precision can be optionally included. The precision can range from 1 to default binary digits. In the NUMBER datatype the precision and scale are represented in decimal digits, whereas in FLOAT the precision is represented in binary digits. In Oracle 11g it is recommended you use BINARY_FLOAT or BINARY_DOUBLE instead of the FLOAT datatype. Date and Time Datatypes In pre–Oracle9i databases, the only datetime datatype available was DATE, which stores the date and time. Oracle9i Database introduced the TIMESTAMP and INTERVAL data- types to enhance the storage and manipulation of date and time data. Six datetime datatypes in Oracle 11g can be used for defining columns in a table: NÛ DATE NÛ TIMESTAMP NÛ TIMESTAMP WITH TIME ZONE
296 Chapter 6 N Creating Tables and Constraints NÛ TIMESTAMP WITH LOCAL TIME ZONE NÛ INTERVAL YEAR TO MONTH NÛ INTERVAL DAY TO SECOND The interval datatypes are used to represent a measure of time. They store the number of months or number of days/hours between two time points. All interval components are integers except the seconds, which may have fractional seconds represented. DATE The syntax for the DATE datatype is as follows: DATE The DATE datatype stores date and time information. You can store the dates from January 1, 4712 BC to December 31, 9999 AD. If you specify a date value without the time component, the default time is 12 a.m. (midnight, 00:00:00 hours). If you specify a date value without the date component, the default value is the first day of the current month. The DATE datatype stores century, year, month, date, hour, minute, and seconds internally. You can display the dates in various formats using the NLS_DATE_FORMAT param- eter or by specifying a format mask with the TO_CHAR function. The various date-format masks are discussed in Chapter 2, “Using Single-Row Functions.” TIMESTAMP The syntax for TIMESTAMP datatype is as follows: TIMESTAMP [()] The TIMESTAMP datatype stores date and time information with fractional seconds precision. The only difference between the DATE and TIMESTAMP datatypes is the abil- ity to store fractional seconds up to a precision of nine digits. The default precision is 6 and can range from 0 to 9. TIMESTAMP WITH TIME ZONE The syntax for the TIMESTAMP WITH TIME ZONE datatype is as follows: TIMESTAMP [()] WITH TIME ZONE The TIMESTAMP WITH TIME ZONE datatype is similar to the TIMESTAMP data- type, but it stores the time-zone displacement. Displacement is the difference between the local time and the Coordinated Universal Time (UTC, also known as Greenwich mean time). The displacement is represented in hours and minutes. Two TIMESTAMP WITH TIME ZONE values are considered identical if they represent the same time in UTC. For example, 5 p.m. CST is equal to 6 p.m. EST or 3 p.m. PST. TIMESTAMP WITH LOCAL TIME ZONE The syntax for the TIMESTAMP WITH LOCAL TIME ZONE datatype is as follows: TIMESTAMP [()] WITH LOCAL TIME ZONE
Built-in Datatypes 297 The TIMESTAMP WITH LOCAL TIME ZONE datatype is similar to the TIME- STAMP datatype, but like the TIMESTAMP WITH TIME ZONE datatype, it also includes the time-zone displacement. TIMESTAMP WITH LOCAL TIME ZONE does not store the displacement information in the database but stores the time as a normalized form of the database time zone. The data is always stored in the database time zone, but when the user retrieves data, it is shown in the user’s local-session time zone. The following example demonstrates how the DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE datatypes store data. The NLS_xx_FORMAT parameter is explicitly set to display the values in the nondefault format. The data is inserted at Central Daylight Time (CDT), which is seven hours behind UTC. (The output shown in the example was reformatted for better readability.) CREATE TABLE date_time_demo ( r_no NUMBER (2), c_date DATE DEFAULT SYSDATE, c_timezone TIMESTAMP DEFAULT SYSTIMESTAMP, c_timezone2 TIMESTAMP (2) DEFAULT SYSTIMESTAMP, c_ts_wtz TIMESTAMP (0) WITH TIME ZONE DEFAULT SYSTIMESTAMP, c_ts_wltz TIMESTAMP (9) WITH LOCAL TIME ZONE DEFAULT SYSTIMESTAMP); Table created. INSERT INTO date_time_demo (r_no) VALUES (1); 1 row created. ALTER SESSION SET NLS_DATE_FORMAT = ‘YYYY-MM-DD HH24:MI:SS’; Session altered. ALTER SESSION SET NLS_TIMESTAMP_FORMAT = ‘YYYY-MM-DD HH24:MI:SS.FF’; Session altered. ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = ‘YYYY-MM-DD HH24:MI:SS.FFTZH:TZM’; Session altered. SELECT * FROM date_time_demo; R_NO C_DATE C_TIMEZONE --------------------- --------------------------- 1 2008-10-24 13:09:14 2008-10-24 13:09:14. 000001
298 Chapter 6 N Creating Tables and Constraints C_TIMEZONE2 C_TS_WTZ ---------------------- -------------------------- 2008-10-24 13:09:14.00 2008-10-24 13:09:14.-07:00 C_TS_WLTZ ----------------------------- 2008-10-24 13:09:14.000001000 INTERVAL YEAR TO MONTH The syntax for the INTERVAL YEAR TO MONTH datatype is as follows: INTERVAL YEAR [(precision)] TO MONTH The INTERVAL YEAR TO MONTH datatype is used to represent a period of time as years and months. precision specifies the precision needed for the year field, and its default is 2. Valid precision values are from 0 to 9. This datatype can be used to store the difference between two datetime values, where the only significant portions are the year and month. INTERVAL DAY TO SECOND The syntax for the INTERVAL DAY TO SECOND datatype is as follows: INTERVAL DAY [(precision)] TO SECOND The INTERVAL DAY TO SECOND datatype is used to represent a period of time as days, hours, minutes, and seconds. precision specifies the precision needed for the day field, and its default is 6. Valid precision values are from 0 to 9. Larger precision values allow a greater difference between the dates; for example, a precision of 2 allows values from 0 through 99, and a precision of 4 allows values from 0 through 9999. This datatype can be used to store the difference between two datetime values, including seconds. The following example demonstrates the INTERVAL datatypes. It creates a table with the INTERVAL datatypes, inserts data to it, and selects data from the table. CREATE TABLE interval_demo ( ts1 TIMESTAMP (2), iy2m INTERVAL YEAR (3) TO MONTH, id2s INTERVAL DAY (4) TO SECOND); Table created. INSERT INTO interval_demo VALUES ( TO_TIMESTAMP(‘080101-102030.45’, ‘YYMMDD-HH24MISS.FF’), TO_YMINTERVAL(‘3-7’), TO_DSINTERVAL(‘4 02:20:30.30’)); 1 row created.
Built-in Datatypes 299 SELECT * FROM interval_demo; TS1 IY2M ID2S ------------------------- -------- --------------------- 2008-01-01 10:20:30.45 +003-07 +0004 02:20:30.300000 Date Arithmetic Datetime datatypes can be used in expressions with the + or - operator. You can use the +, -, *, and / operators with the INTERVAL datatypes. Dates are stored in the database as Julian numbers with a fraction component for the time. A Julian date refers to the number of days since January 1, 4712 BC. Because of the time component of the date, comparing dates can result in fractional differences, even though the date is the same. Oracle provides a number of functions, such as TRUNC, that help you remove the time component when you want to compare only the date portions. Adding 1 to the date simply moves the date ahead one day. You can add time to the date by adding a fraction of a day. One day equals 24 hours, or 24 × 60 minutes, or 24 × 60 × 60 seconds. Table 6.1 shows the numbers used to add or subtract time for a datetime datatype. Ta b l e 6 .1 Date Arithmetic Time to Add or Subtract Fraction Date Difference 1 day 1 1 1 hour 1/24 1/24 1 minute 1/(24×60) 1/1440 1 second 1/(24×60×60) 1/86400 Subtracting two dates gives you the difference between the dates in days. This usually results in a fractional component that represents the time difference. If the time compo- nents are the same, there will be no fractional results. A datetime value operation using a numeric value results in a datetime value. The fol- lowing example adds 2 days and 12 hours to a date value: ALTER SESSION SET NLS_DATE_FORMAT = ‘YYYY-MM-DD HH24:MI:SS’; SELECT TO_DATE(‘2008-10-24 13:09:14’) + 2.5 EXAMP FROM dual; EXAMP ------------------- 2008-10-27 01:09:14
300 Chapter 6 N Creating Tables and Constraints This example subtracts six hours from a timestamp value: SELECT TO_TIMESTAMP(‘2008-10-24 13:09:14.05’) - 0.25 EXAMP FROM dual; EXAMP ------------------- 2008-10-24 07:09:14 A datetime value subtracted from another datetime value results in a numeric value (the difference in days). You cannot add two datetime values. Here is an example that results in the difference between dates as a fraction of a day: SELECT SYSDATE, SYSDATE - TO_DATE(‘2007-10-24 13:09:14’) FROM dual; SYSDATE SYSDATE-TO_DATE(‘2007-10-2413:09:14’) ------------------- ------------------------------------- 2008-05-11 23:34:06 200.433935 This example converts the fraction of days to hours, minutes, and seconds using the NUMTODSINTERVAL function: SELECT SYSDATE, NUMTODSINTERVAL(SYSDATE - TO_DATE(‘2008-10-24 13:09:14’), ‘DAY’) FROM DUAL; SYSDATE NUMTODSINTERVAL(SYSDATE -------------------- ----------------------------- 2008-10-24 15:53:04 +000000000 02:43:49.999999999 A datetime value operation using an interval value results in a datetime value. The fol- lowing example adds one year and three months to today’s date: SELECT TRUNC(SYSDATE), TRUNC(SYSDATE)+TO_YMINTERVAL(‘1-3’) FROM dual; TRUNC(SYSDATE) TRUNC(SYSDATE)+TO_Y ------------------- ------------------- 2008-10-24 00:00:00 2009-01-24 00:00:00 An interval datatype operation on another interval or numeric value results in an inter- val value. You can use + and – between two interval datatypes and use * and / between interval and numeric values. The following example converts a string (which represents