要转换为SqlServer版本的 我是做前端开发的,实在不懂这么复杂的sql 求帮忙
二楼oracle版本代码
oracle:
CREATE OR REPLACE
procedure p_smp_login(in_templte_id in varchar2,
in_batch_id in varchar2,
in_scbb in varchar2,
in_scbc in varchar2,
in_jcbb in varchar2,
in_samplename in varchar2,
in_wendu in varchar2,
in_time in varchar2,
in_list_analysis in varchar2,
in_xianghao in varchar2,
in_zhongliang in varchar2,
in_date in varchar2,
in_xh in varchar2,
in_dengji in varchar2,
in_batch_name in varchar2,
in_login_by in varchar2,
out_sampleid out varchar2) is
sample_id varchar2(10);
samp_tmpl_header_id varchar2(50);
u_batch_id varchar2(50);
v_U_SCBB varchar2(30);
v_U_SCBC varchar2(30);
v_U_jcbb varchar2(30);
TEST_SCHEDULE_name varchar2(30);
sample_point_name varchar2(30);
guigezhibiao varchar2(50);
guigebanben varchar2(50);
v_gbproduct varchar2(50); --国标规格指标
v_gbVersion varchar2(50); --国标规格指标版本
v_sample_name varchar2(50);
v_group_id varchar2(50);
sample_point_id varchar2(10);
TEST_ID VARCHAR2(20);
analysis_ID VARCHAR2(50);
analysis_NAME VARCHAR2(50);
analysis_DESC VARCHAR2(50);
WENDU VARCHAR2(50);
SHIJIAN VARCHAR2(50);
v_test_ordernumber number;
v_samplenames varchar2(50);
/* v_REPLICATE_COUNT VARCHAR2(50);
v_COMPONENT_LIST VARCHAR2(50);
v_ORDER_NUM VARCHAR2(50);*/
begin
--SAMPLE 212912
samp_tmpl_header_id := in_templte_id; -- 'HS_CGGHYKH';
u_batch_id := in_batch_id; -- 'HS001';
v_U_SCBB := in_scbb; -- '甲班';
v_U_SCBC := in_scbc; -- '白班';
v_u_jcbb:=in_jcbb;--检测班别
sample_point_name := in_samplename; -- '片烟';
WENDU := in_wendu; -- '23.5';
SHIJIAN := in_time; -- '11:11:11';
v_test_ordernumber := 0;
update INCREMENTS
set lastval = lpad(to_number(lastval) + 1, 10, ' ')
WHERE MAJOR = upper('SAMPLE')
and minor = upper('KEY0')
returning lpad(lastval, 10, ' ') into sample_id;
out_sampleid := sample_id;
select to_char(to_date(in_time, 'yyyy-MM-dd HH24:mi'), 'HH24:mi')
into SHIJIAN
from dual;
select max(t.default_value)
into sample_point_id
from template_fields t
where t.template_id = samp_tmpl_header_id
and t.field_name = 'SAMPLING_POINT';
select max(name)
into sample_point_name
from sample_point
where identity = sample_point_id;
/*select a.u_ggzb, b.PRODUCT_VERSION
into guigezhibiao, guigebanben
from u_batch a, mlp_header b
where a.u_ggzb = b.identity
and a.identity = u_batch_id;*/
/* select a.u_ggzb, lpad(1, 10, ' ') --b.PRODUCT_VERSION
into guigezhibiao, guigebanben
from u_batch a
where a.identity = u_batch_id;*/ --规格指标会有多个版本，以最新版本为准
select max(v.identity), max(v.product_version)
into guigezhibiao, guigebanben
from v_hs_mlp_header v
where v.identity =
(select u_ggzb from u_batch where identity = u_batch_id);
--国标规格指标
select max(v.identity), max(v.product_version)
into v_gbproduct, v_gbVersion
from v_hs_mlp_header v
where v.identity =
(select u_gb_ggzb from u_batch where identity = u_batch_id);
select a.default_value
into TEST_SCHEDULE_name
from template_fields a
where a.field_name = 'TEST_SCHEDULE'
and a.template_id = samp_tmpl_header_id;
select a.default_value
into v_samplenames
from template_fields a
where a.field_name = 'SAMPLE_NAME'
and a.template_id = samp_tmpl_header_id;
select p.name, a.default_value
into v_sample_name, v_group_id
from samp_tmpl_header p, template_fields a
where p.identity = a.template_id
and a.field_name = 'GROUP_ID'
and p.removeflag = 'F'
and p.identity = samp_tmpl_header_id;
--登录区分原烟批次与生产批次
if trim(in_batch_id) is not null then
insert into sample
(id_numeric,
id_text,
PRODUCT,
PRODUCT_VERSION,
gb_product,
gb_product_version,
SAMPLE_NAME,
TEST_SCHEDULE,
GROUP_ID,
U_SCBB,
U_SCBC,
u_jcbb,
U_SAMPLEBATCH,
batch_name,
LOGIN_DATE,
template_id,
sampling_point,
u_sample_point,
status,
ORIGINAL_SAMPLE,
lOGIN_BY,
sampled_date,
recd_date,
date_started,
starter,
DATERESREQ,
TESTS_TO_DO,
standard_version,
CERTIFICATE,
U_SCRQ,
MODIFIED_ON,
MODIFIED_BY,
modifiable,
u_dj,
u_djlx,
u_cd,
u_pz,
u_scfs,
u_nd,
u_yylx,
u_pyxt,U_JYY)
select sample_id,
trim(sample_id) || '-' || v_samplenames || '-' ||
sample_point_name || '-' || to_char(sysdate, 'yyMMdd'),
guigezhibiao,
guigebanben,
v_gbproduct,
v_gbVersion,
v_samplenames,
TEST_SCHEDULE_name,
v_group_id,
v_U_SCBB,
v_U_SCBC,
v_u_jcbb,
u_batch_id,
in_batch_name,
sysdate,
in_templte_id,
sample_point_id,
sample_point_name,
'V',
' 0',
in_login_by,
to_date(in_time, 'yyyy-MM-dd HH24:mi'),
SYSDATE,
SYSDATE,
in_login_by,
SYSDATE,
'1',
' 0',
' 0',
to_date(in_date, 'yyyy-MM-dd'),
SYSDATE,
in_login_by,
'T',
b.dj,
b.djlx,
b.cd,
b.pz,
b.jgfs,
b.nd,
b.yllx,
b.pyxt,B.SCX
from u_batch b
where b.identity = u_batch_id;
else
insert into sample
(id_numeric,
id_text,
PRODUCT,
PRODUCT_VERSION,
gb_product,
gb_product_version,
SAMPLE_NAME,
TEST_SCHEDULE,
GROUP_ID,
U_SCBB,
U_SCBC,
u_jcbb,
U_SAMPLEBATCH,
batch_name,
LOGIN_DATE,
template_id,
sampling_point,
u_sample_point,
status,
ORIGINAL_SAMPLE,
lOGIN_BY,
sampled_date,
recd_date,
date_started,
starter,
DATERESREQ,
TESTS_TO_DO,
standard_version,
CERTIFICATE,
U_SCRQ,
MODIFIED_ON,
MODIFIED_BY,
modifiable,
u_dj,
u_cd,
u_pz,
u_nd,
u_pyxt,U_JYY)
select sample_id,
trim(sample_id) || '-' || v_samplenames || '-' ||
sample_point_name || '-' || to_char(sysdate, 'yyMMdd'),
guigezhibiao,
guigebanben,
v_gbproduct,
v_gbVersion,
v_samplenames,
TEST_SCHEDULE_name,
v_group_id,
v_U_SCBB,
v_U_SCBC,
v_u_jcbb,
u_batch_id,
in_batch_name,
SYSDATE,
in_templte_id,
sample_point_id,
sample_point_name,
'V',
' 0',
in_login_by,
to_date(in_time, 'yyyy-MM-dd HH24:mi'),
SYSDATE,
SYSDATE,
in_login_by,
SYSDATE,
'1',
' 0',
' 0',
to_date(in_date, 'yyyy-MM-dd'),
SYSDATE,
in_login_by,
'T',
b.dj,
b.cd,
b.pz,
b.nd,
b.pyxt,B.SCX
from u_yy_batch b
where b.id = in_batch_name;
end if;
--analysis_ID := 'HS_CGGHYKH';
FOR CUR_ANALY IN (select distinct va.identity COLUMN_VALUE,
va.u_analysis,
va.description,
c.* /* distinct p.name sampletemplename, c.std_test*/
from samp_tmpl_header p,
template_fields a,
test_sched_header b,
test_sched_entry c,
VERSIONED_ANALYSIS VA
where p.identity = a.template_id
and a.field_name = 'TEST_SCHEDULE'
and a.default_value = b.identity
and b.identity = c.identity
AND VA.IDENTITY = C.ANALYSIS_ID
and p.removeflag = 'F'
and b.removeflag = 'F'
and p.identity = samp_tmpl_header_id
and va.identity in
(SELECT COLUMN_VALUE FROM TABLE(CAST(fn_split(in_list_analysis, ',') AS ty_str_split)))
order by c.order_num
) LOOP
for indextemp in 1 .. cur_analy.REPLICATE_COUNT loop
update INCREMENTS
set lastval = lpad(to_number(lastval) + 1, 10, ' ')
WHERE MAJOR = upper('TEST')
and minor = upper('TEST_NUMBER')
returning lpad(lastval, 10, ' ') into TEST_ID;
analysis_ID := CUR_ANALY.COLUMN_VALUE; -- CUR_ANALY.COLUMN_VALUE;
SELECT K.NAME, K.DESCRIPTION
INTO analysis_NAME, analysis_DESC
FROM VERSIONED_ANALYSIS K
WHERE K.IDENTITY = analysis_ID;
/* SELECT C.REPLICATE_COUNT, C.COMPONENT_LIST, C.ORDER_NUM
INTO v_REPLICATE_COUNT, v_COMPONENT_LIST, v_ORDER_NUM
FROM test_sched_entry c
WHERE C.ANALYSIS_ID = analysis_ID;*/
v_test_ordernumber := v_test_ordernumber + 1;
insert into test
(test_number,
analysis,
sample,
test_count,
test_schedule,
group_id,
OLD_STATUS,
STATUS,
DATE_STARTED,
STARTER,
WORKSHEET,
ORDER_NUM,
HAS_RESULT_LIST,
COMPONENT_LIST,
U_ANALDESC,
U_ANALYSIS)
values
(TEST_ID,
analysis_ID,
sample_id,
v_test_ordernumber,
TEST_SCHEDULE_name,
v_group_id,
NULL,
'V',
SYSDATE,
'SYSTEM',
' 0',
lpad(v_test_ordernumber, 10, ' '), --cur_analy.ORDER_NUM,
'T',
cur_analy.COMPONENT_LIST,
cur_analy.description,
cur_analy.u_analysis);
/* FOR CUR IN (select *
from VERSIONED_COMPONENT t
WHERE ANALYSIS = analysis_ID) LOOP*/
FOR CUR IN (SELECT DISTINCT A.*
FROM VERSIONED_COMPONENT A, versioned_c_l_entry B
WHERE A.ANALYSIS = B.ANALYSIS
AND A.NAME = B.NAME
AND A.ANALYSIS = analysis_ID
--AND B.ANALYSIS_VERSION=''
AND B.COMP_LIST = cur_analy.COMPONENT_LIST) LOOP
INSERT INTO RESULT
(TEST_NUMBER,
NAME,
VALUE,
TEXT,
rep_text,
ORDER_NUMBER,
RESULT_TYPE,
UNITS,
MINIMUM,
MAXIMUM,
CALCULATION,
PLACES,
STATUS,
OLD_STATUS,
ENTERED_ON,
ENTERED_BY,
SIG_FIGS_NUMBER,
SIG_FIGS_ROUNDING,
FORMULA)
VALUES
(TEST_ID,
CUR.NAME,
CASE WHEN CUR.NAME = '温度' THEN unumber(wendu) when
cur.name = '重量' then unumber(in_zhongliang) ELSE
unumber(cur.u_def_value) END,
CASE WHEN CUR.NAME = '温度' THEN WENDU WHEN CUR.NAME = '取样时间' THEN
SHIJIAN WHEN CUR.NAME = '采样时间' THEN SHIJIAN WHEN
CUR.NAME = '取样点' then sample_point_name when
cur.name = '箱号' or cur.name = '箱(袋)号' then in_xianghao when
cur.name = '重量' then in_zhongliang when CUR.NAME = '生产日期' then
in_date when CUR.NAME = '原烟等级' then in_dengji when
CUR.NAME = '等级' then in_dengji when CUR.NAME = '原烟顺序号' then
in_xh else cur.u_def_value END,
CASE WHEN CUR.NAME = '温度' THEN WENDU WHEN CUR.NAME = '取样时间' THEN
SHIJIAN WHEN CUR.NAME = '采样时间' THEN SHIJIAN WHEN
CUR.NAME = '取样点' then sample_point_name when
cur.name = '箱号' or cur.name = '箱(袋)号' then in_xianghao when
cur.name = '重量' then in_zhongliang when CUR.NAME = '生产日期' then
in_date when CUR.NAME = '原烟等级' then in_dengji when
CUR.NAME = '等级' then in_dengji when CUR.NAME = '原烟顺序号' then
in_xh else cur.u_def_value END,
CUR.ORDER_NUMBER,
CUR.RESULT_TYPE,
CUR.UNITS,
CUR.MINIMUM,
CUR.MAXIMUM,
CUR.CALCULATION,
cur.places,
CASE WHEN
CUR.NAME = '温度' OR CUR.NAME = '取样时间' OR CUR.NAME = '采样时间' OR
CUR.NAME = '取样点' or cur.name = '箱号' or cur.name = '箱(袋)号' or
cur.name = '重量' or CUR.NAME = '生产日期' or CUR.NAME = '原烟等级' or
CUR.NAME = '等级' or CUR.NAME = '原烟顺序号' or
trim(cur.u_def_value) is not null then 'E' ELSE 'U' END,
CASE WHEN
CUR.NAME = '温度' OR CUR.NAME = '取样时间' OR CUR.NAME = '采样时间' OR
CUR.NAME = '取样点' or cur.name = '箱号' or cur.name = '箱(袋)号' or
cur.name = '重量' or CUR.NAME = '生产日期' or CUR.NAME = '原烟等级' or
CUR.NAME = '等级' or CUR.NAME = '原烟顺序号' or
trim(cur.u_def_value) is not null then 'U' ELSE NULL END,
sysdate,
'DEFAULT',
CUR.SIG_FIGS_NUMBER,
CUR.SIG_FIGS_ROUNDING,
CUR.FORMULA);
END LOOP;
END LOOP;
end loop;
update sample
set tests_to_do = v_test_ordernumber
where id_numeric = sample_id;
update u_cptm --更新成品条码表
set sampleid = sample_id
where trim(batchidentity) = trim(in_batch_id)
and trim(xianghao) = trim(in_xianghao);
--VERSIONED_ANALYSIS
--将规格指标写入结果表
for cur in (select test_number, name, p_mlp_get_new(test_number, name) gg
from result
where test_number in
(select test_number
from test te
where te.sample = lpad(trim(sample_id), 10, ' '))) loop
if cur.gg is not null and trim(cur.gg) <> '#' then
update result we
set we.minimum_qb = UNUMBER( substr(cur.gg, 0, instr(cur.gg, '#') - 1)),
we.maximum_qb = UNUMBER( substr(cur.gg, instr(cur.gg, '#') + 1))
where we.test_number = cur.test_number
and cur.name = we.name;
end if;
end loop;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
DECLARE
Err_msg varchar2(512) := substr(sqlerrm, 1, 500);
BEGIN
insert into mn.hs_log
(ERRORFROM, errmsg, title, update_time, LOGTYPE)
values
('p_smp_login',
Err_msg,
'数据处理异常' || in_templte_id || ',' || in_batch_id || ',' || in_scbb || ',' ||
in_list_analysis || ',' || in_batch_name,
sysdate,
'ERROR');
commit;
END;
end p_smp_login;
这么长啊，知道具体逻辑不，只知道个头先写
CREATE PROC p_smp_login
@in_templte_id varchar,
@in_batch_id varchar,
@in_scbb varchar,
@in_scbc varchar,
@in_jcbb varchar,
@in_samplename varchar,
@in_wendu varchar,
@in_time varchar,
@in_list_analysis varchar,
@in_xianghao varchar,
@in_zhongliang varchar,
@in_date varchar,
@in_xh varchar,
@in_dengji varchar,
@in_batch_name varchar,
@in_login_by varchar,
@out_sampleid varchar out
AS
BEGIN
DECLARE @sample_id varchar(10)
DECLARE @samp_tmpl_header_id varchar(50)
DECLARE @u_batch_id varchar(50)
DECLARE @v_U_SCBB varchar(30)
DECLARE @v_U_SCBC varchar(30)
DECLARE @v_U_jcbb varchar(30)
DECLARE @TEST_SCHEDULE_name varchar(30)
DECLARE @sample_point_name varchar(30)
DECLARE @guigezhibiao varchar(50)
DECLARE @guigebanben varchar(50)
DECLARE @v_gbproduct varchar(50) --国标规格指标
DECLARE @v_gbVersion varchar(50) --国标规格指标版本
DECLARE @v_sample_name varchar(50)
DECLARE @v_group_id varchar(50)
DECLARE @sample_point_id varchar(10)
DECLARE @TEST_ID varchar(20)
DECLARE @analysis_ID varchar(50)
DECLARE @analysis_NAME varchar(50)
DECLARE @analysis_DESC varchar(50)
DECLARE @WENDU varchar(50)
DECLARE @SHIJIAN varchar(50)
DECLARE @v_test_ordernumber numeric
DECLARE @v_samplenames varchar(50)
/* v_REPLICATE_COUNT varchar(50);
v_COMPONENT_LIST varchar(50);
v_ORDER_NUM varchar(50);*/
--SAMPLE 212912
SET @samp_tmpl_header_id = @in_templte_id -- 'HS_CGGHYKH';
SET @u_batch_id = @in_batch_id; -- 'HS001';
SET @v_U_SCBB = @in_scbb; -- '甲班';
SET @v_U_SCBC = @in_scbc; -- '白班';
SET @v_U_jcbb = @in_jcbb;--检测班别
SET @sample_point_name = @in_samplename; -- '片烟';
SET @WENDU = @in_wendu; -- '23.5';
SET @SHIJIAN = @in_time; -- '11:11:11';
SET @v_test_ordernumber := 0;
update INCREMENTS
set lastval = lpad(to_number(lastval) + 1, 10, ' ')----此處mssql對應函數不懂
WHERE MAJOR = upper('SAMPLE')
and minor = upper('KEY0')
returning lpad(lastval, 10, ' ') into sample_id;----此處不明白