我可以在PL/SQL中使用IF吗? [英] 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 
;

我想创建一个过程，为每一行的全数列添加全数， 如果board列获得指南"，则为board_number列提供guide_seq. 如果board列得到通知"，则将notify_seq用作board_number列.

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'.

现在完成了一个用于Whole_number列的过程.

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;  
/

有人可以帮助我制定一个根据ORACLE中board列的值而获得不同顺序的过程吗? 另外，有什么方法可以在Whole_number列的序列号之前添加字符串'Board_'吗?

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?

推荐答案

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;  
/

这篇关于我可以在PL/SQL中使用IF吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！