–1．创建用户kaifa（密码亦为kaifa）， 并分配connect,create table,resource权限。
–sys 登陆 创建用户
create user kaifa identified by kaifa;
–sys登陆 赋予权限
grant connect,create table,resource to kaifa;
–用kaifa用户登陆做以下题
–创建我们所需要的表
create table CCB_GYB
(
ACCOUNTINGDATE DATE,
RMB_YTD_BALANCE NUMBER,
CNY_YTD_BALANCE NUMBER,
USD_YTD_BALANCE NUMBER
);
–创建索引
create unique index CCB_GYB_IDX on CCB_GYB (ACCOUNTINGDATE);
–编写一个函数GetCurrBal(qrp_rq IN VARCHAR2, qrp_code IN VARCHAR2 )
create or replace function fun_a(qrp_rq IN VARCHAR2, qrp_code IN VARCHAR2)
return number
is
–定义变量
money number;
beginif qrp_code = ‘RMB’ then
select RMB_YTD_BALANCE into money
from CCB_GYB c where c.accountingdate = to_date(qrp_rq,’yyyy-mm-dd’);
elsif qrp_code = ‘CNY’ then
select CNY_YTD_BALANCE into money
from CCB_GYB c where c.accountingdate = to_date(qrp_rq,’yyyy-mm-dd’);
elsif qrp_code = ‘USD’ then
select USD_YTD_BALANCE into money
from CCB_GYB c where c.accountingdate = to_date(qrp_rq,’yyyy-mm-dd’);
else
return ‘您输入的币种不存在！’;
end if;
return money;
end;
3．假设有张学生成绩表(CJ)如下
[姓名] [学科] [成绩]
张三 语文 80
张三 数学 86
张三 英语 75
李四 语文 78
李四 数学 85
李四 英语 78
–创建成绩表
create table CJ(
name varchar2(10),
subject varchar2(10),
grade number
)
–插入数据
insert into CJ values(‘张三’,’语文’,80);
insert into CJ values(‘张三’,’数学’,86);
insert into CJ values(‘张三’,’英语’,75);
insert into CJ values(‘李四’,’语文’,78);
insert into CJ values(‘李四’,’数学’,85);
insert into CJ values(‘李四’,’英语’,78);
select * from CJ;
/*
要求统计分数段的人数。显示结果为：
[成绩] [人数]
0<成绩<60 0
60<成绩<80 0
80<成绩<100 5
*/
–创建存储过程
create or replace procedure pro_show
is
e CJ%rowtype;
cursor cur_a is select * from CJ;
grade number;
c1 number ;
c2 number;
c3 number;
begin
c1:=0;
c2:=0;
c3:=0;
dbms_output.put_line(‘[成绩] [人数]’);
open cur_a;
loop
fetch cur_a into e;
exit when cur_a%notfound;
grade:=e.grade;
if grade >0 and grade <60 then c1:=c1+1;
elsif grade >60 and grade <80 then c2:=c2+1;
elsif grade >80 and grade <100 then c3:=c3+1;
end if;
end loop;
dbms_output.put_line(‘0<成绩<60’||’ ‘||c1);
dbms_output.put_line(’60<成绩<80’||’ ‘||c2);
dbms_output.put_line(’80<成绩<100’||’ ‘||c3);
end;call pro_show();
select sysdate as dd from dual;
–用sql查询
select ‘0<成绩<60′ as 成绩 ,count(*) as 人数 from CJ where grade<60
union
select ’60<成绩<80′ as 成绩 ,count(*) as 人数 from CJ where grade>60 and grade<80
union
select ’80<成绩’ as 成绩 ,count(*) as 人数 from CJ where grade>80;
现有需求如下：
（1）要求统计分数段的人数。显示结果为：
[成绩] [人数]
0<成绩<60 0
60<成绩<80 0
80<成绩<100 5
（2）要求根据姓名，把各科成绩显示在一条记录里。显示结果如下：
姓名 语文 数学 英语 总成绩
———- ———- ———- ———- ———-
李四 78 85 78 241
张三 80 86 75 241
总分 158 171 153 482
使用SQL语句或存储过程（显示结果时可用dbms_output打印出来）实现这两个功能。
–要求根据姓名，把各科成绩显示在一条记录里。
create or replace procedure pro_show1
is
–定义游标
cursor cur_a is select distinct name from CJ;
cursor cur_b (ss varchar2) is select * from CJ where name = ss;
v_name CJ.name%type;
e CJ%rowtype;
yw number;
sx number;
yy number;
total number;
ss CJ.SUBJECT%type;
begin
dbms_output.put_line(‘姓名 语文 数学 英语 总分’);
–打开游标
open cur_a ;
loop
fetch cur_a into v_name;
exit when cur_a%notfound;
open cur_b(v_name);
loop
fetch cur_b into e;
exit when cur_b%notfound;
ss:=e.subject;
if ss=’语文’ then
yw:=e.grade;
elsif ss=’数学’ then
sx:=e.grade;
elsif ss= ‘英语’ then
yy:=e.grade;
end if;
end loop;
close cur_b;
total:=yw+sx+yy;
dbms_output.put_line(v_name||’ ‘||yw||’ ‘||sx||’ ‘||yy||’ ‘||total);
end loop;
close cur_a;
end;
—执行
call pro_show1();
