学步园

CREATE SCHEMA TEST_SCHEMA;
CREATE TABLE TEST_SCHEMA.TEST_USERS
(
    UniqueID INT NOT NULL UNIQUE,
    Name VARCHAR(50),
    Pwd VARCHAR(50),
    Sex VARCHAR(10),
    Email VARCHAR(50),
PRIMARY KEY(UniqueID)
);

-- 创建序列
CREATE SEQUENCE TEST_SCHEMA.USERS_UNIQUEID_SEQ
START WITH 20
INCREMENT BY 1;

-- 用于触发器的函数
CREATE OR REPLACE PROCEDURE TEST_SCHEMA.Test_ForTrigger() RETURN TRIGGER AS
BEGIN 
    NEW.UniqueID = NEXTVAL('TEST_SCHEMA.USERS_UNIQUEID_SEQ');
RETURN NEW;
END;
LANGUAGE 'PLOSCAR';

-- 创建触发器实现ID自增
CREATE TRIGGER Test_ForIncreaseID
BEFORE INSERT ON TEST_SCHEMA.TEST_USERS 
FOR EACH ROW 
EXECUTE PROCEDURE TEST_SCHEMA.Test_ForTrigger();

-- 注意：参数和列名是不区分大小写的！
CREATE OR REPLACE PROCEDURE TEST_SCHEMA.Delete_User(id IN INT) RETURN VOID AS
BEGIN
DELETE FROM TEST_SCHEMA.TEST_USERS WHERE UniqueID = id;
RETURN VOID;
END;
LANGUAGE 'PLOSCAR';

INSERT INTO TEST_SCHEMA.TEST_USERS(Name,Pwd,Sex,Email) VALUES('刘大','liuda','男','liuda@163.com');
INSERT INTO TEST_SCHEMA.TEST_USERS(Name,Pwd,Sex,Email) VALUES('王二','wanger','男','wanger@163.com');
INSERT INTO TEST_SCHEMA.TEST_USERS(Name,Pwd,Sex,Email) VALUES('张三','zhangsan','女','zhangsan@163.com');
INSERT INTO TEST_SCHEMA.TEST_USERS(Name,Pwd,Sex,Email) VALUES('李四','lisi','女','lisi@163.com');
DELETE FROM TEST_SCHEMA.TEST_USERS WHERE UniqueID = 23;
INSERT INTO TEST_SCHEMA.TEST_USERS(Name,Pwd,Sex,Email) VALUES('赵五','zhaowu','男','zhaowu@163.com');