CLOB与VARCHAR2，还有其他选择吗? [英] CLOB vs. VARCHAR2 and are there other alternatives?

问题描述

我正在为我的应用程序使用DevArt的dotConnect和Entity Developer.我已经使用实体优先"功能创建了表格.

I am using DevArt's dotConnect and Entity Developer for my application. I've created the tables using the Entity-First feature.

我注意到许多列类型都设置为CLOB.我只具有MySQL和Microsoft SQL Server的经验，所以不确定将CLOB用于该应用程序.我做了一些阅读，发现CLOB是用于处理大量数据的.

I notice that many of the column types are set to CLOB. I only have experience with MySQL and Microsoft SQL server, so I am not sure about using CLOB for the application. I did some reading, and found out that CLOB are for large chunk of data.

问题是:

1. 对于大多数字段(例如用户的性别(应为varchar(1))或全名)使用CLOB可行吗?将CLOB字段转换为VARCHAR2的步骤需要删除该列，然后重新创建它，并且在DevArt的Entity Explorer中存在错误，因此，如果可能的话，我希望避免使用. 编辑:我刚刚发现，如果您为字符串字段设置了最大长度，它将自动为VARCHAR2.

1. Is using CLOB for most fields, such as the user's gender (which should be a varchar (1) ) or the full name, feasible? The steps for converting a CLOB field to VARCHAR2 requires dropping the column then re-creating it, and is buggy in DevArt's Entity Explorer, so I would like avoid it if possible. Edit: I just found out that if you set a maximum length for a string field it'll automatically be a VARCHAR2.

Oracle中是否有TINYTEXT的等效项?

Are there any equivalents for TINYTEXT in Oracle?

推荐答案

对要使用VARCHAR2(1)的列使用CLOB数据类型是一个非常糟糕的主意.除了开销(实际上是极小的开销，因为Oracle将小于4000个字符的内联CLOB视为VARCHAR2)，我们应该始终努力在模式中使用最准确的数据表示形式:

It is a very bad idea to use a CLOB datatype for a column which ought to be VARCHAR2(1). Apart from the overheads (which are actually minimal, as Oracle will treat inline CLOBs of < 4000 characters as VARCHAR2) we should always strive to use the most accurate representation of our data in the schema: it's just good practice.

这似乎确实是DevArt工具的问题，或者您对如何使用它的理解(没有违法).应该有某种方法可以指定实体属性的数据类型和/或将这些规范映射到Oracle物理数据类型的方法.如果这有点含糊，我深表歉意，我不熟悉该产品.

This really seems like a problem with the DevArt tool, or perhaps your understanding of how to use it (no offence). There ought to be some way for you to specify the datatype of an entity's attribute and/or a way of mapping those specifications to Oracle's physical datatypes. I apologise if this seems a little vague, I'm not familar with the product.

所以，这是基本问题:

SQL> desc t69
 Name                                      Null?    Type
 ----------------------------------------- -------- --------
 COL1                                               CLOB

SQL>
SQL> alter table t69 modify col1 varchar2(1)
  2  /
alter table t69 modify col1 varchar2(1)
                       *
ERROR at line 1:
ORA-22859: invalid modification of columns


SQL>

我们可以通过使用DDL更改表结构来修复它.由于架构具有许多此类列，因此值得使流程自动化.此函数删除现有列并将其重新创建为VARCHAR2.它提供了将CLOB列中的数据迁移到VARCHAR2列的选项.您可能不需要这个，但是为了完整起见. (这不是生产质量代码-它需要错误处理，管理NOT NULL约束等)

We can fix it by using DDL to alter the table structure. Because the schema has many such columns it is worthwhile automating the process. This function drops the existing column and recreates it as a VARCHAR2. It offers the option to migrate data in the CLOB column to the VARCHAR2 column; you probably don't need this, but it's there for completeness. (This is not production quality code - it needs error handling, managing NOT NULL constraints, etc)

create or replace procedure clob2vc
  ( ptab in user_tables.table_name%type 
    , pcol in user_tab_columns.column_name%type
    , pcol_size in number
    , migrate_data in boolean := true )
is
begin
    if migrate_data
    then
        execute immediate 'alter table '||ptab
                    ||' add tmp_col varchar2('|| pcol_size|| ')';
        execute immediate             
                    'update '||ptab
                    ||' set tmp_col = substr('||pcol||',1,'||pcol_size||')';
    end if;
    execute immediate 'alter table '||ptab
                ||' drop column '|| pcol;

    if migrate_data
    then
        execute immediate 'alter table '||ptab
                    ||' rename column tmp_col to '|| pcol;
    else
        execute immediate 'alter table '||ptab
                    ||' add '||pcol||' varchar2('|| pcol_size|| ')';
    end if;
end;
/

所以，让我们更改该列...

So, let's change that column...

SQL> exec clob2vc ('T69', 'COL1', 1)

PL/SQL procedure successfully completed.

SQL> desc t69
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------
 COL1                                               VARCHAR2(1)

SQL>

可以自动调用该过程，也可以按照通常的方式编写脚本.

Calling this procedure can be automated or scripted in the usual ways.

这篇关于CLOB与VARCHAR2，还有其他选择吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！