锐客网

  1. 首页 > it技术 > >

安博培训笔记|【安博培训笔记】Oracle7 子程序和程序包 作业20130914

Oracle7 子程序和程序包 作业


1存储过程的语法格式为：
create or replace procedure fuck
is
--变量声明；
begin
--过程主体；
end fuck;
2. 如果存储过程代码无误，可以通过 "/" 来执行代码，编译存储过程。
3.通过exec 或 execute 来调用编译后的存储过程。
--1．创建用户kaifa（密码亦为kaifa），并分配connect,create table,resource权限。
create user kaifa identified by kaifa;
grant connect,create table,resource to kaifa;


/*--2．在做报表统计时，需要根据报表日期和币种从概要表中查询本期余额。
概要表（CCB_GYB）信息如下：
ACCOUNTING_DATEDATE报表日期（唯一索引）
RMB_YTD_BALANCENUMBER人民币余额
CNY_YTD_BALANCENUMBER本位币余额
USD_YTD_BALANCENUMBER外币折美元余额
其中币种代码如下：
--RMB人民币
--CNY本位币
--USD外币折美元
如果币种为RMB，则取出人民币余额作为本期余额；为CNY，则取本位币余额；为USD
则取外币折美元余额。
请编写一个函数GetCurrBal(
qrp_rqIN VARCHAR2,--报表日期
qrp_codeIN 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)；*/
--其中建表语句如下：
create table CCB_GYB
(
ACCOUNTING_DATE DATE,
RMB_YTD_BALANCE NUMBER,
CNY_YTD_BALANCE NUMBER,
USD_YTD_BALANCE NUMBER
);
drop table CCB_GYB;
--创建索引：
create unique index CCB_GYB_IDX on CCB_GYB (ACCOUNTING_DATE);
--
create or replace function GetCurrBal(qrp_rqIN 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_GYBWHEREACCOUNTING_DATE=TO_DATE(qrp_rq,'yyyy-mm-dd');
end;
elsif qrp_code='CNY' then
begin
select CNY_YTD_BALANCE INTO Result FROM CCB_GYBWHEREACCOUNTING_DATE=TO_DATE(qrp_rq,'yyyy-mm-dd');
end;
elsif qrp_code='USD' then
begin
select USD_YTD_BALANCE INTO Result FROM CCB_GYBWHEREACCOUNTING_DATE=TO_DATE(qrp_rq,'yyyy-mm-dd');
end;
end if;
return(Result);
end GetCurrBal;
--------------------------------------------------------------------------------
select t.accounting_date ta,
t.rmb_ytd_balance tr,
t.cny_ytd_balance tc,
t.usd_ytd_balance tu
from CCB_GYB t
------------------
create or replace function getCurrBal(
v_date varchar2,
v_type varchar2
)
return number
is
temp number;
begin
if v_type = 'RMB'then
select c.rmb_ytd_balance into temp fromCCB_GYB c
where to_char(c.accounting_date,'yyyy-fmmm-dd') =v_date;
elsif v_type='CNY' then
select c.cny_ytd_balance into temp fromCCB_GYB c
where to_char(c.accounting_date,'yyyy-mm-dd') =v_date;
elsif v_type='USD' then
select c.usd_ytd_balance into temp fromCCB_GYB c
where to_char(c.accounting_date,'yyyy-mm-dd') =v_date;
end if;
return temp;
exception
when no_data_found then
dbms_output.put_line('没有找到数据');
end;
--------------
select * from CCB_GYB
--------------
declare
v number;
begin
v:=getCurrBal('2013-07-26','CNY');
dbms_output.put_line(v);
end;


select c.rmb_ytd_balance
from CCB_GYB c
where to_char(c.accounting_date, 'yyyy-mm-dd')
= '2013-07-26';
----------------------------------------------------
/*3． 假设有张学生成绩表(CJ)如下
[姓名] [学科] [成绩]
张三 语文 80
张三 数学 86
张三 英语 75
李四 语文 78
李四 数学 85
李四 英语 78


现有需求如下：
（1）要求统计分数段的人数。显示结果为：
[成绩][人数]
0<成绩<600
60<成绩<800
80<成绩<1005
（2）要求根据姓名，把各科成绩显示在一条记录里。显示结果如下：
姓名语文数学英语总成绩
---------- ---------- ---------- ---------- ----------
李四788578241
张三808675241
总分158171153482


使用SQL语句或存储过程（显示结果时可用dbms_output打印出来）实现这两个功能。
*/
create table cj(
姓名 varchar2(20),
学科 varchar2(20),
成绩 number
);
select * from cj;
drop table cj;
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');
(1).select '0<成绩<60' as 成绩 ,count(*) from cj where 成绩<60
union all
select '60<成绩<80' as 成绩 ,count(*) from cj where 成绩>60 and 成绩<80
union all
select '80<成绩' as 成绩 ,count(*) from cj where 成绩>80;
(2).select 姓名,
sum(decode(学科,'语文',成绩,0)) as 语文,
sum(decode(学科,'数学',成绩,0)) as 数学,
sum(decode(学科,'英语',成绩,0)) as 英语,
sum(成绩) as 总成绩
from cj group by 姓名
union all
select '总分' 总分,
sum(decode(学科,'语文',成绩,0)) as 语文,
sum(decode(学科,'数学',成绩,0)) as 数学,
sum(decode(学科,'英语',成绩,0)) as 英语,
sum(成绩) as 总成绩
from cj;
------------------------------------------------------
1）
with aa as(
select '张三' name, '语文' class, 40 score from dual
union
select'张三' name, '数学' class, 86 score from dual
union
select '张三' name, '英语' class, 75score from dual
union
select '李四' name, '语文' class, 78score from dual
union
select '李四' name, '数学' class, 85score from dual
union
select '李四' name, '英语' class, 78 score from dual
)
select cc,count(1) from (
select (case
when(score>0 and score<60) then '成绩不及格'
when(score>60 and score<80) then '成绩及格'
when(score>80 and score<100) then '成绩优秀'
end )cc,name,class,score
from aa ) group by cc;
2）
with aa as(
select '张三' name, '语文' class, 40 score from dual
union
select'张三' name, '数学' class, 86 score from dual
union
select '张三' name, '英语' class, 75score from dual
union
select '李四' name, '语文' class, 78score from dual
union
select '李四' name, '数学' class, 85score from dual
union
select '李四' name, '英语' class, 78 score from dual
)
select aa.name as 姓名,
sum(case class when '语文' then aa.score end)语文,
sum(case class when '数学' then aa.score end)数学,
sum(case class when '英语' then aa.score end)英语,
bb.总成绩
from aa,(select sum(score) 总成绩,name from aa group by name) bb
where aa.name = bb.name
group by aa.name,bb.总成绩
union all
select '总分' 总分,
sum(decode(class,'语文',score,0)) as 语文,
sum(decode(class,'数学',score,0)) as 数学,
sum(decode(class,'英语',score,0)) as 英语,
sum(score) as 总成绩
from aa;
---------------
/*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),
BIRTHDATEdate,
GENDERVARCHAR2(10),
SALARYNUMBER(10,2),
CERT_TYPEVARCHAR2(10),
CERT_NOVARCHAR2(20),
CREATED_TSTIMESTAMP
);
*/
-------------------------------------------------------------------------------
--utl_file
--创建一个目录sys用户
create or replace directory dir2 as 'f:/info';


grant read ,write on directory dir2 to scott;


create tyep record r_name is record...


declare
handle utl_file.file_type;
begin
--r读w写（替换）a追加
handle:=utl_file.fopen('DIR2','test.txt','w');
utl_file.put_line(handle,'hello world');
utl_file.fclose(handle);
end;


select * from tb1010




create or replace procedure write_file
is
cursor cur_name is select
rpad(nvl(t.individualid,' '),20,' ') id,
rpad(nvl(to_char(t.birthdate,'yyyymmdd'),' '),8,' ') bir,
rpad(nvl(t.gender,' '),10,' ') sex,
lpad(nvl(t.salary,'0'),13,'0') sal,
rpad(nvl(t.cert_type,' '),10,' ') ct,
rpad(nvl(t.cert_no,' '),20,' ') cn,
rpad(nvl(to_char(t.created_ts,'yyyymmddhh24missff3'),' '),17,' ') cs
from tb1010 t;
handle utl_file.file_type;
begin
handle:=utl_file.fopen('DIR2','info.txt','a');
for x in cur_name loop
utl_file.put_line(handle,x.id ||' '
||x.bir||' '||x.sex||' '||x.sal||' '||x.ct||' '||x.cn||' '||x.cs);
end loop;
utl_file.fclose(handle);
end;




select * from tb1010;


insert into tb1010 values(
'222222',sysdate,'女',19999.00,
'学生证','123145253523',systimestamp
);




declare


begin
write_file;
end;
-------------------------------------------------------------------------------
create table tb1010(
INDIVIDUALID VARCHAR2(20),
BIRTHDATEdate,
GENDERVARCHAR2(10),
SALARYNUMBER(10,2),
CERT_TYPEVARCHAR2(10),
CERT_NOVARCHAR2(20),
CREATED_TSTIMESTAMP
);
drop table tb1010;
select * from tb1010;
---------
select * from emp;
--
set echo on;
set feedback on;
set trimspool off;
set linesize 120;
set pagesize 2000;
set newpage 1;
set heading on;
set term off;
set termout on;
set timing off;
spool H:\xxoo\spool.txt
select empno,ename,job,nvl(mgr,0),to_number(to_char(hiredate,'yyyymmdd')),nvl(sal,0),nvl(comm,0),deptno from emp;
spool off;
exit;
----------
column empno,ename,job,mgr,hiredate,sal,comm,deptno format a40;
---------------------------------------------------------------------------
/*5． 某语音电话本表信息如下:
Call_book_info
MOBILE_ID 移动号码 VARCHAR2(12)
CALLIN_TIME 呼入时间 Date
CALLOUT_TIME 呼出时间 Date
STATUS 状态 CHAR(1)


在某次大批量操作后，数据记录达到100万，MOBILE_ID估计有2万个重复，现要求删除重复的号码（只保留一条），因为该表是业务表，删除时不能影响业务的正常使用。编写存储过程实现删除重复号码的功能。
要求如下：
（1） 为保证删除的数据以后可查，在删除时要先做备份，备份不成功则不能进行删除。
（2） 要有日志记录，比如删除所花时间，删除成功了多少条，失败多少条等操作信息。*/
------------------------------------------------------------------------------------------------
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'
);
insert into call_book_info values(
'12345678987',sysdate,sysdate,'0'
);






