Oracle9i第6章PLSQL语言.ppt

第五章 PL/SQL语言,SQL是一种结构化查询语言，它虽然功能强大，但对于有些复杂的带有过程化的操作，就不能用简单的SQL语句来描述。Oracle公司对SQL作了扩展，建立了模块式的过程化SQL，即PL/SQL。SQL是一种描述性的语言，一次只能处理一条语句，每次执行都导致客户向服务器调用，从而在性能上产生很大的开销，尤其是在网络操作中。使用PL/SQL，一个块中的语句作为一个组，一次调用，减少网络传输。,PL/SQL是Procedure Language&Structured Query Language的缩写。Oracle的SQL是支持ANSI(American national Standards Institute)和ISO92(International Standards Organization)标准的产品。目前的PL/SQL包括两部分，一部分是数据库引擎部分；另一部分是可嵌入到许多产品(如C语言，JAVA语言等)工具中的独立引擎。可以将这两部分称为：数据库PL/SQL和工具PL/SQL。两者的编程非常相似。都具有编程结构、语法和逻辑机制。工具PL/SQL另外还增加了用于支持工具(如Oracle Forms)的句法，如：在窗体上设置按钮等。,PL/SQL是一种块结构的语言，组成PL/SQL程序的单元是逻辑块，一个PL/SQL程序包含了一个或多个逻辑块，每个块都可以划分为三个部分：声明部份、可执行部份以及例外处理部份。,声明部分(Declaration section)声明部分包含了变量、常量、游标和用户自定义的异常处理。这个部分是由关键字DECLARE开始，如果不需要声明变量、常量、游标和用户自定义的例外处理，那么可以忽略这一部分。执行部分(Executable section)执行部分是PL/SQL块中的指令部分，包括一些对数据库操作的SQL语句，以及对块中语句进行组织、控制的PL/SQL语句。由关键字BEGIN开始，所有的可执行语句都放在这一部分，其他的PL/SQL块也可以放在这一部分。异常处理部分(Exception section)对可执行部份中的语句，在执行过程中出错或出现非正常现象时所做的相应处理。在PL/SQL中，这一部分是可选的。,PL/SQL块语法结构如下：DECLAREdeclaration statementsBEGINexecutable statementsEXCEPTIONexception statementsEND,PL/SQL块中的每一条语句都必须以分号结束，SQL语句可以多行，但分号表示该语句的结束。一行中可以有多条SQL语句，他们之间以分号分隔。每一个PL/SQL块由BEGIN或DECLARE开始，以END结束。注释由“-”标示。declarepcon_value constant number:=211;begininsert into emp(empno,ename,job)values(pcon_value,Wang,Xuan);end;/,declare v_ename varchar2(20):=赵元杰;v_sal number(7,2):=1234.56;v_deptno number(2):=10;v_empno number(4):=8888;begininsert into emp(empno,ename,JOB,sal,deptno,hiredate)values(v_empno,v_ename,自由职业,v_sal,v_deptno,to_date(,);commit;end;/show error,declare v_ename varchar2(20):=赵元杰;v_empno number(4):=8888;begindelete from emp where empno=v_empno and ename=v_ename;commit;end;/show error,标识符,PL/SQL程序设计中的标识符定义与SQL的标识符定义的要求相同。要求和限制有：标识符名不能超过30字符；第一个字符必须为字母；不分大小写；不能用-(减号);不能是SQL保留字。,合法的标识符：declare v_name varchar2(20);/*存放name 列的值*/v_sal number(9,2);/*存放 sal 列的值*/,不合法的标识符：declare v-name varchar2(20);/*存放name 列的值*/2001_sal number(9,2);/*存放 sal 列的值*/mine-非法的标识符(不能用空格)提示：一般不要把变量名声明与表中字段名完全一样，如果这样可能得到不正确的结果。,PL/SQL块在数据库中的使用,由基本的PL/SQL块组成的PL/SQL程序，可以组成不同的程序形式。程序形式大致有以下几种：无名块就是没有命名的PL/SQL块，它可以是一个嵌入某一个应用之中的一个PL/SQL块。无名块在所有的PL/SQL环境中都适用。存储过程/函数指命名了的PL/SQL块，它可以接收参数，并且可以重复地被调用。,包指由一组相关的过程、函数和标识符组成的命名了的PL/SQL模块。触发器触发器是存储在数据库中的PL/SQL块。并且在数据库对表发出数据库操作行为(update、insert和delete)时，系统就自动执行相应的数据库触发器。,常量与变量,在PL/SQL块中引用的所有标识符都必需在PL/SQL块的定义部分声明。常量和变量类似，但是常量的值在定义时已经被指定，不能被改变。它的声明方式和变量一样，但必须指定关键字CONSTANT。变量与常量的声明语法如下：Variable_name CONSTANT databyte NOT NULL:=|DEFAULT expressionemp_id varchar2(10);m number(6);CON_VALUE CONSTANT NUMBER:=40；,变量的属性,(1)%TYPE属性提供了变量和数据库列的数据类型。在表xs中包含xh列，声明一个变量my_xh与xh列具有相同的数据类型。my_xh xs.xh%type;优点：不必知道xh列的确切数据类型。xh列的数据类型的改变，my_xh列的数据类型自动改变。,(2)%ROWTYPE属性声明表的行数据记录。声明一个记录名为cj_rec，与xs_kc表具有相同的名称和数据类型。DECLAREcj_rec XS_KC%ROWTYPE;可以使用点引用域。my_xh:=cj_rec.xh;,如果声明了游标提取xh、kch、cj和xf列，则可以使用%ROWTYPE声明一个记录存储相同的信息。DECLARECURSOR c1ISSELECT XH,KCH,CJFROM XS_KC;cj_rec c1%ROWTYPE;当执行FETCH c1 INTO cj_rec;语句时，在xs_kc表的xh列的值就赋予cj_rec的xh域。,赋值,给变量赋值可用直接赋值法和间接赋值法。直接赋值是指将一个具体的值或表达式通过“:=”运算符号赋予变量间接赋值是指使用SELECT INTO或FETCH INTO来将查询结果赋予变量select ename into emp_name from emp where empno=211;,作用域,作用域是指引用变量的程序单元部分，可以是包或者子程序包等。一个变量只有在其作用域中，被引用时才是可见的。对于PL/SQL来说，作用域就是从变量声明的开始一直到这个PL/SQL块的结束。,set serverout onSQL declare 2 pEname varchar2(30):=Parent pEname;3 pNo varchar2(30):=Parent pNo;4 begin 5 declare 6 pEname varchar2(30);7 begin 8 pEname:=Child pEname;9 pNo:=Child PL/SQL;10 dbms_output.put_line(pEname);11 end;12 dbms_output.put_line(pEname);13 dbms_output.put_line(pNo);14 end;15/,用户自定义数据类型,利用命令定义数据类型,语法：TYPE typenameIS system_data_type例：TYPE xs_rec IS RECORD(xh number(2)NOT NULL:=1,xm char(8);,复合类型(记录和表),1.记录类型定义记录类型语法如下:TYPE record_type IS RECORD(Field1 type1 NOT NULL:=exp1,Field2 type2 NOT NULL:=exp2,.Fieldn typen NOT NULL:=expn);,DECLARE TYPE t_rec1 is record(Field1 number,Field2 varchar2(5);TYPE t_rec2 IS RECORD(Field1 number,Field2 varchar2(5);V_Rec1 t_rec1 type;V_rec2 t_rec2 type;Beginv_rec1:=v_rec2;v_rec1.field1:=v_rec2.field1;v_rec1.field2:=v_rec2.field2;end;,数据类型转换,CHAR转换为NUMBERT使用TO_NUMBER函数来完成字符到数字的转换，如：v_total:=to_number(100.0)+sal;NUMBERT转换为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.dd hh24:mi:ss);,可以在SQL下输入下面命令来验证以上的语句：select to_char(sysdate,yyyy)|年|to_char(sysdate,mm)|月|to_char(sysdate,dd)|日 from dual;SQL alter session set nls_date_format=yyyy年mm月dd日;SQL select sysdate from dual;SQL select to_number(133)+200 from dual;SQL select to_char(321)|元 from dual;SQL select to_date(2001.07.03,yyyy.mm.dd)from dual;,注释,在PL/SQL里，可以使用两种符号来写注释，即：使用双-(减号)加注释PL/SQL允许用双-(双减号)来写注释，它的作用范围是只能在1行有效。使用/*/来加一行或多行注释使用/*.*/在PL/SQL程序中加，可以对多行进行注释。,PL/SQL运算符,一般运算符用来进行加、减、乘、除等算术运算以及赋值、范围运算和字符连接等操作。,比较运算符用于将一个表达式和另外一个表达式进行比较，其结果总是TRUE、FALSE和NULL三种。,逻辑运算符就只有3个，分别是AND、OR和NOT。其中AND和OR是二元运算符(即需要两个操作数进行运算的)，NOT是一元运算符。,条件语句IFTHEN,表达式一IFTHENPL/SQL和SQL语句END IF；表达式二IFTHENPL/SQL和SQL语句ELSEPL/SQL和SQL语句END IF；,表达式三IFTHENPL/SQL和SQL语句ELSIFTHENPL/SQL和SQL语句ELSEPL/SQL和SQL语句END IF；,IFTHENPL/SQL和SQL语句IFTHENPL/SQL和SQL语句END IF；END IF；,Set serverout onDECLARE V_numberseats rooms.number_seats%TYPE;V_comment varchar2(35);Begin Select number_seats into v_numberseats From rooms where room_id=99999;IF v_numberseats50 then V_comment:=Fairly small;Elsif v_numberseats 100 then V_comment:=A little bigger;Else V_comment:=Lots of room;End if;dbms_output.put_line(v_comment);End;,查询总学分大于40的学生人数。DECLAREv_num number(3);BEGINSELECT COUNT(*)INTO v_numFROM xsWHERE zxf40;IF v_num0 THENdbms_output.put_line(总学分40的人数为：|TO_CHAR(v_num);END IF;END;,判断计算机系总学分大于40的人数是否超过80人。DECLAREv_num number(3);BEGINSELECT COUNT(*)INTO v_numFROM xsWHERE zxf40 AND zym=计算机;IF v_num0 THENIF v_num80 THENdbms_output.put_line(计算机系总学分40的人数超过80人);END IF;END;,如果“数据库原理”课程的平均成绩高于75分，则显示“平均成绩大于75”，否则显示“平均成绩小于75”。Declarev_avg number(4,2);Beginselect avg(cj)into v_avgfrom xs,xs_kc,kcwhere xs.xh=xs_kc.xh and xs_kc.kch=kc.kch andkc.kcm=数据库原理;If v_avg75 thendbms_output.put_line(平均成绩大于75);elsedbms_output.put_line(平均成绩小于75);End if;END;,LOOP循环,LOOPstatementsEND LOOP;,set serverout ondeclarex number:=0;beginloopx:=x+1;if x=100 thenexit;end if;end loop;dbms_output.put_line(x);end;,set serverout ondeclare x number;begin x:=0;loop x:=x+1;dbms_output.put_line(to_char(x);exit when x=10;end loop;end;,DECLARE V_counter INTEGER:=1;Begin LOOP insert into temp_table values(v_counter,loop index);v_counter:=v_counter+1;if v_counter 50 then Exit;End if;End loop;End;,DECLARE v_counter number:=1;Begin loop insert into temp_table values(v_counter,loop index);v_counter:=v_counter+1;Exit when v_counter 50;End loop;End;,declare x number;begin x:=0;loop x:=x+1;dbms_output.put_line(to_char(x);exit when x=10;end loop;end;,DECLARE V_counter BINARY_INTEGER:=1;Begin LOOP Insert into temp_table Values(v_counter,loop index);V_counter:=v_counter+1;If v_counter 50 then Exit;End if;End loop;End;,DECLARE V_counter binary_index:=1;Begin Loop Insert into temp_table Values(v_counter,loop index);Exit when v_counter 50;End loop;End;,WHILE循环,While loop 要执行的语句;end loop;,WHILE循环,set serverout ondeclarex number:=0;beginwhile x=100 loopx:=x+1;end loop;dbms_output.put_line(x);end;,declare x number;begin x:=1;while x10 loop dbms_output.put_line(to_char(x)|还小于10);x:=x+1;end loop;end;,DECLARE V_counter integer:=1;Begin While v_counter=50 loop insert into temp_table Values(v_counter,loop index);V_counter:=v_counter+1;End loop;End;,FOR循环,For 循环计数器 in 下限.上限 loop 要执行的语句;end loop;,FOR循环,FOR counter IN REVERSE start_range.end_range LOOPstatements;END LOOP;FOR循环的循环次数是固定的，counter是一个隐式声明的变量，初始值是start_range，第二个值是start_range+1，直到end_range，如果start_range等于end _range，那么循环将执行一次。如果使用了REVERSE关键字，那么范围将是一个降序。,set serverout ondeclarelow number:=4;high number:=6;x number;beginfor n in low.high loopx:=n;dbms_output.put_line(x);end loop;end;,begin for I in 1.10 loop dbms_output.put_line(in=|to_char(I);end loop;end;,DECLARE V_counter number:=7;Begin insert into temp_table(num_col)Values(v_counter);For v_counter IN 20.30 loop Insert into temp_table(num_col)Values(v_counter);End loop;insert into temp_table(num_col)Values(v_counter);End;,标号和GOTO,PL/SQL中GOTO语句是无条件跳转到指定的标号去的意思。语法如下：GOTO label;.,DECLARE V_counter number:=1;Begin Loop insert into temp_table Values(v_counter,loop count);V_counter:=v_counter+1;If v_counter 50 then Goto l_endofloop;End if;End loop;insert into temp_table(char_col)values(Done!);End;,游标,PL/SQL用游标管理select语句。游标是为处理这些语句而分配的一大块内存。游标提供了对一个结果集进行逐行处理的能力。它是一种特殊的指针，与某个查询结果相联系，可以指向结果集的任意位置。使用游标可以在查询数据的同时对数据进行处理。,游标分显式游标和隐式游标。对显式游标的使用要遵循声明游标-打开游标-读取数据-关闭游标的顺序。使用隐式游标，用户不必执行以上步骤，只要简单的编码select语句并让PL/SQL根据需要处理游标即可。,游标,定义游标就是定义一个游标名，以及与其相对应的SELECT语句。游标是在PL/SQL块的定义部份定义的，语法格式为：declarecursor cursor_name is select_statatement;,declarecursor xs_cur1isselect xh,xm,cjfrom xswhere zym=计算机;,在定义了游标之后，对所引用的输入变量进行赋值，然后就可以打开游标了。打开游标就是执行游标所对应的SELECT语句，将其查询结果放入工作区，并用指针指向工作区的首部。语法格式为：open cursor_name;打开游标后，可使用%ROWCOUNT查看游标中数据行的数目。,定义游标xs_cur2，打开游标，输出其行数。declare cursor xs_cur2isselect xh,xm,zxffrom xs;beginopen xs_cur2;dbms_output.put_line(xs_cur2%rowcount);end;0,set serveroutput ondeclare name varchar2(20);cursor cur_aaisselect*from aa;beginopen cur_aa;fetch cur_aa into name;dbms_output.put_line(cur_aa%rowcount);end;1,取值工作是将游标中的数据取出一行，放入指定的输出变量中。对游标第一次执行FETCH语句时，首先将指针从工作区的首部移动到工作区的第一数据，使第一行成为当前行，然后将该行数据送到输出变量。系统在每执行一次FETCH语句时，都自动将指针顺序下移一行，使下一行成为当前行。通过反复执行FETCH语句，就可以逐行地输出工作区中的全部数据。语法格式为：fetch cursor_name into variables;,Declarev_xh char(6);v_zxf number(2);cursor xs_cur3isselect xh,zxffrom xswhere zym=计算机;Beginopen xs_cur3;fetch xs_cur3 into v_xh,v_zxf;,while xs_cur3%foundloopdbms_output.put_line(v_xh+v_zxf);fetch xs_cur3 into v_xh,v_zxf;end loop;close xs_cur3;End;,完成SELECT语句的处理之后，就可以关闭游标。游标关闭后，系统释放与该游标相关的资源，并使该游标的工作区变成无效，不能再使用FETCH语句取其中的数据。如果用FETCH语句取一个关闭的游标中的数据则会出错。关闭后的游标可以使用OPEN语句重新打开。关闭游标使用CLOSE语句，其语法格式为；close cursor_name;,在使用显式游标时，必须编写4部分代码：(1)在PL/SQL块的declare段中定义游标。(2)在PL/SQL块中初始begin后打开游标。(3)取游标到一个或多个变量中，在接收游标的fetch语句中，接收变量的数目必须与游标的select列表中的表列数目一致(数据类型也要一致)。(4)使用完后要关闭游标。,每个显示游标有4个属性：%FOUND、%NOTFOUND、%ROWCOUNT和%ISOPEN。这些属性附在游标名后面使用。,属性%FOUND如果一个游标未经打开，而使用该游标的属性%FOUND，则产生预定义例外INVALID_CURSOR；在一个游标打开之后，执行FETCH操作之前，其%FOUND属性取值为NULL；如果FETCH操作取得了一行，则其属性%FOUND取值为TRUE；如果FETCH操作执行成功，但未取到数据(原因是活动集合为空或虽不为空但已经取完)，则其属性%FOUND取值为FALSE。,属性%NOTFOUND如果一个游标未经打开，而使用该游标的属性%NOTFOUND，则产生预定义例外INVALID_CURSOR；在一个游标打开之后，挂靠FETCH操作之前，其%NOTFOUND取值为FALSE；如果FETCH操作执行成功，但未取到数据(原因是活动集合为空或虽不为空但已经取完)，则其属性%NOTFOUND取值为TRUE。FETCH操作如果失败，不产生例外，也不改变游标属性%NOTFOUND的值。,在数据库在修改一个学生的记录，若没有该学生的记录，则为该学生创建一个新的记录。DECLARExm varchar2(8):=张琼;zym varchar2(10):=计算机;zxf number(2):=45;BEGINUPDATE XSSET zxf=zxfWHERE xm=xm;IF SQL%NOTFOUND THENINSERT INTO XS(XH,XM,ZYM,ZXF)VALUES(xs_sequence.NEXTVAL,xm,zym,zxf);END IF;END;,属性%ROWCOUNT如果一个游标未经打开，而使用该游标的属性%ROWCOUNT，则产生预定义例外INVALID_CURSOR；每次FETCH操作取得一行数据，%ROWCOUNT的值就增加1，即%ROWCOUNT返回到目前为止所取得的行数。循环执行游标取数操作时，检索出的总数据行存放在相同变量%rowcount中。,属性%ISOPEN如果试图打开一个已打开的游标或关闭一个已关闭的游标，将会出现错误。在使用前，可以用此属性进行检查。如果一个游标已经打开，那么在关闭之前，其属性%ISOPEN取值为TRUE，否则为FALSE。,If mycur%isopen thenfetch mycur into v_kch,v_cj;/*游标已打开，可操作*/Elseopen mycur;/*游标没有打开，先打开游标*/End if;,set serverout onDECLARECURSOR C1 IS SELECT VIEW_NAME FROM ALL_VIEWSWHERE ROWNUM=10;VNAME VARCHAR2(40);BEGINOPEN C1;FETCH C1 INTO VNAME;WHILE C1%FOUND LOOPFETCH C1 INTO VNAME;DBMS_OUTPUT.PUT_LINE(TO_CHAR(C1%ROWCOUNT)|VNAME);END LOOP;CLOSE C1;END;,set serverout onDECLARECURSOR C1 IS SELECT VIEW_NAME FROM ALL_VIEWSWHERE ROWNUM=10;VNAME VARCHAR2(40);BEGINfor c_rec in C1 loopVNAME:=c_rec.view_name;DBMS_OUTPUT.PUT_LINE(TO_CHAR(C1%ROWCOUNT)|VNAME);END LOOP;END;,隐式游标,如果在PL/SQL程序段中使用select语句进行操作，PL/SQL会隐含的处理游标定义，即称作隐式游标。这种游标不需要像显式游标一样需要声明，也不需要打开和关闭。,Create or replace procedure cx_xm(in_xh in char;out_num out char)AsBeginselect xm into out_xm/*隐式游标必须用into*/from xswhere xh=in_xh;dbms_output.put_line(out_num);End cx_xm;,使用隐式游标注意：(1)每一个隐式游标必须有一个into；(2)和显式游标一样，带有into接收数据的变量时数据类型要与列表的一致。(3)隐式游标一次仅能返回一行数据，使用时必须检查异常。最常见的异常有“no_date_found”和“too_many_rows”。,游标的for循环的优点是用户不需要打开游标、取数据、测试数据的存在、关闭游标或定义存放数据的变量。,存在表temp存放需要重修的同学信息。Declarev_xh char(6);v_kch char(3);v_cj number(4,2);cursor kc_curisselect xh,kch,cjfrom xs_kc;Beginopen kc_cur;fetch kc_cur into v_xh,v_kch,v_cj;while kc_cur%found loop,if v_cj60 theninsert into temp values(v_xh,v_kch,v_cj);end if;fetch kc_cur into v_xh,v_kch,v_cj;End loop;Close kc_cur;End;,Declarev_xh char(6);v_kch char(3);v_cj number(4,2);cursor kc_curisselect xh,kch,cjfrom xs_kc;Beginfor kc_cur_rec in kc_cur loopv_xh:=kc_cur_rec.xh;v_kch:=kc_cur_rec.kch;v_cj:=kc_cur_rec.cj;if v_cj60 theninsert into temp values(v_xh,v_kch,v_cj);,end if;End loop;End;,出错处理,在PL/SQL中，将程序执行过程中的一个警告或错误称为一个例外(EXCEPTION)。预定义的Oracle错误Oracle预定义的例外，无需在程序中定义，由Oracle自动地引发。如出现被0除时，就会产生ZERO_DIVIDE异常。用户定义的错误用户定义的错误是程序执行中出现的编程人员认为的非正常情况。对这种例外情况，开发人员需要在程序中定义，而且显示地在程序中将其引发。命令格式为：EXCEPTION_NAMEEXCEPTION;,