Oracle培训—案例整理

1. 概述

Oracle培训共计8堂课，计划有6个案例，但是我整理过程中发现好像是4个大的案例：
1）停车收费模型-批量导入数据
2）导入千万数据（导数据、索引）
3）多人聊天（权限、视图、函数）
4）超市购物（存储过程）

2 .停车收费模型（拼接导入、union、group by）

2.1批量导入2001条数据

问题：如果BOSS给你一个TXT数据文件（2001条），如何考虑用最短的时间导入在用数据库，使之参与业务应用；
格式见下图：


原始数据.png

操作步骤：
1.创建数据库表

--创建（也叫定义）表
CREATE TABLE car_info(
carid VARCHAR2(20) NOT NULL,
intime DATE NOT NULL,
outtime DATE);

2.把.txt文件导入到excel中，在excel中拼接insert语句：

拼接语句.png

最后把excel中的语句复制到txt文件里面，形成car.sql文件存放在d:\car.sql。
3.导入car.sql到数据库的car_info表
sql> @d:\car

统计.png

2.2 业务问题

  1. 查找7：00-7：30之间进入车库多少车辆。
select count(*) from car_info
where
intime between
to_date('20120910 07:00:00','yyyymmdd hh24:mi:ss')
and
to_date('20120910 07:30:00','yyyymmdd hh24:mi:ss');

或者

select count(*)  from car_info 
where to_char(intime,'yyyymmdd hh24:mi:ss' ) 
between '20120910 07:00:00' 
and '20120910 07:30:00' ;
  1. 在7：30—8：00之间车库发生了事故，查找出所有的可疑车辆。
SELECT * FROM CAR_INFO
WHERE 
INTIME <=TO_DATE('2012-09-10 08:00:00','YYYY-MM-DD HH24:MI:SS') AND
OUTTIME>=TO_DATE('2012-09-10 07:30:00','YYYY-MM-DD HH24:MI:SS')
UNION
SELECT * FROM CAR_INFO
WHERE 
INTIME   <=TO_DATE('2012-09-10 08:00:00','YYYY-MM-DD HH24:MI:SS') AND
OUTTIME IS NULL;

或者

SELECT * FROM CAR_INFO
WHERE 
INTIME   <=TO_DATE('2012-09-10 08:00:00','YYYY-MM-DD HH24:MI:SS') AND
(OUTTIME>=TO_DATE('2012-09-10 07:30:00','YYYY-MM-DD HH24:MI:SS') OR
OUTTIME IS NULL);
  1. 求哪辆车停车时间最短，停了多久？
select * from (select carid ,(outtime-intime)*24  from car_info order by 2) where rownum<2 ;

4.按停车时间段建收费表，需求是：
0-5 10 //停车时间在0-5小时之间的 收费10元
5-10 9 //停车时间在5-10小时之间的 收费9元
10-20 7 //停车时间在10-20小时之间的 收费7元
20以上 5 //停车时间在20小时以上的 收费5元

注：如停车在时间段节点上按低费率计算，比如停车5小时收 9元，10小时收7元。
建立收费表：

--DROP TABLE FEE;
CREATE TABLE FEE(
MINTIME INT NOT NULL,
MAXTIME INT NOT NULL,
FEE INT NOT NULL);
INSERT INTO FEE VALUES(0,5,10);
INSERT INTO FEE VALUES(5,10,9);
INSERT INTO FEE VALUES(10,20,7);
INSERT INTO FEE VALUES(20,999999,5);
COMMIT;

5.按照FEE，求0-5，6-10，11-20，20以上各费率档次停了多少车，多少费用？

select mintime,maxtime,count(*),sum(ceil((OUTTIME-INTIME)*24)*fee)
from CAR_info,fee
where
ceil((OUTTIME-INTIME)*24) >=mintime and 
ceil((OUTTIME-INTIME)*24)<maxtime
group by mintime,maxtime;
效果.png

3.导入千万数据（导数据、索引）

3.1 导入1千万条数据，数据存于 abc.txt

  • 用户system 创建表空间test_ts
CREATE TABLESPACE test_idx 
DATAFILE ' C:\oracle\oradata\ora9\test01.dbf ' size 1000M
DEFAULT STORAGE(
INITIAL 10k
NEXT 10k
PCTINCREASE 0);
  • 用户system 创建用户u01
