www.icesr.com
IT运维工程师的摇篮

oracle笔记

1、Oracle默认账户、密码
        sys            change_on_install
        system     manger
        scott      tiger
    
2、Oracle服务说明
        OracleServiceORCL:数据库的服务,如果创建两个数据库就会有两个服务,ORCL是数据库名。
        OracleOraDb10g_home1TNSListener:监听服务,jdbc和plsql远程连接服务,端口号为:1521
        OracleOraDb10g_home1iSQL*Plus:Web管理服务,例如:http://127.0.0.1:5560/isqlplus,端口号为:5560
        注意事项:如果OracleOraDb10g_home1TNSListener启动出错,找到Net Configuration Assistant重新配置监听即可。
        默认启动:OracleServiceORCL 和 OracleOraDb10g_home1TNSListener 就足够了。
        
3、加锁、解锁用户(管理员 "sys" "system" 命令)
        加锁:alter user 用户 account lock;
        解锁:alter user 用户 account unlock;
    
4、登录命令、切换用户命令
        1. connect 用户/密码
        2. conn 用户/密码
    ========不想被别人看到明文密码情况下========
        3. SQL> conn
                请输入用户名:  scott
                输入口令: *****
        4. SQL> conn
                请输入用户名:  scott
                输入口令: *****
            
5、查询当前登录用户
        show user;            
            
6、查询、设置“行宽”和“显示条数”
        查询行宽:show linesize; //默认80
        设置行宽:set linesize 120;
        
        查询显示条数:show pagesize; //默认14
        设置显示条数:set pagesize 100;
        
        1.字符串指定列宽:column 列名 format a号;
                例如:column ENAME format a10;
        
        2.数字指定列宽:column 列名 format 9999;
                说明:一个9代表一个数字。
        
        1 2的缩写格式:
                col 列名 for 指令;
    =====================================================================        
                        例如我添加的内容如下:
                                         — 指定行宽
                                        set linesize 150
                                         — 指定每页显示条数
                                        set pagesize 100
                                         — 指定oracle默认显示时间
                                        alter session set nls_date_format=’yyyy-MM-dd’;
    =====================================================================    

        永久保存设置配置:找到oracle的安装目录,例如:D:/Software/oracle/product/10.2.0/db_1/sqlplus/admin/glogin.sql 设置写到最下面即可。
        
        
    
//测试是否是jdbc语句
        1、jdbc语句必须需要分号,否则会换行让你继续输入。
        2、使用/来测试,如果是jdbc输入完毕,再输入/如果成功执行上句的话,证明是jdbc语句。

//查询当前用户所有表
        select * from tab;

//查看表结构
        desc 表名;

//打开查询耗时
        set timing on;
//关闭查询耗时    
        set timing off;
        
//sqlplus(黑窗口)清屏,sqlplusw中无效
        Windows:host cls
        Linux中:host clear
        plsql中:clear

//多行输错,修改命令
        edit 或者 ed ,修改完输入 / ,文件中不要有问号
    
//处理null值函数(vnl函数)
        nvl(字段,值)
            例如:select nvl(comm,0) from emp;
        nvl2(字段,不为空返回值,为空返回值)
            例如:select nvl2(NULL,2,3) //返回 3
    
//别名大小写
        使用别名,如果不加 "" 默认大写。
        加了 "" 就会按 "" 里显示。
        as xx;  –转为:XX
        as "xx"; –等于:xx
        注意:当别名含有 "空格"、 "字符串" 时一定要加 "" 号;
        
//取消重复行关键字:(distinct)
        select distinct 重复字段 from 表名;
        注意:如果查询多列,distinct作用于多列,多列有重复才算重复。
        
//拼接字符串   –查询格式:ENAME的工资是:SAL
        select ename || ‘的工资是:’ || sal from emp;
        
//拼接字符串((oncat)函数
        select concat(‘我叫 ‘,’小童鞋_成er’) from dual;
        拼接多个:select concat(concat(‘我叫 ‘,’小童鞋_成er’),’ 啊!’) from dual;
            
//oracle的虚表,oracle查询必须指定form关键字
        select ‘Hello ‘ || ‘World’ from dual;
        dual是一个虚拟表:
            比如 得到当前时间:select sysdate from dual;
                 计算数值:    select 3 + 2 from dual;
                 
//关于(like)查询特殊字符用法:
                                                        ID NAME
                                                        — ———-
                                                         1 dd%
                                                         2 %xx
        1、我们进行查询,select * from 表名 where 字段 like ‘%%’; 那么就是查询所有了。
        //定义转移字符:select * from 表名 where 字段 like ‘/%%’ escape ‘/’;    –把/%转义,escpae指定哪个是转义符。
        比如:select * from 表名 where 字段 like ‘x%%’ escape ‘x’; //把x转义
        
        2、查询第三个字母为I的:
            select * from 表名 where 字段 like ‘__I’;
        
        
