Oracle常见小笔记（不停更新）

Case 1:

　　1，查询结果显示不好看，但是每次都SET LINESIZE、PAGESIZE又太麻烦。
　　2，用edit命令编辑缓冲区内容（上一条命令），有时不弹出窗口，因为默认编辑器可能设置错了，windows一般设置记事本。

方法：
      1，找到安装目录下的\sqlplus\admin\glogin.sql(我的：D:\myProgram\Oracle10\sqlplus\admin\glogin.sql）
      2，编辑文件，在最后添加下面字段后保存：
         define _editor=notepad
         set pagesize 9999
         set linesize 200

Case 2: SQLPLUS不支持按上下键来获取缓冲区内容（11G以前的版本）。

方法：

　　在DOS下登陆SQLPLUS（登陆方法运行cmd--->执行 sqlplus "登录名/密码" 如sqlplus "scott/tiger")，用DOS的上下键获取。

Case 3: 实现ID自增长。

方法：

---------表test1,触发器trig1,序列test1_seq搭配实现表ID的自动增长------------

DROP TABLE test1;
DROP SEQUENCE test1_seq;
DROP TRIGGER trig1;

CREATE  TABLE test1(
    id NUMBER PRIMARY KEY,     
    uname VARCHAR(10),
    age NUMBER);

CREATE SEQUENCE test1_seq
          INCREMENT BY 1 -- 每次加几个
          START WITH 1 -- 从1开始计数
          NOMAXVALUE -- 不设置最大值
          NOCYCLE -- 一直累加，不循环
          NOCACHE -- 不建缓冲区;

CREATE TRIGGER trig1 
      BEFORE INSERT ON test1 FOR EACH ROW WHEN (new.id IS NULL)
      BEGIN
          SELECT test1_seq.nextval INTO:new.id FROM dual;
      END;
    /

Case 4: 与标准T_SQL一些小区别

　　1，表和字段的命名不能以下划线打头；

　　2，为表取别名不支持AS，如：SELECTcount(*) FROM test2 t2

　　3，插入数据到表必须有 INTO关键字，不能是：INSERT test4(uname,age) VALUES('zhaoliu',44); --mysql支持

　4，用连接查询join与关键字on搭配，on后面需有条件，不能是：SELECT t3.age,t4.uname FROM test3 t3 join test4 t4 --mysql支持

Case 5: 用户登录几次不成功被锁定，需要解锁比如JDBC操作的时候

方法：

1，用system或sys用户登陆
2，ALTER USER username ACCOUNT UNLOCK;

Case 6: 忘记管理员密码或者修改用户密码

方法：

1，用system或sys用户登陆；

如果服务器在本机，忘记密码也可以直接登录，名字密码都随便，但是要加 as sysdba（如名字为：aaa as sysdba 密码：bbb）；

这是因为当前使用电脑的用户已经通过管理员验证，加as sysdba相当于跳过Oracle本地验证；如远程连接则不行。

2，修改管理员或者用户的密码：alter user user01 identified by 123（把USER01用户密码修改为123）

Case 7:Oracle解锁

Oracle解锁和修改密码的语句：

用户已经被锁住了，一般是用错误的密码尝试次数太多，得解锁用system或sys用户登陆，然后 ALTER USER username ACCOUNT UNLOCK;

ORACLE默认创建2个用户   SYS，密码：change_on_install               
       SYSTEM，密码：manager 一般登陆时可以输入：D:\>sqlplus "/as sysdba"        
          或：D:\>sqlplus user/password

Case 8:分钟请使用mi

　　日期里面的分钟请使用mi，而不要使用mm，因为oracle不区分大小写，所以会把mm当做日期MM来处理

　　select to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') from dual; --v
　　select to_char(sysdate,'yyyy-MM-dd HH24:mm:ss') from dual;　 --x

Case 9:返回字段列名统一大写

oracle不区分大小写，查询返回的时候列名全转为大写

例：

　　select t.pk_id pk_id,

t.taskid taskid,

t.submittimes submittimes,

t.updated_date updated_date,

tl.taskname taskname,

fa.fa_name fa_name

from eoa_to_pases_task_for_trriger t,

f_account fa

PK_ID 162CDE760D373559E0531580140A301B

TASKID N0000000000101360672

SUBMITTIMES 12

UPDATED_DATE 2015-05-16 13:10:02

TASKNAME app中奖名单_lingpeng9

FA_NAME 谢和坤　　

Case 10:日期比较，查询最近几天（trunc）

（1）查询最近七天的数据（trunc能将日期截取到天）

select t.updated_date, trunc(sysdate) - trunc(t.updated_date)

from EOA_TO_PASES_TASK_FOR_TRRIGER t

where trunc(sysdate) - trunc(t.updated_date) <= 7;

