1.Oracle内容介绍: (1)基本部分:oracle基本使用、用户管理、表管理 (2)高级部分:表的查询、权限和角色、函数、PL/SQL编程、数据库管理、索引,约束 和事务
2.安装oracle系统要求: (1)操作系统最好为windows 2000 (2)内存最好在256M以上 (3)硬盘空间需要2G以上
3.Oracle安装: (1)使用DAEMON Tools设置三个虚拟光驱,分别对应三个ISO安装文件,然后可以进行点 击光盘图形的setup.exe进行安装 (2)选择安装产品时,有三个选项,我们一般装第一个(Database 9.0),第二个是客户 端,第三个是管理工具 (3)选择安装类型时,可以选择企业版 (4)数据库配置:常选择“通用” (5)数据库标识:全数据库名:ding.jiaxue或myOra1(不能超过8个字符) (6)数据库字符集:使用缺省字符集 (7)oracle安装会自动生成sys用户和system用户,sys是超级用户,默认密码是manager, 具有最高权限,具有sysdba角色;system用户是管理操作员,权限也很大,默认密 码是change_on_install具有sysoper角色,没有create database权限。
4.oracle的启动: (1)我的电脑--->管理---->服务,启动OracleService+"数据库名"(实际上就是启动了 Oracle的一个实例)和...Listener (2)①Oracle自带的客户端 SQL*Plus ②在运行栏中输入:sqlplus或者在Oracle主目录下\ora90\bin\sqlplus.exe,鼠标 双击即可 ③oracle的企业管理器(oem oracle enterprise manager)---->独立启动 ④常用的登录软件:PL/SQL developer,常用语开发、测试、优化Oracle的存储过程 5.连接命令: (1)conn[ect] 用法:conn用户名/密码@网络服务名[as sysdba/sysoper] (2)disc[onnect]: 说明:该命令用来断开与当前数据库的连接
(3)passw[ord] 说明:该命令用于修改用户的密码。如果想修改其它用户的密码,需要使用 sys/system登录 (4)show user:显示当前用户 (5)exit:该命令会断开与数据库的连接,同时会退出sql*plus
6.文件操作命令: (1)start和@ 说明:运行sql脚本 例如:start d:\a.sql
(2)edit:该命令可以编辑指定路径sql脚本
(3)spool:该命令可以将sql*plus屏幕上的内容输出到指定文件中去。 例如:sql>spool d:\b.sql 并输入 sql>spool off
(4)set linesize 和set pagesize:设置行大小和页大小
7.用户管理: (1)创建用户:create user 用户名 identified by 密码. (2)给用户修改密码: sql>password 用户名
(3)删除用户:drop user 用户名 [cascade] 注意:在删除用户时,如果要删除的用户已经创建了表,那么就需要在删除时带一个 参数cascade。 (4)授予权限:grant 权限 on 表 to 用户 (5)收回权限:revoke 权限 on 表 from 用户 8.使用profile管理用户口令: 概述:profile是口令限制,资源限制的命令集合,当建立数据库时,oracle会自 动建立名称为default的profile。当建立用户没有指定profile选项,那oracle就会将 default分配给用户。 (1)账户锁定: 概述:指定该账户(用户)登陆时最多可以输入密码的次数,也可以指定用户锁定 的时间(天)一般用dba的身份去执行该命令。例如:指定tea这个用户最多只能尝试3次 登录,锁定时间为2天。 sql>create profile lock_account limit failed_login_attempts 3 password_lock_time 2; sql>alter user tea profile lock _account;
(2)给账户(用户)解锁: sql>alter user tea account unlock;
(3)终止口令: 为了让用户定期修改密码可以使用终止口令的指令来完成。例如:给前面创建的 用户tea创建一个profile文件,要求该用户每隔10天要修改自家的登录密码,宽限期 为2天。sql>create profile myprofile limit password_life_time 10 password_ grace_time 2; sql>alter user tea profile myprofile (4)删除profile: 概述:当不需要某个profile时,可以删除profile。 sql>drop profile password_history [cascade]
9.oracle表名和列的命名规则: (1)必须以字母开头 (2)长度不能超过30个字符 (3)不能使用oracle的保留字 (4)只能使用如下字符:A-Z,a-z,0-9,$,#等
10.字符型: (1)char 定长 最大2000字符 例如:char(10) '小韩',前面4个字符放'小韩',后面6个补空格。 (2)varchar2 变长 最大4000字符 (3)clob(character large objiect) 字符型大对象 最大4G
11.数字型: (1)number 范围-10的38次方——10的38次方 可以表示整数,也可以表示小数 (2)number(5,2) 表示一个小数有5位有效数,2位小数 范围:-999.99——999.99 (3)number(5) 表示一个五位整数 范围:-99999——99999
12.日期类型: (1)date 包含年月日和时分秒 (2)timestamp 这是oracle9i对date数据类型的扩展
13.图片类型 blog:二进制数据 可以存放图片/声音/视频 4G
14.修改表: (1)添加一个字段: sql>alter table student add(classid number(2)) (2)修改字段的长度: sql>alter table student modify(xm char(30)) (3)修改字段的类型/或是名字(不能有数据) sql>alter table student modify (xm char(30)) (4)删除一个字段: sql>alter table student drop column sal; (5)修改表的名字: sql>rename student to stu; (6)删除表: sql>drop table student;
15.删除数据: (1)delete from student; 删除所有记录,表结构还在,写日志,可以恢复的,速度慢
(2)drop table student;删除表的结构和数据 (3)delete from student where xh='A001'删除一条记录 (4)truncate table student;删除表中的所有记录,表结构还在,不写日志,无法找回 删除的记录,速度快。
16.简单的查询语句: (1)查看表结构:sql>desc dept; (2)消除重复行:select distinct deptno,job from emp; (3)显示操作时间:set timing on;
17.Oracle 分页 (1)rownum分页: select al.*,rownum rn from (select * from emp) al where rownum<=10; (2)根据rowid来分,太复杂,不推荐 (3)按分析函数来分,效率比较低
18.java中操作Oracle (1)使用jdbc-odbc方式连接数据库(配置数据源): ①加载驱动:Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); ②得到连接:Connection ct=DriverManager.getConnection("jdbc:odbc:数据库 名","sa","密码"); ③执行SQL语句 ④注意:此种连接方式不能用于远程连接
(2)使用Jdbc方式连接: ①加载驱动:Class.forName("oracle.jdbc.driver.OracleDriver"); ②得到连接:Connection ct=DriverManager.getConnection ("jdbc:oracle:thin:@127.0.0.1:1521:数据库实例","用户名","密码");
(3)引进Oracle驱动:classes12.jar
19.事务: 事务用于保证数据的一致性,它由一组相关的dml(数据操作语句,增、删、改)语 句组成,该组的dml语句要么全部成功,要么全部失败。
20.事务和锁: 当执行事务操作(dml语句)时,oracle会在被作用的表上加锁,防止其它用户改表 的结构。
21.提交事务: 当执行使用commit语句时可以提交事务。当执行了commit语句子后,会确认事务 的变化、结束事务、删除保存点、释放锁,当使用commit语句结束事务子后,其它会 话将可以查看到事务变化后的新数据。
22.事务的几个重要操作: (1)设置保存点: savepoint a (2)取消部分事务:rollback to a (3)取消全部事务: rollback
23.在Java中加入事务处理: 加入事务处理:ct.setAutoCommit(false);(ct 是Connection实例) 提交事务:ct.commit();
24.只读事务: (1)只读事务是指只允许执行查询的操作,而不允许执行任何其他dml操作的事务,使用 只读事务可以确保用户只能取得某时间点的数据。
(2)设置只读事务: set transaction read only
25.oracle中的sql函数:字符函数 (1)字符函数是oracle中最常用的函数。 (2)lower(char):将字符串转化为小写的格式 (3)upper(char): 将字符串转化为大写的格式 (4)length(char):返回字符串的长度 (5)substr(char ,m,n):取字符串的字串,从m开始,取长度为n的字串 (6)replace(char1,search_string,replace_string),在字符串char1中,将 search_string替换成replace_string.
26.数学函数: 数学函数的输入参数和返回值的数据类型都是数字类型的。数学函数包cos, cosh,exp,ln,log,sin,sinh,sqrt,tan,acos,asin,atan,round等。最常用的: (1)round(n,[m]) 该函数用于执行四舍五入,如果省掉m,则四舍五入到整数;如果m 是正数,则四舍五入到小数点的m位后。如果m是负数,则四舍五入到小数点的m位前。 (2)trunc(n,[m]) 该函数用于截取数字。如果省掉m,就截去小数部分,如果m是正数就 截取到小数点的m位后;如果m是负数,则截取到小数点的前m位。
(3)mod(m,n):取模
(4)floor(n):返回小于或是等于n的最大整数 (5)ceil(n): 返回大于或是等于n的最小整数 (6)abs(n): 返回数字n的绝对值 (7)acos(n): 返回数字的反余弦值 (8)exp(n):返回e的n次幂 (9)power(m,n):返回m的n次幂
27.日期函数: 日期函数用于处理date类型的数据,默认情况下日期格式是dd-mon-yy。 (1)sysdate:该函数返回系统时间 (2)add_months(d,n),把时间列d加上n个月: 例:查询入职八个月的员工:select * from emp where sysdate>add_months (hiredate,8)
28.转换函数 转换函数用于将数据类型从一种转换为另外一种,在某些情况下,oracle允许值的 数据类型和实际的不一样,这时oracle会隐含的转化数据类型。
例:to_char转换成字符
29.sys和system账户的区别 (1)最重要的区别,存储的数据的重要性不同 sys:所有oracle的数据字典的基表和视图都存放在sys用户中,这些基表和视图 对于oracle的运行是至关重要的,由数据库自己维护,任何用户都不能手动更改。 sys用户拥有dba,sysdba,sysoper角色或权限,是oracle权限最高的用户。
(2)其次的区别,权限的不同: sys用户必须以as sysdba或as sys oper形式登录。不能以normal方式登录数据 库。system如果正常登录,它其实就是一个普通的dba用户,但是如果以as sysdba登 录,其结果实际上是作为sys用户登录的。
30.sysdba,sysoper,dba权限的区别: (1)sysdba拥有的权限:Startup(启动数据库),ShutDown(关闭数据库),alter database open/mount/backup,改变字符集,create database,dropdatabase, create spfile,alter database archivelog(归档日志),alter database recover( 恢复数据库),拥有restricted session(会话限制)权限,可以让用户作为sys用户连 接,登录之后用户是sys。 (2)sysoper拥有的权限:startup,shutdown,alter databaseopen/mount/ backup,create spfile,alter database archivelog,只能完全恢复数据库(不能部分 恢复),拥有restricted session权限,可以让用户作为sys用户连接(但是不能查看用 户数据),登录之后用户是public
(3)dba角色:dba用户是指具有dba角色的数据库用户。特权用户可以执行启动实 例,关闭实例等特殊操作,而dba用户只有在启动数据库后才能执行各种管理工作。
30.数据库的备份 (1)逻辑备份:是指使用工具export将数据对象的结构和数据导出到文件的过程, 逻辑恢复是指当数据库对象被误操作而损坏后使用工具import利用备份的文件把数据 对象导入到数据库的过程。
(2)物理备份即可在数据库open的状态下进行,也可在关闭数据库后进行,但是逻 辑备份和恢复只能在open状态下进行。
31.导出: 导出具体分为三种:导出表,导出方案,导出数据库。导出使用exp命令来完成, 该命令常用的选项有: (1)userid:用于指定执行导出操作的用户名,口令,连接字符串。 (2)tables:用于指定执行导出操作的表 (3)owner:用于指定执行导出操作的方案 (4)full=y:用于指定执行导出操作的数据库 (5)inctype:用于执行执行导出操作的增量类型 (6)rows:用于指定执行导出操作是否要导出表中的数据 (7)file:用于指定导出文件名
32.导出表: (1)导出自己的表: exp userid=Scott、tiger@myora1 tables=(表名1,表名2) file=d:\e1.emp 注意:在导入和导出的时候,要到oracle目录的bin目录下。
(2)导出其他方案的表: 如果用户要导出其他方案的表,则需要dba的权限或是exp_full_database的权 限,比如system就可以导出scott的表。exp userid=system/manager@myora1 tables=(scott.emp) file=d:\e2.dmp
(3)导出表的结构: exp userid=scott/tiger@accp table=(emp) file=d:\e3.dmp rows=n
(4)使用直接导出方式: exp userid=scott/tiger@acc tables=(emp)file=d:\e3.dmp direct=y 这种方式比默认的常规方式速度要快,当数据量大时,可以考虑使用这样的方法, 这时需要数据库的字符集与客户端字符集完全一致,否则会报错。
33.导出方案: 导出方案是指使用export工具导出一个方案或是多个方案中的所有对象(表、索 引、约束。。)和数据,并存放到文件中。
(1)导出自己的方案: exp scott/tiger@myora1 owner=scott file=d:\scott.dmp
(2)导出其它方案: 如果用户要导出其它方案,则需要dba的权限或是exp_full_database的权限, 例如:system用户就可以导出任何方案,exp system/manager@myor owner= (system,scott) file=d:\system.dmp
34.导出数据库: 导出数据库是利用export导出所有数据库中的对象及数据,要求该用户具有dba 的权限或是exp_full_database权限。exp userid=system/manager@myor1 full=y inctye=complete file=x.dmp
35.数据字典: (1)数据字典是oracle数据库中最重要的组成部分,它提供了数据库的一些系统 信息。它是只读表和视图的集合,数据字典的所有者为sys用户。用户只能在数据字典 上执行查询操作,而其维护和修改是由系统自动完成的。 (2)数据字典的组成:数据字典包括数据字典基表和数据字典视图,其中基表存储 数据库的基本信息,普通用户不能直接访问数据字典的基表。数据字典视图是基于数 据字典基表所建立的视图,普通用户可以通过查询数据字典视图取得系统信息。
(3)数据字典视图主要包括user_xxx,all_xxx,dba_xxx三种类型。
36.(1)user_tables:用于显示当前用户所拥有的所有表,它只返回用户所对应方案的表。 比如:select table_name from user_tables;
(2)all_tables:用于显示当前用户可以访问的所有表。它不仅会返回当前用户方案的 所有表,还会返回当前用户可以访问的其它方案的表。 (3)dba_tables:它会显示所有方案拥有的数据库表。但是查询这种数据库字典视图, 要求用户必须是dba角色或是有select any table系统权限。例如:当用system用 户查询数据字典视图dba_tables时,会返回system,sys,scott...方案所对应的 数据库表。
37.用户名,权限,角色 在建立用户时,oracle会把用户的信息存放到数据字典中,当给用户授予权限 或是角色时,oracle会将权限和角色的信息存放到数据字典。
(1)通过查询dba_users可以显示所有数据库用户的详细信息; (2)通过查询数据字典视图dba_sys_privs,可以显示用户具有的系统权限。 (3)通过查询数据字典视图dba_tab_privs可以显示用户具有的对象权限。 (4)通过查询数据字典dba_col_privs可以显示用户具有的列权限。 (5)通过查询数据库字典视图dba_role_privs可以显示用户所具有的角色。
38.查询一个角色包括的权限 (1)一个角色包含的系统权限: select * from dba_sys_privs where grantee='CONNECT' 或者select * from role_sys_privs where role='CONNECT'
(2)一个角色包含的对象权限: select * from dba_tab_privs where grantee='CONNECT'
(3)oracle究竟有多少种角色: select * from dba_roles;
(4)查看某个用户具有的角色: select * from dba_role_privs where grantee='SCOTT'
(5)显示当前用户可以访问的所有数据字典视图: select * from dict where comments like '%grant%'
(6)显示当前数据库的全称: select * from global_name;
39.数据字典的其它说明: 数据字典记录有数据库的所有系统信息。通过查询数据字典可以取得以下系统信 息。比如: (1)对象定义情况 (2)对象占用空间大小 (3)列信息 (4)约束信息 这些信息也可以通过pl/sql developer工具查询得到。
40.动态性能视图: 动态性能视图用于记录当前例程的活动信息,当启动oracle server时,系统会建 立动态性能视图;当停止oracle server时,系统会删除动态性能视图。 oracle的所有动态性能视图都是以v_$开始的,并且oracle为每个动态性能视图都 提供了相应的同义词,并且其同义词是以V$开始的。例如v_$datafile的同义词为 v$datafile;动态性能视图的所有者为sys,一般情况下,由dba或是特权用户来查询动 态性能视图。
41.表空间: 表空间是数据库的逻辑组成部分。从物理上讲,数据库数据存放在数据文件中; 从逻辑上讲,数据库则是存放在表空间中,表空间由一个或是多个数据文件组成。
(1)建立表空间: 建立表空间是使用create tablespase命令完成的,需要注意的是,一般情况下, 建立表空间是特权用户或是dba来执行的,如果用其他用户来创建表空间,则用户必 须具有create tablespace的系统权限。
(2)建立数据表空间: 在建立数据库后,为便于管理表,最好建立自己的表空间create tablespace data01 datafile 'd:\test\data01.dbf' size 20m uniform size 128k 说明:执行完上述命令后,会建立名称为data01.dbf的数据文件,区的大小为128k (3)使用数据表空间 create table mypart(deptno number(4),dname varchar2(14),loc varchar2 (13) tablespace data01)
42.其他表空间: 除了最常用的数据表空间外,还有其它类型表空间: (1)索引表空间 (2)undo表空间 (3)临时表空间 (4)非标准块的表空间
43.数据库的完整性用于确保数据库数据遵从一定的商业和逻辑规则。在oracle中,数据 完整性可以用约束、触发器、应用程序(过程、函数)三种方法来实现,在这三种方法 中,因为约束易于维护,并且具有最好的性能,所以作为维护数据完整性的首选。
44.约束: 约束用于确保数据库数据满足特定的商业规则。在oracle中,约束包括:not null、unique、primary key、foreign key 和 check五种。
45.索引:索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低i/o次数, 从而提高数据访问性能。
46.索引的使用原则: (1)在大表上建立索引才有意义 (2)在where子句或是连接条件上经常引用的列上建立索引 (3)索引的层次不要超过4层
47.创建索引: (1)单列索引: 单列索引是基于单个列所建立的索引,比如: create index 索引名 on 表名(列名) (2)复合索引: 复合索引是基于两列或是多列的索引。在同一张表上可以有多个索引,但是要 求列的组合必须不同。比如: create index emp_idx1 on emp(ename,job) create index emp_idx1 on emp(job,ename)
48.索引的缺点分析: (1)建立索引,系统要占用大约为表的1.2倍的硬盘和内存空间来保存索引。 (2)更新数据的时候,系统必须要有额外的时间来同时对索引进行更新,以维持数据和 索引的一致性。 (3)实践表明,不恰当的索引不但于事无补,反而会降低系统性能。因为大量的索引在 进行插入、修改和删除操作时比没有索引花费更多的系统时间。
49.显示索引信息: (1)显示表的所有索引 在同一张表上可以有多个索引,通过查询数据字典视图:dba_indexs user_ indexs,可以显示索引信息。其中dba_indexs用于显示数据库所有的索引信息,而 user_indexs用于显示当前用户的索引信息:select index_name,index_type from user_indexs where table_name='表名'; (2)显示索引列: 通过查询数据字段视图user_ind_columns,可以显示索引对应的列的信息 select table_name,column_name from user_ind_columns where index_name ='IND_ENAME'
50.常用的预定义角色: (1)connect角色: connect角色具有一般应用开发人员需要的大部分权限,当建立了一个用户后, 多数情况下,只要给用户授予connect和resource角色就够了。connect角色具有的 系统权限有:alter session,create cluster,create database link,create session,create table,create view,create sequence.
(2)resource角色: resource角色具有应用开发人员所需要的其它权限,比如建立存储过程、触发 器等。这里需要注意的是resource角色隐含了unlimited tablespace系统权限。 resource角色包含以下系统权限:create cluster,create indextype,create table,create sequence,create type,create procedure,create trigger
(3)dba角色: dba角色具有所有的系统权限,及with admin option选项,默认的dba用户为 sys和system,他们可以将任何系统权限授予其它用户。但是要注意的是dba角色不具 备sysdba和sysoper的特权(启动和关闭数据库)
51.编写一个存储过程,该过程可以想某表中添加记录: (1)创建表:create table mytest(name varchar2(30),passwd varchar2(30)) (2)创建过程: create procedure sp_pro1 is begin --执行部分 insert into mytest values('韩顺平','m1234'); end; / (3)显示错误:show error; (4)调用过程:有两种方法: exec sp_pro1或call 过程名(参数值1,参数值2)
52.PL/SQL标识符号的命名规范: (1)当定义变量时,建议用v_作为前缀 v_sal (2)当定义常量时,建议用c_作为前缀 c_rate (3)当定义游标时,建议用_cursor作为后缀emp_cursor (4)当定义例外时,建议用e_作为前缀 e_error
53.pl/sql是以块(block)为基本单位的,编写pl/sql程序实际上就是编写pl/sql块。要完 成相对简单的应用功能,可能只需要编写一个pl/sql块;但是如果要想实现复杂的功 能,可能需要在一个pl/sql块中嵌套其它的pl/sql块。
54.pl/sql块基本构成: (1)定义部分:declare--定义常量、变量、游标、例外、复杂数据类型 (2)执行部分:begin--要执行的pl/sql语句和sql语句 (3)例外处理部分:exception--处理运行的各种错误---end;
55.简单输出: (1)set serveroutput on/off---打开/关闭输出选项 (2)begin dbms_output.put_line('Hello'); end; / (3)dbms_output是oracle所提供的包(类似Java的开发包),该包包含一些过程, put_line就是dbms_output包的一个过程。
56.包含定义和执行部分的pl/sql块: declare v_name varchar2(5); ---定义字符串变量 begin select ename into v_ename from emp where empno=&no; dbms_output.put_line('雇员名:'||v_ename); end; / 注意:&表示要接收从控制台输入的变量; into v_enmae 表示把从控制台接收的数存 入数据库对应的字段。
57.过程: 过程用于执行特定的操作,当建立过程时,既可以输入参数(in),也可以指定输 出参数(out).通过在过程中使用输入参数,可以将数据传递到执行部分;通过使用输 出参数,可以将执行部分的数据传递到应用环境。
58.编写一个过程,可以输入雇员名,新工资 可修改雇员的工资 create procedure sp_pro3(spName varchar2,newSal number) is begin update emp set sal=newSal where ename=spName; end; / exec sp_pro3('SCOTT',4678);
59.函数: 函数用于返回特定的数据,当建立函数时,在函数头部必须包含return子句,而 在函数体内必须包含return语句返回的数据。我们可以使用create function来建立函 数。例如:输入雇员的姓名,返回该雇员的年薪: create function sp_fun2(spName varchar2) return number yearSal number(7,2); begin select sal*12+nvl(comm,0)*12 into yearSal from emp where ename=spName; end; /
调用: var abc number; call sp_fun2('SCOTT') into:abc;
60.包:包用于在逻辑上组合过程和函数,它由规范和包体两部分组成。我们可以使用 create or replace package sp_package is procedure update_sal(name varchar2,newSal number); function annual_income(name varchar2) return number; end; /
创建包体: create package body sp_package is.......
61.触发器: 触发器是指隐含的执行的存储过程。当定义触发器时,必须要指定触发的事件和 触发的操作,常用的触发事件包括insert,update,delete语句,而触发操作实际就 是一个pl/sql块。可以使用create trigger来建立触发器、
62.pl/sql程序中包括有: (1)标量类型(scalar) (2)复合类型(composite):用于存放多个值的变量。主要包括pl/sql记录,pl/sql 表,嵌套表,varray。类似与结构体 (3))参照变量:refcursor游标变量: 当定义游标时不需要指定相应的select语句,但是当使用游标(open)时需要指 定select语句,这样一个游标就与一个select语句结合了。
63.编写一个过程,可以输入一个雇员名,如果该雇员的工资低于2000,就给该雇员工资 增加10%. 示例:create or replace sp_pro5(spName varchar2) is ---定义 v_sal emp.sal%type; ---执行 begin select sal into v_sal from emp where ename=spName; ---判断 if v_sal<2000 then update emp set sal=sal*1.1 where ename=spName; end if; end;
64.多重条件分支语句: if --then--elsif--else语句
65.循环语句 --loop 是pl/sql中最简单的循环语句,这种循环语句以loop开头,以end up结尾,这种 循环至少会被执行一次。例:编写一个过程,可输入用户名,并循环添加10个用户到 users表中,用户编号从1开始增加 create or replace procedure sp_pro6(spName varchar2) is v_num number:=1; -----定义,=表示赋值 begin loop insert into users values(v_num,spName); exit when v_num=10; ----判断是否要退出循环 v_num:v_num+1; -----自增 end loop; end;
66.循环语句---while
67.编写分页:
---开发一个包,并定义游标类型test_cursor create or replace package testpackage as type test_cursor is ref cursor; end testpackage;
---开始编写分页的过程 create or replace procedure fenye (tableName in varchar2, PageSize in number, PageNow in number, myrows out number, myPageCount out number, p_cursor out testpackage.test_cursor ---返回的记录数 ) is
---定义部分 ---定义sql语句 字符串 v_sql varchar2(1000); v_begin number:=(pageNow-1)*pageSize+1; v_end number:=pageNow*PageSize; begin ---执行部分 v_sql:='select * from (select t1.*,rownum rn from (select * from'||tableName||') t1 where rownum<='||?||') where rn>='||?' ---把游标和sql关联 open p_cursor for v_sql; ---计算myrows和myPageCount ---组织一个sql v_sql:='select count(*) from'||tableName; ---执行sql,并把返回的值,返回给myrows execute immediate v_sql into myrows; ---计算myPageCount if mod(myrows,PageSize)=0 then myPageCount:=myrows/PageSize; else myPageCount:=myrows/PageZize; end if; ---关闭游标 close p_cursor; end;
68.pl/sql的例外: Oracle将例外分为预定义例外,非预定义例外和自定义例外三种。预定义 例外用于处理常见的Oracle错误,非预定义例外用于处理预定义例外不能处 理的例外,自定义例外用于处理与Oracle错误无关的其他情况。
69.常见预定义例外: (1)case_not_found:在开发pl/sql块中编写case语句时,如果在when子句 中没有包含必须的条件分支,就会出发case_not_found的例外. (2)cursor_already_open:当重新打开已经打开的游标时,会隐含的触发例外 (3)dup_val_on_index:在唯一索引所对应的列上插入重复的值时,会隐含的 触发该例外 (4)invalid_cursor:当试图在不合法的游标上执行操作时,会触发该例外 (5)invalid_number:当输入的数据有误时,会触发该例外 (6)no_data_found:当执行select into没有返回行,就会触发该例外 (7)too_many_rows:当执行select into语句时,如果返回超过了一行,则会触发该例外。 (8)zero_divide:当执行运算时,分母为0 (9)value_error:当执行赋值操作时,如果变量的长度不足以容纳实际数据,则会触发该 例外。
70.其他预定义例外: (1)login_denide:当用户非法登陆时,会触发该例外 (2)not_logged_on:如果用户没有登陆就执行dml操作,就会触发该例外。 (3)storage_error:如果超出了内存空间或是内存被损坏,就触发该例外 (4)timeout_on_resource:如果Oracle在等待资源时,出现了超时就触发该例外。
71.Oracle的视图: 视图是一个虚拟表,其内容由查询定义。同真实的表一样,视图包含 一系列带有名称的列和数据。但是,视图并不在数据库中以存储的数据值集 形式存在。行和列数据来自由定义视图的查询所引用的表,并在引用视图时动态 生成。
72.视图和表的区别: (1)表需要占用磁盘空间,视图不需要 (2)视图不能添加索引 (3)使用视图可以简化复杂查询 (4)视图的使用可以提高安全性
73.创建视图: create view 视图名 as select语句 [with read only] 例:create view myview as select * from emp where sal<1000;
74.创建或修改视图: create or replace view 视图名 as select 语句[with read only]
75.删除视图 drop view 视图名