ORACLE学习笔记（一）

一、oracle trunc 用法——trunc(sysdate)——Trunc函数是用来截取日期或数字的。

 

(1)用法及解释

         select to_char(sysdate,'yyyy-mm-ddhh24:mi:ss') from dual;  --显示当前时间

         select trunc(sysdate,'year') fromdual; --截取到年（本年的第一天）

         select trunc(sysdate,'q') fromdual; --截取到季度（本季度的第一天）

         select trunc(sysdate,'month') fromdual; --截取到月（本月的第一天）

         select trunc(sysdate,'') fromdual;

         selectto_char(trunc(sysdate),'yyyymmdd hh24:mi:ss') from dual; --默认截取到日（当日的零点零分零秒）

         select trunc(sysdate-1,'w') fromdual;  -- 离当前时间最近的周四，若当天为周四则返回当天，否则返回上周四

         select trunc(sysdate,'ww') fromdual;  --截取到上周末（上周周六）

         select trunc(sysdate,'day') fromdual; --截取到周（本周第一天，即上周日）

         select trunc(sysdate,'iw') fromdual; --本周第2天，即本周一

         select to_char(trunc(sysdate,'dd'),'yyyymmdd hh24:mi:ss') from dual;--截取到日（当日的零点零分零秒）

         select trunc(sysdate,'hh24') fromdual;  --截取到小时（当前小时，零分零秒）

         select trunc(sysdate,'mi') from dual; --截取到分（当前分，零秒）

         select trunc(sysdate,'ss') fromdual ;--报错，没有精确到秒的格式

（2）时间字段要求：去掉秒

         ① 输出当日零点零分： selectto_char(trunc(sysdate),'yyyy-mm-dd hh24:mi') from dual;

         TO_CHAR(TRUNC(SY

         --------------------------

         2017-07-21 00:00

         ② 输出当前时间并截取到分： select to_char(sysdate,'yyyy-mm-ddhh24:mi') from dual;

         TO_CHAR(SYSDATE,

         --------------------------

         2017-07-21 11:53

 

 

二、ORACLE Delete与Truncate的区别

（1） 语法：        delete from aa;          truncate table aa;

（2）区别

         1.delete from后面可以写条件，truncate不可以。

         2.delete from记录是一条条删的，所删除的每行记录都会进日志，而truncate一次性删掉整个页，因此日至里面只记录页释放，简言之，delete from更新日志，truncate基本不，所用的事务日志空间较少。

 

 

         3.delete from删空表后，会保留一个空的页，truncate在表中不会留有任何页。

         4.当使用行锁执行 DELETE 语句时，将锁定表中各行以便删除。truncate始终锁定表和页，而不是锁定各行。

         5.如果有identity产生的自增id列，delete from后仍然从上次的数开始增加，即种子不变，而truncate后，种子会恢复初始。

          6.truncate不会触发delete的触发器，因为truncate操作不记录各个行删除。

 

 

（3）总结

          1.truncate和 delete只删除数据不删除表的结构(定义)。

          2.drop语句将删除表的结构被依赖的约束(constrain),触发器(trigger),索引(index); 依赖于该表的存储过程/函数将保留,但是变为invalid状态。

          3.delete语句是dml,这个操作会放到rollback segement中,事务提交之后才生效;如果有相应的trigger,执行的时候将被触发；truncate,drop是ddl, 操作立即生效,原数据不放到rollback segment中,不能回滚. 操作不触发trigger。

 

 

          4.速度,一般来说: drop> truncate > delete。

          5.安全性:小心使用drop 和truncate,尤其没有备份的时候.否则哭都来不及。

 

三、ORACLE  varchar2最大长度和dbms_output.putline输出缓冲区大小问题

          网上经常有人问Oracle varchar2最大支持长度为多少？其实这个叫法不太准确，varchar2分别在oracle的sql和pl/sql中都有使用。

          oracle 在sql参考手册和pl/sql参考手册中指出：

              ①oracle sql varchar2的最大支持长度为4000个字节(bytes)；

             ②而oracle pl/sql中的varchar2最大支持长度为32767个字节。这就是有朋友问，在pl/sql中定义了32767个(字符/字节)，为什么在表的字段中不能定义大于4000个字节的原因了。

          如：在pl/sql中的存储过程或者函数中定义了一个varchar2（32767）的变量

                  p_out_1Varchar2(32767);

          而在函数体中使用了select、update、insert等sql语句之后，

                  select Dbms_Lob.substr(p_clob,Dbms_Lob.instr(p_clob,',',1,3500),1) into p_out_1 from dual; 

          再输出会报这样的错：

                  ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小

          所以在定义了varchar类型后，使用时应该改成赋值语句，尽量不要用sql语句，改了之后发现报出新问题：

                  ORA-20000:ORU-10027: buffer overflow, limit of 20000 bytes

          这是由于所定义的变量大小超过了dbms_output.putline输出要求的最大值，应该扩展输出缓冲区：在begin后面加上DBMS_OUTPUT.ENABLE(buffer_size=> null) ，表示输出buffer不受限制。这样就没问题了。

 

四、ORACLE函数之translate（）、instr（）、copy（）及substr（）的学习

 

      1、translate ( string，to_str，from_str )

          执行时,translate依次检查string中的每个字符,然后查找这个字符是否在from_str中存在。

          如果不存在，那么这个string中的字符被保留，也就是被返回，

          如果存在，那么translate会记下这个字符在from_str中的位置，然后用to_str的同样位置的字符代替string中的这个字符

 

      2、instr（sourceString，destString，start，appearPosition）

           解析：instr（'源字符串' , '目标字符串' ,'开始位置','第几次出现'）

           sourceString代表源字符串；

           destString代表要从源字符串中查找的子串；

           start代表查找的开始位置，这个参数可选的，默认为1；

           appearPosition代表想从源字符中查找出第几次出现的destString，这个参数也是可选的，默认为1 。

           如果start的值为负数，则代表从右往左进行查找，但是位置数据仍然从左向右计算。返回值为查找到的字符串的位置。

 

      3、substr（cExpression，nCharactersReturned，nStartPosition）

           解释：从第nStartPosition的位置开始截取nCharactersReturned个cExpression中的字符

           用处是从给定的字符表达式或备注字段中返回一个子字符串。

           cExpression指定要从其中返回字符串的字符表达式或备注字段；

           nCharactersReturned用于指定返回的字符数目，缺省时返回字符表达式的值结束前的全部字符；

           nStartPosition用于指定返回的字符串在字符表达式或备注字段中的位置。

 

      4、dbms_lob.copy（dest_lob ,src_lob , amount , dest_offset , src_offset）

           解析：

           dest_lob  IN  OUT  NOCOPY  BLOB/CLOB/NCLOB,

           src_lob  IN  BLOB/CLOB/NCOB,

           amount  IN  INTEGER,

           dest_offset  IN  INTEGER:=1,

           src_offset  IN  INTEGER:=1

            从src_lob中，以src_offset为起始位置，截取amount个字符/字节，放到dest_lob的dest_offset位置。

 

 

 

注：内容非原创，但由我个人在学习的过程中整理和练习而作。