CODEWITHSUNDEEP
sqloracle-databaseplsql
Jay Bumjun Kim
Jay Bumjun Kim

Reputation: 113

Can I use IF in PL/SQL?

I made a table and four sequences. (one sequence for a column, and three sequence for a column.)

CREATE TABLE abc (
  whole_number VARCHAR2(100) primary key,
  board_number VARCHAR2(100),
  board VARCHAR2(100),
  content VARCHAR2(100)
);
CREATE SEQUENCE whole_seq
  MINVALUE 1
  NOMAXVALUE
  INCREMENT BY 1 START WITH 1 
;

CREATE SEQUENCE notify_seq
  MINVALUE 1
  NOMAXVALUE
  INCREMENT BY 1 START WITH 1 
;
CREATE SEQUENCE product_seq
  MINVALUE 1
  NOMAXVALUE
  INCREMENT BY 1 START WITH 1 
;
CREATE SEQUENCE guide_seq
  MINVALUE 1
  NOMAXVALUE
  INCREMENT BY 1 START WITH 1 
;

I want to create a Procedure that adds whole_seq for whole_number column at every row, and guide_seq for board_number column if the board column gets 'guide'. and notify_seq for board_number column if the board column gets 'notify'.

One procedure for whole_number column is made now.

CREATE OR REPLACE TRIGGER whole_number_trigger 
  BEFORE INSERT 
  ON abc FOR EACH ROW
  BEGIN
  SELECT whole_seq INTO :new.whole_number FROM dual;
END;  
/

Can someone help me make a procedure that gets different sequence depending on the value of board column in ORACLE? Also, is there any way to add string 'Board_' before the sequence number of whole_number column?

Upvotes: 1

Views: 451

Answers (1)

Michael Piankov
Michael Piankov

Reputation: 1997

CREATE OR REPLACE TRIGGER whole_number_trigger 
  BEFORE INSERT 
  ON abc FOR EACH ROW
  BEGIN
  SELECT 'Board_' || whole_seq.nextval INTO :new.whole_number FROM dual;
  IF :new.board = 'guide' THEN 
    SELECT guide_seq.nextval INTO :new.board_number FROM dual;
  ELSIF   :new.board = 'notify' THEN 
    SELECT notify_seq.nextval INTO :new.board_number FROM dual;
  END IF;
END;  
/

Upvotes: 5

Related Questions