第7章Oracle10g数据库管理系统.ppt

实用数据库技术 Oracle10g数据库管理系统 学习内容 Oracle概述Oracle10g数据库安装Oracle10g数据库卸载Oracle10g常用管理工具Oracle10g表管理使用过程和触发器 Oracle概述 Oracle公司Oracle数据库 Oracle10g数据库安装 服务器安装需求版本企业版标准版个人版最低配置CPU Pentium166内存 128MB硬盘空间 企业版 1 76GB 标准版 1 76GB 个人版 1 72GB 视频 256色 Oracle10g数据库安装 客户端安装需求安装类型管理者 administrator 运行时环境 runtime 自定义 custom 最低配置CPU Pentium166内存 128MB硬盘空间 管理者 647MB 运行时环境 486MB Oracle10g数据库安装过程 略 Oracle数据库卸载 1 停止服务2 卸载Oracle产品3 清理注册表4 清理环境变量5 清理磁盘 Oracle卸载1 5 开始 设置 控制面板 管理工具 服务停止所有Oracle服务 Oracle卸载2 5 开始 程序 OracleInstallationProducts UniversalInstaller卸装所有Oracle产品 但UniversalInstaller本身不能被删除 Oracle卸载3 5 运行regeditHKEY LOCAL MACHINE SYSTEM CurrentControlSet Services滚动这个列表 删除所有Oracle入口 HKEY LOCAL MACHINE SYSTEM CurrentControlSet Services Eventlog Application 除所有Oracle入口 Oracle卸载4 5 开始 设置 控制面板 系统 高级 环境变量删除环境变量CLASSPATH和PATH中有关Oracle的设定 Oracle卸载5 5 从桌面上 STARTUP 启动 组 程序菜单中 删除所有有关Oracle的组和图标删除 ProgramFiles Oracle目录重新启动计算机 重起后才能完全删除Oracle所在目录 删除与Oracle有关的文件 选择Oracle所在的缺省目录C Oracle 删除这个入口目录及所有子目录 并从Windows2000目录 一般为C WINNT 下删除以下文件ORACLE INI oraodbc ini等等 Oracle10g常用管理工具 OEM控制台SQL PLUS工具介绍 Oracle10g表管理 在Oracle中建表 主要有两种方式 一种是在SQL PLUS中执行建表的SQL语句 另一种是通过OEM工具 Oracle10g数据中的数据类型 PL SQL语言基础 注释变量声明运算符PL SQL块结构控制语句 PL SQL程序设计简介 PL SQL是一种高级数据库程序设计语言 该语言专门用于在各种环境下对ORACLE数据库进行访问 由于该语言集成于数据库服务器中 所以PL SQL代码可以对数据进行快速高效的处理 除此之外 可以在ORACLE数据库的某些客户端工具中 使用PL SQL语言也是该语言的一个特点 SQL与PL SQL PL SQL是ProcedureLanguage StructuredQueryLanguage的缩写 ORACLE的SQL是支持ANSI AmericannationalStandardsInstitute 和ISO92 InternationalStandardsOrganization 标准的产品 PL SQL是对SQL语言存储过程语言的扩展 为什么使用PL SQL 有利于客户 服务器环境应用的运行适合于客户环境 PL SQL块结构 PL SQL程序由三个块组成 即声明部分 执行部分 异常处理部分 DECLARE 声明部分 在此声明PL SQL用到的变量 类型及游标 以及局部的存储过程和函数 BEGIN 执行部分 过程及SQL语句 即程序的主要部分 EXCEPTION 执行异常部分 错误处理 END PL SQL块可以分为三类 无名块 动态构造 只能执行一次 子程序 存储在数据库中的存储过程 函数及包等 当在数据库上建立好后可以在其它程序中调用它们 触发器 当数据库发生操作时 会触发一些事件 从而自动执行相应的程序 标识符 PL SQL程序设计中的标识符定义与SQL的标识符定义的要求相同 要求和限制有 标识符名不能超过30字符 第一个字符必须为字母 不分大小写 不能用 减号 不能是SQL保留字 实例 下面的例子将会删除所有的纪录 而不是KING的记录 DECLAREv enamevarchar2 20 KING BEGINDELETEFROMempWHEREename v ename END 如何运行PL SQL块结构 如何运行PL SQL块结构 建议的命名方法 PL SQL变量类型实例 插入一条记录并显示 DECLARERow idROWID infoVARCHAR2 40 BEGININSERTINTOdeptVALUES 90 SERVICE BEIJING RETURNINGrowid dname to char deptno locINTOrow id info DBMS OUTPUT PUT LINE ROWID row id DBMS OUTPUT PUT LINE info END PL SQL变量类型实例 其中 RETURNING子句用于检索INSERT语句中所影响的数据行数 当INSERT语句使用VALUES子句插入数据时 RETURNING子句还可将列表达式 ROWID和REF值返回到输出变量中 在使用RETURNING子句是应注意以下几点限制 不能并行DML语句 不能检索LONG类型信息 当通过视图向基表中插入数据时 只能与单基表视图一起使用 PL SQL变量类型实例 修改一条记录并显示 DECLARERow idROWID infoVARCHAR2 40 BEGINUPDATEdeptSETdeptno 80WHEREDNAME SERVICE RETURNINGrowid dname to char deptno locINTOrow id info DBMS OUTPUT PUT LINE ROWID row id DBMS OUTPUT PUT LINE info END PL SQL变量类型实例 其中 RETURNING子句用于检索被修改行信息 当UPDATE语句修改单行数据时 RETURNING子句可以检索被修改行的ROWID值 以及行中被修改列的列表达式 并可将他们存储到PL SQL变量或复合变量中 当UPDATE语句修改多行数据时 RETURNING子句可以将被修改行的ROWID值 以及列表达式值返回到复合变量数组中 在UPDATE中使用RETURNING子句的限制与INSERT语句中对RETURNING子句的限制相同 PL SQL变量类型实例 删除一条记录并显示 DECLARERow idROWID infoVARCHAR2 40 BEGINDELETEdeptWHEREDNAME SERVICE RETURNINGrowid dname to char deptno locINTOrow id info DBMS OUTPUT PUT LINE ROWID row id DBMS OUTPUT PUT LINE info END PL SQL变量类型实例 RETURNING子句用于检索被删除行信息 当DELETE语句修改单行数据时 RETURNING子句可以检索被删除行的ROWID 以及被删除行中列的列表达式 并可将他们存储到PL SQL变量或复合变量中 当UPDATE语句修改多行数据时 RETURNING子句可以将被修改行的ROWID 以及列表达式值返回到复合变量数组中 在UPDATE中使用RETURNING子句的限制与INSERT语句中对RETURNING子句的限制相同 复合类型 ORACLE在PL SQL中除了提供象前面介绍的各种类型外 还提供一种称为复合类型的类型 记录和表 记录类型使用 TYPE使用 ROWTYPE 记录类型 记录类型是把逻辑相关的数据作为一个单元存储起来 它必须包括至少一个标量型 数字型 字符型 布尔型 日期型 或RECORD数据类型的成员 称作PL SQLRECORD的域 FIELD 其作用是存放互不相同但逻辑相关的信息 定义记录类型语法如下 TYPErecord typeISRECORD Field1type1 NOTNULL exp1 Field2type2 NOTNULL exp2 Fieldntypen NOTNULL expn 实例 DECLARETYPEtest recISRECORD CodeVARCHAR2 10 NameVARCHAR2 30 NOTNULL abook V booktest rec BEGINV book code 123 V book name C Programming DBMS OUTPUT PUT LINE v book code v book name END 使用 TYPE 定义一个变量 其数据类型与已经定义的某个数据变量的类型相同 或者与数据库表的某个列的数据类型相同 这时可以使用 TYPE 使用 TYPE特性的优点在于 所引用的数据库列的数据类型可以不必知道 所引用的数据库列的数据类型可以实时改变 实例1 DECLARE 用 TYPE类型定义与表相配的字段TYPEt RecordISRECORD T noemp empno TYPE T nameemp ename TYPE T salemp sal TYPE 声明接收数据的变量v empt Record BEGINSELECTempno ename salINTOv empFROMempWHEREempno 7788 DBMS OUTPUT PUT LINE TO CHAR v emp t no v emp t name TO CHAR v emp t sal END 实例2 DECLAREv empnoemp empno TYPE 使用 ROWTYPE PL SQL提供 ROWTYPE操作符 返回一个记录类型 其数据类型和数据库表的数据结构相一致 使用 ROWTYPE特性的优点在于 所引用的数据库中列的个数和数据类型可以不必知道 所引用的数据库中列的个数和数据类型可以实时改变 实例 DECLAREv empnoemp empno TYPE 运算符和表达式 算术运算符 运算符和表达式 关系运算符 运算符和表达式 逻辑运算符 变量赋值 在PL SQL编程中 变量赋值是一个值得注意的地方 它的语法如下 variable是一个PL SQL变量 expression是一个PL SQL表达式 variable expression 字符及数字运算特点 空值加数字仍是空值 NULL NULL空值加 连接 字符 结果为字符 NULL BOOLEAN赋值 布尔值只有TRUE FALSE及NULL三个值 如 DECLAREdoneBOOLEAN thefollowingstatementsarelegal BEGINdone FALSE WHILENOTdoneLOOPNull ENDLOOP END 游标赋值 游标赋值是通过SELECT语句来完成的 每次执行SELECT语句就赋值一次 一般要求被赋值的变量与SELECT中的列名要一一对应 游标赋值 DECLAREemp idemp empno TYPE 7788 emp nameemp ename TYPE wagesemp sal TYPE BEGINSELECTename NVL sal 0 NVL comm 0 INTOemp name wagesFROMempWHEREempno emp id DBMS OUTPUT PUT LINE emp name to char wages END 结果 SCOTT 3000 可转换的类型赋值 CHAR转换为NUMBER 使用TO NUMBER函数来完成字符到数字的转换 如 v total TO NUMBER 100 0 sal NUMBER转换为CHAR 使用TO CHAR函数可以实现数字到字符的转换 如 v comm TO CHAR 123 45 元 字符转换为日期 使用TO DATE函数可以实现字符到日期的转换 如 v date TO DATE 2001 07 03 yyyy mm dd 日期转换为字符 使用TO CHAR函数可以实现日期到字符的转换 如 v to day TO CHAR SYSDATE yyyy mm ddhh24 mi ss 变量作用范围及可见性 PL SQL的变量作用范围特点是 变量的作用范围是在你所引用的程序单元 块 子程序 包 内 即从声明变量开始到该块的结束 一个变量 标识 只能在你所引用的块内是可见的 当一个变量超出了作用范围 PL SQL引擎就释放用来存放该变量的空间 因为它可能不用了 在子块中重新定义该变量后 它的作用仅在该块内 实例 DECLAREEmesschar 80 BEGINDECLAREV1NUMBER 4 BEGINSELECTempnoINTOv1FROMempWHERELOWER job president DBMS OUTPUT PUT LINE V1 EXCEPTIONWhenTOO MANY ROWSTHENDBMS OUTPUT PUT LINE Morethanonepresident END 实例续 DECLAREV1NUMBER 4 BEGINSELECTempnoINTOv1FROMempWHERELOWER job manager EXCEPTIONWhenTOO MANY ROWSTHENDBMS OUTPUT PUT LINE Morethanonemanager END EXCEPTIONWhenothersTHENEmess substr SQLERRM 1 80 DBMS OUTPUT PUT LINE emess END 注释 在PL SQL里 可以使用两种符号来写注释 即 使用双 减号 加注释V SalNUMBER 12 2 工资变量 使用 来加一行或多行注释 文件名 department salary sql 示例简单数据插入例子 DECLAREv enameVARCHAR2 20 Bill v salNUMBER 7 2 1234 56 v deptnoNUMBER 2 10 v empnoNUMBER 4 8888 BEGININSERTINTOemp empno ename JOB sal deptno hiredate VALUES v empno v ename Manager v sal v deptno TO DATE 1954 06 09 yyyy mm dd COMMIT END 示例简单数据删除例子 DECLAREv empnonumber 4 8888 BEGINDELETEFROMempWHEREempno v empno COMMIT END PL SQL流程控制语句 PL SQL的流程控制语句包括如下三类 控制语句 IF语句 循环语句 LOOP语句 EXIT语句 顺序语句 GOTO语句 NULL语句 条件语句 IFTHENPL SQL和SQL语句ENDIF IFTHENPL SQL和SQL语句ELSE其它语句ENDIF 条件语句 IFTHENPL SQL和SQL语句ELSIFTHEN其它语句ELSIFTHEN其它语句ELSE其它语句ENDIF 条件语句实例 DECLAREv empnoemp empno TYPE CASE表达式 CASEselectorWHENexpression1THENresult1WHENexpression2THENresult2WHENexpressionNTHENresultN ELSEresultN 1 END CASE表达式实例 DECLAREV gradechar 1 UPPER CASE表达式实例 代码运行结果 Entervalueforp grade Aold2 V gradechar 1 UPPER Grade AAppraisal Excellent 循环 简单循环 LOOP要执行的语句 EXITWHEN 条件满足 退出循环语句 ENDLOOP LOOP循环实例 DECLAREintNUMBER 2 0 BEGINLOOPint int 1 DBMS OUTPUT PUT LINE int的当前值为 int EXITWHENint 10 ENDLOOP END 循环 WHILE循环 WHILELOOP要执行的语句 ENDLOOP WHILE循环实例 DECLARExNUMBER 1 BEGINWHILEx 10LOOPDBMS OUTPUT PUT LINE X的当前值为 x x x 1 ENDLOOP END 循环 数字式循环每循环一次 循环变量自动加1 使用关键字REVERSE 循环变量自动减1 跟在INREVERSE后面的数字必须是从小到大的顺序 而且必须是整数 不能是变量或表达式 可以使用EXIT退出循环 FOR循环计数器IN REVERSE 下限 上限LOOP要执行的语句 ENDLOOP 数字式循环实例 BEGINFORintin1 10LOOPDBMS OUTPUT PUT LINE int的当前值为 int ENDLOOP END 数字式循环实例 CREATETABLEtemp table num colNUMBER DECLAREV counterNUMBER 10 BEGININSERTINTOtemp table num col VALUES v counter FORv counterIN20 25LOOPINSERTINTOtemp table num col VALUES v counter ENDLOOP INSERTINTOtemp table num col VALUES v counter FORv counterINREVERSE20 25LOOPINSERTINTOtemp table num col VALUES v counter ENDLOOP END DROPTABLEtemp table GOTO和标签 标号是用 括起来的标识符 GOTOlabel GOTO和标签实例 DECLAREV counterNUMBER 1 BEGINLOOPDBMS OUTPUT PUT LINE V counter的当前值为 V counter V counter v counter 1 IFv counter 10THENGOTOl ENDofLOOP ENDIF ENDLOOP DBMS OUTPUT PUT LINE V counter的当前值为 V counter END NULL语句 DECLARE BEGIN IFv numISNULLTHENGOTOprint1 ENDIF NULL 不需要处理任何数据 END 游标概念 为了处理SQL语句获取一个表中记录 ORACLE提供一个上下文 它提供了一个指向语句的指针以及查询的活动集 activeset 游标是一个指向上下文的句柄 handle 或指针 通过游标 PL SQL可以控制上下文区和处理语句时上下文区会发生些什么事情 游标概念 对于不同的SQL语句 游标的使用情况不同 处理显式游标 显式游标处理需四个PL SQL步骤 定义游标格式 CURSORcursor nameISselect statement 打开游标格式 OPENcursor name 提取游标数据格式 FETCHcursor nameINTO variable list record variable 关闭游标格式 CLOSEcursor name 示例1 DECLARECURSORc cursorISSELECTename salFROMempWHERErownum 11 v enameemp ename TYPE v salemp sal TYPE BEGINOPENc cursor FETCHc cursorINTOv ename v sal WHILEc cursor FOUNDLOOPDBMS OUTPUT PUT LINE v ename to char v sal FETCHc cursorINTOv ename v sal ENDLOOP CLOSEc cursor END 示例2 DECLAREDeptRecdept ROWTYPE Dept namedept dname TYPE Dept locdept loc TYPE CURSORc1ISSELECTdname locFROMdeptWHEREdeptno 30 CURSORc2 dept noNUMBERDEFAULT10 ISSELECTdname locFROMdeptWHEREdeptno dept no CURSORc3 dept noNUMBERDEFAULT10 ISSELECT FROMdeptWHEREdeptno dept no BEGINOPENc1 LOOPFETCHc1INTOdept name dept loc EXITWHENc1 NOTFOUND DBMS OUTPUT PUT LINE dept name dept loc ENDLOOP 示例2续 CLOSEc1 OPENc2 LOOPFETCHc2INTOdept name dept loc EXITWHENc2 NOTFOUND DBMS OUTPUT PUT LINE dept name dept loc ENDLOOP CLOSEc2 OPENc3 dept no 20 LOOPFETCHc3INTOdeptrec EXITWHENc3 NOTFOUND DBMS OUTPUT PUT LINE deptrec deptno deptrec dname deptrec loc ENDLOOP CLOSEc3 END 游标属性 FOUND布尔型属性 当最近一次读记录时成功返回 则值为TRUE NOTFOUND布尔型属性 与 FOUND相反 ISOPEN布尔型属性 当游标已打开时返回TRUE ROWCOUNT数字型属性 返回已从游标中读取的记录数 示例 DECLAREv empnoemp empno TYPE v salemp sal TYPE CURSORc cursorISSELECTempno salFROMemp BEGINOPENc cursor LOOPFETCHc cursorINTOv empno v sal EXITWHENc cursor NOTFOUND IFv sal 1200THENUPDATEempSETsal sal 50WHEREempno v empno DBMS OUTPUT PUT LINE 编码为 v empno 工资已更新 ENDIF DBMS OUTPUT PUT LINE 记录数 c cursor ROWCOUNT ENDLOOP CLOSEc cursor END 游标的FOR循环 FORindex variableINcursor name value value LOOP 游标数据处理代码ENDLOOP 游标中使用FOR循环示例 DECLARECURSORc salISSELECTempno ename salFROMemp BEGIN 隐含打开游标FORv salINc salLOOP 隐含执行一个FETCH语句DBMS OUTPUT PUT LINE to char v sal empno v sal ename to char v sal sal 隐含监测c sal NOTFOUNDENDLOOP 隐含关闭游标END 有参数游标中使用FOR循环示例 DECLARECURSORc cursor dept noNUMBERDEFAULT10 ISSELECTdname locFROMdeptWHEREdeptno30 DBMS OUTPUT PUT LINE c1 rec dname c1 rec loc ENDLOOP DBMS OUTPUT PUT LINE CHR 10 使用默认的dept no参数值10 FORc1 recINc cursorLOOPDBMS OUTPUT PUT LINE c1 rec dname c1 rec loc ENDLOOP END 游标FOR循环语句中使用子查询来实现游标实例 BEGINFORc1 recIN SELECTdname locFROMdept LOOPDBMS OUTPUT PUT LINE c1 rec dname c1 rec loc ENDLOOP END 处理隐式游标 当查询返回结果超过一行时 就需要一个显式游标 显式游标主要是用于对查询语句的处理 尤其是在查询结果为多条记录的情况下 而对于非查询语句 如修改 删除操作 则由ORACLE系统自动地为这些操作设置游标并创建其工作区 这些由系统隐含创建的游标称为隐式游标 隐式游标的名字为SQL 这是由ORACLE系统定义的 对于隐式游标的操作 如定义 打开 取值及关闭操作 都由ORACLE系统自动地完成 无需用户进行处理 用户只能通过隐式游标的相关属性 来完成相应的操作 在隐式游标的工作区中 所存放的数据是与用户自定义的显示游标无关的 最新处理的一条SQL语句所包含的数据 处理隐式游标 格式调用为 SQL 当执行一条DML语句后 DML语句的结果保存在四个游标属性中 这些属性用于控制程序流程或者了解程序的状态 当运行DML语句时 PL SQL打开一个内建游标并处理结果 游标是维护查询结果的内存中的一个区域 游标在运行DML语句时打开 完成后关闭 处理隐式游标 隐式游标只使用SQL FOUND SQL NOTFOUND SQL ROWCOUNT三个属性 SQL FOUND SQL NOTFOUND是布尔值 SQL ROWCOUNT是整数值 处理隐式游标 SQL FOUND 布尔型属性 当最近一次读记录时成功返回 则值为TRUE 在执行任何DML语句前SQL FOUND和SQL NOTFOUND的值都是NULL 在执行DML语句后 SQL FOUND的属性值将是 INSERT为TRUE DELETE和UPDATE 至少有一行被DELETE或UPDATE为TRUE SELECTINTO至少返回一行为TRUE SQL FOUND为TRUE时 SQL NOTFOUND为FALSE 处理隐式游标示例 DECLAREV deptnoemp deptno TYPE 异常错误处理 异常情况处理是用来处理正常执行过程中未预料的事件 程序块的异常处理预定义的错误和自定义错误 由于PL SQL程序块一旦产生异常而没有指出如何处理时 程序就会自动终止整个程序运行 有三种类型的异常 预定义 Predefined 异常ORACLE预定义的异常情况大约有24个 对这种异常情况的处理 无需在程序中定义 由ORACLE自动将其引发 非预定义 Predefined 异常即其他标准的ORACLE错误 对这种异常情况的处理 需要用户在程序中定义 然后由ORACLE自动将其引发 用户定义 User define 异常程序执行过程中 出现编程人员认为的非正常情况 对这种异常情况的处理 需要用户在程序中定义 然后显式地在程序中将其引发 异常处理部分结构 EXCEPTIONWHENfirst exceptionTHENWHENsecond exceptionTHENWHENOTHERSTHENEND 预定义的异常处理 实例 DECLAREv empnoemp empno TYPE 非预定义的异常处理 非定义的ORACLE错误进行定义 步骤如下 1 在PL SQL块的定义部分定义异常情况 EXCEPTION 2 将其定义好的异常情况 与标准的ORACLE错误联系起来 使用EXCEPTION INIT语句 PRAGMAEXCEPTION INIT 3 在PL SQL块的异常情况处理部分对异常情况做出相应的处理 示例 INSERTINTOdeptVALUES 50 FINANCE CHICAGO DECLAREv deptnodept deptno TYPE 存储函数和过程 ORACLE提供可以把PL SQL程序存储在数据库中 并可以在任何地方来运行它 这样就叫存储过程或函数 过程和函数统称为PL SQL子程序 他们是被命名的PL SQL块 均存储在数据库中 并通过输入 输出参数或输入 输出参数与其调用者交换信息 过程和函数的唯一区别是函数总向调用者返回数据 而过程则不返回数据 在本节中主要介绍 创建存储过程和函数 建立和管理存储过程和函数 创建函数 语法如下 CREATE orREPLACE FUNCTIONfunction name argment in inout TYPE argment in out inout type RETURNRETURN type IS AS BEGINFUNCTION bodyEXCEPTION ENDfunction name 创建函数示例 CREATEORREPLACEFUNCTIONget salary Dept noNUMBER Emp countOUTNUMBER RETURNNUMBERISV sumNUMBER BEGINSELECTSUM sal count INTOV sum emp countFROMempWHEREdeptno dept no RETURNv sum EXCEPTIONWHENNO DATA FOUNDTHENDBMS OUTPUT PUT LINE 你需要的数据不存在 WHENOTHERSTHENDBMS OUTPUT PUT LINE SQLCODE SQLERRM ENDget salary 函数的调用 格式为 argument value1 argument value2 函数的调用实例 DECLAREV numNUMBER V sumNUMBER BEGINV sum get salary 30 v num DBMS OUTPUT PUT LINE 30号部门工资总和 v sum 人数 v num END 运行结果 30号部门工资总和 9400 人数 6 创建过程 创建过程语法 CREATE ORREPLACE PROCEDUREProcedure name argment IN OUT INOUT Type argment IN OUT INOUT Type AUTHIDDEFINER CURRENT USER IS AS BEGINEXCEPTIONEND 实例1 CREATEtablelogtable useridVARCHAR2 10 logdatedate CREATEORREPLACEPROCEDURElogexecutionISBEGININSERTINTOlogtable userid logdate VALUES USER SYSDATE END 实例2 CREATEORREPLACEPROCEDUREDelEmp v empnoINemp empno TYPE ASNo resultEXCEPTION BEGINDELETEFROMempWHEREempno v empno IFSQL NOTFOUNDTHENRAISEno result ENDIF DBMS OUTPUT PUT LINE 编码为 v empno 的员工已被除名 EXCEPTIONWHENno resultTHENDBMS OUTPUT PUT LINE 你需要的数据不存在 WHENOTHERSTHENDBMS OUTPUT PUT LINE SQLCODE SQLERRM ENDDelEmp 实例3 CREATEORREPLACEPROCEDUREInsertEmp v empnoinemp empno TYPE v nameinemp ename TYPE v deptnoinemp deptno TYPE ASempno remainingEXCEPTION PRAGMAEXCEPTION INIT empno remaining 1 1是违反唯一约束条件的错误代码 BEGININSERTINTOemp empno ename hiredate deptno VALUES v empno v name sysdate v deptno DBMS OUTPUT PUT LINE 插入数据记录成功 EXCEPTIONWHENempno remainingTHENDBMS OUTPUT PUT LINE 违反数据完整性约束 WHENOTHERSTHENDBMS OUTPUT PUT LINE SQLCODE SQLERRM ENDInsertEmp 调用存储过程 ORACLE使用EXECUTE语句来实现对存储过程的调用 EXEC UTE Procedure name parameter1 parameter2 存储过程和调用实例1 CREATEORREPLACEPROCEDUREQueryEmp v empnoINemp empno TYPE v enameOUTemp ename TYPE v salOUTemp sal TYPE ASBEGINSELECTename salINTOv ename v salFROMempWHEREempno v empno DBMS OUTPUT PUT LINE 编码为 v empno 的员工已经查到 EXCEPTIONWHENNO DATA FOUNDTHENDBMS OUTPUT PUT LINE 你需要的数据不存在 WHENOTHERSTHENDBMS OUTPUT PUT LINE SQLCODE SQLERRM ENDQueryEmp 实例2 DECLAREv1emp ename TYPE v2emp sal TYPE BEGINQueryEmp 7788 v1 v2 DBMS OUTPUT PUT LINE 姓名 v1 DBMS OUTPUT PUT LINE 工资 v2 QueryEmp 7902 v1 v2 DBMS OUTPUT PUT LINE 姓名 v1 DBMS OUTPUT PUT LINE 工资 v2 QueryEmp 8899 v1 v2 DBMS OUTPUT PUT LINE 姓名 v1 DBMS OUTPUT PUT LINE 工资 v2 END 运行结果 编码为7788的员工已经查到 姓名 SCOTT工资 3000编码为7902的员工已经查到 姓名 FORD工资 3000你需要的数据不存在 姓名 工资 实例3 CREATEORREPLACEPROCEDUREproc demo Dept noNUMBERDEFAULT10 Sal sumOUTNUMBER Emp countOUTNUMBER ISBEGINSELECTSUM sal COUNT INTOsal sum emp countFROMempWHEREdeptno dept no EXCEPTIONWHENNO DATA FOUNDTHENDBMS OUTPUT PUT LINE 你需要的数据不存在 WHENOTHERSTHENDBMS OUTPUT PUT LINE SQLCODE SQLERRM ENDproc demo 实例4 DECLAREV numNUMBER V sumNUMBER 8 2 BEGINProc demo 30 v sum v num DBMS OUTPUT PUT LINE 30号部门工资总和 v sum 人数 v num Proc demo sal sum v sum emp count v num DBMS OUTPUT PUT LINE 10号部门工资总和 v sum 人数 v num END 运行结果 30号部门工资总和 9400 人数 610号部门工资总和 8750 人数 3 实例5 DECLAREV numNUMBER V sumNUMBER 8 2 PROCEDUREproc demo Dept noNUMBERDEFAULT10 Sal sumOUTNUMBER Emp countOUTNUMBER ISBEGINSELECTSUM sal COUNT INTOsal sum emp countFROMempWHEREdeptno dept no EXCEPTION 实例5续 WHENNO DATA FOUNDTHENDBMS OUTPUT PUT LINE 你需要的数据不存在 WHENOTHERSTHENDBMS OUTPUT PUT LINE SQLCODE SQLERRM ENDproc demo BEGINProc demo 30 v sum v num DBMS OUTPUT PUT LINE 30号部门工资总和 v sum 人数 v num Proc demo sal sum v sum emp count v num DBMS OUTPUT PUT LINE 10号部门工资总和 v sum 人数 v num END 运行结果 30号部门工资总和 9400 人数 610号部门工资总和 8750 人数 3 删除过程和函数 删除过程 语法如下 DROPPROCEDURE user Procudure name 删除函数 语法如下 DROPFUNCTION user Function name 包的创建和应用 一个包由两个分开的部分组成 包定义 PACKAGE 包定义部分声明包内数据类型 变量 常量 游标 子程序和异常错误处理等元素 这些元素为包的公有元素 包主体 PACKAGEBODY 包主体则是包定义部分的具体实现 它定义了包定义部分所声明的游标和子程序 在包主体中还可以声明包的私有元素 包定义和包主体分开编译 并作为两部分分开的对象存放在数据库字典中 详见数据字典user source all source dba source 包的定义 创建包定义 CREATE ORREPLACE PACKAGEpackage name AUTHID CURRENT USER DEFINER IS AS 公有数据类型定义 公有数据类型定义 公有游标声明 公有游标声明 公有变量 常量声明 公有变量 常量声明 公有子程序声明 公有子程序声明 END package name 包的定义 创建包主体 CREATE ORREPLACE PACKAGEBODYpackage name IS AS 私有数据类型定义 私有数据类型定义 私有变量 常量声明 私有变量 常量声明 私有子程序声明和定义 私有子程序声明和定义 公有游标定义 公有游标定义 公有子程序定义 公有子程序定义 BEGINPL SQL语句END package name 包的开发步骤 与开发存储过程类似 包的开发需要几个步骤 将每个存储过程调式正确 用文本编辑软件将各个存储过程和函数集成在一起 按照包的定义要求将集成的文本的前面加上包定义 按照包的定义要求将集成的文本的前面加上包主体 使用SQLPLUS或开发工具进行调式 创建包实例 CREATEORREPLACEPACKAGEdemo packISDeptRecdept ROWTYPE FUNCTIONadd dept dept noNUMBER dept nameVARCHAR2 locationVARCHAR2 RETURNNUMBER FUNCTIONremove dept dept noNUMBER RETURNNUMBER PROCEDUREquery dept dept noINNUMBER ENDdemo pack 创建包体实例 CREATEORREPLACEPACKAGEBODYdemo packISFUNCTIONadd dept dept noNUMBER dept nameVARCHAR2 locationVARCHAR2 RETURNNUMBERISempno remainingEXCEPTION PRAGMAEXCEPTION INIT empno remaining 1 1是违反唯一约束条件的错误代码 BEGININSERTINTOdeptVALUES dept no dept name location IFSQL FOUNDTHENRETURN1 ENDIF EXCEPTIONWHENempno remainingTHENRETURN0 WHENOTHERSTHENRETURN 1 ENDadd dept 创建包体实例续 FUNCTIONremove dept dept noNUMBER RETURNNUMBERISBEGINDELETEFROMdeptWHEREdeptno dept no IFSQL FOUNDTHENRETURN1 ELSERETURN0 ENDIF EXCEPTIONWHENOTHERSTHENRETURN 1 ENDremove dept 创建包体实例续 PROCEDUREquery dept dept noINNUMBER ISBEGINSELECT INTODeptRecFROMdeptWHEREdeptno dept no EXCEPTIONWHENNO DATA FOUNDTHENDBMS OUTPUT PUT LINE 数据库中没有编码为 dept no 的部门 WHENTOO MANY ROWSTHENDBMS OUTPUT PUT LINE 程序运行错误 请使用游标 WHENOTHERSTHENDBMS OUTPUT PUT LINE SQLCODE SQLERRM ENDquery dept BEGINNull ENDdemo pack 调用包示例 DECLAREVarNUMBER BEGINVar demo pack add dept 90 Admin Beijing IFvar 1THENDBMS OUTPUT PUT LINE SQLCODE SQLERRM ELSIFvar 0THENDBMS OUTPUT PUT LINE 该部门记录已经存在 ELSEDBMS OUTPUT PUT LINE 添加记录成功 Demo pack query dept 90 DBMS OUTPUT PUT LINE demo pack DeptRec deptno demo pack DeptRec dname demo pack DeptRec loc var demo pack remove dept 90 IFvar 1THENDBMS OUTPUT PUT LINE SQLCODE SQLERRM ELSIFvar 0THENDBMS OUTPUT PUT LINE 该部门记录不存在