Be <\/span>
\nGovt. Certified PL SQL Developer
\n<\/span><\/a><\/p>\nPL\/SQL LEXICAL ELEMENTS<\/span>
\n<\/strong><\/p>\nA lexical element refers to a character or groupings of characters that may legally appear in a source file. Basic lexical elements are<\/p>\n
\n- Tokens<\/li>\n
- Source program character set<\/li>\n
- Comments<\/li>\n<\/ul>\n
PL\/SQL DATATYPES<\/strong><\/span><\/p>\nEvery constant, variable, and parameter has a datatype (or type), which specifies a storage format, constraints, and valid range of values. PL\/SQL provides a variety of predefined datatypes. For instance, you can choose from integer, floating point, character, Boolean, date, collection, reference, and LOB types. In addition, PL\/SQL lets you define your own subtypes. This chapter covers the basic types used frequently in PL\/SQL programs. Later chapters cover the more specialized types.<\/p>\n
The datatypes are:<\/p>\n
\n- Predefined Datatypes<\/li>\n
- User-Defined Subtypes<\/li>\n
- Datatype Conversion<\/li>\n<\/ul>\n
Predefined Datatypes<\/span><\/p>\nA scalar type has no internal components. A composite type has internal components that can be manipulated individually. A reference type holds values, called pointers, that designate other program items. A LOB type holds values, called lob locators, that specify the location of large objects (graphic images for example) stored out-of-line.<\/p>\n
A lexical element refers to or groupings of characters that may legally appear in file. Basic lexical elements are<\/p>\n
\n- Tokens<\/li>\n
- Source program character set<\/li>\n
- Comments<\/li>\n<\/ul>\n
PL\/SQL DATATYPES<\/strong><\/span><\/p>\nEvery constant, variable, and parameter has (or type), which specifies format, constraints, and valid range of values. PL\/SQL provides of predefined datatypes. For instance, you can choose from integer, floating point, character, Boolean, date, collection, reference, and LOB types. In addition, PL\/SQL lets you define your own subtypes. This chapter covers the basic types used frequently in PL\/SQL programs. Later chapters cover the more specialized types.<\/p>\n
The datatypes are:<\/p>\n
\n- Predefined Datatypes<\/li>\n
- User-Defined Subtypes<\/li>\n
- Datatype Conversion<\/li>\n<\/ul>\n
Predefined Datatypes<\/span><\/p>\nA scalar type has no internal components. A composite type has internal components that can be manipulated individually. A reference type holds values, called pointers, that designate other program items. A LOB type holds values, called lob locators, that specify the location of large objects (graphic images for example) stored out-of-line.<\/p>\n
<\/p>\n
A lexical element refers to or groupings of characters that may legally appear in file. Basic lexical elements are<\/p>\n
\n- Tokens<\/li>\n
- Source program character set<\/li>\n
- Comments<\/li>\n<\/ul>\n
PL\/SQL DATATYPES<\/strong><\/span><\/p>\nEvery constant, variable, and parameter has (or type), which specifies format, constraints, and valid range of values. PL\/SQL provides of predefined datatypes. For instance, you can choose from integer, floating point, character, Boolean, date, collection, reference, and LOB types. In addition, PL\/SQL lets you define your own subtypes. This chapter covers the basic types used frequently in PL\/SQL programs. Later chapters cover the more specialized types.<\/p>\n
The datatypes are:<\/p>\n
\n- Predefined Datatypes<\/li>\n
- User-Defined Subtypes<\/li>\n
- Datatype Conversion<\/li>\n<\/ul>\n
Predefined Datatypes<\/span><\/p>\nA scalar type has no internal components. A composite type has internal components that can be manipulated individually. A reference type holds values, called pointers, that designate other program items. A LOB type holds values, called lob locators, that specify the location of large objects (graphic images for example) stored out-of-line.<\/p>\n
<\/p>\n
Number Types<\/h3>\n
Number types let you store numeric data (integers, real numbers, and floating-point numbers), represent quantities, and do calculations.<\/p>\n
BINARY_INTEGER<\/h4>\n
You use the BINARY_INTEGER<\/code> datatype to store signed integers. Its magnitude range is -2**31 .. 2**31. Like PLS_INTEGER<\/code> values, BINARY_INTEGER<\/code> values require less storage than NUMBER<\/code> values. However, most BINARY_INTEGER<\/code> operations are slower than PLS_INTEGER<\/code> operations.<\/p>\nBINARY_INTEGER Subtypes<\/span><\/h5>\nA base type<\/em> is the datatype from which is derived. A subtype<\/em> associates type with and so defines of values. For your convenience, PL\/SQL predefines the following BINARY_INTEGER<\/code> subtypes:<\/p>\n\nNATURAL<\/code><\/dd>\nNATURALN<\/code><\/dd>\nPOSITIVE<\/code><\/dd>\nPOSITIVEN<\/code><\/dd>\nSIGNTYPE<\/code><\/p>\n<\/pre>\n <\/dd>\n<\/dl>\n
The subtypes NATURAL<\/code> and POSITIVE<\/code> let you restrict an integer variable to non-negative or positive values, respectively. NATURALN<\/code> and POSITIVEN<\/code> prevent the assigning of nulls to an integer variable. SIGNTYPE<\/code> lets you restrict an integer variable to the values -1, 0, and 1, which is useful in programming tri-state logic.<\/p>\nNUMBER<\/h4>\n
You use the NUMBER<\/code> datatype to store fixed-point or floating-point numbers. Its magnitude range is 1E-130 .. 10E125. If the value of an expression falls outside this range, you get >numeric overflow or underflow error. You can specify precision<\/em>, which is the total number of digits, and scale<\/em>, which is the number of digits to the right of the decimal point. The syntax follows:<\/p>\nNUMBER[(precision,scale)]<\/pre>\nTo declare fixed-point numbers, for which you must specify scale<\/em>, use the following form:<\/p>\nNUMBER(precision,scale)<\/pre>\nTo declare floating-point numbers, for which you cannot specify precision<\/em> or scale<\/em> because the decimal point can “float” to any position, use the following form:<\/p>\nNUMBER<\/pre>\nTo declare integers, which have no decimal point, use this form:<\/p>\n
NUMBER(precision<\/code><\/em>) -- same as NUMBER(precision<\/code><\/em>,0)<\/pre>\nYou cannot use constants or variables to specify precision<\/em> and scale<\/em>; you must use integer literals. The maximum precision of >NUMBER value is 38 decimal digits. If you do not specify precision<\/em>, it defaults to 38 or the maximum supported by your system, whichever is less.<\/p>\nScale, which can range from -84 to 127, determines where rounding occurs. For instance, of 2 rounds to the nearest hundredth (3.456 becomes 3.46). A negative scale rounds to the left of the decimal point. For example, of -3 rounds to the nearest thousand (3456 becomes 3000). A scale of 0 rounds to the nearest whole number. If you do not specify scale<\/em>, it defaults to 0.<\/p>\nNUMBER Subtypes<\/span><\/h5>\nYou can use the following NUMBER<\/code> subtypes for compatibility with ANSI\/ISO and IBM types or when you want descriptive name:<\/p>\n\nDEC<\/code><\/dd>\nDECIMAL<\/code><\/dd>\nDOUBLE<\/code> PRECISION<\/code><\/dd>\nFLOAT<\/code><\/dd>\nINTEGER<\/code><\/dd>\nINT<\/code><\/dd>\nNUMERIC<\/code><\/dd>\nREAL<\/code><\/dd>\nSMALLINT<\/code><\/p>\n<\/pre>\n <\/dd>\n<\/dl>\n
Use the subtypes DEC<\/code>, DECIMAL<\/code>, and NUMERIC<\/code> to declare fixed-point numbers with precision of 38 decimal digits. Use the subtypes DOUBLE<\/code> PRECISION<\/code> and FLOAT<\/code> to declare floating-point numbers with precision of 126 binary digits, which is roughly equivalent to 38 decimal digits. Or, use the subtype REAL to declare floating-point numbers with precision of 63 binary digits, which is roughly equivalent to 18 decimal digits. Use the subtypes INTEGER<\/code>, INT<\/code>, and SMALLINT<\/code> to declare integers with precision of 38 decimal digits.<\/p>\nPLS_INTEGER<\/h4>\n
You use the PLS_INTEGER<\/code> datatype to store signed integers. Its magnitude range is -2**31 .. 2**31. PLS_INTEGER<\/code> values require less storage than NUMBER<\/code> values. Also, PLS_INTEGER<\/code> operations use machine arithmetic, so they are faster than NUMBER<\/code> and BINARY_INTEGER<\/code> operations, which use library arithmetic. For efficiency, use PLS_INTEGER<\/code> for all calculations that fall within its magnitude range. Although PLS_INTEGER<\/code> and BINARY_INTEGER<\/code> have the same magnitude range, they are not fully compatible. When >PLS_INTEGER calculation overflows, an exception is raised. However, when >BINARY_INTEGER calculation overflows, no exception is raised if the result is assigned to >NUMBER variable. Because of this small semantic difference, you might want to continue using BINARY_INTEGER<\/code> in old applications for compatibility. In new applications, always use PLS_INTEGER<\/code> for better performance.<\/p>\nCharacter Types<\/h3>\n
Character types let you store alphanumeric data, represent words and text, and manipulate character strings.<\/p>\n
CHAR<\/h4>\n
You use the CHAR<\/code> datatype to store fixed-length character data. How the data is represented internally depends on the database character set. The CHAR<\/code> datatype takes an optional parameter that lets you specify size up to 32767 bytes. You can specify the size in terms of bytes or characters, where each character contains one or more bytes, depending on the character set encoding. The syntax follows:<\/p>\nCHAR[(maximum_size [CHAR | BYTE] )]<\/pre>\nYou cannot use constant or variable to specify the maximum size; you must use an integer literal in the range 1 .. 32767.<\/p>\n
If you do not specify size, it defaults to 1. If you specify the maximum size in bytes rather than characters, >CHAR(n) variable might be too small to hold n<\/code> multibyte characters. To avoid this possibility, use the notation CHAR(n CHAR)<\/code>so that the variable can hold n<\/code> characters in the database character set, even if some of those characters contain multiple bytes. When you specify the length in characters, the upper limit is still 32767 bytes. So for double-byte and multibyte character sets, you can only specify 1\/2 or 1\/3 as many characters as with -byte character set.<\/p>\nAlthough PL\/SQL character variables can be relatively long, the maximum width of >CHAR database column is 2000 bytes. So, you cannot insert CHAR<\/code> values longer than 2000 bytes into >CHAR database column.<\/p>\nYou can insert any CHAR(n)<\/code> value into >LONG database column because the maximum width of >LONG column is 2**31 bytes or two gigabytes. However, you cannot retrieve longer than 32767 bytes from >LONG column into >CHAR(n) variable.<\/p>\nWhen you do not use the CHAR<\/code> or BYTE<\/code> qualifiers, the default is determined by the setting of the NLS_LENGTH_SEMANTICS<\/code> initialization parameter. When \/SQL procedure is compiled, the setting of this parameter is recorded, so that the same setting is used when the procedure is recompiled after being invalidated.<\/p>\nNote:<\/strong> Semantic differences between the CHAR<\/code> and VARCHAR2<\/code> base types are discussed in Appendix B.<\/p>\nCHAR Subtype<\/span><\/h5>\nThe CHAR<\/code> subtype CHARACTER<\/code> has the same range of values as its base type. That is, CHARACTER<\/code> is just another name for CHAR<\/code>. You can use this subtype for compatibility with ANSI\/ISO and IBM types or when you want an identifier more descriptive than CHAR<\/code>.<\/p>\nLONG and LONG RAW<\/h4>\n
You use the LONG<\/code> datatype to store variable-length character strings. The LONG<\/code> datatype is like the VARCHAR2<\/code> datatype, except that the maximum size of >LONG value is 32760 bytes.<\/p>\nYou use the LONG<\/code> RAW<\/code> datatype to store binary data or byte strings. LONG<\/code> RAW<\/code> data is like LONG<\/code> data, except that LONG<\/code> RAW<\/code> data is not interpreted by PL\/SQL. The maximum size of >LONG RAW<\/code> value is 32760 bytes.<\/p>\nStarting in Oracle9i<\/em>, LOB variables can be used interchangeably with LONG<\/code> and LONG RAW<\/code> variables. Oracle recommends migrating any LONG<\/code> data to the CLOB<\/code> type, and any LONG RAW<\/code> data to the BLOB<\/code> type.<\/p>\nYou can insert any LONG<\/code> value into >LONG database column because the maximum width of >LONG column is 2**31 bytes. However, you cannot retrieve longer than 32760 bytes from >LONG column into >LONG variable.<\/p>\nLikewise, you can insert any LONG<\/code> RAW<\/code> value into >LONG RAW<\/code> database column because the maximum width of >LONG RAW<\/code> column is 2**31 bytes. However, you cannot retrieve longer than 32760 bytes from >LONG RAW<\/code> column into >LONG RAW<\/code> variable.<\/p>\nLONG<\/code> columns can store text, arrays of characters, or even short documents. You can reference LONG<\/code> columns in UPDATE<\/code>, INSERT<\/code>, and (most) SELECT<\/code> statements, but not<\/em> in expressions, SQL function calls, or certain SQL clauses such as WHERE<\/code>, GROUP<\/code> BY<\/code>, and CONNECT<\/code> BY<\/code>.<\/p>\nNote:<\/strong> In SQL statements, PL\/SQL binds LONG<\/code> values as VARCHAR2<\/code>, not as LONG<\/code>. However, if the length of the bound VARCHAR2<\/code> exceeds the maximum width of >VARCHAR2 column (4000 bytes), Oracle converts the bind type to LONG<\/code> automatically, then issues an error message because you cannot pass LONG<\/code> values to function.<\/p>\nRAW<\/h4>\n
You use the RAW<\/code> datatype to store binary data or byte strings. For example, >RAW variable might store of graphics characters or picture. Raw data is like VARCHAR2<\/code> data, except that PL\/SQL does not interpret raw data. Likewise, Oracle Net does no character set conversions when you transmit raw data from one system to another.<\/p>\nThe RAW<\/code> datatype takes parameter that lets you specify size up to 32767 bytes. The syntax follows:<\/p>\nRAW(maximum_size)<\/pre>\nYou cannot use constant or variable to specify the maximum size; you must use an integer literal in the range 1 .. 32767.<\/p>\n
The maximum width of >RAW database column is 2000 bytes. So, you cannot insert RAW<\/code> values longer than 2000 bytes into >RAW column. You can insert any RAW<\/code> value into >LONG RAW<\/code> database column because the maximum width of >LONG RAW<\/code> column is 2**31 bytes. However, you cannot retrieve longer than 32767 bytes from >LONG RAW<\/code> column into >RAW variable.<\/p>\nROWID and UROWID<\/h4>\n
Internally, every database table has >ROWID pseudocolumn, which stores binary values called rowids<\/strong>. Each rowid represents the storage address of . A physical rowid<\/strong> identifies in an ordinary table. A logical rowid<\/strong> identifies in an index-organized table. The ROWID<\/code> datatype can store only physical rowids. However, the UROWID<\/code> (universal rowid) datatype can store physical, logical, or foreign (non-Oracle) rowids.<\/p>\nSuggestion:<\/strong> Use the ROWID<\/code> datatype only for backward compatibility with old applications. For new applications, use the UROWID<\/code> datatype.<\/p>\nWhen you select or fetch into >ROWID variable, you can use the built-in function ROWIDTOCHAR<\/code>, which converts the binary value into an 18-byte character string. Conversely, the function CHARTOROWID<\/code> converts >ROWID character string into . If the conversion fails because the character string does not represent rowid, PL\/SQL raises the predefined exception SYS_INVALID_ROWID<\/code>. This also applies to implicit conversions.<\/p>\nTo convert between UROWID<\/code> variables and character strings, use regular assignment statements without any function call. The values are implicitly converted between UROWID<\/code> and character types.<\/p>\nPhysical Rowids<\/span><\/h5>\nPhysical rowids provide fast access to particular rows. As long as the row exists, its physical rowid does not change. Efficient and stable, physical rowids are useful for selecting of rows, operating on the whole set, and then updating . For example, you can compare >UROWID variable with the ROWID<\/code> pseudocolumn in the WHERE<\/code> clause of an UPDATE<\/code> or DELETE<\/code> statement to identify the latest row fetched from .<\/p>\nA physical rowid can have either of two formats. The 10-byte extended rowid<\/em> format supports tablespace-relative block addresses and can identify rows in partitioned and non-partitioned tables. The 6-byte restricted rowid<\/em> format is provided for backward compatibility.<\/p>\nExtended rowids use -64 encoding of the physical address for each row selected. For example, in SQL*Plus (which implicitly converts rowids into character strings), the query<\/p>\n
SQL> SELECT rowid, ename FROM emp WHERE empno = 7788;<\/pre>\n