回 帖 发 新 帖 刷新版面

主题:[转帖]sql语句编程手册(实用+练习)

一、SQL PLUS  
1 引言  

SQL命令  
以下17个是作为语句开头的关键字:  
alter  drop  revoke  
audit  grant  rollback*  
commit*  insert  select  
comment  lock  update  
create  noaudit  validate  
delete  rename  
这些命令必须以“;”结尾  
带*命令句尾不必加分号,并且不存入SQL缓存区。  

SQL中没有的SQL*PLUS命令  
这些命令不存入SQL缓存区  
@  define  pause  
#  del  quit  
$  describe  remark  
/  disconnect  run  
accept  document  save  
append  edit  set  
break  exit  show  
btitle  get  spool  
change  help  sqlplus  
clear  host  start  
column  input  timing  
compute  list  ttitle  
connect  newpage  undefine  
copy  

---------  
2 数据库查询  

数据字典  
TAB 用户创建的所有基表、视图和同义词清单  

DTAB 构成数据字典的所有表  

COL 用户创建的基表的所有列定义的清单  

CATALOG 用户可存取的所有基表清单  

select * from tab;  

describe命令 描述基表的结构信息  
describe dept  

select *  
from emp;  

select empno,ename,job  
from emp;  

select * from dept  
order by deptno desc;  

逻辑运算符  
= !=或<> > >= < <=  
in  
between value1 and value2  
like  
%  
_  
in null  
not  
no in,is not null  

谓词in和not in  
有哪些职员和分析员  
select ename,job  
from emp  
where job in ('clerk','analyst');  

select ename,job  
from emp  
where job not in ('clerk','analyst');  

谓词between和not between  
哪些雇员的工资在2000和3000之间  
select ename,job,sal from emp  
where sal between 2000 and 3000;  

select ename,job,sal from emp  
where sal not between 2000 and 3000;  

谓词like,not like  
select ename,deptno from emp  
where ename like 'S%';  
(以字母S开头)  
select ename,deptno from emp  
where ename like '%K';  
(以K结尾)  
select ename,deptno from emp  
where ename like 'W___';  
(以W开头,后面仅有三个字母)  
select ename,job from emp  
where job not like 'sales%';  
(哪些雇员的工种名不以sales开头)  

谓词is null,is not null  
没有奖金的雇员(即commision为null)  
select ename,job from emp  
where comm is null;  

select ename,job from emp  
where comm is not null;  

多条件查询  
select ename,job  
from emp  
where deptno=20  
and job!='clerk';  

表达式  
+ - * /  

算术表达式  
选择奖金高于其工资的5%的雇员  
select ename,sal,comm,comm/sal from emp  
where comm>.05*sal  
  order by comm/sal desc;  

日期型数据的运算  
add two days to 6-Mar-87  
6-Mar-87 + 2 = 8-Mar-87  
add two hours to 6-Mar-87  
6-Mar-87 + 2/24 = 6-Mar-87 and 2hrs  
add 15 seconds to 6-Mar-87  
6-Mar-87 + 15/(24*60*60) = 6-Mar-87 and 15 secs  

列名的别名  
select ename employee from emp  
  where deptno=10;  
(别名:employee)  
select ename,sal,comm,comm/sal "C/S RATIO" from emp  
  where comm>.05*sal  
  order by comm/sal desc;  

SQL命令的编辑  
list or l 显示缓冲区的内容  
list 4 显示当前SQL命令的第4行,并把第4行作为当前行,在该行号后面有个*。  
change or c 用新的内容替换原来在一行中第一次出现内容  
SQL>c/(...)/('analyst')/  
input or i 增加一行或多行  
append or a 在一行后追加内容  
del  删除当前行 删除SQL缓冲区中的当前行  
run  显示并运行SQL缓冲区中的命令  
/  运行SQL缓冲区中的命令  
edit  把SQL缓冲区中的命令写到操作系统下的文本文件,  
并调用操作系统提供的编辑器执行修改。  

-------------  
3 数据操纵  
数据的插入  
insert into dept  
  values (10,'accounting','new york');  

insert into dept (dname,deptno)  
  values ('accounting',10);  

从其它表中选择插入数据  
insert into emp (empno,ename,deptno)  
select id,name,department  
from old_emp  
where department in(10,20,30,40);  

使用参数  
insert into dept  
  values(&deptno,&dname,&loc);  
执行时,SQL/PLUS对每个参数将有提示用户输入  

