database.sql

来自「j2ee写的管理程序」· SQL 代码 · 共 67 行

create table student
(
	id number(6),
	name varchar(200),
	address varchar(200),
	grade number(6)
);

create table student_log
(
	who varchar2(30),
	when date
);

create trigger biufer_student
before insert or update or delete
on student
begin
	insert into student_log values(user,sysdate);
end;
/

create sequence student_sequence increment by 1 start with 1;


create trigger bifer_student_studentid
before insert on student
for each row
begin 
	select student_sequence.nextval
	into :new.id
	from dual;
end;
/

create or replace
procedure insert_into_student(
name_val in varchar,
address_val in varchar,
grade_val in number)
is 
begin
	insert into student(name,address,grade) values(name_val,address_val,grade_val);
end insert_into_student;
/

create or replace
procedure update_student(
name_val in varchar,
address_val in varchar,
grade_val in number,
id_val in number)
is
begin
	update student set name=name_val,address = address_val,grade = grade_val where id=id_val;
end update_student;
/

create or replace
procedure delete_student(
id_val in number)
is
begin
	delete from student where id=id_val;
end delete_student;
/