oracle数据库开发案例

原创 2007年09月28日 11:21:00

1、数据库在应用系统中的重要性
定义:是数据集合或仓库,在计算机存储上,有组织的
类型:关系数据库、内存数据库、嵌入数据库等
环节:需求设计、开发测试、部署实施、监控维护和patch升级等
数据库开发(后台开发)
 我们的应用开发离不开数据库开发
 java、c++、c等中的数据库编程≠数据库开发
 我们不仅仅掌握简单的DML语句
2、常见(对象|函数|数据字典|操作符)
常见对象
table、index、materialized view/snapshot、cluster
存储上占物理空间
Procedure、function、trigger、package、package body
程序体
View、sequence、synonym、db link
常见数据字典
对象级的
 user_clusters、user_db_links、user_indexes、user_mviews、user_sequences、user_snapshots、user_synonyms、user_tables、user_types、user_views、user_triggers
有关表的
 user_tab_cols、user_tab_columns、user_tab_comments、user_tab_partitions、user_tab_privs、user_tab_subpartitions
有关索引的
 user_indextypes、user_ind_columns、user_ind_partitions、user_ind_subpartitions
有关权限和约束的
 user_sys_privs、user_ts_quotas、user_col_privs、user_constraints、user_cons_columns
其他的
 tab、dict、dual等
常见操作符
+、- 、 * 、 / 、 NULL 、 || 、 = 、!= 、 <> 、 < 、 > 、 <= 、 >=、 (not) between... And、like、or、 (not) exists
(not) in、any、all
union(all)、intersect、minus
3、养成良好的编码规范
好的命名吗
  数据库对象名、脚本文件名,程序体变量名等
有注释吗
  为每个脚本文件每个对象每个属性作有助的注释
布局合理吗
  换行、缩进、关键字突出打头,begin/end对齐等
异常有处理吗
  不要吝啬Exception when/try catch等的使用
  回滚脚本
4、开发案例实战演习
场景描述
  sp提供业务供用户定购使用(Mini版)
场景设计
  E-R图,模型建立等
基本对象定义:
  Sp信息表spinfo
  业务表service
  用户表subscriber
  定购关系事实表subscription
============================================================================
5、实战演习
select语句
    结果集
    select into的俘获异常
    select for update的Lock产生
    需要select *么?
索引的正确使用
    索引是什么
    什么情况下要使用索引和不使用索引
    索引对于性能来将是一把双刃剑
游标问题
    什么情况下需使用游标
    显式游标和隐式游标
    解决“ora-010000”异常的产生
特殊字符与通配符的处理
    %、&等
    关键字escape的使用
    set define off的使用
    字符串中两个单引号代表一个单引号等
主从表问题
    PK和FK
    操作时的先后顺序
    参数“ON DELETE CASCADE”的看法
==============================================================================
实战脚本

Rem
Rem Subject: oracle数据库开发案例脚本
Rem Copyright (c) ASPire 2007. All Rights Reserved.
Rem Dbversion:1.0.0

Rem MODIFIED (YYYY/MM/DD) DESCRIPTION
Rem (有待添加)
Rem ShiYiHai 2007/9/10  新建开发案例脚本

-----------------------------------1、建表、索引和约束---------------------------------------------
prompt
prompt SP信息表
prompt ======================================
prompt
CREATE TABLE SPinfo
(
 SPId  VARCHAR2(12) NOT NULL,
 SPName  VARCHAR2(100) NOT NULL,
 Status  VARCHAR2(1) NOT NULL
);
alter table SPinfo
add constraint pk_spinfo primary key (SPID)
using index;


prompt
prompt 服务信息表
prompt ======================================
prompt
CREATE TABLE Service
(
 ServiceId VARCHAR2(12) primary key,
 ServName VARCHAR2(64) NOT NULL,
 SPId  VARCHAR2(12) NOT NULL,
 StartTime VARCHAR2(14),
 EndTime VARCHAR2(14),
 status  VARCHAR2(2)
 );


prompt
prompt 用户基本信息
prompt ======================================
prompt
CREATE TABLE SUBSCRIBER
(
 SubsId  VARCHAR2(15) NOT NULL,
 Name  VARCHAR2(128) NOT NULL,
 Sex  CHAR(1)  NOT NULL,--M:男 F:女
 Status  CHAR(1)  NOT NULL,
 score  number(12),
 constraint pk_SUBSCRIBER primary key(SubsId)
);