CREATE USER u01 identified by a;
  • 用户system 无法创建会话，创建！
GRANT create session TO  u01;
  • 用户system 创建表权限
GRANT create table to u01;
  • 用户system 默认表空间test_ts;
ALTER USER u01 DEFAULT TABLESPACE test_ts;
  • 用户system 为u01创建表空间配额
ALTER USER u01 QUOTA UNLIMITED ON test_ts;
  • 用户u01创建表stu_info1
CREATE TABLE stu_info(
stu_info_id CHAR(10) NOT NULL,
stu_info_name VARCHAR2(30) NOT NULL,
stu_info_sex CHAR(1) NOT NULL,
stu_info_brithday date NOT NULL,
stu_info_card VARCHAR2(30),
stu_info_phone VARCHAR2(20),
stu_info_falldate date NOT NULL,
stu_info_class_id CHAR(10) NOT NULL);
  • 创建外部控制文件
LOAD DATA
INFILE "c:\abc.txt"
BADFILE "db.bad"
APPEND
INTO TABLE stu_info
( 
 stu_info_id position(1:10),
 stu_info_name position(12:23),
 stu_info_sex position(25:25),
 stu_info_brithday position(27:36) date "yyyy-mm-dd",
 stu_info_card position(38:55),
 stu_info_phone position(57:67),
 stu_info_falldate position(69:78) date "yyyy-mm-dd",
 stu_info_class_id position(80:89)
)
  • SQL*LOAD引导
sqlldr userid=u01/a control=db.ctl errors=1000 streamsize=104857600  log=abc.log

执行完后，检查是否导入成功：

效果.png

以下是abc.bad文件的输出数据：

(一).png
(二).png

3.2 给字段 stu_info_id 创建索引

create index stu_info1_id_idx on stu_info1(stu_info_id)  tablespace  test_idx;

如果表空间不足，可以增加表空间的大小。

alter  tablespace  test_idx  add  datafile 'C:\oracle\oradata\ora9\test02.dbf' size 1000M

4.多人聊天（权限、视图、函数）

4.1 多人使用自己的用户使用同一台主机

  1. 客户端打开 C:\oracle\ora92\network\admin 下面的 tnsnames.ora ，添加 share186
添加服务器.png

连接刚才的共享主机，在dos窗口下输入
C:>sqlplus scott/tiger@share186
这是使用scott用户登录，需求是使用自己的账号登陆，管理员需要为每个人建立自己的账户并且赋权限。
--创建角色
CREATE ROLE stu_role;
--角色挂用户
GRANT stu_role TO stu00;
GRANT stu_role TO stu01;
GRANT stu_role TO stu02;
GRANT stu_role TO stu03;
GRANT stu_role TO stu04;
GRANT stu_role TO stu05;
--授权
GRANT create session TO stu_role;
GRANT create any table TO stu_role;
--创建表空间
create tablespace stu_ts datafile 'c:\stu_ts.dbf' size 50m;
--默认表空间
ALTER USER stu00 default tablespace stu_ts;
ALTER USER stu01 default tablespace stu_ts;
ALTER USER stu02 default tablespace stu_ts;
ALTER USER stu03 default tablespace stu_ts;
ALTER USER stu04 default tablespace stu_ts;
ALTER USER stu05 default tablespace stu_ts;
--表空间配额给用户
ALTER USER stu00 QUOTA UNLIMITED ON stu_ts;
ALTER USER stu01 QUOTA UNLIMITED ON stu_ts;
ALTER USER stu02 QUOTA UNLIMITED ON stu_ts;
ALTER USER stu03 QUOTA UNLIMITED ON stu_ts;
ALTER USER stu04 QUOTA UNLIMITED ON stu_ts;
ALTER USER stu05 QUOTA UNLIMITED ON stu_ts;

4.2 stu00用户创建聊天的数据模型

--学生实体

CREATE TABLE chat_user(
id VARCHAR2(10) NOT NULL,
name VARCHAR2(20) NOT NULL,
sex INT NOT NULL); //1代表男生，0代表女生

-- 赋权限

GRANT SELECT,INSERT ,UPDATE ON chat_user TO STU_ROLE;

--聊天消息

