利用sum来实现行列转换-由oracle库文件建立完整数据库...-根据格式字符串自动生成单据号(ORCALE版)

当前位置: 数据库>oracle

本页文章导读:

▪利用sum来实现行列转换原始表如下：预期想要将表中的每一个IDX_CODE分别在查询结果中各显示一列，结果表如下：建表语句如下：create table test( IDX_DATA_ID NUMBER(12) not null, org_no NUMBER(2), org_name VARCHAR2(6), id.........

▪由oracle库文件建立完整数据库的过程最近帮忙老师写一部分网站管理系统的功能，由于不在同一个地方，老师发给了我一个库文件（后续名为.dmp），要我自己建个数据库在上面跑起来，下面就说说我做的步骤，以.........

▪根据格式字符串自动生成单据号(ORCALE版) 本代码为通过特定关键字的字符串自动生成单据号；解决编程中频繁编写代码自动生成单据号。@SJ ---为格式字符串(以下是关键字含义)"%Y"表示完整年如:2012"%y"表示简写年如:12"%M"表示两位月.........

[1]利用sum来实现行列转换

来源: 发布时间: 2013-10-16

原始表如下：

预期想要将表中的每一个IDX_CODE分别在查询结果中各显示一列，结果表如下：

建表语句如下：

create table test
(
IDX_DATA_ID NUMBER(12) not null,
org_no NUMBER(2),
org_name VARCHAR2(6),
idx_code VARCHAR2(12),
stat_cycle NUMBER(6),
data_value NUMBER(12),
CHAIN_VALUE NUMBER(6,2)
)

插入数据：

insert into TEST (IDX_DATA_ID, ORG_NO, ORG_NAME, IDX_CODE, STAT_CYCLE, DATA_VALUE, CHAIN_VALUE)
values (1300025889, 1, '北京', 'ZH001360', 201210, 100000, 0.00);

insert into TEST (IDX_DATA_ID, ORG_NO, ORG_NAME, IDX_CODE, STAT_CYCLE, DATA_VALUE, CHAIN_VALUE)
values (1300025890, 1, '北京', 'ZH001360', 201211, 110000, 1.10);

insert into TEST (IDX_DATA_ID, ORG_NO, ORG_NAME, IDX_CODE, STAT_CYCLE, DATA_VALUE, CHAIN_VALUE)
values (1300025891, 1, '北京', 'ZH001360', 201212, 90000, 0.82);

insert into TEST (IDX_DATA_ID, ORG_NO, ORG_NAME, IDX_CODE, STAT_CYCLE, DATA_VALUE, CHAIN_VALUE)
values (1300025892, 1, '北京', 'ZH001359', 201210, 200000, 0.00);

insert into TEST (IDX_DATA_ID, ORG_NO, ORG_NAME, IDX_CODE, STAT_CYCLE, DATA_VALUE, CHAIN_VALUE)
values (1300025893, 1, '北京', 'ZH001359', 201211, 210000, 1.05);

insert into TEST (IDX_DATA_ID, ORG_NO, ORG_NAME, IDX_CODE, STAT_CYCLE, DATA_VALUE, CHAIN_VALUE)
values (1300025894, 1, '北京', 'ZH001359', 201212, 190000, 0.90);

insert into TEST (IDX_DATA_ID, ORG_NO, ORG_NAME, IDX_CODE, STAT_CYCLE, DATA_VALUE, CHAIN_VALUE)
values (1300025895, 2, '上海', 'ZH001360', 201210, 100000, 0.00);

insert into TEST (IDX_DATA_ID, ORG_NO, ORG_NAME, IDX_CODE, STAT_CYCLE, DATA_VALUE, CHAIN_VALUE)
values (1300025896, 2, '上海', 'ZH001360', 201211, 110000, 1.10);

insert into TEST (IDX_DATA_ID, ORG_NO, ORG_NAME, IDX_CODE, STAT_CYCLE, DATA_VALUE, CHAIN_VALUE)
values (1300025897, 2, '上海', 'ZH001360', 201212, 90000, 0.82);

insert into TEST (IDX_DATA_ID, ORG_NO, ORG_NAME, IDX_CODE, STAT_CYCLE, DATA_VALUE, CHAIN_VALUE)

[2]由oracle库文件建立完整数据库的过程

来源: 发布时间: 2013-10-16

最近帮忙老师写一部分网站管理系统的功能，由于不在同一个地方，老师发给了我一个库文件（后续名为.dmp），要我自己建个数据库在上面跑起来，下面就说说我做的步骤，以免到时候需要用的时候又忘了。

1、首先在本地建立一个ORACLE数据库，全局数据库名和SID我都写了SCHOOL。

2、以管理员身份登录该数据库，我是以SYSTEM，密码是建数据库时设的密码登录的。

3、用PL/SQL登录该数据库后，就可以创建用户了，代码如下：

create user myxuser identified by myxuser;

同时也可以修改数据库密码：alter user zzg identified by unis;

4、默认情况下用户创建好后系统会默认给该用户分配一个表空间（users），我们可以通过下面的语句来查看一下所有用户所在的表空间：

　　select username,default_tablespace from dba_users;

一般情况下我们不会用用户的默认表空间，这时我们可以自己创建一个表空间：

create tablespace myxuser_data datafile 'e:\myxuser.dbf' size 200M;其中tablespace后面的为表空间的名字；datafile后的路径为表空间的物理存储路径；文件的后缀可以随便取；

size 200M是设定了表空间的大小。

5、创建好表空间,并将表空间分配给用户：alter user myxuser default tablespace myxuser_data；

6、给用户myxuser分配权限：grant create session,create table,create view,create sequence,unlimited tablespace to myxuser；

7、这样用户myxuser就可以登录了。同时把.dmp文件导入数据库：imp myxuser/myxuser@SCHOOL file=E:\school20130120.dmp fromuser=myxuser touser=myxuser

这里出现了点小问题，出现错误：IMP-00013，只有DBA才能导入由其他DBA导出的文件。所以我们要把DBA权限赋予给用户myxuser：

grant dba to myxuser;

至此发现数据库中知导入了一部分的表，查询原因，

原来因为我给用户myxuser分配的表空间大小为200M，但库文件内容比较多，所以只导入了一部分表，故应自动扩展表空间的大小：

ALTER DATABASE DATAFILE 'E:\MYXUSER.DBF' AUTOEXTEND ON NEXT 200M ;//每次自动增长200M

至此则导入成功。

本文链接

[3]根据格式字符串自动生成单据号(ORCALE版)

来源: 发布时间: 2013-10-16

本代码为通过特定关键字的字符串自动生成单据号；解决编程中频繁编写代码自动生成单据号。

@SJ ---为格式字符串(以下是关键字含义)

"%Y"表示完整年如:2012
"%y"表示简写年如:12
"%M"表示两位月份如:01
"%m"表示简写月份如:1
"%D"表示天如:08
"%d"表示简写天如:8
"%W" 或者 "%w"表示周(全年52周) 如:05(全年第五周)
"%H"表示完整小时如:08
"%h"表示简写小时如:8
"%N"表示完整分钟如:08
"%n"表示简写分钟如:8
"%S"表示完整秒如:08
"%s"表示简写秒如:8
"%K"表示完整毫秒如:008
"%04X"表示生成单据号的流水号
- 04表示4为数字如:流水号0005
- % X 表示通配符
其他非特定关键字可以任意组合

@TableName ---为单据号的数据库表
@ColName ----为数据表的单据号列
@Remove -----为剔除自动生成单据号的数组如:格式为"1,2,3,4,5,"
@Mantissa ----为控制是否剔除包含还是匹配;"1"表示包含剔除的数据；"0"表示剔除完全匹配的单据号
@Where ---为自动生成单据号的查询条件
@ZD_date ---为单据号生成的单据日期
@SSN ----为生产的单据号

例子:

格式字符串：投诉单%Y%M%06XAAAsdsA

自动生成的单据号：投诉单201206000001AAAsdsA

代码1：判断是否数值型字符串

代码2：自动生成序列号的函数

CREATE OR REPLACE FUNCTION SN_ISNUMBER(MyStr VARCHAR2) RETURN NUMBER
IS
STR VARCHAR2(400);
ISNUM NUMBER;
NUM NUMBER;
BEGIN
--返回0，不是数字。1，是数字
ISNUM:=0;
STR:=TRIM(MyStr);
--如果输入值为空，就返回不是数字。
IF TRIM(STR) IS NOT NULL THEN
BEGIN
NUM:=TO_NUMBER(STR);
ISNUM:=1;
EXCEPTION
WHEN INVALID_NUMBER THEN
NULL;
WHEN OTHERS THEN
NULL;
END;
END IF;
RETURN ISNUM;
END;

CREATE OR REPLACE FUNCTION SN_PROCESSAUTOSN(SJ VARCHAR2,
TABLENAME VARCHAR2,
COLNAME VARCHAR2,
REMOVE VARCHAR2,
MANTISSA INT,
WHERES VARCHAR2,
ZZD_DATE DATE) RETURN VARCHAR2 IS
--自动生成单据号
--SJ 流水号格式字符串
--TableName 表名
--ColName 列名
--存储过程参数
ZD_DATE DATE;
SN VARCHAR2(500);
YEARS VARCHAR2(4);
MINYEAR VARCHAR2(2);
MONTHS VARCHAR2(2);
DAYS VARCHAR2(2);
HOUR VARCHAR2(2);
SSN VARCHAR2(1000);
Q VARCHAR2(2);
MINUTE VARCHAR2(2);
WEEK VARCHAR2(2);
SECONDS VARCHAR2(2);
MSECONDS VARCHAR2(10);
SL VARCHAR2(500);
ZERO VARCHAR2(500);
XZERO VARCHAR2(500);
XCOUNT INT;
LSQL VARCHAR2(4000);
LMAX VARCHAR2(500);
LSTART INT;
LEND INT;
LLENGTH INT;
IMAX VARCHAR2(500);
LMAX INT;
MSTART INT;
STAT INT;
RES VARCHAR2(500);
RMOVE VARCHAR2(5000);
BEGIN

/* TABLENAME := 'OI_CONTRACT_HEADER';
COLNAME := 'OCH_CONTRACT_ID'; --投诉单1009001
SJ := '%y%M%046';
ZD_DATE:=to_date('2009-01-01','yyyy-MM-dd');*/

IF (ZZD_DATE IS NULL) THEN
ZD_DATE := SYSDATE;
ELSE
ZD_DATE := ZZD_DATE;

END IF;

SN := SJ;
YEARS := TO_CHAR(ZD_DATE, 'yyyy');
MINYEAR := SUBSTR(YEARS, 3, 2);
MONTHS := TO_CHAR(ZD_DATE, 'MM');
DAYS := TO_CHAR(ZD_DATE, 'dd');
WEEK := TO_CHAR(ZD_DATE, 'WW');
HOUR := TO_CHAR(ZD_DATE, 'HH24');
MINUTE := TO_CHAR(ZD_DATE, 'MI');
Q := TO_CHAR(ZD_DATE, 'Q');
SECONDS := TO_CHAR(ZD_DATE, 'SS');
SN := REPLACE(SN, '%Y', YEARS);
SN := REPLACE(SN, '%y', MINYEAR);
SN := REPLACE(SN, '%M',
SUBSTR('00' || TO_CHAR(MONTHS),
LENGTH('00' || TO_CHAR(MONTHS)) - 1, 2));
SN := REPLACE(SN, '%D',
SUBSTR('00' || TO_CHAR(DAYS),
LENGTH('00' || TO_CHAR(DAYS)) - 1, 2));
SN := REPLACE(SN, '%m', TO_NUMBER(MONTHS));
SN := REPLACE(SN, '%d', TO_NUMBER(DAYS));
SN := REPLACE(SN, '%w', TO_NUMBER(WEEK));
SN := REPLACE(SN, '%W',
SUBSTR('00' || TO_CHAR(WEEK),
LENGTH('00' || TO_CHAR(WEEK)) - 1, 2));
SN := REPLACE(SN, '%H',
SUBSTR('00' || TO_CHAR(HOUR),
LENGTH('00' || TO_CHAR(HOUR)) - 1, 2));
SN := REPLACE(SN, '%h', TO_NUMBER(HOUR));
SN := REPLACE(SN, '%N',
SUBSTR('00' || TO_CHAR(MINUTE),
LENGTH('00' || TO_CHAR(MINUTE)) - 1, 2));
SN := REPLACE(SN, '%n', TO_NUMBER(MINUTE));
SN := REPLACE(SN, '%S',
SUBSTR('00' || TO_CHAR(SECONDS),
LENGTH('00' || TO_CHAR(SECONDS)) - 1, 2));
SN := REPLACE(SN, '%s', TO_NUMBER(SECONDS));
SN := REPLACE(SN, '%K',
SUBSTR('000' || TO_CHAR(MSECONDS),
LENGTH('000' || TO_CHAR(MSECONDS)) - 2, 3));
SN := REPLACE(SN, '%k', TO_NUMBER(MSECONDS));
SN := REPLACE(SN, '%Q',
SUBSTR('00' || TO_CHAR(Q), LENGTH('00' || TO_CHAR(Q)) - 1, 2));
SN := REPLACE(SN, '%q', TO_NUMBER(Q));
ZERO := REGEXP_SUBSTR(SN, '%[0-9]{1,10}X');

XZERO := REPLACE(ZERO, '%', '');
XZERO := REPLACE(XZERO, 'X', '');
XZERO := REPLACE(XZERO, 'x', '');
IF (ZERO IS NULL) THEN
BEGIN
SSN := SN;
RETURN SSN;
END;
END IF;

SELECT COUNT(T.TABLE_NAME)
INTO XCOUNT
FROM USER_TABLES T
WHERE T.TABLE_NAME = TABLENAME;
IF XCOUNT < 1 THEN
BEGIN
SSN := SN;
RETURN SSN;
END;
ELSE
BEGIN
SELECT COUNT(T.TABLE_NAME)
INTO XCOUNT
FROM USER_TAB_COLUMNS T
WHERE T.TABLE_NAME = TABLENAME
AND T.COLUMN_NAME = COLNAME;
IF XCOUNT < 1 THEN
BEGIN
SSN := SN;
RETURN SSN;
END;
END IF;
END;
END IF;

DBMS_OUTPUT.PUT_LINE(SL);

LSTART := REGEXP_INSTR(SN, '%[0-9]{1,10}X');

LEND := LSTART + TO_NUMBER(XZERO);
LLENGTH := LENGTH(SN) - LENGTH(ZERO) + TO_NUMBER(XZERO);

LSQL := 'substr(' || COLNAME || ',' || TO_CHAR(LSTART) || ',' ||
TO_CHAR(XZERO) || ')';

LSQL := 'select max(case when sn_isnumber(' || LSQL || ')=1 then ' || LSQL ||
'else ''' || ZERO || ''' end ) from ' || TABLENAME ||
' where length(' || COLNAME || ')=' || TO_CHAR(LLENGTH);
LSQL := LSQL || ' and substr(' || COLNAME || ',1,' || TO_CHAR(LSTART - 1) ||
')=substr(''' || SN || ''',1,' || TO_CHAR(LSTART - 1) || ')';
IF (LLENGTH - LEND) > 0 THEN
LSQL := LSQL || ' and substr(' || COLNAME || ',' || TO_CHAR(LEND) || ',' ||
TO_CHAR(LLENGTH - LEND) || ')=substr(''' || SN || ''',' ||
TO_CHAR(LSTART + LENGTH(ZERO)) || ',' ||
TO_CHAR(LLENGTH - LEND) || ')';
END IF;

IF WHERES IS NOT NULL THEN
BEGIN
LSQL := LSQL || ' and ' || WHERES;
END;
END IF;

EXECUTE IMMEDIATE LSQL
INTO IMAX;

IF IMAX IS NULL OR IMAX = ZERO THEN
IMAX := '0';
END IF;

MSTART := TO_NUMBER(IMAX) + 1;

IF (REMOVE IS NOT NULL) THEN
BEGIN

RMOVE := REMOVE;
IF (MANTISSA IS NOT NULL AND MANTISSA = 0) THEN
BEGIN
<<A>>
STAT := 0;
RMOVE := REMOVE;
WHILE (INSTR(RMOVE, ',') > 0) LOOP
RES := SUBSTR(RMOVE, 1, INSTR(RMOVE, ',') - 1);
DBMS_OUTPUT.PUT_LINE(RES);
IF INSTR(TO_CHAR(MSTART), TO_CHAR(RES)) > 0 THEN
STAT := 1;
END IF;
RMOVE := SUBSTR(RMOVE, INSTR(RMOVE, ',') + 1,
LENGTH(RMOVE) - INSTR(RMOVE, ','));
END LOOP;

IF STAT = 1 THEN
BEGIN
MSTART := TO_NUMBER(MSTART) + 1;
GOTO A;
END;

END IF;
MSTART := TO_NUMBER(MSTART) - 1;
END;
ELSE
BEGIN
WHILE (INSTR(REMOVE, TO_CHAR(MSTART)) > 0) LOOP
MSTART := MSTART + 1;
--dbms_output.put_line(MSTART||'A');
END LOOP;
END;
END IF;
END;
END IF;
SSN := LPAD(TO_CHAR(MSTART), XZERO, '0');
SN := REPLACE(SN, ZERO, SUBSTR(SSN, LENGTH(SSN) - XZERO, XZERO));

SSN := SN;
RETURN SSN;
END;

本文