//查询~到~范围(between)
        比如查询工资1500~6000之间的。
        select * from 表名 where (工资字段 between 1500 and 6000);
        
        
//查询id为1 2 3 4的用户 (in查询)
        第一种写法:select * from 表名 where ID字段 = 1 or ID字段 = 2 or ID字段 = 3 or ID字段 = 4;
        批处理写法:select * from 表名 where ID字段 in(1,2,3,4);
        注意:
                select * from 表名 where 字段 in(xx,xx,null); //没有影响
                select * from 表名 where 字段 not in(xx,xx,null); //如果not in 含有null,则不返回任何结果。
        
        
//字符串转为日期(to_date)
        to_date(‘2014-10-02′,’yyyy-MM-dd’); //年-月-日
        to_date(‘2014-10-02 12:00:02′,’yyyy-MM-dd HH24:mi:ss’); //年-月-日-时-分-秒
        
        
//日期转为指定格式(to_char)
        to_char(日期字段,’yyyy-MM-dd’); //年-月-日
        to_char(日期字段,’yyyy-MM-dd HH24:mi:ss’); //年-月-日-时-分-秒
        

//查询Oracle指定的格式(v$nls_parameters)
        select * from v$nls_parameters; //只能查看,不能修改
        
//修改当前会话的格式    默认时间格式:DD-MON-YY
        alter session set nls_date_format=’yyyy-MM-dd’; //只对当前会话有效
        
//排序 (order by)
        升序:
                select * from 表名 order by 要排序字段 asc; //asc默认也可不写,从高到底
        降序:
                select * from 表名 order by 要排序字段 desc; //降序,从低到高
                
        NULL值排序始终在下面:
                select * from 表名 order by 含有NULL字段 desc nulls last;
        NULL值排序始终在上面:
                select * from 表名 order by 含有NULL字段  desc nulls first;
                select * from 表名 order by nvl(含有NULL字段,-1) asc;
        根据字段位置排序:
                select id,name from xx order by 2 desc; //2是name的位置
                
//分组
        分组函数:
                    max(字段); //最大值
                    min(字段); //最小值
                    avg(字段); //平均值
                    count(字段); //总条数
                    sum(字段); //总和
        分组方法(group by):
                select max(sal),deptno from emp group by deptno; //查询每个部门工资最高的人,如果group by中没有这个字段,就不能显示这个字段
        对两个字段进行排序:
                select max(sal),deptno,job from emp group by deptno,job; //根据“部门(deptno)”和“工作(job)”进行排序。
        对分组条件进行筛选(group by 字段 having 条件),同时进行排序(order by):
                select deptno,count(*) from emp group by deptno having count(*)>4 order by deptno desc;
        
        注意:
                    group by 和 having 都不可以使用“别名”;
                    order by 可以使用“别名”;
                    
                    某些情况下,优先使用where,而不使用having。
                      例子,查询“部门”,“部门人数”,取消10号部门信息:
                              效率高:select deptno,count(*) from emp where deptno <> 10 group by deptno;
                              效率低:select deptno,count(*) from emp group by deptno having deptno <> 10;
                              
//字符函数
        LOWER(String) //将字段转换为“小写”;
                select LOWER(‘ABC’) from dual; — 结果:abc
                
        UPPER(String) //将字段转换为“大写”;
                select LOWER(‘abc’) from dual; — 结果:ABC
                
        INITCAP(String) //每个单词首字母转为“大写”;/
                select INITCAP(‘hello word.day’) from dual; — 结果:Hello Word.Day
      
      — 首写字母为大写
          select initcap(ename) from emp;
        
//拼接字符串
        CONCAT(‘a’,’b’); //跟||一样
        
//截取函数:
    SUBSTR(String,index,index) //要截取的字符串,从第几个开始,此位置往后截取几个  (从1下标开始)
        select SUBSTR(‘abc’,1,2) from dual; — 结果:ab
        
    USBSTR(String,index) //如果不指定,就从1位置,截取到字符串结束
        select SUBSTR(‘abc’,2) from dual; — 结果:bc

//字段长度
        LENGTH(String) //返回字段长度
            select LENGTH(‘abc’) from dual; — 结果:3
            
//查找字符串位置
        INSTR(String,String) //返回字符串位置下标
            select INSTR(‘abcdefg’,’c’) from dual; — 结果:3
            
//补齐函数LPAD(String,indexOf,char)和RPAD(String,indexOf,char)
        LPAD:
                select lpad(‘abc’,5,’*’) from dual; — 结果:**abc
        RPAD:
                select rpad(‘abc’,5,’*’) from dual; — 结果:abc**
                
        //可以匿名,比如:
                select ename as 姓名,substr(ename,1,1)|| replace(rpad(‘ ‘,length(ename)-1,’*’),’ ‘,”) ||substr(ename,length(ename)) as 匿名 from emp where ename = ‘SCOTT’;
                姓名      匿名
                —————
                SCOTT     S***T
                