prompt
prompt 订购关系事实表
prompt ======================================
prompt
CREATE TABLE SUBSCRIPTION
(
 SubsId   VARCHAR2(15) NOT NULL,
 ServiceId  VARCHAR2(12) NOT NULL,
 Subscribe_Date  DATE  default sysdate NOT NULL
);

 

alter table Service
add constraint FK_Service_SPid foreign key (spid) references spinfo(spid);

create index indx_SUBSCRIPTION_SubsId on SUBSCRIPTION(SubsId);


-----------------------------------2、采用不同方式初始化数据---------------------------------------------
insert into SPINFO (SPID, SPNAME, STATUS)
values ('444401', '444401', 'A');
insert into SPINFO (SPID, SPNAME, STATUS)
values ('900652', 'AutoCSSP', 'S');
insert into SPINFO (SPID, SPNAME, STATUS)
values ('832014', '紫移通', 'X');
insert into SPINFO
values ('911001', '灵通网fs1', 'A');
commit;
--A正常,S暂停,X下线


create sequence seq_service_serviceid
increment by 1
start with 1
maxvalue 999999999
nocycle
cache 20;


declare
v_num number;
v_servname varchar2(64);
v_spid varchar2(12);
v_starttime date;
v_endtime date;
v_status varchar2(2);
begin
 for v_num in 1 .. 1000 loop
  v_servname := '业务'||v_num;
  v_spid := '444401';
  v_starttime := sysdate;
  v_endtime := sysdate+365;
  v_status := 'A';
  insert into Service(serviceid,servname,spid,starttime,endtime,status)
  values(seq_service_serviceid.nextval,v_servname,v_spid,v_starttime,v_endtime,v_status);
  if mod(v_num,100)=0 then
   commit;
  end if;
 end loop;
exception
 when others then
  rollback;
  dbms_output.put_line(substr(sqlerrm,1,256));
  return;
end;
/

insert /*+ append */ into service
select seq_service_serviceid.nextval,'业务'||seq_service_serviceid.currval,
       '900652',sysdate,to_date('20100101235959','yyyy-mm-dd hh24:mi:ss'),'S'
from service;
commit;

insert  into service
select seq_service_serviceid.nextval,'业务'||seq_service_serviceid.currval,
       '832014',sysdate,to_date('20090109235959','yyyy-mm-dd hh24:mi:ss'),'X'
from service
where spid='444401';
commit;

--采用sqlldr往subscriber中插入数据
--采用@执行脚本载入数据


---------------3、当sp下线时sp相应的业务也下线,当sp暂停时sp相应的业务也暂停-----------
prompt
prompt sp状态变更时业务对应状态也作相应变更
prompt 当sp下线时sp相应的业务也下线,当sp暂停时sp相应的业务也暂停
prompt ======================================
prompt
create or replace trigger trg_spinfo
AFTER update on spinfo
for each row
begin
  if updating then
    if(:old.status <> 'X') and (:new.status = 'X') then
      update service
      set status = 'X'
      where spid = :new.spid;
    end if;
    if(:old.status = 'A') and (:new.status = 'S') then
      update service
      set status = 'S'
      where spid = :new.spid;
    end if;
  end if;
end trg_spinfo;
/


-------4、每月订购业务数排名前100的用户奖励积分,积分为当月定购的业务数;同时将订购人气最旺的业务有效期延长半年时间-----------

prompt
prompt 创建中间表来保存每月订购业务数的对应中间表
prompt ======================================
prompt
create table make_score_user
(
 SubsId   VARCHAR2(15) NOT NULL,
 num   number(12)
);

 

