oracle数据库简介
对象关系型的数据库管理系统 (ORDBMS)

在管理信息系统、企业数据处理、因特网及电子商务等领域使用非常广泛

在数据安全性与数据完整性控制方面性能优越

跨操作系统、跨硬件平台的数据互操作能力


Oracle数据库的主要特点

支持多用户、大事务量的事务处理

数据安全性和完整性控制

支持分布式数据处理

可移植性

一oracle数据库用户级权限
1,sqlserver 只能安装在windows系统上
  oracle 数据库是唯一 一个可以分区的数据库 可以跨平台使用



A.创建用户
show user  --查看当前用户
create user accp identified by a123;//创建用户(密码不能数字开头)
conn 用户名/密码;切换用户( 示例:conn accp/a123 )
conn system/orcl;
select * from scott.emp//当是系统管理员的时候可以查询其他用户的表
select * from tab;//查看当前用户的表
grant dba to accp 授予(accp用户)管理权限
desc  表名   查看表结构
解除锁定
alter user SCOTT account unlock; (用系统管理员(system/orcl)登录)
set lin 100 控制查询表不换行
l 看  上一条命令
/ 执行
 ed 编辑 打开一个记事本
dos命令版连接oracle
cmd->sqlplus
scott/tiger ;

desc dual;--查看dual表结构
二 .oracle 查询
设置语言
select to_char(sysdate,'day','NLS_DATE_LANGUAGE=AMERICAN') day from dual;//wednesday
1,oracle 查询数据区分大小写 表中添加的数据是大写 则查询时也是大写
如 1 insert into person values(1,'Jack');
select * from person   where name='jack' 查询不到数据
select * from emp where ename<'D';可以根据字母筛选数据
2.union (关键字联查) 排序
  union all 不排序
--两个列变成一个列
如
(select tname from teachers)
union (select sname from student);//查询老师学生的姓名
--查询中的关键字
--having 分组以后在筛选
 select deptno,sum(sal) from emp group by deptno
 having sum(sal)>=9000
--自连接
--  xxxx 是 xxx 老板
select e2.ename 自己 ,'老板是',e1.ename 上司 from emp e1
right join emp e2 on e1.empno=e2.mgr
--any --all
--比这些 任意一个人的工资都要高
select * from emp where sal >any(
   select sal from emp where empno in(7782,7788,7369)
)
-- 大于全部
select * from emp where sal >all(
   select sal from emp where empno in(7782,7788,7369)
)
--^=(不等于)、< >(不等于)、

-- 查交集( 所有查询中的列数和列的顺序必须相同。
 
     数据类型必须兼容。)
(select deptno from emp)
intersect
(select deptno from dept)
--带minus的子查询(左查询中返回右查询没有找到的所有非重复值,即,两个结果集的差集)
(select deptno from scott.dept)
minus

(select deptno from scott.emp);

--分页查询
select * from
(select rownum r,e.* from emp e ) s
where s.r>=2 and s.r<=5
--分页第二种
(select * from emp where rownum<=6)
minus
(select * from emp where rownum<=3)


拼接 ||
select empno||'他的名字'||ename from emp;
二 oracle 中几个简单函数的用法
--1．ceil( )函数
用法：
ceil（n），取大于等于数值n的最小整数。

select mgr, mgr/100,ceil(mgr/100) from scott.emp;
--2．mod( )函数
用法：mod（m,n），取m整除n后的余数。
--3．round( )函数
用法：round（m,n），四舍五入，保留n位
select round(45.4544,-1) from dual; 50
select round(45.4544,2) from dual;  45.45


select mgr, round(mgr/100,2),round(mgr/1000,2)
from scott.emp;
--4．avg( )函数
用法：avg（字段名），求平均值，要求字段为数值型。


select avg(mgr) 平均值 from scott.emp;

--5．count( )函数
用法：count（字段名）或count（*），统计总数。


select count(*) 记录总数 from scott.emp;
--6）trunc(num)/trunc(num,s) 截断数字，直接舍去
--其他函数
7）nvl(exp1,exp2) 空值替换函数
2）decode() 相当于case..when..语句
select ename,deptno,
decode(deptno,10,'AAA',20,'BBB',30,'CCC') 部门 from emp;

--分析函数，用于统计 排名
语法：函数名() over(order by 排序字段 asc | desc)
1、row_number() 无论值是否相等，生成连续的行号
select row_number() over(order by sal desc) 名次,ename,sal
from emp order by ename;


2、dense_rank() 如果值相等，则排名相同，排名仍连续
select dense_rank() over(order by sal ) 名次,ename,sal
from emp


