orcale重点资料.doc

-/pengpenglin/category/28499.html-oracle 开发网页链接1.在oracle中数据库由一个或多个表空间组成;表空间由一个或多个数据文件组成。表空间和表空间之间是完全独立的，一个数据文件只能从属于一个表空间。一张表可能跨越多个数据文件存放，但不能跨表空间存放2.归档日志文件完全是redo日志文件的备份。只有在需要恢复的时候才会用到归档日志文件。3 .Oracle数据库的一个重要部分是它的系统表。在数据库创建时产生这些系统表，系统表总是属于sys用户的，并且所有用户对某些系统表都有一定的权限，但用户是不能手工修改这些系统表的。系统表也被称做Oracle的数据字典（Data Dictionary）。数据字典是一系列只读的表（tables），用来描述数据库中实体的信息。当其中一个实体被建立、修改或取消时，数据库将自动修改数据字典。数据字典可以保存的信息如下：1. Oracle用户的用户名。2. 每个用户的权限（privilege）和角色(role)。3. 各种表、视图、索引等的名字4. 系统的规则信息。5. 各个字段的缺省值。分配的内存空间量和当前使用量Oracle中的raw类型Raw类型类似于char，varchar，不同点是里面存储的是2进制数据对象名和列名不区分大小写Lob类型：large object大数据对象Oracle 中的变量：1在建表时使用的数据类型：比如：number,varchar2等常用的数据类型2在pl/sql语句块中使用的变量比如：type rowtype，record,boolean , 替代变量，绑定变量，表类型替代变量： &要替换内容作用：提供用户的输入的界面，在用户输入数据以后，先替换pl/sql脚本，再编译执行。&变量 并不是一个真正的变量，他只是提供一个字符串的替换功能。绑定变量:在使用的时候声明变量variable XXX;（推荐使用提升效率）然后使用：XXX作为占位符替代变量的使用方法:案例一：输入年龄然后打印出来Declare v_age number(3);Begin v_age:=&年龄；Dbms_output.put_line(您的年龄是=|v_age);End;/替换变量小结：替换变量的作用只是为匿名块提供一个数据的录入界面替换变量只是完成字符串的替换功能，不保存值如果替换变量是字符串，则加上单引号例如：&姓名table类型table类型叫表类型，又叫索引表类型，注：他是pl/sql中的tablel类型，而不是数据库对象.语法：type 类型名 is table of 值类型 index by 键类型值类型：实际保存数据的类型，键类型有两种：binary_integer或者pls_integerBinary_integer 类型变量的值计算是由oracle来执行的,不会出现溢出，但是执行的速度慢Pls_integer的执行是由硬件即直接由cpu来运算，会出现溢出，但是执行的效率要比前者块很多。数据库的逻辑对象方案（schame）是一组数据库对象的集合，在创建用户的时候，会同时生成一个与用户同名的方案，此方案归同名的用户所有。簇：有些表共享公共的列，并经常被同时访问，为了提高数据存取的效率，把这些表在物理上存储在一起，得到的表的组合就是簇。与索引相似，簇并不影响应用程序的设计。用户和应用程序并不关心表是否是簇的一部分，因为无论表在不在簇中，访问表的SQL语句都是一样的。创建用户：q 每个用户都有一个默认表空间和一个临时表空间CREATE USER MARTIN -用户名IDENTIFIED BY martinpwd -密码DEFAULT TABLESPACE USERS -指定默认的表空间TEMPORARY TABLESPACE TEMP; -临时表空间Rank与dense_rankrank()对表中的数据进行分级排序.譬如有张学生成绩统计单的表STUDENT_RESULTname number kemu fenshuli 0113101 高数 90zhang 0113098 高数 80wang 0113077 高数 70li 0113101 物理 80zhang 0113098 物理 90wang 0113077 物理 70我如果要检索出表中高数的前两名和物理的前两名那么可以通过使用 RANK()方法达到目的-首先 通过kemu进行分类，然后按照fenshu降序排序select rank() over(partition by kemu order by fenshu desc) rk,t.* from student_result t结果rk name number kemu fenshu1 li 0113101 高数 902 zhang 0113098 高数 803 wang 0113077 高数 701 zhang 0113098 物理 902 li 0113101 物理 803 wang 0113077 物理 70-然后，检索出rk=2的记录，即取出前两名select * from (select rank() over(partition by kemu order by fenshu desc) rk,t.* from student_result t) as y where y.rk=2;结果rk name number kemu fenshu1 li 0113101 高数 902 zhang 0113098 高数 801 zhang 0113098 物理 902 li 0113101 物理 80dense_rank()和 rank()的用法完全相同，不同的是在出现分数相同的情况下如name number kemu fenshuli 0113101 高数 80zhang 0113098 高数 80wang 0113077 高数 70li 0113101 物理 80zhang 0113098 物理 90wang 0113077 物理 70select rank() over(order by fenshu desc) rk,t.* from student_result t的结果为rk name number kemu fenshu1 zhang 0113098 物理 902 li 0113101 高数 802 zhang 0113098 高数 802 li 0113101 物理 805 wang 0113077 高数 705 wang 0113077 物理 70而select dense_rank() over(order by fenshu desc) rk,t.* from student_result t结果为rk name number kemu fenshu1 zhang 0113098 物理 902 li 0113101 高数 802 zhang 0113098 高数 802 li 0113101 物理 803 wang 0113077 高数 703 wang 0113077 物理 70附 row_number()的使用select row_number() over(order by fenshu desc) rk,t.* from student_result trk name number kemu fenshu1 zhang 0113098 物理 902 li 0113101 高数 803 zhang 0113098 高数 804 li 0113101 物理 805 wang 0113077 高数 706 wang 0113077 物理 70fetch n rows only取出当前记录的前多少行如select row_number() over(order by user_no) from user_files fetch first 5 rows only;查看数据库中的标的信息：desc XXX -desc 表的名字Oracle 的新特征（只是包含一部分）（1） 引入了新的pl/sql 编译器（2） 通过indicesof 子句和values of子句就可以允许在pl/sql集合类型上使用非连续下标。（3） 提供了新的数据类型binary_float和binary_double分别用于定义单精度和双精度的浮点数。这两种类型主要用于高速的科学计算。赋值时必须使用1.5f或3.005d.（4） 如果在字符数值中需要包含单引号，那样开发人员可以在字符串中使用其他分隔符（，【】，）包围字符串，这样开发人员可以在字符串中直接将单引号作为字符值使用。Pl/sql语句书写的格式Declare/* *定义变量，常量，复杂数据类型，游标*/Begin/* *执行部分，pl/sql语句，或sql 语句*/Exception /*/例外部分，处理运行错误End; -块结束标记，end后面必须有分号包用于逻辑组合相关的过程和函数，他由包规范和包体两部分组成，包规范用于定义公用的变量，常量，过程和函数。包规范只是包含了过程和函数的说明，而没有过程和函数的实现代码。在sql/plus中建立包规范可以使用create package,建立包体可以使用create package body使用nvl2(expr1,expr2,expr3)-如果expr1不是null 返回expr2,如果是null则返回expr3.Pl/sql中的table 类型类似于java里面的map类型是键值对，在此可以当做可变长数组理解，不同于数据库里面的table。Decode函数,在oracle pl/sql中decode具有和if-then-else一样的功能语法如下：decode(expression,search,result ,search,result ,default)Expression -要比较的表达式Rollup 与cube函数的使用在直接使用group by 执行数据统计时，只会产生列的对应的数据统计，如果还希望产生纵向和横向的统计可以使用 rollup 和cube 函数.在使用rollup操作符时，在原先的统计结果上还会生成横向小计；在使用cube操作符时在rollup的统计结果基础之上，还会产生纵向小计。例如：rollupCube操作符：使用grouping 函数用于确定统计结果是否用到了特定的列，如果函数返回0，表示结果使用到了列，如果函数返回1表示统计结果未使用该列。Grouping Set可以合并多个分组的结果批量绑定是oracle9i新增的特点当在pl/sql应用程序中执行批量insert update ,delete时可以使用forall语句。从oracle10g开始通过使用新增加的indices of 和values of子句可以使用不连续的集合元素（注意for语句是循环语句，forall语句不是循环语句）语法一：forall index in lower_bound . .upper_bound sql_statement;Index 为隐含定义的整数变量。Indices为index 的复数形式语法二：forall index in indices of collection （indices of 子句用于跳过null集合元素）between lower_bound and upper_bound sql_statement语法三： forall index in values of index_collection sql_statement; （values of 用于从其他集合中取得集合下标（index值）例：在insert语句中使用批量绑定在使用批量绑定为数据库表插入数据时，首先要给集合元素赋值，然后使用forall语句执行批量绑定插入操作。下面将10条记录批量插入到demo 表。Declare Type id_table_type is table of number(6) index by binary_integer;Type name_table_type is table of varchar2(10) index by binary_integer;Id_table id_table_type;Name_table name_table_type;Begin for i in 1.10 loop -为表变量赋初值id_table(i):=i;name_table(i) :=Name|to_char(i);end loop;forall iin 1.id_table.count insert into demo values (id_table(i),name_table(i); -批量绑定插入end; /使用id_table去删除demo表的id值为1，3，5的数据为例Declare type id_table_type is table of number(6);Id_table id_table_type;Begin Id_table:=id_table_type(1,null,3,null,5); -集合类型初始化Forall i in indices of id_table Delete from demo where id=id_table(i); -删除指定元素忽略nullEnd; /Bulk collect 子句用于取得批量数据，它只适用于select into ,fetch into,和dml。通过使用该子句可以将批量数据存放在pl/sql 集合变量中。例：Declare type emp_table_type is table of emp%rowtype index by binary_integer;emp_table emp_table_type;begin select * bulk collect into emp_table from emp where deptno=&no;for i in 1.emp_table.count loopdbms_output .put_line(雇员的姓名：|emp_table(i).ename)end loop;end; / cursor 分类： 从总体是分cursor分为静态游标，动态游标 。静态游标分为：隐式游标（自动打开或关闭其名为sql），显示游标。动态游标即：ref cursor其又分为：强类型(规定返回类型，返回类型必须是记录类型)，弱类型（不规定返回类型）静态游标与动态游标的几点区别：（1） 静态游标不能返回客户端，只有pl/sql才能使用他，ref cursor能够被返回到客户端（2） 静态游标可以是全局的，而ref则不能（3） Ref可以从子例程传递到子例程，而cursor 不可以（4） 静态光标比ref光标效率高；ref的使用限于以下几种情况：把结果集返回给客户端在多个子例程之间共享光标等情况。也可以使用显示游标来更新行，但是声明游标时必须使用select.for update 形式Cursor 表达式：语法cursor(subquery)在oracle9i 以前使用显示游标结果集只能包含普通数据，之后可以包含嵌套游标的数据。示例如下：Declare Type refcursor is ref cursor;Cursor dept_cursor(no number) is select a.dname,cursor(select ename,sal from emp where deptno=a.deptno) from dept a where a.deptno=no;Empcur refcursor; - - - - 定义动态的游标变量V_dname dept.dname%type;V_ename emp.ename%type;V_sal emp.sal%type;Begin open dept_cursor(&no);Loop -外层循环Fetch dept_cursor into v_dname,empcur; exit when dept_cursor%not FOUND;Dbms_output.put_line(部门名：|v_dname);Loop -内层循环Fetch empcur into v_ename ,v_sal;Exit when empcur%notfound;Dbms_output.put_line(雇员名|v_ename|,工资:|v_sal);End loop;End loop;Close dept_cursor;End; /数据字典例： Select constraint_name,table_name, constraint_type from user_constraints;其中constraint_type取值的含义如下： Ccheck Pprimary key Uunique Rreferential Oview with read only例：alter table emp drop constraint PK_emp;To_char(sysdate,DY,nls_date_language=AMERICAN)含义DY three letter abbreviation of the day of the week星期的前3个字母NLS-Native Language SupportPl/sql集合 例如可以存放多个雇员的姓名，即多行单列：索引表：从语法上来看 type XXX is table of XXX index by XXX; 下标可以为负数嵌套表：type XXX is table of XXX；在使用前，必须要使用构造函数初始化，下标从1开始,而且可以作为表列的数据类型使用。变长数组：type XXX is varray(size_limit) of XXX; 在使用前，必须要使用构造函数初始化，下标从1开始，,而且可以作为表列的数据类型使用。学习分析函数：select deptno,sal,sum(sal) over(order by deptno asc rows between unbounded preceding and current row) 积累工资总和 from scott.emp ;运算结果本例题重点是研究汇总：sum(sal) over(order by deptno asc rows between unbounded preceding