oracle第七讲plsql剖析.ppt

Oracle数据库管理系统第七讲PL/SQL基础,第七讲pl/sql基础,课程目标,SQL与PL/SQL,PL/SQL内置包,1,2,3,PL/SQL块结构和组成元素,4,第七讲pl/sql基础,一、概述,Oracle5以前是没有PL/SQL这个产品的。后来由于数据库的发展和软件技术的发展。出现了第4代开发工具，称为4GL。4GL的功能是在处理界面上有优势，但在数据库的数据处理上仍不是它们的特长。鉴于数据库在SQL方面的优势，Oracle公司在Oracle6版本以后开发了PL/SQL产品。Oracle在后来的版本中增强了PL/SQL的功能。包括现在的许多系统的处理几乎是由PL/SQL写成，而不是用C来编写。,第七讲pl/sql基础,一、概述,1什么是PL/SQL2PL/SQL的好处3PL/SQL可用的SQL语句,第七讲pl/sql基础,1.什么是PL/SQL,PL/SQL是ProcedureLanguage-年月.begin.end;,第七讲pl/sql基础,三、运行PL/SQL程序,在SQL*PLUS下运行可以用下面命令启动运行：SQLexecutefunstat1(2001.01,2001.02);类似地，在Powerbuilder和Developer/2000下可以用下面语句来启动运行：EXECUTEfundstat1(2001.01,2001.03);或date1:=2001.01;date2:=2001.03;EXECUTEfundstat1(:date1,:date2);,第七讲pl/sql基础,四、PL/SQL内置包,Oracle系统本身提供了一套功能强大的内置包。这些系统包都以DBMS_开头进行命名。用这些包可以完成许多Oracle管理功能。下面是一些常用包的简单说明：DBMS_ALERT数据库报警，允许会话间通信；DBMS_JOB任务调度服务；DBMS_LOB大对象操作用的包；DBMS_PIPE数据库管道用的包；DBMS_SQL动态SQL所用的包；UTL_FILE文本文件I/O所用的包。,第七讲pl/sql基础,五、PL/SQL块结构和组成元素,PL/SQL结构PL/SQL块标识符PL/SQL变量类型运算符和表达式变量赋值变量作用范围以可见性注释简单例子,第七讲pl/sql基础,五、PL/SQL块结构和组成元素,PL/SQL结构,PL/SQL块中可以包含子块；子块可以位于PL/SQL中的任何部分；子块也可以是PL/SQL中的一条命令；已定义的对象有一定的作用范围。,第七讲pl/sql基础,五、PL/SQL块结构和组成元素,Declare/*本部分可以定义变量、光标等。*/v_namevarchar2(10);v_sqlnumber(7,2);Begin/*执行部分*/.selectenameintov_namefromscott.empwhereempno=7777;begin/*子块开始，也是执行语句*/.EXCEPTION/*执行异常处理部分*/.end;EXCEPTION/*执行异常部分*/.end;/*本PL/SQL程序结束*/,第七讲pl/sql基础,五、PL/SQL块结构和组成元素,PL/SQL块,PL/SQL程序组成:声明部分、执行部分、异常处理部分。PL/SQL块的结构如下：Declare/*声明部分:在此声明PL/SQL用到的变量,类型及光标*/begin/*执行部分:过程及SQL语句,即程序的主要部分*/Exception/*执行异常部分:错误处理*/End;,第七讲pl/sql基础,五、PL/SQL块结构和组成元素,标识符,标识符名不能超过30字符；第一个字符必须为字母,可包含字母，数字，$，_，和#；不分大小写；不能是SQL保留字。,提示:一般不要把变量名声明与表中字段名完全一样,如果这样可能得到不正确的结果.,第七讲pl/sql基础,五、PL/SQL块结构和组成元素,例如：下面的例子将会删除所有的记录，而不是KING的记录DECLAREenamevarchar2(20):=KING;BEGINDELETEFROMempWHEREename=ename;END;,第七讲pl/sql基础,五、PL/SQL块结构和组成元素,下面是建议的命名方法：,第七讲pl/sql基础,五、PL/SQL块结构和组成元素,变量类型,例:declareorder_nonumber(3);cust_namevarchar2(20);order_datedate;emp_nointeger:=25;piconstantnumber:=3.14159;beginnull;end;,第七讲pl/sql基础,五、PL/SQL块结构和组成元素,变量类型,1.记录类型:记录类型用于表示逻辑相关的一组信息,这些信息按某种结构组织在一起。定义记录类型语法如下:TYPErecord_typeISRECORD(Field1type1NOTNULL:=exp1,Field2type2NOTNULL:=exp2,.FieldntypenNOTNULL:=expn);,第七讲pl/sql基础,五、PL/SQL块结构和组成元素,DECLARETYPEmy_recISRECORD(idnumber(3),namevarchar2(20);teachert_rec1;BEGINteacher.id:=10;=Jack;dbms_output.put_line(teacher.id|:|);END;,第七讲pl/sql基础,五、PL/SQL块结构和组成元素,可以用select语句对记录变量进行赋值,只要保证记录字段与查询结果列表中的字段相配即可。DECLARETYPEt_studentrecordISRECORD(Firstnamesutdents.first_name%TYPE,Lastnamesutdents.last_name%TYPE,Majorsutdents.major%TYPE);v_stduentt_StudentRecord;BeginSelectfirst_name,last_name,majorIntov_studentFromstudentsWhereid=10000;End;,第七讲pl/sql基础,五、PL/SQL块结构和组成元素,使用%ROWTYPE,PL/SQL可以基于已定义的记录类型变量或数据库中已存在的表来定义与其结构相同的记录类型变量，格式如下：（1）新变量名已定义的记录类型变量%ROWTYPE（2）新变量名表名%ROWTYPE,第七讲pl/sql基础,五、PL/SQL块结构和组成元素,表类型（相当于数组）DECLARETYPEmy_table_typeisTABLEOFvarchar(20)INDEXBYBINARY_INTEGER;my_tablemy_table_type;Beginmy_table(1):=百川东到海；my_table(3):=何时复西归；dbms_output.put_line(my_table(1);dbms_output.put_line(my_table(3);end;,第七讲pl/sql基础,五、PL/SQL块结构和组成元素,用户定义的子类型,SUBTYPEsubtype_nameISbase_type(constraint)NOTNULL;SUBTYPEcharacterISCHAR;SUBTYPEintegerISNUMBER(38,0);,第七讲pl/sql基础,五、PL/SQL块结构和组成元素,用户定义的子类型,使用子类型一旦定义子类型，就可以在声明的地方使用子类型。例：DECLARESUBTYPEAccumulatorISNUMBER;totalAccumulator(7,2);,第七讲pl/sql基础,五、PL/SQL块结构和组成元素,用户定义的子类型？,DECLARESUBTYPENumeralISNUMBER(1,0);x_axisNumeral;y_axisNumeral;BEGINx_axis:=10;.END;,第七讲pl/sql基础,五、PL/SQL块结构和组成元素,运算符和表达式（关系运算符）,第七讲pl/sql基础,五、PL/SQL块结构和组成元素,运算符和表达式（一般运算符）,一般运算符有：+加号-减号*乘号/除号:=赋值号=关系号.范围运算符|字符连接符,第七讲pl/sql基础,五、PL/SQL块结构和组成元素,运算符和表达式（逻辑运算符）,逻辑运算符有：isnull是空值Between介于两者之间In在一列值中间And逻辑与Or逻辑或Not取反,如isnotnull,notin,第七讲pl/sql基础,五、PL/SQL块结构和组成元素,字符及数字运算特点,空值加数字仍是空值：null+=null空值加（连接）字符，结果为字符：null|=,第七讲pl/sql基础,五、PL/SQL块结构和组成元素,字符及数字运算特点,Boolean赋值：布尔值有TRUE,FALSE及NULL三个值DECLAREdoneBOOLEAN;BEGINdone:=FALSE;WHILENOTdoneLOOP.ENDLOOP;END;,第七讲pl/sql基础,五、PL/SQL块结构和组成元素,数据库赋值,数据库赋值是通过select语句来完成的。每执行select语句一次只能赋值一次。一般要求被赋值的变量与select中的列名要一一对应。,第七讲pl/sql基础,五、PL/SQL块结构和组成元素,DECLAREemp_idemp.empno%TYPE;emp_nameemp.ename%TYPE;wagesNUMBER(7,2);BEGIN.SELECTename,sal+commINTOemp_name,wagesFROMempWHEREempno=emp_id;.END;,第七讲pl/sql基础,五、PL/SQL块结构和组成元素,可转换的类型赋值,1.CHAR转换为NUMBER使用TO_NUMBER函数来完成字符到数字的转换，如：v_total:=to_number(100.0)+sal;2.NUMBER转换为CHAR使用TO_CHAR函数可以实现数字到字符的转换，如：v_comm:=to_char(123.45)|元;,第七讲pl/sql基础,五、PL/SQL块结构和组成元素,可转换的类型赋值,3.字符转换为日期使用TO_DATE函数可以实现字符到日期的转换，如：v_date:=to_date(2001.07.03,yyyy.mm.dd);4.日期转换为字符使用TO_CHAR函数可以实现日期到字符的转换，如：v_to_day:=to_char(sysdate,yyyy.mm.ddhh24:mi:ss);,第七讲pl/sql基础,五、PL/SQL块结构和组成元素,可转换的类型赋值,SQLselectto_number(133)+200fromdual;TO_NUMBER(133)+200-333SQLselectto_char(321)|元fromdual;TO_CHAR-321元,第七讲pl/sql基础,五、PL/SQL块结构和组成元素,变量作用范围以及可见性,变量的作用范围是在你所引用的程序单元（块、子程序、包）内。即从声明变量开始到该块的结束。一个变量（标识）只能在你所引用的程序单元内是可见的。当一个变量超出了作用范围，PL/SQL引擎就释放用来存放该变量的空间（因为它可能不用了）。在子块中重新定义该变量后，它的作用仅在该块内。,第七讲pl/sql基础,五、PL/SQL块结构和组成元素,注释,1.使用双-(减号)加注释PL/SQL允许用双-(双减号)来写注释，它的作用范围是只能在1行有效。如：V_Salnumber(12,2);-工资变量。2.使用/*/来加一行或多行注释,第七讲pl/sql基础,五、PL/SQL块结构和组成元素,例1:简单的数据录入declarev_enamevarchar2(20):=赵杰;v_salnumber(7,2):=1234.56;v_deptnonumber(2):=10;v_empnonumber(4):=8888;begininsertintoemp(empno,ename,JOB,sal,dep