3、rank()   如果值相等，则排名相同，排名不连续
select rank() over(order by sal ) 名次,ename,sal
from emp





三表间 数据复制
--创建表test 并复制scott.emp表的数据
create table scott.test

as (
        
     select empno,ename,hiredate
        
    from scott.emp
        
    where empno>=7000
    
 );
1表删除
truncate table命令将快速删除数据表中的所有记录，
但保留数据表的结构。

truncate table scott.test ;
 drop table 表名;--将表从数据库中删除。
1．语法

update 数据表

set 字段名1=(select 字段列表 from 数据表 where 条件),
示例
update scott.emp
set sal= (select sal+300 from scott.emp where empno=7499 )
where empno=7499;
   字段名2=(select 字段列表 from 数据表 where 条件),……


五.日期函数
--select sysdate from dual;//获得当前时间

1）sysdate 返回系统当前时间
2）用于加/减，关系运算
--几天之后
select sysdate+3 from dual;
--几天之前
select sysdate-3 from dual;
--间隔天数
select sysdate-to_date('2011-9-26','yyyy-mm-dd') from dual
--查询1982年1月1日之后入职的员工
select * from emp where hiredate>to_date('1982-1-1','yyyy-mm-dd')

3）months_between(d1,d2) 返回指定日期之间的月份间隔
select months_between(sysdate,to_date('2012-8-29','yyyy-mm-dd')) from dual
4）add_months(d,num) 返回给指定日期加上整数个月后的新日期
--查询入职时间和转正时间 [3个月转正]
select hiredate,add_months(hiredate,3) 转正时间 from emp;

5）last_day(d) 返回指定日期当月的最后1天
--查询每月最后1天入职的员工
select to_char(last_day(sysdate),'yyyymmdd') from dual;


6）extract() 返回指定日期的指定部分（年、月、日）
select extract(year from sysdate) 年 from dual;
select extract(month from sysdate) 月 from dual;
select extract(day from sysdate) 日 from dual;

--2、字符函数：字符串下标从1开始，0也表示第1位

--1）substr(str,index)/substr(str,index,length)
select substr('accp',2) from dual
--从第几个位子 开始 截取 几位
select substr('你好吗?',2,1) from dual
--lower upper (首字母大写怎么搞?)
select upper(substr(ename,1,1))||lower(substr(ename,2)) from emp;

--2）instr(str1,str2) 返回str1中str2出现的下标
select instr('accp@163.com','@') from dual
----'&xxx' 表示接收用户输入
select substr('&name',1,3) from dual;

--3）replace(str,olds,news)/replace(str,olds) 替换字符串
--把空格替换为*
select replace(' a c c p ',' ','*') from dual
--去掉了空格
select replace(' a c c p ',' ') from dual

--3、转换函数
--1）to_char(d)/to_char(d,fmt) 日期>>字符
--转默认格式的字符串
select to_char(sysdate) from dual;
--转指定格式的字符串：yyyy,mm,dd,hh/hh24,mi,ss,day
--fm 表示去掉日期前缀0
select to_char(sysdate,'day') from dual;
select to_char(sysdate,'yyyy/mm/dd') from dual;
select to_char(sysdate,'hh24:mi:ss') from dual;
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss day') from dual;
select to_char(sysdate,'fmyyyy"年"mm"月"dd"日" day') from dual;
--3）to_char(num,fmt) 数字>>字符，用于货币的显示
select ename,sal,
to_char(sal,'$9,999.99') 美元,
select to_char(3000.869,'L999,999,999.99') from  dual;
to_char(sal,'C9,999.99') 人民币,
to_char(sal,'L9,999.99') 本土 from emp;
六事务 ,随机数
在select 后面加for update 则实现查询锁定 除本用户
事例: select * from studentinfo for update
--随机数
select
round(dbms_random.value(1,9))
 from dual --

savepoint p3;
insert into emp(empno,ename) values(9999,'cat');
rollback to p3; --回滚到保存点 记得加 to
视图
--视图  隐藏敏感字段
create or replace view v_emp as
select emp.*,dept.dname from emp ,dept where emp.deptno=dept.deptno
with read only  --只读视图
索引
create unique index uqindex_ename on emp2(ename);//创建唯一索引
create index ind_ename on emp2(ename);
drop index uqindex_ename;--删除索引
--约束
--查询所有的约束
select * from user_constraints
where table_name='EMP2'

alter table emp2 add constraint uq_ename unique (ename)
alter table emp2 drop constraint uq_ename
--加了唯一约束 自动添加唯一索引
--组合索引
create index ind_ename on emp2(ename,job);
函数索引
create index hir_index
on emp2(to_char(hiredate,'yyyy-mm-dd'))

