Oracle 数据库 练习题 T7

1.创建用户kaifa（密码亦为kaifa），    并分配connect,create table,resource权限。

 2.

在做报表统计时，需要根据报表日期和币种从概要表中查询本期余额。

概要表（CCB_GYB）信息如下：

ACCOUNTING_DATE	DATE	报表日期（唯一索引）
RMB_YTD_BALANCE	NUMBER	人民币余额
CNY_YTD_BALANCE	NUMBER	本位币余额
USD_YTD_BALANCE	NUMBER	外币折美元余额

其中币种代码如下：

  --RMB    人民币

  --CNY    本位币

  --USD    外币折美元

如果币种为RMB，则取出人民币余额作为本期余额；为CNY，则取本位币余额；为USD

则取外币折美元余额。

请编写一个函数GetCurrBal(

                qrp_rq      IN VARCHAR2,  --报表日期

             qrp_code   IN VARCHAR2   --币种    

)

实现此功能，并能在sqlplus里调用。

其中建表语句如下：

create table CCB_GYB

(

  ACCOUNTING_DATE DATE,

  RMB_YTD_BALANCE NUMBER,

  CNY_YTD_BALANCE NUMBER,

  USD_YTD_BALANCE NUMBER

)；

创建索引：create unique index CCB_GYB_IDX on CCB_GYB (ACCOUNTING_DATE)；

 3.

假设有张学生成绩表(CJ)如下 
[姓名] [学科] [成绩]
张三 语文 80
张三 数学 86
张三 英语 75
李四 语文 78
李四 数学 85
李四 英语 78

现有需求如下：
（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打印出来）实现这两个功能。

 

 4.

某一客户表包含如下信息：

 

INDIVIDUALID	客户ID	VARCHAR2(20)（唯一键）
BIRTHDATE	出生日期	Date
GENDER	性别	VARCHAR2(10)
SALARY	月收入	NUMBER（10，2）
CERT-TYPE	证件类型	VARCHAR2(10)
CERT-NO	证件号码	VARCHAR2(20)
CREATED-TS	进入系统的时间	TIMESTAMP

现要把该表数据导出成文件，导出的内容格式如下:

属性列	列长度	备注
INDIVIDUALID	20
BIRTHDATE	8	格式为：yyyymmdd
GENDER	10
SALARY	13
CERT-TYPE	10
CERT-NO	20
CREATED-TS	17	格式为：yyyymmddhh24missff3

要求每个字段列的内容长度是固定的，不足部分由空格补齐，字符串左对齐（右补空格），数字右对齐。如果列的内容为null，需先进行处理，字符串默认为空格，数字默认为0，日期默认为99991231,时间戳默认为99991231000000000。

请编写程序实现该导出功能。

 

 

创建表脚本：

create table tb1010(

INDIVIDUALID VARCHAR2(20),

BIRTHDATE    date,

GENDER       VARCHAR2(10),

SALARY       NUMBER(10,2),

CERT_TYPE    VARCHAR2(10),

CERT_NO      VARCHAR2(20),

CREATED_TS   TIMESTAMP

);

5.

某语音电话本表信息如下:

Call_book_info

MOBILE_ID	移动号码	VARCHAR2(12)
CALLIN_TIME	呼入时间	Date
CALLOUT_TIME	呼出时间	Date
STATUS	状态	CHAR(1)

 

在某次大批量操作后，数据记录达到100万，MOBILE_ID估计有2万个重复，现要求删除重复的号码（只保留一条），因为该表是业务表，删除时不能影响业务的正常使用。编写存储过程实现删除重复号码的功能。

要求如下：

为保证删除的数据以后可查，在删除时要先做备份，备份不成功则不能进行删除。

要有日志记录，比如删除所花时间，删除成功了多少条，失败多少条等操作信息。

create table CCB_GYB
(
  ACCOUNTING_DATE DATE,
  RMB_YTD_BALANCE NUMBER,
  CNY_YTD_BALANCE NUMBER,
  USD_YTD_BALANCE NUMBER
);
create unique index CCB_GYB_IDX on CCB_GYB (ACCOUNTING_DATE);
-------------------------------------------------------------------------------------
2.----------------------------
create or replace function GetCurrBal(qrp_rq  IN VARCHAR2,qrp_code IN VARCHAR2)
return number
is
  Result number;
