Learning from Oracle Database 1

Common basic types of 1.oracle databases

CHAR (Ten)--store a fixed-length string

VARCHAR2 (Ten)--storing variable-length strings

Date

integer-to-store integers

Number (p,s)-->p the maximum number of digits that can be saved to the database, including the integer and fractional parts of the decimal point, and the number of digits reserved by S decimals

Example: Numbers that are actually stored by the number entered

Number 1234.567 1234.567

Number (6,2) 1234.567 1234.56

Number (6,2) 12345.67 the length of the input exceeds the maximum number of digits stored, error

2. Large object LOB can store 128TB of data

1) LOB has the following 4 types

CLOB, character lob type, for storing character data

Nclob--> national language Character set LOB type, for storing non-English characters

BLOB-to-binary LOB type for storing binary data

bfile--> binary file type for storing file pointers. The text itself does not exist in the database, and the database stores only a pointer to the file.

2) How to use?

Creating table: CREATE TABLE Test (ID integer primary key,

Clob_col clob not NULL);

Insert data: INSERT INTO Test (ID,CLOB_COL) VALUES (1, to_clob (' Form day to day ... '));

Modify data: Update Test Set clob_col = To_clob (' What's it ... ')

Initialize Clob:insert into Test (Id,clob_col) VALUES (1, Empty_clob ());

When using bfile, you need to create a directory that represents the directory where the file is stored in the file system.

Create directory Test_file as ' C:\windows\file\ ';

Populate bfile Columns: INSERT INTO Test (ID,BFILE_COL) VALUES (1, bfilename (' Test_file ', ' textcontent.txt '));

However, when using large objects in PL/SQL, you can use the Dbms_lob package that comes with the Oracle database.

Learning from Oracle Database 1