//去掉前后空格TRIM(‘ ‘ from String)
        TRIM(String) //去掉前后空格
        TRIM(‘a’ from ‘abc’) //去掉a,注意:只能为前、后替换
        
        
//替换函数replace(String,String,String)
        select * from replace(‘abc’,’a’,’1′) from dual; — 结果:1bc
        

//数学函数:
        MOD(number,nunber)  //求余数
        ROUND(number) //四舍五入
        ROUND(number,number); //截取小数点第几位,比如:12.91 结果:12 没有小数; 如果是-1,比如:12.22,-1 结果:10
        
        TRUNC(number)
                                        跟ROUND() 函数一样,只是不四舍五入
        TRUNC(number,number)
        

//操作时间
        sysdate-1 :减一天
        sysdate-1/24 :减一天1小时
        sysdate-1/24/24 :减一天1小时1分钟
        
        last_day(date) : 获取当月最后一天
            比如:select to_char(last_day(to_date(’11’,’mm’)),’yyyy-mm-dd’) from dual; //返回:2014-11-30
            
        add_months(date,number) :当前日期减一天 或 加一天
            比如:select to_char(add_months(last_day(sysdate),-1)+1,’yyyy-mm-dd’) from dual; //返回当月的1号
            
        months_between :计算两个时间相差“月份”
            比如;select months_between(sysdate,to_date(‘2014-12-19′,’yyyy-mm-dd’)) from dual; //返回-1
            
        round(date,’xx’):///对日期四舍五入,比如:round(to_date(‘2014-10-10 22:00:00′,’yyyy-mm-dd hh24:mi:ss’),’dd’) 返回:2014-10-11 00:00:00
        trunct(date,’xx’):///对日期进行截断,比如:trunc(to_date(‘2014-10-10 22:00:00′,’yyyy-mm-dd hh24:mi:ss’),’month’)) 返回:2014-10-01 00:00:00
            上面两个函数指定xx:yyyy  month  dd  hh24  mi  ss
            
        //根据“星期几”获取下个“星期几”的日期
        next_day(sysdate,’星期三’);
        
        
//to_char(number,’xx’) 转换数字
        to_char(sal,’L9999′) : 转换为本地数字格式,9999表示显示位数
                                 $999,9999.00 :
                                 0000 :0补齐

//字符串转数字
        to_number(‘3’);
        
        
//判断,类似switch case case
        CASE 字段
            WHEN xx — 如果是 xx值
            THEN xx — 替换成 xx值
        ELSE    –否则
            xx    –返回 xx值
        END
        
————————————————        
        
        CASE 字段
            WHEN xx — 如果是 xx值
            THEN xx — 替换成 xx值
            
            WHEN xx — 如果是 xx值
            THEN xx — 替换成 xx值
            
        ELSE    –否则
            xx    –返回 xx值
        END
        
        
//判读,类似if else
        case
            when xx=xx
            then Yes — 是
            else No  — 不是
        end
        
————————————————    

//判断(oracle独有)  decode函数
        select
            decode(ename,’是SCOTT’,’Yes’,’不是SCOTT’,’No’,’都不是’)
        from emp
            where ename = upper(‘scott’);
            

//集合操作
        union :取并集,比如:A集合有1 3,B集合有1 4;并集结果:1 3 4
                select * from emp where ename in(‘SCOTT’,’CLARK’) union select * from emp where ename in(‘SMITH’,’ALLEN’,’SCOTT’);
                
        union all :取合集,比如:A集合有1 3,B集合有 1 4;合集结果:1 1 3 4
                select * from emp where ename in(‘SCOTT’,’CLARK’) union all select * from emp where ename in(‘SMITH’,’ALLEN’,’SCOTT’);
                
        intersect :取交集,比如:A集合有1 3,B集合有1 4;交集结果:1
                select * from emp where ename in(‘SCOTT’,’CLARK’) intersect select * from emp where ename in(‘SMITH’,’ALLEN’,’SCOTT’);
                
        minus :取差集,比如:A集合有1 3 ,B集合有1 4;差集结果:3
                例如:select ename from emp where ename in(‘SCOTT’,’ALLEN’) minus select ename from emp where ename in(‘SCOTT’,’ALLEN’,’SMITH’);
                    结果:“无结果”
                例如:select ename from emp where ename in(‘SCOTT’,’ALLEN’,’SMITH’) minus select ename from emp where ename in(‘SCOTT’,’ALLEN’);
                    结果:SMITH
              总结:以“第一个”集合为中心,取两结果的相差
    
     /*
       注意事项:
               1、如果是两个查询设计分组;
               2、order by必须在最后一个集合,group by无限制
               3、集合数量必须一样,类型必须一样,其它字段名字不同可以。
     */
    

