c＃处理oracle clob（一）——insert

最新推荐文章于 2021-04-13 11:08:17 发布

tan_jianhui

最新推荐文章于 2021-04-13 11:08:17 发布

阅读量2.6k

点赞数

分类专栏：软件开发文章标签： insert oracle c null command buffer

本文链接：https://blog.csdn.net/tan_jianhui/article/details/1956143

版权

软件开发专栏收录该内容

137 篇文章 0 订阅

订阅专栏

创建表：

create table TB_TEST
(
ID         NUMBER not null,
COLUMNID   NUMBER not null,
TITLE      VARCHAR2(100) not null,
CONTENT    CLOB not null,
CREATETIME DATE default SYSDATE not null
)

存储过程：

create or replace procedure PROC_TEST_ADD
(
    i_title                  in varchar2,
    i_columnId               in number,
    i_content                in clob,

    o_id                     out number
)
is
    v_content                clob;
    v_buffer                 varchar2(512);
    v_length                 number := 0;
    v_offset                 number := 1;
    v_amount                 number := 256;
begin
    select seq_test.nextval into o_id from dual;
    insert into tb_test
    (
        id,
        columnid,
        title,
        content,
        createtime
    )
    values
    (
        o_id,
        i_columnid,
        i_title,
        empty_clob(),
        sysdate
    );
    dbms_output.put_line(v_length);
    v_length := dbms_lob.getlength(i_content);
    dbms_output.put_line(v_length);
    select content into v_content from tb_test where id = o_id for update;
    dbms_lob.open(v_content,dbms_lob.lob_readwrite);
    while v_offset <= v_length
    loop
        dbms_lob.read(i_content,v_amount,v_offset,v_buffer);
        dbms_lob.writeappend(v_content,v_amount,v_buffer);
        v_offset := v_offset + v_amount;
    end loop;
    dbms_lob.close(v_content);
    commit;
end PROC_TEST_ADD;

Insert函数：

public void Insert()
    {
        string sConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["wuyou"].ConnectionString;
        OracleConnection connection = null;
        OracleCommand command = null;
        try
        {
            connection = new OracleConnection(sConnectionString);
            connection.Open();

            string str = "跨世纪发的司法考试方";
            while (str.Length < 5000)
            {
                str += str;
            }

            command = new OracleCommand("PROC_TEST_ADD",connection);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("i_title", OracleType.VarChar);
            command.Parameters["i_title"].Direction = ParameterDirection.Input;
            command.Parameters["i_title"].Value = "";
            command.Parameters.Add("i_columnid", OracleType.Number);
            command.Parameters["i_columnid"].Direction = ParameterDirection.Input;
            command.Parameters["i_columnid"].Value = 1;
            command.Parameters.Add("i_content", OracleType.Clob);
            command.Parameters["i_content"].Direction = ParameterDirection.Input;
            command.Parameters["i_content"].Value = str;
            command.Parameters.Add("o_id", OracleType.Number);
            command.Parameters["o_id"].Direction = ParameterDirection.Output;

            command.ExecuteNonQuery();
        }
        finally
        {
            if (command != null)
            {
                command.Dispose();
            }
            if (connection != null)
            {
                connection.Dispose();
                connection.Close();
            }
        }
    }