分区
create table my
(
    id number(12) primary key,
        mydate date --my 表的mydate列
)
partition by range (id)
(
    partition p1 values less than (10000)  ,
    partition p2 values less than (20000)  
)

select * from my partition(p2)


七.异常
1,普通异常
declare
    mysal emp.sal%type;
begin
    select sal into mysal from emp
    where empno='&empno';
    dbms_output.put_line('工资为:'||mysal);
    exception
        when no_data_found then
            dbms_output.put_line('没有这个员工');
end;
2.--抛出异常
declare
    mye exception;
begin
    if '&age'<18 then
        raise mye;--抛出异常
    else
        dbms_output.put_line('正常');
    end if;
    exception
        when mye then --捕获异常
            dbms_output.put_line('年龄太小');--处理
end;
--异常捕捉
declare
    i number(5):=1;
begin
  --     i:=i/0;
  select empno into i from emp where empno=11;
      dbms_output.put_line('aaa');
  exception
      when ZERO_DIVIDE then
         dbms_output.put_line('不能除0');    
      when TOO_MANY_ROWS then
          dbms_output.put_line('结果不能大于1');
      when NO_DATA_FOUND then
          dbms_output.put_line('没结果也不行');
      when others then
          dbms_output.put_line('出错了');
end;
--case when
declare
    score varchar2(50);
BEGIN
    score:='&score';
    CASE score
       WHEN 'A'  THEN
            dbms_output.put_line('优秀');
         WHEN 'B'  THEN
            dbms_output.put_line('一般');
       WHEN 'C'  THEN
            dbms_output.put_line('及格');
     ELSE
            dbms_output.put_line('没这个等级');
    END CASE;
END;
循环
--for循环
BEGIN
    FOR i IN  0..200 LOOP
        dbms_output.put_line(i);
    END LOOP;

END;
----
简单循环
declare
    i number(19):=0;
BEGIN
    loop  --循环开始
        i:=i+1;     --一定要有改变循环变量      
        dbms_output.put('a');
        dbms_output.put_line(i);

        exit WHEN i>=100;
    end loop; --循环结束
END;

--逻辑判断
declare
    age number(5);
BEGIN
    age:='&age';
    if age>20 and age<100 then
        dbms_output.put_line('成年人');
    elsif age>10  then
        dbms_output.put_line('年轻人');
    elsif age>1 then
        dbms_output.put_line('小孩');
    else
        dbms_output.put_line('宝宝');
    end if;
END;

变量
1,变量类型定义
 --行类型定义
declare  变量名 表名%rowtype;
列类型
declare  变量名 表名.列名%type;
例:declare
    myname emp.ename%type;
--定义变量 (一次只能定义一个)
--定义一个[记录]类型
declare

type Student is RECORD(
     name varchar2(50),
     sal number(12,2)     
);
--定义一个变量 stu 变量
 stu Student;
八.游标
--1,游标:是PL/SQL程序中用来获取查询结果集的[指针]
--2,有显示游标,隐士游标,ref 游标
--SQL%found [有] 影响的行数 true -false
--SQL%rowcount 影响的行数
begin
  update emp set sal=sal+10 where deptno=10;
  if SQL%found THEN
      dbms_output.put_line('更新了数据');
      dbms_output.put_line('影响了'||SQL%rowcount||'行');
  elsif sql%notfound then
      dbms_output.put_line('没有符合条件的记录');
  end if;
end;
rollback;
--显示游标 cursor
declare
    mname varchar2(50);
    ssal emp.sal%type;
    cursor c is select ename,sal from emp;
BEGIN
    open c;  --打开游标
    loop
    fetch c into mname,ssal; --下一行
    exit when c%notfound ;
    dbms_output.put_line(mname||'  '||ssal);
    end loop;
    close c; --关闭游标
END;

declare  
    cursor c is select * from emp;
    v c%rowtype;
BEGIN
    open c;
    fetch c into v; -- 下一行
    while c%found  --是否有记录
    loop
        dbms_output.put_line(v.ename||'  '||v.empno);
    fetch c into v; --下一行
    end loop;
    close c;
END;

