oracle pl sql 控制语句

最新推荐文章于 2024-07-03 09:00:00 发布

原创最新推荐文章于 2024-07-03 09:00:00 发布 · 545 阅读

0 ·

CC 4.0 BY-SA版权

oracle 专栏收录该内容

103 篇文章

订阅专栏

本文详细介绍了如何使用SQL创建表、插入数据，并通过PL/SQL编写控制语句进行条件判断与输出。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

先创建表：

CREATE TABLE TB_USER
(
    ID INTEGER PRIMARY KEY,
    USER_NAME VARCHAR2(20) NOT NULL,
    USER_AGE INTEGER NOT NULL
);

CREATE SEQUENCE SEQ_USER
INCREMENT BY 1
START WITH 1
NOMAXVALUE
NOCYCLE
CACHE 10;

CREATE OR REPLACE TRIGGER TR_USER BEFORE INSERT ON TB_USER FOR EACH ROW
BEGIN
    SELECT SEQ_USER.NEXTVAL INTO :NEW.ID FROM DUAL;
END;

插入一些数据。

然后编写控制语句：

DECLARE
V_AGE TB_USER.USER_AGE%TYPE;
V_NAME TB_USER.USER_NAME%TYPE;
BEGIN
  SELECT USER_AGE, USER_NAME INTO V_AGE, V_NAME FROM TB_USER WHERE ID = 1;
  IF V_AGE > 0 THEN
    DBMS_OUTPUT.put_line(V_NAME || '>0');
  END IF;
END;

DECLARE
V_AGE TB_USER.USER_AGE%TYPE;
V_NAME TB_USER.USER_NAME%TYPE;
BEGIN
  SELECT USER_AGE, USER_NAME INTO V_AGE, V_NAME FROM TB_USER WHERE ID = 1;
  IF V_AGE < 0 THEN
    DBMS_OUTPUT.put_line(V_NAME || '<0');
  ELSE
    DBMS_OUTPUT.put_line(V_NAME || '>=0');
  END IF;
END;

DECLARE
V_AGE TB_USER.USER_AGE%TYPE;
V_NAME TB_USER.USER_NAME%TYPE;
BEGIN
  SELECT USER_AGE, USER_NAME INTO V_AGE, V_NAME FROM TB_USER WHERE ID = 1;
  IF V_AGE < 0 THEN
    DBMS_OUTPUT.put_line(V_NAME || '<0');
  ELSIF V_AGE = 0 THEN
    DBMS_OUTPUT.put_line(V_NAME || '=0');
  ELSE
    DBMS_OUTPUT.put_line(V_NAME || '>0');
  END IF;
END;