Creating Oracle Procedure To Update Table-CodePudding

I'm trying to create a procedure in an Oracle database that will update the PMBPCSB with an 'X' if any of the other columns have a value present. If all of the columns are NULL then I don't want it to do anything. I'm not exactly sure how to format the procedure to accomplish what I'm wanting. Any help is appreciated.

COLUMN_NAME	DATA
PMBPCSB
PMBPCFBB
PMBPCHUB
PMBCITNC
PMBCITNSB
PMBCSDB
PMBCSDVOS
PMBCVOSB
PMBCWOB

CodePudding user response：

That's just an ordinary UPDATE, is it not?

SQL> create table pm
  2    (pmbpcsb  varchar2(1),
  3     pmbpcfbb varchar2(10),
  4     pmbpchub varchar2(10),
  5     pmbcitnc varchar2(10)
  6    );

Table created.

SQL> insert into pm values (null, '100', '200', '300');

1 row created.

SQL> insert into pm values (null, '300', null , '500');

1 row created.

SQL> insert into pm values (null, null , null , null );

1 row created.

Update:

SQL> update pm set
  2    pmbpcsb = 'X'
  3  where coalesce(pmbpcfbb, pmbpchub, pmbcitnc) is not null;

2 rows updated.

Result:

SQL> select * from pm;

PMBPCSB    PMBPCFBB   PMBPCHUB   PMBCITNC
---------- ---------- ---------- ----------
X          100        200        300
X          300                   500
                                            --> this is a row whose columns are NULL (all of them)

SQL>

How to convert it to a procedure? Simply:

SQL> create or replace procedure p_pm as
  2  begin
  3    update pm set
  4      pmbpcsb = 'X'
  5    where coalesce(pmbpcfbb, pmbpchub, pmbcitnc) is not null;
  6  end;
  7  /

Procedure created.

SQL>

However, perhaps you'd rather use CASE and affect all rows because - what if table contents changes?

SQL> update pm set
  2    pmbpcsb = case when coalesce(pmbpcfbb, pmbpchub, pmbcitnc) is not null then 'X'
  3                   else null
  4              end;

3 rows updated.

SQL>

Or, as people commented, create a view (using the same principle):

SQL> create or replace view v_pm as
  2  select case when coalesce(pmbpcfbb, pmbpchub, pmbcitnc) is not null then 'X'
  3              else null
  4         end pmbpcsb,
  5     pmbpcfbb,
  6     pmbpchub,
  7     pmbcitnc
  8  from pm;

View created.

SQL> select * From v_pm;

PMBPCSB    PMBPCFBB   PMBPCHUB   PMBCITNC
---------- ---------- ---------- ----------
X          100        200        300
X          300                   500


SQL>