Oracle's BLOB field is special, and he performs much better than a long field, and can be used to save binary data such as a sample.
Writing BLOB fields and writing to other types of fields is very different because the BLOB itself has a cursor, you must manipulate the BLOB with the cursor, so you must obtain the cursor before writing to the Blob, So how do I get the blob cursor? This requires you to insert an empty BLOB first, which will create a blob of the cursor, and then you can use this empty BLOB cursor with a select query, so that in two steps, you get the BLOB cursor, Can actually write BLOB data. Examples are: Oracle Series: LOB large object processing is primarily used to store large amounts of data
Databasefield, which can store up to 4G bytes of unstructured data. Mainly introduces the character type and the storage of the binary file type LOB data, separately describes the storage of the binary type LOB data.
One, the LOB data type in Oracle is categorized as 1, divided by the type of data stored: ① character type: CLOB: Stores large amounts of single-byte character data. Nlob: Stores fixed-width multibyte character data. ② binary type: BLOB: Stores large unstructured binary data. ③ binary file type: BFILE: Stores the binaries in the operating system files outside the database. The file path is stored.
2, by storage: ① stored in the internal tablespace: Clob,nlob and Blob② point to external operating system files: BFILE Two, large object data entry 1, declaring LOB type column/* conn Scott/tiger; Create tablespace ts5_21 datafile ' E:\Oracle\ts5_21.dbf ' Size 5m; */Create Table Tlob (no number (4), name VarChar2 (Ten), resume CLob, photo BLob, record BFile) Lob (Resume,photo) Store as (Tablespace ts5_21--Specify the stored tablespace Chunk 6k--Specify the data block size Disable Storage in Row); 2, insert large object column ① insert normal column data first
② inserts a blank constructor when a large object column is encountered. Character type: Empty_clob (), Empty_nclob () binary: Empty_blob () binary file type: The Bfilename function points to an external file. Bfilename function: Bfilename (' Logical directory name ', ' filename '); Logical directory names can only be capitalized, because the data dictionary is stored in uppercase. Oracle is case-sensitive. At the time of creation, it is not necessary to point the Bfilename function logical directory to the physical path, but to check if both are associated. Example: Insert into Tlob Values (1, ' Gene ', Empty_clob (), Empty_blob (), Bfilename (' MYDIR ', ' img_0210.jpg '));
③ associates a logical directory with a physical directory. Grant Create any directory permissions (if it is a binary file type) for grant create any directory to user name with ADMIN OPTION; Associate logical directory and physical directory local CREATE Directo Ry logical directory name as ' file's physical directory '; network: Create directory logical directory name as ' \ \ hostname (IP) \ shared directory '; example: Create directory MYDIR as ' E:\Oracle ';
Insert Example: INSERT into Tlob values (1, ' Gene ', ' clob large Object column ', Empty_blob (), Bfilename (' MYDIR ', ' img_0210.jpg ')); Three, large object data reading and manipulation: Dbms_lob Package Dbms_lob Package: Contains procedures and functions for handling large objects/* INSERT INTO TLOB values (1, ' Gene ', ' clob large Object column ', Empty_blob (), Bfilename (' MYDIR ', ' img_0210.jpg ')); INSERT into Tlob values (2, ' Jack ', ' clob large Object column ', Empty_blob (), Bfilename (' MYDIR ', ' img_0210.jpg ')); INSERT into Tlob values (3, ' Mary ', ' Big object column Clob ', Empty_blob (), Bfilename (' MYDIR ', ' img_0210.jpg ')); */1, the process of reading large object data and function ①:dbms_lob. Read (): The process of reading the specified length of data from the LOB data into the buffer. Dbms_lob. Read (LOB data, specify length, start location, store return LOB type value variable); Example: Declare Varc clob; Vrstr VARCHAR2 (1000); ln number (4); STRT number (4); Begin Select Resume into Varc from tlob where no = 1; ln: = Dbms_lob. GetLength (VARC); STRT: = 1; Dbms_lob. Read (Varc, Ln, STRT, VRSTR); Dbms_output.put_line (' Return: ' | | VRSTR); End;
②:dbms_lob. SUBSTR (): a function that extracts substrings from LOB data. Dbms_lob. SUBSTR (LOB data, specifying extract length, extracting starting position): Example: Declare Varc clob; Vrstr VARCHAR2 (1000); ln number (4); STRT number (4); Begin Select Resume into Varc from tlob where no = 1; ln: = 4; STRT: = 1; VRSTR: = Dbms_lob. SUBSTR (Varc, Ln, STRT); Dbms_output.put_line (' Result: ' | | VRSTR); End;
③:dbms_lob. INSTR (): a function that looks up the position of a substring from the LOB data. Dbms_lob. INSTR (LOB data, substring); Example: Declare Varc clob; Vsubstr VARCHAR2 (1000); Vrstr VARCHAR2 (1000); ln number (4); Begin Select Resume into Varc from tlob where no = 1; Vsubstr: = ' large object '; ln: = Dbms_lob. INSTR (VARC,VSUBSTR); Dbms_output.put_line (' Position: ' | | LN);
VRSTR: = Dbms_lob. SubStr (Varc, Length (vsubstr), LN); Dbms_output.put_line (' position is ' | | ln| | ' Length is ' | | Length (VSUBSTR) | | The substring of the string is: ' | | VRSTR); End;
④:dbms_lob. GetLength (): Returns a function that specifies the length of the LOB data. Dbms_lob. GetLength (LOB data);
⑤:dbms_lob.compare (): Compares two large objects for equality. The return value 0 is equal,-1 is not equal. Dbms_lob.compare (LOB data, LOB data); Example: Declare varC1 clob; VarC2 Clob; VarC3 Clob; ln number (4); Begin Select Resume into varC1 from tlob where no = 1; Select Resume into varC2 from tlob where no = 2; Select Resume into varC3 from tlob where no = 3; ln: = Dbms_lob.compare (VARC1,VARC1); Dbms_output.put_line (' Comparison results are: ' | | LN); ln: = Dbms_lob.compare (VARC2,VARC3); Dbms_output.put_line (' Comparison results are: ' | | LN); End; 2, the operation of large object data will change the original data in the database, you need to add updata lock locks the specified data column, after the modification of the commit transaction.
①:dbms_lob. Write (): The process of writing the specified amount of data to the LOB. Dbms_lob. Write (written to lob, write length (referred to as LOB data written), write starting position (referred to as LOB), write LOB data); Example: Declare Varc clob; Vwstr VARCHAR2 (1000); VSTRT number (4); ln number (4); Begin vwstr: = ' CLOB '; ln: = Length (VWSTR); VSTRT: = 5; Select Resume into Varc from Tlob where no. = 1 for UPDATE; Dbms_lob. Write (Varc, Ln, VSTRT, VWSTR); Dbms_output.put_line (' rewrite result: ' | | VARC); Commit; End;
②:dbms_lob. Append (): The procedure for appending the specified LOB data to the specified LOB data. Dbms_lob. Append (LOB data, LOB data); Example: Declare Varc clob; Vastr VARCHAR2 (1000); Begin vastr: = ', this is a large object column '; Select Resume into Varc from Tlob where no. = 1 for UPDATE; Dbms_lob. Append (Varc, VASTR); Commit Dbms_output.put_line (' Append result: ' | | VARC); End;
③:dbms_lob. Erase (): The process of deleting part of the data at a specified location in the LOB data; Dbms_lob. Erase (LOB data, specifying deletion length, starting delete location); Example: Declare Varc clob; ln number (4); STRT number (4); Begin ln: = 1; STRT: = 5; Select Resume into Varc from Tlob where no. = 1 for UPDATE; Dbms_lob. Erase (Varc, Ln, STRT); Commit Dbms_output.put_line (' Erase result: ' | | VARC); End;
④:dbms_lob. Trim (): The process of truncating part of the data in the LOB data that specifies the length from the first position; Dbms_lob. Trim (LOB data, truncated length); Example: Declare Varc clob; ln number (4); Begin ln: = 4; Select Resume into Varc from Tlob where no. = 1 for UPDATE; Dbms_lob. Trim (Varc, LN); COMMIT; Dbms_output.put_line (' truncated result: ' | | VARC); End;
⑤:dbms_lob. Copy (): Copies the source LOB to the target LOB starting at the specified location; Dbms_lob. Copy (source LOB, Target LOB, copy source lob length, copy to target LOB start location, copy source LOB start location) Example: Declare Vdest_lob clob; Vsrc_lob Clob; AMOUNT number; Dest_offset number; Src_offset number; Begin Select Resume into Vdest_lob from Tlob where no. = 1 for UPDATE; Select Resume into Vsrc_lob from tlob where no = 2;
AMOUNT: = Dbms_lob. GetLength (VSRC_LOB); Dest_offset: = Dbms_lob. GetLength (Vdest_lob) +1; Src_offset: = 1;
Dbms_lob. Copy (Vdest_lob, Vsrc_lob, AMOUNT, Dest_offset, Src_offset); Dbms_output.put_line (' Copy result: ' | | VDEST_LOB); End;about performance issues with LOB data types:Clob/blob implementation is more complex, here only to mention a few and performance-related points, of course, can not use lob as far as possible: A, a LOB field including Lobindex and Lobsegment B, lob default can be stored in the Table (table field), the condition is: 1. Its size is less than 4KB 2. And is not used at the time of definition (disable storage inrow) sentence (the default is enable) when the LOB is greater than 4kb it will be stored in the Lobsegment C, when the lob is stored in the table, it can be cached, The operation is much more efficient than the lob stored in lobsegment (no lobindex) d, the lob stored in lobsegment is not in the buffer cache, and the read/write to the LOB is physical IO, very expensive, Therefore, for LOB fields greater than 4KB, do not update frequently, the efficiency is very low E, the lob stored in Lobsegment can be specified using the cache at the time of definition (NoCache by default), which is useful for medium-sized lobs (such as a few k~ dozens of k). Reduce physical IOperformance issues to consider with other data types:1, Char fixed-length format string, stored in the database when the insufficient number of spaces to fill, not recommended, will bring unnecessary trouble A, string comparison, if you do not pay attention to (Char less bit padding) will bring error B, string comparison, if using the trim function, This will invalidate the index on the field (sometimes causing serious performance problems) C, waste storage space
2, Varchar2/varchar indefinite long format string, for 4000 bytes of string, it is recommended to use the type A, online has said that char than varchar2 performance, but if you are interested in doing testing, you will find no difference (if a row migration occurs, Can be adjusted by Pctfree) B, make full use of storage space
3, Long/long Raw Oracle has been discarded, just for backwards compatibility retention, should be all upgraded to lob long type There are many restrictions a, the table can only have one column long type B, the long type does not support distributed transaction C, too many queries can not be used on Long
4, number defines the method of numbers: Number (p,s) where p,s are optional: A, p for precision, the default is B, s for the number of decimal digits, the value range -84~127, the default value depends on whether p is specified, if p is established, the default s is 0, if no p is specified , the maximum value is taken by default. A few examples: A, number (5,0) =number (5) range of values 99999~-99999 B, numbers (5,2) Range 999.99~-999.99 Note: There are only 3 digits in the integer digits, and the number of decimal digits is 2 bits, calculated as follows: Integer digits <=p-s number of decimal places <=s if the Insert 123.555 store becomes 123.56 in the database (rounded on the third bit of the decimal), if you insert 999.999, the database will be thrown wrong. C, Number (5,-2) value range 9999900~-9999900 (integer digits <=p-s, no decimal digits) If insert 9999949 is stored in the database becomes 9999900 (rounded on the second bit of the integer), if you insert 9999950, The database is going to be thrown wrong. The other numeric types are derived from number, and the underlying numbers are, for example, Integer/int fully mapped to (38) performance-Related: # is a type of soft implementation, if you need to do a complex operation on number, It is recommended that you first convert number to floating-point type with cast built-in functions another thing to note is that number is a variable-length type, so remember when calculating the table storage space
5, the date date type is a 7-byte fixed-length data type, nothing to say, an example: Performance A>b>c A, Where date_colum>=to_date (' 01-jan-2007 ', ' dd-mon-yyyy ') and date_colum< Div>b, where trunc (Date_colum, ' y ') =to_date (' 01-jan-2007 ', ' dd-mon-yyyy ') c, where To_char (Date_ Colum, ' yyyy ') = ' 2007 '
6. Timestamp/timestamp with time Zone/timestamp with the local timezone and date are similar, except that it supports fractional seconds and the timezones in addition. Syntax timestamp (n), n specifies the number of decimal digits in seconds, and the range of values 0~9. Options available
