PL/SQL Data Types

واضح آرشیو وب فارسی:سایت ریسک: View Full Version : PL/SQL Data Types Vahed24-10-2007, 01:02 PMLets discuss data types used with PL/SQL. These types should not be confused with database types. In most cases, capabilities and limitations between database and PL/SQL types are identical, but some have dramatically different storage capabilities that can pop up to bite you. PL/SQL data types can be broken down into the following categories: ¦ Scalar ¦ Reference ¦ Composite ¦ LOB The Scalar category is broken down further into subcategories, or families of types. The next few sections discuss each category, subcategory, and PL/SQL data type. Scalar A Scalartype is a data type that holds a single value. Scalar types can be broken down into subcategories, or families, that include ¦ Character/String ¦ Number ¦ Boolean ¦ Date/Time We’ll review each of these in detail in the following sections . Character/String PL/SQL character or string types include everything from single character values to large strings up to 32K in size. These types can store letters, numbers, and binary data, and they can store any character supported by the database character set. They all define their precision as an integer with units in bytes (the default setting of bytes can be changed, as you will see in the next section on “Character Semantics”) at the time the variable is declared. Character Semantics Character/stringtypes define precision, or storage, with an integer. The number provided actually specifies the number of bytes allowed rather than the number of characters. Prior to Oracle 9i, this was a real problem when working with multibyte characters. It was possible that a variable precision of 2 could not even handle a single three-byte Asian character. Oracle introduced character semantics in 9i to solve this problem. Character semantics can be specified for the system using the NLS_LENGTH_ SEMANTICS init.ora parameter, or for each variable in the declaration. The following example shows a normal declaration of a variable of type VARCHAR2: DECLARE v_string VARCHAR2(10); By default, this declaration means that the variable v_string can store up to ten bytes. Here we modified the declaration to use character semantics: 5pt; COLOR: #231f20; FONT-FAMILY: Courier; mso-bidi-font-family: Courier">DECLARE v_string VARCHAR2(10 CHAR); The addition of the CHAR to the precision means that the v_string variable will now store up to ten characters, regardless of the number of bytes per character. Character/String Types Type Description CHAR Fixed-length character data type. The precision is specified as an integer. Storage is in bytes rather than characters by default. Use character semantics to override. LONG The LONG PL/SQL type is different than the LONG database type. It is a variable-length type with a limit of 32K (32,760 bytes). It is possible for a column of type LONG to not fit in a variable of type LONG. Because of the difference between the PL/SQL and database types, use of the LONG PL/SQL type is limited. LONG RAW LONG RAW holds binary data up to 32K (32,760 bytes). Just like the LONG type, the LONG RAW differs in size restriction between PL/SQL type and database type. It is possible for a column of type LONG RAW to not fit in a variable of type LONG RAW. Because of the difference between the PL/SQL and database types, use of the LONG RAW PL/SQL type is limited. Type Description NCHAR NCHAR holds fixed-length national character data. It is identical to the CHAR type but takes on the character set specified by the National Character Set. NVARCHAR2 NVARCHAR2 holds variable-length character data. It is identical to the VARCHAR2 type, but takes on the character set specified by the National Character Set. RAW The RAW type stores fixed-length binary data and can hold up to 32K (32,760 bytes). The RAW type for the database can hold only 2K, so the problem is the opposite of that experienced with the LONG and LONG RAW types. If a RAW variable holds more than 2K, it cannot insert it into the database column of the same type. ROWID Every record in a table contains a unique binary value called a ROWID. The rowid is an identifier of the row in the table. The PL/SQL type is the same and stores the ROWID of a database record without conversion to a character type. The ROWID type supports physical rowids, but not logical rowids. UROWID UROWID supports both physical and logical rowids. Oracle recommends using the UROWID PL/SQL type when possible. VARCHAR VARCHAR is an ANSI-standard SQL type, synonymous with VARCHAR2. Oracle recommends VARCHAR2 be used to protect against future modifications to VARCHAR impacting code. VARCHAR2 The VARCHAR2 PL/SQL data type can store up to 32K (32,767) bytes in Oracle 10 g. The database VARCHAR2 type can store only 4K. This can be a problem, of course, when a variable of type VARCHAR2 holds data that exceeds 4K and attempts to insert the contents into a database column of type VARCHAR2. IT Articles 2007 سایت ما را در گوگل محبوب کنید با کلیک روی دکمه ای که در سمت چپ این منو با عنوان +1 قرار داده شده شما به این سایت مهر تأیید میزنید و به دوستانتان در صفحه جستجوی گوگل دیدن این سایت را پیشنهاد میکنید که این امر خود باعث افزایش رتبه سایت در گوگل میشود

این صفحه را در گوگل محبوب کنید

[ارسال شده از: سایت ریسک]

[مشاهده در: www.ri3k.eu]

[تعداد بازديد از اين مطلب: 407]