OraclePL/SQL编程手册

<script type="text/javascript"><!-- google_ad_client = "pub-2947489232296736"; /* 728x15, 创建于 08-4-23MSDN */ google_ad_slot = "3624277373"; google_ad_width = 728; google_ad_height = 15; //--> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
<script type="text/javascript"><!-- google_ad_client = "pub-2947489232296736"; /* 160x600, 创建于 08-4-23MSDN */ google_ad_slot = "4367022601"; google_ad_width = 160; google_ad_height = 600; //--> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

  一、SQLPLUS

  1引言

  SQL命令

  以下17个是作为语句开头的关键字:

  alterdroprevoke

  auditgrantrollback*

  commit*insertselect

  commentlockupdate

  createnoauditvalidate

  deleterename

  这些命令必须以“;”结尾

  带*命令句尾不必加分号,并且不存入SQL缓存区。

  SQL中没有的SQL*PLUS命令

  这些命令不存入SQL缓存区

  @definepause

  #delquit

  $describeremark

  /disconnectrun

  acceptdocumentsave

  appendeditset

  breakexitshow

  btitlegetspool

  changehelpSQLplus

  clearhoststart

  columninputtiming

  computelistttitle

  connectnewpageundefine

  copy

  ---------

  2数据库查询

  数据字典

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

  DTAB构成数据字典的所有表

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

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

  select*fromtab;

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

  describedept

  select*

  fromemp;

  selectempno,ename,job

  fromemp;

  select*fromdept

  orderbydeptnodesc;

  逻辑运算符

  =!=或<>>>=<<=

  in

  betweenvalue1andvalue2

  like

  %

  _

  innull

  not

  noin,isnotnull

  谓词in和notin

  有哪些职员和分析员

  selectename,job

  fromemp

  wherejobin('clerk','analyst');

  selectename,job

  fromemp

  wherejobnotin('clerk','analyst');

  谓词between和notbetween

  哪些雇员的工资在2000和3000之间

  selectename,job,salfromemp

  wheresalbetween2000and3000;

  selectename,job,salfromemp

  wheresalnotbetween2000and3000;

  谓词like,notlike

  selectename,deptnofromemp

  whereenamelike'S%';

  (以字母S开头)

  selectename,deptnofromemp

  whereenamelike'%K';

  (以K结尾)

  selectename,deptnofromemp

  whereenamelike'W___';

  (以W开头,后面仅有三个字母)

  selectename,jobfromemp

  wherejobnotlike'sales%';

  (哪些雇员的工种名不以sales开头)

  谓词isnull,isnotnull

  没有奖金的雇员(即commision为null)

  selectename,jobfromemp

  wherecommisnull;

  selectename,jobfromemp

  wherecommisnotnull;

  多条件查询

  selectename,job

  fromemp

  wheredeptno=20

  andjob!='clerk';

  表达式

  +-*/

  算术表达式

  选择奖金高于其工资的5%的雇员

  selectename,sal,comm,comm/salfromemp

  wherecomm>.05*sal

  orderbycomm/saldesc;

  日期型数据的运算

  addtwodaysto6-Mar-87

  6-Mar-87+2=8-Mar-87

  addtwohoursto6-Mar-87

  6-Mar-87+2/24=6-Mar-87and2hrs

  add15secondsto6-Mar-87

  6-Mar-87+15/(24*60*60)=6-Mar-87and15secs

  列名的别名

  selectenameemployeefromemp

  wheredeptno=10;

  (别名:employee)

  selectename,sal,comm,comm/sal"C/SRATIO"fromemp

  wherecomm>.05*sal

  orderbycomm/saldesc;

  SQL命令的编辑

  listorl显示缓冲区的内容

  list4显示当前SQL命令的第4行,并把第4行作为当前行,在该行号后面有个*。

  changeorc用新的内容替换原来在一行中第一次出现内容

  SQL>c/(...)/('analyst')/

  inputori增加一行或多行

  appendora在一行后追加内容

  del删除当前行删除SQL缓冲区中的当前行

  run显示并运行SQL缓冲区中的命令

  /运行SQL缓冲区中的命令

  edit把SQL缓冲区中的命令写到操作系统下的文本文件,

  并调用操作系统提供的编辑器执行修改。

  -------------

  3数据操纵

  数据的插入

  insertintodept

  values(10,'accounting','newyork');

  insertintodept(dname,deptno)

  values('accounting',10);

  从其它表中选择插入数据

  insertintoemp(empno,ename,deptno)

  selectid,name,department

  fromold_emp

  wheredepartmentin(10,20,30,40);

  使用参数

  insertintodept

  values(&deptno,&dname,&loc);

  执行时,SQL/PLUS对每个参数将有提示用户输入

  参数对应日期型或字符型数据时,可在参数上加引号,输入时就可不用引号

  insertintodept

  values(&deptno,'&dname','&loc');

  插入空值(NULL)

  insertintodept

  values(50,'education',null);

  插入日期型数据

  日期型数据缺省格式:DD-MON-YY

  insertintoemp

  (empno,ename,hiredate)

  values(7963,'stone','07-APR-87');

  系统时间:SYSDATE

  insertintoemp

  (empno,ename,hiredate)

  values(7600,'kohn',SYSDATE);

  数据更新

  updateemp

  setjob='manager'

  whereename='martin';

  updateemp

  setjob='marketrep'

  whereename='salesman';

  updateemp

  setdeptno=40,job='marketrep'

  wherejob='salesman';

  数据删除

  deleteemp

  whereempno=765;

  更新的提交

  commit

  自动提交方式

  setautocommiton

  如果状态设为开,则使用inesrt,update,delete会立即提交。

  更新取消

  rollback

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

  ---------------

  4创建基表、视图

  创建基表

  createtabledept

  (deptnonumber(2),

  dnamechar(14),

  locchar(13));

  数据字典会自动更新。

  一个基表最多254列。

  表名列名命名规则:

  限制

  第一个字符必须是字母,后面可任意(包括$#_但不能是逗号)。

  名字不得超过30个字符。

  唯一

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

  使用双引号

  如果表名用双引号括起来,则可不满足上述规则;

  只有使用双引号,才能区别大、小写;

  命名时使用了双引号,在以后的操作也必须使用双引号。

  数据类型:

  char(n)(不得超过240字符)

  number(n,d)

  date

  long(最多65536字符)

  raw(二进制原始数据)

  空值处理

  有时要求列值不能为空

  createtabledept

  (deptnonumber(2)notnull,

  dnamechar(14),

  locchar(13));

  在基表中增加一列

  altertabledept

  add(headcntnumber(3));

  修改已有列属性

  altertabledept

  modifydnamechar(20);

  注:只有当某列所有值都为空时,才能减小其列值宽度。

  只有当某列所有值都为空时,才能改变其列值类型。

  只有当某列所有值都为不空时,才能定义该列为notnull。

  例:

  altertabledeptmodify(locchar(12));

  altertabledeptmodifylocchar(12);

  altertabledeptmodify(dnamechar(13),locchar(12));

  创建视图

  createviewmanagersas

  selectename,job,sal

  fromemp

  wherejob='manager';

  为视图列名取别名

  createviewmydept

  (person,title,salary)

  asselectename,job,sal

  fromemp

  wheredeptno=10;

  withcheckoption选项

  使用withcheckoption,保证当对视图插入或更新数据时,

  该数据必须满足视图定义中select命令所指定的条件。

  createviewdept20as

  selectename,job,sal,deptno

  fromemp

  wheredeptno=20

  withcheckoption;

  在做下述操作时,会发生错误

  updatedept20

  setdeptno=30

  whereename='ward';

  基表、视图的拷贝

  createtableemp2

  asselect*fromemp;

  基表、视图的删除

  droptable表名

  dropview视图名

  ------------

  5SQL*PLUS报表功能

  SQL*PLUS的一些基本格式命令

  columndeptnoheadingdepartment

  columnenameheadingname

  columnsalheadingsalary

  columnsalformat$99,999.00

  ttitlesamplereportfor|hitechcorp

  btitlestrictlyconfidential

  breakondeptno

  computesumofsalondeptno

  run

  表头和表尾

  ttitlesamplereportfor|hitechcorp

  btitlerightstrictlyconfidential

  “|”表示换行,结尾不必加分号

  选项有三种:leftrightcenter

  使用TTITLE,系统将自动地在每页的顶部显示日期和页号。

  TTITLET和BTITLE命令有效,直至重新设置表头或表尾,或退出SQL*PLUS。

  下面命令使标题语句失效

  TTITLEOFF

  BTITLEOFF

  列名

  column命令定义用于显示列名

  若名字为一个单词,不必加引号

  columnenameheadingemployee

  columnenameheading'employee|name'

  (|为换行)

  取消栏定义

  columnenameclear

  列的格式

  columnenameformatA15

  columnsalformat$9,999.99

  columncommlikesal

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

  控制记录显示分组顺序

  breakondeptno

  (不显示重复值)

  selectdeptno,ename

  fromemp

  orderbydeptno;

  (ORDERBY子句用于控制BREAK)

  显示为

  10clark

  niller

  20smith

  scott

  30allen

  blake

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

  breakon列名1on列名2

  记录分组

  breakondeptnoskip2

  selectdeptno,ename

  fromemp

  orderbydeptno;

  每个deptno之间空两行

  clearbreak(取消BREAK命令)

  breakonpage(每次从一新页开始)

  breakonreport(每次从一新报表开始)

  breakonpageonreport(联合使用)

  分组计算

  breakondeptnoskip2

  computesumofsalondeptno

  计算每个部门的工资总和

  skip子句使部门之间的信息分隔开

  其他计算命令

  computeavgofsalondeptno(平均值)

  count非空值的总数

  MAX最大值

  MIN最小值

  STD标准偏差

  VAR协方差

  NUMBER行数

  使compute命令失效

  一旦定义了COMPUTE,则一直有效,直到

  关闭COMPUTE(clearcompute)

  SQL/PLUS环境命令

  show选项

  (显示当前参数设置情况)

  showall(显示全部参数)

  设置参数

  set选项值或开关

  setautocommiton

  SET命令包括

  setautocommit{off|on|immediate}

  (自动提交,OFF缺省)

  setecho{off|on}

  (命令文件执行,是否在终端上显示命令本身,OFF缺省)

  setfeedback{off|on}

  (ON:查询结束时,给出结果,记录数的信息,缺省;

  OFF:无查询结果,记录数的信息)

  setheading{off|on}

  (ON:列的头标在报表上显示,缺省;OFF:不在报表上显示)

  setlinesize{n}

  一行显示的最大字符数,缺省为80

  setpagesize{n}

  每页的行数,缺省是14

  setpause{off|on|text}

  (ON:当报表超过一页时,每显示一屏就暂停显示,等待用户打回车键,再接着显示;

  OFF:页与页不停顿,缺省;text:页与页停顿,并向用户提示信息)

  SETBUFFERbuffer

  设置当头的命令缓冲区,通常情况下,SQL命令缓冲区已为当前缓冲区。

  由于SQL命令缓冲区只能存放一条SQL命令,

  所以可用其它缓冲区来存放SQL命令和SQL*PLUS命令。

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

  SETNULL

  setnull'nodata'

  selectename,comm

  fromemp

  wheredeptno=30;

  把部门30中无佣金雇员的佣金显示为“NODATA”。

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

  存盘命令SAVE

  save文件名

  input

  1selectempno,ename,job

  2fromemp

  3wherejob='analyst'

  saveresearch

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

  编辑命令EDIT

  edit

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

  编辑一个文件

  editresearch

  调入命令GET

  getresearch

  把磁盘上的文件内容调入缓冲区,并显示在屏幕上,文件名尾不必加.SQL

  START命令

  运行指定的文件

  startresearch

  输出命令SPOOL

  spooltryfile

  不仅可以使查询结果在屏幕上显示,还可以使结果存入文件

  停止向文件输出

  spooloff

  把查询结果在打印机上输出,先把它们存入一个文件中,

  然后不必使用SPOOLOFF,而用:

  spoolout

  SPOOLOUT关闭该文件并在系统缺省的打印机上输出

  制作报表举例

  edittryfile

  setechooff

  setautocommiton

  setpagesize25

  insertintoemp(empno,ename,hiredate)

  values(9999,'geiger',sysdate);

  insertintoemp(empno,ename,deptno)

  values(3333,'samson',20);

  spoolnew_emp

  select*fromemp

  wheredeptno=20

  ordeptnoisnull

  /

  spooloff

  setautocommitoff

  用start命令执行这个文件

  --------

  6函数

  字符型函数

  initcap(ename);将ename中每个词的第一个字母改为大写。

  如:jacksmith--JackSmith

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

  substr(job,1,4);

  其它

  lower

  upper

  least取出字符串列表中按字母排序排在最前面的一个串

  greatest取出字符串列表中按字母排序排在最后的一个串

  日期函数

  add_month(hiredate,5)在雇佣时间上加5个月

  month_between(sysdate,hiredate)计算雇佣时间与系统时间之间相差的月数

  next_day(hiredate,'FRIDAY')计算受雇日期之后的第一个星期五的日期

  例

  selectename,sal,next_day(sysdate,'FRIDAY')as_of

  fromemp

  wheredeptno=20;

  (as_of是别名)

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

  to_char(date,datepicture)

  selectename,to_char(hiredate,'DyMondd,yyyy')hired

  fromemp

  wheredeptno=10;

  to_date(字符串,格式)

  insertintoemp(empno,ename,hiredate)

  values(7999,'asms',to_date('070387083000','MMDDYYHHMISS'));

  日期型数据的格式

  dd12

  dyfri

  dayfriday

  ddspthtwelfth

  mm03

  monmar

  monthmarch

  yy87

  yyyy1987

  例

  Mar12,1987'Mondd,yyyy'

  MAR12,1987'MONdd,yyyy'

  ThursdayMARCH12'DayMONTHdd'

  Mar1211:00am'Monddhh:miam'

  Thu,thetwelfth'Dy,"the"ddspth'

  算术函数

  least(v1,v2)

  selectename,empno,mgr,least(empno,mgr)lownum

  fromemp

  whereempno0

  trunc(sal,0)

  取sal的近似值(截断)

  空值函数

  nvl(v1,v2)

  v1为列名,如果v1不是空值,nvl返回其列值。

  v1为空值,返回v2的值。

  聚组函数

  selectsum(comm)

  fromemp;

  (返回一个汇总信息)

  不能把sum用在select语句里除非用groupby

  字符型、日期型、数字型的聚组函数

  minmaxcount可用于任何数据类型

  selectmin(ename)

  fromemp;

  selectmin(hiredate)

  fromemp;

  selectmin(sal)

  fromemp;

  有多少人有工作?

  selectcount(job)

  fromemp;

  有多少种不同的工种?

  selectcount(distinctjob)

  fromemp;

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

  其它聚组函数(只用于数字型数据)

  avg计算平均工资

  selectavg(sal)

  fromemp;

  stddev计算工资的平均差

  selectstddev(sal)

  fromemp;

  sum计算总工资

  selectsum(sal)

  fromemp;

  groupby子句

  selectdeptno,sum(sal),avg(sal)

  fromemp

  groupbydeptno;

  按多个条件分组

  每个部门的雇员数

  selectdeptno,count(*)

  fromemp

  groupbydeptno;

  每个部门的每个工种的雇员数

  selectdeptno,job,count(*)

  fromemp

  groupbydeptno,job;

  满足条件的分组

  (where是针对select的,having是针对groupby的)

  哪些部门的工资总和超过了9000

  selectdeptno,sum(sal)

  fromemp

  groupbydeptno

  havingsum(sal)>9000;

  select小结

  除去职员,哪些部门的工资总和超过了8000

  selectdeptno,sum(sal)

  fromemp

  wherejob!='clerk'

  groupbydeptno

  havingsum(sal)>8000

  orderbysum(sal);

  ---------

  7高级查询

  等值联接

  selectempno,ename,job,emp.deptno,dname

  fromemp,dept

  whereemp.deptno=dept.deptno;

  外联接

  selectename,dept.deptno,loc

  fromemp,dept

  whereemp.deptno(+)=dept.deptno;

  如果在dept.deptno中有的数值,在emp.deptno中没有(如deptno=40),

  则作外联接时,结果中会产生一个空值

  自联接:同一基表的不同行要做联接,可使用自联接

  指出每个雇员的经理名字

  selectworker.ename,manager.enamemanager

  fromempworker,empmanager

  whereworker.mgr=manager.empno;

  非等值联接

  哪些雇员的工资属于第三级别

  selectename,sal

  fromemp,salgrade

  wheregrade=3

  andsalbetweenlosalandhisal;

  (基表salgrade:gradelosalhisal)

  集合运算

  行的连接

  集合运算把2个或多个查询结果合并为一个

  union-setunion

  Rowsoffirstqueryplusofsecondquery,lessduplicaterows

  intersect-setintersection

  Rowsbothquerieshaveincommon

  minus-setdifference

  rowsuniquetothefirstquery

  介绍几个视图

  accountview

  enamesaljob

  salesview

  enamesaljob

  researchview

  enamesaljob

  union运算

  返回一个查询结果中有但又不重要的行,它将基表或视图中的记录合并在一起

  所有部门中有哪些雇员工资超过2000

  对应列的数据类型必须相同

  selectename,sal

  fromaccount

  wheresal>2000

  union

  selectename,sal

  fromresearch

  wheresal>2000

  union

  selectename,sal

  fromsales

  wheresal>2000;

  intersect运算

  返回查询结果中相同的部分

  各个部门中有哪些相同的工种

  selectjob

  fromaccount

  intersect

  selectjob

  fromresearch

  intersect

  selectjob

  fromsales;

  minus运算

  返回在第一个查询结果中与第二个查询结果不相同的那部分行记录。

  有哪些工种在财会部中有,而在销售部中没有?

  selectjobfromaccount

  minus

  selectjobfromsales;

  子查询

  slectename,deptno

  fromemp

  wheredeptno=

  (selectdeptno

  fromemp

  whereename='smith');

  多级子查询

  selectename,job,sal

  fromemp

  wherejob=

  (selectjob

  fromemp

  whereename='clark')

  orsal>

  (selectsal

  fromemp

  whereename='clark');

  多个基表与子查询

  selectename,job,sal

  fromemp,dept

  whereloc='newyork'

  andemp.deptno=dept.deptno

  andsal>

  (selectsal

  fromemp

  whereename='scott');

  子查询中使用聚组函数

  selectename,hiredate

  fromemp

  wherehiredate=

  (selectmin(hiredate)

  fromemp);

  ------------

  8授权

  系统权限

  DBA所有权限

  RESOURCE注册,创建新的基表

  CONNECT,注册,查询

  只有DBA才有权创建新的用户

  grantconnecttoscott

  identifiedbytiger;

  DBA或用户自己可以改变用户口令

  grantconnecttoscott

  identifiedbyleopard;

  基表权限1

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

  创建自己的基表

  获得基表创建用户的许可

  grantselect,insert

  onemp

  toscott;

  这些权限有

  selectinsertupdatedeletealterindex

  把所有权限授于他人

  grantallonemptoscott;

  同义词

  select*

  fromscott.emp

  创建同义词

  为用户allen的EMP基表创建同义词employee

  createsynonymemployee

  forallen.emp

  基表权限2

  你可以使其他用户有这样的权力,即其他用户可以把你的基表权限授予他人

  grantall

  onemp

  toscott

  withgrantoption;

  收回权限

  系统权限只有被DBA收回

  基表权限随时都可以收回

  revokeinsert

  onemp

  fromscott;

  ---------

  9索引

  建立索引

  createindexemp_ename

  onemp(ename);

  删除索引

  dropindexemp_ename;

  关于索引

  只对较大的基表建立索引(至少50条记录)

  建立索引之前插入数据

  对一个基表可建立任意多个索引

  一般是在作为主键的列上建立索引

  建立索引之后,不影响SQL命令的执行

  建立索引之后,ORACLE自动维护和使用索引

  保证数据唯一性

  提高执行速度的同时,索引还可以保证每个记录中的每个列值是不重复的。

  createuniqueindexemp_empno

  onemp(empno);

  --------

  练习和答案

  有没有工资比奖金多的雇员?如果有,按工资的降序排列。

  如果有两个以上的雇员工资相同,按他们的名字排序。

  selectenameemployee,salsalary,commcommision

  fromemp

  wheresal>comm

  orderbysaldesc,ename;

  列出有关雇员姓名、奖金占收百分比的信息。

  要求显示时列名意义明确,按雇员姓名排序,不包括奖金未知的雇员。

  selectenameemployee,(comm/(comm+sal))*100incentive

  fromemp

  wherecommisnotnull

  orderbyename;

  在chicago(部门30)工作的所有雇员的工资上涨10%。

  updateemp

  setsal=1.1*sal

  wheredeptno=30;

  updateemp

  setsal=1.1*sal

  wheredeptno=(selectdeptno

  fromdept

  whereloc='chicago');

  为hitech公司新建一个部门,编号为50,其它信息均不可知。

  insertintodept(dname,deptno)

  values('faclities',50);

  创建视图,三个列名,其中不包括职员信息

  createviewemployee("employeename",

  "employeenumber",

  "employeejob")

  asselectename,empno,job

  fromemp

  wherejob!='clerk';

  制作工资报表,包括雇员姓名、受雇时间(按星期计算),工资和部门编号,

  一页显示一个部门的信息,每页尾,显示该页的工资之和以及受雇时间之和,

  报表结尾处,显示所有雇员的工资总和以及受雇时间总和,

  工资按美元计算,受雇时间按星期计算,每页的上方应有标题。

  ttitle'service'

  breakondeptnoonpageonreport

  computesumofsalondeptno

  computesumofsalonreport

  computesumofservice_lengthondeptno

  computesumofservice_lengthonreport

  columnsalformat$99,999.00

  columnservice_lengthformat9999

  selectdeptno,enameemployee,(sysdate-hiredate)/7service_length,sal

  fromemp

  orderbydeptno;

  制作报表,包括雇员姓名、总收入和受佣日期,

  且:姓名的第一个字母必须大写,雇佣日期格式为MM/DD/YYYY,

  总收入包括没有奖金的雇员的总收入,姓名按字母顺序排列。

  col"hiredate"formatA12

  col"employee"formatA10

  col"compensation"format$99,999.00

  selectinitcap(ename)"employee",

  (sal+nvl(comm,0))"compensation",

  to_char(hiredate,'MM/DD/YYYY')"hiredate"

  fromemp

  orderbyename;

  列出有超过7个周边国家的国家名字和面积。

  selectnation,area

  fromnation

  wherecodein

  (selectnation_code

  fromborder

  groupbynation_code

  havingcount(*)>7);

  列出所有面积大于等于日本的岛国的国名和人口。

  selectnation,population

  fromnation,border

  wherecode=nation_code(+)

  andnation_codeisnull

  andarea>=

  (selectarea

  fromnation

  whereupper(nation)='JAPAN');

  列出所有边界在其它国家中的国名,并且显示其边界国家名字。

  breakonnation

  selectnation1.nation,

  nation2.nationborderin_country

  fromnationnation1,border,nationnation2

  wherenation1.code=border.nation_code

  andborder.border_code=nation2.code

  orderbynation1.nation;

  -----------

  -----------

  PL/SQL

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

  块结构的特点

  嵌套

  begin

  ......

  begin

  ......

  exception

  ......

  end;

  exception

  ......

  end;

  标识符:

  不能超过30个字符

  第一个字符必须为字母

  其余字符可以是字母,数字,$,_,或#

  不区分大小写形式

  如果用双引号括起来,则字符顺序可以任意排列,并区分大小写形式

  无SQL保留字

  数据类型

  数字型:

  整数,实数,以及指数

  字符串:

  用单引号括起来

  若在字符串表示单引号,则使用两个单引号

  字符串长度为零(两个单引号之间没有字符),则表示NULL

  字符:

  长度为1的字符串

  数据定义

  语法

  标识符[常数]数据类型[NOTNULL][:=PL/SQL表达式];

  ':='表示给变量赋值

  数据类型包括

  数字型number(7,2)

  字符型char(120)

  日期型date

  布尔型boolean(取值为true,false或null,不存贮在数据库中)

  日期型

  anniversarydate:='05-JUL-95';

  project_completiondate;

  布尔型

  over_budgetbooleannotnull:=false;

  availableboolean;

  (初始值为NULL)

  %type类型匹配

  books_printednumber(6);

  books_soldbook_printed%type;

  manager_nameemp.ename%type;

  变量赋值

  变量名:=PL/SQL表达式

  numvar:=5;

  boolvar:=true;

  datevar:='11-JUN-87';

  字符型、数字型表达式中的空值

  null+<数字>=null(空值加数字仍是空值)

  null><数字>=null(空值与数字进行比较,结果仍是空值)

  null||'字符串'='字符串'(null即'')

  (空值与字符串进行连接运算,结果为原字符串)

  变量作用范围

  标识符在宣言它的块中有效

  标识符如果不在子块中重新定义,则在PL/SQL块的所有子块中同样有效

  重新定义后的标识符,作用范围仅在本子块中有效

  例

  declare

  e_messchar(80);

  begin

  /*子块1*/

  declare

  v1number(4);

  begin

  selectempnointov1fromemp

  wherejob='president';

  exception

  whentoo_many_rowsthen

  insertintojob_errors

  values('morethanonepresident');

  end;

  /*子块2*/

  declare

  v1number(4);

  begin

  selectempnointov1fromemp

  wherejob='manager';

  exception

  whentoo_many_rowsthen

  insertintojob_errors

  values('morethanonemanager');

  end;

  exception

  whenothersthen

  e_mess:=substr(SQLerrm,1,80);

  insertintogeneralerrorsvalues(e_mess);

  end;

  ---------

  3SQL和PL/SQL

  插入

  declare

  my_salnumber(7,2):=3040.55;

  my_enamechar(25):='wanda';

  my_hiredatedate:='08-SEP-88';

  begin

  insertintoemp

  (empno,enmae,job,hiredate,sal,deptno)

  values(2741,my_ename,'cabdriver',my_hiredate,my_sal,20);

  end;

  删除

  declare

  bad_child_typechar(20):='naughty';

  begin

  deletefromsantas_gift_listwhere

  kid_rating=bad_child_type;

  end;

  事务处理

  commit[WORK];

  rollback[WORK];

  (关键字WORK可选,但对命令执行无任何影响)

  savepoint标记名;(保存当前点)

  在事务中标记当前点

  rollback[WORK]to[SAVEPOINT]标记名;(回退到当前保存点)

  取消savepoint命令之后的所有对数据库的修改

  关键字WORK和SAVEPOINT为可选项,对命令执行无任何影响

  函数

  PL/SQL块中可以使用SQL命令的所有函数

  insertintophonebook(lastname)value(upper(my_lastname));

  selectavg(sal)intoavg_salfromemp;

  对于非SQL命令,可使用大多数个体函数

  不能使用聚组函数和参数个数不定的函数,如

  x:=sqrt(y);

  lastname:=upper(lastname);

  age_diff:=months_between(birthday1,birthday2)/12;

  赋值时的数据类型转换

  4种赋值形式:

  变量名:=表达式

  insertinto基表名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';

  字符型转换成日期型

  insertinto表名(num_col)values('604badnumber');

  错误,无法成功地转换数据类型

  ---------

  4条件控制

  例

  declare

  num_jobsnumber(4);

  begin

  selectcount(*)intonum_jobsfromauditions

  whereactorid=&&actor_idandcalled_back='yes';

  ifnum_jobs>100then

  updateactorsetactor_rating='wordclass'

  whereactorid=&&actor_id;

  elsifnum_job=75then

  updateactorsetactor_rating='daytimesoaps'

  whereactorid=&&actor_id;

  else

  updateactorsetactor_rating='waiter'

  whereactorid=&&actor_id;

  endif;

  endif;

  commit;

  end;

  --------

  5循环

  语法

  loop

  ......

  endloop;

  exit;(退出循环)

  exit[when];(退出循环,当满足WHEN时)

  例1

  declare

  ctrnumber(3):=0;

  begin

  loop

  insertintotable1values('tastesgreat');

  insertintotable2values('lessfilling');

  ctr:=ctr+1;

  exitwhenctr=100;

  endloop;

  end;

  (注:如果ctr取为NULL,循环无法结束)

  例2

  FOR语法

  for变量<范围>loop

  ......

  endloop;

  declare

  my_indexchar(20):='fettucinialfredo';

  bowlchar(20);

  begin

  formy_indexinreverse21..30loop

  insertintotemp(coll)values(my_index);

  /*循环次数从30到21*/

  endloop;

  bowl:=my_index;

  end;

  跟在inreverse后面的数字必须是从小到大的顺序,必须是整数,不能是变量或表达式

  ----------

  6游标

  显式游标

  打开游标

  open<游标名>

  例

  opencolor_cur;

  游标属性

  %notfound

  %found

  %rowcount

  %isopen

  例

  fetchmy_curintomy_var;

  whilemy_cur%foundloop

  (处理数据)

  fetchmy_curintomy_var;

  exitwhenmy_cur%rowcount=10;

  endloop;

  %notfound属性

  取值情况如下:

  fetch操作没有返回记录,则取值为true

  fetch操作返回一条记录,则取值为false

  对游标无fetch操作时,取值为null

  <游标名>%notfound

  例

  ifcolor_cur%notfoundthen...

  注:如果没有fetch操作,则<游标名>%notfound将导致出错,

  因为%notfound的初始值为NULL。

  关闭游标

  close<游标名>

  例

  closecolor_cur;

  游标的FOR循环

  语法

  for<记录名>in<游标名>loop

  <一组命令>

  endloop;

  其中:

  索引是建立在每条记录的值之上的

  记录名不必声明

  每个值对应的是记录名,列名

  初始化游标指打开游标

  活动集合中的记录自动完成FETCH操作

  退出循环,关闭游标

  隐式游标

  隐式游标是指SQL命令中用到的,没有明确定义的游标

  insert,update,delete,select语句中不必明确定义游标

  调用格式为SQL%

  存贮有关最新一条SQL命令的处理信息

  隐式游标的属性

  隐式游标有四个属性

  SQL%NOTFOUND

  SQL%FOUND

  SQL%ROWCOUNT:隐式游标包括的记录数

  例:

  deletefrombaseball_teamwherebatting_avg<100;

  ifSQL%rowcount>5thn

  insertintotemp

  values('yourteamneedshelp');

  endif;

  SQL%ISOPEN:取值总为FALSE。SQL命令执行完毕,PL/SQL立即关闭隐式游标。

  ---------

  7标号

  GOTO语句

  用法:

  gotoyou_are_here;

  其中you_are_here是要跳转的语句标号

  标号必须在同一组命令,或是同一块中使用

  正确的使用

  <>(标号)

  x:=x+1

  ifa>bthen

  b:=b+c;

  gotodinner;

  endif;

  错误的使用

  gotojail;

  ifa>bthen

  b:=b+c;

  <>(标号)

  x:=x+1;

  endif;

  标号:解决意义模糊

  标号可用于定义列值的变量

  <>

  declare

  deptnonumber:=20;

  begin

  updateempsetsal=sal*1.1

  wheredeptno=sample.deptno;

  commit;

  endsample;

  如果不用标号和标号限制符,这条命令将修改每条记录。

  ----------

  8异常处理

  预定义的异常情况

  任何ORACLE错误都将自动产生一个异常信息

  一些异常情况已命名,如:

  no_data_found当SELECT语句无返回记录时产生

  too_many_rows没有定义游标,而SELECT语句返回多条记录时产生

  whenevernotfound无对应的记录

  用户定义的异常情况

  由用户自己获取

  在DECLARE部分定义:

  declare

  xnumber;

  something_isnt_rightexception;

  用户定义的异常情况遵循一般的作用范围规则

  条件满足时,获取异常情况:raisesomething_isnt_right

  注意:同样可以获取预定义的异常情况

  exception_init语句

  允许为ORACLE错误命名

  调用格式:

  pragmaexception_init(<表达式>,);

  例

  declare

  deadlock_detectedexception;

  pragmaexception_init(deadlock_detected,-60);

  raise语句

  单独使用RAISE命令,可再一次获取当前的异常情况(就象异常情况被重复处理了一样)。

  在异常处理中,此语句只能单独使用。

  异常处理标识符

  一组用于处理异常情况的语句:

  exception

  when<表达式>or[表达式...]then

  <一组语句>

  ...

  whenothersthen--最后一个处理

  <一组语句>

  end;既结束PL/SQL块部分,也结束异常处理部分

  --------

  练习与答案

  1:

  接收contract_no和item_no值,在inventory表中查找,如果产品:

  已发货,在arrival_date中赋值为今天后的7天

  已订货,在arrival_date中赋值为今天后的一个月

  既无订货又无发货,则在arrival_date中赋值为今天后的两个月,

  并在order表中增加一条新的订单记录。

  product_status的列值为'shipped'和'ordered'

  inventory:

  product_idnumber(6)

  product_descriptionchar(30)

  product_statuschar(20)

  std_shipping_qtynumber(3)

  contract_item:

  contract_nonumber(12)

  item_nonumber(6)

  arrival_datedate

  order:

  order_idnumber(6)

  product_idnumber(6)

  qtynumber(3)

  答案:

  declare

  i_product_idinventory.product_id%type;

  i_product_descriptioninventory.product_description%type;

  i_product_statusinventory.product_status%type;

  i_std_shipping_qtyinventory.std_shipping_qty%type;

  begin

  selectproduct_id,product_description,product_status,std_shipping_qty

  intoi_product_id,i_product_description,

  i_product_status,i_std_shipping_qty

  frominventory

  whereproduct_id=(

  selectproduct_id

  fromcontract_item

  wherecontract_no=&&contractnoanditem_no=&&itemno);

  ifi_product_status='shipped'then

  updatecontract_item

  setarrival_date=sysdate+7

  whereitem_no=&&itemnoandcontract_no=&&contractno;

  elsifi_product_status='ordered'then

  updatecontract_item

  setarrival_date=add_months(sysdate,1)

  whereitem_no=&&itemnoandcontract_no=&&contractno;

  else

  updatecontract_item

  setarrival_date=add_months(sysdate,2)

  whereitem_no=&&itemnoandcontract_no=&&contractno;

  insertintoorders

  values(100,i_product_id,i_std_shipping_qty);

  endif;

  endif;

  commit;

  end;

  2:

  1.找出指定部门中的所有雇员

  2.用带'&'的变量提示用户输入部门编号

  3.把雇员姓名及工资存入prnttable表中,基结构为:

  createtableprnttable

  (seqnumber(7),linechar(80));

  4.异常情况为,部门中奖金不为空值的雇员信息才能存入prnttable表中。

  答案:

  declare

  cursoremp_curis

  selectename,sal,comm

  fromempwheredeptno=&dno;

  emp_recemp_cur%rowtype;

  null_commissionexception;

  begin

  openemp_cur;

  fetchemp_curintoemp_rec;

  while(emp_cur%found)loop

  ifemp_rec.commisnullthen

  begin

  closeemp_cur;

  raisenull_commission;

  end;

  endif;

  fetchemp_curintoemp_rec;

  endloop;

  closeemp_sur;

  exception

  whennull_commissionthen

  openemp_cur;

  fetchemp_curintoemp_rec;

  while(emp_cur%found)loop

  ifemp_rec.commisnotnullthen

  insertintotempvalues(emp_rec.sal,emp_rec.ename);

  endif;

  fetchemp_curintoemp_rec;

  endloop;

  closeemp_cur;

  commit;

  end;

<script type="text/javascript"><!-- google_ad_client = "pub-2947489232296736"; /* 728x15, 创建于 08-4-23MSDN */ google_ad_slot = "3624277373"; google_ad_width = 728; google_ad_height = 15; //--> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
<script type="text/javascript"><!-- google_ad_client = "pub-2947489232296736"; /* 160x600, 创建于 08-4-23MSDN */ google_ad_slot = "4367022601"; google_ad_width = 160; google_ad_height = 600; //--> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
阅读更多
个人分类: xml ajax
想对作者说点什么? 我来说一句

ORACLEpl/sql 手册

2008年11月18日 113KB 下载

没有更多推荐了,返回首页

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