关闭

oracle数据库开发案例

1301人阅读 评论(0) 收藏 举报

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;

 

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:139564次
    • 积分:1943
    • 等级:
    • 排名:千里之外
    • 原创:59篇
    • 转载:62篇
    • 译文:0篇
    • 评论:6条
    文章分类
    最新评论