常用脚本:
--1. 循环插入测试语句:
declare
i int:=1;
begin
while i<=1000 loop
insert into t_b values(i,'I love xian'||i);
i := i+1;
if (i mod 100 = 0) then
commit;
end if;
end loop;
end;
/
* 关于job中的next_date的时间设定:
每1小时: TRUNC(SYSDATE,'hh')+1/24
每2小时: TRUNC(SYSDATE,'hh')+2/24
每1天的七点: trunc(sysdate,'dd')+1+7/24
每个月的十点: add_months(trunc(sysdate,'mm'),1)+10/24
*. PL/SQL (Procedural Language/SQL )是Oracle在标准SQL语言上的过程性扩展。
*. 服务名和SID分别是什么概念。
A: 对应就是DataBase名称,因为Oracle数据库没有Database/Schema的概念,对应的是实例。安装Oracle可以安装多个实例。 [SID]即为具体的数据库实例名。
* SQL*Plus的语法是 sqlplus user/password @server
server是网络服务名,或者称连接标识符
sqlplus username/password@servicename
*. 字符串连接采用 || 符号。例如:
update serviceinfo
set wsdlurl = 'http://exchange.xchg.sz.gov/iexchange/services/' || servicename || '?wsdl'
*. NVL函数用于将NULL变为实际值,其语法格式为 NVL(exp1,exp2)
*. LIKE操作符的 通配符包括 %和_
3. 在Oracle的数据库建表中,默认会创建index(index的column值是表主键)。
4. 当为Oracle创建表的主键或者外键时,如果没有制定外键/主键的名称,则系统会默认起名sys_xxxx的名字。
5. Oracle服务器更改IP后 listener服务无法启动的解决方案:
修改服务器端的listen.ora,更改客户端的tnsnames.ora即可。
一般路径在( E:\oracle\product\10.1.0\Db_1\NETWORK\ADMIN\ 下)
6. TOAD软件显示乱码问题的解决:
新建系统环境变量NLS_LANG ,变量值为AMERICAN_AMERICA.ZHS16GBK.
7. PS/SQL中,使用&可以定义临时变量。
高级查询——
集合操作符
UNION ALL,UNION,INTERSECT,MINUS
UNION ALL 是两个集合的简单合并;
UNION则是在合并的基础上进行排序和筛选,删除重复的记录,所以效率比较低。
其他使用函数
TRANSLATE(x,from_string,to_string)
DECODE(value,search_value,result,default_value)
使用CASE表达式来替代多个查询
select
count( case when price <13 then 1 else null end ) low,
count( case when price between 13 and 15 then 1 else null end ) med,
count( case when price >15 then 1 else null end ) high
from products;
游标
1. 游标的作用
当需要对获取结果集的每一行记录,进行复杂逻辑的判断。这时候就需要用到游标。例如"MERGE_UD_PAIR"这个存储过程
游标比批量更新Update的优势:对回滚段的耗用更少。
2. 游标的类型和属性
显示游标和隐式游标。
对于DML语句和单行SELECT INTO语句来说,Oracle会为她们分配隐式游标。
4种属性:
%FOUND,%NOTFOUND,%ROWCOUNT,%ISOPEN
3. 游标的使用步骤
4. 对游标的动态参数的赋值
类似 DBMS_SQL.BIND_VARIABLE(cursor_name8, ':v_imei', v_imei);
简单示例代码1(包含动态语句):
------------------------------------------
---- 游标以及动态语句的应用
-- SQL处理逻辑:
-- 1. 创建fix_XX_TABLE表,里面有正确数据的IMEI,MANU_ID。(PS:建议加上主键索引)
-- 2. 通过游标方式从fix_XX_TABLE表中取数据,然后一条条地去update超大表XX_TABLE(有一亿数据量以上)
-- 3. 动态SQL语句则使用 using来对参数赋值
------------------------------------------
drop table fix_XX_TABLE;
create table fix_XX_TABLE as
select t1.imei,t2.manu_id from XX_TABLE t1,model t2
where t1.model_id=t1.manu_id and t2.model_id=t1.model_id ;
declare
cursor p_cur is select imei,manu_id from fix_XX_TABLE;
v_imei varchar2(30);
v_manu_Id number;
i number default 0;
begin
write_run_log('update_XX_TABLE','update XX_TABLE start','0');
open p_cur;
loop
fetch p_cur into v_imei,v_manu_id;
exit when p_cur%notfound;
execute immediate 'update XX_TABLE set manu_id=:v_manu_id where imei=:v_imei'
using v_manu_id,v_imei;
i:=i+1;
if mod(i,10000)=0 then commit;
write_run_log('update_XX_TABLE','commit times='||to_char(i/10000),sqlcode);
end if;
end loop;
write_run_log('update_XX_TABLE','update XX_TABLE successed',sqlcode);
exception when others then
write_run_log('update_XX_TABLE','update XX_TABLE failed',sqlerrm);
end;
/
简单示例代码2
------------------------------------------
---- 游标以及动态语句的应用
-- SQL处理逻辑:
-- 1. 创建fix_XX_TABLE表,里面有正确数据的IMEI,MANU_ID。(PS:建议加上主键索引)
-- 2. 通过游标方式从fix_XX_TABLE表中取数据,然后一条条地去update超大表XX_TABLE(有一亿数据量以上)
-- 3. 动态SQL语句则使用 using来对参数赋值
------------------------------------------
drop table fix_XX_TABLE;
create table fix_XX_TABLE as
select t1.imei,t2.manu_id from XX_TABLE t1,model t2
where t1.model_id=t1.manu_id and t2.model_id=t1.model_id ;
declare
cursor p_cur is select imei,manu_id from fix_XX_TABLE;
v_imei varchar2(30);
v_manu_Id number;
i number default 0;
begin
write_run_log('update_XX_TABLE','update XX_TABLE start','0');
open p_cur;
loop
fetch p_cur into v_imei,v_manu_id;
exit when p_cur%notfound;
execute immediate 'update XX_TABLE set manu_id=:v_manu_id where imei=:v_imei'
using v_manu_id,v_imei;
i:=i+1;
if mod(i,10000)=0 then commit;
write_run_log('update_XX_TABLE','commit times='||to_char(i/10000),sqlcode);
end if;
end loop;
write_run_log('update_XX_TABLE','update XX_TABLE successed',sqlcode);
exception when others then
write_run_log('update_XX_TABLE','update XX_TABLE failed',sqlerrm);
end;
/
ResultSet与游标:
ResultSet就是结果集游标。
Connection创建Statement有一个方法是:Statement createStatement(int resultSetType, int resultSetConcurrency) 。
第一个参数是结果集游标类型,第二个参数是结果集的并发类型:
* @param resultSetType a result set type; one of
* <code>ResultSet.TYPE_FORWARD_ONLY</code>,
* <code>ResultSet.TYPE_SCROLL_INSENSITIVE</code>, or
* <code>ResultSet.TYPE_SCROLL_SENSITIVE</code>
* @param resultSetConcurrency a concurrency type; one of
* <code>ResultSet.CONCUR_READ_ONLY</code> or
* <code>ResultSet.CONCUR_UPDATABLE</code>
一般地读取大数据量,则设置为:
ps = con.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
若是使用分页读取结果集。则设置为:
ps = con.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);