首先是函数:
CREATE OR REPLACE FUNCTION fun_get_cust_info(prod_num varchar2,prod_type varchar2)
RETURN CUST_INFO
AS
r_prod_inst_num varchar2(30);
r_cust_code varchar2(30);
r_cust_name varchar2(100);
r_cust_srv_level_tp varchar2(20);
r_cust_type varchar2(20);
r_sub_stat_tp varchar2(20);
r_install_date date;
r_uninstall_date date;
c number;
result CUST_INFO;
num varchar2(4000);
BEGIN
if prod_num is null then
return null;
end if;
select count(*) into c from nri_crm.ODS_ALL_CUSTOMER where PROD_INST_NUM=prod_num;
if c>0 then
select PROD_INST_NUM, CUST_CODE , CUST_NAME , CUST_SRV_LEVEL_TP , CUST_TYPE , SUB_STAT_TP ,
INSTALL_DATE, UNINSTALL_DATE
into r_prod_inst_num, r_cust_code, r_cust_name, r_cust_srv_level_tp,
r_cust_type,r_sub_stat_tp,r_install_date,r_uninstall_date
from nri_crm.ODS_ALL_CUSTOMER where PROD_INST_NUM=prod_num and rownum=1;
result := new CUST_INFO(r_prod_inst_num, r_cust_code, r_cust_name, r_cust_srv_level_tp,r_cust_type,r_sub_stat_tp,r_install_date,r_uninstall_date);
return result;
end if;
num :=''; if num='' or num is null then
return null;
end if;
select PROD_INST_NUM, CUST_CODE , CUST_NAME , CUST_SRV_LEVEL_TP , CUST_TYPE , SUB_STAT_TP ,
INSTALL_DATE, UNINSTALL_DATE
into r_prod_inst_num, r_cust_code, r_cust_name, r_cust_srv_level_tp,
r_cust_type,r_sub_stat_tp,r_install_date,r_uninstall_date
from nri_crm.ODS_ALL_CUSTOMER where PROD_INST_NUM=num and rownum=1;
if r_prod_inst_num is not null and r_prod_inst_num!='' then
result := new CUST_INFO(r_prod_inst_num, r_cust_code, r_cust_name, r_cust_srv_level_tp,r_cust_type,r_sub_stat_tp,r_install_date,r_uninstall_date);
else
return null;
end if;
return result;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
用SQL语句在PL/SQL中select fun_get_cust_info('prod_num',prod_type') from dual成功
用C#怎么实现
CREATE OR REPLACE FUNCTION fun_get_cust_info(prod_num varchar2,prod_type varchar2)
RETURN CUST_INFO
AS
r_prod_inst_num varchar2(30);
r_cust_code varchar2(30);
r_cust_name varchar2(100);
r_cust_srv_level_tp varchar2(20);
r_cust_type varchar2(20);
r_sub_stat_tp varchar2(20);
r_install_date date;
r_uninstall_date date;
c number;
result CUST_INFO;
num varchar2(4000);
BEGIN
if prod_num is null then
return null;
end if;
select count(*) into c from nri_crm.ODS_ALL_CUSTOMER where PROD_INST_NUM=prod_num;
if c>0 then
select PROD_INST_NUM, CUST_CODE , CUST_NAME , CUST_SRV_LEVEL_TP , CUST_TYPE , SUB_STAT_TP ,
INSTALL_DATE, UNINSTALL_DATE
into r_prod_inst_num, r_cust_code, r_cust_name, r_cust_srv_level_tp,
r_cust_type,r_sub_stat_tp,r_install_date,r_uninstall_date
from nri_crm.ODS_ALL_CUSTOMER where PROD_INST_NUM=prod_num and rownum=1;
result := new CUST_INFO(r_prod_inst_num, r_cust_code, r_cust_name, r_cust_srv_level_tp,r_cust_type,r_sub_stat_tp,r_install_date,r_uninstall_date);
return result;
end if;
num :=''; if num='' or num is null then
return null;
end if;
select PROD_INST_NUM, CUST_CODE , CUST_NAME , CUST_SRV_LEVEL_TP , CUST_TYPE , SUB_STAT_TP ,
INSTALL_DATE, UNINSTALL_DATE
into r_prod_inst_num, r_cust_code, r_cust_name, r_cust_srv_level_tp,
r_cust_type,r_sub_stat_tp,r_install_date,r_uninstall_date
from nri_crm.ODS_ALL_CUSTOMER where PROD_INST_NUM=num and rownum=1;
if r_prod_inst_num is not null and r_prod_inst_num!='' then
result := new CUST_INFO(r_prod_inst_num, r_cust_code, r_cust_name, r_cust_srv_level_tp,r_cust_type,r_sub_stat_tp,r_install_date,r_uninstall_date);
else
return null;
end if;
return result;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
用SQL语句在PL/SQL中select fun_get_cust_info('prod_num',prod_type') from dual成功
用C#怎么实现
具体代码好吗
OracleConnection conn = new OracleConnection();
conn.ConnectionString = "Data Source=test;user=test;password=test";
OracleCommand cmd = new OracleCommand("select fun_get_cust_info('001','001') from dual", conn); OracleDataAdapter da = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds); //在这里报错 遇到不支持的 Oracle 数据类型 USERDEFINED
改成
select dbo.fun_get_cust_info('001','001'
conn.ConnectionString = "Data Source=test;user=test;password=test";
OledbCommand cmd = new OledbCommand("select fun_get_cust_info('001','001') from dual", conn); OledbDataAdapter da = new OledbDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
试试这个看行吗?
首先,ORCLE 好像不能像SQLSERVER一樣在FUN和SP中返回一個查詢結果(也就是一個表),但是它提供一個解決方案,可以返回XML,網上找一下DBMS_XMLQUERY.GETXML的資料
然後才是C#的部分
IS
BEGIN
src := DBMS_XMLQUERY.GETXML('SELECT * FROM tab1');
END;
C# 關鍵代碼取得SP返回的XML字符串,剩下的自已搞定吧ocom.CommandText = "xmltest";
Oracle.DataAccess.Client.OracleParameter op = new Oracle.DataAccess.Client.OracleParameter("src",Oracle.DataAccess.Client.OracleDbType.Clob);
op.Direction = ParameterDirection.Output;
ocom.Parameters.Add(op);
//System.Xml.XmlReader xr = ocom.ExecuteXmlReader();
ocom.ExecuteNonQuery();
Oracle.DataAccess.Types.OracleClob c = (Oracle.DataAccess.Types.OracleClob)op.Value;
string s = c.Value;C#代碼臨時寫的,供參考。
在函数中加了CREATE OR REPLACE TYPE CUST_INFO_T AS TABLE OF CUST_INFO
将函数输出转化为表
再在代码中 select * from table (fun_get_cust_info('prod_num',prod_type'))
这个结果直接填入dataSet成功谢谢大家