oracle使用goto实现continue
oracle 10g没有continue功能，在11g才加入。如果要在10g实现continue，可以用goto代替。
--/
begin
for i in 1 .. 20
loop
if i < 10 then
GOTO print_now;
end if;
dbms_output.put_line(i);
<>
NULL;
end loop;
dbms_output.put_line('111');
end;
/
oracle的ASCII、ASCIISTR函数
oracle的ASCII、ASCIISTR函数
1、ASCII函数
作用是将字符转换为ascii码值，中文没有ascii码值，应该是转成某一编码再取编码的ascii值。
2、ASCIISTR函数
ASCIISTR函数，参数是一个字符串，如果这个字符在ASCII码表中有，则转成ASCII表中的字符。
如果没有，则转成\xxxx格式，xxxx是UTF-16的编码。
如果表中只有中文和英文、数字等字符，则可以用\来判断是否带有中文。
举例：
select * from bacs.tcim_per_account where ASCIISTR(USER_ID) LIKE '%\%';
3、对比区别
select ASCIISTR('A'), ASCIISTR('b'), ASCIISTR('才'), ASCII('A'), ASCII('b'), ASCII('才') from dual;
ASCIISTR('A') ASCIISTR('B') ASCIISTR('才') ASCII('A') ASCII('B') ASCII('才')
------------- ------------- ------------- ---------- ---------- ----------
A b \624D 65 98 15108493
oracle截取字符串
oracle截取字符串
1、substr
SUBSTR("字符串", 起始位, 截取长度)
从起始位置截取一定长度的字符串，起始位置从1开始
SUBSTR("字符串",起始位)
从起始位置截取到结尾的字符串
2、substrb
substrb格式和substr一致，substr()是按照字符来算的，而substrb()是按照字节来算的，对中文的计算不一致
在substr里一个中文算1个长度，在substrb里一个中文算2个长度
oracle简单运维
oracle数据库磁盘满了，常用的处理方法：
1）清空无用的大表
2）删除日志
3）转移表空间文件
一、准备工作
0、切换到oracle用户
su - oracle
1、查看oracle安装目录
echo $ORACLE_HOME
/u01/oracle/product/10.2.0/db_1
2、查看实例名称
echo $ORACLE_SID
pbcssit
二、清空无用的大表
1、查看数据占用大小
select * from dba_segments where owner = 'NPBCS';
plsql在BYTES列，右键 - Column Totals - Sum
可以看到数据占用了6G多的空间
建议：按照BYTES大小降序排列，查看是否有历史表、临时表可以清空
2、查看表空间文件
select * from dba_data_files;
查看表空间文件占用磁盘大小，关闭自增功能，修改AUTOEXTENSIBLE为NO
因为有些表自动扩展是无限制的，会把磁盘都占满，测试环境可以把表空间初始值设置的大一点，如果满了手工添加
3、查看表空间剩余大小
select * from dba_free_space;
三、删除日志
1、cd到oracle安装目录下
du -sh * 4.8M adump 5.5G bdump 73M cdump 8.0K dpdump 8.0K pfile 3.0G udump
删除365天前的文件：
find . -name "*" -mtime +365 -exec rm -rf {} \;
四、转移表空间文件
通过软链接把表空间文件移动到有空闲空间的磁盘。
五、oracle状态查询
1、查看oracle自有进程
ps -ef | grep ora_
2、查看oracle SID
echo $ORACLE_SID
3、查看oracle安装目录
echo $ORACLE_HOME
4、查看oracle连接数
ps -ef | grep oracle
看 (LOCAL=NO) 数量，表示不是本地连接，为外部连接进来的
5、查看系统session数量
select * from v$session;
6、查看参数文件配置
select * from v$parameter;
oracle 创建索引 create index online
普通创建索引的命令create index会先锁表，然后再创建索引，如果表中数据量很大，会造成阻塞DML语句。
加了online后，可以以非独占的方式创建和删除索引。
但是加online字段有一些限制：
ONLINE
Specify ONLINE to indicate that DML operations on the table will be allowed during creation of the index.
Restrictions on Online Index Building
Parallel DML is not supported during online index building. If you specify ONLINE and then issue parallel DML statements, Oracle returns an error.
You cannot specify ONLINE for a bitmap index or a cluster index.
You cannot specify ONLINE for a conventional index on a UROWID column.
For a unique index on an index-organized table, the number of index key columns plus the number of primary key columns in the index-organized table cannot exceed 32.
参考资料：
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_5012.htm
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会报错
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'))
);
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
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高水位线会下降（释放表空间）
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;
/