时间戳

首页 > 解决方案 > 如何让 pl/sql 代码跳过多列中的 NULL 值

sql - 如何让 pl/sql 代码跳过多列中的 NULL 值

问题描述

我目前正在编写一个从游标中获取数据的 PL/SQL 脚本。游标按多个分组集进行分组，因此我在选择的几个条目中为多个列返回空值。有没有办法让我的 pl/sql 脚本在我的插入语句中跳过这些条目？我尝试过使用 IF 语句，但这不起作用。以下是我的代码：提前致谢！

create or replace Procedure TEST_PROC IS
CURSOR c1 is
select sum(v.value_tx) as sum_of_values
     , max(c.create_dt) as max_calculation_dt
     , max(TRUNC(to_date((v.hr + ((v.utc_offset - 1)/24)), 'DD-MM-YY hh24:mi:ss'), 'iw')) as REPORT_PERIOD_DT
     , to_char((v.hr + ((v.utc_offset - 1)/24)),  'DD-MM-YY hh24:mi:ss') as CONVERTED_HR 
     , v.utc_offset
     , ff.form_field_label_tx
     , v.region_id 
     , v.hr_num
     , v.data_code
  from value v
  join calculation_value cv on v.value_id = cv.value_id
  join calculation c on cv.calculation_id = c.calculation_id
  join form_field ff on cv.form_field_id = ff.form_field_id

 where v.hr is not null
   and v.data_code is not null
   and v.utc_offset is not null
group by grouping sets(
(v.region_id, v.data_code, v.hr_num, v.utc_offset, ff.form_field_label_tx, ff.form_field_id, to_char(TRUNC(to_date((v.hr + ((v.utc_offset - 1)/24)), 'DD-MM-YY hh24:mi:ss'), 'iw'), 'dddyyyy'), TRUNC(to_date((v.hr + ((v.utc_offset - 1)/24)), 'DD-MM-YY hh24:mi:ss'), 'iw'), to_char((hr + ((utc_offset - 1)/24)),  'DD-MM-YY hh24:mi:ss'), ),
(v.region_id, v.data_code, v.hr_num, v.utc_offset, ff.form_field_label_tx, ff.form_field_id,  to_char(TRUNC(to_date((v.hr + ((v.utc_offset - 1)/24)), 'DD-MM-YY hh24:mi:ss'), 'iw'), 'dddyyyy'), TRUNC(to_date((v.hr + ((v.utc_offset - 1)/24)), 'DD-MM-YY hh24:mi:ss'), 'iw'), v.utc_offset), v.data_code
                   )
order by max(TRUNC(to_date((v.hr + ((v.utc_offset - 1)/24)), 'DD-MM-YY hh24:mi:ss'), 'iw')) desc;
l_var c1%ROWTYPE;
v_value_id value.value_id%type;
v_calculation_id calculation.calculation_id%type;
--
BEGIN
 Open c1;
        FETCH c1 into l_var;
                          insert into calculation (calculation_id,     calculation_date, calculation_name, report_period_dt)
                                               VALUES (null, sysdate, 'AGGWKL ' || l_var.REPORT_PERIOD_DT, l_var.report_period_dt)
                                               returning calculation_id into v_calculation_id;
 Close c1;
 Open c1;
    LOOP
        FETCH c1 into l_var;
        EXIT when c1%NOTFOUND;
  IF l_var.utc_offset is not null
  THEN
                          insert into value (value_id, energy_product_id, data_source_id, unit_cd, value_tx, utc_offset, data_date, hr_utc, hr, hr_num, data_code)
                                         VALUES (null, '777', '5', 'NA', l_var.sum_of_values, l_var.utc_offset, l_var.data_date, null, null, l_var.hr_num, l_var.data_code)
                                         returning value_id into v_value_id;
                          insert into calculation_value(calculation_value_id, calculation_id, value_id, form_field_id
                                      values (null, v_calculation_id, v_value_id, l_var.form_field_id);

           END LOOP;
           CLOSE c1;
END TEST_PROC;

标签： sqloracleplsql

解决方案

您没有指出在哪个表中您得到空值，所以我将尝试仅回答我看到的内容。如果没有一些测试数据，很难写出准确的答案，但据我所知，你有几个问题：

insert into calculation (calculation_id,     calculation_date, calculation_name, report_period_dt)
     VALUES (null, sysdate, 'AGGWKL ' || l_var.REPORT_PERIOD_DT, _var.report_period_dt) returning calculation_id into v_calculation_id;

在这里您要插入null表格calculation，然后将其返回v_calculation_id

这里也发生了类似的事情：

insert into value (value_id, energy_product_id, data_source_id, unit_cd, 
                    value_tx, utc_offset, data_date, hr_utc, hr, hr_num, data_code)
VALUES (null, '777', '5', 'NA', l_var.sum_of_values, l_var.utc_offset, 
        l_var.data_date, null, null, l_var.hr_num, l_var.data_code)                                       returning value_id into v_value_id;

您正在插入null表格value，然后将其返回v_value_id

最后你将所有这些插入nulls到calculation_value这里：

insert into calculation_value(calculation_value_id, calculation_id, value_id, form_field_id
values (null, v_calculation_id (this is null), v_value_id (this is null), l_var.form_field_id);

推荐阅读