c#执行Oracle存储过程

1.不返回结果集，有输入，输出参数

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="sql">执行的sql语句</param>
        /// <param name="sql2">执行的sql语句2</param>
        /// <returns></returns>
        public static bool RunProcedure(string storedProcName, string sql, string sql2)
        {
            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                connection.Open();
                OracleCommand sqlCmd = new OracleCommand(storedProcName, connection);
                int state =0;
                OracleParameter[] paras ={
                                new OracleParameter("p_sql",sql),
                                new OracleParameter("p_sql2",sql2),
                                new OracleParameter("p_state",state) //返回值 0表示失败 1表示成功
                                        };
                paras[2].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.AddRange(paras);
                sqlCmd.CommandType = CommandType.StoredProcedure;//设置 使用存储过程
                sqlCmd.ExecuteNonQuery();
                connection.Close();

                state = Convert.ToInt32(paras[2].Value);
                return state == 1 ? true : false;
            }
        }




存储过程：

create or replace procedure TCZ_Operation
(
p_sql in varchar2, --sql语句
p_sql2 in varchar2, --sql语句
p_state out integer -- 返回值 0是失败1是成功

)
is

begin

execute immediate p_sql; --执行sql语句
execute immediate p_sql2; --执行sql语句
commit;
p_state:=1;
DBMS_OUTPUT.put_line(p_state);
EXCEPTION
when others then
rollback;
p_state:=0;
DBMS_OUTPUT.put_line(p_state);
end;

 

2.返回结果集有输入输出参数

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="TableName">表名</param>
        /// <param name="strWhere">查询条件</param>
        /// <param name="orderColumn">排序的列</param>
        /// <param name="orderStyle">排序方式</param>
        /// <param name="pageIndex">当前页</param>
        /// <param name="pageSize">每页显示记录条数</param>
        /// <param name="rowCount">总记录数</param>
        /// <param name="pageCount">总页数</param>
        /// <returns></returns>
        public static DataSet RunProcedure(string storedProcName, string TableName, string strWhere, string orderColumn, string orderStyle, int pageIndex, int pageSize, out int rowCount, out int pageCount)
        {
            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                DataSet dataSet = new DataSet();
                connection.Open();
                OracleDataAdapter sqlDA = new OracleDataAdapter(storedProcName, connection);
                rowCount = 0;
                pageCount = 0;
                OracleParameter[] paras ={
                                new OracleParameter("p_tableName",TableName),
                                new OracleParameter("p_strWhere",strWhere),
                                new OracleParameter("p_orderColumn",orderColumn),
                                new OracleParameter("p_orderStyle",orderStyle),
                                new OracleParameter("p_curPage",pageIndex),
                                new OracleParameter("p_pageSize",pageSize),
                                new OracleParameter("p_totalRecords",rowCount),
                                new OracleParameter("p_totalPages",pageCount),
                                new OracleParameter("v_cur", OracleType.Cursor) //返回的游标
                 
                                        };

                paras[6].Direction = ParameterDirection.Output;
                paras[7].Direction = ParameterDirection.Output;
                paras[8].Direction = ParameterDirection.Output;
                sqlDA.SelectCommand.Parameters.AddRange(paras);
                sqlDA.SelectCommand.CommandType = CommandType.StoredProcedure;//设置 使用存储过程
                sqlDA.Fill(dataSet);
                connection.Close();

                rowCount = Convert.ToInt32(paras[6].Value);
                pageCount = Convert.ToInt32(paras[7].Value);
                
                return dataSet;
            }
        }



创建包：

CREATE OR REPLACE PACKAGE pkg_query AS TYPE cur_query IS REF CURSOR;
END pkg_query;

存储过程：

create or replace procedure Sp_GetPageList
(p_tableName in varchar2, --表名
p_strWhere in varchar2, --查询条件
p_orderColumn in varchar2, --排序的列
p_orderStyle in varchar2, --排序方式
p_curPage in out Number, --当前页
p_pageSize in out Number, --每页显示记录条数
p_totalRecords out Number, --总记录数
p_totalPages out Number, --总页数
v_cur out pkg_query.cur_query) --返回的结果集

is
v_sql VARCHAR2(2000) := ''; --sql语句
v_startRecord Number(4); --开始显示的记录条数
v_endRecord Number(4); --结束显示的记录条数
BEGIN
--记录中总记录条数
v_sql := 'SELECT TO_NUMBER(COUNT(*)) FROM ' || p_tableName || ' WHERE 1=1';
IF p_strWhere IS NOT NULL or p_strWhere <> '' THEN
v_sql := v_sql || p_strWhere;
END IF;
EXECUTE IMMEDIATE v_sql INTO p_totalRecords;

--验证页面记录大小
IF p_pageSize < 0 THEN
p_pageSize := 0;
END IF;

--根据页大小计算总页数
IF MOD(p_totalRecords,p_pageSize) = 0 THEN
p_totalPages := p_totalRecords / p_pageSize;
ELSE
p_totalPages := p_totalRecords / p_pageSize + 1;
END IF;

--验证页号
IF p_curPage < 1 THEN
p_curPage := 1;
END IF;
IF p_curPage > p_totalPages THEN
p_curPage := p_totalPages;
END IF;

--实现分页查询
v_startRecord := (p_curPage - 1) * p_pageSize + 1;
v_endRecord := p_curPage * p_pageSize;
v_sql := 'SELECT * FROM (SELECT A.*, rownum r FROM ' ||
'(SELECT * FROM ' || p_tableName;
IF p_strWhere IS NOT NULL or p_strWhere <> '' THEN
v_sql := v_sql || ' WHERE 1=1' || p_strWhere;
END IF;
IF p_orderColumn IS NOT NULL or p_orderColumn <> '' THEN
v_sql := v_sql || ' ORDER BY ' || p_orderColumn || ' ' || p_orderStyle;
END IF;
v_sql := v_sql || ') A WHERE rownum <= ' || v_endRecord || ') B WHERE r >= '
|| v_startRecord;
DBMS_OUTPUT.put_line(v_sql);
OPEN v_cur FOR v_sql;
END Sp_GetPageList;