oracle的一个存储过程转SqlServer 求帮忙

Question

要转换为SqlServer版本的 我是做前端开发的,实在不懂这么复杂的sql 求帮忙
二楼oracle版本代码

Answer 1

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;