本文详细介绍了Oracle数据库的基础操作，包括SQL查询技巧、PL/SQL编程实践、存储过程与触发器的创建使用等内容。文章还提供了大量实用示例，帮助读者掌握Oracle数据库管理和应用开发的关键技能。
客户端
1.SqlPlus(客户端），命令行直接输入：sqlplus，然后按提示输入用户名，密码。
2.从开始程序运行:sqlplus，是图形版的sqlplus.
3.http://localhost:5560/isqlplus
Toad：管理，PlSqlDeveloper:
更改用户(cmd)
1.sqlplussys/密码assysdba
2.alteruserscottaccountunlock;(解锁账号)
tablestructure
(系统自带的表有emp、salgrade、dept、bonus、dual)
1.描述某一张表：desc表名
2.select*from表名
select语句：
1.计算数据可以用空表：比如：.select2*3fromdual
2.selectename,sal*12annual_salfromemp;与selectename,sal*12"annualsal"fromemp;
区别：加双引号保持原大小写，不加全变大写。
任何含有空值的数学表达式结果都为空值。
3.selectename||‘abcd’
||用来连接两个字符串
如果连接字符串中含有单引号，用两个单引号代替一个单引号。
第五课：distinct(不重复)
selectdeptnofromemp;
selectdistinctdeptnofromemp;
selectdistinctdeptno,jobfromemp
去掉deptno,job两者组合的重复。更多的项，就是这么多项的组合的不重复组合。
Where
select*fromempwheredeptno=10;
select*fromempwheredeptno<>10;不等于10
select*fromempwhereename='bike';
selectename,salfromempwheresalbetween800and1500(>=800and<=1500)
空值处理:
selectename,sal,commfromempwherecommis(not)null;
selectename,sal,commfromempwhereename(not)in('smith','king','abc');
模糊查询like：
%代表任意数量的任意字符
_代表一个任意字符
selectenamefromempwhereenamelike'_A%';
如果要查询含有%的，要用转义字符\
转义字符可以自定义：escape'自定义的转义字符' 比如:
selectenamefromempwhereenamelike'%$a%'escape'$';
orderby
select*fromdept;
默认按升序(asc)排列，要按降序(desc)用如下语句：
select*fromdeptorderbydeptnodesc;
selectename,sal,deptnofromemporderbydeptnoasc,enamedesc;
sqlfunction1:
selectename,sal*12annual_salfromemp
whereenamenotlike'_A%'andsal>800
orderbysaldesc;
selectlower(ename)fromemp;
selectenamefromemp
wherelower(ename)like'_a%';等同于
selectenamefromempwhereenamelike'_a%'orenamelike'_A%';
selectsubstr(ename,2,3)fromemp;从第二个字符开始截,一共截三个字符.
selectchr(65)fromdual结果为:A
selectascii('a')fromdual结果为:65
selectround(23.652,1)fromdual;结果为:23.7（第二个参数为指定四舍五入到哪位数）
selectround(23.652,-1)fromdual;20
selectto_char(sal,'$99,999.999')fromemp;（用9就可以在没有数字的地方不显示，如果用0的话一定会用0填充满）
selectto_char(sal,'L99,999.999')fromemp;L:代表本地符号
这个需要掌握牢:
selecthiredatefromemp;
显示为:
BIRTHDATE
----------------
17-12月-80
----------------
改为:
selectto_char(hiredate,'YYYY-MM-DDHH:MI:SS')fromemp;
显示:
BIRTHDATE
-------------------
1980-12-1712:00:00
-------------------
selectto_char(sysdate,'YYYY-MM-DDHH24:MI:SS')fromdual;//也可以改为:HH12
TO_CHAR(SYSDATE,'YY
-------------------
2007-02-2514:46:14
to_date函数:
selectename,hiredatefromempwherehiredate>to_date('1981-2-2012:34:56','YYYY-MM-DDHH24:MI:SS');
如果直接写birthdate>'1981-2-2012:34:56'会出现格式不匹配,因为表中的格式为:DD-MM月-YY.
selectsalfromempwheresal>888.88无错.但
selectsalfromempwheresal>$1,250,00;
会出现无效字符错误.
改为:
selectsalfromempwheresal>to_number('$1.250.00','$9,999,99');
把空值改为0
selectename,sal*12+nvl(comm,0)fromemp;
作用：把comm为空的地方用0代替，这样可以防止comm为空时,sal*12相加也为空的情况.
Groupfunction组函数（即从多行中得到一个输出）
牢记组函数：
max():返回一个数字列或计算列的最大值
min():返回一个数字列或计算列的最小值
avg():返回一个数字列或计算列的平均值
sum():返回一个数字列或计算列总和
count():返回找到的记录数
selectto_char(avg(sal),'99999999,99')fromemp;
selectround(avg(sal),2)fromemp;
结果:2073.21
selectcount(*)fromempwheredeptno=10;
selectcount(ename)fromempwheredeptno=10;count某个字段,如果这个字段不为空就算一个.
selectcount(distinctdeptno)fromemp;
selectsum(sal)fromemp;
Groupby语句
注意：count()是计数不是空值的数量
需求:现在想求每个部门的平均薪水.
selectavg(sal)fromempgroupbydeptno;
selectdeptno,avg(sal)fromempgroupbydeptno;
selectdeptno,job,max(sal)fromempgroupbydeptno,job;
求薪水值最高的人的名字.
selectename,max(sal)fromemp;出错,因为max只有一个值,但等于max值的人可能好几个,不能匹配.
应如下求:
selectenamefromempwheresal=(selectmax(sal)fromemp);
Groupby语句应注意,
出现在select中的字段,如果没出现在组函数中,必须出现在Groupby语句中.
Having对分组结果筛选
Where是对单条纪录进行筛选,Having是对分组结果进行筛选.
selectavg(sal),deptnofromemp
groupbydeptno
havingavg(sal)>2000;
查询工资大于1200雇员,按部门编号进行分组,分组后平均薪水大于1500,按工薪倒充排列.
selectavg(sal)fromemp
wheresal>1200
groupbydeptno
havingavg(sal)>1500
orderbyavg(sal)desc;
子查询
谁挣的钱最多(谁:这个人的名字,钱最多)
select语句中嵌套select语句,可以在where,from后.
问那些人工资,在平均工资之上.
selectename,salfromempwheresal>(selectavg(sal)fromemp);
查找每个部门挣钱最多的那个人的名字.
selectename,deptnofromempwheresalin(selectmax(sal)fromempgroupbydeptno)查询会多值.
应该如下:
把selectmax(sal),deptnofromempgroupbydeptno;当成一个表.语句如下:
selectename,salfromempjoin(selectmax(sal)max_sal,deptnofromempgroup
bydeptno)ton(emp.sal=t.max_salandemp.deptno=t.deptno);
每个部门的平均薪水的等级.
分析:首先求平均薪水(当成表),把平均薪水和另外一张表连接.
self_table_connection(自连接)
把某个人的名字以及他的经理人的名字求出来(经理人及这个人在表中同处一行)
分析:首先求出这个人的名字,取他的编号,然后从另一张表与其相对应编号,然后找到经理的名字.
selecte1.ename,e2.enamefromempe1,empe2wheree1.mgr=e2.empno.
empno编号和MGR都是编号.
SQL1999_table_connections
selectename,dname,gradefromempe,deptd,salgrades
wheree.deptno=d.deptnoande.salbetweens.losalands.hisaland
job<>'CLERK'
有没有办法把过滤条件和连接条件分开来?出于这样考虑,Sql1999标准推出来了.有许多人用的还是
旧的语法,所以得看懂这种语句.
selectename,dnamefromemp,dept;(旧标准).
selectename,dnamefromempcrossjoindept;(1999标准)
selectename,dnamefromemp,deptwhereemp.deptno=dept.deptno(旧)
selectename,dnamefromempjoindepton(emp.deptno=dept.deptno);1999标准.没有Where语句.
selectename,dnamefromempjoindeptusing(deptno);等同上句,但不推荐使用.
selectename,gradefromempejoinsalgradeson(e.salbetweens.losalands.hisal);
join连接语句,on过滤条件。连接，条件一眼分开。如果用Where语句较长时，连接语句和过滤语句混在一起。
三张表连接：
slectename,dname,gradefrom
empejoindeptdon(e.deptno=d.deptno)
joinsalgradeson(e.salbetweens.losalands.hisal)
whereenamenotlike'_A%';
把每张表连接条件不混在一起，然后数据过滤条件全部区分开来。读起来更清晰，更容易懂一点。
selecte1.ename,e2.enamefromempe1joinempe2on(e1.mgr=e2.emptno);
左外连接：会把左边这张表多余数据显示出来。
selecte1.ename,e2,enamefromempe1leftjoinempe2on(e1.mgr=e2.empno);left后可加outer
右外连接：把右边这张表多余数据显示出来。
selectename,dnamefromemperightouterjoindeptdon(e.deptno=d.deptno);outer可以取掉。
全外连接：即把左边多余数据，也把右边多余数据拿出来，
selectename,dnamefromempefulljoindeptdon(e.deptno=d.deptno);
PS：所谓的“外”连接，即把多余的数据显示出来。Outer关键字可以省略
16-23
求部门中哪些人的薪水最高：
selectename,salfromemp
join(selectmax(sal)max_sal,deptnofromempgroupbydeptno)t
on(emp.sal=t.max_salandemp.deptno=t.deptno)
A.求部门平均薪水的等级。
selectdeptno,avg_sal,gradefrom
(selectdeptno,avg(sal)avg_salfromempgroupbydeptno)t
joinsalgradeson(t.avg_salbetweens.losalands.hisal)
B.求部门平均薪水的等级
selectdeptno,avg(grade)from
(selectdeptno,ename,gradefromempjoinsalgradeson
(emp.salbetweens.losalands.hisal))t
groupbydeptno
C.哪些人是经理
selectenamefromempwhereempnoin(selectdistinctmgrfromemp);
更高效率的写法：selectenamefromempwhereempnoin(selectdistinctmgrfromemp);
D.不准用组函数，求薪水的最高值（面试题）
selectdistinctsalfromempwheresalnotin(
selectdistincte1.salfromempe1joinempe2on(e1.sal<e2.sal));
E.平均薪水最高的部门编号
selectdeptno,avg_salfrom
(selectavg(sal)avg_sal,deptnofromempgroupbydeptno)
whereavg_sal=
(selectmax(avg_sal)from
(selectavg(sal)avg_sal,deptnofromempgroupbydeptno)
)
F.平均薪水最高的部门名称
selectdnamefromdeptwheredeptno=
(
selectdeptnofrom
(selectavg(sal)avg_sal,deptnofromempgroupbydeptno)
whereavg_sal=
(selectmax(avg_sal)from
(selectavg(sal)avg_sal,deptnofromempgroupbydeptno)
)
)
组函数嵌套
如：平均薪水最高的部门编号，可以用更简单的方法如下：
selectdeptno,avg_salfrom
(selectavg(sal)avg_sal,deptnofromempgroupbydeptno)
whereavg_sal=
(selectmax(avg(sal))fromempgroupbydeptno)
组函数最多嵌套两层
G.求平均薪水的等级最低的部门的部门名称
分析：
首先求
1.平均薪水：selectavg(sal)fromgroupbydeptno;
2.平均薪水等级：把平均薪水当做一张表，需要和另外一张表连接salgrade
selectdeptno,grade,avg_salfrom
(selectdeptno,avg(sal)avg_salfromempgroupbydeptno)t
joinsalgradeson(t.avg_salbetweens.losalands.hisal)
上面结果又可当成一张表。
DEPTNOGRADEAVG_SAL
-------------------------
3031566.66667
2042175
1042916.66667
3.求上表平均等级最低值
selectmin(grade)from
(
selectdeptno,grade,avg_salfrom
(selectdeptno,avg(sal)avg_salfromempgroupbydeptno)t
joinsalgradeson(t.avg_salbetweens.losalands.hisal)
)
4.把最低值对应的3结果的那张表的对应那张表的deptno,然后把3对应的表和另外一张表做连接。
selectdname,t1.deptno,grade,avg_salfrom
(
selectdeptno,grade,avg_salfrom
(selectdeptno,avg(sal)avg_salfromempgroupbydeptno)t
joinsalgradeson(t.avg_salbetweens.losalands.hisal)
)t1
joindepton(t1.deptno=dept.deptno)
wheret1.grade=
(
selectmin(grade)from
(selectdeptno,grade,avg_salfrom
(selectdeptno,avg(sal)avg_salfromempgroupbydeptno)t
joinsalgradeson(t.avg_salbetweens.losalands.hisal))
)
结果如下：
DNAMEDEPTNOGRADEAVG_SAL
-------------------------------
SALES3031566.6667
H:视图（视图就是一张表，一个子查询）
G中语句有重复，可以用视图来简化。
【默认scott账户没有创建视图的权限，可通过如下语句授权：
首先登陆超级管理员：connsys/bjsxtassysdba;
授权：grantcreatetable,createviewtoscott;
再以scott账户登陆：connscott/tiger
】
创建视图：
createviewv$_dept_avg-sal_infoas
selectdeptno,grade,avg_salfrom
(selectdeptno,avg(sal)avg_salfromempgroupbydeptno)t
joinsalgradeson9t.avg_salbetweens.losalands.hisal)
然后G中查询可以简化成：
selectdname,t1.deptno,grade,avg_salfrom
v$_dept_avg-sal_infot1
joindepton(t1.deptno=dept.deptno)
wheret1.grade=
(selectmin(grade)fromv$_dept_avg-sal_infot1)
24、-------求比普通员工最高薪水还要高的经理人的名称-------
selectename,salfromempwhereempnoin
(selectdistinctmgrfromempwheremgrisnotnull)
andsal>
(
selectmax(sal)fromempwhereempnonotin
(selectdistinctmgrfromempwheremgrisnotnull)
)
25、---面试题：比较效率（理论上前一句效率高，但实际上可能Oracle可能会自动对代码优化，所以不见得后一句就会慢）
select*fromempwheredeptno=10andenamelike'%A%';//效率高，因为将过滤力度大的放在前面
select*fromempwhereenamelike'%A%anddeptno=10;
-------------------------------------------以上为select语句的内容-----------------------------------------
----------------创建新用户---------------
1、backupscott//备份
exp//导出
2、createuser
createuserguohailongidentified(认证)byguohailongdefaulttablespaceusersquota(配额)10Monusers
grantcreatesession(给它登录到服务器的权限),createtable,createviewtoguohailong
3、importdata
Imp
25、----------取消操作--------
rollback
-----------事务确认语句--------
commit;//此时再执行rollback无效
当正常断开连接的时候例如exit,事务自动提交。当非正常断开连接，例如直接关闭dos窗口或关机，事务自动提交
-----表的备份
createtabledept2asselect*fromdept;
-----插入数据
insertintodept2values(50,'game','beijing');
----只对某个字段插入数据
insertintodept2(deptno,dname)values(60,'game2');
-----将一个表中的数据完全插入另一个表中（表结构必须一样）
insertintodept2select*fromdept;
-----求前五名员工的编号和名称（使用伪字段rownum只能使用<或=要使用>必须使用子查询）
selectempno,enamefromempwhererownum<=5;
----求10名雇员以后的雇员名称--------
selectenamefrom(selectrownumr,enamefromemp)wherer>10;
----求薪水最高的前5个人的薪水和名字---------
selectename,salfrom(selectename,salfromemporderbysaldesc)whererownum<=5;
----求按薪水倒序排列后的第6名到第10名的员工的名字和薪水--------
selectename,salfrom
(selectename,sal,rownumrfrom
(selectename,salfromemporderbysaldesc)
)
wherer>=6andr<=10
-----面试题：有3个表S，C，SC
S（SNO，SNAME）代表（学号，姓名）
C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）
SC（SNO，CNO，SCGRADE）代表（学号，课号成绩）
问题：
1，找出没选过“黎明”老师的所有学生姓名。
2，列出2门以上（含2门）不及格学生姓名及平均成绩。
3，即学过1号课程有学过2号课所有学生的姓名。
答案：
1、
selectsnamefromsjoinscon(s.sno=sc.sno)joincon(sc.cno=c.cno)wherecteacher<>'黎明';
2、
selectsnamewheresnoin(selectsnofromscwherescgrade<60groupbysnohavingcount(*)>=2);
3、
selectsnamefromswheresnoin(selectsno,fromscwherecno=1andcnoin
(selectdistinctsnofromscwherecno=2);
)
27、--------------创建表--------------
createtablestu
(
idnumber(6),
namevarchar2(20)constraintstu_name_mmnotnull,
sexnumber(1),
agenumber(3),
sdatedate,
gradenumber(2)default1,
classnumber(4),
emailvarchar2(50)unique
);
28、五种约束条件：非空约束、唯一约束、主键约束、外键约束、检查约束（check）
--------------给name字段加入非空约束，并给约束一个名字，若不取，系统默认取一个-------------
createtablestu
(
idnumber(6),
namevarchar2(20)constraintstu_name_mmnotnull,
sexnumber(1),
agenumber(3),
sdatedate,
gradenumber(2)default1,
classnumber(4),
emailvarchar2(50)
);
--------------给nameemail字段加入唯一约束两个null值不为重复-------------
createtablestu
(
idnumber(6),
namevarchar2(20)constraintstu_name_mmnotnull,
sexnumber(1),
agenumber(3),
sdatedate,
gradenumber(2)default1,
classnumber(4),
emailvarchar2(50)unique
);
--------------两个字段的组合不能重复约束：表级约束-------------
createtablestu
(
idnumber(6),
namevarchar2(20)constraintstu_name_mmnotnull,
sexnumber(1),
agenumber(3),
sdatedate,
gradenumber(2)default1,
classnumber(4),
emailvarchar2(50)，
constraintstu_name_email_uniunique(email,name)
);
29、--------------主键约束-------------
createtablestu
(
idnumber(6),
namevarchar2(20)constraintstu_name_mmnotnull,
sexnumber(1),
agenumber(3),
sdatedate,
gradenumber(2)default1,
classnumber(4),
emailvarchar2(50)，
constraintstu_id_pkprimarykey(id),
constraintstu_name_email_uniunique(email,name)
);
--------------外键约束被参考字段必须是主键-------------
createtablestu
(
idnumber(6),
namevarchar2(20)constraintstu_name_mmnotnull,
sexnumber(1),
agenumber(3),
sdatedate,
gradenumber(2)default1,
classnumber(4)referencesclass(id),
emailvarchar2(50)，
constraintstu_class_fkforeignkey(class)referencesclass(id),
constraintstu_id_pkprimarykey(id),
constraintstu_name_email_uniunique(email,name)
);
红色为字段约束的写法，蓝色为表级约束的写法
createtableclass
(
idnumber(4)primarykey,
namevarchar2(20)notnull
);
31、修改表结构：
---------------添加字段--------------------------
altertablestuadd(addrvarchar2(29));
---------------删除字段--------------------------
altertablestudrop(addr);
---------------修改表字段的长度------------------
altertablestumodify(addrvarchar2(50));//更改后的长度必须要能容纳原先的数据
----------------删除约束条件----------------
altertablestudropconstraint约束名
-----------修改表结构添加约束条件---------------
altertablestuaddconstraintstu_class_fkforeignkey(class)referencesclass(id);
32、---------------数据字典表（有user_tables、user_views、user_constraints等等）----------------
---------------查看当前用户下面所有的表、视图、约束-----数据字典表user_tables---
selecttable_namefromuser_tables;
selectview_namefromuser_views;
selectconstraint_namefromuser_constraints;
存储数据字典表的信息的表：dictionary;
//该表共有两个字段table_namecomments
//table_name主要存放数据字典表的名字
//comments主要是对这张数据字典表的描述
33、-------------索引(能优化查询效率)------------------
createindexidx_stu_emailonstu(email);//在stu这张表的email字段上建立一个索引：idx_stu_email
----------删除索引------------------
dropindexindex_stu_email;
---------查看所有的索引----------------
selectindex_namefromuser_indexes;
---------创建视图-------------------
createviewv$stuasselesctid,name,agefromstu;
视图的作用:简化查询，保护我们的一些隐私数据，通过视图也可以用来更新数据，但是我们一般不这么用
缺点：要对视图进行维护
34、-----------创建序列（sequence，oracle特有的东西，一般用来做主键）------------
createsequenceseq;//创建序列
selectseq.nextvalfromdual;//查看seq序列的下一个值
dropsequenceseq;//删除序列
35、------------数据库的三范式--------------
（1）、要有主键，列不可分
（2）、不能存在部分依赖：当有多个字段联合起来作为主键的时候，不是主键的字段不能部分依赖于主键中的某个字段
（3）、不能存在传递依赖
=======================PL_SQL(过程化SQL语言)==========================
38、-------------------在客户端输出helloworld-------------------------------
setserveroutputon;//默认是off，设成on是让Oracle可以在客户端输出数据
begin
dbms_output.put_line('helloworld');
end;
/
----------------pl/sql变量的赋值与输出----
declare
v_namevarchar2(20);//声明变量v_name变量的声明以v_开头
begin
v_name:='myname';
dbms_output.put_line(v_name);
end;
/
39、-----------pl/sql对于异常的处理(除数为0)-------------
declare
v_numnumber:=0;
begin
v_num:=2/v_num;
dbms_output.put_line(v_num);
exception
whenothersthen
dbms_output.put_line('error');
end;
/
40、----------变量的声明----------
binary_integer:整数，主要用来计数而不是用来表示字段类型比number效率高
number:数字类型
char:定长字符串
varchar2：变长字符串
date：日期
long：字符串，最长2GB
boolean：布尔类型，可以取值true，false，null//最好给一初值
----------变量的声明，使用'%type'属性---------
declare
v_empnonumber(4);
v_empno2emp.empno%type;
v_empno3v_empno2%type;
begin
dbms_output.put_line('Test');
end;
/
//使用%type属性，可以使变量的声明根据表字段的类型自动变换，省去了维护的麻烦，而且%type属性，可以用于变量身上
41、组合变量：
---------------Table变量类型(相当于java里面的数组)-------------------------------------------
declare
typetype_table_emp_empnoistableofemp.empno%typeindexbybinary_integer;
v_empnostype_tabletype_table_empno;
begin
v_empnos(0):=7345;
v_empnos(-1):=9999;
dbms_output.put_line(v_empnos(-1));
end;
42、-----------------Record变量类型（相当于Java里面的类）--------------------------------------------
declare
typetype_record_deptisrecord
(
deptnodept.deptno%type,
dnamedept.dname%type,
locdept.loc%type
);
v_temptype_record_dept;
begin
v_temp.deptno:=50;
v_temp.dname:='aaaa';
v_temp.loc:='bj';
dbms_output.put_line(v_temp.deptno||''||v_temp.dname);
end;
-----------使用%rowtype声明record变量，直接参照表来声明record-------------------
declare
v_tempdept%rowtype;
begin
v_temp.deptno:=50;
v_temp.dname:='aaaa';
v_temp.loc:='bj';
dbms_output.put_line(vtemp.deptno||''||vtemp.dname)
end;
43、--------------select语句的运用(必须保证select语句有相应的返回记录)-------------------
declare
v_enameemp.ename%type;
v_salemp.sal%type;
begin
selectename,salintov_ename,v_salfromempwhereempno=7369;
dbms_output.put_line(v_ename||''||v_sal);
end;
---------------------------select语句的应用（record）----------------------------------------
declare
v_empemp%rowtype;
begin
select*intov_empfromempwhereempno=7369;
dbms_output_line(v_emp.ename);
end;
-------------insert语句的应用-----------------------------
declare
v_deptnodept.deptno%type:=50;
v_dnamedept.dname%type:='aaa';
v_locdept.loc%type:='bj';
begin
insertintodept2values(v_deptno,v_dname,v_loc);
commit;
end;
-------------update语句的应用------------------------------
declare
v_deptnoemp2.deptno%type:=50;
v_countnumber;
begin
updateemp2setsal=sal/2wheredeptno=v_deptno;
dbms_output.put_line(sql%rowcount||‘条记录被影响’);
commit;
end;
注：sql%rowcount统计上一条sql语句更新的记录条数
44、-----------------ddl语言，数据定义语言-----------------------
begin
executeimmediate'createtableT(nnnvarchar(30)default''a'')';
end;
------------------ifelse语句--------------------------------------
declare
v_salemp.sal%type;
begin
selectsalintov_salfromempwhereempno=7369;
if(v_sal<2000)then
dbms_output.put_line('low');
elsif(v_sal>2000)then
dbms_output.put_line('middle');
else
dbms_output.put_line('height');
endif;
end;
45、-------------------dowhile循环---------------------------
declare
ibinary_integer:=1;
begin
loop
dbms_output.put_line(i);
i:=i+1;
exitwhen(i>=11);
endloop;
end;
---------------------while循环---------------------------
declare
jbinary_integer:=1;
begin
whilej<11loop
dbms_output.put_line(j);
j:=j+1;
endloop;
end;
---------------------for循环---------------------------
begin
forkin1..10loop
dbms_output.put_line(k);
endloop;
forkinreverse1..10loop
dbms_output.put_line(k);
endloop;
end;
46、-----------------------异常(1)---------------------------
declare
v_tempnumber(4);
begin
selectempnointov_tempfromempwheredeptno=10;
exception
whentoo_many_rowsthen
dbms_output.put_line('太多记录了');
whenothersthen
dbms_output.put_line('error');
end;
-----------------------异常(2)---------------------------
declare
v_tempnumber(4);
begin
selectempnointov_tempfromempwhereempno=2222;
exception
whenno_data_foundthen
dbms_output.put_line('没有该项数据');
end;
----------------错误记录日志(用表记录：将系统日志存到数据库便于以后查看)-----------
创建序列（用来处理递增的ＩＤ）：
createsequenceseq_errorlog_idstartwith1incrementby1;
创建日志表:
createtableerrorlog
(
idnumberprimarykey,
errcodenumber,
errmsgvarchar2(1024),
errdatedate
);
示例程序：
declare
v_deptnodept.deptno%type:=10;
v_errcodenumber;
v_errmsgvarchar2(1024);
begin
deletefromdeptwheredeptno=v_deptno;
commit;
exception
whenothersthen
rollback;
v_errcode:=SQLCODE;
v_errmsg:=SQLERRM;
insertintoerrorlogvalues(seq_errorlog_id.nextval,v_errcode,v_errmsg,sysdate);
commit;
end;
47、---------------------PL/SQL中的重点cursor(游标)和指针的概念差不多----------------------
declare
cursorcis
select*fromemp;//此处的语句不会立刻执行，而是当下面的openc的时候，才会真正去数据库中取数据
v_empc%rowtype;
begin
openc;
fetchcintov_emp;
dbms_output.put_line(v_emp.ename);//这样会只输出一条数据134将使用循环的方法输出每一条记录
closec;
end;
----------------------使用dowhile循环遍历游标中的每一个数据---------------------
declare
cursorcis
select*fromemp;
v_empc%rowtype;
begin
openc;
loop
fetchcintov_emp;
(1) exitwhen(c%notfound);//notfound是oracle中的关键字，作用是判断是否还有下一条数据
(2) dbms_output.put_line(v_emp.ename);//(1)(2)的顺序不能颠倒，否则会把最后一条结果再多打印一次。
endloop;
closec;
end;
------------------------使用while循环，遍历游标---------------------
declare
cursorcis
select*fromemp;
v_empemp%rowtype;
begin
openc;
fetchcintov_emp;
while(c%found)loop
dbms_output.put_line(v_emp.ename);
fetchcintov_emp;
endloop;
closec;
end;
--------------------------使用for循环，遍历游标（最方便快捷的方法！）---------------------
declare
cursorcis
select*fromemp;
begin
forv_empincloop
dbms_output.put_line(v_emp.ename);
endloop;
end;
---------------------------带参数的游标（相当于函数）---------------------
declare
cursorc(v_deptnoemp.deptno%type,v_jobemp.job%type)
is
selectename,salfromempwheredeptno=v_deptnoandjob=v_job;
begin
forv_tempinc(30,'CLERK')loop
dbms_output.put_line(v_temp.ename);
endloop;
end;
-------------------------可更新的游标-----------------------------
declare
cursorc
is
select*fromemp2forupdate;
begin
forv_tempincloop
if(v_temp.sal<2000)then
updateemp2setsal=sal*2wherecurrentofc;
elsif(v_temp.sal=5000)then
deletefromemp2wherecurrentofc;
endif;
endloop;
commit;
end;
49、------------------------------storeprocedure存储过程(带有名字的程序块)-------------------
createorreplaceprocedurep
is--除了这两句替代declare，下面的语句全部都一样
cursorcis
select*fromemp2forupdate;
begin
forv_empincloop
if(v_emp.deptno=10)then
updateemp2setsal=sal+10wherecurrentofc;
elseif(v_emp.deptno=20)then
updateemp2setsal=sal+20wherecurrentofc;
else
updateemp2setsal=sal+50wherecurrentofc;
endif;
endloop;
commit;
end;
执行存储过程的两种方法：
（1）execp;(p是存储过程的名称)
（2）begin
p;
end;
/
-------------------------------带参数的存储过程
先创建存储过程：（in标识传入参数，out标识传出参数，默认为传入参数）
createorreplaceprocedurep
(v_ainnumber,v_bnumber,v_retoutnumber,v_tempinoutnumber)
is
begin
if(v_a>v_b)then
v_ret:=v_a;
else
v_ret:=v_b;
endif;
v_temp:=v_temp+1;
end;
再调用：
declare
v_anumber:=3;
v_bnumber:=4;
v_retnumber;
v_tempnumber:=5;
begin
p(v_a,v_b,v_ret,v_temp);
dbms_output.put_line(v_ret);
dbms_output.put_line(v_temp);
end;
------------------删除存储过程---------------------------
dropprocedurep;
50、------------------------创建函数计算个人所得税的税率-------------------------------------
createorreplacefunctionsal_tax
(v_salnumber)
returnnumber
is
begin
if(v_sal<2000)then
return0.10;
elsif(v_sal<2750)then
return0.15;
else
return0.20;
endif;
end;
-----------------------------创建触发器（trigger） 触发器不能单独的存在，必须依附在某一张表上
写主语谓语宾语游戏
创建触发器的依附表：
createtableemp2_log
(
enamevarchar2(30),
eactionvarchar2(20),
etimedate
);
createorreplacetriggertrig
afterinsertordeleteorupdateonemp2foreachrow--加上此句，每更新一行，触发一次，不加入则值触发一次
begin
ifinsertingthen
insertintoemp2_logvalues(USER,'insert',sysdate);
elsifupdatingthen
insertintoemp2_logvalues(USER,'update',sysdate);
elsifdeletingthen
insertintoemp2_logvalues(USER,'delete',sysdate);
endif;
end;
51、--------------------触发器用法之一：通过触发器更新约束的相关数据-------------------
createorreplacetriggertrig
afterupdateondept
foreachrow
begin
updateempsetdeptno=:NEW.deptnowheredeptno=:OLD.deptno;
end;
//////只编译不显示的解决办法setserveroutputon;
52、-------------------------------通过创建存储过程完成递归
createorreplaceprocedurep(v_pidarticle.pid%type,v_levelbinary_integer)is
cursorcisselect*fromarticlewherepid=v_pid;
v_preStrvarchar2(1024):='';
begin
foriin0..v_leaveloop
v_preStr:=v_preStr||'****';
endloop;
forv_articleincloop
dbms_output.put_line(v_article.cont);
if(v_article.isleaf=0)then
p(v_article.id);
endif;
endloop;
end;
-------------------------------查看当前用户下有哪些表---
首先，用这个用户登录然后使用语句：
select*fromtab;
-----------------------------用Oracle进行分页！--------------
因为Oracle中的隐含字段rownum不支持'>'所以：
select*from(
selectrownumrn,t.*from(
select*fromt_userwhereuser_id<>'root'
)twhererownum<6
)wherern>3
------------------------Oracle下面的清屏命令----------------
clearscreen;或者clescr;
-----------将创建好的guohailong的这个用户的密码改为abc--------------
alteruserguohailongidentifiedbyabc
当密码使用的是数字的时候可能会不行
扫一扫
780
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
