1.5 Table Column Datatypes
| The most common datatypes are VARCHAR2, NUMBER, and DATE. These types handle basic column types for character strings, numbers , and columns with a time dimension. The examples in this text, including the data model in Chapter 4, use these three types. Oracle provides many other datatypes with powerful features. Some datatypes are supplemented with built-in functions and packages. Built-in functions are available to convert a DATE between time zones. Large objects, such as CLOB and BLOB types, are supported with a PL/SQL built-in package, DBMS_LOB which enables the manipulation of 4-gigabyte objects. The following lists the datatypes available for column definitions in a table. 1.5.1 Character
The VARCHAR2 is the general string type used to store strings of up to 4,000 characters . A field of this type is, internally, a variable length string. It only uses the minimum necessary bytes ”no padding. The parameter n is required. For the column STUDENT_NAME, in the STUDENTS table, we can store a name up to 30 characters. Oracle version 6 and earlier versions used a VARCHAR that was a fixed length character field. Oracle introduced this variable length and more space proficient datatype in version 7 and called it VARCHAR2. VARCHAR2 is the basic string type for an Oracle table column.
This type is used to store a fixed-length string. Inserts will right-pad blanks to the value if the length of the inserted value is less than the length of the CHAR declaration ”this impacts programmers writing SQL with WHERE clauses who do not expect column values to be padded . This datatype is rare.
These types are used only to store Unicode (unified encoding) character data, which is applicable for Oracle applications that support multiple languages. 1.5.2 Number
This is a column type used for storing numbers. Do not use precision parameters for storing values of unknown range. For the following, we can store a number with virtually any magnitude in the column AMOUNT. CREATE TABLE TEMP (AMOUNT NUMBER); INSERT INTO TEMP VALUES (0.12345); INSERT INTO TEMP VALUES (12345.1209); The parameter "a" represents precision, the total number of placeholders. The parameter "b" represents scale, places to the right of the decimal point. So if your field's domain is the age of a person then you would use the following to store any three-digit whole number: CREATE TABLE TEMP (age NUMBER(3)); Should you be storing stock prices with a maximum of a million dollars (seven places to the left of the decimal point) and four places to the right of the decimal point, you would use: CREATE TABLE TEMP (PRICE NUMBER(11,4)); Scale is optional and, if not used, the values are stored as whole numbers. Fractions, when inserted, are rounded to the nearest whole number. To specify a scale only, use: CREATE TABLE TEMP (PRICE NUMBER(*,4)); 1.5.3 DATE Types
The DATE type stores date and time ”some databases separate this into two types, a type for the date and a type for a time. For Oracle, the DATE type is a calendar date and time ”all in one. In some applications the time of day is irrelevant ”just the date, hence an application might choose to not show the time but it is always there. There are numerous built-in functions on date types that PL/SQL can perform, such as computations to determine the last day of the previous month. DATE arithmetic is also supported. You can easily build on existing date functions and create a PL/SQL library of date operations tailored to your application. The default display of a DATE column is DD-MON-YY. This default display can be changed with an ALTER SESSION command. To display DATE values showing hours, minutes, seconds: ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYYhh24:MI:SS';
The TIMESTAMP data type is derived from the DATA type. It represents time with the same precision as DATE including a fractional second's field. The TIMESTAMP format is: 28-JUL-03 01.25.56.000122 PM TIMESTAMP can be declared with an optional parameter. The parameter "a" represents the number of digits included in the fractional part of the timestamp.
TIMESTAMPS can incorporate time zones. Applications spread over multiple time zones are likely to use date columns of this type. The INTERVAL type represents a time difference to a particular significance. The following creates a table with columns of each type. CREATE TABLE times (d1 TIMESTAMP WITH TIME ZONE, d2 TIMESTAMP WITH LOCAL TIME ZONE, d3 INTERVAL DAY TO SECOND, d4 INTERVAL YEAR TO MONTH); 1.5.4 Large Objects
Use a CLOB to store large-character objects. The limit is four gigabytes. A VARCHAR2 is limited to 4,000 characters. Character-based columns needing more than four gigabytes should be CLOBs. Use the BLOB to store four gigabytes of binary data. A student writing sample can be a column in the student table. If writing samples are documents that exceed 4,000 characters they are CLOB-type columns. The table and column are created with the following: CREATE TABLE student_writting_samples (student_id VARCHAR2(10), writing_sample CLOB)); Manipulation of the CLOB is through a CLOB/BLOB API. This API is the Oracle built-in package DBMS_LOB. An application manipulates the object by first selecting the CLOB into a PL/SQL object. The CLOB/BLOB is a private type. Operations are restricted to the API. The following PL/SQL script prints the length of the student writing sample. DECLARE v_writing_sample CLOB; the_length INTEGER; BEGIN SELECT writing_sample INTO v_writing_sample FROM student_writing_samples WHERE student_id = 'A101'; dbms_lob.open(v_writing_sample, dbms_lob.lob_readonly); the_length := dbms_lob.getlength(v_writing_sample); dbms_lob.close(v_writing_sample); dbms_output.put_line(the_length); END; BLOB is a four-gigabyte object that is intended for storing binary images. BLOB objects are manipulated using the built-in package DBMS_LOB. NCLOB types store four-gigabyte Unicode character objects using the National Character set.
An object need not physically be stored in a column. Rather, a column can act as a reference to an object. Such an object would be a file on the host disk. A student writing sample can be stored completely in a column as a CLOB. Alternatively, a writing sample can remain on a file server, yet be accessible through SQL. This requires that there be a BFILE column type. The BFILE acts as a reference to the external file. The external file content can be accessed using the DBMS_LOB API. The following creates a table for student writing samples and inserts an external file reference. The BFILE column is just a pointer to the file JOHN.DOC that physically resides in the directory D:\STUDENT_FILES. The creation of the Oracle directory requires the CREATE ANY DIRECTORY privilege. CREATE OR REPLACE DIRECTORY STUDENT_FILES AS 'D:\student_files'; CREATE TABLE student_writing_samples (student_id VARCHAR2(10), writing_sample BFILE); INSERT INTO student_writing_samples (student_id, writing_sample) VALUES ('A101',bfilename('STUDENT_FILES','John.doc')); BFILE objects are manipulated using the DNMS_LOB API. To print the length of the writing sample: DECLARE v_writing_sample BFILE; the_length INTEGER; BEGIN SELECT writing_sample INTO v_writing_sample FROM student_writing_samples WHERE student_id = 'A101'; dbms_lob.fileopen (file_loc => v_writing_sample, open_mode => dbms_lob.file_readonly); the_length := dbms_lob.getlength(v_writing_sample); dbms_lob.fileclose(file_loc => v_writing_sample); dbms_output.put_line(the_length); END; 1.5.5 XML Type
The XMLTYPE was released with Oracle 9 i Release 2. This type is used to store XML documents as attributes to an entity. For example, student transcripts can be stored as XML documents. This document would be an attribute of the student and exist in a STUDENTS table as a single column. The table for a student and transcript is: CREATE TABLE student_transcripts (student_id VARCHAR2(10), transcript SYS.XMLTYPE); The XMLType has built-in methods . The following statement inserts a transcript for a student with two classes. INSERT INTO student_transcripts (student_id, transcript) VALUES ('A101', sys.XMLTYPE.createXML('<STUDENT_TRANSCRIPT>' '<CLASS>' '<COURSE>Math 101</COURSE>' '<GRADE>A</GRADE>' '</CLASS>' '<CLASS>' '<COURSE>English 102</COURSE>' '<GRADE>A</GRADE>' '</CLASS>' '</STUDENT_TRANSCRIPT>')); The following SELECT statement returns the student and transcript record. set long 10000 SELECT student_id, p.transcript.getClobVal() FROM student_transcripts p; XML documents can be read from a host file and loaded into a CLOB-type column. It can then be navigated in PL/SQL using the XML built-in packages that fully support the Document Object Model (DOM) API for XML documents. 1.5.6 LONG and RAW Types
Oracle introduced support for large objects in version 8. Prior to this release, strings that exceeded the bounds of a VARCHAR2 type could be manipulated, with limited functionality, using a LONG datatype. Strings that exceed the limits of a VARCHAR2 should now be manipulated as a CLOB using the built-in DBMS_LOB package.
Similar to the LONG type, the RAW and LONG RAW datatypes served a useful purpose prior to LOB support with Oracle 8. The RAW and LONG RAW types were used for manipulating binary data ”the Oracle built-in package, DBMS_LOB, provides advanced support for manipulating binary data. 1.5.7 ROWID Type
A ROWID contains composite information that allows Oracle to identify, form a single ROWID value, where a specific row in a table is located (i.e., the tablespace, file, database block, and specific location within that block). You can declare a table with a column of type ROWID, populate that table with ROWID values, and extract information about the value using the Oracle built-in package DBMS_ROWID. The UROWID is a universal ROWID structure that supports non-Oracle tables. |
EAN: 2147483647
Pages: 111