//左连接、右连接
    — 左连接:
        /*Oracle专用:*/ select d.deptno as "部门编号",d.dname as "部门名称",count(e.deptno) as "总人数" from emp e,dept d where d.deptno=e.deptno(+) group by d.deptno,d.dname order by count(e.deptno);
    /*通用:*/   SELECT d.deptno AS "部门编号",d.dname AS "部门名称",COUNT(e.deptno) AS "部门总人数" FROM emp e LEFT JOIN dept d ON e.deptno=d.deptno GROUP BY d.deptno,d.dname ORDER BY 部门总人数
 
  — 右连接:
      /*Oracle专用:*/ select d.deptno as "部门编号",d.dname as "部门名称",count(e.deptno) as "总人数" from emp e,dept d where d.deptno=e.deptno(+) group by d.deptno,d.dname order by count(e.deptno);
      /*通用:*/   SELECT d.deptno AS "部门编号",d.dname AS "部门名称",COUNT(e.deptno) AS "部门总人数" FROM emp e RIGHT JOIN dept d ON e.deptno=d.deptno GROUP BY d.deptno,d.dname ORDER BY 部门总人数
    
    
//单表“自连接”
    /*方法1:*/  select a.ename,b.ename from emp a, emp b where a.mgr=b.empno;
    /*内链接通用:*/ SELECT a.ename,b.ename FROM emp a INNER JOIN emp b ON a.mgr=b.empno; //inner 可以省略不写
    

//满外连接
        /*mysql不能用:*/  SELECT e.ename,e.deptno,d.dname,d.deptno FROM emp e FULL JOIN dept d ON e.deptno = d.deptno;
        

//返回笛卡尔集
    SELECT e.ename,e.deptno,d.dname,d.deptno FROM emp e CROSS JOIN dept d;
    
    
==============================================Oracle分页==============================================================
//第一页
    select * from (select rownum rn,a.* from (select * from emp) a where rownum <=10) where rn >=1;
//第二页
    select * from (select rownum rn,a.* from (select * from emp) a where rownum <=20) where rn >=11;

==============================================DML操作==============================================================

//临时表创建
        全部字段:create table 新表名 as select * from 表名;
        部分字段:create table 新表名 as select 字段1,字段2,字段3 from 表名;
        
        
//制定列插入数据
        insert into 表名(字段1,字段2) select 字段1,字段2 from 表名;
        

//删除表结构,不经过回收站
        drop table 表名 purge;
        

//关闭Oracle反馈提示
        set feedback off;
        
        
//delete和truncate删除的区别:
    delete table 表名:可恢复,删除速度做了优化,快。
    truncate table 表名:不可恢复,删除速度慢。
    
    
================================保存屏幕到文本=================================
spool D:/1.txt; //保存路径
select * from emp;
spool off; //关闭保存

================================保存点=========================================
savepoint 保存点名称; //建立保存点
rollback to 保存点名称; //回滚指定名称的保存点
rollback; //回滚全部
        
        
================================数据闪回=======================================
//启用表闪回
    alter table 表名 enable row movement;
//闪回到指定时间
    flashback table 表名 to timestamp to_timestamp(‘2014-12-03 15:04:40′,’yyyy-mm-dd HH24:mi:ss’);
    
    /*注意:delete可以恢复,truncate不可恢复。*/

====================|============数据类型===================|==================
                    |      数据类型     |           描述         |
                    |—————-|———————-|
                    | varchar2(size) |    可变长字符数据。  |
                    |—————-|———————-|
                    |      char(size)  |    定长字符数据。    |
                    |—————-|———————-|
                    |number(si~[.xx])|   可变长数值数据。   |
                    |—————-|———————-|
                    |        date     |            日期型数据。|
                    |—————-|———————-|
                    |    long         |可变长字符数据,最大2G |
                    |—————-|———————-|
                    |    clob         |   字符数据,最大4G。 |
                    |—————-|———————-|
                    |        blob     |  二进制数据,最大4G。|
                    |—————-|———————-|
                    |    bfile       |存储外置文件二进制,4G |
                    —————————————–
                                     
                                     
                                     
 ================================数据库创建、约束=================================
 create table stu(
     id number
     constraint PK_STU_ID //PK_STU_ID 是约束的名字
     primary key, //设置主键
     name varchar2(20) check( length(name) > 2), //长度要大于2个字符
     gender char(2) check( gender in(‘男’,’女’) ), //值只能为“男”或“女”
     address varchar2(30) not null, //不能为NULL
     birthady date default sysdate //默认当前时间
 );
— 约束:
    1、主键约束:constraint PK_STU_ID primary key //唯一,不能为NULL,不可重复
    2、不能为NULL约束:constraint NOTNULL_STU_NAME not null //不能为NULL
    3、只能是唯一约束:constraint UNIQUE_STU_XX unique //唯一约束,例如身份证一对一
    4、数字约束:constraint CHECK_STU_AGE check( age > 0 and age <= 100) //年龄值必须是大于0,小于100
    5、规定约束:constraint CHECK_STU_SEX check( sex in(‘男’,’女’) ) //必须是“男”或“女”
    6、外键约束:constraint FK_STU_DEPTNO references 外键表(外键表字段) //外键约束

