Shulou(Shulou.com)05/31 Report--

This article mainly shows you the "Oracle CLOB big data field type operation example analysis", the content is easy to understand, well-organized, hope to help you solve your doubts, the following let Xiaobian lead you to study and learn "Oracle CLOB big data field type operation example analysis" this article.

1. Varchar2 types in Oracle

The character data we store in the Oracle database is usually stored in VARCHAR2. VARCHAR2 is divided into both variable types in PL/SQL Data Types and field types in Oracle Database. The maximum length varies with different scenarios.

In Oracle Database, the VARCHAR2 field type, the maximum value is 4000 VARCHAR2 variable type in PL _ SQL, and the maximum byte length is 32767.

When VARCHAR2 can't hold the information we need to store, the Oracle's big data type LOB (Large Object) comes out.

II. LOB types in Oarcle

In Oracle, fields of type LOB (Large Object) are now used more and more. Because this type of field has a large capacity (up to 4GB data), and there can be multiple fields of this type in a table, it is very flexible and suitable for business areas with a very large amount of data (such as images, archives, etc.).

There are two types of LOB: BLOB and CLOB: BLOB is binary large object (Binary Large Object), which is suitable for storing non-text byte stream data (such as programs, images, audiovisual, etc.).

On the other hand, CLOB, that is, character-based large objects (Character Large Object), is related to the character set and is suitable for storing text-based data (such as historical archives, tome, etc.).

Third, use CLOB type fields in DB

(1), create a table (using sql or directly in the PL/SQL client), field type CLOB

-- Create tablecreate table TEMP (name VARCHAR2, age NUMBER, temp_clob CLOB) tablespace INSIGHTDATA3_TS pctfree 10 initrans 1 maxtrans 255storage (initial 160K next 1m minextents 1 maxextents unlimited)

(2) add, delete, modify and check

Let's first take a look at the normal way to manipulate the CLOB type:

SELECT t.name, t.temp_clob FROM temp t;-General query INSERT INTO temp t VALUES ('Grand.Jon', 22,' first day of joining the blog park')

Since the query is not of type varchar2, the field content of type CLOB cannot be seen in an ordinary query. The result is as follows.

The ordinary insert operation will also convert the string to varchar2 by default because of the implicit conversion of Oracle. Once the content of the string exceeds the maximum limit of varchar2, it will report an ora-01704 (string is too long) error.

Correct operation

-- use PL/ SQL syntax to bind variables instead of directly concatenating SQLDECLARE V_LANG CLOB: = 'massive string to be inserted'; V_UPDATE CLOB: = 'updated massive string'; BEGIN INSERT INTO temp t VALUES ('Grand.Jon', 22, V_LANG);-- add UPDATE temp t SET t.temp_clob = V_UPDATE WHERE rownum = 1 -- modify SELECT t.NAME, dbms_lob.substr (t.temp_clob) FROM TEMP t;-- query to convert CLOB to character type DELETE temp t WHERE rownum = 1;-- delete COMMIT;END;/ by column

For the operation of CLOB, we basically use substr, append, write and other methods in dbms_lob in the stored procedure.

Summary of dbms_lob method

Dbms_lob.createtemporary;-- create a temporary clob to store the stitched sql dbms_lob.write;-- write operation dbms_lob.append;-- splice clob dbms_lob.substr (v_SQL);-- intercept clob, and read all SQL without passing parameters (v_SQL) -- release clob

The query results are as follows:

Fourth, use CLOB type instances in stored procedures

Requirements: take the developed stored procedure as an example, you need to cycle through the time range stitching sql and reverse the time and date by column (pivot). If the time is too long (more than 1 year), the sql statement (varchar2) will report an error out of the range. At this time, you need to use CLOB to store the stitched sql.

PROCEDURE P_AND_CPT_RATIOOTH_APP_BAK2_N (V_APPIDS IN VARCHAR2, V_TYPE IN VARCHAR2, V_CHANNEL IN VARCHAR2, V_TABLE IN VARCHAR2, V_START IN VARCHAR2, V_END IN VARCHAR2, RESULT OUT mycursor) ISV_SQL CLOB;V_SQLWHERE VARCHAR2 (32767) default''; V_SQLWHERE_CHANNEL VARCHAR2 (32767) default''; V_SQL_DATES CLOB;V_Sdate DATE;V_Edate DATE;V_TABLE_DATE VARCHAR2 (50) V_TABLE_TYPE VARCHAR2 (50); V_START_DATE VARCHAR2 (50); V_END_DATE VARCHAR2 (50); V_DAY VARCHAR2 (50); BEGIN select column_name into V_TABLE_DATE from user_tab_columns where table_name='' | | V_TABLE | |''and column_id=1; select column_name into V_TABLE_TYPE from user_tab_columns where table_name='' | | V_TABLE |' 'and column_id=5; dbms_lob.createtemporary -- create a temporary lob dbms_lob.createtemporary;-- create a temporary lob IF V_APPIDS is NOT NULL THEN V_SQLWHERE: = 'AND t.appid in (' | V_APPIDS | |')'; END IF; IF V_CHANNEL IS NOT NULL THEN V_SQLWHERE_CHANNEL: = 'AND t.channel =''| V_CHANNEL | |'; END IF IF V_TABLE_DATE = 'MON' THEN V_START_DATE: = SUBSTR; V_END_DATE: = SUBSTR; v_sdate: = to_date (V_START_DATE,' yyyymm'); v_edate: = to_date (V_END_DATE, 'yyyymm'); WHILE (v_sdate

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.