Oracle 数据库练习题 T7

来源：互联网发布：js仿ios日期选择器编辑：程序博客网时间：2024/06/11 09:51

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

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

概要表（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_IDXon CCB_GYB (ACCOUNTING_DATE)；

假设有张学生成绩表(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打印出来）实现这两个功能。

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

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

BIRTHDATE

格式为：yyyymmdd

GENDER

SALARY

CERT-TYPE

CERT-NO

CREATED-TS

格式为：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

);

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

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 numberis  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    5create 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<60union allselect '60<成绩<80' as Cgrade ,count(*) from cj where Cgrade>=60 and Cgrade<80union allselect '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 Cnameunion allselect '总分' 总分,             sum(decode(Csubject,'语文',Cgrade,0)) as 语文,             sum(decode(Csubject,'数学',Cgrade,0)) as 数学,             sum(decode(Csubject,'英语',Cgrade,0)) as 英语,             sum(Cgrade) as 总成绩             from cj;----------------------------4.---------------------------------------5create 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_bookis  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;

阅读全文

0 0

Oracle 数据库 练习题 T7

Oracle 数据库练习题 T7