大家好,有个sql的写法在Oracle中怎么写IF not Exists(SELECT 1 FROM SystemRoleFunction where ROLEID='1')
BEGIN
INSERT into SystemRoleFunction(functionId,roleid) values ('123','1'))
end;IF not Exists在Oracle中不支持转为insert when (not exists (SELECT 1 FROM SystemRoleFunction where ROLEID='1' and FUNCTIONID='B0100')) then
into SystemRoleFunction(functionId,roleid) values ('B0100','1');
也提示不对
BEGIN
INSERT into SystemRoleFunction(functionId,roleid) values ('123','1'))
end;IF not Exists在Oracle中不支持转为insert when (not exists (SELECT 1 FROM SystemRoleFunction where ROLEID='1' and FUNCTIONID='B0100')) then
into SystemRoleFunction(functionId,roleid) values ('B0100','1');
也提示不对
DECLARE
L_X INT;
BEGIN
SELECT COUNT(*) INTO L_X FROM SYSTEMROLEFUNCTION WHERE ROLEID = '1';
IF L_X > 0 THEN
INSERT INTO SYSTEMROLEFUNCTION
(FUNCTIONID, ROLEID)
VALUES
('123', '1');
END IF;
COMMIT;
END;
/
INSERT into SystemRoleFunction(functionId,roleid) values ('123','1'))
when not Exists(SELECT 1 FROM SystemRoleFunction where ROLEID='1')
谢谢,不过报错
SQL> INSERT into SystemRoleFunction(functionId,roleid) values ('123','1')
2 when not Exists(SELECT 1 FROM SystemRoleFunction where ROLEID='1');
when not Exists(SELECT 1 FROM SystemRoleFunction where ROLEID='1')
*
第 2 行出现错误:
ORA-00933: SQL 命令未正确结束
谢谢,这个可以
不过我下面这个又出现点错误,如果分行写是可以的,
insert into SystemRoleFunction(ROLEID,FUNCTIONID) values ('1','A0101'); DECLARE L_X INT; BEGIN SELECT COUNT(*) INTO L_X FROM SYSTEMROLEFUNCTION WHERE ROLEID='1' and FUNCTIONID='A0100'; IF L_X > 0 THEN INSERT INTO SystemRoleFunction (FUNCTIONID, ROLEID) VALUES('A0100','1'); END IF; END;但是写成一个sql语句里面就提示错误:
insert into SystemRoleFunction(ROLEID,FUNCTIONID) values ('1','A0101'); DECLARE L_X INT; BEGIN SELECT COUNT(*) INTO L_X FROM SYSTEMROLEFUNCTION WHERE ROLEID='1' and FUNCTIONID='A0100'; IF L_X > 0 THEN INSERT INTO SystemRoleFunction (FUNCTIONID, ROLEID) VALUES('A0100','1'); END IF; END;
这个怎么解决啊,谢谢
谢谢,这个可以
不过我下面这个又出现点错误,如果分行写是可以的,
insert into SystemRoleFunction(ROLEID,FUNCTIONID) values ('1','A0101'); DECLARE L_X INT; BEGIN SELECT COUNT(*) INTO L_X FROM SYSTEMROLEFUNCTION WHERE ROLEID='1' and FUNCTIONID='A0100'; IF L_X > 0 THEN INSERT INTO SystemRoleFunction (FUNCTIONID, ROLEID) VALUES('A0100','1'); END IF; END;但是写成一个sql语句里面就提示错误:
insert into SystemRoleFunction(ROLEID,FUNCTIONID) values ('1','A0101'); DECLARE L_X INT; BEGIN SELECT COUNT(*) INTO L_X FROM SYSTEMROLEFUNCTION WHERE ROLEID='1' and FUNCTIONID='A0100'; IF L_X > 0 THEN INSERT INTO SystemRoleFunction (FUNCTIONID, ROLEID) VALUES('A0100','1'); END IF; END;
这个怎么解决啊,谢谢
2楼只是一部分,我原来的语句是在sqlserver的一个存储过程中执行多个操作:
Delete From SystemRoleFunction
WHERE functionId= @functionId and roleid=@roleidIF not Exists(SELECT 1 FROM SystemRoleFunction a ,SystemFunction b
WHERE roleid = @roleid and a.functionid=b.functionid and b.parentId=@parentId )
BEGIN
Delete From SystemRoleFunction
WHERE functionId= @parentId and roleid=@roleid
END所以修改到Oracle中也想一句话在c#中执行,不过就会出错 StringBuilder strSql = new StringBuilder();
strSql.Append("Delete From SystemRoleFunction ");
strSql.Append(" where ROLEID='" + model.ROLEID + "' and FUNCTIONID='" + model.FUNCTIONID + "' "); strSql.Append(" DECLARE");
strSql.Append(" L_X INT;");
strSql.Append(" BEGIN");
strSql.Append(" SELECT 1 INTO L_X FROM SYSTEMROLEFUNCTION WHERE ROLEID='" + model.ROLEID + "' and FUNCTIONID='" + model.FUNCTIONID.Substring(0, 3) + "00" + "';");
strSql.Append(" IF L_X = 0 THEN");
strSql.Append(" INSERT INTO SystemRoleFunction");
strSql.Append(" (FUNCTIONID, ROLEID)");
strSql.Append(" VALUES(");
strSql.Append("'" + model.FUNCTIONID.Substring(0, 3) + "00" + "',");
strSql.Append("'" + model.ROLEID + "');");
strSql.Append(" END IF;");
strSql.Append(" END;"); DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
db.ExecuteNonQuery(dbCommand);当然可以变通的办法,分两次执行也可以,但是不清楚在一个sql语句应该怎么写
select * from
(
select decode(count(*),0,'123',null) f1,
decode(count(*),0,'1',null) f2
from SystemRoleFunction where roleid = '1'
) where f1 is not null或
INSERT into SystemRoleFunction (functionId,roleid)
select '123',1 roleid from dual where not exists (select roleid from SystemRoleFunction where roleid = 1)