//查看约束字典
    select * from user_constraints;
//查询指定表约束
    select * from user_constraints where table_name=’表名’;
    
    
===========================表字段操作=============================
//增加列
    alter table 表名 add(列名 xx);

//修改列
    alter table 表名 modify(age number check(age>2));

//修改列名
    alter table 表名 rename column 旧列名 to 新列名;
    
//删除列
    alter table 表名 drop column 列名;
    
//给表修改名字
    rename 旧表名 to 新表名;
    
//给列字段添加备注
    comment on column 表名.列名 is ‘这是备注信息’;
    /*mysql*/
        alter table emp modify 表名 列类型 comment ‘这是备注信息’;

//给表增加注释
    comment on table 表名 is ‘这是给表增加注释’;

===========================视图=============================
//赋予创建视图的权限
    grant create view to 用户名;

//查询VIEW
    select * from tab where tabtype=’VIEW’;
    
/**
    * 视图的作用
**/
    1、屏蔽掉DML(增、删、改)操作,利用别名。
    2、视图不可以提高查询性能。
    3、简化复杂的查询。
    4、限制数据的访问。

//创建视图语法,默认如果不指定 就可以对视图进行DML操作的
    create view 视图名称
        as
            查询sql
    ;
    
//创建只读视图
    create view 视图名称
        as
            查询sql
        with read only
    ;

=========================================================================================
注意:mysql、sql server都不支持with read only,使用下列可以达到效果

  create view my_view
        as
            select empno,ename,deptno from emp where deptno = 10
      union
            select 0,’0′,0 from emp where 1=0
    ;
=========================================================================================

    
//替换现有视图
    create or replace view 旧视图名称
        as
            ….
    ;

//只允许选择条件插入视图
    create view 视图名称
        as
            select empno,ename,sal,deptno from emp where deptno = 10
        with check option — 只能插入 deptno=10 的
    ;
    /*
        where deptno in(10,20)
        with check option  — 只能插入 10 和 20 的数据
    */
    
    
===========================序列=============================
//创建一个序列
    create sequence 序列名;

//修改一个完整序列
    alter sequence myxl
      increment by 1 //每次递增数(可不写)
      minvalue 1 //最小值为1(可不写)
      start with 2 //值从哪个数开始(可不写)
      maxvalue 10 //最大值(可不写)
      //nomaxvalue 没有最大值
      nocache //不使用缓存,(可不写,默认大小20)
      nocycle; //不循环(可不写),循环是cycle

//查询当前序列值
    select 序列名.currval from dual;

//增加一个序列值
        select 序列名.nextval from dual;

 注意:如果首次创建序列没有调用,查询当前序列值 会报:ORA-08002: 序列 MYXL.CURRVAL 尚未在此会话中定义
 
//删除序列
    drop sequence 序列名;

//查询当前用户有哪些序列
    select * from user_sequences;
    
    
===========================索引=============================    
//创建索引
    create index 序列名 on 表名(字段名);

//查看当前用户有哪些序列    
    select * from user_indexes;
    
//删除序列
    drop index 序列名;

/*
    LIKE 查询不会用到索引
*/
    
    
===========================同义词=============================
/*
    从字面上理解就是别名的意思,和视图的功能类似。就是一种映射关系。
*/

//创建同义词
    create synonym 同义词名 for 表名;
    
//删除同义词
    drop synonym 同义词名;
    
//查看当前用户有哪些同义词
    select * from user_synonyms;
    
/* 知识扩展:数据库对象
     表、视图、序列、过程、函数、程序包,甚至其它同义词都可以创建同义词。
*/
     
     
============================存储程序(控制台)=====================================
//显示存储过程脚本输出     
    set serveroutput on;
——————————————————————————
//存储过程实例                                                                                                                            
    declare
        — 变量
         //定义一个变量
                 变量名 number(3);
         //定义一个变量,并赋默认值
                 变量名 varchar2(20):= NULL;
         //定义一个,使用“表.列”类型
                 变量名 表名.列名%type;
         //定义一个容器,相当于List,使用表全部字段
                 变量名 表名%rowtype;
    begin
        — sql语句
            //赋值一个变量
                select ename into 变量名 from emp where empno=7839;
            //赋值两个或多个变量
                select ename,sal into 变量名,变量名 from emp where empno=7839;
            //赋值容器,表全部字段
                select * into 变量名 from emp where empno=7839;
            
            //输出语句
                dbms_output.put_line(变量名 || ‘值为:’ || 变量名);
    end;
——————————————————————————

//友好提示输入窗口(命令)
    accept &提示输入的名字 prompt ‘请输入员工编码:’; //就是 &empno
    select ename into 变量名 from emp where empno=&empno;

