Oracle实战练习.doc

Oracle实战练习用检 用户号2002026364工单 2000739486命名规则：type_姓名全拼_题号（如同一题内有多个同type，则以A,B,C区分）type:序列号：seq快表： qt表: t触发器：tri函数: f存储过程: p数据链：dl同义词：syn复习回顾：1、not in 、not exists、in 、exists ，from 表的顺序2、外部连接 (+)3、子查询4、构建虚表5、排序(统计）6、分组7、字段加函数8、伪列 rowid rownum9、快表10、并集、与集等11、调试方法：test(pl/sql developer) / pub_error_log/dbms_output包12、触发器、序列、JOB练习题：1、 建立快表，字段只有三个，序号，户名，地址，序号为number，户名、地址同cim_customer的user_name,address字段Create table t_tom_1 asSelect 1 no,user_name,addressFrom cim_customerWhere 1= 22、 库表记录超过5000记录，如何限制只取5000条，以select * from cim_customer为例，完善SQL；select * from cim_customer where rownum =5000create table t as select * from all_objects where rownum = 5000;set timing onselect object_name, object_idfrom ( select object_name, object_idfrom twhere object_name like %c)where rownum = 6;select object_name, object_idfrom ( select object_name, object_idfrom t sample ( 1 )where object_name like %order by dbms_random.random )where rownum （大于1 的数值）、=（大于或等于1 的数值）、=（大于或等于1 的数值），否则无结果）3、 建立序列sqeuence，最小值100，最大值10000，步长为2，每次缓存取10条create sequence seq_tom_3minvalue 100maxvalue 10000start with 100increment by 2cache 10order;4、 建立函数，实现将cim_customer前10条记录插入到快表 ，序号采用序列号实现create function f_tom_4 return number asv_ret number;begin v_ret := -10; insert into t_tom_1(no,user_name,address) select seq_tom_3.nextval,user_name,address from cim_customer where rownum =to_date(2008-5-1,yyyy-mm-dd)and pay_dateto_date(2008-6-1,yyyy-mm-dd)and business_place_code like 13020201% and a.money_to =B and collector_id in (001,002,003,004)group by collector_id;6、 业扩档案：计量点(cim_agreement),表位置（cim_meter_position）具有一一对应关系，也就是说计量点库表中存在用户和计量点属性（字段），表位置也存在对应的用户和计量点属性（字段）。试着找出计量点库表中有，而表位置没有的用户、计量点信息（反过来找出表位置库表有，而计量点库表没有的用户、计量点信息）（用两种方法: （+） ，not exists ）select a.*,b.*from cim_agreement a,cim_meter_position bwhere a.user_no = b.user_no(+) and a.fee_no = b.group_no(+)and b.user_no is nullselect a.*from cim_agreement awhere not exists (select 1 from cim_meter_position b where a.user_no = b.user_no and a.fee_no = b.group_no )7、 电费有个规则：按户汇总：应收-实收=欠费，应收为df_money_files的should_money,实收为sf_charge_record的fact_money,欠费为sf_arrearage的owe_money,试找出mon=200802月份，business_place_code = 13220201，存在不满足规则的记录数select user_no,sum(should_money), sum(fact_money),sum(owe_money) from (select user_no,sum(should_money) should_money,0 fact_money,0 owe_money from df_money_files where mon = 200802 -and user_no = 6014001547 and business_place_code = 13220201 group by user_no union all select user_no,0 should_money,sum(fact_money) fact_money,0 owe_money from sf_charge_record where mon = 200802 -and user_no =6014001547 and business_place_code = 13220201 group by user_no union all select user_no,0 should_money,0 fact_money,sum(owe_money) owe_money from sf_arrearage where mon = 200802 - and user_no =6014001547 and business_place_code = 13220201 group by user_no ) t group by user_no having sum(should_money) - sum(fact_money) sum(owe_money)-having abs(sum(should_money) - sum(fact_money) - sum(owe_money)0.108、 编写存储过程，要求存储过程体内有两个子begin end ，第一个begin end没有exception处理，第二个、存储过程主体有exception(分别输出”err2!”，“err0!”。每个begin end主体内语句自由编写。出错语句处理： 存储过程变量定义 v_user_name varchar2(8);出错语句：select user_name into v_user_name from cim_customer where customer_id = 5014002956;将出错语句分别放在第一个begin end中，第二个begin end中，存储过程主体begin end中，输出分别是什么出错语句位置提示第一个begin end中这里输入结果第二个begin end中这里输入结果存储过程主体begin end中这里输入结果把错误输出改为 pub_error_log 错误陷进捕获表形式格式：（v_resule number ）p_pub_error_log(v_result,sqlcode,sqlerrm,存储过程名);create or replace procedure p_tom_8 isv_user_name varchar2(8);v_date date;begin begin select sysdate into v_date from dual; end; begin select sysdate into v_date from dual; exception when others then dbms_output.put_line(er2!); end;select user_name into v_user_name from cim_customer where customer_id = 5014002956; dbms_output.put_line(ok!);exception when others then dbms_output.put_line(er0!); p_pub_error_log(v_ret,sqlcode,sqlerrm,p_tom_8);end;declarebegin p_tom_8;end;9、 触发器：建立快表，字段只有三个，序号，户名，地址，序号为number，户名、地址同cim_customer的user_name,address字段，在1、的库表上建立update 触发器，将update前的序号，户名，地址行数据都插入到新建立的快表中。（通过pl/sql对快表某行三个字段都做修改并保存）。INSERTUPDATEDELETE:OLD:NEWcreate or replace trigger tri_tom_10 before update on t_tom_1 for each rowdeclare begin insert into t_tom_10(no,user_name,address) values (:old.no,:old.user_name,:old.address);end tri_tom_10;10、 上下级关系 ：依据库表 rs_dept （字段：dept_id,upper_dept_id）查找 13221202的所有上级，查找1303 的所有下级Oracle数据库中 Start with .connect by Prior 是一个遍历一棵树型表的递归函数，Level就是一个树中，各节点的层数。 向下查询一棵树（知道一棵子树的根，把下面的所有节点遍历出来）：select id from tab_name start with id=要查询的根节点 connect by prior id=fatherid; 向上（倒查）查询一棵树（知道一棵子树的末节点，把这个末节点的的所有上级（一至到整棵树的根节点）遍历出来）：select id from tab_name start with id=要查询的末节点 connect by prior fatherid=id;/ernest100/blog/item/87406f270c445803918f9d8c.htmlselect dept_code ,dept_namefrom rs_dept start with dept_code = 13221202connect by Prior upper_dept_id = dept_id select dept_code ,dept_namefrom rs_dept start with dept_code = 130302connect by Prior dept_id = upper_dept_id/nboy/blog/item/777efdfac916b9deb48f31d4.html 11、 建立一个JOB把8、内容做成每周五晚上11点执行的一个任务12、 在hzyd当前用户下建立全局同义词（快表1：假如：t_tom），通过hyyd/j2yd登录，可以通过直接输入select * from t_tom_1进行访问13、 EXP/IMP 支持列表EXP/IMP一般EXPORT时使用低版本，IMPORT时使用与目标库相同的IMP工具10.1.09.2.0..58.0.x7.3.x10.1.0EXP10EXP920EXP901EXP817EXP816EXP815N/AN/A9.2.0EXP920EXP920EXP901EXP817EXP816EXP815EXP80xN/A9.0.1EXP901EXP901EXP901EXP817EXP816EXP815EXP80xN/A8.1.7EXP817EXP817EXP817EXP817EXP816EXP815EXP80x#EXP73x8.1.6EXP81