数据库中存储过程编写
PROCEDURE p_clobwriteappend
(i_clobtablename in varchar2, ----对应的表名称
i_clobfieldname in varchar2, ----Clob对应的字段名称
i_clobidfieldname in varchar2, ----where 条件对应的字段名称
i_clobidfieldvalue in varchar2, ----where条件对应的值
i_clob in varchar2, ----clob 对应的值
o_RetNum OUT CHAR, ----返回代码
o_RetMsg OUT VARCHAR2 ----返回信息
) is
-----------------------定义变量--------------------
v_execsql_select varchar2(1000);
v_execsql_update varchar2(1000);
v_execsql varchar2(1000);
v_lobloc clob;
-----------------------执行-----------------------
begin
--o_execstate :='error in p_clobwriteappend';--返回执行状态
o_RetNum := PKG_CONSTANT.C_TRUE;
v_execsql_select := ' select ' || i_clobfieldname || ' from ' || i_clobtablename ||
' where ' || i_clobidfieldname || ' =''' || i_clobidfieldvalue || '''';
EXECUTE IMMEDIATE v_execsql_select INTO v_lobloc;
IF v_lobloc IS NULL THEN
v_execsql_update := ' update ' || i_clobtablename ||
' set ' || i_clobfieldname || ' = empty_clob() ' ||
' where ' || i_clobidfieldname || ' =''' || i_clobidfieldvalue || '''';
EXECUTE IMMEDIATE v_execsql_update;
execute immediate 'commit';
end if;
v_execsql := ' select ' || i_clobfieldname || ' from ' || i_clobtablename ||
' where ' || i_clobidfieldname || ' =''' || i_clobidfieldvalue || ''' for update ';
EXECUTE IMMEDIATE v_execsql INTO v_lobloc;
dbms_lob.append(v_lobloc,i_clob);
execute immediate 'commit';
----------------------输出------------------------------
--o_execstate :='success in p_clobwriteappend';--返回执行状态
exception
when others then
dbms_output.put_line(sqlcode||'execsql：'||v_execsql||sqlerrm);
o_RetNum := PKG_CONSTANT.C_FALSE;
o_RetMsg := SQLCODE || '--' || SQLERRM;
rollback;
end;
c#调用的代码
/// <summary>
/// 添加一条文章信息,先文章内容为空,再返回出ID,再更新文章的内容
/// </summary>
/// <param name="nwarticle"></param>
/// <returns></returns>
public void InsertNwArticle(NwArticle nwarticle, string nwproids, ref string id)
{
OracleParameter[] pars = {
new OracleParameter("io_Pkid",OracleType.Char,24),
new OracleParameter("i_TITLE",OracleType.VarChar,100),
new OracleParameter("i_CONTENT",OracleType.Clob),
new OracleParameter("i_SOURCE",OracleType.VarChar,40),
new OracleParameter("i_HITS",OracleType.Number,10),
new OracleParameter("i_TOP_START_TIME",OracleType.DateTime,7),
new OracleParameter("i_AUTHOR",OracleType.VarChar,40),
new OracleParameter("i_RECOM_MARK",OracleType.Number,1),
new OracleParameter("i_STATUS",OracleType.Number,1),
new OracleParameter("i_COLUMN_CODE",OracleType.VarChar,20),
new OracleParameter("i_FOCUS_NEWS",OracleType.Number,1),
new OracleParameter("i_MARK_TITLE",OracleType.Number,9),
new OracleParameter("i_TOP_END_TIME",OracleType.DateTime,7),
new OracleParameter("i_WEIGHT",OracleType.Number,0),
new OracleParameter("i_CREATE_TIME",OracleType.DateTime,7),
new OracleParameter("i_UPDATE_TIME",OracleType.DateTime,7),
new OracleParameter("i_CREATED_BY",OracleType.Char,24),
new OracleParameter("i_UPDATED_BY",OracleType.Char,24),
new OracleParameter("i_NWPRO_IDS",OracleType.VarChar,1000),
new OracleParameter("i_IS_NEED_MESSAGE",OracleType.VarChar,2),
new OracleParameter("i_mobile_img_name",OracleType.VarChar,200),
new OracleParameter("i_mobile_img_path",OracleType.VarChar,500),
new OracleParameter("i_mobile_remark",OracleType.VarChar,1000),
new OracleParameter("o_RetNum",OracleType.Char,24),
new OracleParameter("o_RetMsg",OracleType.VarChar,200)
};
pars[0].Direction = ParameterDirection.InputOutput;
pars[1].Value = nwarticle.Title;
pars[2].Value = " ";//nwarticle.Content;
pars[3].Value = nwarticle.Source;
pars[4].Value = nwarticle.Hits;
pars[5].Value = nwarticle.Top_start_time;
pars[6].Value = nwarticle.Author;
pars[7].Value = nwarticle.Recom_mark;
pars[8].Value = nwarticle.Status;
pars[9].Value = nwarticle.Column_code;
pars[10].Value = nwarticle.Focus_news;
pars[11].Value = nwarticle.Mark_title;
pars[12].Value = nwarticle.Top_end_time;
pars[13].Value = nwarticle.Weight;
pars[14].Value = nwarticle.Create_time;
pars[15].Value = nwarticle.Update_time;
pars[16].Value = nwarticle.Createdby;
pars[17].Value = nwarticle.Updatedby;
pars[18].Value = nwproids;
pars[19].Value = nwarticle.Is_need_message;
pars[20].Value = nwarticle.mobile_img_name;
pars[21].Value = nwarticle.mobile_img_path;
pars[22].Value = nwarticle.mobile_remark;
pars[23].Direction = ParameterDirection.Output;
pars[24].Direction = ParameterDirection.Output;
combase.RunProcedure("PKG_MW_ARTICLE.P_MW_ARTICLE_INSERT", pars);
id = pars[0].Value as string;
//更新文章的内容
nwarticle.Id = id;
UpdateNewArticelContent(nwarticle);
}
///更新文章的内容,先把文章的内容更新为空,在根据主键去更新文章的内容.
public bool UpdateNwArticle(NwArticle nwarticle)
{
bool isSuccess = false;
OracleParameter[] pars = {
new OracleParameter("i_ID",OracleType.Char,24),
new OracleParameter("i_TITLE",OracleType.VarChar,100),
new OracleParameter("i_CONTENT",OracleType.Clob),
new OracleParameter("i_SOURCE",OracleType.VarChar,40),
new OracleParameter("i_TOP_START_TIME",OracleType.DateTime,7),
new OracleParameter("i_AUTHOR",OracleType.VarChar,40),
new OracleParameter("i_COLUMN_CODE",OracleType.VarChar,20),
new OracleParameter("i_MARK_TITLE",OracleType.Number,9),
new OracleParameter("i_TOP_END_TIME",OracleType.DateTime,7),
new OracleParameter("i_WEIGHT",OracleType.Number,0),
new OracleParameter("i_UPDATE_TIME",OracleType.DateTime,7),
new OracleParameter("i_Updated_By",OracleType.Char,24),
new OracleParameter("i_IS_NEED_MESSAGE",OracleType.VarChar,2),
new OracleParameter("i_File_Url",OracleType.VarChar,255),
new OracleParameter("i_mobile_img_name",OracleType.VarChar,200),
new OracleParameter("i_mobile_img_path",OracleType.VarChar,500),
new OracleParameter("i_mobile_remark",OracleType.VarChar,1000),
new OracleParameter("o_RetNum",OracleType.Char,24),
new OracleParameter("o_RetMsg",OracleType.VarChar,200)
};
pars[0].Value = nwarticle.Id;
pars[1].Value = nwarticle.Title;
pars[2].Value = " ";//nwarticle.Content;
pars[3].Value = nwarticle.Source;
pars[4].Value = nwarticle.Top_start_time;
pars[5].Value = nwarticle.Author;
pars[6].Value = nwarticle.Column_code;
pars[7].Value = nwarticle.Mark_title;
pars[8].Value = nwarticle.Top_end_time;
pars[9].Value = nwarticle.Weight;
pars[10].Value = nwarticle.Update_time;
pars[11].Value = nwarticle.Updatedby;
pars[12].Value = nwarticle.Is_need_message;
pars[13].Value = nwarticle.File_url;
pars[14].Value = nwarticle.mobile_img_name;
pars[15].Value = nwarticle.mobile_img_path;
pars[16].Value = nwarticle.mobile_remark;
pars[17].Direction = ParameterDirection.Output;
pars[18].Direction = ParameterDirection.Output;
isSuccess = combase.RunProcedure("PKG_MW_ARTICLE.P_MW_Article_Update", pars);
//更新文章的内容
UpdateNewArticelContent(nwarticle);
return isSuccess;
}
/// <summary>
/// 更新文章的内容,分段更新文章的内容,2000个字符截取
/// </summary>
/// <returns></returns>
public void UpdateNewArticelContent(NwArticle nwarticle)
{
if (!string.IsNullOrEmpty(nwarticle.Content)&&nwarticle.Content.Trim().Length > 0)
{
int length = nwarticle.Content.Trim().Length;
if (length > 2000)
{
int i = length / 2000;
for (int j = 0; j < i+1; j++)
{
string temp = string.Empty;
if (i == j)
{
temp = nwarticle.Content.Substring(j * 2000, length - j * 2000);
}
else
{
temp = nwarticle.Content.Substring(j * 2000, 2000);
}
//更新文章的内容
OracleParameter[] pars = {
new OracleParameter("i_clobtablename",OracleType.VarChar,100),
new OracleParameter("i_clobfieldname",OracleType.VarChar,100),
new OracleParameter("i_clobidfieldname",OracleType.VarChar,100),
new OracleParameter("i_clobidfieldvalue",OracleType.VarChar,100),
new OracleParameter("i_clob",OracleType.VarChar,3000),
new OracleParameter("o_RetNum",OracleType.Char,24),
new OracleParameter("o_RetMsg",OracleType.VarChar,200)
};
pars[0].Value = "MW_ARTICLE";
pars[1].Value = "Content";
pars[2].Value = "Id";
pars[3].Value = nwarticle.Id;
pars[4].Value = temp;
pars[5].Direction = ParameterDirection.Output;
pars[6].Direction = ParameterDirection.Output;
combase.RunProcedure("PKG_MW_ARTICLE.p_clobwriteappend", pars);
}
}
else
{
//更新文章的内容
OracleParameter[] pars = {
new OracleParameter("i_clobtablename",OracleType.VarChar,1000),
new OracleParameter("i_clobfieldname",OracleType.VarChar,1000),
new OracleParameter("i_clobidfieldname",OracleType.VarChar,1000),
new OracleParameter("i_clobidfieldvalue",OracleType.VarChar,1000),
new OracleParameter("i_clob",OracleType.VarChar,3000),
new OracleParameter("o_RetNum",OracleType.Char,24),
new OracleParameter("o_RetMsg",OracleType.VarChar,200)
};
pars[0].Value = "MW_ARTICLE";
pars[1].Value = "Content";
pars[2].Value = "Id";
pars[3].Value = nwarticle.Id; ;
pars[4].Value = nwarticle.Content;
pars[5].Direction = ParameterDirection.Output;
pars[6].Direction = ParameterDirection.Output;
combase.RunProcedure("PKG_MW_ARTICLE.p_clobwriteappend", pars);
}
}
}