--for 游标 `最简单
--自动打开游标  自动关闭游标  自动fetch
--自动定义变量
declare
    cursor c is select * from emp;
    BEGIN
        FOR v IN c LOOP
            dbms_output.put_line(v.empno||'   '||v.ename);
        END LOOP;
    END;
--带参数的游标
--根据部门编号输出该部门的员工姓名
--在open的时候 输入参数
declare
    cursor c(ptno emp.deptno%type)
    is select * from emp where deptno=ptno;
BEGIN
    FOR v IN c('&deptno') LOOP
dbms_output.put_line(v.deptno||'部门'||' '||v.ename);
    END LOOP;
END;

--带更新的游标
--可以对每一条数据进行 判断 然后做对应的修改
--nvl(arg0,agr1) 函数
--第一个参数如果为 null 就设置为第二个参数
declare
    cursor c is select * from emp  for update;--锁
    BEGIN
        FOR v IN c LOOP
            if v.deptno=10 then
                --current of c  当前游标所在行
                update emp set comm=nvl(comm,0)+100 where current of c;
                dbms_output.put_line('当前第'||c%rowcount||' 加了100');
            elsif v.deptno=20 then
                --current of c  当前游标所在行
                update emp set comm=nvl(comm,0)+200 where current of c;
                dbms_output.put_line('当前第'||c%rowcount||' 加了200');
            
          end if;
        END LOOP;
    END;

--ref 游标
--作用: 当sql语句确定不了的时候 使用(整个sql语句可以 变化)
--输入表名 来查数据
declare
    tname varchar(20);--表名
    type myc IS REF CURSOR;--定义引用游标类型
    c myc;-- 定义引用游标对象
    e emp%rowtype;
    d dept%rowtype;
    BEGIN
        tname:='&table';
        --for循环不跟 open 和close 一起用
        open c for 'select * from '||tname;
        loop
            if tname='emp' then
        fetch c into e;
        exit WHEN c%notfound ;
        dbms_output.put_line(e.ename ||'  '||e.sal);
            elsif tname='dept' then
                fetch c into d;
        exit WHEN c%notfound ;
        dbms_output.put_line(d.loc ||'  '||d.deptno);
            end if;
                      
        end loop;
        
    END;
--ddl 数据定义语言  drop     alter   create  
--dml 数据操作语言  增删改查

--动态SQL  在plsql 不能直接 使用ddl
--可以把一个任意字符串 做为语句来执行
BEGIN
    execute immediate
    'drop table myemp2';
END;

BEGIN
    execute immediate
    ' create table myemp (
         name varchar(50) primary key,
         age  number(9),
         bir date)';
END;
九.存储过程
--存储过程procedure

create or replace procedure proc1
as
begin
    dbms_output.put_line('这是一个过程');
end;
--带参数的存储过程

create or replace procedure proc2(eno varchar2)
is
  name emp.ename%type;
begin
   select ename into name from emp where empno=eno;
   dbms_output.put_line(eno||'  '||name);
end;

begin
    proc2('7499');
end;
--输出out参数
create or replace procedure proc2(eno out varchar2,e2 out varchar2)
is
begin
       eno:='欢迎进入sun';
       e2:='欢迎进入oracle';
end;
--
declare
   temp varchar2(50);
   temp2 varchar2(50);
begin
    proc2(temp,temp2);
    dbms_output.put_line('hello'||temp||temp2);
end;

--输入输出参数in out  参数
create or replace procedure proc3(myno in out varchar2)
as
--定义变量的区域
begin
   select ename into myno from emp where empno=myno;
end;

declare
   eno varchar2(50):='7369';
begin
   proc3(eno);
   dbms_output.put_line(eno);
end;
--存储过程里面 再调用 存储过程
--下面这条数据删除没了
create or replace procedure proc4
is
begin
    delete from emp where empno=7369;
    rollback;
end;

create or replace procedure proc5
is
begin
    delete from emp where empno=7499;
    proc4;
end;
--调用存储过程
begin
    proc5;
end;
--函数
create or replace function fun1
return varchar2
as
begin
      return '我爱你';
end;
--函数调用
select fun1() from dual;--

begin
     dbms_output.put_line(fun1());
end;
--给用户赋权限
grant execute  on proc1 to system;

--SQL> set serveroutput on; ---命令执行proc
--SQL> exec proc1;
--程序包
--先建立包头
create or replace package hr
is
 function fun2(money number) return number;
 procedure proc3(myno in out varchar2);
end hr;
--建立包体
create or replace package body hr
is
  function fun2(money number) return number
  as
 sal number(12,2);
     begin
       sal:=money-3500;
      if sal<=0 then
          return money;
     
      elsif sal<38000 and sal>=12000  then
          return money-(sal*0.25-975);
      end if;
    end fun2;
   procedure proc3(myno in out varchar2)
   as
   begin
      select ename into myno from emp where empno=myno;
   end;
end hr;

declare
eno varchar2(50):='7369';
begin
  --select hr.fun2(10000) from dual;
  hr.proc3(eno);
  dbms_output.put_line(eno);
end;

--参数为游标的存储过程
create or replace package mypack
as
 type c is ref cursor;  
 procedure proc(v out c);
end;

create or replace package body mypack
as
 procedure proc(v out c)
 as
 begin
   open v for 'select ename from emp';        
 end;
end mypack;
--调用!  
declare
   mc mypack.c;
   mycur emp.ename%type;
begin
    mypack.proc(mc);
    loop
     fetch mc into mycur;
           exit when mc%notfound;
     dbms_output.put_line(mycur);
    end loop;
end;


--触发器  java 调用存储过程
create or replace trigger sal_trigger
  after update of sal on emp  
  for each row --每行触发一次
  when (NEW.sal>OLD.sal) --再加一个触发条件(可以省略)
begin
  dbms_output.put_line(:new.ename||'加了'||(:NEW.sal-:OLD.sal));
end ;

update emp set sal=sal-100 where deptno=10;

--模拟自动增长
create sequence empsq
start with 8000
increment by 1
cache 20;
--
create or replace trigger emp_trigger
before insert on emp --添加的之前触发
for each row
begin

--模拟自动增长
create sequence empsq
start with 8000
increment by 1
cache 20;
--
create or replace trigger emp_trigger
before insert on emp --添加的之前触发
for each row
begin
--删除触发
-- 不允许删除 7369
create or replace trigger emp7_trigger
before delete on emp
for each row
when (OLD.empno=7369)
declare
 mye exception;
begin
   dbms_output.put_line('员工'||:old.ename||'不允许删除');
   raise mye;--抛出异常
end;

begin
delete from emp where empno=8001 ;
exception
when others then
     dbms_output.put_line('删除失败');
end;
--触发器应用
--日志记录

create table emplog
(
    uname varchar2(50),
    action varchar2(50),
    udate  date
)

create or replace trigger emp_log_trigger
after insert or update or delete on emp
for each row
declare
 str varchar2(50);
begin
    if inserting then
    str:='用户执行添加操作';
    elsif updating then
     str:='用户执行修改操作';
    elsif deleting then
      str:='用户执行删除操作';
    end if;
    insert into emplog values(user,str,sysdate);
end;

--定义个全局游标变量
create or replace package myhr
is
 type mye is ref cursor;
end myhr;

create or replace procedure proc6(c out myhr.mye,tab varchar2)
is
begin
    open c for 'select * from '||tab;
end;
----------------------在java代码里面调用存储过程
public static void main(String[] args) throws Exception {
        //返回带游标的存储过程
        BaseDAO db=new BaseDAO();
        Connection conn=db.getConnection();
        CallableStatement cstmt=conn.prepareCall("{call proc6(?,?)}");
        cstmt.setString(2, "emp");
        cstmt.registerOutParameter(1, OracleTypes.CURSOR);
        cstmt.execute();
        ResultSet rs=(ResultSet)cstmt.getObject(1);
        while(rs.next()){
            System.out.print(rs.getString(1)+" ");
            System.out.print(rs.getString(2)+" ");
            System.out.println(rs.getString(3)+" ");
        //    System.out.print(rs.getString(4)+" ");
            
        }
        rs.close();
        cstmt.close();
        conn.close();
    }
    
    
public static void main4(String[] args) throws Exception {
    //输入输出参数的 的过程
    BaseDAO db=new BaseDAO();
    Connection conn=db.getConnection();
    CallableStatement cstmt=conn.prepareCall("{call proc3(?)}");
    cstmt.setString(1, "7499");
    cstmt.registerOutParameter(1, java.sql.Types.VARCHAR);
    cstmt.execute();
    String s=cstmt.getString(1);
    System.out.println(s);
    cstmt.close();
    conn.close();
}
    
    
 public static void main3(String[] args) throws Exception {
    //输出参数的 的过程
        BaseDAO db=new BaseDAO();
        Connection conn=db.getConnection();
        CallableStatement cstmt=conn.prepareCall("{call proc2(?,?)}");
        //注册输出参数
        cstmt.registerOutParameter(1, java.sql.Types.VARCHAR);
        cstmt.registerOutParameter(2, java.sql.Types.VARCHAR);
        //执行
        cstmt.execute();
        String s1=cstmt.getString(1);
        String s2=cstmt.getString(2);
        System.out.println(s1+"      "+s2);
        
 }
    public static void main2(String[] args) throws Exception {
        BaseDAO db=new BaseDAO();
        
        Connection conn=db.getConnection();
        //调用存储过程
        CallableStatement cstmt=conn.prepareCall("{call proc1(?)}");
        cstmt.setString(1, "CAT");
        cstmt.execute();
        cstmt.close();
        conn.close();