送100分 现场调试,现场结贴 Oracle 下 SQL 语句返回结果不对

Question

public int AddRole(TAIS.Model.Privilege.RoleInfo role)
        {
            string sql = "DECLARE
                          num number := 0;
                          begin
                          select count(*)
                            into num
                            from irole
                           where RoleName = :RoleName
                             and GroupId = :GroupId;
                          if num = 0 then
                            insert into IRole
                              (RoleID, RoleName, GroupId, CreateTime, UpdateTime, Description)
                            values
                              (IROLESEQ.NEXTVAL,
                               :RoleName,
                               :GroupID,
                               :CreateTime,
                               :UpdateTime,
                               :Description);
                          else
                            return;
                          end if;
                          end; ";         
            OracleParameter[] paras = { 
                     new OracleParameter(":RoleName", role.RoleName),
                     new OracleParameter(":GroupID", role.GroupID),
                     new OracleParameter(":CreateTime", DateTime.Now),
                     new OracleParameter(":UpdateTime", DateTime.Now),
                     new OracleParameter(":Description", role.Description)};
            try
            {
                int result = DBUtility.OracleHelper.ExecuteNonQuery(con, CommandType.Text, sql, paras);
                return result;
            }
            catch (Exception e)
            {
                throw new ApplicationException(e.Message);            }
        }执行insert into时返回1，不执行insert into时不想让他返回1;但现在都是返回1;不知怎么解决,恳请大哥大姐指点迷津,小弟唯有以分和仰慕之情向谢.谢谢,在线等,恩

Answer 1

PS:我的结贴率是100%,CSDN数据库有Bug

Answer 2

把sql 语句改成存储过程，然后调用，根据返回值的不同来判断不是更好吗
                       
create or replace procedure aaaa(      p_RoleName    varchar2,
                                       p_GroupID varchar2,
                                       p_CreateTime varchar2,
                                       p_UpdateTime   varchar2,
                                       p_Description   varchar2,
                                       p_RETURN IN  out integer) as   
num number := 0;
begin
   p_RETURN:=0;
  select count(*)
    into num
    from irole
   where RoleName = P_RoleName
     and GroupId = P_GroupId;
  if num = 0 then
     p_RETURN:=1;
      insert into IRole
        (RoleID, RoleName, GroupId, CreateTime, UpdateTime, Description)
      values
        (IROLESEQ.NEXTVAL,
         P_RoleName,
         P_GroupID,
         P_CreateTime,
         P_UpdateTime,
         P_Description);
  else
     return ;   
  end if;   
end aaaa; 程序再调用过程

Answer 3

create or replace procedure aaaa(      p_RoleName    varchar2,
                                       p_GroupID varchar2,
                                       p_CreateTime varchar2,
                                       p_UpdateTime   varchar2,
                                       p_Description   varchar2,
                                       p_RETURN IN  out integer) as   
num number := 0;
begin
   p_RETURN:=0;
  select count(*)
    into num
    from irole
   where RoleName = P_RoleName
     and GroupId = P_GroupId;
  if num = 0 then
     p_RETURN:=1;
      insert into IRole
        (RoleID, RoleName, GroupId, CreateTime, UpdateTime, Description)
      values
        (IROLESEQ.NEXTVAL,
         P_RoleName,
         P_GroupID,
         P_CreateTime,
         P_UpdateTime,
         P_Description);  
  end if;   
  
  commit;
  
end aaaa; 

Answer 4

回djs00717 大哥:听您的,用存储过程,可是报了以下的错误:
ORA-06550: 
PLS-00103: Encountered the symbol "" when expecting one of the following:
:= . ( @ % ;The symbol "" was ignored.小弟愚钝,还望大哥耐心赐教,小弟唯有以分向谢,谢谢;在线等您

Answer 5

-- 那是JAVA应用程序层的事，和ORACLE没关系，当然你也可以写成存储过程的形式,试试看:
CREATE OR REPLACE PROCEDURE TEST_PROC(P_ROLENAME    VARCHAR2,
                                      P_GROUPID     VARCHAR2,
                                      P_CREATETIME  VARCHAR2,
                                      P_UPDATETIME  VARCHAR2,
                                      P_DESCRIPTION VARCHAR2,
                                      P_RETURN      OUT NUMBER) AS
  NUM NUMBER := 0;
BEGIN
  P_RETURN := 0;
  SELECT COUNT(*)
    INTO NUM
    FROM IROLE
   WHERE ROLENAME = P_ROLENAME
     AND GROUPID = P_GROUPID;
  IF NUM = 0 THEN
    INSERT INTO IROLE
      (ROLEID, ROLENAME, GROUPID, CREATETIME, UPDATETIME, DESCRIPTION)
    VALUES
      (IROLESEQ.NEXTVAL,
       P_ROLENAME,
       P_GROUPID,
       P_CREATETIME,
       P_UPDATETIME,
       P_DESCRIPTION);
  
    COMMIT;
  
    P_RETURN := 1;
  END IF;END TEST_PROC;

Answer 6

java不太懂啊
建议建议换个思路
你再sql段写个返回值，如果插入了返回1，未插入返回0
再你的java抛出异常那段做判断 if 返回值=1 then return 1

Answer 7

给你我的代码，执行是没有问题的，你自己比对一下。
java:
public static void main(String[] args) {
String user = "SCOTT";
String password = "TIGER";
String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@192.168.1.229:1521:orcl"; try {
Class.forName(driver).newInstance();
Connection conn = DriverManager.getConnection(url, user, password); conn.setAutoCommit(false);
CallableStatement cstat = conn
.prepareCall("{ call scott.aaaa(?,?,?,?,?,?) }");
cstat.setString(1, "12");
cstat.setString(2, "12");
cstat.setString(3, "12");
cstat.setString(4, "12");
cstat.setString(5, "12");
cstat.registerOutParameter(6, Types.INTEGER);// Types.OTHER); cstat.execute(); int x = cstat.getInt(6); System.out.println(x); cstat.close();
conn.close(); } catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} }oracle的存储过程：
create or replace procedure aaaa(      p_RoleName    varchar2,
                                      p_GroupID varchar2, 
                                      p_CreateTime varchar2, 
                                      p_UpdateTime  varchar2, 
                                      p_Description  varchar2, 
                                      p_RETURN IN  out integer) as  
num number := 0; 
begin 
  p_RETURN:=0; 
  select count(*) 
    into num 
    from irole 
  where RoleName = P_RoleName 
    and GroupId = P_GroupId; 
  if num = 0 then 
    p_RETURN:=1; 
      insert into IRole 
        (RoleID, RoleName, GroupId, CreateTime, UpdateTime, Description) 
      values 
        (IROLESEQ.NEXTVAL, 
        P_RoleName, 
        P_GroupID, 
        P_CreateTime, 
        P_UpdateTime, 
        P_Description); 
  else 
    return ;  
  end if;  
  commit;
end aaaa;