`
webdev2014
  • 浏览: 670089 次
文章分类
社区版块
存档分类
最新评论

oracle日记

 
阅读更多

win7旗舰版安装oracle11g 64位用sql plus可以登录 但用pl/sql dev报错ORA-12560

小型数据库:access、foxbase
中型数据库:mysql、sqlserver、informix
大型数据库:sybase、oracle、db2--sybase<oracle<db2
oracle认证:dba(database administrator);java开发认证;oracle网络认证
Oracle OCA介绍:
Oracle10g Certified Associate (OCA)为Oracle公司的数据库助理工程师的认证,又称之为Oracle技术入门级专业证书,拥有OCA认证说明你拥有了大型Oracle数据库管理的入门基础。
Oracle OCP介绍:
Oracle10g Certified Professional(OCP)为Oracle公司的数据库工程师的认证。拥有OCP认证说明你拥有了大型Oracle数据库管理的技术能力。具备了成为大型企业核心数据库系统工程师的资格。掌握了大型Oracle数据库在Linux/Unix平台上的备份、高级配置、优化等高级维护技术。有资格成为大型数据库系统核心工程技术人员。
Oracle OCM介绍:
Oracle Certified Master(OCM) 大师认证资质是Oracle认证的最高级别。Oracle认证大师是解决最困难的技术难题和最复杂的系统故障的最佳Oracle专家人选。资深专家级Oracle 技能考试,通过后将成为企业内的资深专家和顾问。OCM 不但有能力处理关键业务数据库系统和应用,还能帮助客户解决所有的Oracle 技术困难。要想获得OCM 证书,必须先通过OCA、OCP考试,再学习两门高级技术课程,然后在Oracle 实验室通过场景实验考试。场景实验考试的目的是测试您的实际问题分析和故障解决能力。
oracle管理口令:Oracle11g
start和@:运行sql脚本
edit:编辑指定的sql脚本
spool:可将sql plus屏幕上的内容输出到指定文件中去
显示和设置环境变量:linesize pagesize
权限可分为系统权限(用户对数据库的相关权限:如建库、建表、建索引、建存储过程、登录数据库、修改密码等)和对象权限(用户对其他用户的数据对象(比如表、视图、存储过程...)访问或操作的权限:select、insert、update、delete、all、create index...)。
角色:自定义角色( )和预定义角色()。
grant、revoke、with grant option(对象权限)、identified by、whit admin option(系统权限)
使用profile管理用户口令
概述:profile是口令限制,资源限制的命令集合,当建立数据库时,oracle会自动建立名称为default的profile.当建建立用户没有指定的profile选项,那oracle就会将default分配给用户。
(1)账户锁定
概述:指定该账户(用户)登录时最多可以输入密码的次数,也可以指定用户所定的时间(天)一般用dba的身份去执行该命令。
ex:指定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)终止口令
为了让用户定期修改密码可以使用终止口令的指令来完成,同样这个命令也需要dba身份来操作,ex:给用户tea创建一个profile文件,要求该用户每隔十天要修改自家的登录密码,宽限期为十天。
sql>create profile myprofile limit password_life_time 10 password_grace_time 2;
sql>alter user tea profile myprofile;
口令历史:修改密码且不能使用以前用过的密码,可使用口令历史
ex:1)建立profile
sql>create profile password_history limit password_life_time 10 password_grace_time 2 password_reuse_time 10;
password_reuse_time//指定口令可重用时间即十天后就可以重用
2)分配给某个用户.
3)删除profile
sql>drop profile password_history【cascade】
表:必须以字母开头、长度不能超过30字符、不能使用oracle保留字、只能使用如下字符A-Z,a-z,0-9,$,#等
数据类型:number,char(),varchar2(),clob,blob,date,timestamp,
添加一个字段
alter table 表名 add (字段名 字段类型));
修改字段的类型/或是名字(不能有数据)
alter table 表名 modify (字段名 字段类型);
删除一个字段
alter table student drop column 字段名;
修改表的名字
rename student to stu;
删除表
drop table student;
保存点:savepoint 回滚:rollback
truncate table 表名;删除表中所有记录,但不写日志,删除后无法恢复,优点是速度快。
查看表结构:desc 表名;
打开操作所用时间开关:set timing on;
使用nvl函数处理null值.

1、分组函数只能出现在选择列表、having、order by字句中
2、如果在select语句中同时包含有group by,having,order by那么它们的顺序是group by,having,order by
3、在选择列中如果有列、表达式和分组函数,那么这些列和表达式必须有一个出现在group by子句中,否则会出错
用查询结果创建新表:create table mytable (id,name,sal,job,deptno) as select empno,ename,sal,job,deptno from emp;
minus/union/union all/intersect/
日期输入格式转换函数:to_date('2012-8-2','yyyy-mm-dd')
使用子查询插入数据
insert into kkk(字段名...) select 字段名... from emp where ...;
使用子查询更新数据
update emp set (字段名...)=(select 字段名... from emp where ename='SMITH') where ename='SCOTT';
设置只读事务:set transaction read only;
lower(char):将字符串转化为小写格式 upper()
length(char)返回字符串长度
substr(char,m,n)取字符串子串
replace(char1,search_string,replace_string)
用replace_string替换search_string
instr(char1,char2,[,n[,m]])
round(n,[m]):用于执行四舍五入
trunc(n,[m]):用于截取数字
mod(m,n)
floor(n) ceil(n)
sysdate:返回系统时间
add_months(d,n)
last_day(d):返回指定日期所在月份的最后一天
to_char 日期转换
系统函数:sys_context
1)terminal:当前会话客户所对应的终端的标识符
2)language:语言
3)db_name:当前数据库名称
4)nls_date_format:当前会话客户对应的日期格式
5)session_user:当前会话客户所对应的的数据库用户名
6)current_schema:当前会话客户所对应的默认方案名
7)host:返回数据库所在主机的名称
格式:select sys_context('userenv','属性名(如db_name)');
用户和方案的关系
dba:数据库管理员
管理数据库的用户主要是sys和system
区别:1)存储的数据的重要性不同
sys:所有oracle的数据字典的基表(静态数据)和动态视图(动态数据)都存放在sys用户中,这些基表和动态视图对于oracle的运行是至关重要的,由数据库自己维护,任何用户都不能手动更改,sys用户拥有dba,sysdba,sysoper角色或权限,是oracle权限最高的用户。
system:用于存放次一级的内部数据,如oracle的一些特性或工具的管理信息,system用户拥有dba,sysdba角色或系统权限。
2)权限的不同
sys用户必须以as sysdba或as sysoper形式登录。不能以normal方式登录数据库;system如果正常登录,他其实就是一个普通的dba用户,但是如果以as sysdba登录,其结果实际上它是作为sys用户登录的,从登录信息里面我们可以看出来。
dba权限用户:dba用户是指具有dba角色的数据库用户,特权用户可以执行启动实例,关闭实例等特殊操作,而dba用户只有在启动数据库后才能有各种管理工作。
启动数据库:startup
关闭数据库:shutdown
数据库(表)的逻辑备份与恢复
逻辑备份是指使用工具export将数据对象的结构和数据导出到文件的过程,逻辑恢复是指当数据库对象被误操作而损坏后使用工具import利用备份的文件把数据对象导入到数据库的过程。物理备份即可在数据库open的状态下进行也可在关闭数据库后进行,但是逻辑备份和恢复只能在open的状态下进行。
导出表
1)导出自己的表
exp userid=scott/tiger@myoral tables=(emp,...) file=d:\e1.dmp
2)导出其他方案的表
如果用户要导出其他方案的表,则需要dba的权限或是exp_full_database的权限,比如system就可以导出scott的表exp userid=system/manager@myoral tables=(scott.emp) file=d:\e2.dmp;
导出表的结构
exp userid=scott/tiger@myoral tables=(emp) file=d:\e3.dmp rows=n;
使用直接导出方式
exp userid=scott/tiger@myoral tables=(emp) file=d:\e4.dmp direct=y;
这种方式比默认的常规方式速度要快,当数据量大时,可以考虑使用这样的方法(这是需要数据库的)
导出方案:
导出方案是指使用export工具导出一个方案或是多个方案中的所有对象(表,索引,约束...)和数据,并存放到文件中。
1)导出自己的方案
exp userid=scott/tiger@myoral owner=scott file=d:\scott.dmp;
2)导出其他的方案
如果用户要导出其他方案的表,则需要dba的权限或是exp_full_database的权限,比如system用户就可以导出任何方案
exp system/manager@myoral owner=(system,scott) file=d:\scott.dmp;
导出数据库
导出数据库是指利用export导出所有数据库中的对象及数据,要求该用户具有dba的权限或是exp_full_datavase权限
exp userid=system/manager@myoral full=y inctype=complete file=d:\x.dmp;
导入表:
1)导入自己表
imp userid=scott/tiger@myoral tables=(emp) file=d:\xx.dmp;
2)导入表到其他用户
要求该用户具有dba的权限,或是imp_full_database
imp userid=system/manager@myoral tables=(emp) file=d:\xx.dmp touser=scott;
3)导入表的结构
imp userid=scott/tiger@myoral tables=(emp) file=d:\xx.dmp rows=n;
4)导入数据
如果对象(如此表)已经存在
imp userid=scott/tiger@myoral tables=(emp) file=d:\xx.dmp ignore=y;
导入方案:
imp userid=scott/tiger@myoral file=d:\xxx.dmp;
若是导入其他方案,要求该用户具有dba的权限,或是imp_full_database
imp userid=system/manager file=d:\xxx.dmp fromuser=system touser=scott;
导入数据库
imp userid=system/manager full=y file=d:\xxx.dmp;
----数据字典----
数据字典是oracle数据库中最重要的组成部分,它提供了数据库的一些系统信息
动态性能视图记载了例程启动后的相关信息
数据字典的组成:数据字典包括数据字典基表和数据字典视图,其中基表存储数据库的基本信息,普通用户不能直接访问数据字典的基表,数据字典视图是基于数据字典基表所建立的视图,普通用户可以通过查询数据字典视图取得系统信息,数据字典视图主要包括user_xxx,all_xxx,dba_xxx三种类型。
user_tables:
用于显示当前用户所拥有的所有表,它只返回用户所对应方案的所有表
ex:select table_name from user_tables;
all_tables:
用于显示当前用户可以访问的所有表,它不仅会返回当前用户方案的所有表,还会返回当前用户可以访问的其它方案的表
ex:select table_name from all_tables;
dba_tables:
它会显示所有方案拥有的数据库表,但是查询这种数据库字典视图,要求用户必须是dba角色或是有select any table系统权限
ex:当用system用户查询数据字典视图dba_tables时,会返回system,sys,scott...方案所对应的数据库表。
用户名,权限,角色
在建立数据库时,oracle会把用户的信息存放到数据字典中,当给用户授予权限或是角色时,oracle会将权限和角色的信息存放到数据字典.
通过查询dba_users可以显示所有数据库用户的详细信息;
通过查询数据字典视图dba_sys_privs,可以显示用户所具有的系统权限;
通过查询数据字典视图dba_tab_privs可以显示用户具有的对象权限;
通过查询数据字典视图dba_col_privs可以显示用户具有的列权限;
通过查询数据字典视图dba_role_privs可以显示用户具有的角色。

-----------------------------------
//查询oracle中所有的系统权限
select * from system_privilege_map order by name;
//查询oracle中所有的角色
select * from dba_roles;
//查询oracle中所有的对象权限
select distinct privilege from dba_tab_privs;
//查询数据库的表空间
select tablespace_name from dba_tablespaces;
一个角色包含的系统权限:
select * from dba_sys_privs where grantee='DBA';
另外也可这样查看:
select * from role_sys_privs where role='CONNECT';
一个角色的对象权限:
select * from dba_tab_privs where grantee='DBA';
查看某个用户具有什么样的角色:
select * from dba_role_privs where grantee='SCOTT';
===================================
55种角色,17种对象权限,208种系统权限
显示当前用户可以访问的所有数据字典视图 select * from dict where comments like '%grant%';
显示当前数据库的全称
select * from golbal_name;
表空间是数据库的逻辑组成部分。从物理上讲,数据库数据存放在数据文件中;从逻辑上讲,数据则是存放在表空间中,表空间中由一个或多个数据文件组成。
oracle的逻辑由表空间,段,区,块组成。其中,表空间由段组成,段由区组成,区由块组成。
表空间用于从逻辑上组织数据库的数据,数据库逻辑上是由一个或是多个表空间组成的。通过表空间可以达到以下作用:(1)控制数据库占用的磁盘空间(2)dba可以将不同数据类型部署到不同的位置,这样有利于提高i/o性能,同时有利于备份和恢复等管理操作。
建立表空间
建立表空间是使用create tablespace命令完成的,需要注意的是,一般情况下,建立表空间是特权用户或是dba来执行的,如果用其它用户来创建表空间,则用户必须具有create tablespace的系统权限。
建立数据表空间
在建立数据库后,为便于管理,最好建立自己的表空间create tablespace data01 datafile 'd:\test\data01.dbf' size 20m uniform size 128k;
说明:执行完上述命令后,会建立名为data01的表空间,并为该表空间建立名称为data01.dbf的数据文件,区的大小为128k.
使用数据表空间
create table mypart(deptno number(4),dname varchar2(14),loc varchar2(13)) tablespace data01;
改变表空间的状态
当建立表空间时,表空间处于联机的(online)状态,此时该表空间是可以访问的,并且该表空间是可以读写的,即可以查询该表空间的数据,而且还可以在表空间执行各种语句。但是在进行系统维护或是数据维护时,可能需要改变表空间的状态。一般情况下,由特权用户或是dba来操作。
(1)使表空间脱机
alter tablespace data01 offline;
(2)使表空间联机
alter tablespace data01 online;
(3)只读表空间
当建立表空间时,表空间可以读写,如果不希望在该表空间上执行update,delete,insert操作,那么可以将空间修改为只读alter tablespace data01 read only;
再改回可读写为
alter tablespace data01 read write;
1)知道表空间名,显示该表空间包括的所有表
select * from all_tables where tablespace_name='表空间名';
2)知道表名,查看该表属于哪个表空间
select tablespace_name,table_name from user_tables where table_name='emp';
删除表空间
drop tablespace 'data01' including contents and datafiles;
说明:including contents 表示删除表空间时,删除该空间的所有数据库对象,而datafiles表示将数据库文件也删除。
扩展表空间
1、增加数据文件
alter tablespace data01 add datafile 'd:\data01.dbf' size 20m;
2、增加数据文件的大小
alter database datafile 'd:data01.dbf' resize 20m;
3、设置文件的自动增长
alter database datafile 'd:\data01.dbf' autoextend on next 10m maxsize 500m;
盘损坏-移动数据文件
1)确定数据文件所在的表空间
select tablespace_name from dba_data_files where file_name='d:data01.dbf';
2)使表空间脱机
确保数据文件的一致性,将表空间转变为offline的状态
alter tablespace data01 offline;
3)使用命令移动数据文件到指定的目标位置
host move d:\data01.dbf d:\test\data01.dbf;
4)执行alter tablespace命令
在物理上移动了数据后,还必须执行alter tablespace命令对数据库文件进行逻辑修改:
alter tablespace data01 rename datafile 'd:\data01.dbf' to 'd:\test\data01.dbf';
5)使表空间联机
在移动了数据文件后,为了使用户可以访问该表空间,必须将其转变为online状态:alter tablespace data01 online。
数据的完整性用于确保数据遵从一定的商业和逻辑规则。在oracle中,数据完整性可以使用约束、触发器、应用程序(过程、函数)三种方法来实现。
约束包括:not null,uniquee,primary key,foreign key和check五种。
显示约束信息
通过数据字典视图user_constraints,可以显示当前用户所有的约束的信息
select constraint_name,constraint_type,status,validated from user_constraints where table_name='表名';
显示约束列
通过查询数据字典视图user_cons_columns,即可查询与此约束有关的列信息
select column_name,position from user_cons_columns where constraint_name='约束名';
列级约束-----表级约束
单列索引
create index 索引名 on 表名(列名);
复合索引
create index 索引名 on 表名(列名,...);
索引缺点
1:建立索引,系统要占用大约为表的1.2倍的硬盘和内存来保存索引。
2:更新数据的时候,系统必须要有额外的时间来同时对索引进行更新,以维持数据和索引的一致性。
实践表明,不恰当的索引不但于事无补,反而会降低系统性能。因为大量的索引在进行插入、修改和删除操作时比没有索引花费更多的系统时间。
显示表的所有索引
通过查询数据字典视图dba_indexs和user_indexs,可以显示索引信息,其中dba_indexs用于显示数据库所有的索引信息,user_indexs用于显示当前用户的索引信息:
select index_name,index_type from user_indexs where table_name='表名';
显示索引列
通过查询数据字典视图user_ind_columns,可以显示索引对应的列的信息
select table_name,column_name from user_ind_columns where index_name='索引名';
管理权限和角色
收回系统权限不会级联回收
收回对象权限会级联回收
自定义角色,根据自己的需要来定义,一般是dba来建立,如果用别的用户来建立,则需要具有create role的系统权限,在建立角色时可以指定验证方式(不验证,数据库验证等)
(一)建立角色(不验证)
如果角色是公用的角色,可以采用不验证的方式建立角色
create role 角色名 not identified;
(二)建立角色(数据库验证)
采用这样的方式时,角色名、口令存放在数据库中。当激活该角色时,必须提供口令,在建立这种角色时,需要为其提供口令
create role 角色名 identified by 口令;
给角色授权
给角色授权与给用户授权没有太大区别,但是要注意:系统权限的unlimited tablespace和对象权限的with grant option选项是不能授予角色的
精细访问控制:是指用户可以使用函数、策略实现更加细微的安全访问控制。通过这样的控制,可使得不同的数据库用户在访问相同表时,返回不同的数据信息。
pl/sql(procedural language/sql)
学习必要新;:
1)提高应用程序的运行性能
2)模块化的设计思想[分页的过程,订单的过程,转账的过程...]
3)减少网络传输量
4)提高安全性
缺点:
移植性不好.
show error;
创建过程
create (or replace) procedure sp_pro1 is
begin
--执行部分
insert into mytest values('Join','123');
end;
如何调用该过程
1)exec 过程名(参数值1,参数值2,...)
2)call 过程名(参数值1,参数值2,...)
编写规范
1.注释
单行注释--
多行注释/*......*/
2.标识符号的编写规范
1)当定义变量时,建议用v_作为前缀
2)当定义常量时,建议用c_作为前缀
3)当定义游标时,建议用_cursor作为后缀
4)当定义例外时,建议用e_作为前缀
块(block)是pl/sql的基本程序单元,编写pl/sql程序实际上就是编写pl/sql块。要完成相对简单的功能,可能只需要编写一个pl/sql块;但是如果要想编写实现复杂的功能,可能需要在一个pl/sql块中嵌套其它的pl/sql块。
pl/sql块由三个部分构成:定义部分、执行部分、例外部分
declare
/*定义部分---定义常量、变量、游标、例外、复杂数据类型*/(可选)
begin
/*执行部分---要执行的pl/sql语句和sql语句*/(必选)
exception
/*例外处理部分---处理运行的各种错误*/(可选)
end;
有定义和执行部分的块
declare
v_ename varchar(5);
v_sal number(7,2);
begin
select ename,sal into v_ename,v_sal from emp where empno=&aa;
dbms_output.put_line('用户名:'||v_ename||' 工资:'||v_sal);
--异常处理
exception
when no_data_found then
dbms_output.put_line('编号输入有误');
end;
&表示要接受从控制台输入的变量
为了避免pl/sql程序运行出错,提高pl/sql的健壮性,应该对可能的错误进行处理
oracle事先预定义了一些例外,no_data_found就是找不到数据的例外。
过程
过程用于执行特定的操作,当建立过程时,既可以指定输入参数(in),也可以指定输出参数(out),通过在过程中使用输入参数,可以将数据传递到执行部分,通过输出参数,可以将执行部分的数据传递到应用环境。
-----------函数-------------------
函数用于返回特定的数据,当建立函数时,在函数头部必须包含return字句,而在函数体内必须包含return语句返回的数据。我们可以使用create function来建立函数,案例
create function sp_fun1(spName varchar2)
return number is yearSal number(7,2);
begin
select sal*12+nvl(comm,0)*12 into yearSal from emp where ename=spName;
return yearSal;
end;
调用函数
var income number;
call sp_fun1('SCOTT') into:income;
-----------包----------------------
包用于逻辑上组合过程和函数,它由包规范和包体两部分组成。实例:
create package sp_package is
procedure sp_pro1(name varchar2,...);
function sp_fun1(name varchar,...) return number;
end;
包的规范只包含了过程和函数的说明,但是没有过程和函数的实现代码,包体用于实现包规范中的过程和函数,
建立包体:
create or replace package body sp_package is
...
调用包体
exec sp_package.包里的过程或函数(参数);
----------触发器----------
触发器是指隐含的执行的存储过程。当定义触发器时,必须要指定触发的时间和触发的操作,常用的触发事件包括insert,update,delete语句,而触发操作实际就是一个pl/sql块。可以使用create trigger来建立触发器。
----------定义并使用常量---------
1)标量类型(scalar)
2)复合类型(composite)
3)参照类型(reference)
4)lob(large object)
identifier(名称) [condtant(指定常量 datatype(数据类型) [:=(给变量或是常量指定初始值)default(用于指定初始值)|expr(指定初始值的pl/sql表达式,可是文本值、其他变量、函数等)])]
declare
c_tax_rate number(3,2):=0.03;
v_ename varchar(5);
v_sal number(7,2);
v_tax_sal number(7,2);
begin
select ename,sal into v_ename,v_sal from emp where empno=&no;
v_tax_sal:=v_sal*c_tax_rate;
dbms_output.put_line('name:'||v_ename||' sal:'||v_sal||' tax:'||v_tax_sal);
end;
标量(scalar)--使用%type类型
标识符名 表名.列名%type;
符合变量--pl/sql记录
相当于高级语言中的结构体
declare
type emp_record_type is record(name emp.ename%type,salary emp.sal%type,title emp.job%type);
sp_record emp_record_type;
begin
select ename,sal,job into sp_record from emp where empno=7788;
dbms_output.put_line('员工名:'||sp_record.name)
end;
复合类型--pl/sql表
相当于高级语言中的数组(但下表可为负数)
declare
type sp_table_type is table of emp.ename%type index by binary_integer(下标是整数);
sp_table sp_table_type;
begin
select ename into sp_table(0) from emp where empno=7788;
dbms_output.put_line('员工名:'||sp_table.name)
end;
复合变量--嵌套表(nested table)
复合变量--变长数组(varray)
-----------------------------------
参照变量是指用于存放数值指针的变量,通过使用参照变量,可以使得应用程序共享相同对象,从而降低占用的空间。在编写pl/sql程序时,可以使用游标变量(ref cursor)和对象变量类型(ref obj_type)两种参照变量类型。
declare
--定义游标
type sp_cursor is ref cursor;
--定义一个游标变量
test_cursor sp_cursor;
--定义变量
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
--执行
--把 test_cursor 和一个select结合
open test_cursor for select ename,sal from emp where deptno=&no;
--循环取出
loop
fetch test_cursor into v_ename,v_sal;
--判断是否test_cursor为空
exit when test_cursor%notfound;
dbms_output.put_line('名字:'||v_ename||'工资:'||v_sal);
end loop;
end;
条件分支语句:if--then,if--then--else,if--then--elsif--then--else
-----------------------------------
-----------------------------------
--返回结果集的过程
--创建一个包,在该包中,定义一个游标类型
create package testpackage as
type test_cursor if ref cursor;
end testpackage;
--创建过程
create or replace procedure sp_pro9(spNo in number,sp_cursor out package.test_cursor) is
begin
open sp_cursor for select * from emp where deptno=spNo;
end;
--如何在java中调用该过程
--oracle的分页
select temp.*,rownum rn from (select * from emp) temp;
--在分页的时候,大家可以把下面的sql语句当做一个模板使用
select * from
(select temp.*,rownum rn from (select * from emp) temp where rownum<=10)
where rn>=6;


--开发一个包
--开始编写分页的过程
create or replace procedure page
(tableName in varchar2,
pageSize in number,
pageNow in number,
myRows out number,
myPageCount out number,
sp_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 temp.*,rownum rn from (select * from '|| tableName
||') temp where rownum<='||v_end||') where rn>='||v_begin;
--把游标和sql关联
open sp_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/pageSize+1;
end if;
--关闭游标
--close p_cursor;
end;

--使用java测试

--新的需要,要求按照薪水从低到高排序,然后取出第6-10
就是v_sql:='select * from (select temp.*,rownum rn from (select * from '|| tableName
||' order by sal asc) temp where rownum<='||v_end||') where rn>='||v_begin;

例外的分类
oracle将例外分为预定义例外(用于处理常见的oracle错误),非预定义例外(用于处理预定义例外不能处理的例外)和自定义例外(用于处理与oracle错误无关的其它情况)三种
常见预定义例外:no_data_found,case_not_found,cursor_already_open,dup_val_on_index,invalid_cursor,invalid_number,too_many_rows,zero_divide,value_error
其它预定义例外:login_denide(当用户非法登录时触发),not_logged_on(用户没有登录就执行dml操作时触发),storage_error(超出内存空间或是内存被损坏时触发),timeout_on_resource(如果oracle等待资源时,出现了超时就触发该例外)
非预定义例外:用于处理与预定义例外无关的oracle错误,使用预定义例外只能处理21个oracle错误,而当使用pl/sql开发应用程序时,可能会遇到其他的一些oracle错误,比如在pl/sql块中执行dml语句时,违反了约束规定等等,在这种情况下,也可以处理oracle的各种例外。
自定义例外
create or replace procedure ex_test(spNo number) is
myex exception;
begin
update emp set sal=sal+1000 where empno=spNo;
if sql%notfound then --sql%notfound表示没有update
raise myex; --raise myex表示触发myex
endif;
exception
when myex then
dbms_output.put_line('没有更新用户');
end;
--------------视图---------------
创建视图
create (or replace) view myview as select 语句 [with read only];
删除视图:drop view 视图名;

不能通过视图添加记录的条件–视图中包含分组函数–视图中含有GROUP BY子句–视图中含有DISTINCT关键字–视图中包含伪列ROWNUM–视图中要修改的列包含表达式–视图中没有表的NOT NULL列。

WITH CHECK OPTION
–WITH CHECK OPTION实质是给视图加一个“CHECK”约束,该CHECK约束的条件就是视图中的子查询的WHERE条件,以后如果想通过该视图执行DML操作,不允许违反该CHECK约束。

CREATE OR REPLACE VIEW v_emp3 AS SELECT employee_id,salary FROM emp_dml WHERE employee_id=141 WITH CHECK OPTION CONSTRAINT v_emp3_ck;

该约束的条件为视图中WHERE条件,即“employee_id=141”,如视图v_emp3想执行DML操作,不能把记录的employee_id字段值改成其他编号(只能是141),如果违反了,执行出错,会出现错误提示。

WITH READ ONLY
–WITH READ ONLY的视图是只读的,不允许通过该视图执行DML语句。

CREATE OR REPLACE VIEW v_emp4 AS SELECT employee_id,salary FROM emp_dml WITH READ ONLY;

分享到:
评论

相关推荐

Global site tag (gtag.js) - Google Analytics