天使羊波波闪耀光芒 软件及互联网爱好者

2112月/160

Oracle sum字段报错 ORA-01722: invalid number

有一个sql要统计多个字段的sum值:

select card_accp_id c1,date_settlmt_8, sum(amt_trans), sum(mcht_fee), sum(host_trans_fee1)
from bth_new_gc_txn_succ where date_settlmt_8 > '20160101' group by card_accp_id,date_settlmt_8

执行报错:

14:14:36 [SELECT - 0 row(s), 0.000 secs] [Error Code: 1722, SQL State: 42000] ORA-01722: invalid number
... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [0 successful, 0 warnings, 1 errors]

解决:
原因是sum字段有空格,在host_trans_fee1上加个trim()

结果:

select card_accp_id c1,date_settlmt_8, sum(amt_trans), sum(mcht_fee), sum(trim(host_trans_fee1))
from bth_new_gc_txn_succ where date_settlmt_8 > '20160101' group by card_accp_id,date_settlmt_8

sum字段如果遇到有记录为非number会报错

223月/160

oracle 分区表

分区表的概念,简单来说就是:原来一个用户下表的数据是放在一个表空间里,使用了分区表后,表的数据是放在多个表空间中。

PS:表空间是包括一个用户下所有文件的逻辑概念。

如果一个数据库中,某几个表(特别是历史表)占了数据库50%以上的空间,或达到几百G的磁盘容量。这时就要考虑使用分区表了。

Oracle表分区分为四种:范围分区(Range分区),散列分区(Hash分区),列表分区(List分区)和复合分区(范围-散列分区range-hash,范围-列表分区range-list)

分区的好处,主要用到以下两点:
改善查询性能
维护备份数据方便

一、范围分区
范围分区就是根据表中某一字段的值的范围来分区,特别适用于时间日期、ID号来分区

1、建立测试用户

create user wangyi identified by wangyi;
grant dba to wangyi;
conn wangyi/wangyi;

2、建立测试表和数据
//策略是按月创建分区,保留一年数据,年末备份前年数据。

例子:

create table range_table (
inst_date date,
produce_id number,
amt varchar(12)
)
partition by range(inst_date)
(
partition p1 values less than(to_date('01/02/2016','DD/MM/YYYY')),
partition p2 values less than(to_date('01/03/2016','DD/MM/YYYY')),
partition p3 values less than(to_date('01/04/2016','DD/MM/YYYY')),
partition p4 values less than(to_date('01/05/2016','DD/MM/YYYY')),
partition p5 values less than(to_date('01/06/2016','DD/MM/YYYY'))
);

223月/160

oracle interval函数

一、interval函数
interval函数用于将一个整形值转换成时间值date类型的年月或时分秒。

oracle语法:

interval 'integer [- integer]' {YEAR | MONTH | DAY | HOUR | MINUTE | SECOND} [(precision)][TO {YEAR | MONTH | DAY | HOUR | MINUTE | SECOND}]

注:
1、年、月、日、时、分、秒可以单独使用。
2、{YEAR | MONTH} to {YEAR | MONTH}和
{DAY | HOUR | MINUTE | SECOND} to {DAY | HOUR | MINUTE | SECOND}是分别使用。
3、该函数常用来计算一段时间差。
4、precision为精度域,有效范围是0到9,默认值为2。这里精度是指显示结果的精度,如果结果超过两位长度要指定精度。

例子:
1、表示2个年

select interval '2' year from dual;

INTERVAL'2'YEAR
---------------------------------------------------------------------------
+02-00


2、表示1234个年

select interval '1234' year(4) from dual;

INTERVAL'1234'YEAR(4)
---------------------------------------------------------------------------
+1234-00

252月/161

oracle清理数据问题

一、删除一个用户下所有数据库对象
一种方法是删除这个用户然后重建,需要管理员操作:

drop user wangyi cascade; --删除用户
select * from dba_users where username = 'wangyi'; --查询默认表空间
drop tablespace WANGYI_DTBS including contents and datafiles; --删除表空间

重建用户:

create user wangyi identified by wangyi;
grant create session, create view, create any index, imp_full_database to wangyi;
alter user wangyi default tablespace wangyi temporary tablespace temp;
alter user wangyi quota unlimited on wangyi;

二、清除整个表的内容

delete from 表名;
truncate table 表名;

delete和truncate的区别:
delete删除的数据可以恢复,truncate不能恢复
delete高水位线不会下降,truncate高水位线会下降(释放表空间)

17月/152

PL/SQL基础学习(4)

--1、select * into 必须只满足一条记录 没有记录或有多条记录会报异常
--只能取出一条 要取多条要用游标
--2、update和delete语句没有记录或多条记录满足都不会出现异常

3、
--select * into 如果没找到记录会出现异常

--/
DECLARE
v_emp emp%rowtype;
BEGIN
select * into v_emp from emp where empno = 9999;
dbms_output.put_line(v_emp.ename);
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('未找到记录');
END;
/

4、
--select * into 如果有多行记录满足也会出现异常

