The Blob field in Oracle is special. It has much better performance than the long field and can be used to save binary data such as sample slices.
Writing Blob fields is very different from writing other types of fields. Because Blob itself has a cursor, you must use cursor to operate blob. Therefore, before writing Blob, you must obtain the cursor to write data. How can you obtain the Blob cursor? This requires you to insert an empty blob first, which will create a blob cursor, and then you can query the cursor of the empty blob using select. In this way, you can perform two steps, you can get the blob cursor to write blob data. Example: Oracle series: LOB large object processing is mainly used to store large amounts of data
DatabaseField, which can store up to 4 GB of unstructured data. This section describes the storage of character type and binary file type LOB data, and separately introduces the storage of binary type LOB data.
1. Oracle LOB Data Type Classification 1. Data Storage types are classified as follows: ① character type: CLOB: large volumes of single-byte character data are stored. NLOB: stores fixed-width, multi-byte character data. ② Binary type: BLOB: stores large unstructured binary data. ③ Binary file type: BFILE: stores binary files in operating system files outside the database. Path for storing files.
2. Classified by storage method: ① stored in the internal tablespace: CLOB, NLOB, and BLOB ② pointed to the external operating system file: BFILE 2, big object data input 1, declare the LOB type column/* conn scott/tiger; Create TableSpace ts5_21 DataFile 'e: \ Oracle \ ts5_21.dbf' Size 5 m; */Create Table tLob (no Number (4 ), name VarChar2 (10), resume CLob, photo BLob, record BFile) Lob (resume, photo) store As (Tablespace ts5_21 -- specify the Storage Tablespace Chunk 6 k -- specify the data block size Disable Storage In Row); 2. Insert a large object column ① insert data In a common column first
② When a large object column is encountered, insert a blank constructor. Quiet: empty_clob (), empty_nclob () binary: empty_blob () binary file type: BFileName function points to external files. BFileName function: BFileName ('logical directory name', 'filename '); logical directory names can only be capitalized because data dictionaries are stored in uppercase. Oracle is case sensitive. During creation, you do not need to point the logical directory of the BFileName function to the physical path. When using this function, you can check whether the two are associated. Example: Insert Into tLob Values (1, 'gene ', empty_clob (), empty_blob (), bfilename ('mydir', 'img _ 0210. JPG '));
③ Associate the logical directory with the physical directory. (If it is a binary file type) Grant the create any directory permission Grant create any directory to user name with admin option; associate logical Directory and physical Directory local Create Directory logical Directory name As 'physical Directory of the file'; Network: Create Directory logical Directory name As '\ Host Name (IP) \ shared directory '; example: Create Directory MYDIR As 'e: \ Oracle ';
Insert example: insert into tlob values (1, 'gene ', 'clob large object columns', empty_blob (), bfilename ('mydir', 'img _ 0210. JPG '); 3. Read and operate big object data: DBMS_LOB package: contains the process of processing large objects and functions/* insert into tlob values (1, 'expression', 'clob large object columns ', empty_blob (), bfilename ('mydir', 'img _ 0210. JPG '); insert into tlob values (2, 'jack', 'clob large object columns', empty_blob (), bfilename ('mydir', 'img _ 0210. JPG '); insert into tlob values (3, 'Mary', 'Big object column clob', empty_blob (), bfilenam E ('mydir', 'img _ 0210. JPG '); */1, the process of reading big object data and function ①: DBMS_LOB.Read (): The process of reading the specified length of data from the LOB data to the buffer zone. DBMS_LOB.Read (LOB data, specifying the length, starting position, and storing the returned 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 (): The function used to extract the substring from the LOB data. DBMS_LOB.SubStr (LOB data, specify the extraction length and start 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 (the result is: '| vRStr); End;
③: DBMS_LOB.InStr (): The function used to find the position of the substring from the LOB data. DBMS_LOB.InStr (LOB data, sub-string); 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 ('location: '| ln );
VRStr: = DBMS_LOB.SubStr (varC, Length (vSubStr), ln); DBMS_output.put_line ('location: '| ln |' Length: '| Length (vSubStr) | 'substring:' | vRStr); End;
④: DBMS_LOB.GetLength (): return the function of the length of the specified LOB data. DBMS_LOB.GetLength (LOB data );
⑤: DBMS_LOB.Compare (): checks whether two large objects are equal. The return value 0 is equal, and-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 result: '| ln); ln: = DBMS_LOB.Compare (varC2, varC3); DBMS_output.put_line ('comparison result:' | ln); End; 2. Operations on large object data will change the original data in the database. You need to add the Updata lock to lock the specified data column and submit the transaction after modification.
①: DBMS_LOB.Write (): Write a specified amount of data to LOB. DBMS_LOB.Write (written LOB, write length (written LOB data), Write Start position (written 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 process of 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: = ', which is a big 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 the specified position in the LOB data; DBMS_LOB.Erase (LOB data, specifying the deletion length and starting to delete the 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 ('erased result: '| varC); End;
④: DBMS_LOB.Trim (): Process of truncating part of the data from the first position to specify the length; DBMS_LOB.Trim (LOB data, truncation 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 ('truncation result: '| varC); End;
⑤: DBMS_LOB.Copy (): copy the source LOB to the target LOB from the specified location; DBMS_LOB.Copy (source LOB, target LOB, copy the source LOB length, and copy it to the start location of the target LOB, 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); reverse ('Copy result: '| vDEST_LOB); End; performance problems with LOB data types: clob/blob implementation is complicated. Here we only mention a few performance-related points. Of course, lob is not needed as much as possible: a. a lob field includes lobindex, lobsegment B, and Lob can be stored in the table (table field) by default. The condition is: 1. it is smaller than 4 kb 2. the disable storage inrow statement is not used during definition (enable by default) when a lob is larger than 4 kb, it will be stored in the lobsegment. c. When a lob is stored in the table, it can be cached, its operation efficiency is much higher than that of lob stored in lobsegment (without lobindex) d, The lob stored in the lobsegment is not cached in the buffer by default. The lob read/write operations are physical IO and the cost is very high. Therefore, do not update the lob fields larger than 4 kb frequently, low Efficiency e. You can specify the cache when defining the lob stored in the lobsegment. The default value is nocache. For a lob of medium size (such as a few k ~ Dozens of k) very effective, reduce the performance issues that need to be considered when using other data types for physical IO: 1. Char fixed-length Format String, when storing in the database, fill in spaces with insufficient digits, it is not recommended to use it, which will lead to unnecessary troubles. When comparing strings, if you do not pay attention to it (the char character is not filled with spaces), it will lead to errors. When comparing strings, if the trim function is used, the index on the field becomes invalid (sometimes causing serious performance problems). c. A waste of storage space.
2. Varchar2/Varchar string of an uncertain length format. For strings within 4000 bytes, we recommend that you use this type a. On the internet, char is better than varchar2, however, if you are interested in testing, you will find that there is no difference (in case of row migration, you can adjust it through pctfree) B. Make full use of the storage space
3. Long/long raw Oracle has been discarded, but is reserved for backward compatibility, there are many restrictions on upgrading all the data to the lob Long type. a table can only have one column of long TYPE B, Long type does not support distributed transaction c, and too many queries cannot be used on long.
4. Number: Number (p, s). p and s are optional. a and p indicate precision. The default value is 38 B, and s indicates decimal places, value Range:-84 ~ 127. The default value depends on whether p is specified. If p is specified, the default value s is 0. If p is not specified, the maximum value is used by default. Examples: a, Number (99999) = Number (5) value range ~ -99999 B, Number (999.99) value range ~ -999.99 Note: The number of integer digits is only three digits, and the number of decimal places is two digits. The calculation is as follows: integer digits <= p-s decimal places <= s if 123.555 is inserted and stored in the database becomes 123.56 (rounded to the third decimal place), if 999.999 is inserted, the database will throw an error. C. The value range of Number (5,-2) is 9999900 ~ -9999900 (integer digits <= p-s, no decimal places) If 9999949 is inserted and stored in the database becomes 9999900 (rounded to the second digit of the integer), if 9999950 is inserted, an error is required for the database. Other numeric types are derived from number, and the bottom layer is number. For example, integer/int are fully mapped to number (38). Performance-related: number is a soft Implementation type, if you need to perform complex operations on the number, we recommend that you use the cast built-in function to convert the number to the floating point type. Note that the number is of the variable length type. Remember to use this function when calculating the table storage space.
5. The Date type is a 7-byte fixed-length data type, which is nothing to mention. 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') = '000000'
6. Timestamp/timestamp with time zone/timestamp with local time zone is similar to date, except that it supports decimal seconds and time zone. Syntax: Timestamp (n). n indicates the number of decimal places in seconds. The value range is 0 ~ 9. Optional
