Oracle相关总结
Oracle相关总结
一、测试oracle安装是否成功
安装完Oracle如何验证安装是否成功呢？下面介绍测试方法：
1.执行操作系统级命令：tnspingorcl（假定全局数据库名为orcl）结果如下：
结果出现OK，说明侦听器配置无误，继续第2步。如果没有出现上图结果，请检查：
1）使用OracleNetConfigurationAssistant检查网络配置（netca命令）；2）使用DatabaseConfigurationAssistant检查数据库配置（dbca命令）。
2.执行操作系统级的命令：sqlplussys/root@orclassysdba连接成功后输入测试sql语句：select1+2+3sumfromdual;结果如下：
如果结果正确，表明数据库例程安装和启动正常。可以开始用刚安装完的Oracle开始其他工作。
二、新建用户、表空间并分配权限
Oracle安装完后，其中有一个缺省的数据库，除了这个缺省的数据库外，我们还可以创建自己的数据库。创建完数据库后，并不能立即在数据库中建表，必须先创建该数据库的用户，并且为该用户指定表空间。
明确要在哪个数据库下建立用户、表空间，就先用dba权限的用户（sys,system等）登录该数据库，再进行以下操作。
1.查找所有数据库文件存放的路径,可根据查到的路径定自己新建表空间数据文件存放的路径。（当然也可以自己定路径，不必参照，可略过此步）select*fromdba_data_files;2.创建表空间
createtablespaceorcl_VoicedReading--表空间名Datafile--数据库文件保存路径
"C:\\ORACLE\\PRODUCT\\10.2.0\\ORADATA\\ORCL\\orcl_VoicedReading.dbf"size100M--大小autoextendon;create
userwyli-用户名
identifiedbyroot--密码
defaulttablespaceorcl_VoicedReading;--指定默认表空间4.分配权限
--自动增长
3.创建用户并指定表空间
grantdbatowyli;--分配dba权限给用户wyli
到此，为数据库新建用户完成，可以用新建的用户以normal身份登录，然后开始在数据库上做建表等工作。
三、写Oracle存储过程要注意的问题
1.在oracle中，数据表别名不能加as，如：selecta.appnamefromappinfoa;--正确selecta.appnamefromappinfoasa;--错误
2.在存储过程中，select某一字段时，后面必须紧跟into，如果select整个记录，利用游标的话就另当别论了。
selectaf.keynodeintoknfromAPPFOUNDATIONafwhereaf.appid=aidandaf.foundationid=fid;--有into，正确编译
selectaf.keynodefromAPPFOUNDATIONafwhereaf.appid=aidandaf.foundationid=fid;
--没有into，编译报错，提示：Compilation
3.在利用select...into...语法时，必须先确保数据库中有该条记录，否则会报出"no
datafound"异常。
可以在该语法之前，先利用selectcount(*)from查看数据库中是否存在该记录，如果存在，再利用select...into...
4.在存储过程中，别名不能和字段名称相同，否则虽然编译可以通过，但在运行阶段会报错selectkeynodeintoknfromAPPFOUNDATIONwhereappid=aidandfoundationid=fid;--正确运行
selectaf.keynodeintoknfromAPPFOUNDATIONafwhereaf.appid=appidandaf.foundationid=foundationid;
5.在存储过程中，关于出现null的问题假设有一个表A，定义如下：createtableA(
idvarchar2(50)primarykeynotnull,vcountnumber(8)notnull,
bidvarchar2(50)notnull--外键);
如果在存储过程中，使用如下语句：
selectsum(vcount)intofcountfromAwherebid="xxxxxx";如果A表中不存在bid="xxxxxx"的记录，则fcount=null(即使fcount定义时设置了默认值，如：fcountnumber(8):=0依然无效，fcount还是会变成null)，这样以后使用fcount时就可能有问题，所以在这里最好先判断一下：iffcountisnullthenfcount:=0;endif;
--运行阶段报错
四、企业库连接Oracle
企业库(3.1)连接Oracle
命名空间Microsoft.Practices.EnterpriseLibrary.Data;中的DatabaseFactory类提供两个方法来创建database对象。
1.用方法DatabaseFactory.CreateDatabase(string)
配置文件：
示例代码：
usingMicrosoft.Practices.EnterpriseLibrary.Data;usingSystem.Data.Common;publicboolIsExistBackgroundMusic(stringbgMusicName){//创建database实例Databasedb=DatabaseFactory.CreateDatabase("Oracle");stringproduceName="proce_BackgroundMusic_Exist";DbCommandcommand=db.GetStoredProcCommand(produceName);//为command提供参数db.AddInParameter(command,"ParamBackgroundMusicName",DbType.String,bgMusicName);db.AddOutParameter(command,"outCount",DbType.Int32,4);//执行存储过程db.ExecuteScalar(command);intoutCount=(int)db.GetParameterValue(command,"outCount");if(outCount==0){returnfalse;}returntrue;}
2.用方法DatabaseFactory.CreateDatabase()
配置文件：
示例代码：
usingMicrosoft.Practices.EnterpriseLibrary.Data;usingSystem.Data.Common;publicboolIsExistBackgroundMusic(stringbgMusicName){Databasedb=DatabaseFactory.CreateDatabase();stringproduceName="proce_BackgroundMusic_Exist";DbCommandcommand=db.GetStoredProcCommand(produceName);db.AddInParameter(command,"ParamBackgroundMusicName",bgMusicName);db.AddOutParameter(command,"outCount",DbType.Int32,4);db.ExecuteScalar(command);intoutCount=(int)db.GetParameterValue(command,"outCount");if(outCount==0){returnfalse;}returntrue;}
DbType.String,
扩展阅读：Oracle知识点总结
Oracle知识点总结
根据阎赫老师讲义整理Zealjiang1、Oracle数据库的安装和配置⑴OracleInternet级数据库SQLServer中小企业级数据库Access桌面级数据库⑵Oracle的安装
注意：来源和目标的目录不允许有中文或空格⑶Oracle数据库启动时必需开启的后台服务
①OracleOrahome90TNSListener使第三方的软件或语言访问②OracleServiceETCOracle的实例CRUD增删改查注意：②中的ETC是你起的实例的名字⑷Oracle的开发工具
①DOS界面的开发平台->运行->sqlplus②Oracle本身基于DOS的平台->运行->sqlplusw③OracleEnterpriseManagerConsole④PL/SQLDeveloper7.1.5⑸创建一个表的完整流程①创建一个数数库
例子：创建一个数据库ETC,物理文件放在F:\\，初始化1m,自增长开启
createtablespaceetcdatafile"f:\\etc.dbf"size1mautoextendon;
删除表空间
droptablespace数据库名称;②为该数据库创建一个帐号和密码语法：createuser用户名称identifiedby密码
defaulttablespace数据库名称
注意：1、在Oracle中账号和数据库one2one绑定2、Oracle的密码不允许为纯数字或空3、需要在system账号完成动作修改Oracle的账号密码语法：alteruser用户identifiedby新密码③用grant权限to用户为该帐户授权语法：grant权限名称to用户;撤销权限
语法：revoke权限名称from用户名;④在该帐号下创建表⑹Oracle中的事务处理Transacation事务
特点：整体提交(commit)整体回滚(rollback)事务的四个特性
①原子性不可分割
②持久性->当数据整整写入到数据库物理文件中后，该数据被持久化
③隔离性->事务之间相互独立互不干扰④一致性->数据安全⑺Oracle中的保留点(还原点)关键字：savepoint使用方法：savepoint名称;如进行保留点回滚rollbacktomark2⑻Oracle备份数据表数据
①createtable备份表名称as查询语句;②Oracle备份表结构
createtableemp_bakasselect*fromempwhere1=2;2、Oracle的函数
⑴function（系统预定义函数）
函数和存储过程相比，在于函数必须有返回值，而存储过程只有输出参数。语法：createorreplacefunction函数名称return返回类型as
PL/SQL语句块
例子：输入一个员工工号，返回该员工所在部门平均工资
createorreplacefunctionfun_demo1(enoemp.empno%type)returnemp.sal%typeas
avgsalemp.sal%type;begin
selectavg(sal)intoavgsalfromempwhereempno=eno;returnavgsal;end;
调用：selectfun_demo1(7788)fromdual;
publicintadd(intnum1,intnum2){intres=0;res=num1+num2;returnres;}
⑵单行函数(单值函数)：函数有且只有返回1个值|-字符函数substrinstr①字符串连接函数：concatconcat(字符串1,字符串2)Oracle中字符连接操作符号"||"
②字符串对齐函数：lpad->leftpadding左填充/rpad语法：lpad(字符串,预留位置,填充字符)
SQL:selectlpad(ename,10,"")fromemp;③字符串截取函数：substr->substring
语法：substr(字符串,截取的起始位置,截取个数)substr(字符串,截取的起始位置)SQL:>selectsubstr(ename,1,3)fromeemp;④字符查找函数：instr->innerstring
语法：instr(字符串,待找的字符,查找的起始位置,出现次数)SQL:>selectename,instr(ename,"T",1,1)fromemp;注意：当返回值为0时，字符不存在⑤函数：initcap->initialcaptor语法：initcap(字符串)
SQL:>selectinitcap(ename)fromemp;⑥函数：length
语法：length(字符串)
SQL:>select*fromempwherelength(ename)=5;⑦函数：lower/upper语法：lower(字符串)SQL:>selectlower(ename)fromemp;
|-日期函数sysdateadd_months①函数：sysdate
SQL:>selectsysdatefromdual;dual-无实际意义，函数测试或其他测试使用
②月份差：months_between
语法：months_between(日期1,日期2)SQL:>select*fromempwheremonths_between(sysdate,hiredate)>=144③函数：last_day
语法：last_day(日期数据)SQL:>select*fromempwherelast_day(hiredate)-2=hiredate;④函数：add_months
语法：add_months(时间,添加月份)
SQL：>selectadd_months(sysdate,3)fromdual;|-转换函数to_charto_date①转换函数to_char
语法：to_char(时间数据,制定格式)
SQL:>selectto_char(sysdate,"yyyy-mm-ddhh24:mi:ssddddday")fromdual;yyyy-mm-dd年月日hh24:mi:ss时分秒(24)d一周第几天ddd一年的第几天day星期几
②to_date:将文本类型数据按照制定格式转换成时间类型数据语法：to_date("具体的时间字符数据","时间字符串格式")to_date("201*-01-19","yyyy-mm-dd")注意：Oracle中的date字段类型及其特殊|-数学函数absceilfloorabs(n)绝对值
ceil(n)向上取值ceilling天花板ceil(1.1)->2floor(n)向下取值floor地板floor(2.9)->2mod(m,n)求模取余mod(5,2)->1power(m,n)m的n次方power(2,8)->256
round(m,n)四舍五入m待处理数据n精度round(3.1415,2)->3.14
trunc(m)整数截取trunc(3.1415)=3sign(m)符号sqrt(m)平方根|-混合函数usernvl①user:返回当前登录账号SQL>selectuserfromdual;②nvl:替空函数
语法：nvl(字符串,替换后的数据)
注意：替换后的数据必须与该字段类型保持一致SQL>selectename,nvl(comm,0)fromemp;
⑶分析函数：数据分析和挖掘功能DataMiningrankdense_rank作用：主要用于排名使用函数：rank
①语法：rank()over(orderby字段名称)
SQL:>selectrank()over(orderbysaldesc),ename,salfromemp;若排名相同下一个将跳转
SQL:>selectdense_rank()over(orderbysaldesc),ename,salfromemp;连续
②语法：rank(参数1，参数2)withingroup(orderby字段1，字段2)
SQL:>selectename,rank(2850,"BLAKE")withingroup(orderbysal,ename)fromemp;
⑷分组函数：类似于SQLServer中的聚合函数sumavgcountgroupby分组关键字having条件3、Oracle数据库的查询⑴制定区间查询
Oracle中伪列：Oracle为每张数据表(包括用户自定义创建的数据表)都自动创建两个位列分别是：rowid、rownum通过命令：SQL>desc表名;--查看表结构rowid:由18个字符组成唯一标识每一行rownum:行号
⑵介绍问题：使用Oracle分层查询以数据结构显示emp表中的人员关系语法：selectlpad(ename,level*5,"")fromempconnectbypriorempno=mgrstartwithmgrisnull;
⑶并集：查询部门10的办事员和部门20的经理关键字：unionall
select*fromempwheredeptno=10andjob="CLERK"unionall
select*fromempwheredeptno=20andjob="MANAGER";
注意：unionall不会消除查询结果中的重复数据,union会消除查询结果中的重复记录
⑷交集：查询部门10和部门20都有的工作类型关键字：intersect
selectjobfromempwheredeptno=10intersect
selectjobfromempwheredeptno=20;
⑸差集：查询部门30中有，而部门10中没有的工作类型关键字：minusselectjobfromempwheredeptno=30minus
selectjobfromempwheredeptno=10;
4、Oracle数据库的数据对象
Oracle的数据库对象：用户、表、约束、序列、视图、同义词和索引定义：但凡使用"create"开头创建的对象称之为数据库对象。⑴锁定/解除用户
语法：alteruser用户名称accountlock;注意：该指令一般在system账号下输入解除用户锁定
语法：alteruser用户名称accountunlock;SQL>alteruserscottaccountunlock;
注意：Oracle11g中scott账号是默认锁定的，需要进行解锁处理。⑵用户授权/撤销
grant权限名称to用户名称;撤销用户权限
revoke权限名称from用户名;⑶表
数据字段的类型①数字类型：number语法：number(长度,精度)number(5,2)数字长度为3，精度为2范围：-999.99~999.99number(5)数字长度为5，精度为0范围：-99999~99999number精度默认为0范围：-32767~32768②字符类型：varchar2可变长度char字符类型语法：varchar2(长度)③日期类型：date
④long数据类型：该字段最大存储空间为2GB，该字段不允许添加索引
LOB数据类型：该字段最大存储空间为4GB，该字段不允许添加索引CLOB：大字符类型->文章(小说，cnki)
BLOB：大二进制类型->图片、音频、视频.....多媒体文件FLOB：文件定位器->内存指针⑷创建数据表的语法结构：createtable表名(
字段名称1类型[约束],字段名称1类型[约束],
字段名称n类型[约束])
①使用desc表名查看表结构②给表添加字段address和telephone语法：altertable表名add(字段名称类型);③删除表字段address
语法：altertable表名dropcolumn字段名称;④修改表中已有字段的类型
语法：altertable表名modify(字段名称新类型);⑤修改表名称
语法：rename原表名to新表名;约束
完整性约束=准确性+一致性⑸约束的四大分类：
①实体完整性约束(行约束)->尽量减少数据表中数据的冗余(重复的数据)
技术实施：主键约束、唯一约束
语法：altertable表名addprimarykey(字段名称);altettable表名addunique(字段名称);添加非空约束
语法：altertable表名modify(字段名称notnull);
②域完整性约束(列约束)->达到数据的准确性，控制数据的大小或范围或格式
技术实施：check检查约束
altertable表名addconstraint约束名称check(条件);③引用完整性约束(表间约束)->达到数据的一致性技术实施：外键约束添加外键约束
altertable外键表addconstraint约束名称foreignkey(外键字段)references主键表(主键字段);删除外键约束
语法：altertable表名dropconstraint约束名称;删除匿名约束
语法：altertable表名modify(字段名称null);
④自定义完整性约束->以上三种数据库内置约束不能满足开发人员的需求是，需要开发人员自定一些约束条件技术实施：触发器⑹使用sql脚本批量插入数据使用命令SQL>@路径+文件名称SQL>@f:/a.sql;⑺序列
定义：Oracle中使用sequence来实现字段的自增长功能，和SQLServer中的identity属性类型语法：createsequence序列名称startwith起始数字incrementby增长量;
用法：序列对象通过两个重要的属性进行访问取值.nextval->nextvalue->下一个值例子：selectseq_1.nextvalfromdual;.currval->currentvalue->当前值例子：selectseq_1.currvalfromdual;如何实现其自增长功能
通过语法结构将序列对象与对应的数据表进行绑定，实现其自增长功能。
例子：createtableusers(useridnumber(2),usernamevarchar2(10));
createsequenceseq_usersstartwith1incrementby1;insertintousersvalues(seq_users.nextval,"alvin");注意：Oracle建议一个序列对象尽与一张数据表进行绑定技巧：createsequence序列名称;默认从1每次增长1⑻视图
作用：1、简化复杂的SQL语句2、提高数据的访问安全性语法：createorreplaceview视图名称as
复杂的SQL查询语句
视图是一张虚拟的数据表，在Table对象中不存在，只存在于内存中注意：若视图由1张基表组成，修改视图将会修改基表数据，若视图由多张表组成，则不会修改基表数据⑼同义词
作用：Oracle中的同义词提供各种数据库对象(表)的别名，目的在于提高数据表访问的安全性，尤其多用户并发访问时。语法：createsynonym表的别名for表名;SQL>createsynonymbak1foremp;扩展：公有同义词public
语法：createpublicsynonym别名for表名;⑽索引
作用：提高SQL查询语句按照制定字段查询的效率语法：createindex索引的名称on数据表(字段)
例子：为hiredate字段添加索引，提高按日期查询的SQL语句的效率
createindexidx_hireonemp(hiredate)原理：空间换效率SQL语句索引优化规则
①不带where条件的SQL语句一定不能使用索引②在where条件中有索引的字段不能使用函数例子：假设我们查询在1987年参加工作的所有员工
SQL>select*fromempwhereto_char(hiredate,"yyyy")="1987";SQL>select*fromempwherehiredate=to_date("1987","yyyy");③在where条件中有索引的字段不能参与运算例子：查询在10000天以前参加工作的员工信息SQL>select*fromempwheresysdate-hiredate>10000;SQL>select*fromempwherehiredateselecte.*,d.dnamefromempe,deptdwheree.deptno=d.deptnoandd.dname="ACCOUNTING";⑾Oracle中常用的数据字典
Oracle是以中以表管表的模式，同时数据字典是有表或视图组成。数据字典的分类：
USER_xxx：表示当前用户所拥有的数据库对象ALL_xxxx：表示当前用户与权力查看的数据库对象
DBA_xxxx：表示数据中所拥有的全部对象，只有在超级管理员级别下才可查看。
xxxx：代表数据库对象的复数形式，例如：tablesindexes.......
5、Oracle数据库的多表查询
⑴等连接：selectemp.ename,dept.dnamefromemp,deptwhereemp.deptno=dept.deptno;使用内连接模式编写：⑵内连接：innerjoin......on......
selectemp.ename,dept.dnamefromempinnerjoindeptonemp.deptno=dept.deptno;
分析：部门为主显示字段，dept为主表姓名为辅助显示字段，emp为辅助表
⑶外连接：selectd.dname,e.enamefromempe,deptdwheree.deptno(+)=d.deptno;
6、Oracle数据库的游标游标cursor
游标的分类：隐式游标：Oracle自动应以一个隐式游标名称为SQL,该游标不被程序员控制，自动开启、操作及结束。显式游标：可供程序员自己创建及操作|---静态游标|---动态游标⑴静态游标loop循环游标/for循环游标
====loop循环游标=====步骤1：创建一个游标
cursor游标名称isSQL查询语句;
步骤2：开启游标
open游标名称;->执行定义的SQL查询语句并将结果集合存放到游标中
步骤3：使用loop循环遍历游标中的数据并进行相应处理loop
fetch游标名称into变量;exitwhen游标名称%notfound;.....endloop;步骤4：关闭游标close游标名称;
例子：显示部门编号为10的员工姓名declare
--步骤1：创建一个游标
cursormycurisselectenamefromempwheredeptno=10;enemp.ename%type;begin
--步骤2：开启游标openmycur;
--步骤3：使用loop循环遍历游标中的数据并进行相应处理loop
fetchmycurintoen;exitwhenmycur%notfound;dbms_output.put_line(en);endloop;
--步骤4：关闭游标closemycur;end;
=======for循环游标=======declare
--步骤1：创建一个游标
cursormycurisselect*fromempwheredeptno=10;erowemp%rowtype;begin
--步骤3：使用for循环遍历游标中的数据并进行相应处理forerowinmycurloopdbms_output.put_line(erow.ename);endloop;end;
⑵动态游标(扩展)
作用：使用游标变量高度重用(只能使用loop循环)语法结构：
步骤1：声明一个动态游标类型type类型名称isrefcursor;
步骤2：使用声明号的类型创建一个游标变量游标名称类型名称;
步骤3：打开游标并且绑定SQL语句open游标名称forSQL查询语句
步骤4：使用循环遍历游标中的数据并进行处理loop
fetch游标名称into变量;exitwhen游标名称%notfound.....数据处理......endloop;步骤5：重复3~步骤6：关闭游标close游标名称;
例子：显示部门30的员工姓名及dept表中全部部门名称declare
--声明一个游标类型typecurisrefcursor;--声明游标变量mycurcur;--声明变量erowemp%rowtype;drowdept%rowtype;begin--打开游标
openmycurforselect*fromempwheredeptno=30;loop
fetchmycurintoerow;exitwhenmycur%notfound;
dbms_output.put_line(erow.ename);endloop;
dbms_output.put_line("============");--打开游标
openmycurforselect*fromdept;loop
fetchmycurintodrow;exitwhenmycur%notfound;
dbms_output.put_line(drow.dname);endloop;--关闭游标closemycur;end;
⑶游标所有属性游标名称%属性名称属性名称含义
notfound当游标中没有遍历的数据时返回1found当游标中存在有遍历的数据时返回1isopen当游标开启状态时返回1rowcount返回游标影响行数
7、Oracle数据的PL/SQL⑴PL/SQL块
语法格式：declare声明
......变量声明区begin程序开始......执行语句区exception异常处理部分.....
end;程序结束;①变量的声明
标量标识符，不区分大小写，先声明再使用变量声明的语法结构：标量名称类型;例子：declare
enonumber(4);edatedate;beginend;②★属性引用类型|-字段引用类型例子：declareenoemp.ename%type;|-行引用类型例子：declareerowemp%rowtype;③变量的赋值四种方式：◆声明时赋值赋值运算符“:=”
例子：声明一个变量赋值为2declare
num1number(2):=2;.....begin
.....end;◆执行区赋值
例子：声明一个变量赋值为2declare
num1number(2);.....beginnum1:=2;.....end;
◆对话框赋值->程序与用户之间进行数据交互例子：声明一个变量赋值为2declare
num1number(2);.....begin
num1:=&请输入数字;.....end;
注意点：当输入字符串类型数据的使用书写格式为：name:="&姓名";当输入数字类型数据的使用书写格式为：age:=&年龄;◆select....into....赋值模式
例子：将emp表中simth的工资复制到变量esal中declare
esalemp.sal%type;begin
selectsalintoesalfromempwhereename="SMITH";end;
④Oracle的PL/SQL中屏幕输出语句
语法：dbms_output.put_line("xxxxxxx");将信息输出并换行dbms_output.put("xxxxxxx");输出不换行
注意：Oracle默认关闭屏幕输出功能，需要开发人员输入指令将其功能开启
SQL>setserveroutputon;⑤PL/SQL中的异常处理语法：exception
when异常名称then....处理方式.....异常：others->任何异常都可捕获⑵执行语句部分
①循环的使用：forwhileloop
for语法结构：for循环变量in起始数字..结束数字loop
....循环体....endloop;
例子1：显示数字10~1(reverse)declareinumber;begin
foriinreverse1..10loop
dbms_output.put_line(i);endloop;end;
例子1：显示乘法口诀declareinumber;jnumber;begin
--外层循环控制行数foriin1..9loop
--内层循环控制个数forjin1..iloop
dbms_output.put(j||"*"||i||"="||i*j||"");endloop;--换行
dbms_output.put_line("");endloop;end;
②while循环
语法：while进入条件loop
....循环体.....endloop;③loop循环语法：loopexitwhen推出条件;...循环体....endloop;⑶条件判断
语法：if条件thenelsif条件thenelseendif;
例子：模拟一个登录结构，用户名为admin密码为chinasoftdeclare
usernamevarchar2(10);passwordvarchar2(10);begin
--用户输入账号及密码username:="&账号";password:="&密码";--逻辑判断
ifusername="admin"andpassword="chinasoft"thendbms_output.put_line("欢迎登录："||username);else
dbms_output.put_line("账号或密码错误！");endif;end;
⑷处理自定义异常
例子：若用户输入的数字不再1~10之内则抛出异常
步骤1：创建一个异常对象在变量声明区输入一下代码
expexception;--声明一个异常对象，名称为exp步骤2：编写条件判断结构抛出异常在语句执行区编写一下代码if.....then
raise异常对象名称;--抛出异常endif;步骤3：异常捕获在语句执行区编写exception
when异常对象名称then......处理方式........declare
nnumber;--用来接收用户输入的数字expexception;--创建一个异常对象beginn:=&数字;--判断
ifn10thenraiseexp;--抛出异常endif;
dbms_output.put_line(n);exceptionwhenexpthen
dbms_output.put_line("数字范围不再1~10之内");end;⑸
8、Oracle数据库中的存储过程、函数、数据包⑴Oracle中的存储过程
Oracle中的PL/SQL语句块，没有名字，成为匿名的PL/SQL块，代码重用性低。需要为PL/SQL块起名字，起名后的PL/SQL块统称为子程序。子程序的分类：存储过程、自定义函数①存储过程
语法：createorreplaceprocedure存储过程名称as
PL/SQL块②如何调用存储过程
两种方式：1、指令方式->exec存储过程名称;2、使用PL/SQL块调用begin
存储过程名称;end;③带参数的存储过程两大类：a)带输入参数b)带输出参数
a)带输入参数的存储过程
语法：createorreplaceprocedure存储过程名称(参数名称1in类型,....,参数名称n类型)as
PL/SQL块b)带输出参数的存储过程
语法：createorreplaceprocedure存储过程名称(参数名称1out类型,....,参数名称nout类型)as
PL/SQL块例子：显示部门10的平均工资(使用输出参数)
createorreplaceprocedurepro_demo3(avgsaloutemp.sal%type)asbegin
selectavg(sal)intoavgsalfromempwheredeptno=10;end;
调用：只能使用PL/SQL块declare
getsalemp.sal%type;begin
pro_demo3(getsal);
dbms_output.put_line(getsal);end;
/1、输入指定员工的员工编号，输出其所在部门的其他姓名(存储过程、带参数的游标、输入参数)
思路：*1、使用SQL语句查询7788员工所在部门的其他员工信息select*fromempwheredeptno=(selectdeptnofromempwhereempno=7788)andempno7788;*2、修改成PL/SQL块declare
cursormycur(enoemp.empno%type)isselect*fromempwheredeptno=(selectdeptnofromempwhereempno=eno)andempnoeno;erowemp%rowtype;tmpemp.empno%type;begintmp:=&工号;
forerowinmycur(tmp)loop
dbms_output.put_line(erow.ename);endloop;end;
*3、修改成为存储过程
createorreplaceprocedurepro_demo4(tmpemp.empno%type)as
cursormycur(enoemp.empno%type)isselect*fromempwheredeptno=(selectdeptnofromempwhereempno=eno)andempnoeno;erowemp%rowtype;begin
forerowinmycur(tmp)loop
dbms_output.put_line(erow.ename);endloop;end;*4、调用
execpro_demo4(7788);
/2、输出制定员工所在部门的平均工资(存储过程、输入参数、输出参数)思路：*1、编写SQL语句
selectavg(sal)fromempwheredeptno=(selectdeptnofromempwhereempno=7788)*2、PL/SQL语句declare
enoemp.empno%type;esalemp.sal%type;begineno:=&编号;
selectavg(sal)intoesalfromempwheredeptno=(selectdeptnofromempwhereempno=eno);dbms_output.put_line(esal);end;*3、修改为存储过程
createorreplaceprocedurepro_demo5(enoinemp.empno%type,esaloutemp.sal%type)asbeginselectavg(sal)intoesalfromempwheredeptno=(selectdeptnofromempwhereempno=eno);end;*4、调用declare
tmpemp.sal%type;begin
pro_demo5(7788,tmp);dbms_output.put_line(tmp);end;⑵程序包
程序包是一个函数或存储过程的集合。程序包的结构：第一部分：程序包的声明createorreplacepackage包名称as
存储过程的声明;自定义函数的声明;end;
第二部分：程序包主体
createorreplacepackagebody包名称as存储过程的实现;自定义函数的实现;
例子：完成对EMP表进行增删改的操作part1:
createorreplacepackageemp_pack
procedureadd_emp(enoemp.empno%type,enmemp.ename%type,dnoemp.deptno%type);
procedureupdate_emp(enoemp.empno%type,esalemp.sal%type);proceduredelete_emp(enoemp.empno%type);end;part2:
createorreplacepackagebodyemp_packas--添加
procedureadd_emp(enoemp.empno%type,enmemp.ename%type,dnoemp.deptno%type)asbegin
insertintoemp(empno,ename,sal)values(eno,enm,dno);dbms_output.put_line("addisok");end;--修改procedureupdate_emp(enoemp.empno%type,esalemp.sal%type)asbegin
updateempsetsal=esalwhereempno=eno;dbms_output.put_line("updateisok");end;--删除
proceduredelete_emp(enoemp.empno%type)asbegin
deletefromempwhereempno=eno;dbms_output.put_line("deleteisok");end;end;
如何调用exec程序包名称.过程名称(参数);select程序包名称.函数名称(参数)fromdual;⑶触发器
关键字：trigger->扳机、目标当需要级联操作的时候可能用到触发器语法：createorreplacetrigger触发器名称
before|afterinsertorupdateordelete[of字段名称]on表begin
PL/SQL语语句
案例：当对Emp进行CUD操作时候后经行出发createorreplacetriggertri_empafterinsertorupdateordeleteonempbegin
ifinsertingthen
dbms_output.put_line("addisok");elsifupdatingthen
dbms_output.put_line("updateisok");else
dbms_output.put_line("deleteisok");endif;end;
触发器的分类：分为：表级触发
行及触发foreachrow
例子：更新Emp表中部门为10的员工工资表级触发
createorreplacetriggertri_demo1afterupdateonempbegin
ifupdatingthen
dbms_output.put_line("更新isok");endif;end;行级触发
createorreplacetriggertri_demo1afterupdateonempforeachrowbegin
ifupdatingthen
dbms_output.put_line(:old.empno||"更新isok");endif;end;
在触发器中:old代表原数据对象(一行对象),:new代表新的数据对象
案例：删除Emp中指定数据的时候，将删除的数据自动备份到另外一张表中。
步骤1：创建一张备份表createtableemp_bak2asselect*fromempwhere1=2;步骤2：编写触发器自动完成数据备份功能createorreplacetriggertri_demo2beforedeleteonempforeachrowbegin
insertintoemp_bak2
values(:old.empno,:old.ename,:old.job,:old.mgr,:old.hiredate,:old.sal,:old.comm,:old.deptno);
dbms_output.put_line("数据备份成功");end;
9、Oracle数据库中的备份与还原10、Oracle数据库优化
友情提示：本文中关于《Oracle相关总结》给出的范例仅供您参考拓展思维使用，Oracle相关总结：该篇文章建议您自主创作。
来源：网络整理 免责声明：本文仅限学习分享，如产生版权问题，请联系我们及时删除。
公文素材库友情提示：本网站所有内容为共享上传提供，不涉及任何商业利益，本站对此不承担任何保证责任！
Copyright © www.bsmz.net Corporation, All Rights Reserved 共享时代 共享你我他 版权所有