对c#,连数据库不熟悉,想用C#编个前台程序调用oracle的存储过程,请求指点,最好帮忙写好C#代码,感恩不尽!CREATE OR REPLACE PROCEDURE P_STATUS_MSISDN(CURDATE IN VARCHAR2,
MESSAGE OUT VARCHAR2) IS
V_TABLE_NAMEA VARCHAR2(50);
V_SQLSTRING VARCHAR2(500);
V_YEARA VARCHAR2(4);
V_MONTHA VARCHAR2(3);
V_DAYA VARCHAR2(3);
V_TABLE_NAMEB VARCHAR2(50);
V_YEARB VARCHAR2(4);
V_MONTHB VARCHAR2(3);
V_DAYB VARCHAR2(3);
V_INT_FLG INTEGER; V_FIRST_DATE VARCHAR2(10); --入参月份第一天
V_CURMONTH_DAYS INTEGER; --入参月份最大天数
/******************************************************************************/
/* */
/* 输入日期 自动计算日期之后7天的sql,自动判断跨月、跨年 */
/* 统计结果写入临时表 */
/* 入参:时间YYYYMMDD */
/* 出参:信息提示 */
/* 2011-1-4 zhangmingzhi */
/* */
/******************************************************************************/
BEGIN
--判断入参是否正确
V_INT_FLG := 0;
V_SQLSTRING := 'select to_date(' || CURDATE || ',''yyyymmdd'') from dual';
BEGIN
EXECUTE IMMEDIATE V_SQLSTRING;
EXCEPTION
WHEN OTHERS THEN
MESSAGE := '日期输入错误';
V_INT_FLG := 1;
END;
IF V_INT_FLG = 0 THEN
BEGIN
V_YEARA := SUBSTR(CURDATE, 0, 4);
V_MONTHA := SUBSTR(CURDATE, 5, 2);
V_DAYA := SUBSTR(CURDATE, 7, 2);
V_TABLE_NAMEA := 'UCR_BC1.TG_CDR' || V_MONTHA || '@NGBIL';
V_FIRST_DATE := V_YEARA || V_MONTHA || '01';
V_SQLSTRING := 'select to_char(last_day(to_date(''' || V_FIRST_DATE ||
''',''YYYY-MM-DD'')),''DD'') from dual';
EXECUTE IMMEDIATE V_SQLSTRING
INTO V_CURMONTH_DAYS;
--创建临时表
V_SQLSTRING := 'Drop TABLE t_msisdn';
BEGIN
EXECUTE IMMEDIATE V_SQLSTRING;
EXCEPTION
WHEN OTHERS THEN
MESSAGE := -SQLCODE || ' ' || SQLERRM;
END;
IF TO_NUMBER(V_DAYA) + 6 > V_CURMONTH_DAYS THEN
--跨月
V_MONTHB := to_char(TO_NUMBER(V_MONTHA) + 1,'00');
V_YEARB := V_YEARA;
V_DAYB := TRIM(TO_CHAR(6 - (V_CURMONTH_DAYS - TO_NUMBER(V_DAYA)),'00'));
IF TO_NUMBER(V_MONTHA) = 12 THEN
--跨年
V_YEARB := TO_NUMBER(V_YEARA) + 1;
V_MONTHB := '01';
END IF;
V_TABLE_NAMEB := 'UCR_BC1.TG_CDR' || V_MONTHB || '@NGBIL';
------------------------------------------------------------
V_SQLSTRING := 'create table t_msisdn as (SELECT CITY, COUNT(DISTINCT MSISDN) CNT_MSISDN' ||
' FROM (SELECT B.CITY, A.MSISDN ' ||
' FROM ' || V_TABLE_NAMEA || ' A, JZ_LTX B ' ||
' WHERE START_DATE BETWEEN ' || CURDATE || ' AND ' || v_yeara || v_montha || to_char(V_CURMONTH_DAYS) ||
' AND A.LAC1 = B.LAC ' ||
' AND A.CELL_ID1 = B.CI ' ||
' UNION ' ||
' SELECT B.CITY, A.MSISDN ' ||
' FROM ' || V_TABLE_NAMEB || ' A, JZ_LTX B ' ||
' WHERE START_DATE BETWEEN ' || v_yearb || v_monthb || '01' || ' AND ' || v_yearb || v_monthb || V_DAYB ||
' AND A.LAC1 = B.LAC ' ||
' AND A.CELL_ID1 = B.CI) ' ||
' GROUP BY CITY)';
ELSE
V_DAYB := TRIM(to_char(to_number(v_daya) + 6,'00'));
V_SQLSTRING := 'create table t_msisdn as (SELECT B.CITY CITY, COUNT(DISTINCT A.MSISDN) CNT_MSISDN' ||
' FROM ' || V_TABLE_NAMEA || ' A, JZ_LTX B ' ||
' WHERE START_DATE BETWEEN ' || CURDATE || ' AND ' || v_yeara || v_montha || V_DAYB ||
' AND A.LAC1 = B.LAC ' ||
' AND A.CELL_ID1 = B.CI ' ||
' GROUP BY B.CITY)';
END IF;
BEGIN
EXECUTE IMMEDIATE V_SQLSTRING;
MESSAGE := 'Success! please select * from t_msisdn !';
EXCEPTION
WHEN OTHERS THEN
MESSAGE := -SQLCODE || ' ' || SQLERRM;
END;
END;
END IF;
END P_STATUS_MSISDN;
/
MESSAGE OUT VARCHAR2) IS
V_TABLE_NAMEA VARCHAR2(50);
V_SQLSTRING VARCHAR2(500);
V_YEARA VARCHAR2(4);
V_MONTHA VARCHAR2(3);
V_DAYA VARCHAR2(3);
V_TABLE_NAMEB VARCHAR2(50);
V_YEARB VARCHAR2(4);
V_MONTHB VARCHAR2(3);
V_DAYB VARCHAR2(3);
V_INT_FLG INTEGER; V_FIRST_DATE VARCHAR2(10); --入参月份第一天
V_CURMONTH_DAYS INTEGER; --入参月份最大天数
/******************************************************************************/
/* */
/* 输入日期 自动计算日期之后7天的sql,自动判断跨月、跨年 */
/* 统计结果写入临时表 */
/* 入参:时间YYYYMMDD */
/* 出参:信息提示 */
/* 2011-1-4 zhangmingzhi */
/* */
/******************************************************************************/
BEGIN
--判断入参是否正确
V_INT_FLG := 0;
V_SQLSTRING := 'select to_date(' || CURDATE || ',''yyyymmdd'') from dual';
BEGIN
EXECUTE IMMEDIATE V_SQLSTRING;
EXCEPTION
WHEN OTHERS THEN
MESSAGE := '日期输入错误';
V_INT_FLG := 1;
END;
IF V_INT_FLG = 0 THEN
BEGIN
V_YEARA := SUBSTR(CURDATE, 0, 4);
V_MONTHA := SUBSTR(CURDATE, 5, 2);
V_DAYA := SUBSTR(CURDATE, 7, 2);
V_TABLE_NAMEA := 'UCR_BC1.TG_CDR' || V_MONTHA || '@NGBIL';
V_FIRST_DATE := V_YEARA || V_MONTHA || '01';
V_SQLSTRING := 'select to_char(last_day(to_date(''' || V_FIRST_DATE ||
''',''YYYY-MM-DD'')),''DD'') from dual';
EXECUTE IMMEDIATE V_SQLSTRING
INTO V_CURMONTH_DAYS;
--创建临时表
V_SQLSTRING := 'Drop TABLE t_msisdn';
BEGIN
EXECUTE IMMEDIATE V_SQLSTRING;
EXCEPTION
WHEN OTHERS THEN
MESSAGE := -SQLCODE || ' ' || SQLERRM;
END;
IF TO_NUMBER(V_DAYA) + 6 > V_CURMONTH_DAYS THEN
--跨月
V_MONTHB := to_char(TO_NUMBER(V_MONTHA) + 1,'00');
V_YEARB := V_YEARA;
V_DAYB := TRIM(TO_CHAR(6 - (V_CURMONTH_DAYS - TO_NUMBER(V_DAYA)),'00'));
IF TO_NUMBER(V_MONTHA) = 12 THEN
--跨年
V_YEARB := TO_NUMBER(V_YEARA) + 1;
V_MONTHB := '01';
END IF;
V_TABLE_NAMEB := 'UCR_BC1.TG_CDR' || V_MONTHB || '@NGBIL';
------------------------------------------------------------
V_SQLSTRING := 'create table t_msisdn as (SELECT CITY, COUNT(DISTINCT MSISDN) CNT_MSISDN' ||
' FROM (SELECT B.CITY, A.MSISDN ' ||
' FROM ' || V_TABLE_NAMEA || ' A, JZ_LTX B ' ||
' WHERE START_DATE BETWEEN ' || CURDATE || ' AND ' || v_yeara || v_montha || to_char(V_CURMONTH_DAYS) ||
' AND A.LAC1 = B.LAC ' ||
' AND A.CELL_ID1 = B.CI ' ||
' UNION ' ||
' SELECT B.CITY, A.MSISDN ' ||
' FROM ' || V_TABLE_NAMEB || ' A, JZ_LTX B ' ||
' WHERE START_DATE BETWEEN ' || v_yearb || v_monthb || '01' || ' AND ' || v_yearb || v_monthb || V_DAYB ||
' AND A.LAC1 = B.LAC ' ||
' AND A.CELL_ID1 = B.CI) ' ||
' GROUP BY CITY)';
ELSE
V_DAYB := TRIM(to_char(to_number(v_daya) + 6,'00'));
V_SQLSTRING := 'create table t_msisdn as (SELECT B.CITY CITY, COUNT(DISTINCT A.MSISDN) CNT_MSISDN' ||
' FROM ' || V_TABLE_NAMEA || ' A, JZ_LTX B ' ||
' WHERE START_DATE BETWEEN ' || CURDATE || ' AND ' || v_yeara || v_montha || V_DAYB ||
' AND A.LAC1 = B.LAC ' ||
' AND A.CELL_ID1 = B.CI ' ||
' GROUP BY B.CITY)';
END IF;
BEGIN
EXECUTE IMMEDIATE V_SQLSTRING;
MESSAGE := 'Success! please select * from t_msisdn !';
EXCEPTION
WHEN OTHERS THEN
MESSAGE := -SQLCODE || ' ' || SQLERRM;
END;
END;
END IF;
END P_STATUS_MSISDN;
/
DataSet ds = new DataSet();
scParameter.ds = ds;
scParameter.commandtext = "STATUS_PACKAGE.P_STATUS_MSISDN";
scParameter.commandtype = CommandType.StoredProcedure;一般oracle的存储过程放到包头 包体里面的吧,调用类似于MSSQL
OracleCommand cmd = new OracleCommand("",con);
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter p1 = new OracleParameter("参数", OracleType.Cursor);
p1.Direction = System.Data.ParameterDirection.Input;
p1.value="";
cmd.ExecuteNonQuery();