-----------------------------------------------------------------------------------------
16讲 第四天
-------------
dba 指数据库管理员:主要职责:
1，安装和升级oracle数据库 2 建库,表空间,表,视图,索引..... 3 制定并实施备份与恢复计划 4 数据库权限管理,调优,故障排除 5 对于高级dba,要求能参与项目开发,会编写sql语句,存储过程,触发器,规则,约束,包;
(OCP认证)
SYS和SYSTEM用户的区别 :1最重要的区别是存储的数据的重要性不同2，其次的区别是权限的不同
sys用户:基表,动态视图,权限 sys 拥有dba(数据库管理员),sysdba(系统管理员),sysoper(系统操作员),sys用户必须以sysdba,或sysoper形式登陆数据库
system用户:存放次一级的数据,拥有dba,sysdba角色或是系统权限,system如果是正常登陆,它其实是一个普通的dba用户,但是如果以sysdba登录,其结果实际上它是作为sys用户登录的.
conn sys/change_on_install as sysdba;
startup(启动数据库)
权限比较sysdba>sysoper>dba
show parameter;查看数据库配置信息
备份表的逻辑备份(使用export)与恢复(使用import)
物理备份既可在数据库open的状态下进行也可以在关闭数据库后进行,但是逻辑备份和恢复只能在open的状态下进行
导出具体的氛围:导出表,导出反,导出数据库三种方式
导出使用exp命令完成,该命令常用的选项有:
userid:用于指定执行导出操作的用户名,口令,连接字符串.
tables:用于指定执行导出操作的表
owner:用于指定执行导出操作的方案
full=y:用于指定执行导出操作的数据库
inctype:用于指定执行导出操作的增量类型
rows:用于指定执行导出操作是否要导出表中的数据
file:用于指定导出文件名
导出表
1,导出自己的表 exp userid=scott/tiger@oradb1 tables=(emp) file=d:a.dmp
2导出其它反的表,如果用户要导出其它方案的表,则需要dba的权限或是
exp_full_database的权限 比如system就可以导出scott的表
exp userid=system/manager@oradb1 tables=(scott.emp) file=d:.dmp
注意在导入导出的时候要到oracle的主目录下D:oracleora90BINexp.exe然后到运行cmd
导出表的结构 exp userid=scott/tiger@oradb1 tables=(mytable) file=d:j.dmp rows=n;
使用直接导出方式 exp userid=scott/tiger@oradb1 tables=(mytable) file=d:
.dmp direct=y;此法速度快
导出方案 导出自己的方案 exp scott/tiger@oradb1 owner=scott file=d:a.dmp;
导出数据库 :导出数据库是指利用export导出所有数据库中的对象和数据,要求该用户具有dba的权限或是exp_full_database权限
exp userid=system/manager@oradb1 full=y inctype=complete file=d:\oradb1.dmp
---------------------(本人导入表的时候总"imp-00058:遇到oracle错误 12154" ora-12154:tns:无法处理服务名,imp-00000:未成功终止导入)
导入表 该命令常用的选项有userid ,tables,fornuser(指定源用户),ignore(如果表存在,则只导入数据) touser,file,full=y,inctype,rows
1导入自己的表
imp userid=scott/tiger@oradb tables=(emp) file=d:xx.dmp;
2导入表到其他用户(要求该用户具有dba权限,或是imp_full_database;
imp userid=system/manager@oradb tables=(emp) file=d:us.dmp touser=scott;
3只导入表的结构 imp userid=scott/tiger@oradb1 tables=(emp) file=d:emp.dmp rows=n
4导入表的数据(如果对象比如表已经存在,刻意只导入表的数据)
imp userid=scott/tiger@oradb1 tables=(emp) file=d:emp.dmp ignore=y
------------------------------------
导入方案是指使用import工具将文件中的对象和数据导入到一个或多个方案中,如果要导入其他方案,
要求该用户具有dba权限,或是imp_full_database
1导入自身的方案 imp userid=scott/tiger file=d:xxx.dmp
2导入其他方案 要求该用户具有dba权限 imp userid=system/manager file=d:xxx.dmp fromuser=system touser=scott
----------------------------------------------------------------
注意如果导入emp表的话,因为有主键,故会出错
综合练习 conn system/manager;
exp userid=scott/tiger@oradb1 tables=(dept) file=d:dept.dmp
drop table dept;
imp userid=system/manager@oradb1 tables=(dept) file=d:dept.dmp
select * from dept;
---
create table mytable(id,name,hiredate,sal,comm) as select empno,ename,hiredate,sal,comm from emp;
d:oracleora90inexp userid=scott/tiger@oradb1 tables=(mytable) file=d:mytable.dmp
d:oracleora90inimp userid=scott/tiger@oradb1 tables=(mytable) file=d:mytable.dmp
------数据字典和动态性能视图
数据字典是oracle数据库中最重要的组成部分,
它提供了数据库的一些系统信息,它是只读表和视图的集合,数据字典的所有
者位sys用户,用户只能在数据字典上执行查询操作(seelct语句),而其维护和
修改时由系统自动完成的数据字典的组成:数据字典基表(存放静态数据)和数据字典视图(存放动态数据),其中
基表存储数据库的基本信息,普通用户不能直接访问数据字典的基表;数据字典视图是基于数据字典基表所建立的视图,普通用
户可以通过查询数据字典视图取得系统信息,
数据字典视图主要包括user_xxx,all_xxx,dba_xxx三种类型
动态性能视图记载了例程启动后的相关信息
user_tables用于显示当前用户所拥有的所有表 select table_name from user_tables;
all_tables用于显示当前用户可以访问的所有表,它不仅会发挥当前用户反感的所有表,还会返回当前用户可以访问的其他方案的表
select table_name from all_tables;
dba_tables 它会显示多有方案拥有的数据库表,但是查询这种数据库视图,要求必须是dba角
色或是有select any table系统权限.例如当system查询数据字典视图dba_tables时,会返回system,sys,scott...方案所对应的数据库表.
用户名,权限,角色:在建立用户是,oracle会把用户的信息存放到数据字典中,当给用户授予权限或是角色是,oracle会将权限和角色的信息存放到数据字典中,
通过查询数据字典视图dba_sys_privs表,可以显示用户所具有的系统权限;
通过查询数据字典视图dba_tab_privs表,可以显示用户所具有的对象权限;
通过查询数据字典视图dba_col_privs表,可以显示用户所具有的列权限;
通过查询数据字典视图dba_role_privs表,可以显示用户所具有的角色;
例如查看scott具有的角色,可查询desc dba_role_privs; select * from dba_role_privs where grantee='scott';
查看所有的用户和密码select username,password from dba_users ;
-------------------------
?如何查询一个角色 包括的权限
a 一个角色包含的系统权限 select * from dba_role_privs where grantee='用户名';或者select * from role_sys_privs where role='CONNECT';
b 一个角色包含的对象权限 可以直接通过pl/sql工具查看或者 select * from dba_tab_privs where grantee='角色名';
查看某个角色包括哪些系统权限 select * from dba_sys_privs where grantee='DBA';或者 select * from role_sys_privs where role='DBA';
oracle究竟有多少中角色?select * from dba_roles;
--------------------------
查询oracle的所有角色 ,一般是dba, select * from dba_roles;
如何查看某个用户具有什么样的角色 select * from dba_role_privs where grantee='用户名';
查询oracle中的所有系统权限,一般是dba,
select * from system_privilege_map order by name;
查询oracle中所有对象权限,一般是dba ,select distinct privilege from dba_tab_privs;
查询数据库的表空间 select tablespace_name from dba_tablespaces;
系统权限主要是针对数据库而言,对象权限主要是针对数据对象而言;
显示当前用户可以访问的所有数据字典视图 select * from dict where comments like '%grant%';
显示当前使用的是哪个数据库 select * from global_name;
数据字典记录有oracle数据库的所有系统信息,通过查询数据字典可以取得以下系统信息 1，对象定义情况,2对象占用空间大小3列信息,4约束信息 (可以通过pl/sql工具查询得到)
动态性能视图用于记录当前例程的活动信息.当启动oracle server时,系统会建立动态性能视图,停止时系统会删除动态性能视图(实际中较少用)
------------20讲 管理表空间和数据文件
表空间是数据库的逻辑组成部分,从物理上讲,数据库数据存放在数据文件中;从逻辑上讲,数据库则是存放在表空间中,表空间由一个或多个数据文件组成;
数据库的逻辑结构 oracle中逻辑结构包括表空间,段,区和块.说明一下数据库
由表空间构成,而表空间又是由段构成,而段又是由区构成,而区又是由oracle块构成的这样一种结构,可以提高数据库的效率
数据库-->表空间-->段-->区-->块
表空间用于从逻辑上组织数据库的数据,数据库逻辑上是由一个或是多个表空间组成的.通过表空间可以达到一下作用:1控制数据库占用的磁盘空间2dba可以将不同数据类型部署这样有利于提高i/o性能,同时利于备份和恢复等管理操作;
建立表空间是 create tablespace命令完成的 一般情况下,建立表空间是特权用户或是dba来执行的,如果用其他用户来创建表空间,则用户必须要具有create tablespace的系统权限.
建立数据表空间 :建立名为data01的表空间,并为该表空间创建名称为data01.dbf的数据文件,区的大小的128k -->
create tablespace data01 datafile 'd:data01data01.dbf' size 20m uniform size 128k;
使用表空间 create table myspace(dname varchar2(10),deptno number(4),loc varchar2(10)) tablespace data01;
datafile是日志文件
改变表空间的状态
当建立表空间时,表空间处于联机的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; 取消只读,设置只写
知道表空间名,显示该表的空间包括的所有表 select * from all_tables where tablespace_name='DATA01';表空间名要大写
知道表名,查看该表属于哪个表空间 select tablespace_name ,table_name from user_tables where table_name='EMP';表名EMP一定要大写
知道scott.emp是在system这个表空间上,现在我们可以将system改为只读的但是我们不会成功,因为system是系统表空间,如果是普通表空间,那么我们就可以将其设为只读的
向表中添加数据 insert into myspace select * from myspace;快速复制
拓展表空间 :
1增加数据文件 alter tablespace data01 add datafile 'd:data01data02.dbf' size 2m;
----------------------------------------------------************---------------------------------------------------------
2(运行不了)增加数据文件的大小 alter tablespace data01 'd:data01data01.dbf' resize 8m; 这里注意数据文件的大小不要超过500m
3(运行不了)设置文件的自动增长 alter tablespace data01 'd:data01data01.dbf' autoextend on next 2m maxsize 50m;
---------------------------------------------------------**************************--------------------------------
移动数据文件:
以移动数据文件data01.dbf 为例说明
1确定数据文件所在的表空间 select tablespace_name from dba_data_filles where file_name='d:data01data01.dbf';---->2 使表空间脱机(确保数据文件的一致性,
将表空间转变为offline状态) alter tablespace data01 offline;-->3 使用命令移动数据文件到指定的目标位置 host move d:data01data01.dbf e:data01.dbf;-->4执行alter tablespace 命令(在物理上移动了数据后,还必须执行alter tablespace
命令对数据库文件进行逻辑修改) alter tablespace data01 rename datafile 'd:data01data01.dbf' to 'e:data01.dbf';-->5使表空间联机 (在移动了数据文件后,为例使用户可以访问该表空间,必须将其转变为online状态 alter tablespace data01 online;
了解表空间的各个状态online ,offline ,read write,read only 的作用
此外还有其他表空间如索引,undo,临时,非标准块的表空间,之前的是常用表空间
第五天
维护数据的完整性 :用于确保数据库数据遵从一定的商业和逻辑规则.在oracle中,数据完整性可以使用约束,触发器,应用程序(过程,函数)三种方法来实现,在这三种方法中,因为约束易于维
护,并且有最好的性能,所有作为维护数据完整性的首选.
约束:用于确保数据库数据满足特定的商业规则,在oracle中,约束包括:not null,unique(唯一,定义后,该列值是不能重复的,但可以为空)
,primary key(该列不但不能重复也不能为空,一个表不可能有多个主键,但可以有多个联合主键)
,foreign key(用于定义主表和从表之间的关系,外键约束要定义在从表上,主表则必须越有主键约束或是unique约束,当定义外键约束后,要求外键列数据必须在主表的主键列存在或是为
null),和check(检查)五种
---------------------------------
商店售货系统表设计案例 :现有一个商店的数据库,记录客户机器购物情况,由下面三个表组成 :
商品goods(商品号goodsId,商品名goosName,单价unitprice,商品类别category,供应商provider);
客户customer(客户号customerId,姓名name,住址address,电邮email,性别sex,身份证cardId);
购买purchase(客户号customerId,商品号goodsId,购买数量nums);
请用sql语言完成下列功能: 1，建表,在定义中要求声明: (1) 每个表的主外键 (2) 客户的姓名不能为空指 (3) 单价必须大于0 ,购买数量必须1到30之间 (4)电邮不能重复 (5) 客户的性
别必须是男或女默认是男
create table goods(
goodsId char(8) primary key ,
goodsName varchar2(30),
unitprice number(8,2) check(unitprice>0),
category varchar2(8),
privider varchar2(30));
--------
create table customer
(customerId char(8) primary key,
name varchar2(30) not null,
address varchar2(50),
email varchar2(30) unique,
sex char(2) default '男' check(sex in('男','女')),
cardId char(18));
create table purchase(customerId char(8) references customer(customerId),
goodsId char(8) references goods(goodsId),
nums number(4) check(nums between 1 and 30));
如果在建表是忘记建立必要的约束,则可以在建表后使用alter table 命令为表增加约束
给商品名称增加不能为空的约束alter table goods modify goodsName not null;
增加身份证不能为空 alter table customer add constraint cardunique unique(cardId);
住址只能是'海淀,朝阳,东城,西城,通州,崇文'; alter table customer add constraint addresscheck check(address in('东城','西城','海淀','朝阳','崇文');
删除主键约束 alter table 表名 drop primary key cascade;cascade 在两张表存在主从关系时加上,否则会有错;
删除约束 当不需要约束时可以删除 alter table 表名 drop constraint 约束名称;
显示约束信息:
通过查询数据字典视图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 table department4(dept_id number(2) constraint pk_department primary key,name varchar2(20),loc varchar2(12));
表级定义 :表级定义是指在定义了所有列后,在定义约束,这里注意 not null 约束只能在列级上定义以在建立employee2表时定义主键约束和外键约束为例
create table employee2(emp_id number(4) ,name varchar2(15),dept_id number(2),constraint pk_employee primary key (emp_id),constraint fk_department foreign
key(dept_id)references department4(dept_id));
管理索引 :索引是用于加速数据存储的数据对象,合理的索引可以大大的降低i/o次数,从而提高数据访问性能.索引有很多种.
创建索引 :单列索引:单列索引时给予单个列所建立的索引比如 create index 索引名 on 表名(列名);
创建customer表的索引 create index nameindex on customer(name) ;
复合索引 :复合索引是基于两列或是多列的索引,在同一张表上可以有多个索引,但是要求列的组合必须不同 比如 create index emp_index1 on emp(ename,job); create index
emp_index2 on emp(job,ename);
索引使用原则:必须是一张大表才有意义,在where子句或是连接条件上经常引用的列上建立索引,索引的层次不要超过4层
索引有一些先天不足 1建立索引,系统要占用大约为表的1.2倍的硬盘和内存空间来保存索引.2更新数据的时候,系统必须要有额外的时间来同时对索引进行更新,以维持数据和索引的一致性.
-->实践表名,不恰当的索引不但于事无补,反而会降低系统性能因为索引在进行插入,修改和删除操作时比没有索引话费更多的系统时间
比如在如下字段建立索引时不恰当的:很少或从不引用的字段,逻辑型的字段,如男或女(是或否).综上所述,提高查询效率是以消耗一定的系统资源为代价的,索引不能盲目的建立,这是
考验一个dba是否优秀的很重要的指标.
create index sal_comm on mytable ( (sal+comm)*12, sal,comm);
列上有unique的话oracle会自动加个索引
显示索引信息
??什么事系统(对象)权限,系统(对象)权限有哪些,如何赋予系统(对象)权限?
当刚刚建立用户时,用户没有任何权限,也不能执行任何操作,如果要执行某种特定的数据库操作,则必须为其授予系统的权限,如果用户要访问其他方案的对象,
方案:当你创建了一个则必须为其授予对象的权限,为了简化权限的管理,可有使用角色.
角色:用于简化权限的管理
权限是指执行特定的sql命令的权利.
授予系统权限,一般情况下授予系统权限是由dba完成的,如果用其他用户来授予系统权限,则要求该用户必须具有grant any privilege 的系统权限在授予系统权限时,可以
带with admin option
选项,这样,被授予权限的用户或是角色还可以将该系统权限授予其它的用户或角色,例如:
创建用户create user key identified by ken;-->给ken授权 grant create session,create table to ken with admin option;--> grant create session,create to tom-->
(执行不了的) grant create view to tom;-->回收权限 revoke create session from ken;(ken不能登录连接了,但是tom可以登录)
对象权限是指用其他方案对象的权利,用户可以直接访问自己方案的对象,但是如果要访问别的方案的对象,则必须具有对象的权限.比如smith用户要访问scott.emp表则必须在scott.emp表上
具有对象的权限.常用的有alter,delete ,select,insert ,update,index,references引用,execute 执行
显示对象权限:通过数据字典视图可以显示用户或是角色所具有的对象权限,视图为 dba_tab_privs--> select grantor,owner,table_name,privilege from dba_tab_privs where
grantee='BLAKE';
授予对象权限:dba用户sys,system 可以将如何对象上的对象权限授予其他用户,授予对象权限是用grant命令来完成的,对象权限可以授予用户,角色和public,如果带有with grant option
选项,则可以将该权限转授给其他用户,但是要注意with grant option选项不能被授予角色,monkey 可以查询scott.emp表的数据,则必须授予相应的对象权限:希望monkey 用户可以
查询scott.emp表的数据 grant select on emp to monkey;
希望monkey指可以修改scott.emp表的sal字段 grant update on emp(sal) to monkey;(授予列权限)授予alter权限,授予index权限希望black可以在scott.emp表建立索引,grant index on
scott.emp to blake with grant option;
预定义角色:常用的预定义角色:connect,resource,dba 1connect角色:alter session ,create cluster/[database link]/[session]/[table]/[view]/[sequence];
resource 角色:其角色具有应用开发人员所需要的其他权限,如建立存储过程,触发器,这里注意resource角色隐含了unlimited tablespace 系统权限
resource角色包含以下系统权限 create cluster/[indextype]/[table]/[sequence]/[type]/[procedure]/[trigger];
dba角色:具有所有的系统权限及with admin option 选项,默认的dba用户为sys和system他们可以将任何系统权限授予其他用户,但是dba角色不具备sysdba 和sysoper的特权(启动和关
闭数据库);
自定义角色:自己定义的角色,根据自己的需要来定义,一般是dba来创建,如果用别的用户来建立,则需要具有create role的系统权限 ,在机那里角色时可以指定验证方式.
建立角色(不验证) 如果角色是公用的可用采用不验证的方式创建角色 create role 角色名 not identified;-->给角色授权 grant create session to 角色名;
建立角色(数据库验证) create role 角色名 identified by 密码名;
角色授权:当建立角色时,角色没有任何权限,为了使得角色完成特定的任务,必须为其授予相应的系统权限和对象权限.1给角色授权:给角色授权和给用户授权没有太多区别,但是要注意,系统权限的unilitmited tablespace 和对象权限的with grant option选项是不能授予角色
2,分配角色给某个用户,一般分配角色是由dba来完成的,如果要以其他用户身份废品角色,则要求用户必须具有grant any role的系统权限. conn system/manager; -->grant 角色名 to blake with admin option; 因为我们给了with admin option 选项,blake 可以把system分配给他的角色
分配给别的用户 .
------------------
题目:角色就是系统权限的命令集合,使用角色的主要目的是为了简化权限的管理,假定有a,b,c用户为了让他们都拥有连接数据库,和在emp表上select,insert ,update的权限,如果直接授权要12次才行
解决方案:首先将create session , select on scott.emp ,insert on scott.emp ,update on scott.emp 首角色,然后将该角色授予a,b,c用户,这样就可以三次授权搞定
conn system/manager-->create role myrole1 not identified; -->grant create session to myrole1 with admin option;-->conn scott/tiger;--> grant select on emp to myrole1;-->grant insert ,update, delete on emp to myrole1;-->conn system/manager;-->create user blake identified by blake;-->grant myrole1 to blake with admin option;
删除角色 conn system/manager;-->drop role myrole1;
-----------------------------------------------------
显示所有角色信息 select * from dba_roles;
显示角色具有的权限 select privilege ,admin_option from role_sys_privs where role='角色名(大写)';
显示角色具有的对象权限 :通过查询数据字典视图dba_tab_privs 可以查看角色具有的对象权限或是列的权限
显示用户具有的角色以及默认的角色 :通过查询数据字典视图dba_role_privs可以欣赏某个用户具有的所有角色及当前默认的角色.
select granted_role,default_Role from dba_role_privs where grantee='SCOTT';
精细访问控制:指用户可以使用函数,策略实现更加细微的安全访问控制.通过这种控制,可以使得不同的数据库用户在访问相同的表时,返回不同的数据信息.(oracle学得高级时用)
24讲:pl/sql编程(编写过程,函数,触发器...)
pl/sql是oracle在标准的sql语言上的扩展.它嵌入了sql语言,还可以定义常量和变量,允许使用条件和循环语句.
学习pl/sql 的必要性:有点-->提高应用程序的运行性能,模块化的设计思想(分页的过程,订单的过程,转账的过程...),减少网络传输量,提高安全性.
缺点:移植性不好.
sqlplus开发工具(是oracle公司提供的一个工具)和pl/sql developer开发工具(是用于开发pl/sql块的集成开发环境(ide),是一个独立的产品,而不是oracle的一个附带品)
编写一个过程,向表中添加数据 create table mytest(name varchar2(30),password varchar2(20));
create or replace procedure sp_pro1 is
begin
insert into mytest values('陈超','123');
end;
/
如果出错要加上'/'回车 或show error;命令查看
如何调用该过程-->1, exec 过程名(参数1,参数2...); 2,call 过程名(参数1,参数2...);
编写一个存储过程,该过程可以删除某个表的记录--> create or replace procedure sp_pro2 is begin delete from mytest where name='陈超'; end;/
简单分类 块-->过程,函数,触发器,包.
块(block)是pl/sql的基本程序单元,
注释:单行--; 多行 标识符号的命名规范 当定义常量时,建议用v_作为前缀v_sal
块结构示意图 :pl/sql块由三个部分构成-->定义部分,执行部分,例外处理部分.示例如下:
declare 定义部分-->定义常量,变量,游标,例外,复杂数据类型
begin 执行部分-->要执行pl/sql语句和sql语句
exception 例外处理部分 -->处理运行的各种错误
end;
注意:定义部分是从declare开始的,该部分是可选的,执行部分是从begin开始的,该部分是必须的 ;例外部分是从exception开始的,该部分是可选的;
实例1-只包含执行部分的pl/sql块
set serveroutput on --打开输出选项
begin
dbms_output.put_line('hello');
end;
相关说明 dbms_output是oracle所提供的包(类似java的开发包),该包包含一些过程,put_line就是dbms_output包的一个过程;
实例2->包含定义部分和执行部分的pl/sql块
declare
v_ename varchar2(5);
v_sal number(7,2);
begin
select ename,sal into v_ename,v_sal from emp where empno=&no;
dbms_output.put_line('雇员名:' || v_ename ||'工资:' || v_sal);
end;
这里注意要set serveroutput on ;否则不能看到数据,当输入的雇员号不存在是,只需进行异常处理,在end;的前面加入 :
exception
when no_data_found then dbms_output.put_line('你输入的编号不存在');
&表示控制符要从控制台输入;
实例3-->包含定义部分,执行部分,和例外部分 ,为了避免pl/sql程序的运行错误,提高pl/sql的健壮性,应该对可能的错误进行处理,这个很有必要的.
比如 在实例2中如果输入了不存在的雇员号,应当做例外的处理,有时出现异常,希望例外的逻辑处理 ;相关说明:oracle事先预定义了一些例外,not_data_found 就是找不到数据的例外
过程用于执行特定的操作,当建立过程时,既可以指定输入参数,也可以指定输出参数,通过在过程中使用输入参数,可以将数据传递到执行部分,通过使用输
出参数,可以将执行部分的数据传递到应用环境,
实例如下 :1,编写一个过程,可以输入雇员名,新工资,可以修改雇员的工资;2 ,如何调用过程有两种方法 3,如何在java程序中调用一个存储过程
-----------------------------------------------------------
create procedure sp_pro3(spName varchar2,newSal number) is begin
--执行部分,根据用户名去修改工资
update emp set sal=newSal where ename=spName;
end ;
/
调用exec sp_pro3('SCOTT',5000);
---------------------------------------------------------
演示java程序调用存储过程--创建了Connection 之后
CallableStatement cs=ct.prepareCall("{call sp_pro3(?,?)";
cs.setString (1,"SCOTT");
cs.setInt(2,1000);
cs.execute();
关闭资源
输入雇员的星姓名返回该雇员的年薪->
create function sp_fun2(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;/
在sqlplus中调用函数 var abc number ; ->call sp_fun2('SCOTT') into :abc;-> print abc;
包: 包用于在逻辑上组合过程和函数,它由包规范和包体两部分组成
创建包可以用create package命令
(创建一个包sp_package ,声明该包有一个过程
create 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 procedure update_sal(name varchar2,newsal number) is begin update emp set sal=newsal where
ename=name;
end;
function annual_income(name varchar2) return number is annual_salary number;
begin select sal*12+nvl(comm,0) into annual_salary from emp where ename=name;
return annual_salary;
end;
end;/
当调用包的过程或是函数时exec sp_package.update_sal('SCOTT',1300);
--------
触发器 是指隐含的执行的存储过程,当定义触发器时,必须要指定触发器的时间和触发的事件和触发的过程,常用的触发过程包括insert,update,delete语句,而触发操作实际就是一个pl/sql块,可以使用create trigger来建立触发器.触发器是非常有用的,因为
在编写pl/sql程序时,可以定义变量时变量和常量;在pl/sql程序中包括有:标量(scalar),复合(composite),参照(reference) ,lob(large object)
标量:有很多常用类型,在编写pl/sql块时,如果要使用变量,需在定义部分定义变量. pl/sql中定义变量和常量的语法如下: identifier [constant]datatype [not null] [:=| default expr] ;
identifier :名称 constant :指定常量,需要指定它的初始值,且其值是不能改变的 datetype:数据类型 not null:指定变量不能为null ; := 给变量或是常量指定初始值; default 用于指定初始值; expr: 指定初始值的pl/sql表达式可是文本值,其它变量,函数等
变量定义的案例
1定义一个变长字符串 var_ename varchar2(10); 2定义一个小数 范围 -9999.99到9999.99 v_sal number(5,2); 3定义一个小数并给一个初始值5.4:=是pl/sql的赋值号 v_sal2 number(5,2):=5.4; 4定义个日期类型 v_hiredate date;
5定义一个布尔变量 ,不能为空 ,初始值为false --> v_valid boolean not null default false;
标量(存放单个)实例:以输入员工号,显示雇员姓名,工资,个人所得税(税率是0.03)为例 .说明变量的使用 -->
declare c_tax_rate number(3,2):=0.03;
v_ename varchar2(8); v_sal number(7,2);
v_tax_sal number(8,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('姓名是'||v_ename ||'工资'||v_sal ||'交税'||v_tax_sal);
end;
变量使用scalar的%type类型,上面的实例如果字符长度较大的话就会出现字符串缓冲区太小的错误解决方法: v_ename emp.ename%type;-->这种较好;
用法 :标识符号 表名.列名%type;
复合变量:用于存放多个值的变量 主要包括这几种:1pl/sql记录 2 pl/sql表 3嵌套表 4 varray;
复合类型 pl/sql记录:类似与高级语言中的结构体
declare type emp_record_type is record( --定义一个pl/sql记录类型emp_record_type ,类型包含三个数据name,salary,title
name emp.ename%type, salary emp.sal%type,title emp.job%type);
sp_record emp_record_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 ||'工资'||sp_record.salary);
end;/
复合类型 的pl/sql表 -->相当于高级语言中的数组,但是需要注意的是在高级语言中数组的下标不能为负数
declare type sp_table_type is table of emp.ename%type index by binary_integer;
--定义了一个pl/sql表类型sp_table_type,该类型是用于存放emp.ename%type ,index by binary_integer 表示下标是整数
sp_table sp_table_type;--定义了一个sp_table 变量 ,这个变量的类型是sp_table_type;
begin select ename into sp_table(0) from emp where empno=7788;
dbms_output.put_line('员工名' ||sp_table(0));
end;
提示:上面的列子如果把where删除会怎么样:就要使用参照变量;
参照变量是指用于存放数值指针的变量,通过使用参照变量,可以使得应用程序共享相同对象,从而降低了占用的空间
参照变量-ref cursor 变量 ,使用游标时,当定义游标时不需要指定相应的select 语句,但是当使用游标时(open)需要指定select语句,这样一个游标就与一个select语句结合了 实例如下: 1 使用pl/sql编写一个块,可以输入部门号,并显示该部门所有员工的姓名和他的工资
declare
type sp_emp_cursor is ref cursor;--定义游标
test_cursor sp_emp_cursor; --定义一个游标变量
v_ename emp.ename%type; v_sal emp.sal%type;--定义变量
begin open test_cursor for select ename ,sal from emp where deptno=&no; --把test_cursor 和一个select结合
loop --循环输出
fetch test_cursor into v_ename,v_sal;
exit when test_cursor%notfound; --判断游标是否为空
dbms_output.put_line('名字' ||v_ename ||'工资'||v_sal);
end loop;
end;
2,在1的基础上,如果某个员工的工资低于200元,就增加100;
