Oracle学习笔记（六）

《子查询的妙用》
当使用values字句时，一次能插入一行数据，当使用子查询插入数据时，一条insert语句可以插入大量的数据。当处理行迁移或者装载外部表的数据到数据库时，可以使用子查询来插入数据。
用子查询插入数据：
例子：新建一张表，把10号部门的信息插入表中。
create table kkk(
  myid number(4),
  myname varchar(50),
  myDept number(5)
)
insert into kkk (myid,myname,myDept) select empno,ename,deptno from emp where deptno=10;
select * from kkk;
用子查询更新数据：
例子：希望员工scott的岗位、工资、补助与smith员工的一样。
update emp set (job,sal,comm)=(select job,sal,comm from emp where ename='smith') where ename='scott';
-------------
事务:用于保证数据的一致性，它由一组相关的dml语句（增，删，改）组成.
提交事务：commit;
回退事务：先设置保存点(可以设置多个保存点),savepoint a;回滚 rollback to a;commit后，保存点消失。如果没有commit,但exit了，则它会自动提交。
事务的几个重要操作：设置保存点 savepoint a,取消部分事务rollback to a,取消全部事务 rollback
《java程序中如何使用事务》
例子：从一个账户减去10，在另一个账户加10
package yzh;
import java.sql.*;
public class OracleConnect2 {
 
 public static void main(String[] args) {
  Connection con=null;
  Statement st=null;
  ResultSet rs=null;
  try{
   System.out.println("++++++++++++++++++");
   Class.forName("oracle.jdbc.driver.OracleDriver");
   con=DriverManager.getConnection("jdbc:oracle:thin:@WWW-EB6353B8576:1521:orcl","system","yzh");
   //加入事务处理
   con.setAutoCommit(false);
   
      st=con.createStatement();
      st.executeUpdate("update emp set sal=sal-10 where ename='heizi'");
   //加入异常
   //int i=7/0;
      st.executeUpdate("update emp set sal=sal+10 where ename='xiaohei'");
   //提交事务
   con.commit();
  }catch(Exception e){
      try{
     con.rollback();
   }catch(Exception ee){
     ee.printStackTrace();
   }
   e.printStackTrace();
  }finally{
   try{
    rs.close();
    st.close();
    con.close();
   }catch(Exception ex){
    ex.printStackTrace();
   }
  }
 }

}
------------
只读事务：只允许执行查询的操作，而不允许执行任何其他dml操作的事务，使用它可以确保用户只能取得某时间点的数据。
设置只读事务：set transaction read only
-------------
《Sql函数的使用》
字符函数：
lower(char) :将字符串转化为小写格式//select lower(name) from student;
upper(char) :将字符串转化为大写格式//select upper(name) from student;
length(char):返回字符串长度//select * from student where length(name)=5;
substr(char,m,n):取字符串的子串,m代表从第几位取，n代表取几个字符//select substr(name,2,3) from student;//
首字母大写,其余小写：
第一步：select upper(substr(name,1,1)) from student;
第二步：select lower(substr(name,2,length(name)-1)) from student;
合并：select upper(substr(name,1,1))||lower(substr(name,2,length(name)-1)) from student;
instr(char1,char2,[,n[,m]]):取子串在字符串中的位置
replace(char1,search_string,replace_string)：替换
数学函数：
cos,cosh,exp,ln,log,sin,sinh,sqrt,tan,tanh,acos,asin,atan,round
常用的有
round(n,[m]),用于执行四舍五入，如果省去m，则四舍五入到整数；如果m是正数，则四舍五入到小数点的m位后;如果m是负数，则四舍五入到小数点的m位前
trunc(n,[m]),用于截取数字。如果省掉m,就截掉小数部分，如果m是正数，则截取到小数点m位后，如果m是负数，则截取到小数点的前m位
mod(m,n),取模
floor(n),返回小于或是等于n的最大整数
ceil(n)，返回大于或是等于n的最小整数
abs(n),返回绝对值
power(m,n),返回m的n次幂
日期函数：
默认情况下日期格式是dd-mon-yy 即12-5月-88
sysdate:该函数返回系统时间
add_months(d,n)
last_day(d):返回指定日期所在月份的最后一天
例子：查询入职8个多月的员工信息
select * from emp where sysdate>add_months(hiredate,8);//add_months(hiredate,8);表示雇佣时间加上八个月时间
显示满十年服务年限的员工的姓名和受雇日期
select * from emp where sysdate>add_months(hiredate,8);//add_months(hiredate,12*10);
对于每个员工，显示其加入公司的天数
select trunc(sysdate-hiredate) "入职天数" from emp ;
找出各月倒数第三天受雇的所有员工
select * from emp where  last_day(hiredate)-2=hiredate;
转换函数：
Oracle可以进行隐含的数据类型的转换，但是它并不适应所有情况。
to_char()
日期是否可以显示为时/分/秒？
select ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss') from emp;
薪水是否可以显示指定的货币符号？
//已知：sal number(7,2)
select ename,to_char(sal,'L99999.99') from emp;//L表示本地货币符号
select ename,to_char(sal,'L99,999.99') from emp;
select ename,to_char(sal,'$99,999.99') from emp;//美金符号
显示1980年入职的所有员工？
select * from emp where to_char(hiredate,'yyyy')=1980;
显示所有12月份入职的员工？
select * from emp where to_char(hiredate,'mm')=12;
to_date()函数：
系统函数：
sys_context
参数：
terminal:当前会话客户所对应的终端的标识符
language:语言
db_name:当前数据库名称
nls_date_format:当前会话客户所对应的日期格式
session_user:当前会话客户所对应的数据库用户名
current_schema:当前会话客户所对应的默认方案名
host:返回数据库所在主机的名称
例子：select sys_context('USERENV','db_name')from dual;
select sys_context('USERENV','language')from dual;
select sys_context('USERENV','session_user')from dual;
select sys_context('USERENV','current_schema')from dual;
方案名跟用户名一样的.Oracle以方案的方式组织数据对象。方案中有视图、函数、表等。