prompt
prompt 每月订购业务数排名前100的用户奖励积分,积分为当月定购的业务数;
prompt 同时将订购人气最旺的业务有效期延长半年时间
prompt ==================================================
prompt
CREATE OR REPLACE PROCEDURE proc_make_score(p_month in varchar2)
AS
BEGIN

    --对输入参数做判定
    if(length(p_month)<>6) then  --检查日期的格式
 dbms_output.put_line('日期格式不对,请输入YYYYMM.');
 return;
    end if;
   
    --清理中间表数据
    begin
     execute immediate 'truncate table make_score_user';
    exception
     when others then
      dbms_output.put_line(substr(sqlerrm,1,256));
      return;
    end;
   
    --插入每月用户订购业务数到中间表中
    begin
     insert into make_score_user(subsid,num)
     select subsid,count(distinct serviceid)
     from SUBSCRIPTION
     where to_char(Subscribe_Date,'yyyymm') = p_month
     group by subsid;
     commit;
    exception
     when others then
      dbms_output.put_line(substr(sqlerrm,1,256));
      rollback;
      return;
    end;
   
    --给积分(积分为当月定购的业务数)
    begin
     FOR vcursor in (select subsid,num from (select rownum as rn,subsid,num from (select subsid,num from make_score_user order by num desc)) a where a.rn<=100)
     loop
       update SUBSCRIBER
       set score=score+vcursor.num
       where subsid=vcursor.subsid;
     end loop;
     commit;
    exception
     when others then
      dbms_output.put_line(substr(sqlerrm,1,256));
      rollback;
      return;
    end;
   
   
    --将订购人气最旺的业务有效期延长半年时间
    begin
     update service
     set endtime=add_months(endtime,6)
     where serviceid in (
          select serviceid from subscription
          group by serviceid having count(*)=   
         (select max(num) from
          (select serviceid,count(*) as num
           from subscription
        group by serviceid
       )
      )
    );
     commit;
    exception
     when others then
      dbms_output.put_line(substr(sqlerrm,1,256));
      rollback;
      return;
    end;

    commit;
    return;

EXCEPTION
    when OTHERS then
    rollback;

END proc_make_score;
/


-------5、实现用户数据同步的n种方式-----------
prompt 1、视图方式;
prompt 2、同义词方式;
prompt 3、实体化视图方式;
prompt 4、procedure方式;

 

ACCEPT user_data_user CHAR prompt 'Please input the db user name for train to another user:'
--也可以是db link
--ACCEPT user_data_link CHAR prompt 'Please input the db link name for dbA to dbB:'

prompt
prompt Creating package syn_user_data
prompt =========================
prompt
create or replace package syn_user_data as

-- 1. 同步方法1(全量在一个事务中)
procedure proc_syn_full_user_data;

-- 2. 同步方法2(一条一条记录比较)
procedure proc_syn_increment_user_data;

end syn_user_data;
/


prompt
prompt Creating package body syn_user_data
prompt ==============================
prompt
create or replace package body syn_user_data as

--increment方式
PROCEDURE proc_syn_full_user_data IS
BEGIN
 delete from SUBSCRIBER;
 insert into SUBSCRIBER(subsid,name,sex,status,score)
 select subsid,name,sex,status,score
 from &user_data_user .SUBSCRIBER;
commit;
return;
END proc_syn_full_user_data;


--increment方式
PROCEDURE proc_syn_increment_user_data IS
TYPE RefCurTyp IS REF CURSOR;
vcursor RefCurTyp;
errstr varchar2(1024);
v_full_count number(2);
v_pk_count number(2);

BEGIN
FOR vcursor in (select subsid,name,sex,status,score from &user_data_user .SUBSCRIBER)
LOOP
   begin
      select nvl(count(*),0) into v_full_count from SUBSCRIBER
      where subsid = vcursor.subsid
        and name = vcursor.name
        and sex  = vcursor.sex
        and status = vcursor.status
        and ((score = vcursor.score and vcursor.score is not null) or (score is null and vcursor.score is null));
      if v_full_count > 0 then  --两边数据完全匹配,不作任何操作
       null;
      else
       select nvl(count(*),0) into v_pk_count from SUBSCRIBER
        where subsid = vcursor.subsid;
       if v_pk_count > 0 then  --两边数据不完全匹配,需update
          update SUBSCRIBER set (subsid,name,sex,status,score)
                    =(select vcursor.subsid,vcursor.name,vcursor.sex,vcursor.status,vcursor.score from dual)
          where subsid = vcursor.subsid;
        else   --不存在的数据,需insert
          insert into SUBSCRIBER(subsid,name,sex,status,score)
          values(vcursor.subsid,vcursor.name,vcursor.sex,vcursor.status,vcursor.score);
        end if;
      end if;

      EXCEPTION
         when OTHERS then
         begin
            errstr := SQLERRM;
            --这里可写同步出错日志信息到日志表中
         end;
   end;
END LOOP;

--删除portal用户下冗余的sp
delete from SUBSCRIBER a where not exists (select 1 from &user_data_user .SUBSCRIBER b where a.subsid = b.subsid);