--/
DECLARE
v_emp emp%rowtype;
BEGIN
select * into v_emp from emp where job = 'MANAGER';
dbms_output.put_line(v_emp.ename);
EXCEPTION
WHEN TOO_MANY_ROWS THEN
dbms_output.put_line('超过一行返回');
END;
/

17月/150

oracle数据库对象-游标

游标分为隐式游标和显示游标。

一、隐式游标
对于select * into和DML操作(update、delete、insert)都附带有一个隐式游标。

隐式游标的属性

SQL%ROWCOUNT   整型  代表DML语句成功执行的数据行数
SQL%FOUND    布尔型  值为TRUE代表插入、删除、更新或单行查询操作成功
SQL%NOTFOUND 布尔型  与SQL%FOUND属性返回值相反
SQL%ISOPEN   布尔型  DML执行过程中为真,结束后为假


当系统使用一个隐式游标时,可以通过隐式游标的属性来了解操作的状态和结果,进而控制程序的流程。隐式游标可以使用名字SQL来访问,但要注意,通过SQL游标名总是只能访问前一个DML操作或单行SELECT操作的游标属性。所以通常在刚刚执行完操作之后,立即使用SQL游标名来访问属性。

例子:

--/
DECLARE
v_emp emp%rowtype;
BEGIN
delete from emp where empno = 9999;
IF SQL%notfound THEN
  dbms_output.put_line('隐式游标 属性为notfound');
END IF;
dbms_output.put_line('delete无记录不会出现异常');
END;
/

306月/150

PL/SQL基础学习(3)

1、--helloworld程序

--/
BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello World!!!');
END;
/


2、--定义变量
--变量名 类型

--/
DECLARE
  A NUMBER(5, 2) := -1;
  B NUMBER(5, 2) := 3;
  C NUMBER(5, 2);
BEGIN
  C := A * B / (A + B);
  DBMS_OUTPUT.PUT_LINE('c=' || C);
EXCEPTION
  WHEN ZERO_DIVIDE THEN
    DBMS_OUTPUT.PUT_LINE('除数不能为0!');
END;
/


3、--定义常量

--/
DECLARE
a CONSTANT NUMBER := 100;
BEGIN
DBMS_OUTPUT.PUT_LINE(a);
END;
/

296月/150

oracle数据库对象-包

程序包是ORACLE PL/SQL的一个特性,它就像是一个容器或者说是一个命名空间,可以将各种逻辑相关的类型、常量、变量、异常和子程序结合在一起。为开发人员编写大型复杂应用程序时,提供了一个良好的组织单元。

当定义好了程序包之后,应用程序就可以通过包来访问各种不同的功能单元,而不用担心过多零散的子程序导致程序代码的松散。

一个PL/SQL程序包包含如下两个部分组成:
·包规范:主要是包的一些定义信息,不包含具体的代码实现部分。
·包体:包体是对包规范中声明的子程序的实现部分,包体的内容对于外部应用程序来说是不可见的,包体就像是一个黑匣子一样,是对包规范的实现。

1、包规范基本语法

CREATE [OR REPLACE] PACKAGE PACKAGE_NAME
{IS | AS}
  PACKAGE_SPECIFICATION
END PACKAGE_NAME;


例子:

--/
CREATE OR REPLACE PACKAGE pack_fun IS
--定义一个函数返回两个数的和
FUNCTION fun_add(num1 IN NUMBER, num2 IN OUT NUMBER) RETURN NUMBER;
--定义一个函数返回两个数的差
FUNCTION fun_sub(num1 IN NUMBER, num2 IN OUT NUMBER) RETURN NUMBER;
--定义一个存储过程显示九九乘法表
PROCEDURE proc_1;
END;
/

286月/150

oracle数据库对象-函数

函数的作用是计算一个功能,往往是用来计算并返回一个计算结果。

1、函数和存储过程的区别:
1)函数和过程比较类似,不同的是函数必须返回一个值,而过程仅是为了执行一系列的操作。

2)在调用的时候,函数可以作为表达式的一部分进行调用,并且可以使用在SELECT中。而过程只能作为一个PL/SQL语句进行调用,且不能在SELECT语句中使用。

3)函数的语法结构和过程比较类似,除了函数使用FUNCTION进行定义之外。外一个重要的特点就是,函数具有RETURN子句,指定函数的返回类型。

4)函数和存储过程都可以使用out参数来返回内容。

2、基本语法:

CREATE [OR REPLACE] FUNCTION function_name
[(parameter_name [IN | OUT | IN OUT]  TYPE[,....])]
RETURN TYPE
{IS | AS}
BEGIN
  FUNCTION BODY
END function_name;

286月/150

oracle数据库对象-存储过程

存储过程:一段程序,用于改变数据库对象的状态,可以包含一个或多个行为,往往是将一个表的记录经过处理后放到另一个表。

1、基本语法:

CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN | OUT | IN OUT] TYPE[,....])]
{IS | AS }
  [LOCAL declarations]
BEGIN
  executable statements;
[EXCEPTION 
  exception_statements;
END procedure_name;


例子:

--/
CREATE OR REPLACE PROCEDURE proc_1 (num number, name varchar2)
IS
BEGIN
  insert into emp(empno, ename) values (num, name);
END;
/

第 1 页,共 8 页12345...末页 »