select c.*,rowid from call_book_info c






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 issuccessfrom 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;


select * from user_tables;


select count(*) from temp2;


begin
delete_call_book;
end;


grant create any table to scott;














select dbms_utility.get_time() from dual
------------------------------------------------------------------------------------------------
1.第1个问题，可采用
create table tablename_back
as select * from tablename 的方式来备份

2.第2个问题很纠结，按理说删除重复数据批量删除比较快，但这样的结果是要失败全都失败，要成功全都成功了，题目要求统计失败条数和成功条数，那我能想到只有逐条删除才能统计出来，

思路如下，不一定正确，仅供参考，以下是伪代码，需要自己修改

---批量
date dt1,dt2
number timeSpan;
number falseCount,succeseCount;
varchar2(2000) delStr；
alteStr='alter table tb add isdelete char '
execute alteStr---加个字段用于记录删除标记，逻辑删除
delStr=‘update tb a set a.isdelete=1 where a.rowid>(select min(rowid) from tb b where b.MOBILE_ID=a.MOBILE_ID) and exists (select 1 from tb c where c.MOBILE_ID=a.MOBILE_ID)’
dt1=sysdate;
execute delStr; 执行删除语句，其实是将重复的数据除第一条外isdelete字段设置为1
dt2=sysdate;
timeSpan=(dt2-dt1)*24*60-----花费时间 ，单位分钟