CREATE TABLE message(
message VARCHAR2(100) NOT NULL, //内容
mdate DATE DEFAULT SYSDATE,  //时间取当前时间
aid VARCHAR2(10) NOT NULL,  //发消息者
bid VARCHAR2(10));   //收消息者

-- 赋权限

GRANT SELECT,INSERT ,UPDATE ON message TO STU_ROLE;

每个人使用自己的用户在 stu00用户的chat_user表里面插入自己的用户记录：
比如：老师执行的sql是： INSERT INTO stu00.chat_user VALUES('STU00','老师',1);
我学号是STU05，执行的sql是：INSERT INTO stu00.chat_user VALUES('STU05','gaoyx',0);
这样大家就可以通过插入 message语句来聊天了。

4.3 业务问题

1.查找最近5分钟我发给别人以及别人发给我的或者群发的消息,群发的bid是STU99。

select aid,bid,message,mdate from ms where aid='STU05' or  bid= in ('STU05','STU99')
and  mdate > = sysdate-5/(24*60) order by mdate desc;

2.根据2张表 chat_user、message，求出第一问，查询结果不可以用id来表示，要用聊天人的名字来表示。（视图）

CREATE
VIEW v_ms AS
SELECT to_char(mdate,'hh24:mi:ss') md,c1.name aname,c2.name bname,message 
from message ms,chat_user c1,chat_user c2
WHERE 
ms.aid=c1.id and ms.bid=c2.id and
(aid='STU00' OR bid in ('STU05','STU99'))
and MDATE >= sysdate-5/(24*60)
ORDER BY mdate DESC;

函数实现：

--自定义函数 y=f(x)
CREATE or REPLACE FUNCTION fn_abc(v_x in varchar2)
RETURN VARCHAR2
AS
v_name VARCHAR2(20);
BEGIN
     select name into v_name from chat_user where id=v_x;
     return v_name;
END;

SELECT to_char(mdate,'hh24:mi:ss'),fn_abc(aid),fn_abc(bid),message 
from message
WHERE 
(aid='STU00' OR bid in ('STU05','STU99'))
and MDATE >=sysdate-5/(24*60)
ORDER BY mdate DESC;

5.超市购物（存储过程）

5.1 多人连一台服务器，操作见4.1

5.2 stu00创建超时购物模型

--账户表

DROP TABLE BANK;
create table bank(
vipno char(5) not null primary key,
balance number(10,2) default 0)
TABLESPACE stu_ts;

--产品表

drop table product;
create table product(
pid char(10) not null primary key,
pname varchar(50) not null,
price number(10) not null,
amount number(10) default 0)
TABLESPACE stu_ts;

--对2张表进行同名定义

create public synonym bank for stu00.bank; 
create public synonym product for stu00.product;

--每个人（我是STU05）把自己的信息插入到 bank 表

insert into bank values("STU05","1000");

5.3 业务问题

1.stu00写存储过程插入product表数据

--创建新增商品存储过程
create or replace procedure ins_product(v_pid in char,v_name in varchar2,v_price in number,v_amount in number)
as 
begin
    insert into product values(v_pid,v_name,v_price,v_amount);
        commit;

end;
exec ins_product('STU00000001','笔 ',5,10);
create public synonym ins_product for stu00.ins_product;
grant execute any procedure to stu_role;

stu00用户创建了上面的存储过程并且给 stu_role分配权限，其他用户是不能使用这个存储过程的，会提示权限不足，必须给每个用户分配权限：grant execute any procedure to stu_05;

2.创建序列号，因为订单的流水号会用到

--创建序列号
create sequence sale_seq
INCREMENT BY 1 -- 每次加几个
START WITH 1 -- 从1开始计数
NOMAXVALUE -- 不设置最大值
NOCYCLE -- 一直累加，不循环
CACHE 10; 
create public synonym sale_seq for stu00.sale_seq

3.创建销售表并添加外键

drop table sale;
create table sale(
seqno varchar2(20) not null primary key,
vipno char(5) not null,
pid char(10) not null,
amount number(10) not null,
sdate date default sysdate
)TABLESPACE stu_ts;

create public synonym sale for stu00.sale;

alter table sale add constraint fk_vipno foreign key(vipno) references bank(vipno);
alter table sale add constraint fk_pid foreign key(pid) references product(pid);
  1. 写存储过程实现购物流程
    --主程序 pro_main
create or replace procedure pro_main(v_vipno in char,v_PId in char,v_Amount in number)
as
v_money number(10);
BEGIN
    v_money:=FN_price(v_PId)*v_Amount;
    PRO_BANK(v_vipno,v_money);
    up_product(v_PId,v_Amount);
    ins_sale(v_vipno,v_PId,v_Amount);
    commit;
    dbms_output.put_line('OK,购物成功！');
END;

create public synonym pro_main for stu00.pro_main;
create public synonym pro_bank for stu00.pro_bank;
create public synonym up_product for stu00.up_product;
create public synonym ins_sale for stu00.ins_sale;

注释：其实commit不应该在 分存储过程处理完提交的，而应该在每个分存储过程后面都要添加判断，每个子存储过程失败是要给一个返回码，主程序中判断如果等于失败的反馈码则 rollback,否则 commit

--查价格的函数 FN_price

create or replace function  FN_price(v_x  in char)
 return number
 as
 v_price number(10);
 begin
 select price  into v_price from  stu00.product  where pid=v_x;
 return v_price;
 end;

--转帐存储过程 PRO_BANK

CREATE OR REPLACE PROCEDURE PRO_BANK(
v_vipno in CHAR,
v_money in number)
as 
v_j NUMBER(10);
BEGIN
    select nvl(balance,0) into v_j from bank where vipno=v_vipno;
    if v_j>=v_money then
        update bank set balance=balance-v_money 
            where vipno=v_vipno;
        update bank set balance=balance+v_money 
            where vipno='STU20';
    else
        dbms_output.put_line('余额不足！');
        rollback;
    end if;
END;
/

--仓库存储过程 up_product

CREATE OR REPLACE PROCEDURE up_product(
v_pid in CHAR,v_amount in NUMBER)
AS
BEGIN
    update product set amount=amount-v_amount where pid=v_pid;
END;
/

--将购买记录写入sale表 ins_sale

create or replace procedure ins_sale(
v_vipno in char,
v_pid in char,
v_amount in number)
as
begin
    insert into sale values(
to_char(sysdate,'yyyymmddhh24miss')||Lpad(sale_seq.nextval,6,'000000'),
v_vipno,v_pid,v_amount,sysdate);
end;
/

--调用主存储过程完成订购：

exec pro_main('STU00','STU0600009',1);

5.测试并发

背景：11个人每个人准备20条调用购物存储过程的语句，大家同时买一个商品，即同一时间处理一个商品的220个订单。

执行结束后查看执行时间：select max(sdate)-min(sdate) from sale;
发现用了2S多，把主存储里面的打印 成功的信息注释掉再执行用时1S多，说明打印会耗时。

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 216,240评论 6赞 498
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 92,328评论 3赞 392
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 162,182评论 0赞 353
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 58,121评论 1赞 292
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 67,135评论 6赞 388
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 51,093评论 1赞 295
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 40,013评论 3赞 417
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 38,854评论 0赞 273
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 45,295评论 1赞 310
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,513评论 2赞 332
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,678评论 1赞 348
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 35,398评论 5赞 343
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 40,989评论 3赞 325
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,636评论 0赞 22
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,801评论 1赞 268
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 47,657评论 2赞 368
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 44,558评论 2赞 352

推荐阅读更多精彩内容

  • Oracle.1Z0-052-0
    一、源题QUESTION 1The instance abnormally terminates because ...
    猫猫_tomluo阅读 1,597评论 0赞 2
  • oracle 11g rac install(linux_x64+udev+asm)
    1、NTP时间服务器配置，2节点，使用root用户 cd /etc/sysconfig;sed -i '/OPTI...
    limengyun阅读 1,207评论 0赞 0
  • Oracle常用操作指南
    目录 用户操作语句 表空间操作语句 数据文件操作语句 数据表操作语句 数据库属性操作语句 1. 用户操作语句 查看...
    garyond阅读 1,083评论 0赞 2
  • 江南好
    我们分手吧！看着对话框里跳出来的这五个字，我有些怔怔，看着窗外昏暗的天空，最终还是回复道，好。这是陈远第三次提出分...
    莲素阅读 301评论 4赞 14
  • 坚持（16）
    大家好，我是日记星球272号，我正在参加第七期蜕变之旅，今天是我的第16篇日记。 不知不觉间，我坚...
    艺坊园阅读 214评论 0赞 4