c＃处理oracle clob（一）—

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

2024-03-16 21:48

文章标签 oracle 处理 insert clob

本文主要是介绍c＃处理oracle clob（一）——insert，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

创建表：

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();
            }
        }
    }