逐条删除的话，需要先查出哪些数据重复了
然后通过游标遍历逐条删除
成功则succeseCount+1
失败则falseCount+1


----------
create table Call_book_info(
MOBILE_ID VARCHAR2(12),
CALLIN_TIME Date,
CALLOUT_TIME Date,
STATUS CHAR(1)
);
drop table Call_book_info;
select * from Call_book_info;
------------


create or replace procedure fuck
is
v_exists number;
begin
select count(*) into v_exists from user_tables where table_name = 'TEST1';
if v_exists > 0 then
execute immediate 'drop table TEST1';
end if;
execute immediate '
create table test1(
mobile_id varchar2(12),
callin_time date,
callout_time date,
status char(1)
)';
insert into test1(mobile_id,callin_time,callout_time,
status)
select * from Call_book_info;
commit;
delete from Call_book_info where mobile_id
not in (select min(mobile_id) from call_book_info);
end;
sql plus 中输入 exec fuck;
-------------------------------------------------------------
create or replace procedure fuck
is--此处少了is或as
--begin--声明变量要出现在begin end块前
--declare --存储过程中不用declare
v_exists number;
begin--
select count(*) into v_exists from user_tables where table_name = 'TEST1';
if v_exists > 0 then
execute immediate 'drop table TEST1';
end if;
-- end;
-- begin
execute immediate '
create table test1(
mobile_id varchar2(12),
callin_time date,
callout_time date,
status char(1)
)';
--commit; --创建表属ddl语句，不需要commit;
--end;
insert into test1(mobile_id,callin_time,callout_time,
status)
select * from Call_book_info;
commit;
delete from Call_book_info where mobile_id
not in (select min(mobile_id) from call_book_info);
end;
/
------------------------------------------------------------------------------------------------

    推荐阅读


    上一篇：java中的字符编码方式

    下一篇：DP