begin
  if qrp_code='RMB' then
    begin
      select RMB_YTD_BALANCE INTO Result FROM CCB_GYB  WHERE  ACCOUNTING_DATE=TO_DATE(qrp_rq,'yyyy-mm-dd');
    end;
  elsif qrp_code='CNY' then
    begin
      select CNY_YTD_BALANCE INTO Result FROM CCB_GYB  WHERE  ACCOUNTING_DATE=TO_DATE(qrp_rq,'yyyy-mm-dd');
    end;
  elsif qrp_code='USD' then
    begin
       select USD_YTD_BALANCE INTO Result FROM CCB_GYB  WHERE  ACCOUNTING_DATE=TO_DATE(qrp_rq,'yyyy-mm-dd');
    end; 
  end if;
  return(Result);  
end GetCurrBal;
----测试
declare
     v number;
begin
   v:=getCurrBal('2017-07-27','RMB');
     dbms_output.put_line(v);
end;
3.---------------------------------------------
（1）要求统计分数段的人数。显示结果为：
[成绩]      [人数]
0<成绩<60      0 
60<成绩<80     0 
80<成绩<100    5
create table CJ(
       Cname varchar2(20),
       Csubject varchar2(20),
       Cgrade 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 '0<成绩<60' as Cgrade ,count(*) from cj where Cgrade<60
union all
select '60<成绩<80' as Cgrade ,count(*) from cj where Cgrade>=60 and Cgrade<80
union all
select '80<成绩' as Cgrade ,count(*) from cj where Cgrade>=80;

select Cname,
             sum(decode(Csubject,'语文',Cgrade,0)) as 语文,--"DECODE"  条件取值
             sum(decode(Csubject,'数学',Cgrade,0)) as 数学,--IF 条件=值1 RETURN(翻译值1) ELSIF 条件=值2　ELSE RETURN(缺省值)
             sum(decode(Csubject,'英语',Cgrade,0)) as 英语,
             sum(Cgrade) as 总成绩
             from cj group by Cname
union all
select '总分' 总分,
             sum(decode(Csubject,'语文',Cgrade,0)) as 语文,
             sum(decode(Csubject,'数学',Cgrade,0)) as 数学,
             sum(decode(Csubject,'英语',Cgrade,0)) as 英语,
             sum(Cgrade) as 总成绩
             from cj;
----------------------------
4.
---------------------------------------
5
create table call_book_info(
       moble_id varchar2(12),
       callin_time date,
       callout_time date,
       status char(1)
)
insert into call_book_info values('123124125212',sysdate,sysdate,'1');
insert into call_book_info values('123124125212',sysdate,sysdate,'1');
insert into call_book_info values('123124125212',sysdate,sysdate,'1');
insert into call_book_info values('123124125212',sysdate,sysdate,'1');
insert into call_book_info values('123124125212',sysdate,sysdate,'1');
insert into call_book_info values('123124125212',sysdate,sysdate,'1');
insert into call_book_info values('123124125212',sysdate,sysdate,'1');
insert into call_book_info values('12345678987',sysdate,sysdate,'0');
insert into call_book_info values('12345678987',sysdate,sysdate,'0');
insert into call_book_info values('12345678987',sysdate,sysdate,'0');
insert into call_book_info values('12345678987',sysdate,sysdate,'0');
insert into call_book_info values('12345678987',sysdate,sysdate,'0');
insert into call_book_info values('12345678987',sysdate,sysdate,'0');
insert into call_book_info values('12345678987',sysdate,sysdate,'0');

create or replace procedure delete_call_book
is
  create_sql varchar2(1000):=
             'create table temp as select * from call_book_info';
  issuccess number;
  begintime number;
  endtime number;
  sumcount number;
  shengcount number;
  zuicount number;
begin
  --execute immediate 'drop table temp';
  execute immediate create_sql;
  commit;
  select count(*) into issuccess  from user_tables;
  select count(*) into sumcount from call_book_info;
  select count(*) into zuicount from (select min(rowid) from call_book_info group by moble_id);

  if issuccess>0 then
     begintime:=dbms_utility.get_time();
     dbms_output.put_line(begintime);
     delete call_book_info where rowid
          not in(select min(rowid) from call_book_info group by moble_id); 
     endtime:=dbms_utility.get_time();
     dbms_output.put_line(endtime);  
   dbms_output.put_line(endtime-begintime);
  select count(*) into shengcount from call_book_info;
  dbms_output.put_line('成功删除：'||(sumcount-shengcount));
  dbms_output.put_line('不成功：'||(shengcount-zuicount));

  else dbms_output.put_line('没有备份成功');
  end if;
end;
begin
  delete_call_book;
end;