如果不使用trunc可能出现误差，因为包含了时分秒的加减，一天照24小时转换

select t.updated_date, (sysdate - t.updated_date)

from EOA_TO_PASES_TASK_FOR_TRRIGER t

where (sysdate - 7) <= t.updated_date;

Case 11: ROUND(四舍五入)和TRUNC（按精度截取）

（1）

SQL> select round(55.5),round(-55.4),trunc(55.5),trunc(-55.5) from dual;

ROUND(55.5) ROUND(-55.4) TRUNC(55.5) TRUNC(-55.5)
----------- ------------ ----------- ------------
56 -55 55 -55

decode 函数及其用法：参考http://blog.csdn.net/oscar999/article/details/18399177

decode (expression, search_1, result_1, search_2, result_2)

decode (expression, search_1, result_1, search_2, result_2, default)比较表达式和搜索字，如果匹配，返回结果；如果不匹配，返回default值；如果未定义default值，则返回空值。

使用：

　　条件语句:统计男女各数

　　select count(decode(FA_HAVEACCOUNT,'1',1)) ,count(decode(FA_HAVEACCOUNT,'0',0)) from f_account f;
　　select count(*) from f_account f where FA_HAVEACCOUNT='1';
　　select count(*) from f_account f where FA_HAVEACCOUNT='0';

　　自定义排序：　

　　select PK_ID, TASKID, SUBMITTIMES, FLAG
　　from (select PK_ID, TASKID, SUBMITTIMES, FLAG
　　from EOA_TO_PASES_TASK_FOR_TRRIGER t
　　where (t.flag = 0)
　　OR (t.flag = 1 AND ROUND(TO_NUMBER(sysdate - t.updated_date) * 24 * 60) > 10)
　　OR (t.flag = -1
　　AND t.submittimes>=10
　　AND (TRUNC(sysdate) - TRUNC(t.created_date)) <= (select item_value from e_syspara where item_name = 'autoReSubmitToPasesDays')
　　AND TRUNC(t.updated_date)!=TRUNC(sysdate))
　　ORDER BY decode(t.flag, 0, 1, 1, 2, -1, 3), t.created_date desc)

　　行列转换：

往以上table 再插入一些其他学生的成绩：

1. insert into student_score (name,subject,score)values('li shi','Chinese',96);
2. insert into student_score (name,subject,score)values('li shi','Mathematics',86);
3. insert into student_score (name,subject,score)values('li shi','English',76);
5. insert into student_score (name,subject,score)values('wang wu','Chinese',92);
6. insert into student_score (name,subject,score)values('wang wu','Mathematics',82);
7. insert into student_score (name,subject,score)values('wang wu','English',72);

使用以下SQL:

1. select name,
2. sum(decode(subject, 'Chinese', nvl(score, 0), 0)) "Chinese",
3. sum(decode(subject, 'Mathematics', nvl(score, 0), 0)) "Mathematics",
4. sum(decode(subject, 'English', nvl(score, 0), 0)) "English"
5. from student_score
6. group by name;

Oracle trunc()函数的用法

--Oracle trunc()函数的用法
/**************日期********************/
1.select trunc(sysdate) from dual --2013-01-06 今天的日期为2013-01-06
2.select trunc(sysdate, 'mm') from dual --2013-01-01 返回当月第一天.
3.select trunc(sysdate,'yy') from dual --2013-01-01 返回当年第一天
4.select trunc(sysdate,'dd') from dual --2013-01-06 返回当前年月日
5.select trunc(sysdate,'yyyy') from dual --2013-01-01 返回当年第一天
6.select trunc(sysdate,'d') from dual --2013-01-06 (星期天)返回当前星期的第一天
7.select trunc(sysdate, 'hh') from dual --2013-01-06 17:00:00 当前时间为17:35
8.select trunc(sysdate, 'mi') from dual --2013-01-06 17:35:00 TRUNC()函数没有秒的精确
/***************数字********************/
/*
TRUNC（number,num_digits）
Number 需要截尾取整的数字。
Num_digits 用于指定取整精度的数字。Num_digits 的默认值为 0。
TRUNC()函数截取时不进行四舍五入
*/
9.select trunc(123.458) from dual --123
10.select trunc(123.458,0) from dual --123
11.select trunc(123.458,1) from dual --123.4
12.select trunc(123.458,-1) from dual --120
13.select trunc(123.458,-4) from dual --0
14.select trunc(123.458,4) from dual --123.458
15.select trunc(123) from dual --123
16.select trunc(123,1) from dual --123
17.select trunc(123,-1) from dual --120

发表于 2013-03-01 16:10 寂寥的出生在1874 阅读(209) 评论(0) 编辑收藏举报

刷新页面返回顶部

Oracle常见小笔记（不停更新）

Oracle trunc()函数的用法

公告