Data types in Oracle

Data types in Oracle

/*data types in ORACLE; char length fixed range: 1-2000 VARCHAR2 length variable range: 1-4000 long length variable maximum range 2gb longer character type numb ER numeric number (p,s) date type, accurate to seconds TIMESTAMP storage date, time, timezone, wonderful value, accurate to decimal CLOB character data BLOB storing binary data, video picture etc. BF   ILE: Used to store binary data in operating system files outside the database the so-called fixed length: the so-called fixed length: refers to the value of the field is less than the limit of the length of the field, but the actual storage of data, will be automatically the right to fill the space before the contents of the field value is stored in the data block. This approach is a waste of space, but storage efficiency is better than variable length types.         It also reduces the migration of data rows to occur.   Variable length: means that when the input field value is less than the limit length of the field, the contents of the field value are stored directly in the data block without padding, which saves block space. */--stored bytes or characters?  CREATE TABLET1 (NAMECHAR(4)--The default is byte ); INSERT  intoT1VALUES('AB'); INSERT  intoT1VALUES('ABCD'); INSERT  intoT1VALUES('I love China');--This will be an error .  INSERT  intoT1VALUES('I love');--This is the right drop .    SELECT *  fromT1; SELECTLENGTH (LTrim(RTrim(NAME))) fromT1WHERENAME='AB' --2  SELECTLENGTH (NAME) fromT1WHERENAME='AB' --4     SELECTNAME fromT1WHERENAME=N'AB';--no value drops;       SELECTNAME fromT1WHERENAME='AB'; --the same comparison;   CREATE TABLET2 (NAMECHAR(4 CHAR)--The default is byte ); INSERT  intoT2VALUES('ABCD'); INSERT  intoT2VALUES('ABCDABCD');--Error  INSERT  intoT2VALUES('I love China');----Normal insertion    SELECT *  fromT2; --A Chinese character occupies a few characters, the specific way to see the encoding  /*the Nls_characterset of the database is Al32utf8, that is, a Chinese character occupies three to four bytes.  If Nls_characterset is ZHS16GBK, a character occupies two bytes. */--NCHAR--note that n begins with a data drop of Unicode character type/*This is a fixed-length string that contains data in Unicode format. The nchar field can store up to 2,000 bytes of information. Its maximum length depends on the national character set. In addition to the query, if the field is nchar type, you need to write the Select translated_description from Product_descriptionswhere translated_name = N ' LCD Monitor 11/pm ';*/ CREATE TABLEN1 (NAMENCHAR(4)--The default is byte ); INSERT  intoN1VALUES('AB'); INSERT  intoN1VALUES('ABCD'); INSERT  intoN1VALUES('I love China');--Insert Successful  INSERT  intoN1VALUES('I love');--Insert Successful    SELECT *  fromN1;/*N here means Unicode, which is a double-byte character.  For Western characters, it is enough to be stored in one byte, and two bytes is required for the oriental character. Unicode is intended to be uniform, canonical, convenient, and compatible, and the western characters are also stored in two bytes. */--N can't add a drop  SELECT *  fromN1WHERENAME=N'I love';--in this case, you must add two spaces to find the value of the drop;    SELECT *  fromN1WHERENAME='I love'; SELECT *  fromN1WHERENAME='AB'; SELECT *  fromN1WHERENAME=N'AB'; SELECTLENGTH ('AB') fromDUAL;--2    SELECTLENGTH ('AB') fromDUAL;--4    SELECTLENGTH (NAME) fromN1WHERENAME='AB';--Length of 4/*do not use the varchar data type. Use the VARCHAR2 data type. Although the varchar data type is currently synonymous with VARCHAR2, the varchar data type has different comparison semantics compared to a single data type used for variable-length strings. The 1.4:VARCHAR2 type has a variable length string, and unlike the char type, it does not use spaces to fill the maximum length. VARCHAR2 can store up to 4,000 bytes of information. 1.5:NVARCHAR2 type This is a variable-length string that contains data in Unicode format. NVARCHAR2 can store up to 4,000 bytes of information. */DECLAREIINT:=1; JVARCHAR2( -) :='1'; BEGINDbms_output.put_line (I||J);--The result is one   END; DECLAREIINT:=1; JVARCHAR2( -) :='1'; BEGINDbms_output.put_line (I+J);--results: 2, there is a problem with privacy conversion and display conversion drop it.   END; DECLAREIVARCHAR2( -) :='1'; JVARCHAR2( -) :='1'; BEGINDbms_output.put_line (I+J);--and the result is our 2 key. Look, we're using symbols.   END; DECLAREIINT:=1; JINT:=1; BEGINDbms_output.put_line (I||J);--The result is 11, + is used to add and subtract between numeric types, | | is used to add and subtract between strings;                                       --This is the key to the problem.   END; --conversion of the time format drop it!SELECTSysdate fromDUAL;--The default format is this: 2016/6/22 16:12:07--then use our to_char to make our time-format conversion drops: 2016-06-22 04:06:02SELECTTo_char (Sysdate,'YYYY-MM-DD HH:MM:SS') fromDUAL;--converts a numeric type to a string type;--force display as local currency typeSELECTTo_char ( -,'l99,999.99') fromDUAL;SELECTTo_char (SAL,'$00,000,00') fromEMP;

Add some more here;

-- Here is also the value of our number data type; -- Integer is the subtype of number, which is equivalent to number (38,0), which is used to store integers. If the inserted and updated values have decimals, they are rounded.  --float type is also a subtype of number */*
It stores variable-length strings up to 2G of character data (2GB refers to 2000 megabytes instead of 2000 megabytes), and as with VARCHAR2 or char types, text stored in a long type is converted to character set. Oracle recommends using CLOB to replace the long type in development. Long columns are supported only for backward compatibility. The CLOB type is much less restrictive than the long type. The limit for the long type is as follows: 1. Only one column in a table can be a long type. (Why? some unclear white) The 2.LONG column cannot be defined as a primary key or a unique constraint, 3. Cannot build index 4. The Long data cannot specify a regular expression. 5. A function or stored procedure cannot accept parameters of the Long data type. 6.LONG columns cannot appear in a WHERE clause or integrity constraint (except that NULL and NOT NULL constraints may occur)


*/

Data types in Oracle