//判断============================================================
    — 方式一– —
        /*
            if 条件
                then 语句;
            end if;
        */

    — — 方式二 — —
        /*
            if 条件
                then 语句;
            else
                语句;
            end if;
        */
    
    — — 方式三 — —
        /*
            if 条件
                then 语句;
            elsif
                then 语句;
            else
                语句;
            end if;
        */
            
//判断示例
    set serveroutput on; //显示存储过程脚本输出
    accept vsex prompt ‘请输入您的性别:’; //友好提示输入窗口(命令)
    declare
        vsex char(2); //定义变量,存储性别
        vstr varchar2(20);    //定义变量,打印字符串
      begin
        vsex := ‘&vsex’; //使用输入窗口命令赋值
        if vsex = ‘男’
          then vstr:=’先生,欢迎您!’; //如果是男就把vstr字符串赋值为:先生,欢迎您!
        elsif vsex = ‘女’
          then vstr:=’女士,欢迎您!’; //…或者
        else
          vstr:=’对不起,请登录你的性别!’; //…否则
        end if; //关闭判断
        dbms_output.put_line(vstr); //打印内容
    end;

//循环============================================================

    /*
        for 循环
    */
    set serveroutput on;
    declare
    begin
      for i in 1..5
        loop
          dbms_output.put_line(i);
        end loop;
    end;
    
    /*
        loop 循环
    */
    set serveroutput on;
    declare
        vnum number(2):=1;
      begin
        loop //定义loop
          exit when vnum > 10; //当满足这个添加退出循环
            dbms_output.put_line(vnum);
            vnum := vnum + 1;
          end loop; //结束loop
    end;

    /*
        while 循环
    */
    set serveroutput on;
    declare
      vnum number(2):=1;
      begin
        while vnum <= 10 //此处不要分号
        loop
          dbms_output.put_line(vnum);
          vnum:=vnum+1;
        end loop;
    end;

============================光标(游标)=====================================
//小例子
set serveroutput on;
declare
  — 1、定义光标(所有员工的集合)
  cursor c_emp is select ename,job from emp;
  — 7、定义变量,存放ename 和 job
  vename emp.ename%type;
  vjob emp.job%type;
 
begin
  — 2、打开光标
  open c_emp;
  — 4、循环集合(光标)
  loop
    — 6、获取数据
    fetch c_emp into vename,vjob;
    — 8、判断集合循环(光标)完退出循环
    exit when c_emp%notfound;
    //最后输出
    //dbms_output.put_line(vename||’的工作是:’||vjob);
    /*  — 修改语法
        if vename = ‘SCOTT’
      then
        update emp set ename=’修改后的名字’ where ename=vename;
        commit;
        end if;  
    */
  — 5、关闭循环
  end loop;
  — 3、关闭光标
  close c_emp;
end;

异常类型:
    ZERO_DIVIDE:        除数为0异常。
    NO_DATA_FOUND:    未找到数据异常。

//预定义除数为0异常
    set serveroutput on;
    declare
      vnum number(1):=2;
    begin
      vnum := vnum/0;
      raise ZERO_DIVIDE; — oracle 预定义异常:除数为0异常
      dbms_output.put_line(‘发生异常不走这里!’);
      exception
        — 发生异常处理
        when ZERO_DIVIDE then dbms_output.put_line(‘自定义除0异常!’);
    end;
    
    /*自定义异常*/
    set serveroutput on;
    declare
      vnum number(1):=1;
      my_exception exception;
    begin
      if vnum is null
        then dbms_output.put_line(‘空啦!’);
      elsif vnum is not null
        — then dbms_output.put_line(‘不为空啦!’);
        then
          raise my_exception; — 抛出异常
      end if;
      exception
        when my_exception then dbms_output.put_line(‘发生异常啦,兄弟!’);
    end;
    
    //返回其他异常
        exception
            when others
          then  xxxx;
    
============================存储过程=====================================
//创建存储过程过程
    create procedure 存储过程名字
    as
        //变量
      vnum number:=3;
  begin
      dbms_output.put_line(vnum);
  end;

  //修改为 create or replace procedure 存储过程名字

//带参数的存储过程    注意:如果传入的是varchar2类型,这样:vename varchar2 不能 varchar2(10)
    create or replace
        — 员工序号,涨多少工资
        procedure addSal(vempno in number,vmoney in number)
    as
      vename emp.ename%type;
      vsal emp.sal%type;
    begin
      update stu set sal=sal+vmoney where empno = vempno;
      commit;
      select ename,sal into vename,vsal from stu where empno=vempno;
      dbms_output.put_line(‘姓名:’ || vename || ‘ 涨了 ‘ || vmoney || ‘ 工资!’);
      dbms_output.put_line(‘月工资为:’ || vsal);
    end;
 
  /*
      执行方式一、
          set serveroutput on;
          execute 存储过程名字();
  */
 
  /*
      执行方式二、
          set serveroutput on;
          begin
              存储过程名字();
        end;
        /
  */
    
