第4个回答 2019-09-03
create table classes(id number,name varchar2(50));
insert into classes(id,name) values(1,'T202');
insert into classes(id,name) values(2,'T203');
insert into classes(id,name) values(3,'T204');
insert into classes(id,name) values(4,'T205');
rollback;
commit;
select * from classes;
create table student(
id number,
name varchar2(50),
classid number,
heigth number,
weigth number
);
insert into student(id,name,classid,heigth,weigth) values(1,'a',1,170,48);
insert into student(id,name,classid,heigth,weigth) values(2,'a',1,171,49);
insert into student(id,name,classid,heigth,weigth) values(3,'a',1,172,50);
insert into student(id,name,classid,heigth,weigth) values(4,'b',2,173,51);
insert into student(id,name,classid,heigth,weigth) values(5,'c',2,174,52);
insert into student(id,name,classid,heigth,weigth) values(6,'e',3,175,53);
insert into student(id,name,classid,heigth,weigth) values(7,'f',3,176,54);
insert into student(id,name,classid,heigth,weigth) values(8,'g',4,177,55);
insert into student(id,name,classid,heigth,weigth) values(9,'h',4,178,56);
select * from student;
rollback;
commit;
--1.每个年级身高前两名
SELECT * FROM (
select (select name from classes where id=classid) as
班级名称,heigth,classid,
row_number() OVER (PARTITION BY CLASSID order by heigth desc) 名次
from student) where 名次 IN (1,2);
--2.班级平均体重
select (select name from classes where id=classid) as
班级名称,avg(weigth) as 平均体重
from student
group by classid;
--3.每个班的总人数
select (select name from classes where id=classid) as
班级名称,count(1) as 总人数
from student
group by classid;
select count(1) from student group by classid;
--删除重复数据
delete from student where rowid not in(
select max(rowid) from student group by name,classid,heigth,weigth);
select * from student;
rollback;
--创建存储过程 删除学生信息
create procedure ccgc(
v_id student.id%type
)
is
begin
delete from student where id=v_id;
end;
declare
v_id student.id%type;
begin
v_id:=&学生编号;
ccgc(v_id);
end;
select * from student;
rollback;
--创建存储过程计算身高总和
create procedure test2(
v_classid student.classid%type,
v_sum out number
)
is
begin
select sum(heigth) into v_sum from student where classid=v_classid;
end;
declare
v_classid student.classid%type;
v_sum number;
begin
v_classid:=&班级编号;
test2(v_classid,v_sum);
dbms_output.put_line('身高总和:'||v_sum);
end;
--创建序列
create sequence sl
start with 1
increment by 1
maxvalue 1000
nocycle
create table test1(id number,name varchar2(50),money number);
begin
for i in 1 .. 100 loop
insert into test1 (id,name,money) values(sl.nextval,'邓嘉男',i);
end loop;
end;
select * from test1;
--根据游标查询前十条数据
declare
cursor yb
is
select * from test1 where id<=10;
begin
for i in yb loop
dbms_output.put_line('数据为:'||i.id||i.name||i.money);
end loop;
end;
--查询最大值最小值之间的数据
create or replace procedure test3
(
v_max number,
v_min number,
v_yb1 out sys_refcursor
)
is
begin
open v_yb1 for
select * from test1 where money<=v_max and money>=v_min;
end;
declare
v_id test1.id%type;
v_name test1.name%type;
v_money test1.money%type;
v_max number:=&最大值;
v_min number:=&最小值;
v_yb1 sys_refcursor;
begin
test3(v_max,v_min,v_yb1);
loop
FETCH v_yb1 into v_id,v_name,v_money;
exit when v_yb1%notfound;
dbms_output.put_line(v_id||v_name||v_money);
end loop;
end;
--创建表
create table emploee
(
id number(10),
salary number(10,2) default 0,
name varchar2(24)
)
;
select * from emploee where ID<=5
select count(*) from emploee
-- 创建序列
create sequence seq_employee;
--插入数据
begin
for i in 1..100 loop
insert into emploee values(seq_employee.nextval,2000,'name');
end loop;
end;
--3
declare
cursor niao is
select id,salary,name,rownum from emploee where rownum<=10;
begin
for ni in niao loop
dbms_output.put_line(ni.id||' '||ni.salary||' '||ni.name);
end loop;
end;
--4
create or replace procedure p_query_employee(
v_a number,
v_b number,
v_all out sys_refcursor )
is
begin
open v_all for
select id,salary,name from emploee where salary>=v_a and salary<=v_b order by salary;
end;
declare
v_a number(10):=&大于;
v_b number(10):=&小于;
v_id number(10);
v_salary number(10);
v_name varchar2(24);
v_all sys_refcursor;
begin
p_query_employee (v_a,v_b,v_all);
loop
fetch v_all into v_id, v_salary,v_name;
exit when v_all%notfound;
dbms_output.put_line(v_id||' ' ||v_salary||' '||v_name);
end loop;
end;
--5
create or replace procedure p_update_employee(
v_a number,
v_b varchar2,
v_k out number
)
is
begin
update emploee set salary=salary+salary*0.05 where name=v_b and salary<v_a;
if sql%notfound then
v_k:=0;
else
v_k:=1;
end if;
end;
--6
declare
v_a number(10):=2000;
v_b varchar2(24):='wang';
v_k number(10);
begin
p_update_employee (v_a,v_b,v_k);
dbms_output.put_line(v_k);
end;
--7
create or replace procedure p_create_emp(
v_k out varchar2
)
is
begin
v_k:='删除成功!';
end;
--8
declare
v_k varchar2(24);
begin
p_create_emp(v_k);
dbms_output.put_line(v_k);
end;