参数对应日期型或字符型数据时,可在参数上加引号,输入时就可不用引号  
insert into dept  
  values(&deptno,'&dname','&loc');  

插入空值(NULL)  
insert into dept  
  values(50,'education',null);  

插入日期型数据  
日期型数据缺省格式:DD-MON-YY  
insert into emp  
(empno,ename,hiredate)  
values(7963,'stone','07-APR-87');  

系统时间:SYSDATE  
insert into emp  
(empno,ename,hiredate)  
values(7600,'kohn',SYSDATE);  

数据更新  
update emp  
set job='manager'  
where ename='martin';  

update emp  
set job='market rep'  
where ename='salesman';  

update emp  
set deptno=40,job='market rep'  
where job='salesman';  

数据删除  
delete emp  
where empno=765;  

更新的提交  
commit  

自动提交方式  
set autocommit on  
如果状态设为开,则使用inesrt,update,delete会立即提交。  

更新取消  
rollback  

两次连续成功的commit之间的操作,称为一个事务  

---------------  
4  创建基表、视图  
创建基表  
create table dept  
(deptno number(2),  
dname char(14),  
loc char(13));  

数据字典会自动更新。  
一个基表最多254列。  

表名列名命名规则:  
限制  
第一个字符必须是字母,后面可任意(包括 $ # _ 但不能是逗号)。  
名字不得超过30个字符。  

唯一  
某一用户的基表名必须唯一,不能是ORACLE的保留字,同一基表的列名互不相同。  

使用双引号  
如果表名用双引号括起来,则可不满足上述规则;  
只有使用双引号,才能区别大、小写;  
命名时使用了双引号,在以后的操作也必须使用双引号。  

数据类型:  
char(n)    (不得超过240字符)  
number(n,d)  
date  
long    (最多65536字符)  
raw    (二进制原始数据)  

空值处理  
有时要求列值不能为空  
create table dept  
(deptno number(2) not null,  
dname char(14),  
loc char(13));  

在基表中增加一列  
alter table dept  
add (headcnt number(3));  

修改已有列属性  
alter table dept  
modify dname char(20);  
注:只有当某列所有值都为空时,才能减小其列值宽度。  
只有当某列所有值都为空时,才能改变其列值类型。  
只有当某列所有值都为不空时,才能定义该列为not null。  
例:  
alter table dept modify (loc char(12));  
alter table dept modify loc char(12);  
alter table dept modify (dname char(13),loc char(12));  

创建视图  
create view managers as  
select ename,job,sal  
from emp  
where job='manager';  
为视图列名取别名  
create view mydept  
(person,title,salary)  
as select ename,job,sal  
from emp  
where deptno=10;  

with check option选项  
使用with check option,保证当对视图插入或更新数据时,  
该数据必须满足视图定义中select命令所指定的条件。  
create view dept20 as  
select ename,job,sal,deptno  
from emp  
where deptno=20  
with check option;  
在做下述操作时,会发生错误  
update dept20  
set deptno=30  
where ename='ward';  

基表、视图的拷贝  
create table emp2  
as select * from emp;  

基表、视图的删除  
drop table 表名  
drop view 视图名  
0

回复列表 (共45个回复)

沙发

------------  
5 SQL*PLUS报表功能  
SQL*PLUS的一些基本格式命令  
column deptno heading department  

column ename heading name  

column sal heading salary  

column sal format $99,999.00  

ttitle sample report for|hitech corp  

btitle strictly confidential  

break on deptno  

compute sum of sal on deptno  

run  

表头和表尾  
ttitle sample report for|hitech corp  
btitle right strictly confidential  

“|”表示换行,结尾不必加分号  
选项有三种:left right center  

使用TTITLE,系统将自动地在每页的顶部显示日期和页号。  
TTITLET和BTITLE命令有效,直至重新设置表头或表尾,或退出SQL*PLUS。  

下面命令使标题语句失效  
TTITLE OFF  
BTITLE OFF  

列名  
column命令定义用于显示列名  
若名字为一个单词,不必加引号  
column ename heading employee  

column ename heading 'employee|name'  
(|为换行)  

取消栏定义  
column ename clear  

列的格式  
column ename format A15  

column sal format $9,999.99  

column comm like sal  

like子句,使得某一列的格式参照另一列格式,它拷贝列名及其格式  

控制记录显示分组顺序  
break on deptno  
(不显示重复值)  

select deptno,ename  
from emp  
order by deptno;  
(ORDER BY子句用于控制BREAK)  

显示为  
10 clark  
   niller  
20 smith  
   scott  
30 allen  
   blake  

每次只有一个BREAK命令起作用,但一次可以在多个列上使用BREAK命令  
break on 列名1 on 列名2  

记录分组  
break on deptno skip 2  
select deptno,ename  
from emp  
order by deptno;  

每个deptno之间空两行  
clear break(取消BREAK命令)  
break on page(每次从一新页开始)  
break on report(每次从一新报表开始)  
break on page on report(联合使用)  

分组计算  
break on deptno skip 2  
compute sum of sal on deptno  
计算每个部门的工资总和  
skip子句使部门之间的信息分隔开  

其他计算命令  
compute avg of sal on deptno(平均值)  
count 非空值的总数  
MAX 最大值  
MIN 最小值  
STD 标准偏差  
VAR 协方差  
NUMBER 行数  

使compute命令失效  
一旦定义了COMPUTE,则一直有效,直到  
关闭COMPUTE(clear compute)  

SQL/PLUS环境命令  
show 选项  
(显示当前参数设置情况)  

show all(显示全部参数)  

设置参数  
set 选项 值或开关  

set autocommit on  

SET命令包括  
set autocommit {off|on|immediate}  
(自动提交,OFF缺省)  

set echo {off|on}  
(命令文件执行,是否在终端上显示命令本身,OFF缺省)  

set feedback {off|on}  
(ON:查询结束时,给出结果,记录数的信息,缺省;  
OFF:无查询结果,记录数的信息)  

set heading {off|on}  
(ON:列的头标在报表上显示,缺省;OFF:不在报表上显示)  

set linesize {n}  
一行显示的最大字符数,缺省为80  

set pagesize {n}  
每页的行数,缺省是14  

set pause {off|on|text}  
(ON:当报表超过一页时,每显示一屏就暂停显示,等待用户打回车键,再接着显示;  
OFF:页与页不停顿,缺省;text:页与页停顿,并向用户提示信息)  

SET BUFFER buffer  
设置当头的命令缓冲区,通常情况下,SQL命令缓冲区已为当前缓冲区。  
由于SQL命令缓冲区只能存放一条SQL命令,  
所以可用其它缓冲区来存放SQL命令和SQL*PLUS命令。  

经常用到的设置可放在login.sql文件中。  

SET NULL  
set null 'no data'  

select ename,comm  
from emp  
where deptno=30;  
把部门30中无佣金雇员的佣金显示为“NO DATA”。  

set null是SQL*PLUS命令,用它来标识空值(NULL),可以设置为任意字符串。  

存盘命令SAVE  
save 文件名  

input  
1 select empno,ename,job  
2 from emp  
3 where job='analyst'  

save research  

目录中会增加一个research.sql文件。  

编辑命令EDIT  
edit  

EDIT编辑当前缓冲区中的内容。  

编辑一个文件  
edit research  

调入命令GET  
get research  
把磁盘上的文件内容调入缓冲区,并显示在屏幕上,文件名尾不必加.sql。  

START命令  
运行指定的文件  
start research  

输出命令SPOOL  
spool tryfile  
不仅可以使查询结果在屏幕上显示,还可以使结果存入文件  

停止向文件输出  
spool off  

把查询结果在打印机上输出,先把它们存入一个文件中,  
然后不必使用SPOOL OFF,而用:  
spool out  
SPOOL OUT关闭该文件并在系统缺省的打印机上输出  

制作报表举例  
edit tryfile  

set echo off  
set autocommit on  
set pagesize 25  
insert into emp (empno,ename,hiredate)  
  values(9999,'geiger',sysdate);  
insert into emp (empno,ename,deptno)  
  values(3333,'samson',20);  
spool new_emp  
select * from emp  
  where deptno=20  
or deptno is null  
/  
spool off  
set autocommit off  

用start命令执行这个文件

板凳

--------  
6 函数  
字符型函数  
initcap(ename);将ename中每个词的第一个字母改为大写。  
如:jack smith--Jack Smith  

length(ename);计算字符串的长度。  

substr(job,1,4);  

其它  
lower  
upper  
least 取出字符串列表中按字母排序排在最前面的一个串  
greatest 取出字符串列表中按字母排序排在最后的一个串  

日期函数  
add_month(hiredate,5) 在雇佣时间上加5个月  
month_between(sysdate,hiredate) 计算雇佣时间与系统时间之间相差的月数  
next_day(hiredate,'FRIDAY') 计算受雇日期之后的第一个星期五的日期  

例  
select ename,sal,next_day(sysdate,'FRIDAY') as_of  
from emp  
where deptno=20;  
(as_of是别名)  

如果不用to_char函数,日期在ORACLE中的缺省格式是'DD_MON_YY'  
to_char(date,date picture)  

select ename,to_char(hiredate,'Dy Mon dd,yyyy') hired  
from emp  
where deptno=10;  

to_date(字符串,格式)  

insert into emp(empno,ename,hiredate)  
values(7999,'asms',to_date('070387083000','MMDDYYHHMISS'));  

日期型数据的格式  
dd 12  
dy fri  
day friday  
ddspth twelfth  

mm 03  
mon mar  
month march  

yy 87  
yyyy 1987  

例  
Mar 12,1987    'Mon dd,yyyy'  
MAR 12,1987    'MON dd,yyyy'  
Thursday MARCH 12    'Day MONTH dd'  
Mar 12 11:00am    'Mon dd hh:miam'  
Thu,the twelfth    'Dy,"the"ddspth'  

算术函数  
least(v1,v2)  

select ename,empno,mgr,least(empno,mgr) lownum  
from emp  
where empno0  

trunc(sal,0)  
取sal的近似值(截断)  

空值函数  
nvl(v1,v2)  
v1为列名,如果v1不是空值,nvl返回其列值。  
v1为空值,返回v2的值。  

聚组函数  
select sum(comm)  
from emp;  
(返回一个汇总信息)  
不能把sum用在select语句里除非用group by  

字符型、日期型、数字型的聚组函数  
min max count可用于任何数据类型  

select min(ename)  
from emp;  

select min(hiredate)  
from emp;  

select min(sal)  
from emp;  

有多少人有工作?  
select count(job)  
from emp;  

有多少种不同的工种?  
select count(distinct job)  
from emp;  

count distinct 计算某一字段中不同的值的个数  

其它聚组函数(只用于数字型数据)  
avg 计算平均工资  
select avg(sal)  
from emp;  

stddev 计算工资的平均差  
select stddev(sal)  
from emp;  

sum 计算总工资  
select sum(sal)  
from emp;  

group by子句  
select deptno,sum(sal),avg(sal)  
from emp  
group by deptno;  

按多个条件分组  
每个部门的雇员数  
select deptno,count(*)  
from emp  
group by deptno;  

每个部门的每个工种的雇员数  
select deptno,job,count(*)  
from emp  
group by deptno,job;  

满足条件的分组  
(where是针对select的,having是针对group by的)  
哪些部门的工资总和超过了9000  
select deptno,sum(sal)  
from emp  
group by deptno  
having sum(sal)>9000;  

select小结  
除去职员,哪些部门的工资总和超过了8000  
select deptno,sum(sal)  
from emp  
where job!='clerk'  
group by deptno  
having sum(sal)>8000  
order by sum(sal);  

---------  
7 高级查询  
等值联接  
select empno,ename,job,emp.deptno,dname  
from emp,dept  
where emp.deptno=dept.deptno;  

外联接  
select ename,dept.deptno,loc  
from emp,dept  
where emp.deptno(+)=dept.deptno;  
如果在dept.deptno中有的数值,在emp.deptno中没有(如deptno=40),  
则作外联接时,结果中会产生一个空值  

自联接:同一基表的不同行要做联接,可使用自联接  
指出每个雇员的经理名字  
select worker.ename,manager.ename manager  
from emp worker,emp manager  
where worker.mgr=manager.empno;  

非等值联接  
哪些雇员的工资属于第三级别  
select ename,sal  
from emp,salgrade  
where grade=3  
and sal between losal and hisal;  
(基表salgrade:grade losal hisal)  

集合运算  
行的连接  
集合运算把2个或多个查询结果合并为一个  
union-set union  
Rows of first query plus of second query, less duplicate rows  

intersect-set intersection  
Rows both queries have in common  

minus-set difference  
rows unique to the first query  

介绍几个视图  
account view  
ename sal job  

sales view  
ename sal job  

research view  
ename sal job  

union运算  
返回一个查询结果中有但又不重要的行,它将基表或视图中的记录合并在一起  
所有部门中有哪些雇员工资超过2000  
对应列的数据类型必须相同  
select ename,sal  
from account  
where sal>2000  
union  
select ename,sal  
from research  
where sal>2000  
union  
select ename,sal  
from sales  
where sal>2000;  

intersect运算  
返回查询结果中相同的部分  
各个部门中有哪些相同的工种  
select job  
from account  
intersect  
select job  
from research  
intersect  
select job  
from sales;  

minus运算  
返回在第一个查询结果中与第二个查询结果不相同的那部分行记录。  
有哪些工种在财会部中有,而在销售部中没有?  
select job from account  
minus  
select job from sales;  

子查询  
slect ename,deptno  
from emp  
where deptno=  
(select deptno  
from emp  
where ename='smith');  

多级子查询  
select ename,job,sal  
from emp  
where job=  
(select job  
from emp  
where ename='clark')  
or sal>  
(select sal  
from emp  
where ename='clark');  

多个基表与子查询  
select ename,job,sal  
from emp,dept  
where loc='new york'  
and emp.deptno=dept.deptno  
and sal>  
(select sal  
from emp  
where ename='scott');  

子查询中使用聚组函数  
select ename,hiredate  
from emp  
where hiredate=  
(select min(hiredate)  
from emp);

3 楼

------------  
8 授权  
系统权限  
DBA 所有权限  
RESOURCE 注册,创建新的基表  
CONNECT,注册,查询  

只有DBA才有权创建新的用户  
grant connect to scott  
identified by tiger;  

DBA或用户自己可以改变用户口令  
grant connect to scott  
identified by leopard;  

基表权限1  
有两种方法获得对基表操作的权限  

创建自己的基表  
获得基表创建用户的许可  
grant select,insert  
on emp  
to scott;  

这些权限有  
select insert update delete alter index  

把所有权限授于他人  
grant all on emp to scott;  

同义词  
select *  
from scott.emp  

创建同义词  
为用户allen的EMP基表创建同义词employee  
create synonym employee  
for allen.emp  

基表权限2  
你可以使其他用户有这样的权力,即其他用户可以把你的基表权限授予他人  
grant all  
on emp  
to scott  
with grant option;  

收回权限  
系统权限 只有被DBA收回  

基表权限 随时都可以收回  

revoke insert  
on emp  
from scott;  

---------  
9 索引  
建立索引  
create index emp_ename  
on emp(ename);  

删除索引  
drop index emp_ename;  

关于索引  
只对较大的基表建立索引(至少50条记录)  
建立索引之前插入数据  
对一个基表可建立任意多个索引  
一般是在作为主键的列上建立索引  
建立索引之后,不影响SQL命令的执行  
建立索引之后,ORACLE自动维护和使用索引  

保证数据唯一性  
提高执行速度的同时,索引还可以保证每个记录中的每个列值是不重复的。  
create unique index emp_empno  
on emp(empno);  

--------  
练习和答案  

有没有工资比奖金多的雇员?如果有,按工资的降序排列。  
如果有两个以上的雇员工资相同,按他们的名字排序。  
select ename employee,sal salary,comm commision  
from emp  
where sal>comm  
order by sal desc,ename;  

列出有关雇员姓名、奖金占收百分比的信息。  
要求显示时列名意义明确,按雇员姓名排序,不包括奖金未知的雇员。  
select ename employee,(comm/(comm+sal))*100 incentive  
from emp  
where comm is not null  
order by ename;  

在chicago(部门30)工作的所有雇员的工资上涨10%。  
update emp  
set sal=1.1*sal  
where deptno=30;  

update emp  
set sal=1.1*sal  
where deptno=(select deptno  
from dept  
where loc='chicago');  

为hitech公司新建一个部门,编号为50,其它信息均不可知。  
insert into dept(dname,deptno)  
values('faclities',50);  

创建视图,三个列名,其中不包括职员信息  
create view employee("employee name",  
"employee number",  
"employee job")  
as select ename,empno,job  
from emp  
where job!='clerk';  

制作工资报表,包括雇员姓名、受雇时间(按星期计算),工资和部门编号,  
一页显示一个部门的信息,每页尾,显示该页的工资之和以及受雇时间之和,  
报表结尾处,显示所有雇员的工资总和以及受雇时间总和,  
工资按美元计算,受雇时间按星期计算,每页的上方应有标题。  
ttitle 'service'  
break on deptno on page on report  
compute sum of sal on deptno  
compute sum of sal on report  
compute sum of service_length on deptno  
compute sum of service_length on report  
column sal format $99,999.00  
column service_length format 9999  
select deptno,ename employee,(sysdate-hiredate)/7 service_length,sal  
from emp  
order by deptno;  

制作报表,包括雇员姓名、总收入和受佣日期,  
且:姓名的第一个字母必须大写,雇佣日期格式为MM/DD/YYYY,  
总收入包括没有奖金的雇员的总收入,姓名按字母顺序排列。  
col "hire date"format A12  
col "employee" format A10  
col "compensation" format $99,999.00  
select initcap(ename) "employee",  
(sal+nvl(comm,0)) "compensation",  
to_char(hiredate,'MM/DD/YYYY') "hire date"  
from emp  
order by ename;  
列出有超过7个周边国家的国家名字和面积。  
select nation,area  
from nation  
where code in  
(select nation_code  
from border  
group by nation_code  
having count(*)>7);  

列出所有面积大于等于日本的岛国的国名和人口。  
select nation,population  
from nation,border  
where code=nation_code(+)  
and nation_code is null  
and area>=  
(select area  
from nation  
where upper(nation)='JAPAN');  

列出所有边界在其它国家中的国名,并且显示其边界国家名字。  
break on nation  
select nation1.nation,  
nation2.nation borderin_country  
from nation nation1,border,nation nation2  
where nation1.code=border.nation_code  
and border.border_code=nation2.code  
order by nation1.nation;  

-----------  
-----------  
PL/SQL  

2 PL/SQL的块结构和数据类型  

块结构的特点  
嵌套  
begin  
......  
  begin  
  ......  
  exception  
  ......  
  end;  
exception  
......  
end;  

4 楼


标识符:  
不能超过30个字符  
第一个字符必须为字母  
其余字符可以是字母,数字,$,_,或#  
不区分大小写形式  
如果用双引号括起来,则字符顺序可以任意排列,并区分大小写形式  
无SQL保留字  

数据类型  
数字型:  
整数,实数,以及指数  

字符串:  
用单引号括起来  
若在字符串表示单引号,则使用两个单引号  
字符串长度为零(两个单引号之间没有字符),则表示NULL  

字符:  
长度为1的字符串  

数据定义  
语法  
标识符[常数] 数据类型[NOT NULL][:=PL/SQL表达式];  
':='表示给变量赋值  

数据类型包括  
数字型 number(7,2)  
字符型 char(120)  
日期型 date  
布尔型 boolean(取值为true,false或null,不存贮在数据库中)  

日期型  
anniversary date:='05-JUL-95';  
project_completion date;  

布尔型  
over_budget boolean not null:=false;  
available boolean;  
(初始值为NULL)  

%type类型匹配  
books_printed number(6);  
books_sold book_printed%type;  
manager_name emp.ename%type;  

变量赋值  
变量名:=PL/SQL表达式  
numvar:=5;  
boolvar:=true;  
datevar:='11-JUN-87';  

字符型、数字型表达式中的空值  
null+<数字>=null(空值加数字仍是空值)  
null><数字>=null(空值与数字进行比较,结果仍是空值)  
null||'字符串'='字符串'(null即'')  
(空值与字符串进行连接运算,结果为原字符串)  

变量作用范围  
标识符在宣言它的块中有效  
标识符如果不在子块中重新定义,则在PL/SQL块的所有子块中同样有效  
重新定义后的标识符,作用范围仅在本子块中有效  

例  
declare  
  e_mess char(80);  
begin  
  /*子块1*/  
  declare  
    v1 number(4);  
  begin  
    select empno into v1 from emp  
    where job='president';  
  exception  
    when too_many_rows then  
      insert into job_errors  
      values('more than one president');  
  end;  
  /*子块2*/  
  declare  
    v1 number(4);  
  begin  
    select empno into v1 from emp  
    where job='manager';  
  exception  
    when too_many_rows then  
      insert into job_errors  
      values('more than one manager');  
  end;  
exception  
  when others then  
    e_mess:=substr(sqlerrm,1,80);  
    insert into general errors values(e_mess);  
end;  ---------  
3 SQL和PL/SQL  

插入  
declare  
  my_sal number(7,2):=3040.55;  
  my_ename char(25):='wanda';  
  my_hiredate date:='08-SEP-88';  
begin  
  insert into emp  
  (empno,enmae,job,hiredate,sal,deptno)  
  values(2741,my_ename,'cab driver',my_hiredate,my_sal,20);  
end;  

删除  
declare  
  bad_child_type char(20):='naughty';  
begin  
  delete from santas_gift_list where  
  kid_rating=bad_child_type;  
end;  

事务处理  
commit[WORK];  
rollback[WORK];  
(关键字WORK可选,但对命令执行无任何影响)  
savepoint 标记名;(保存当前点)  
在事务中标记当前点  
rollback [WORK] to [SAVEPOINT] 标记名;(回退到当前保存点)  
取消savepoint命令之后的所有对数据库的修改  
关键字WORK和SAVEPOINT为可选项,对命令执行无任何影响  

函数  
PL/SQL块中可以使用SQL命令的所有函数  
insert into phonebook(lastname) value(upper(my_lastname));  
select avg(sal) into avg_sal from emp;  

对于非SQL命令,可使用大多数个体函数  
不能使用聚组函数和参数个数不定的函数,如  
x:=sqrt(y);  
lastname:=upper(lastname);  
age_diff:=months_between(birthday1,birthday2)/12;  

赋值时的数据类型转换  
4种赋值形式:  
变量名:=表达式  
insert into 基表名 values(表达式1,表达式2,...);  
update 基表名 set 列名=表达式;  
select 列名 into 变量名 from ...;  

数据类型间能进行转换的有:  
char转成number  
number转成char  
char转成date  
date转成char  

例  
char_var:=nm_var;  
数字型转换成字符型  
date_var:='25-DEC-88';  
字符型转换成日期型  
insert into 表名(num_col) values('604badnumber');  
错误,无法成功地转换数据类型  
---------  
4 条件控制  
例  
declare  
  num_jobs number(4);  
begin  
  select count(*) into num_jobs from auditions  
  where actorid=&&actor_id and called_back='yes';  
  if num_jobs>100 then  
    update actor set actor_rating='word class'  
    where actorid=&&actor_id;  
  elsif num_job=75 then  
    update actor set actor_rating='daytime soaps'  
    where actorid=&&actor_id;  
  else  
    update actor set actor_rating='waiter'  
    where actorid=&&actor_id;  
  end if;  
  end if;  
  commit;  
end;  

--------  
5 循环  
语法  
loop  
......  
end loop;  
exit;(退出循环)  
exit [when];(退出循环,当满足WHEN时)  
例1  
declare  
  ctr number(3):=0;  
begin  
  loop  
    insert into table1 values('tastes great');  
    insert into table2 values('less filling');  
    ctr:=ctr+1;  
  exit when ctr=100;  
  end loop;  
end;  
(注:如果ctr取为NULL,循环无法结束)  

5 楼

例2  
FOR语法  
for 变量<范围> loop  
......  
end loop;  

declare  
  my_index char(20):='fettucini alfredo';  
  bowl char(20);  
begin  
  for my_index in reverse 21..30 loop  
  insert into temp(coll) values(my_index);  
  /*循环次数从30到21*/  
  end loop;  
  bowl:=my_index;  
end;  
跟在in reverse后面的数字必须是从小到大的顺序,必须是整数,不能是变量或表达式  

----------  
6 游标  
显式游标  

打开游标  
open <游标名>  
例  
open color_cur;  

游标属性  
%notfound  
%found  
%rowcount  
%isopen  
例  
fetch my_cur into my_var;  
while my_cur %found loop  
  (处理数据)  
  fetch my_cur into my_var;  
  exit when my_cur %rowcount=10;  
end loop;  

%notfound属性  
取值情况如下:  
fetch操作没有返回记录,则取值为true  
fetch操作返回一条记录,则取值为false  
对游标无fetch操作时,取值为null  
<游标名> %notfound  
例  
if color_cur %notfound then...  
注:如果没有fetch操作,则<游标名> %notfound将导致出错,  
因为%notfound的初始值为NULL。  

关闭游标  
close <游标名>  
例  
close color_cur;  

游标的FOR循环  
语法  
for <记录名> in <游标名> loop  
<一组命令>  
end loop;  
其中:  
索引是建立在每条记录的值之上的  
记录名不必声明  
每个值对应的是记录名,列名  
初始化游标指打开游标  
活动集合中的记录自动完成FETCH操作  
退出循环,关闭游标  

隐式游标  
隐式游标是指SQL命令中用到的,没有明确定义的游标  
insert,update,delete,select语句中不必明确定义游标