create tablespace demo datafile 'D:/demo.dbf' size 100m autoextend on next 20m maxsize unlimited;
create user yang identified by yc default tablespace demo;
grant connect,resource to yang;
create table students
(
stuid int primary key not null,
stuname varchar(20) not null
);create table scores
(
scid int primary key not null,
stuid int not null,
score int not null
)alter table scores add constraint FK_STUDENTS_SCORES foreign key (stuid) references students(stuid);create sequence seq_stuid;
create sequence seq_scid;begin
for rn in 1..20 loop
insert into students values(seq_stuid.nextval,dbms_random.string('a',5));
end loop;
commit;
end;declare
sid int;
cursor cus_id is select stuid from students;
begin
for r in cus_id loop
sid:=r.stuid;
for rn in 1..3 loop
insert into scores values(seq_scid.nextval,sid,round(dbms_random.value(0,100)));
end loop;
commit;
end loop;
end;
--分页过程
create or replace procedure proc_cutpage(res out sys_refcursor,currentPage in integer,pageSize in integer)
as
begin
declare
str varchar(5000);
begin
str:='select s.stuid,s.stuname,s.avgscore,s.sumscore from
(select rownum rn,st.stuid,st.stuname,st.avgscore,st.sumscore from
(select stu.stuid,stu.stuname,avg(sco.score) avgscore,sum(sco.score)
sumscore from scores sco inner join students stu on stu.stuid=sco.stuid
group by stu.stuid,stu.stuname order by sumscore desc) st
where rownum<='||(currentPage*pageSize)||') s where s.rn>='||((currentPage-1)*pageSize+1);
open res for str;
end;
end;--自定义类型
create or replace type sc as Object
(
score int
);
create or replace type scs as varray(4) of sc;
--新增过程
create or replace procedure proc_save (stuname in varchar,scoreses in scs)
as
begin
insert into students values(seq_stuid.nextval,stuname);
commit;
for rn in 1..scoreses.count loop
insert into scores values(seq_scid.nextval,seq_stuid.currval,scoreses(rn).score);
end loop;
commit;
end;
--触发器
create or replace trigger trig_delete_stu
before delete on students for each row
begin
delete from scores where stuid=:old.stuid;
end;
--修改过程
create or replace procedure proc_update (stid in integer,stname in varchar,scoreses in scs)
as
begin
declare
nums int:=1;
cursor cur_scid is select scid from scores where stuid=stid;
begin
update students set stuname=stname where stuid=stid;
commit;
for rn in cur_scid loop
update scores set score = scoreses(nums).score where scid=rn.scid;
nums:=nums+1;
commit;
end loop;
end;
end;
create user yang identified by yc default tablespace demo;
grant connect,resource to yang;
create table students
(
stuid int primary key not null,
stuname varchar(20) not null
);create table scores
(
scid int primary key not null,
stuid int not null,
score int not null
)alter table scores add constraint FK_STUDENTS_SCORES foreign key (stuid) references students(stuid);create sequence seq_stuid;
create sequence seq_scid;begin
for rn in 1..20 loop
insert into students values(seq_stuid.nextval,dbms_random.string('a',5));
end loop;
commit;
end;declare
sid int;
cursor cus_id is select stuid from students;
begin
for r in cus_id loop
sid:=r.stuid;
for rn in 1..3 loop
insert into scores values(seq_scid.nextval,sid,round(dbms_random.value(0,100)));
end loop;
commit;
end loop;
end;
--分页过程
create or replace procedure proc_cutpage(res out sys_refcursor,currentPage in integer,pageSize in integer)
as
begin
declare
str varchar(5000);
begin
str:='select s.stuid,s.stuname,s.avgscore,s.sumscore from
(select rownum rn,st.stuid,st.stuname,st.avgscore,st.sumscore from
(select stu.stuid,stu.stuname,avg(sco.score) avgscore,sum(sco.score)
sumscore from scores sco inner join students stu on stu.stuid=sco.stuid
group by stu.stuid,stu.stuname order by sumscore desc) st
where rownum<='||(currentPage*pageSize)||') s where s.rn>='||((currentPage-1)*pageSize+1);
open res for str;
end;
end;--自定义类型
create or replace type sc as Object
(
score int
);
create or replace type scs as varray(4) of sc;
--新增过程
create or replace procedure proc_save (stuname in varchar,scoreses in scs)
as
begin
insert into students values(seq_stuid.nextval,stuname);
commit;
for rn in 1..scoreses.count loop
insert into scores values(seq_scid.nextval,seq_stuid.currval,scoreses(rn).score);
end loop;
commit;
end;
--触发器
create or replace trigger trig_delete_stu
before delete on students for each row
begin
delete from scores where stuid=:old.stuid;
end;
--修改过程
create or replace procedure proc_update (stid in integer,stname in varchar,scoreses in scs)
as
begin
declare
nums int:=1;
cursor cur_scid is select scid from scores where stuid=stid;
begin
update students set stuname=stname where stuid=stid;
commit;
for rn in cur_scid loop
update scores set score = scoreses(nums).score where scid=rn.scid;
nums:=nums+1;
commit;
end loop;
end;
end;
MySQL官方文档 http://dev.mysql.com/doc/refman/5.1/zh/index.html
不过trigger可以直接用。
procedure要修改的,你只能一点点来调。
还有自定义类型 和 存储过程 不太会写..
还有自定义类型 和 存储过程 不太会写..
自定义类型没有。