?? worker.sql~
字號:
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;/
?? 快捷鍵說明
復(fù)制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -