Monthly Archives: 6 月 2015

使用SQL实现对邮政编码分类规则

Posted on by

早晨在大阪工作的学长发信息过来要想用SQL实现对日本邮政编码分类的规则.本来学长只需要做概要设计即可,详细设计和开发都是外包给越南人做,但是担心越南的童鞋方法不能够满意,索性就直接把关键的步骤都自己实现掉,想想也是为了世界和平做了不少贡献;

规则是这样的:
1.日本邮编是7位的;
2.最终的结果集只需要两列(前缀 个数);
3.第一优先级是尾数为[00]的邮政编码;
4.第二优先级是前5位数字相同的邮政编码,只展现出现次数大于10次的结果,如果不足10次的邮政编码进入下一优先级统计;
5.第三优先级是前4位数字相同的邮政编码,规则同上;
6.第四优先级是前3位数字相同的邮政编码,规则同上;
7.第五优先级是剩余其它的邮政编码;

— 测试的数据,生成20W个邮政编码;
DROP TABLE TBPOSTTEST;
CREATE TABLE TBPOSTTEST AS
SELECT LPAD(ROUND(DBMS_RANDOM.VALUE (1, 9999999)) , 7, 0) postcode FROM DUAL
CONNECT BY LEVEL <= 200000;

— 实现的sql语句;
SELECT PREFIX, COUNT FROM (
WITH
P0P0 AS (SELECT ‘____00′ AS “PREFIX”, COUNT(*) c0 FROM TBPOSTTEST WHERE SUBSTR(postcode, LENGTH(postcode)-1) = ’00’),
P1P5 AS (SELECT SUBSTR(postcode, 0, 5) cp5, COUNT(*) c5 FROM TBPOSTTEST WHERE SUBSTR(postcode, LENGTH(postcode)-1) <> ’00’ GROUP BY SUBSTR(postcode, 0, 5)),
P2P4 AS (SELECT SUBSTR(cp5, 0, 4) cp4, SUM(c5) c4 FROM P1P5 WHERE c5 < 10 GROUP BY SUBSTR(cp5, 0, 4)),
P3P3 AS (SELECT SUBSTR(cp4, 0, 3) cp3, SUM(c4) c3 FROM P2P4 WHERE c4 < 10 GROUP BY SUBSTR(cp4, 0, 3))
SELECT ‘0’ AS “PRIORITY”, PREFIX, c0 AS “COUNT” FROM P0P0
UNION ALL
SELECT ‘1’, cp5 , c5 FROM P1P5 WHERE c5 >= 10
UNION ALL
SELECT ‘2’, cp4 , c4 FROM P2P4 WHERE c4 >= 10
UNION ALL
SELECT ‘3’, cp3 , c3 FROM P3P3 WHERE c3 >= 10
UNION ALL
SELECT ‘4’, ‘OTHER’ , SUM(c3) c2 FROM P3P3 WHERE c3 < 10)
ORDER BY PRIORITY;

— 测试的DEMO, 把条件改为大于2条;
INSERT INTO TBPOSTTEST VALUES(‘123400’);
INSERT INTO TBPOSTTEST VALUES(‘123500’);
INSERT INTO TBPOSTTEST VALUES(‘123450’);
INSERT INTO TBPOSTTEST VALUES(‘123451’);
INSERT INTO TBPOSTTEST VALUES(‘123452’);
INSERT INTO TBPOSTTEST VALUES(‘123453’);
INSERT INTO TBPOSTTEST VALUES(‘123454’);
INSERT INTO TBPOSTTEST VALUES(‘123444’);
INSERT INTO TBPOSTTEST VALUES(‘123555’);
INSERT INTO TBPOSTTEST VALUES(‘123566’);
INSERT INTO TBPOSTTEST VALUES(‘124444’);
COMMIT;

SELECT PREFIX, COUNT FROM (
WITH
P0P0 AS (SELECT ‘____00′ AS “PREFIX”, COUNT(*) c0 FROM TBPOSTTEST WHERE SUBSTR(postcode, LENGTH(postcode)-1) = ’00’),
P1P5 AS (SELECT SUBSTR(postcode, 0, 5) cp5, COUNT(*) c5 FROM TBPOSTTEST WHERE SUBSTR(postcode, LENGTH(postcode)-1) <> ’00’ GROUP BY SUBSTR(postcode, 0, 5)),
P2P4 AS (SELECT SUBSTR(cp5, 0, 4) cp4, SUM(c5) c4 FROM P1P5 WHERE c5 < 2 GROUP BY SUBSTR(cp5, 0, 4)),
P3P3 AS (SELECT SUBSTR(cp4, 0, 3) cp3, SUM(c4) c3 FROM P2P4 WHERE c4 < 2 GROUP BY SUBSTR(cp4, 0, 3))
SELECT ‘0’ AS “PRIORITY”, PREFIX, c0 AS “COUNT” FROM P0P0
UNION ALL
SELECT ‘1’, cp5 , c5 FROM P1P5 WHERE c5 >= 2
UNION ALL
SELECT ‘2’, cp4 , c4 FROM P2P4 WHERE c4 >= 2
UNION ALL
SELECT ‘3’, cp3 , c3 FROM P3P3 WHERE c3 >= 2
UNION ALL
SELECT ‘4’, ‘OTHER’ , SUM(c3) c2 FROM P3P3 WHERE c3 < 2)
ORDER BY PRIORITY;

— 测试结果;
PREFIX        COUNT
—————— ———-
____00               2
12345                 5
1235                   2
OTHER                2

创建带参数的视图

Posted on by

PROMPT CREATE OR REPLACE PACKAGE pkg_session

CREATE OR REPLACE PACKAGE pkg_session IS
TYPE parm_type IS TABLE OF varchar2(1000) index BY VARCHAR2(30);
PROCEDURE set_val(p_idx IN VARCHAR2,p_value IN VARCHAR2);
PROCEDURE set_val(p_idx IN VARCHAR2,p_value IN NUMBER);
PROCEDURE set_val(p_idx IN VARCHAR2,p_value IN DATE);
FUNCTION get_val(p_idx IN VARCHAR2) RETURN VARCHAR2 ;
FUNCTION get_val_number(p_idx IN VARCHAR2) RETURN NUMBER;
FUNCTION get_val_date(p_idx IN VARCHAR2) RETURN DATE;
PRAGMA RESTRICT_REFERENCES(get_val,WNDS,WNPS);
PRAGMA RESTRICT_REFERENCES(get_val_number,WNDS,WNPS);
PRAGMA RESTRICT_REFERENCES(get_val_date,WNDS,WNPS);
END pkg_session;
/

CREATE OR REPLACE PACKAGE BODY pkg_session IS
sv_parameters parm_type;
PROCEDURE set_val(p_idx IN VARCHAR2,p_value IN VARCHAR2)
IS
BEGIN
sv_parameters(TRIM(UPPER(p_idx))) := SUBSTR(p_value,1,1000);
RETURN;
END set_val;

PROCEDURE set_val(p_idx IN VARCHAR2,p_value IN NUMBER)
IS
BEGIN
set_val(p_idx,TO_CHAR(p_value));
RETURN;
END set_val;

PROCEDURE set_val(p_idx IN VARCHAR2,p_value IN DATE)
IS
BEGIN
set_val(p_idx,TO_CHAR(p_value,’YYYYMMDDHH24MISS’));
RETURN;
END set_val;

FUNCTION get_val(p_idx IN VARCHAR2) RETURN VARCHAR2
IS
BEGIN
RETURN sv_parameters(TRIM(UPPER(p_idx)));
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
END get_val;

FUNCTION get_val_number(p_idx IN VARCHAR2) RETURN NUMBER
IS
BEGIN
RETURN TO_NUMBER(get_val(p_idx));
END get_val_number;

FUNCTION get_val_date(p_idx IN VARCHAR2) RETURN DATE
IS
BEGIN
RETURN TO_DATE(get_val(p_idx),’YYYYMMDDHH24MISS’);
END get_val_date;

END pkg_session;
/

CREATE OR REPLACE VIEW VW_TEST
AS SELECT * FROM t WHERE ROWNUM<=pkg_session.get_val_number('P_ROWNUM');

— 未设参数时没有数据
SELECT * FROM VW_TEST;

— 设置参数：

EXEC pkg_session.set_val('P_ROWNUM',10);

— 以下会返回10行：
SELECT * FROM VW_TEST;

–两个条件 并且当不设置值的时候不启用
CREATE OR REPLACE VIEW VW_TEST2
AS SELECT * FROM t WHERE ROWNUM exec pkg_session.set_val(‘P_NAME’,’UCJMH’);

PL/SQL procedure successfully completed.

SQL> select * from vw_test2;

USERNAME
——————————
UCJMH
这个时候就只会有一行数据了

在给定时间间隔，求间隔内出现的记录数

Posted on by

在itpub里看到一个很有意思的sql

一个表中记录了各个地点的报警开始时间，结束时间。

现在想统计，各个地点报警次数和时长，时长=结束时间-开始时间。

要求，同一个地点，开始时间间隔超过10分钟的，这个地点要在结果中再出现一次，10分钟之内的假如有多条，统计条数和报警时长，时长=10分钟内各条时长之和。

create table T_DEMO
(
id VARCHAR2(32) not null,
place_id VARCHAR2(32),
s_time DATE,
e_time DATE
)；

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (‘1’, ‘1’, to_date(’21-08-2014 01:24:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’21-08-2014 01:24:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (‘2’, ‘1’, to_date(’21-08-2014 01:25:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’21-08-2014 01:25:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (‘3’, ‘1’, to_date(’21-08-2014 01:35:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’21-08-2014 01:35:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (‘4’, ‘1’, to_date(’21-08-2014 01:39:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’21-08-2014 01:39:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (‘5’, ‘1’, to_date(’21-08-2014 02:05:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’21-08-2014 02:05:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (‘6’, ‘1’, to_date(’21-08-2014 02:06:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’21-08-2014 02:06:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (‘7’, ‘1’, to_date(’21-08-2014 02:12:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’21-08-2014 02:12:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (‘8’, ‘1’, to_date(’21-08-2014 02:14:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’21-08-2014 02:14:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (‘9’, ‘1’, to_date(’21-08-2014 02:50:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’21-08-2014 02:50:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (’10’, ‘1’, to_date(’21-08-2014 02:52:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’21-08-2014 02:52:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (’11’, ‘1’, to_date(’21-08-2014 02:57:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’21-08-2014 02:57:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (’12’, ‘1’, to_date(’25-08-2014 09:13:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’25-08-2014 09:13:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (’13’, ‘1’, to_date(’25-08-2014 09:18:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’25-08-2014 09:18:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (’14’, ‘1’, to_date(’25-08-2014 09:30:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’25-08-2014 09:30:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (’15’, ‘1’, to_date(’25-08-2014 09:37:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’25-08-2014 09:37:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (’16’, ‘1’, to_date(’25-08-2014 09:47:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’25-08-2014 09:47:01′, ‘dd-mm-yyyy hh24:mi:ss’));

insert into t_demo (ID, PLACE_ID, S_TIME, E_TIME)

values (’17’, ‘2’, to_date(’25-08-2014 10:09:00′, ‘dd-mm-yyyy hh24:mi:ss’), to_date(’25-08-2014 10:09:01′, ‘dd-mm-yyyy hh24:mi:ss’));

WITHt2AS

(SELECT  a.place_id,

a.s_time,

a.e_time,

a.id,

floor((a.s_time – to_date(‘2000-01-01′,’YYYY-MM-DD’)) * 24 * 60 / 10) last_cha

FROM t_demo a)

SELECT

place_id 地点,

SUM(e_time – s_time) * 24 * 60 * 60 “时长:秒”,

min(s_time)  开始时间，

COUNT(*) 次数

FROM t2

GROUP BY place_id, last_cha;

导入导出工具04–Oralce数据库中表数据导出为TXT格式

Posted on by

Oracle数据库中表数据导出为TXT格式

  1. 使用SPOOL命令把结果输出到文本中;
  2. 使用编写的C语言的脚本;
——————————- 使用SPOOL命令把结果输出到文本中 ——————————-
— 1.编写脚本为/tmp/orauldr.sql;
set echo on            — 是否显示执行的命令内容
set feedback off       — 是否显示提示符,即多少条记录打一个点
set heading off        — 是否显示字段的名称
set verify off         — 是否显示替代变量被替代前后的语句
set trimspool off      — 去字段空格
set pagesize 1000      — 页面大小
set linesize 50        — linesize设定尽量根据需要来设定，大了生成的文件也大
define fil= ‘/tmp/exp.txt’
prompt *** Spooling to &fil
spool &fil
select col1 || ‘,’ || username || ‘,’ || ‘,’|| password from tbtest; — 格式自己拼接出来,比较灵活,导入也不容易出错
spool off;
— 2.在SQL*PLUS中执行;
SQL>@ /tmp/orauldr.sql;
——————————- 使用SPOOL命令把结果输出到文本中 ——————————-
——————————- 使用编写的C语言的脚本 ——————————-
第三方使用C语言写的工具蛮多的，可以联系sonne.k.wang@gmailcom交流;
——————————- 使用编写的C语言的脚本 ——————————-

导入导出工具03–可传输表空间

Posted on by
可传输表空间
  1. 用途:
    1. 用于不同平台的数据迁移;
    2. 用于不同版本数据库之间的迁移;
  2. 限制:
    1. 不支持XMLSchema类型的数据;
    2. 只是把表空间上的数据传输到目标,用户的对象(序列,视图,包,过程,触发器)需要手动创建
  3. 实验目标:创建测试表空间TBS_TT1,并传输到目标服务器;
  4. 检查是否满足表空间传输的条件:
    1. 查看可传输表空间支持数据库平台和当前数据库的版本;                                              
    2. 查看要传输的表空间集是否是自包含的:
      1. 如果某个表空间集引用了其他表空间的对象,则这个表空间不是自包含的,否则就是自包含;
      2. 只有自包含的表空间集才可以用传输表空间技术;
      3. 使用dbms_tts.transport_set_check来验证表空间是否是自包含的,要把需要表空间传输的表空间列表都写上,中间用逗号分割;
      4. TRANSPORT_SET_VIOLATIONS表是一个临时表,必须在执行dbms_tts.transport_set_check的session中查看,如果有记录返回,则表示表空间集不是自包含的;
      5. 检查的结果;                                                  
    3. 查看数据库的字符集;                                                                                                             
  5. 在源数据库导出表空间;
    1. 创建目录对象:CREATE DIRECTORY dir_tt AS ‘/u01/tt’;                     
    2. 把对应的表空间设置为只读模式:ALTER TABLESPACE tbs_tt1 READ ONLY;                                
    3. 使用数据泵导出,只是导出表空间的元信息:expdp \’sys/oracle as sysdba\’ directory=dir_tt transport_tablespaces=tbs_tt1 dumpfile=tt_tbs_tt1.dmp logfile=tt.log job_name=’job_tt’;                                                      
  6. 转换数据文件格式:
    1. 如果源数据库和目标数据库所在的平台不同需要进行此步骤,把表空间的数据转化为目标平台格式;
    2. rman提供了此功能,可以在源数据库进行也可以在目标数据库进行:
      1. 在源数据库转换:convert tablespace ts1,ts2… to platform <destination platform> format ‘/path/%U’;
      2. 在目标数据库转换:convert datafile df1,df2… from platform <source platform> format ‘/path/%U’;
  7. 把导出的表空间的元信息和表空间的数据文件传到目标数据库服务器,数据文件放到数据库的目录下,元数据放到目录对象下;
  8. 修改源数据库表空间为读写模式:ALTER TABLESPACE tbs_tt1 READ WRITE;            
  9. 在目标服务器导入表空间:
    1. 创建目录对象:CREATE DIRECTORY dir_tt AS ‘/u01/tt’;                      
    2. 在目标数据库上创建表空间上的用户和并授予权限,否则会报错;                         
    3. 如果之前进行了平台的转换操作的话,此时可能需要使用rman对数据文件进行重命名了;
    4. 如果源数据库和目标数据库的块大小不同的话,需要在目标数据库添加源数据库块的cache_size:ALTER SYSTEM SET db_4k_cache_size = 5M;
    5. 把数据导入到目标数据库中:impdp \’sys/oracle as sysdba\’ directory=dir_tt dumpfile=tt_tbs_tt1.dmp transport_datafiles=’/u01/app/oracle/oradata/ORCL/tbs_tt1.dbf’ logfile= tt.log job_name=’job_tt’;                                  
    6. 修改表空间为读写模式:ALTER TABLESPACE tbs_tt1 READ WRITE;(SELECT tablespace_name, status FROM dba_tablespaces WHERE tablespace_name = ‘TBS_TT1’;)           
    7. 修改用户的默认表空间:ALTER USER u1 DEFAULT TABLESPACE tbs_tt1;(SELECT username, default_tablespace FROM dba_users WHERE username = ‘U1’;)          
  10. 验证数据;
— 测试数据;
— 创建表空间;
CREATE TABLESPACE TBS_TT1 DATAFILE ‘/u01/app/oracle/oradata/ORCL/tbs_tt1.dbf’ SIZE 20M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
— 创建用户并授权;
CREATE USER u1 IDENTIFIED BY u1 DEFAULT TABLESPACE tbs_tt1;
GRANT CONNECT, RESOURCE TO u1;
— 创建表,序列,并插入测试数据;
CREATE TABLE u1.t1(ID INTEGER, c_lob CLOB) TABLESPACE tbs_tt1;
CREATE SEQUENCE u1.seq_t1;
INSERT INTO u1.t1(ID, c_lob) VALUES (u1.seq_t1.NEXTVAL, to_clob(‘传输表空间-表1中BLOB字段’));
COMMIT;
— 创建视图;
CREATE VIEW v_t1(ID, NAME) AS SELECT ID, to_char(c_lob) FROM u1.t1;
SELECT * FROM v_t1;
— 创建过程;
CREATE OR REPLACE PROCEDURE pr_t1 AS
BEGIN
NULL;
END;
— 查看可传输表空间支持数据库平台和当前数据库的版本;
SELECT d.NAME, i.host_name, i.version, d.platform_name, endian_format
FROM v$transportable_platform tp, v$database d, v$instance i
WHERE tp.platform_name = d.platform_name AND d.db_unique_name = i.instance_name;
— 查看表空间是否是自包含的;
EXECUTE dbms_tts.transport_set_check(‘TBS_TT1’, TRUE, TRUE);
SELECT * FROM transport_set_violations;
— 查看数据库的字符集;
SELECT max(decode(parameter,’NLS_LANGUAGE’, VALUE, NULL)) || ‘_’ ||
max(decode(parameter,’NLS_TERRITORY’, VALUE, NULL)) || ‘.’ ||
max(decode(parameter,’NLS_CHARACTERSET’, VALUE, NULL)) AS “NLS_LANG”
FROM nls_database_parameters;
— 数据文件平台转换的脚本;
RMAN> run{
allocate channel c1 device type disk connect ‘sys/oracle@orcl_source’;
convert tablespace example,users to platform ‘Linux IA (32-bit)’ Format ‘/backup/dmp/%U’;
release channel c1;}