Oracle2

create or replace procedure Pro_GetAllUsers(cur_usr out sys_refcursor) is
begin
  open cur_usr for
  select userid ,
         adder as 创建者,
         to_char(addtime,'yyyy-mm-dd') as 创建时间,
         moder as 修改者,
         to_char(modtime,'yyyy-mm-dd') as 修改时间,
         delstatus as 状态, deptid as 部门,
         indexid as 排序号,
         chinaname as 姓名,
         loginname as 登录名,
         mail as 邮件,
         mobile as 移动电话,
         phonedept as 部门电话,
         phonehome as 家庭电话,
         sex as 性别,
         specialrole as 特定角色,
         to_char(birth,'yyyy-mm-dd') as 出生日期,
         permstring as 权限,
         remark as 备注,
         prefix as 称谓,
         jobgrade as 职称,
         personroles as 角色,
         blnmaster as 是否主用户,
         relationusr as 从用户,
         blncanhandwrite as 签批权限,
         blncanremindbymsg as 短信提醒,
         usrtheme as 系统皮肤,
         nisnewshortinfo as 网络寻呼,
         wordclipboard as 词汇剪贴板,
         unitid as 单位名称,
         surpervise as 监察权限,
         expersonroles as 扩展角色
    from usr;  
end Pro_GetAllUsers;

 

调用：

OracleParameter oraPara = new OracleParameter("cur_usr", OracleType.Cursor);
oraPara.Direction = ParameterDirection.Output;//设置为Output

return OrclDataBase.getDataSet(CommandType.StoredProcedure, SqlText, oraPara);

 

public static DataSet getDataSet(CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
        {
            using (OracleConnection connection = new OracleConnection(ConnectionString))
            {
                OracleCommand cmd = new OracleCommand();
                PrepareCommand(cmd, cmdType, connection, cmdText, commandParameters);
                DataSet ds = new DataSet();
                OracleDataAdapter da = new OracleDataAdapter(cmd);
                da.Fill(ds);
                cmd.Parameters.Clear();
                return ds;
            }
        }

 

 private static void PrepareCommand(OracleCommand cmd, CommandType cmdType, OracleConnection conn, string cmdText, OracleParameter[] commandParameters)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();

            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            cmd.CommandType = cmdType;

            if (commandParameters != null)
            {
                foreach (OracleParameter parm in commandParameters)
                    cmd.Parameters.Add(parm);
            }
        }