worker.sql~

这是一本描述JDBC数据库的书籍

connect jdbc/jdbc;
alter table Worker_Tbl drop constraint PK_worker_TBL;
drop table Worker_Tbl;
drop procedure update_worker_salary;
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(
    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;
        commit;
    end if;
end update_worker_salary;
/


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
)as count integer;
begin
    select count(*) into count from worker_Tbl where id = p_id;
    if count > 0 then
        count = 0;
        return count;
    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;
        count = 1;
        return count;
    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;
            commit;
            return 1;
        end if;
end update_worker_salary_func;

/