commit;
return;
END proc_syn_increment_user_data;

end syn_user_data;
/


prompt
prompt 每天定时在早上6点执行同步用户信息
prompt =============================
prompt
variable v_job number;
Set ServerOutput on
begin
  Dbms_Job.Submit
    (
      job       => :v_job,
      what      => 'syn_user_data.proc_syn_increment_user_data;',
      next_date => to_date(to_char(sysdate+1,'yyyy/mm/dd') || ' 6:00:00','yyyy/mm/dd hh24:mi:ss'), /* run at 6:00 */
      interval  => 'to_date(to_char(sysdate+1,''yyyy/mm/dd'') || '' 6:00:00'',''yyyy/mm/dd hh24:mi:ss'')'
    );
  Dbms_Job.Run ( :v_job );
  Dbms_Output.Put_Line ( 'Submitted as job # ' || to_char ( :v_job ) );
end;
/
commit;

 

Oracle442个应用场景----------数据库实例

应用场景1-7为Oracle的安装过程,此处不过多的讲解,网上的材料很多,也许会在后期中补上。 应用场景8 查看所有数据文件的位置: SELECT NAME FROM V$DATAFILE; ...
  • u011225629
  • u011225629
  • 2015年06月23日 20:25
  • 1649

oracle 数据库开发面试题

最近参加了4、5场面试,总结一下竞聘oracle 开发岗位最长问到哪些问题: 1、delete 与 truncate 区别? 1)truncate 是DDL语句,delete 是DML语句; 2)...
  • IndexMan
  • IndexMan
  • 2014年05月15日 20:41
  • 14191

数据库开发规范

1.背景 应用系统数据库方面设计水平的高低直接关系到应用系统运行的稳定、高效性,为保证数据库方面的设计质量和效率,必须建立标准、完备的开发规范。本规范从数据库维护技术角度及数据库设计规范性要求出发,给...
  • oradh
  • oradh
  • 2014年05月10日 10:29
  • 1834

oracle数据库设计实例

1)要求 设计一个网上购物程序(使用powerdesigner建立模型并编写测试数据)。有一下需求: 管理员可以在后台添加商品,每个商品属于一个商品组 可以对管理员进行分组,对...
  • YABIGNSHI
  • YABIGNSHI
  • 2013年04月02日 10:56
  • 1564

Oracle数据库开发和设计规范

1 命名原则 1.1约定 ü  是指对数据库、数据库对象如表、字段、索引、序列、存储过程等的命名约定; ü  命名使用富有意义的英文词汇,尽量避免使用缩写,多个单词组成的,中间以下划...
  • g_blue_wind
  • g_blue_wind
  • 2016年12月19日 10:16
  • 209

oracle数据库开发实战

现有一个商店的数据库,记录顾客及其购物情况,由下面三个表组成:    商店 product(商品号 productid,商品名 productname,单价 unitprice,商品类别 categ...
  • miachen520
  • miachen520
  • 2016年06月19日 15:43
  • 1065

Oracle Database 12c可插拔数据库应用案例

对于那些苦于管理大规模数据库的DBA来说,也许新版的Oracle Database 12c中的可插拔数据库(Pluggable Database,简称PDB)功能将成为他们的福音。   针对...
  • qq_20545159
  • qq_20545159
  • 2015年01月31日 20:57
  • 1347

Java的数据库编程之入门案例

通过上一篇博客Java的数据库编程之背景概述,可以得出: 1、JDBC API与驱动器管理器是有SUN公司制定并退出的; 2、每个数据库的驱动器程序是由每个数据库开发商以及数据库工具开发商在遵循S...
  • yu102655
  • yu102655
  • 2017年02月15日 14:58
  • 3005

Oracle 数据库12c新特性总结(一)

甲骨文公司近日正式发布了新版旗舰级数据库Oracle Database 12c,在TechTarget数据库网站之前的一些报道中,我们曾对12c的一些新特性进行了介绍(参考:尝鲜Oracle ...
  • lively1982
  • lively1982
  • 2013年12月10日 13:43
  • 1152

oracle数据库等待事件

查看等待事件 select inst_id,event,count(*) from gv$session where wait_class 1.1 等待事件主要可以分为两类,即空闲(IDLE)等...
  • caoyhao
  • caoyhao
  • 2016年08月11日 08:58
  • 1523
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:oracle数据库开发案例
举报原因:
原因补充:

(最多只允许输入30个字)