//查看用户存储过程
    select * from user_procedures;

//删除存储过程
    drop procedure 存储过程名字;
    

============================存储函数(带有返回值)=====================================
//创建存储函数
    create or replace function My_Function(vempno number)
        return varchar2 //存储函数返回值类型
    as
      vename stu.ENAME%type;
    begin
    select ename into vename from stu where empno=vempno;
    return vename; //返回
    end;
    
        /*执行存储函数*/
            select My_Function(参数) from dual; //也可用于插入字段
    
//查看用户存储函数
    select * from user_procedures;

//删除存储函数
drop function 存储函数名称;

 //结论:
         什么时候用存储过程、什么时候用存储函数?
             答:一般返回值有一个,用“存储函数”;多个就用“存储过程”!
             

//存储过程跟存储函数,都可以使用out输出变量
    create or replace
    — out 输出变量:会生成注释输入,取消掉注释,测试输出
    function My_Function(vempno number,vsal out number, vcomm out varchar2)
    return varchar2
    as
        — 输出不了,没有指定out
         vename stu.ENAME%type;
    begin
        select ename,sal,comm into vename,vsal,vcomm from stu where empno=vempno;
        — 只能手动打印
        dbms_output.put_line(vename);
        return NULL;
    end;

//存储过程使用out参数
    create or replace
    procedure My_Procedure(name in varchar2,vename out varchar2)
    as
     vsal stu.sal%type;
    begin
      select ename,sal into vename,vsal from stu where ename=name;
    end;
    
    

============================Oracle回收站(ORACLE 10G之后特性) 普通用户,管理员没有回收站(慎重删除)=====================================
//查看回收站
    select * from user_recyclebin;

//从回收站中撤回删除的表
    flashback table 表名 to before drop; //表名是ORIGINAL_NAME字段
    
//从回收站撤回删除表时重命名
    flashback table 表名 to before drop rename to 新表名;

//根据回收站名字闪回表
    flashback table "BIN$XX文字" to before drop;

//清空回收站指定表
    purge table 回收站表名; //也就是ORIGINAL_NAME

//清空回收站
    purge recyclebin;
    

==========================(for update 和 for update wait)区别============================
/**
*  for update 操作没有提交时,如有另一线程操作,会进行等待…
*  for update nowait 操作没有提交时,另一线程操作时,会弹出:ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源
**/

==========================JDBC调用“存储过程”和“存储函数”============================
//JDBC调用存储函数
    create or replace
        procedure My_Procedure(p_empno in number,r_ename out varchar2,r_sal out number)
    as
    begin
      select ename,sal into r_ename,r_sal from emp where empno=p_empno;
    end;
—————————————————————————————————————————–
    Class.forName("oracle.jdbc.OracleDriver");
    Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "tiger");
    //第一个问号是in 参数,后两个是out
    CallableStatement call = conn.prepareCall("{call My_Procedure(?,?,?)}");
    // 赋值?为7839
    call.setObject(1, 7839);
    // 指定返回值类型
    call.registerOutParameter(2, OracleTypes.VARCHAR);
    // 指定返回值类型
    call.registerOutParameter(3, OracleTypes.NUMBER);
    // 执行查询
    call.executeQuery();
    //打印返回参数,从?号开始索引
    System.out.println(call.getString(2));
    //打印返回参数,从?号开始索引
    System.out.println(call.getString(3));
    //关闭
    call.close();
    conn.close();
    
    
    
//JDBC调用存储函数
    create or replace
        function My_Function(p_empno in number)
    return varchar2
    as
      v_ename emp.ename%type;
    begin
      select ename into v_ename from emp where empno=p_empno;
      return v_ename;
    end;
    
—————————————————————————————————————————–
    Class.forName("oracle.jdbc.OracleDriver");
    Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "tiger");
    //第一个问号是返回值,(?)才是参数
    CallableStatement call = conn.prepareCall("{?= call My_Function(?)}");
    // 赋值第二个?为7839
    call.setObject(2, 7839);
    // 指定返回值类型?=是返回值
    call.registerOutParameter(1, OracleTypes.VARCHAR);
    // 执行查询
    call.executeQuery();
    //打印返回参数,从?号开始索引
    System.out.println(call.getString(1));
    //关闭
    call.close();
    conn.close();
    
/*
    注意:function只能有一个返回值,如果有多个,用存储过程
*/

==========================数据字典============================
//数据字典总表
    select * from dictionary;

/*
    数据字典表前缀说明
*/
        前缀            说明
      user   用户自己的。
      all         用户可以访问到的。
      dba         管理员视图。
      v$     性能或者参数设置相关的数据。

//查询当前用户对象(表、视图、索引、序列、存储等)
select * from user_objects;

//查询所有用户对象(……)
select * from all_objects;

