110,500
社区成员
发帖
与我相关
我的任务
分享
/* 定义视图参数:目前用到的三个参数(点检类型:DJLX,站点ID:SITEID,岗位代码:GWDM)*/
CREATE
OR REPLACE PACKAGE p_view_param IS --站点ID
FUNCTION set_siteId (siteId VARCHAR2) RETURN VARCHAR2 ; FUNCTION get_siteId RETURN VARCHAR2 ; --点检类型
FUNCTION set_djlx (djlx VARCHAR2) RETURN VARCHAR2 ; FUNCTION get_djlx RETURN VARCHAR2 ; --岗位代码
FUNCTION set_gwdm (gwdm VARCHAR2) RETURN VARCHAR2 ; FUNCTION get_gwdm RETURN VARCHAR2 ;
END p_view_param ;
/*创建包体,处理get,set方法*/
CREATE OR REPLACE PACKAGE BODY p_view_param IS
paramSiteId VARCHAR2(20); --站点ID
paramDjlx VARCHAR2(1); --点检类型
paramGwdm VARCHAR2(20); --岗位代码
/*站点ID赋值*/
FUNCTION set_siteId (siteId VARCHAR2) RETURN VARCHAR2 IS
BEGIN
paramSiteId:= siteId; RETURN siteId;
END; /*站点ID取值*/
FUNCTION get_siteId RETURN VARCHAR2 IS
BEGIN
RETURN paramSiteId;
END; /*点检类型赋值*/
FUNCTION set_djlx (djlx VARCHAR2) RETURN VARCHAR2 IS
BEGIN
paramDjlx:= djlx; RETURN djlx;
END; /*点检类型取值*/
FUNCTION get_djlx RETURN VARCHAR2 IS
BEGIN
RETURN paramDjlx;
END; /*岗位代码赋值*/
FUNCTION set_gwdm (gwdm VARCHAR2) RETURN VARCHAR2 IS
BEGIN
paramGwdm:= gwdm; RETURN gwdm;
END; /*岗位代码取值*/
FUNCTION get_gwdm RETURN VARCHAR2 IS
BEGIN
RETURN paramGwdm;
END;
END p_view_param;
create or replace view v_wms_searchXMBZ as
with tmp as (
select DJ_SBXMB.*,
length(DJ_SBXMB.XMZQ)-length(regexp_replace(DJ_SBXMB.XMZQ, ',', ''))+1 len
from DJ_SBXMB WHERE DJ_SBXMB.SITEID=p_view_param.get_siteId() and DJ_SBXMB.DJLX=p_view_param.get_djlx() and DJ_SBXMB.GWDM=p_view_param.get_gwdm()
)
select a.*, regexp_substr(XMZQ, '[^,]+', 1, rn) ZQ
from tmp a, (select rownum rn from dual connect by level <= (select max(len) from tmp x)) b
where a.len>=b.rn
order by 1
string sql =string.Format("select * from V_WMS_SEARCHXMBZ where p_view_param.set_siteId('{0}')='{1}' and p_view_param.set_djlx('{2}')='{3}' and p_view_param.set_gwdm('{4}')='{5}'",model.SiteId,model.SiteId,model.DJLX,model.DJLX,model.GWDM,model.GWDM);
var dt = OracleDbHelper.QueryTable(sql);
public static DataTable QueryTable(string sqlString)
{
using (var connection = new OracleProvider().GetConn(OracleDbHelper.CONNECTION_STRING_NAME))
{
var ds = new DataSet();
try
{
connection.Open();
var command = new OracleDataAdapter(sqlString, connection);
command.Fill(ds, "ds");
return ds.Tables.Count < 1 ? null : ds.Tables[0];
}
catch (Exception e)
{
throw e;
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
}
}