worker_tbl.sql

来自「开源中间件 导入Hibernate相关的包进该项目： 选中项目名称后单击鼠标」· SQL 代码 · 共 94 行

ALTER TABLE WORKER_TBL DROP CONSTRAINT PK_WORKER_TBL;
DROP TABLE WORKER_TBL;
DROP PROCEDURE UPDATE_WORKER_SALARY_PRC;
DROP FUNCTION UPDATE_WORKER_SALARY_FUNC;
DROP SEQUENCE WORKER_ID_SEQ;
DROP FUNCTION ADD_WORKER_FUNC;

CREATE SEQUENCE WORKER_ID_SEQ INCREMENT BY 1 START WITH 1;

CREATE TABLE WORKER_TBL (
ID NUMBER(9)  NOT NULL, 
FNAME VARCHAR(10)                     NOT NULL,
LNAME VARCHAR(10)                       NOT NULL, 
SEX VARCHAR(6),                       
AGE NUMBER(5,2), 
BIRTHDAY DATE, 
SALARY NUMBER(10, 5), 
CREATETIME DATE                    NOT NULL,
CONSTRAINT PK_WORKER_TBL PRIMARY KEY (ID)
);

INSERT INTO WORKER_TBL(ID, FNAME, LNAME, SEX, AGE, BIRTHDAY, SALARY, CREATETIME)
 VALUES(100, 'Alan', 'Liu', 'male', 30, TO_DATE('1975-12-07','yyyy/mm/dd'), 2000.32, SYSDATE);

INSERT INTO WORKER_TBL(ID, FNAME, LNAME, SEX, AGE, BIRTHDAY, SALARY, CREATETIME)
 VALUES(200, 'Kevin', 'Ding', 'male', 32, TO_DATE('1973-6-26','yyyy/mm/dd'), 1000.32, SYSDATE);

INSERT INTO WORKER_TBL(ID, FNAME, LNAME, SEX, AGE, BIRTHDAY, SALARY, CREATETIME)
 VALUES(300, 'Larry', 'Zhao', 'male', 40, TO_DATE('1965-12-07','yyyy/mm/dd'), 2100.32, SYSDATE);

INSERT INTO WORKER_TBL(ID, FNAME, LNAME, SEX, AGE, BIRTHDAY, SALARY, CREATETIME)
 VALUES(400, 'George', 'Zhu', 'male', 35, TO_DATE('1970-12-07','yyyy/mm/dd'), 2000.32, SYSDATE);

COMMIT;

CREATE PROCEDURE UPDATE_WORKER_SALARY_PRC(
    P_ID IN WORKER_TBL.ID%TYPE,
    P_FACTOR    IN NUMBER
)AS WORKER_COUNT INTEGER;
BEGIN
    SELECT COUNT(*) INTO WORKER_COUNT FROM WORKER_TBL WHERE ID=P_ID;

    IF WORKER_COUNT = 1 THEN
        UPDATE WORKER_TBL SET SALARY = SALARY * P_FACTOR WHERE ID = P_ID;
        COMMIT;
    END IF;
END UPDATE_WORKER_SALARY_PRC;
/


CREATE FUNCTION ADD_WORKER_FUNC(
    P_ID        IN  WORKER_TBL.ID%TYPE,
    P_FNAME     IN  WORKER_TBL.FNAME%TYPE,
    P_LNAME     IN  WORKER_TBL.LNAME%TYPE,
    P_SEX       IN  WORKER_TBL.SEX%TYPE,
    P_AGE       IN  WORKER_TBL.AGE%TYPE,
    P_BIRTHDAY  IN  WORKER_TBL.BIRTHDAY%TYPE,
    P_SALARY    IN  WORKER_TBL.SALARY%TYPE,
    P_CREATETIME    IN  WORKER_TBL.CREATETIME%TYPE
)RETURN INTEGER AS WORKER_COUNT INTEGER;
BEGIN
    SELECT COUNT(*) INTO WORKER_COUNT FROM WORKER_TBL WHERE ID = P_ID;
    IF WORKER_COUNT > 0 THEN
        
        RETURN 0;
    ELSE
        INSERT INTO WORKER_TBL (ID, FNAME, LNAME, SEX, AGE, BIRTHDAY, SALARY, CREATETIME) 
        VALUES (P_ID, P_FNAME, P_LNAME, P_SEX, P_AGE, P_BIRTHDAY, P_SALARY, P_CREATETIME);
        COMMIT;
        RETURN 1;
    END IF;
    
END ADD_WORKER_FUNC;
/

CREATE FUNCTION UPDATE_WORKER_SALARY_FUNC(
    P_ID IN  WORKER_TBL.ID%TYPE,
    P_FACTOR   IN  NUMBER
) RETURN INTEGER AS WORKER_COUNT INTEGER;
BEGIN
        SELECT COUNT(*) INTO WORKER_COUNT FROM WORKER_TBL WHERE ID = P_ID;
        
        IF WORKER_COUNT = 0 THEN
            RETURN 0;
        ELSE
            UPDATE WORKER_TBL SET SALARY = SALARY * P_FACTOR WHERE ID = P_ID;
            COMMIT;
            RETURN 1;
        END IF;
END UPDATE_WORKER_SALARY_FUNC;

/