//表与列的信息
    desc[ribe] user_tables;

//列的信息
    desc user_tab_columns;

//当前用户表上的约束
    select * from user_constraints;

//当前用户创建的约束
    select * from user_cons_columns;
    
//当前用户视图信息
    desc user_views;

//查看当前用户权限
    select * from session_privs;

//查看数据库中所有的系统权限信息(需要dba权限)
    select * from dba_sys_privs;

//查看数据库所有的对象权限信息(需要dba权限)
select * from dba_tab_privs;

==========================触发器============================
/*
    触发器是一个与表关联的、存储pl/sql程序
    insert、update、delete
    
    分类:
            语句级:
                在指定的操作语句操作之前或之后执行一次,不管这条语句影响了多少行。
            
            行级触发器(for eachrow):
                触发语句作用的每一条记录都被触发,在行级触发器中使用old和new伪记录变量,识别值的状态。
    
    触发器作用:
        1、数据确认。
        2、实施复杂的安全检查。
        3、做审计、跟踪表上所做的数据操作等。
        4、数据的备份和同步。
        
    定义错误规则:
        在触发器中阻止SQL继续执行的方法:
            抛一个异常:
                raise_application_error(-20001,’异常内容’);
                异常代码应在-20000~-20999之间(包含,否则提示:“错误号参数超出范围”)
*/

//语法
    create [or replace] trigger 触发器名
        //之前,之后
        {before | after}
        //指定操作
        {delete | insert | update [of 列名]}
        //指定表
        on 表名
        //加上代表行级触发器
        [for each row [when(条件)]]
        //plsql代码块
        declare
            — 变量
        as
            — 代码
        end;

//创建一个触发器,不是8点到18点工作日,不能插入数据
    create or replace trigger My_Trigger
    before — 指定插入数据之前
      insert — 指定操作触发器DML
    on
      stu — 表名
    declare
      — 变量
    begin
      if not (to_number(to_char(sysdate,’hh24′)) between 8 and 18)
          — 错误代码必须在-20000~-20999之间(包含,否则提示:“错误号参数超出范围”)
        then raise_application_error(-20000,’现在是非工作时间,不能插入数据!’);
      end if;
    end;

//创建一个行级触发器,涨工资不能越涨越少
    create or replace trigger My_Trigger2
    after — 插入之后
      update
    on
      stu
    for each row — 行触发器
    declare
      — 变量
    begin
      — :new 新值 <  :old 旧值
      if :new.sal < :old.sal
        then raise_application_error(-20000,’工资不能越涨越少呀!’);
      end if;
    end;
    
    SQL:update stu set sal=sal-1 where empno=7839

//查询触发器
    select * from user_triggers;
    
//删除触发器
    drop trigger 触发器名;
    
    
/*
    主键自动增长 –> 触发器
*/
    — 创建触发器
    create sequence My_Sequence;
    
    create or replace trigger My_Sequence
    before
      insert
    on
      stu
    for each row
    declare
      –变量
    begin
      select My_Sequence.nextVal into :new.empno from dual;
    end;
    
    
    
==========================Oracle备份============================
//选择表备份,生成日志
    /*
        file : 数据库备份文件
        log  : 控制台导出内容
        tables : 指定表,多个表,隔开
    */
    exp scott/tiger@127.0.0.1:1521/orcl file=D:/xx.dmp log=D:/xx.log tables=emp,dept;
    
//导出全部
    exp scott/tiger@127.0.0.1:1521/orcl file=D:/xx.dmp log=D:/xx.log;

//导入
    imp scott/tiger@127.0.0.1:1521/orcl file=D:/xx.dmp log=D:/imp.log;
    

==========================用户管理============================
//创建一个用户(dba权限)
    create user 用户名 identified by 密码(必须字母开头);

//赋予create session权限
    grant CREATE SESSION  to 用户;

//用户解锁与锁定
    alter user xiaoming account unlock; — 解锁
    alter user xiaoming account lock; — 锁定

//给用户修改密码
    alter user 用户名 identified by 新密码;
    
//删除用户
    drop user 用户名;
    drop user 用户名 cascade; — 级联删除用户(诛杀九族)
    
//用户授权
    grant 权限1,权限2,… to 用户;

//回收权限
    revoke 权限1,权限2,… from 用户;
    
—————————————–
 
 赋予表空间使用权限:
         — quota 容量, unlimited 无限制, on users 在users这个表空间上没有限制
     alter user 用户名 quota unlimited on users;
 
 
         create session  //登录“数据库”权限
         create table    //创建“表”权限
         create view     //创建“视图”权限
         create sequence //创建“序列”权限
         create procedure //创建“存储”权限
         create trigger  //创建“触发器”权限
         create synonym  //创建“同义词”权限

未经允许不得转载:冰点网络 » oracle笔记

分享到:更多 ()

评论 抢沙发

评论前必须登录!