oracle

oracle基本概念    连接oracle数据库有两种方式        使用jdbc,需要jar包,连接url:jdbc:oracle:thin:@localhost:1521:数据库名,图形化工具:SQL Developer        安装客户机,连接url:jdbc:oracle:oci:@localhost:1521:数据库名,图形化工具:PL/SQL Developer        使用客户机可提高性能    数据库和实例(重点)        一个oracle服务器由一个oracle数据库和多个oracle实例组成            数据库可以理解成一个物理概念                oradata目录用于存放数据库,里面的每一个目录都是一个数据库,三种文件:数据文件,控制文件,日志文件            实例可以理解成一个逻辑概念                oracle将数据库读入到内存中,形成实例.我们通过实例操作数据库.                实例可以在另外的电脑上,而且可以有多个            实例可以操作数据库,一个实例只能与一个数据库关联.一个数据库可以有多个实例进行操作    集群        多个实例就组成一个集群,优点            1.Load Balance 负载均衡(多台电脑分担压力)            2.Fail Over 失败迁移(一台电脑崩溃,会转由另一台电脑继续执行操作)    oracle体系结构        一个实例中存在一个SGA和多个PGA            SGA,系统全局区system global area            PGA,程序全局区program global area                在独占模式下,一个客户端对应一个PGA,客户端操作PGA,然后将修改提交到服务器    表空间和数据文件        表空间是逻辑概念,数据文件是物理概念        表空间由多个数据文件组成,数据文件只能属于一个表空间        普通用户默认只有一个表空间users    段,区和块        由小到大分别是 数据块,区,段,表,表空间 ,都是虚拟概念        数据块会被映射到磁盘块    在linux上安装oracle参考        http://69520.blog.51cto.com/59520/91156oracle服务    OracleServiceORCL : orcl数据库服务,orcl为数据库名称    OracleOraDb10g_home1TNSListener : 监听 到数据库的连接(监听1521端口)    OracleOraDb10g_home1iSQL*Plus : iSQLPlus,oracle网页管理工具(监听5560端口)    OracleJobSchedulerORCL : 定时器,定时执行某项操作    OracleDBConsoleorcl : oracle管理控制台(监听1158端口)    启动服务命令        lsnrctl start/stop/status   监听服务 启动/停止/状态        net start/stop OracleServiceORCL 数据库服务 启动/停止oracle自带管理工具    SQLPlus:oracle提供的命令行工具    iSQLPlus:SQLPlus的网页版,访问网址:http://localhost:5560/isqlplus(要访问成功需要确保OracleOraDb10g_home1iSQL*Plus服务启动 )    oracle Database Control:oracle的管理控制台,访问网址:http://localhost:1158/em/(要访问成功需要确保OracleDBConsoleorcl服务启动)(只有管理员可以登录)sqlplus    登陆sqlplus        cmd --> sqlplus 用户名/密码        或 先输入sqlplus,回车,然后再输入用户名和密码,好处:密码不显示    常用sqlplus命令        show user           查询当前用户        select * from tab;  查询当前用户下的表        host cls            清屏        exit                退出当前用户        desc 表名           查看表结构        show linesize       显示行宽        set linesize 数字   设置行宽(默认80)        col 列名 for a数字  设置字符串列的列宽        col 列名 form 999   设置数字列的列宽,几个9代表几个字符        show pagesize       显示结果集页大小        set pagesize 数字   设置结果集页大小        /                   执行上一条sql语句        spool 命令          记录命令行的内容并保存到本地,            spool 路径      开始记录,例: spool c:/基本查询.txt.            spool off       结束记录        set timing on/off   执行sql语句后打印运行时间        set feedback on/off 关闭运行提示(例:已插入1条记录)        set serveroutput on 开启输出开关        @路径               执行外部sql文件 例@C:/test_delect.sql    组织输出格式,例        set linesize 200;        set pagesize 20;        col sal for 9999;        col mgr for 9999;        col comm for 9999;        alter session set NLS_DATE_FORMAT='yyyy-MM-dd';    sqlplus修改上一条出错的sql语句        1. c 命令 (change)            例:                SQL> select empno,ename,sal,sal*12                  2  form emp;                form emp                     *                第 2 行出现错误:                ORA-00923: 未找到要求的 FROM 关键字                SQL> 2                  2* form emp                SQL> -- c 命令 change                SQL> c /form/from                  2* from emp                SQL> /            说明:                第一次把from 错打为form                输入2表示要修改上一条sql语句的第二行                c命令表示修改,语句 c /错误单词/正确单词                最后输入/表示执行上一条sql语句        2. ed 命令 (edit)            例:                SQL> select * form emp;                select * form emp                         *                第 1 行出现错误:                ORA-00923: 未找到要求的 FROM 关键字                SQL> ed                已写入 file afiedt.buf                  1* select * from emp                SQL> /            说明:                第一次把from 错打为form                输入ed表示编辑上一次的sql语句,则上一次的sql语句会用一个记事本打开,修改完成后保存关闭即可                最后输入/表示执行上一条修改后的sql语句oracle的默认日期格式    DD-MON-RR,例:1981-10-17记录为17-10月-81    查看默认日期格式:select * from v$nls_parameters;    修改默认日期格式        修改本次会话: alter session set NLS_DATE_FORMAT='yyyy-MM-dd';        修改数据库默认(管理员): alter system set NLS_DATE_FORMAT='yyyy-mm-dd';转义字符    查询名称中带有_的所有员工    不能使用 select * from emp where ename like '%_%'    要使用转义字符 select * from emp where ename like '%\_%' escape '\'    escape后指定转义字符为\,可定义其它字符,转义字符后的字符就是本意的字符,不发挥特殊作用单行函数和多行函数(组函数)    单行函数:对单行结果进行处理,返回一个值.分为字符函数,数值函数,日期函数,类型转换函数,通用函数,条件表达式六种    多行函数:对多行结果进行处理,返回一个值.如sum,avg,count,max,min    注意:        多行函数会自动滤空,可以使用count(nvl(comm,0))来屏蔽多行函数的滤空功能        having和where的区别:where中不能使用多行函数        wm_concat(str)组函数, 行转列, 使用示例:select deptno,wm_concat(ename) from emp group by deptno;字符函数    转小写          lower(str)    转大写          upper(str)    首字母大写      initcap(str)    字符串连接      concat(str1,str2)    截取字符串      substr(a,b) 从a中，第b位开始取;substr(a,b,c) 从a中，第b位开始取,取c位    在字符串中查找另一字符串    instr(a,b) 在a中，查找b；找到返回下标；否则返回0    字符数          length(str)    字节数          lengthb(str)    左填充          lpad(a,len,b) 将a左边用b字符填充,直到总长度为len    右填充          rpad(a,len,b) 将a右边用b字符填充,直到总长度为len    去掉前后指定的字符  trim(c from str) 将str两边的c字符都去掉    替换            replace(str,a,b) 将str中的a字符全部换为b字符数值函数    四舍五入        round(num,a) 将num四舍五入,保留a位小数 (a为-1表示根据个位取舍,结果为两位数或0)    舍去            trunc(num,a) 将num小数点后第(a+1)位舍去,保留a位小数(a为-1表示舍去个位,保留十位)    求余            mod(a,b) 用a对b求余日期函数    日期类型        datetime,timestamp    相差天数        日期相减得到相差天数    当前日期        select sysdate from dual;  select systimestamp from dual;    格式化当前日期  to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),取日期中的年为to_char(sysdate,'yyyy')                    to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss:ff')                    to_char(systimestamp,'yyyy-mm-dd "今天是"day') 如果要加入自定义字符串,要加""    相差月数        months_between(day1,day2)    日期加上若干月  add_months(day,num) num为负表示减去    下一单位的日期  next_day(day,str) 例:select next_day(sysdate,'星期日') from dual;    本月最后一天    last_day(day)    四舍五入        round(day,str) str为:'MONTH','YEAR'等    舍去            trunc(day,str) str为:'MONTH','YEAR'等类型转换函数    字符串 --> 数字 : to_number(str[,format])    字符串 --> 日期 : to_date(str[,format])    数字 --> 字符串 : to_char(num,format) 例:to_char(sal,'L9,999.99') L表示本地货币符号,9表示数字,0表示0,点表示小数点,逗号表示千位符    日期 --> 字符串 : to_char(date,format) 例:to_char(sysdate,'yyyy-mm-dd hh24:mi:ss"今天是"day')通用函数    适用于任何数据类型,同时也适用于空值    nvl(a,b) : 如果a不为null,则返回a;如果a为null,则返回b    nvl2(a,b,c) : 如果a不为null,则返回b;如果a为null,则返回c    nullif(a,b) : 当a=b时候,返回null,否则返回a    coalesce : 从左到右,找到第一个不为null的值    wm_concat(列名) : 将每行中指定列的值用逗号分隔组成一个字符串条件表达式    case 表达式(不同数据库通用)        SQL> select ename,job,sal 涨前,          2         case job when 'PRESIDENT' then sal+1000          3                  when 'MANAGER' then sal+800          4                  else sal+400          5          end 涨后          6  from emp;    decode函数(oracle特有)        SQL> select ename,job,sal 涨前,          2         decode(job,'PRESIDENT',sal+1000,          3                    'MANAGER',sal+800,          4                              sal+400) 涨后          5  from emp;伪列    伪列是oracle提供的列,在使用时在select后跟上伪列的名称即可    常用伪列:        rownum:行号             注意:                1.rownum永远按照默认的顺序生成(即没有排序时的顺序,排序后不会重新生成,需要使用子查询,在外边的查询使用行号)                 2.rownum只能使用< <= !, 不能使用> >= = between...and(如 where rownum>2 因为rownum总是从1开始的，如果没有满足的第一条记录的话，后面的记录的rownum永远还为1。所以永远没有满足条件的记录。)        rowid:行地址(记录数据行在dbf文件中的位置)top n语句,分页语句    oracle中不存在top n语法,需要借助伪列rownum    找到员工表中工资最高的前三名员工:        select rownum,empno,ename,sal        from (select * from emp order by sal desc)        where rownum<=3    找到员工表中工资第五到第八的员工        select *        from (select rownum r,empno,ename,sal            from (select * from emp order by sal desc) e1            where rownum<=8) e2        where r>=5        上面的r不是e2表的行号(是e1表的行号),是e2表的一个普通列,可以使用>=符号sql优化    1. 查询时指定列名比*更有效率(oracle9i之后,基本一样)    2. where解析的顺序为从右往左    3. 理论上 多表查询快于子查询    4. 尽量不要使用集合运算地址符  &    相当于jdbc中的PreparedStatement    例:insert into emp(empno,ename,sal,deptno) values(&empno,&ename,&sal,&deptno);    地址符可以出现在 数据操作语言的几乎所有位置    将上述语句运行,则数据库会要求输入对应的值delete和truncate的区别    1. delete属于逐条删除 truncate先摧毁表 再重建    2. delete是DML(可以回滚) truncate是DDL（不可以回滚）    3. delete 不会释放空间 truncate会    4. delete会产生碎片 truncate不会    5. delete 可以闪回 truncate不可以事务    起始标志:   DML语句    结束标志:   提交--> 显式 commit                        隐式 正常退出exit , 执行DDL语句                回滚--> 显式 rollback                        隐式 非正常退出(直接关闭控制台)，掉电，宕机    提交事务: commit;    回滚事务: rollback;    定义保存点:savepoint 保存点名称;    回滚到保存到:rollback to savepoint 保存点名称;    oracle的隔离级别        read commited(读已提交)        serializable(串行化)        read only(只读,别的事务只能读,不能写,非标准)常见数据库对象(12个)    表:基本的数据存储集合,由行和列组成.    视图:从表中抽出的逻辑上相关的数据集合.    序列:提供有规律的数值.    索引:提高查询的效率.    同义词:给对象起别名.    存储过程:存储在数据库中供所有用户程序调用的子程序    存储函数:存储在数据库中供所有用户程序调用的子程序    触发器:触发器是一个与表关联的,存储的PL/SQL程序.每当表中一个特定的数据库操作语句发出时,oracle会自动执行触发器中定义的语句序列    包:声明存储过程和光标    包体:定义存储过程和光标    快照:定期备份分布式数据库的数据    数据库链路:实现分布式数据库,通过一个服务器操作另一个服务器上的数据库表操作    创建表必须具备        create table权限        存储空间    数据类型        varchar2(size)      可变长字符数据        char(size)          定长字符数据        number(p,s)         可变长数值数据        date                日期型数据        long                可变长字符数据,最大可达到2G        clob                字符数据,最大可达到4G        raw,long raw        原始的二进制数据        blob                二进制数据,最大可达到4G        bfile               存储外部文件的二进制数据,最大可达到4G        rowid               行地址(指向dbf文件中的一个位置)    使用子查询创建表        create table 名称 as 查询语句    回收站        drop表后,表被放入到回收站        查看回收站: show recyclebin        清空回收站: purge recyclebin        当drop语句后跟上purge关键字,相当于不放入回收站,彻底删除        通过原来的名称,不可以访问到原来的表,但是可以通过回收站中的名称(要加引号)访问原来的表,数据还在        注意:管理员没有回收站,视图也不被放入回收站        恢复回收站中的表需要用到闪回    当定义外键约束时,可以指定级联操作        on delete cascade 删除主表数据前,先删除子表中引用此数据的数据        on delete set null 删除主表数据前,先将子表中引用此数据的数据的外键置为null        例: references 主表名(引用列名) on delete cascade视图    视图概述        视图是一种虚表        视图建立在已有表的基础上,视图赖以创建的这些表称为基表        向视图提供数据内容的语句为select语句,可以将视图理解为存储起来的select语句        视图向用户提供基表数据的另一种表现形式    视图的优点        简化复杂查询        同样的数据,可以有不同的显示方式        提供数据的相互独立        限制数据访问    创建视图        create view 视图名         as 查询语句        [with check option]     --修改时,必须遵循定义视图的select语句的条件        [with read only]        --只读的,不能修改    创建视图需要权限(管理员)        grant create view to 用户;    视图可以看作表,操作视图和操作表基本一致    不建议通过视图对表进行修改,因为有非常多的限制序列    可供多个用户用来产生唯一数值的数据库对象    实质是一个数组,相当于一个自动增长列的值    作用:        自动提供唯一的数值        共享对象        主要用于提供主键值        序列值存入内存中可以提高访问效率    创建序列:        create sequence 名称            [increment by n]    //间隔,默认为1            [start with n]      //起始值,默认为1            [{maxvalue n | nomaxvalue}] //最大值,默认为nomaxvalue无最大值            [{minvalue n | nominvalue}] //最小值,默认为nominvalue无最小值            [{cycle | nocycle}]     //是否循环,默认为nocycle不循环            [{cache n | nocache}]   //用完后每次增加多少,默认为cache 20    删除序列:        drop sequence 名称    序列的属性(伪列):nextval,currval        nextval:序列中下一个有效的值,任何用户都可以引用        currval:序列的当前值        初始指针在第一个值之前,所以第一次应先使用nextval,再使用currval    伪列的使用:        序列名称.nextval 或 序列名称.currval        例:insert into test_seq values(myseq.nextval,'aaa');    序列的使用有很多限制(了解)        带集合运算的查询语句不能使用        在delete,select,update语句的子查询中不能使用        带有distinct操作符的select语句不能使用        有group by,order by操作的select语句不能使用        在select语句中的where子句中不能使用        在create table或alter table语句的中default值是不能使用sequence        在check约束中不能使用    插入多条记录时使用序列        insert into t_user(id,username)            select my_seq.nextVal,u.* from (                    select '杭三' from dual union                    select '杭三1' from dual                ) u    序列在下列情况下会出现裂缝(不连续):        回滚        系统异常(序列存在于内存中,假如一次生成20个,当前用到了3,如果停电了,则下次从21开始)        多个表同时使用同一序列索引    概述        一种独立于表的模式对象,可以存储在与表不同的磁盘或表空间中        索引被删除或损坏,不会对表产生影响,其影响的只是查询的速度        索引一旦建立,oracle管理系统会对其进行自动维护,而且由oracle管理系统决定何时使用索引.用户不用在查询语句中指定使用哪个索引        在删除一个表时,所有基于该表的索引会自动被删除        通过指针加速oracle服务器的查询速度        通过快速定位数据的方法,减少磁盘I/O        索引表中存放的是有规律的rowid,可快速定位要查询的没有规律的数据    创建索引        create index 索引名称 on 表(列[,列]...);    使用索引        oracle管理系统决定何时使用索引.用户不用在查询语句中指定使用哪个索引    删除索引        drop index 索引名称;    适合创建索引的情况        列中数据值分布范围很广        列经常在where子句或连接条件中出现(需要通过索引所在的列查询)        表经常被访问而且数据量很大,访问的数据大概只占数据总量的2%到4%    不适合创建索引的情况        表很小        列不经常做为连接条件或出现在where子句中        查询的数据大于2%到4%        表经常更新(索引基于列经常更新)    oracle中的索引分为两种(了解)        B树结构(默认)        位图结构同义词    为数据库对象取别名(表,视图,序列等)    创建同义词:        create [public] synonym 别名 for 数据库对象;        public为公有的,所有用户都可以使用,默认为私有的    需要管理员授权:        grant create synonym to scott;PL/SQL    概述        PL/SQL也是一种程序语言，叫做过程化SQL语言（Procedural Language/SQL）。        PL/SQL是Oracle数据库对SQL语句的扩展。        指在SQL命令语言中增加了过程处理语句(如分支,循环等),使SQL语言具有过程处理能力        oracle对SQL的扩展语言叫做Plsql,SQLServer对SQL的扩展语言叫做Transact-sql    目的        PL/SQL程序操作oracle数据库效率最高        存储过程,存储函数需要使用到PL/SQL程序    格式        [declare]            --说明部分(变量说明,光标说明,例外说明),没有可以省略declare关键字        begin            --程序        [exception]            --例外处理语句,没有可以省略exception关键字        end;        /        --定义变量要写在declare部分        --程序以begin开始,以end和;结束    最简单的PLSQL程序示例:        begin         dbms_output.put_line('Hello World');        end;        /        --程序包dbms_output下的存储过程put_line用于输入        --要打印需要开启oracle的输出开关:set serveroutput on    PL/SQL基本语法:变量的使用,分支,循环,光标(游标),例外(异常)变量    定义变量        语法: 名称 类型;        类型: char,varchar2,date,number,boolean,long        例:            varl    char(15);            married boolean:=true;  --赋初始值            psal    number(7,2);            my_name emp.ename%type; --引用型变量,与emp表的ename列的类型一样            emp_rec emp%rowtype;    --记录型变量,记录一行的数据,相当于数组    使用变量        使用:=给变量赋值            pename:='张三';        使用into关键字在查询语句或光标中给变量pename赋值            查询语句中: select sal into pename from emp where empno=78            光标中: fetch cemp into pename;    引用型变量示例        --查询并打印7839的姓名和薪水        set serveroutput on        declare          --定义引用型变量 保存姓名和薪水          pename emp.ename%type;          psal   emp.sal%type;        begin          --得到姓名和薪水          select ename,sal into pename,psal from emp where empno=7839;          --打印          dbms_output.put_line(pename||'的薪水是'||psal);        end;        /    记录型变量示例        --查询并打印7839的姓名和薪水        set serveroutput on        declare          --定义记录型变量 代表一行          emp_rec emp%rowtype;        begin          select * into emp_rec from emp where empno=7839;          dbms_output.put_line(emp_rec.ename||'的薪水是'||emp_rec.sal);        end;        /        --使用 变量.列名 取出记录型变量中的每个值分支(if语句)    语法:(注意elsif关键字的写法)        1.  if 条件 then 语句1;            语句2;            end if;        2.  if 条件 then 语句序列1;            语句序列2;            end if;        3.  if 条件 then 语句;            elsif 语句 then 语句;            else 语句;            end if;    示例,判断用户输入的数字        set serveroutput on        --键盘输入        --num 是一个地址值(叫什么无所谓,是一个变量)；在该地址上 保存了输入的值        accept num prompt '请输入一个数字';        declare          --定义变量 保存输入的数字          pnum number := &num;        begin          if pnum = 0 then dbms_output.put_line('您输入的是0');            elsif pnum = 1 then dbms_output.put_line('您输入的是1');            elsif pnum = 2 then dbms_output.put_line('您输入的是2');            else dbms_output.put_line('其他数字');          end if;        end;        /循环    语法:        1.  where 条件            loop                语句;            end loop;        2.  loop                语句;                exit when 条件; --当条件成立时退出,exit when语句可以有多个,表示多个退出条件                语句;            end loop;        3.  for i in 初始值..结束值            loop                语句;            end loop;            -- 如果初始值 和 结束值时连续的,可使用..        注意:oracle中没有++的语法,应为 变量:=变量+1;光标(游标)    概述        用于存储一个查询返回的多行数据,相当于java中的结果集(ResultSet)    定义光标        cursor 光标名称 [(参数名 数据类型[,参数名 数据类型]...)]        is select语句;        --参数是输入参数,在打开时就要指定参数值    打开光标(去执行查询)        open c1 [(参数,...)];    使用光标        取一行值存入变量(光标初始指向第一行记录)            fetch c1 into pename,penum;        光标的属性            %isopen(是否已打开)            %rowcount(返回的行数)            %notfound(后面不存在记录)            %found(后面存在记录)            使用示例                循环的退出条件: exit when 光标名称%notfound;    关闭光标        close c1;    例1,查询并打印员工的姓名和薪水        set serveroutput on        declare          --定义光标          cursor cemp is select ename,sal from emp;          pename emp.ename%type;          psal   emp.sal%type;        begin          open cemp;          loop            --取一条记录            fetch cemp into pename,psal;            --没有取到  退出            exit when cemp%notfound;            dbms_output.put_line(pename||'的薪水是'||psal);          end loop;          close cemp;        end;        /    例2,给员工涨工资 总裁1000 经理800 其他400        set serveroutput on        declare          cursor cemp is select empno,empjob from emp;          pempno emp.empno%type;          pjob   emp.empjob%type;        begin          open cemp;          loop            --取一个员工            fetch cemp into pempno,pjob;            exit when cemp%notfound;            if pjob = 'PRESIDENT' then update emp set sal=sal+1000 where empno=pempno;              elsif pjob = 'MANAGER' then update emp set sal=sal+800 where empno=pempno;              else update emp set sal=sal+400 where empno=pempno;            end if;          end loop;          close cemp;                    commit;                    dbms_output.put_line('完成');        end;        /    带参数的光标: 查询某个部门的员工姓名        set serveroutput on        declare          cursor cemp(dno number) is select ename from emp where deptno=dno;    --定义光标不同          pename emp.ename%type;        begin          open cemp(20);        --打开光标不同,要传递参数          loop            fetch cemp into pename;            exit when cemp%notfound;                        dbms_output.put_line(pename);          end loop;          close cemp;        end;        /例外    例外是程序设计语言提供的一种功能,用来增强程序的健壮性和容错性    系统定义例外        No_data_found   没有找到数据        Too_many_rows   select ... into 语句匹配多个行        Zero_Divide     被零除        Value_error     算数或转换错误        Timeout_on_resource 请求超时    捕获异常示例        --被0除        set serveroutput on        declare          pnum number;        begin          pnum := 1/0;                  exception   --exception关键字用于捕获例外,when用来识别例外,then用来指定处理的操作,一个then后面可跟多条语句          when zero_divide then dbms_output.put_line('0不能做被除数');          when value_error then dbms_output.put_line('算术或者转换错误');          when others then dbms_output.put_line('其他例外');    --others关键字表示其它例外        end;        /        --注意:相当于java中的异常,但是没有finally语句块    自定义例外        --查询50号部门的员工姓名        set serveroutput on        declare          cursor cemp is select ename from emp where deptno=50;          pename emp.ename%type;                    --自定义例外          no_emp_found exception;        begin          open cemp;          --取一条记录          fetch cemp into pename;          if cemp%notfound then             --抛出例外,使用raise关键字            raise no_emp_found;          end if;                    close cemp;                  exception          when no_emp_found then dbms_output.put_line('没有找到员工');                                if cemp%isopen then close cemp end if;  --关闭光标,每个when中都要写一遍          when others then dbms_output.put_line('其他例外');                                  if cemp%isopen then close cemp end if;  --关闭光标        end;        /    手动抛出例外        1. 使用raise关键字,表示不应该出现的例外,会抛给数据库,会报错            raise 异常变量;        2. 使用函数,表示应用程序希望抛出的例外            raise_application_error(error_code,msg)函数抛出一个例外,错误码要在-20000~-20999之间            raise_application_error(-20001,'这里发生错误'); --如在before触发器中阻止后面的操作执行存储过程和存储函数    概述        指存储在数据库中供所有用户程序调用的子程序叫做存储过程,存储函数    存储过程        定义存储过程            create [or replace] procedure 名称(参数列表)            as                PLSQL程序体;            --如果存在or replace选项,则如果存储过程已存在,自动覆盖            --这里的as相当于PLSQL程序中的declare,即说明部分,但是如果没有说明部分,as也不可以省略.下面的PLSQL就不能出现declare关键字了            例:                create or replace procedure sayHello                as                   --说明部分                begin                    dbms_output.put_line('Hello World');                end;                /            注意:                参数分为输入参数in 和输出参数out. 形参的类型都不需要指定精度                不要在存储过程中进行提交和回滚事务,交给调用者处理事务        调用存储过程            1.  [exec] 存储过程名称(参数列表);  --当存储过程为第一句时,必须写上exec            2.  begin                           --在PLSQL程序块中调用                    存储过程名称(参数列表);                end;            参数列表的写法                1. raiseSalary(7839);                2. raiseSalary(eno => 7839); --前面为形参名,后面为值或变量        带参数的存储过程            给指定的员工涨100工资,并且打印涨前和涨后的工资                create or replace procedure raiseSalary(eno in number)  --in表示输入型参数,可以省略                as                   --定义变量保存涨前的薪水                   psal emp.sal%type;                begin                   select sal into psal from emp where empno=eno;                   update emp set sal=sal+100 where empno=eno;                   dbms_output.put_line('涨前:'||psal||'   涨后:'||(psal+100));                end;                /            调用                set serveroutput on     --打开输出开关                begin                    raiseSalary(7839);                    raiseSalary(7566);                    commit;                end;        带输出参数的存储过程            查询并返回某个员工的姓名  月薪 职位                create or replace procedure queryEmpInfo(                    eno in number,                    pename out varchar2,    --out表示输出参数                    psal out number,                    pjob out varchar2)                as                begin                 select ename,sal,empjob into pename,psal,pjob from emp where empno=eno;                end;                /            调用                set serveroutput on     --打开输出开关                declare                    pename varchar(200);                    pjob varchar(200);                    psal number;                begin                    queryempinfo(7839,pename,psal,pjob);                    dbms_output.put_line(pename||'   '||psal||'   '||pjob);                end;    存储函数        定义存储函数            create [or replace] function 名称(参数列表)            return 函数值类型            as                PLSQL程序体;            --如果存在or replace选项,则如果存储过程已存在,自动覆盖            --这里的as相当于PLSQL程序中的declare,即说明部分,但是如果没有说明部分,as也不可以省略.下面的PLSQL就不能出现declare关键字了            注意                存储过程必须有return子句,用于返回函数值.函数说明中要指定结果值的类型                参数分为 输入参数in 和输出参数out,形参的类型都不需要指定精度        调用存储函数            和存储过程相同        示例            查询并返回某个员工的年收入                create or replace function queryEmpIncome(eno in number)                return number                as                   --定义变量保存月薪和奖金                   psal emp.sal%type;                   pcomm emp.comm%type;                begin                   select sal,comm into psal,pcomm from emp where empno=eno;                   --返回年收入                   return psal*12+nvl(pcomm,0);                end;                /            调用                set serveroutput on     --打开输出开关                declare                    returnVal number;                begin                    returnVal:=queryEmpIncome(7839);                    dbms_output.put_line(returnVal);                end;    存储过程和存储函数的区别        存储过程没有返回值,存储函数可以有返回值        但是存储过程和函数都可以通过out指定一个或多个输出参数,实现返回多个值        实际上两者可以实现的功能是相同的,是由于历史问题造成的两者并存        建议:如果只有一个返回值,使用存储函数.否则使用存储过程.包和包体    引出        查询并返回某个部门中所有员工的信息,使用 输出参数定义的变量太多,应使用out的光标        在存储过程或存储函数中使用光标,必须将光标和存储过程或存储函数声明到包中    包头:声明存储过程和光标    包体:定义存储过程和光标    例:        --包头        create or replace package mypackage as            type empcursor is ref cursor;                               --声明一个类型empcursor,为光标类型            procedure queryEmpList(dno in number,empList out empcursor);--声明存储过程,empList的类型为empcursor,即光标类型        end mypackage;        --包体        create or replace package body mypackage as            --定义存储过程            procedure queryEmpList(dno in number,empList out empcursor) as            begin                open empList for select * from emp where deptno=dno;            end queryEmpList;        end mypackage;        --调用        set serveroutput on     --打开输出开关        declare            returnVal mypackage.empcursor;  --包中定义的游标类型            rowVal emp%rowtype;        begin            mypackage.queryEmpList(20,returnVal);            loop                fetch returnVal into rowVal;                exit when returnVal%notfound;                dbms_output.put_line(rowVal.empno||','||rowVal.ename);            end loop;        end;        /在java中调用存储过程,存储函数,包    public class TestOracle {            /*         * create or replace procedure queryEmpInfo(                eno in number,                pename out varchar2,                psal out number,                pjob out varchar2)         */        @Test        public void testProcedure(){            //{call <procedure-name>[(<arg1>,<arg2>, ...)]}            String sql = "{call queryEmpInfo(?,?,?,?)}";            Connection conn = null;            CallableStatement call = null;            try {                conn = JDBCUtils.getConnection();                call = conn.prepareCall(sql);                                //对于in 参数，赋值                call.setInt(1, 7839);                                //对于out参数，声明                call.registerOutParameter(2, OracleTypes.VARCHAR);                call.registerOutParameter(3, OracleTypes.NUMBER);                call.registerOutParameter(4, OracleTypes.VARCHAR);                                //执行                call.execute();                                //取出值                String name = call.getString(2);                double sal = call.getDouble(3);                String job = call.getString(4);                System.out.println(name);                System.out.println(sal);                System.out.println(job);            } catch (Exception e) {                e.printStackTrace();            }finally{                JDBCUtils.release(conn, call, null);            }        }                /*         * create or replace function queryEmpIncome(eno in number)            return number            */        @Test        public void testFunction(){            // {?= call <procedure-name>[(<arg1>,<arg2>, ...)]}            String sql  = "{?=call queryEmpIncome(?)}";                        Connection conn = null;            CallableStatement call = null;            try {                conn = JDBCUtils.getConnection();                call = conn.prepareCall(sql);                                //对于out参数，声明                call.registerOutParameter(1, OracleTypes.NUMBER);                                //对于in 参数，赋值                call.setInt(2, 7839);                                //执行                call.execute();                                //取出值                double income = call.getDouble(1);                System.out.println(income);            } catch (Exception e) {                e.printStackTrace();            }finally{                JDBCUtils.release(conn, call, null);            }               }                /*         *  CREATE OR REPLACE PACKAGE MYPACKAGE AS               type empcursor is ref cursor;              procedure queryEmpList(dno in number,empList out empcursor);            END MYPACKAGE;         */        @Test        public void testCursor(){            //{call <procedure-name>[(<arg1>,<arg2>, ...)]}            String sql = "{call MYPACKAGE.queryEmpList(?,?)}";  //包名.存储过程名            Connection conn = null;            CallableStatement call = null;            ResultSet rs = null;            try {                conn = JDBCUtils.getConnection();                call = conn.prepareCall(sql);                                //对于in 参数，赋值                call.setInt(1, 20);                                //对于out参数，声明                call.registerOutParameter(2, OracleTypes.CURSOR);                                //执行                call.execute();                                rs = ((OracleCallableStatement)call).getCursor(2);                while(rs.next()){                    String name = rs.getString("ename");                    double sal = rs.getDouble("sal");                    String job = rs.getString("empjob");                    System.out.println(name+"\t"+sal+"\t"+job);                }                            } catch (Exception e) {                e.printStackTrace();            }finally{                JDBCUtils.release(conn, call, rs);  --当关闭结果集时,光标也被关闭            }        }    }触发器    触发器是一个与表关联的,存储的PL/SQL程序.    每当一个特定的数据库操作语句(insert,update,delete)在指定的表上发出时,oracle会自动执行触发器中定义的语句序列    触发器可用于        数据确认        实施复杂的安全性检查        做审计,跟踪表上所做的数据操作等        数据的备份和同步    触发器的类型        语句级触发器            在指定的操作语句执行之前或之后执行一次,不管这条语句影响了多少行.        行级触发器(for each row)            触发语句作用的每一条记录都被触发.在行级触发器中使用:old和:new伪记录变量,获得之前的值和修改之后的值.    定义语法        create [or replace] trigger 名称        {before | after}        {delete|insert|update[of 列名]}        on 表名        [for each row [when(条件)]]        PLSQL程序体;        --[for each row [when(条件)]] 如果指定了该选项,则触发器为行级触发器,[when(条件)]表示数据满足条件才会触发        注意            如果有变量,声明在PLSQL程序体的declare部分中            在before触发器中阻止后面的操作执行,可以使用raise_application_error(error_code,msg)函数抛出一个例外,错误码要在-20000~-20999之间    伪记录变量        触发语句    :old                :new        insert      所有字段都是null    将要插入的数据        update      更新以前该行的值    更新后的值        delete      删除以前该行的值    所有字段都是null        伪记录变量相当于记录型变量,记录一行的数据,使用 :old.列名 访问其中的每一列的值    例:        /*        实施安全性检查        禁止在非工作时间插入新员工        非工作时间        1. 周末: to_char(sysdate,'day') in ('星期六','星期日')        2. 上班前 下班后: to_number(to_char(sysdate,'hh24')) not between 9 and 17        */        create or replace trigger securityemp        before insert        on emp        begin          if to_char(sysdate,'day') in ('星期六','星期日') or              to_number(to_char(sysdate,'hh24')) not between 9 and 17 then              --此函数相当于java的throw new RuntimeException(),错误码要在-20000~-20999之间            raise_application_error(-20001,'禁止在工作时间插入新员工');           end if;             end;        /        /*        数据确认        涨后的薪水不能少于涨前的薪水        */        create or replace trigger checksal        before update        on emp        for each row        begin          --if 涨后的薪水 < 涨前的薪水 then           if :new.sal < :old.sal  then             raise_application_error(-20002,'涨后的薪水不能少于涨前的薪水.涨前:'||:old.sal||'  涨后:'||:new.sal);          end if;         end;        /数据字典    数据库表分类        基本表:描述数据库信息,只能由数据库服务器进行修改        用户表:用户自定义表    数据字典        基本表就是数据字典,由管理员进行维护        dictionary是一张总的数据字典表,记录了有哪些数据字典表    数据字典命名规则        前缀        说明        user        用户自己的        all         用户可以访问到的        dba         管理员视图        v$          性能相关的数据    常用数据字典        user_objects,记录当前用户创建的数据库对象        user_tab_columns,记录当前用户创建的表的列        user_constraints,当前用户表上的约束        user_cons_columns,当前用户创建的列约束        user_views, user_sequences, user_synonyms 当前用户创建的视图,序列,同义词        user_col_comments,user_tab_comments 列或表上的注释        user_source 当期用户创建的数据库对象的源代码        上面的数据字典都有对应的all开头的数据字典闪回    概述        当发生数据丢失,数据错误问题时,解决的主要办法是数据的导入导出,备份恢复技术,这些错误都需要在错误发生前,有一个正确的备份才能进行恢复        为了减少这方面的损失,oracle提供了闪回技术,可以实现数据的快速恢复,而且不需要数据备份(基于日志文件)    闪回可以解决的问题        1. 错误的删除了数据,并且commit        2. 错误的删除了表        3. 如何获取表上的历史记录        4. 如何撤销一个已经提交了的事务    闪回的类型        1. 闪回表 : 将表回退到过去的一个时间上(错误的删除了数据,并且commit)        2. 闪回删除 :　操作oracle的回收站(错误的删除了表)        3. 闪回版本查询 : 获取表上的历史记录(只要曾经被commit过)        4. 闪回事务查询 : 获取一个undo_sql,用于回退事务        5. 闪回数据库 : 将数据库回退到过去的一个时间上(了解)        6. 闪回归档日志 : 将日志回退到过去的一个时间上(了解)    闪回表        解决问题:错误的删除了数据,并且commit        解决方法:将表回退到过去的一个时间上 或 系统改变号SCN上 (系统改变号和当前时间一一对应)        语法:            FLASHBACK TABLE [schema.]<table_name>             TO              {[BEFORE DROP [RENAME TO table]]              [SCN|TIMESTAMP] 表达式            [ENABLE|DISABLE]TRIGGERS}             --schema：模式名，一般为用户名。            --TO TIMESTAMP：系统邮戳，包含年、月、日、时、分、秒。            --TO SCN：系统更改号.与当前时间对应,可通过时间查询scn号:timestamp_to_scn(时间)            --ENABLE TRIGGERS：表示触发器恢复以后为enable状态，而默认为disable状态。            --TO BEFORE DROP：表示恢复到删除之前。            --RENAME TO table：表示更换表名。        例:            flashback table test to scn号;        注意:            通过show parameter undo命令可以了解与撤销表空间相关的undo信息。            需要管理员赋予权限: grant flashback any table to 用户;            需要打开表的行移动: alter table 表名 enable row movement;            闪回表操作不能闪回系统表            闪回表操作不能跨越ddl操作            如何获取离正确操作最近的一个SCN? 可使用 闪回版本查询.        示例            --打开表的行移动            alter table flashback_table enable row movement;            --闪回            flashback table 表名 to SCN 2234630;    闪回删除        解决问题:错误的删除了表        解决方法:操作oracle的回收站        语法同闪回表的语法,选项为to before drop,不使用scn或timestamp        例:            flashback table 表名 to before drop;        表名可以使用原先的表名,也可以使用回收站中的名称要加引号.        注意,原表名可能重复,最好使用回收站中的名称.        当要恢复的表名称已被其他对象占用,可以使用 rename to 名称 选项更改表名称    闪回版本查询        闪回版本查询提供了一个审计行改变的查询功能,通过它可以查找到所有已经提交了的行记录.(可以查询到已改变或已删除的记录)        语法:            select column_name[,column_name,...]            from table_name            versions between [SCN|TIMESTAMP] [expr|MINVALUE]                        and [epxr|MAXVALUE] as of [SCN|TIMESTAMP] expr;            --column_name列名            --table_name表名            --between...and时间段            --SCN系统改变号            --TIMESTAMP时间戳            --AS OF表示恢复单个版本            --MAXVALUE最大值            --MINVALUE最小值            --expr指定一个值或者表达式。        例:            select vid,vname,versions_o,versions_starttime,versions_endtime,versions_xid            from 表 versions between timestamp minvalue and maxvalue;         伪列:            versions_o : 操作类型            versions_starttime : 开始时间            versions_endtime : 结束时间            versions_xid : 事务号    闪回事务查询        解决问题:撤销一个已经提交的事务        闪回事务查询实际上闪回版本查询的一个扩充，通过它可以审计某个事务甚至撤销一个已经提交的事务。        实现闪回事务查询，需要从flashback_transaction_query视图中获取事务的历史操作记录以及撤销语句(UNDO_SQL)。        需要管理员授权:grant select any transaction to 用户;        操作:            1. 通过闪回版本查询得到事务号xid.                select vid,vname,versions_o,versions_starttime,versions_endtime,versions_xid                from 表 versions between timestamp minvalue and maxvalue;            2. 查询撤销sql                select operation,undo_sql                 from Flashback_transaction_query                 where xid=刚才查询的xid;            3. 拷贝sql语句并执行,可撤销操作导入导出    注意:导入和导出命令是window的命令,不用登陆sqlplus,执行在命令行执行    导出:        导出表:exp 用户名/密码 file=d:/a.dmp log=d:/log.log tables=dept,emp;    --file为导出的文件,必须为dmp文件        导出某个用户所有对象:exp 用户名/密码 file=d:/a.dmp log=d:/log.log;        导出所有用户所有对象(管理员):exp 用户名/密码 file=d:/a.dmp log=d:/log.log full=y; --full=y表示全部导出        输入exp 用户名/密码,按回车,则出现导出向导    导入:        导入表:imp 用户名/密码 file=d:/a.dmp log=d:/log.log tables=dept,emp fromuser=scott touser=hr commit=y ignore=y;              --commit 导入完成自动提交            --ignory 忽略导入过程中的错误,继续导入        导入用户下的表:imp 用户名/密码 file=d:/a.dmp log=d:/log.log fromuser=scott touser=hr commit=y ignore=y;        导入数据库:imp 用户名/密码 file=d:/a.dmp log=d:/log.log full=y ignore=y destroy=y; --destroy,如果存在同名对象,则删除原来的对象,再导入        输入imp 用户名/密码,按回车,则出现导出向导方案(Schema)    方案就是属于某一用户的所有对象的集合,与用户是一一对应的关系,并且与用户是同名的.    使用em管理器管理方案,地址:http://localhost:1158/em , 注意要启动服务OracleDBConsoleorcl    登录em需要有select_catalog_role角色管理用户安全    用户管理        创建用户:create user 用户名 identified by 密码        删除用户:drop user 用户名 cascade; --cascade表示级联删除用户的所有内容        修改用户密码:alter user 用户名 identified by 密码;    权限管理        用户权限分为两种:            System:系统权限,允许用户执行对于数据库的特定行为.例:创建表,创建用户            Object:用户权限,允许用户访问和操作一个特定的对象.例:对一个表的访问权限        授予权限:grant 权限 to 用户[,用户][with admin option|grant option];             admin option只能在分配系统权限时使用,表示允许用户将权限分配给别人.撤销权限时不能级联            grant option只能在分配对象权限时使用,表示允许用户将权限分配给别人.撤销权限时可以级联        撤销权限:revoke 权限 from 用户;        分配表空间:alter user 用户 quota unlimited on 表空间;    角色管理        创建角色:create role 角色名;        授予角色权限:grant {权限|角色} to 角色; --可以把另一角色的所有权限赋予创建的角色        将角色分配跟用户:grant 角色 to 用户;    创建用户示例        --创建用户        create user sss identified by sss;        --授权:能登陆,能创建基本数据库对象,并且分配了表空间        grant connect,resource to sss;    概要文件(了解)        用户只能被关联到一个概要文件        作用            控制资源使用            管理账户状态和密码有效期分布式数据库    分布式数据库:数据物理上被存放在网络的多个节点上,但是逻辑上是一个整体.    远程登陆sqlplus: sqlplus 用户名/密码@IP:端口/数据库名称    建立分布式数据库        一个服务器需要操作另一个服务器上的数据,需要向另一条服务器创建数据链路(数据链路是单向的).        定义数据库链路            create database link 名称 connect to 用户 identified by 密码 using 服务命名;                --用户名和密码是要连接的数据库那边的用户和密码                --服务命名为要连接的数据库的服务命名                --注意,需要权限grant create database link to scott;            配置服务命名                服务命名可以通过 oracle的Net Manager工具创建,工具在开始菜单中,注意:配置完成后 要文件-->保存网络配置                配置的内容保存在/product/10.2.0/db_1/netWord/admin/tnsnames.ora文件中,也可打开直接修改,例:                    remoteorcl =                         (description =                            (address_list =                                (address = (protocol = TCP)(HOST = 192.168.1.217)(POST = 1521))                            )                            (connect_data =                                (service_name = orcl)                            )                        )            例:                create database link l2 connect to scott identified by tiger using 'remoteorcl';    分布式数据库的操作        要操作远程数据库的表需要在表名后跟@数据链路名        例: select ename,dname from dept,emp@l2 where emp.deptno=dept.deptno;   --emp表在远程服务器        可以创建一个同义词来使调用远程数据库表和调用本地表没有区别.        例: create synonym remoteemp for emp@l2;            select ename,dname from dept,remoteemp where remoteemp.deptno=dept.deptno;        增删改一样    分布式数据库的跨节点更新(要向另一个服务器备份同步数据)        快照            在备份端创建快照,例:                create snapshot emp                refresh start with sysdate      --第一次刷新时间为现在                next next_day(sysdate,'Monday') --下一次刷新时间为下个星期一                as select * from emp@l1;            本地数据查询快照: select * from emp;        触发器            在本地服务器创建触发器,例:                create or replace trigger syncsal                after update                on emp                for each row                 begin                    update remoteemp set sal=:new.sal where empno=:new.empno;                end;                /        区别:             快照是异步的,触发器是同步的            快照存在于备份端,触发器存在于服务端通过tns(names)连接数据库    例:        TEST =          (DESCRIPTION =            (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.123.11)(PORT = 1521))            (CONNECT_DATA =              (SERVER = DEDICATED)              (SERVICE_NAME = orcl)            )          )        * TEST为自定义名称,前面不能有空格        * Host = 192.168.123.11 为IP地址        * PORT = 1521 为端口        * SERVICE_NAME 为实例名称    将上述内容添加到%oracle%/network/ADMIN/tnsnames.ora中    打开spldeveloper尝试连接其它    oracle插入大数据的方式        1. 并行插入        2. 数据泵    Oracle SQL Developer连接mySQL数据库        工具-->首选项-->数据库-->第三方JDBC驱动程序-->添加mysql-jdbc的jar包即可,在新建连接时即出现mysql选项    查看存储过程api        desc 程序包名称    查看数据库参数(管理员)        show parameter 参数名称  --会进行模糊查询    修改数据库参数(管理员)        alter system set 参数名=值 scope=作用域;    scope的取值:        memoery : 修改当前数据库,不修改配置文件,下次启动会还原        spfile : 修改配置文件,不改当前数据库        both : 当前数据库和配置文件都会修改问题    sqlDeveloper连接不上oracle数据库        提示 Io 异常 The Network Adapter could not establish the connection        解放办法:使用oracle配置和移植工具NetManager(下面可能不是最简洁的配置)            修改了监听程序和服务命名中orcl的主机名            修改了监听程序中的数据库服务为orcl数据库    sqlDeveloper连接不上oracle数据库        无提示,卡住        解决办法:将监听服务重启一下