PLSQL开发精.pptx

2024/9/6 周五*第八章 PL/Sql开发 2024/9/6 周五*本章要点lPL/SQL基本概念 lPL/SQL的变量 lPL/SQL的基本语法 lOracle各种程序单元 l使用游标取出多条数据 l异常处理 2024/9/6 周五*PL/SQL概述l什么是PL/SQLlPL/SQL也是一种程序语言,被称作支持SQL的程序语言(Program Language),是Oracle数据库对SQL语句的扩展，在普通的SQL语言中增加了编程语言的特点l数据操作和查询语句被包含在PL/SQL代码的过程性单元中，经过逻辑判断、循环等操作完成复杂的功能或者计算2024/9/6 周五*PL/SQL的优点l使用PL/SQL可以编写具有很多高级功能的程序,虽l然这些功能可以通过多个SQL语句来完成同样的功l能,但是PL/SQL具有如下的优点:l使一组语句功能形成模块化程序开发l使用过程性语言控制程序结构l可以对程序中的错误进行处理l具有较好的可移植性l集成在数据库中,调用更快l减少了网络的交互,有助于提高程序性能2024/9/6 周五*PL/SQL提供的新特性lPL/SQL提供了一些新的特性,可以进行复杂的信息处理l软件包l触发器l存储过程l函数l异常处理lPL/SQL可以使用所有的SQL数据操作,游标控制和事务控制命令,以及所有的SQL函数和运算符.PL/SQL完全支持SQL数据类型2024/9/6 周五*SQL,SQL*PLUS,PL/SQL之间的关系l可以把oracle数据库看作餐馆的厨房,而SQL*PLUS是将菜单(脚l本,命令或程序)送进厨房(即数据库)的服务员.在厨房中有两l个厨师,SQL 和PL/SQL.作为服务员的SQL*PLUS知道它可以l处理那些命令,那些命令要交给厨师处理.l在SQL*PLUS提示符下输入的执行命令或程序就象顾客点的l菜.对于每个顾客点的菜,厨师都知道如何进行处理,就像在厨l师心中的菜谱一样,PL/SQL也存储常用命令的食谱(这些元素l称为触发器,存储函数,存储过程,软件包).l有些大菜需要多个厨师共同处理,大多数的程序都是结合了lSQL和PL/SQL,并在他们之间来回的传递信息,从而处理脚本或程l序.顾客点的菜作好之后,再由作为服务员的SQL*PLUS将执行的结l果显示给用户2024/9/6 周五*PL/SQL提高了性能DatabaseDatabaseSQLSQLSQLSQLSQLIF.THENSQLELSESQLEND IF;SQL应用程序应用程序应用程序应用程序2024/9/6 周五*PL/SQL块的基本结构lPL/SQL中起作用的部分都是由基本块组成的.基本块有四个l组成部分l声明部分声明部分:DECLARE 可选部分变量、常量、游标、用户定义异常声明l执行体开始部分执行体开始部分:BEGIN 必要部分SQL语句PL/SQL语句l异常处理部分异常处理部分:EXCEPTION 可选部分程序出现异常时，捕捉异常并处理异常l执行体结束执行体结束:END;必要部分2024/9/6 周五*PL/SQL块例子lDECLAREl v_dept_id employees.department_id%TYPE;lBEGINl SELECT department_id INTO v_dept_idl FROM employeesl WHERE employee_id=100;l DELETE departmentsl WHERE department_id=v_dept_id;l COMMIT;lEXCEPTIONl WHEN OTHERS THENl ROLLBACK;l INSERT INTO exception_table(message)l VALUES(Some error occurred in the database.);l COMMIT;lEND;2024/9/6 周五*PL/SQL环境PL/SQLBlockPL/SQL 引擎引擎oracle 数据库数据库PL/SQLBlockProceduralStatementExecutornon-SQLSQLSQL Statement ExecutorSQL 引擎引擎在数据库执行PL/SQL程序的时候,PL/SQL语句和SQL语句被分别解析和执行的。PL/SQL块被数据库内部的PL/SQL引擎提取，将SQL语句取出送给Oralce的SQL引擎。两种语句分别在两种引擎中分析处理，在数据库内部完成数据交互，处理的过程2024/9/6 周五*在PL/SQL中处理变量l在声明部分在声明部分声明和初始化变量l在执行部分在执行部分为变量赋新值，或在表达式中使用变量l在异常处理部分也可以使用变量l通过参数把值传递到PL/SQL 块中l通过输出变量或者参数将值传出PL/SQL块2024/9/6 周五*声明变量和常量：语法l定义的标示符名称应遵循命名规则l在声明常量和变量的时候可以为其设置初始化值,也可以设置NOT NULLl可以使用赋值运算符(:=)或者DEFAULT保留字来初始化标识符l在声明时，每行只能声明一个标识符identifier CONSTANT datatype NOT NULL :=|DEFAULT expr;例如例如:v_total_sal NUMBER(9,2):=0;c_tax_rateCONSTANT NUMBER(3,2):=8.25;v_genderCHAR(1);v_validBOOLEAN NOT NULL:=TRUE;2024/9/6 周五*PL/Sql中的变量类型l简单变量l复合（组合）变量l外部变量2024/9/6 周五*简单变量l简单变量不包括任何组件，只能保存一个值l基本类型包括三大类：字符,数字,日期BINARY_INTEGER 整形数字NUMBER(precision,scale)数字类型CHAR(maximum_length)定长字符类型VARCHAR2(maximum_length)变长字符类型DATE 日期类型LONG 长字符类型LONG RAW 长二进制类型CLOB/BLOB/BFILE 大对象类型(字符大对象,二 进制大对象,操作系统文件大对象)BOOLEAN 布尔类型,有效值为 TRUE,FALSE,NULL 2024/9/6 周五*简单变量的声明lv_genderCHAR(1);lv_countBINARY_INTEGER:=0;lv_total_salNUMBER(9,2):=0;lv_order_dateDATE:=SYSDATE+7;lc_tax_rate CONSTANT NUMBER(3,2):=8.25;lv_validBOOLEAN NOT NULL:=TRUE;2024/9/6 周五*复合数据类型l复合变量也叫做组合变量复合变量也叫做组合变量.在复合变量中包含多个内部的组件,每个组件都可以单独存放值.一个复合变量可以存放多个值l与简单变量类型不同,复合变量类型不是数据库中已经存在的数据类型,所以复合变量在声明类型之前,首先要创建使用到的复合类型,然后将变量声明为复合变量l复合数据类型：PL/SQL TABLES 表类型PL/SQL RECORDS 记录类型复合类型被创建后,可以被使用多次定义多个变量2024/9/6 周五*复合数据类型-TABLEl表类型类似于其他编程语言中的数组类型l由两个组件组成：数据类型为BINARY_INTEGER(整形数字)的主键数据类型为一个确定的简单类型的列lTable类型没有长度限制，可以动态增长.表类型中的第二部分类似与数组中的值,这个部分必须是一个已经确定的简单类型,不能是其他的复合类型l表类型的结构很像数组.第一部分使一个按1递增的整形数字,起到数字索引的作用,第二部分使一种确定的简单类型,用来存放每个索引号对应的具体的数值2024/9/6 周五*PL/SQL TABLE 结构l 主键列l .l 1Jonesl 2Smithl 3Madurol .lBINARY_INTEGER标量2024/9/6 周五*声明一个PL/SQL TABLEl TYPE type_name IS TABLE OF scalar_datatypelNOT NULL INDEX BY BINARY_INTEGER;l identifiertype_name;.TYPE name_table_type IS TABLE OF VARCHAR2(16)INDEX BY BINARY_INTEGER;v1_namename_table_type;v2_namename_table_type;.语法：语法：例子：例子：2024/9/6 周五*PL/SQL RECORDSl复合类型中的RECODES类型是由多个组件组成的一种类型.包含一个或几个组件，每个组件称为一个域(FIELD)，域的数据类型可以是简单变量类型、另一个RECORD类型或PL/SQL的TABLE类型 l在使用RECORD变量时把多个域的集合作为一个逻辑单元使用,对记录类型变量赋值或引用,都需要使用“记录变量记录变量名名.域名域名”的方式来实现l主要用于从表中取出查询到的行数据2024/9/6 周五*PL/SQL RECORD结构l记录类型可以包含一个或多个域,每个域相当于记录类型变量的一个属性.在使用记录变量类型时,实际上是对记录类型变量的属性进行操作.每个域都可以是不同的数据类型,存放不同类型的数据Field1(数据类型数据类型)Field2(数据类型数据类型)Field3(数据类型数据类型)2024/9/6 周五*声明PL/SQL RECORDl语法：语法：lTYPE type_name IS RECORDl(field_name1 field_typelNOT NULL:=|DEFAULT expr,l field_name2 field_typelNOT NULL:=|DEFAULT expr,.);l identifiertype_name;l例子：例子：l.l TYPE emp_record_type IS RECORDl (last_name VARCHAR2(25),l first_name VARCHAR2(25),l salNUMBER(8)l );l emp_record emp_record_type;l.2024/9/6 周五*%TYPE与%ROWTYPEl除了象前面那样直接为变量声明一个确定的简单类型或者已经创建好的复合类型外,PL/SQL也支持另外的两种声明变量类型的方法,通红%TYPE和%ROWTYPE属性来声明变量类型2024/9/6 周五*%TYPE 属性l通过%TYPE属性声明一个变量，变量将遵循下面的类型声明：一个已经声明过的变量类型一个数据库中的表的字段定义通过%TYPE类型声明新变量的类型,实际上就是将参照的变量或表中的字段类型作为新变量的类型,新变量的类型与它所参照的类型完全相同,并且保持同步l可以作为%TYPE的前缀的可以是数据库表和列前面声明的变量名称lPL/SQL在运行程序时确定变量的数据类型和大小2024/9/6 周五*%TYPE属性：例子l使用%TYPE 属性的好处：l可能不知道数据库中字段的数据类型l数据库中字段的数据类型可以在运行时已被改变l和前面声明过的变量的类型保持一致.v_last_names_emp.last_name%TYPE;v_first_name s_emp.first_name%TYPE;v_balanceNUMBER(7,2);v_minimum_balancev_balance%TYPE:=10;.2024/9/6 周五*%ROWTYPE属性l与%TYPE作用类似，用于定义不确定的类型l变量类型将定义为由数据库的表的字段集合构成的RECORD类型l%ROWTYPE的前缀是数据库的表名，或者另一个已经定义好的RECORD变量lRECORD中的域，与表的字段的名称,个数,数据类型,以及长度完全相同,可以用来存放从表中取出的一条记录的所有的字段值(select*from 表)2024/9/6 周五*%ROWTYPE 属性：优点l数据库中表字段的数据类型和数目可能不知道l数据库中表字段的个数和数据类型会在运行中改变.dept_records_dept%ROWTYPE;emp_records_emp%ROWTYPE;.2024/9/6 周五*PL/SQL 块语法规则l语句可以写在多行,就象SQL语句一样l各个关键字,字段名称等,通过空格分隔l每条语句必须通过分号结束,包括PL/SQL结束部分的END关键字后面也需要加分号l标识符的规定：最多可以包含30个字符不能包含保留字，若有使用双引号括起来必须以字母字符开始不能与数据库的表或者列名称相同 2024/9/6 周五*PL/SQL 语法规则l在在PL/SQL程序中出现的字符和日期必须用单引号括起来程序中出现的字符和日期必须用单引号括起来l数字可以是简单值或科学计数法表示l V_SAL NUMBER(4):=2000;l V_YEAR NUMBER(4):=V_SAL *12;l在PL/SQL中也要养成添加注释的习惯,注释可以是/*和*/之间的多行注释单行注释，以-开始2024/9/6 周五*赋值语句使用使用“:=”:=”作为赋值符号，而不是作为赋值符号，而不是“=”=”语法语法:identifier:=expr;plsql_table_name(primary_key_value):=expr;plsql_record_name.field_name:=expr;2024/9/6 周五*变量赋值设置保存薪水的变量v_salv_sal:=5000;:=5000;v_married :=true;v_married :=true;在PL/SQL TABLE的第一个值中保存名字“Smith /*前面定义好的table型的变量 TYPE name_table_type IS TABLE OF VARCHAR2(16)INDEX BY BINARY_INTEGER;v1_namename_table_type;v2_namename_table_type;*/v1_name(1):=Smith;v1_name(1):=Smith;把一个员工的基本信息保存到PL/SQL RECORD中/*/*前面定义好的RECORD型的变量 TYPE emp_record_type IS RECORD (last_nameVARCHAR2(25),first_nameVARCHAR2(25),salNUMBER(8);emp_record emp_record_type;*/*/emp_record.last_nameemp_record.last_name:=Smith;:=Smith;emp_record.first_nameemp_record.first_name:=zhang;:=zhang;emp_record.salemp_record.sal:=5000;:=5000;2024/9/6 周五*嵌套块和变量作用域lPL/SQL程序也叫做PL/SQL程序块,在程序块中可以嵌套另一个程序块,外部的程序块叫做“父块父块”或或“外部块外部块”,嵌套的块叫“子块子块”或或“嵌套块嵌套块”l只要是允许执行语句的地方，就可以使用嵌套语句l嵌套块也被当作一个语句l异常部分也可以包含嵌套块，嵌套块也可以包括异常部分l对象的作用域指的是可以应用对象的程序范围l标识符可见的范围：子块可以向上查看父块的标识符父块不能向下查看到子块的标识符2024/9/6 周五*嵌套块和变量作用域lDECLAREl x BINARY_INTEGER;lBEGINl .l DECLAREl y NUMBER;l BEGINl .l END;l .lEND;2024/9/6 周五*嵌套块中的变量赋值set serveroutput on;declare v_weight number(3):=100;begin -嵌套块-declare v_weight number(3):=1;begin v_weight:=v_weight+1;dbms_output.put_line(在嵌套块中v_weight的值是:|v_weight);end;-v_weight:=v_weight+1;dbms_output.put_line(在嵌套块外v_weight的值是:|v_weight);end;/2024/9/6 周五*PL/SQL语句中的操作符l逻辑操作符l数学操作符l连接操作符l指数操作符2024/9/6 周五*PL/SQL中的操作符l设置Boolean 标志的值lv_equal:=(v_1=v_2);l为循环增加计数器的值lv_count:=v_count+1;2024/9/6 周五*l set serveroutput on;declare v_1 number(3):=100;v_2 number(3):=100;v_valid boolean;begin v_valid:=(v_1=v_2);if(v_valid)then dbms_output.put_line(真);else dbms_output.put_line(假);end if;end;/2024/9/6 周五*PL/SQL中的函数l可用：数字函数字符函数类型转换函数日期函数l不可用：组函数2024/9/6 周五*PL/SQL中的函数l计算姓名字符数：lset serveroutput on;declare v_name varchar2(20):=aaa;v_addr varchar2(20):=北京;v_count number(3);begin v_count:=length(v_addr);dbms_output.put_line(v_count);end;/2024/9/6 周五*数据类型转换l把数据转换为可操作的数据类型l转换函数：TO_CHARTO_DATETO_NUMBER2024/9/6 周五*数据类型转换l set serveroutput on;declare v_1 varchar2(20);begin -v_1:=USER|:|TO_CHAR(SYSDATE);v_1:=USER|:|SYSDATE;dbms_output.put_line(v_1);end;/2024/9/6 周五*PL/SQL中的SQL命令l在PL/SQL程序块中可以使用各种SQL命令，但是使用的方法根据命令不同也各不相同使用SELECT 命令，可以从数据库中取出单行数据,语法略有变化使用DML命令，修改数据库中的行,没有变化使用COMMIT 或ROLLBACK 命令控制事务,没有变化通过EXECUTE IMMEDIATE，执行DDL和DCL语句2024/9/6 周五*查询数据l在PL/SQL中通过SELECT从数据库中检索数据：SELECTSELECTselect_listselect_listINTOINTO ariable_nameariable_name|record_namerecord_name FROMFROMtabletableWHEREWHEREconditioncondition;l必须使用INTO子句,用于将查询出的数据传递给变量l查询必须并且只能返回一行l可以使用完整的SELECT 语法2024/9/6 周五*查询数据l取出某个员工的雇佣时间和所属部门drop table employees;create table employees(empl_id number(5)primary key,hire_date date,department_id number(5);insert into employees values(1000,1-2月-1999,2000);insert into employees values(1001,2-2月-1999,2000);insert into employees values(1002,3-2月-1999,2000);insert into employees values(1003,4-2月-1999,2000);commit;set serveroutput on;declare v_hire_date employees.hire_date%type;v_department_id employees.department_id%type;begin select hire_date,department_id into v_hire_date,v_department_id from employees where empl_id=1000;dbms_output.put_line(v_hire_date|-|v_department_id);end;/2024/9/6 周五*查询数据l返回员工的平均薪水drop table employees;create table employees(empl_id number(5)primary key,hire_date date,salary number(8,2),department_id number(5);insert into employees values(1000,1-2月-1999,2000,2000);insert into employees values(1001,2-2月-1999,5000,2000);insert into employees values(1002,3-2月-1999,8900,2000);insert into employees values(1003,4-2月-1999,6700,2000);commit;set serveroutput on;declare v_avg_salary employees.salary%type;begin select avg(salary)into v_avg_salary from employees;dbms_output.put_line(平均工资是|:|v_avg_salary);end;/2024/9/6 周五*查询异常lPL/SQL 中的SELECT 语句必须返回且只返回一行l如果检索到了零行或多于一行，将会引起异常lSELECT 常见的异常：TOO_MANY_ROWSNO_DATA_FOUND2024/9/6 周五*TOO_MANY_ROWS异常l100号部门的员工名单CreateCreate OrOr ReplaceReplace PROCEDUREPROCEDURE my_empISIS v_emp_nameemployees.last_name%TYPETYPE;BEGINBEGINSELECTSELECTlast_name INTOINTOv_emp_name FROMFROMemployees WHEREWHEREdepartments_id=100;-TOO_MANY_ROWSENDEND;2024/9/6 周五*NO_DATA_FOUND 异常l15号部门的员工名单PROCEDURE my_empIS v_emp_nameemployees.last_name%TYPE;BEGINSELECT last_name INTOv_emp_name FROMemployees WHEREdepartments_id=15;.-NO_DATA_FOUNDEND;2024/9/6 周五*操作数据l通过使用DML 命令，修改数据库中表的数据INSERTUPDATEDELETE2024/9/6 周五*插入数据l增加一个新的部门：PROCEDURE dept_addISBEGIN INSERT INTO departments VALUES (500,products,100,1007);END;2024/9/6 周五*更新数据l修改某个部门的管理者编号：PROCEDURE new_mgr (v_mgrdepartments.manager_id%TYPE,v_deptdepartments.department_id%type)ISBEGIN UPDATEdepartments SET manager_id=v_mgr WHEREdepartment_id=v_dept;END;2024/9/6 周五*删除数据l删除指定的部门PROCEDURE del_dept (v_dept_id departments.department_id%TYPE)ISBEGIN DELETE departments WHERE department_id=v_dept_id;END;