Oracle 基本操作

1. 日期格式化

TO_DATE('2015-07-28 08:00:00',  'YYYY-MM-DD HH24:MI:SS')

2. 表数据备份

create table dbname.tablename_bak as select * from dbname.tablename where 1=2

insert into tablename_bak select * from tablename; 

3. 上一条，下一条数据

1. /*语法*/  
2. lag(exp_str,offset,defval) over()  
3. Lead(exp_str,offset,defval) over()  
4. --exp_str要取的列  
5. --offset取偏移后的第几行数据  
6. --defval:没有符合条件的默认值

lead(tr_step, 1, '') over(partition by tester_id, head_id order by system_time asc) as end_tr_step

lag(tr_step, 1, '') over(partition by tester_id, head_id order by system_time asc) as last_tr_step

4. 取前10条数据

select * from (select * from tablename order by columnname desc) where rownum <=10

5. 调用存储过程

SQL窗口:

call   procedure_name(parameter);

begin   procedure_name(parameter);  

end;

COMMAND窗口:

exec   procedure_name(parameter);

call   procedure_name(parameter);

begin   procedure_name(parameter);  

end;

6. 统计时间差

(1) 以天为单位

round(to_number(end-date-start_date))

(2) 以小时为单位

round(to_number(end-date-start_date)*24)

(3) 以分钟为单位

round(to_number(end-date-start_date)*1440)

7. 数据库参数查询

1. --查看连接数  
2. select count(*) from v$process  
3.    
4. --查看充许链接数  
5. select value from v$parameter where name='processes'  
6.    
7. --查看最大并发数  
8. select * from v$license

8. 表锁定及解锁

（1）锁表查询sql

SELECT object_name, machine, s.sid, s.serial#

FROM gv$locked_object l, dba_objects o, gv$session s

WHERE l.object_id = o.object_id

AND l.session_id = s.sid;

（2）解锁，释放SESSION SQL

alter system kill session 'sid, serial#';

9. 函数

(1)create function

CREATE OR REPLACE FUNCTION GET_CUSTOMER_INFO(P_TESTER_ID NVARCHAR2,

                                             P_HEAD_ID   NUMBER,

                                             P_LOT_ID    NVARCHAR2)

  RETURN NVARCHAR2 IS

  V_CUST_NAME   NVARCHAR2(10);

  V_DEVICE_NAME NVARCHAR2(50);

  R_RESULT      NVARCHAR2(100);

BEGIN

EXCEPTION

  WHEN OTHERS THEN

    DBMS_OUTPUT.PUT_LINE();

END GET_CUSTOMER_INFO

(2)sys_connect_by_path,列合并为行

select max(substr(sys_connect_by_path(description, ','), 2))

  from (select description, rownum rn from rtm_depts)

 start with rn = 1

connect by rn = rownum;

--级联查询叶子结点

select parent_function_id,

       parentValue,connect_by_isleaf,

       ltrim(sys_connect_by_path(function_name, '|'), '|') valuues

  from (select f.parent_function_id,

               f.function_name,

               pf.function_name as parentValue,

               row_number() over(partition by f.parent_function_id order by f.function_id) as curr,

               row_number() over(partition by f.parent_function_id order by f.function_id) - 1 as prev

          from rtm_functions f, rtm_functions pf

         where f.parent_function_id = pf.function_id

         order by f.function_id) t

 where 1=1 and connect_by_isleaf = 1

 start with curr = 1

connect by prev = prior curr

       and parent_function_id = prior parent_function_id

SYS_CONNECT_BY_PATH ：实现将从父节点到当前行内容以“path”或者层次元素列表的形式显示出来

CONNECT_BY_ROOT： 它用在列名之前用于返回当前层的根节点

connect_by_isleaf：来判断当前行是不是叶子。如 果是叶子就会在伪列中显示“1”，如果不是叶子而是一个分支（例如当前内容是其他行的父亲）就显示“0”。

详见： oracle中使用sys_connect_by_path进行表中行值连接

10. Oracle免客户端For .Net

对比项	.Net自带Oracle驱动	ODP.Net驱动
连接字符串TNS写法	支持	支持
连接字符串非TNS写法	不支持	支持
是否支持Oracle9运行时	支持	不支持
是否支持Oracle11运行时	支持	支持
OCI目录设置方式	环境变量Path中设置	环境变量Path中设置 或 配置文件设置DllPath 或 注册表设置DllPath
其它环境变量	需要设置ORACLE_HOME	无
运行时安装包大小	9i运行时安装包13M	11g运行时安装包30M 最新ODP.Net共52M
绿色精简后大小	9i运行时6.5M	11g运行时30M
据说的差别	性能底下，功能特性少	性能最高，功能特性多

详见： http://www.cnblogs.com/nnhy/archive/2011/10/30/2229444.html