Begin
if( exists (select 1 from ADM_PLANT_MAST where CLIENT='888' and PLANT='CN10'))
begin
return 0
insert into ADM_PLANT_MAST
(CLIENT,PLANT,NAME1,NAME2,ADDRESS1,CITY,POSTAL,COUNTRY,CREATEBY,CREATEDATE,UPDATEBY,UPDATEDATE,ADDR2,TELEPHONE,FAX)
values('1','CN00','','','','','','','','','','','','','')
return 1
end我想在Oracle中实现这样的语句,不知道怎么写?
c int;
begin
select count(1)
into c
from ADM_PLANT_MAST
where CLIENT = '888'
and PLANT = 'CN10';
if c >= 1 then
insert into ADM_PLANT_MAST
(CLIENT,
PLANT,
NAME1,
NAME2,
ADDRESS1,
CITY,
POSTAL,
COUNTRY,
CREATEBY,
CREATEDATE,
UPDATEBY,
UPDATEDATE,
ADDR2,
TELEPHONE,
FAX)
values
('1', 'CN00', '', '', '', '', '', '', '', '', '', '', '', '', '');
end if;
end;
insert into ADM_PLANT_MAST ......;
return 1;
else
return 0;
end if;
我把3楼的加进去就Error了~declare
c int;
begin
select count(1)
into c
from ADM_PLANT_MAST
where CLIENT = '888'
and PLANT = 'CN10';
if c >= 1 then
insert into ADM_PLANT_MAST
(CLIENT,PLANT,NAME1,NAME2,ADDRESS1,CITY,POSTAL,COUNTRY,CREATEBY,CREATEDATE,UPDATEBY,UPDATEDATE,ADDR2,TELEPHONE,FAX)
values
('1', 'CN00', '', '', '', '', '', '', '', '', '', '', '', '', '');
return 1;
else
return 0;
end if;
end;
declare
c int;
begin
select count(1)
into c
from ADM_PLANT_MAST
where CLIENT = '888'
and PLANT = 'CN10';
if c >= 1
then
insert into ADM_PLANT_MAST
(CLIENT,PLANT,NAME1,NAME2,ADDRESS1,CITY,POSTAL,COUNTRY,CREATEBY,CREATEDATE,UPDATEBY,UPDATEDATE,ADDR2,TELEPHONE,FAX)
values
('1', 'CN00', '', '', '', '', '', '', '', '', '', '', '', '', '');
return 1;
else
return 0;
end if ;我把最后的end去掉也报错
有的话UPDATE没有的话INSERT也可以单UPDATE或者INSERT
這段SQL應該放到Procedure或Trigger中
比较好的是使用,try exception,这在大部分时候是比较节约资源的.
直接写不行吗?
return 1 / 0应该不行这种用法用在function返回值.
愿听高手详解.
RETURNNUM 输出参数
------------/*
PROCEDURE PROC*NAME (param*...,RETURNNUM OUT NUMBER)
DECLARE AAA NUMBER(10);
BEGIN
SELECT 1 INTO AAA FROM ADM_PLANT_MAST where CLIENT='888' and PLANT='CN10';
IF AAA=1 THEN
insert into ADM_PLANT_MAST
(CLIENT,PLANT,NAME1,NAME2,ADDRESS1,CITY,POSTAL,COUNTRY,CREATEBY,CREATEDATE,UPDATEBY,UPDATEDATE,ADDR2,TELEPHONE,FAX)
values('1','CN00','','','','','','','','','','','','','')
RETURNNUM :=0;
ELSE
RETURNNUM :=1;
END IF;
END;
你用return目的无非是为了得到一个返回值吗,所以采取function来写,既容易管理又方便调用,为何还是一味去追求在pl/sql块里return值?
我只是想public static int ExecuteNonQuery(string strSql)
{
GetConnString();
int retcount = -1;
using (OracleConnection conn = new OracleConnection(ConnectionString))
{
using (OracleCommand command = new OracleCommand(strSql, conn))
{
conn.Open();
retcount=command.ExecuteNonQuery();
conn.Clone();
}
}
return retcount;
}public int UploadBulletin(string NoticeID,string Class, string Title, string Content, string Status, string CreateBy)
{
StringBuilder sb = new StringBuilder();
sb.Append(" insert into ADM_BLT(NOTICEID,CLASS,SUBJECT,CONTENT,STATUS,CREATEBY,CREATEDATE)");
sb.Append(" VALUES('" + NoticeID + "','" + Class + "','" + Title + "','" + Content + "','" + Status + "','" + CreateBy + "', sysdate)");
string strSQL = sb.ToString();
return DBHelp.ExecuteNonQuery(strSQL);
}protected void BtUpload_Click(object sender, EventArgs e)
//判断布告是否上传成功
int ChkBulletinInsert = myCreateFactory.UploadBulletin(NoticeID, ddlCategory.Text, txtTitle.Text.Trim(), txtContent.Text.Trim(), Status, User.Identity.Name.ToString());
//布告栏上传成功-->上传附件
if (ChkBulletinInsert == 1)
{}
if (ChkBulletinInsert == 0)
{}
else
{}这样写难道就得不到return的值吗?
http://blog.csdn.net/mantisXF/archive/2008/09/07/2896524.aspx
存储过程没有你想的那么难,我也刚新学带参数的存储过程,做了一个,现在看就基本能看懂了。
在java里面有个方法可以调用存储过程的。。
具体什么忘了,需要查一下。。
你是个执着的人.看了看你的java,觉得你的程序设计有问题,
原因是:你想通过得到一个返回值去判断数据库写入是否成功(注意:返回只是一个用于判断成功与否的值,而不是一个具有很强业务特征的数据),你既然已经使用了数据库外部语言,从设计上应该更多在java去判断,而不要太多地去耦合database层(从分层架构来看).
你可以这样做:
在发送insert的java method里,去捕捉异常,如果没有异常刚返回1,否则在catch execption里return 0;