PLSQL语法以及程序编写介绍.ppt

PL/SQL语法以及程序编写介绍,一、PL/SQL介绍 PL/SQL是一种高级数据库程序设计语言，该语言专门用于在各种环境下对Oracle数据库进行访问。 PL/SQL语言实现了将过程结构与Oracle SQL的无缝集成，从而为用户提供了一种功能强大的结构化程序设计语言。,二、引入PL/SQL的原因 PL/SQL语言可以将第四代语言的强大功能和灵活性与第三代语言的过程结构的优势融为一体。,三、PL/SQL内容 PL/SQL是在SQL语言中扩充了面向过程语言中使用的程序结构，如： 变量和类型（即可以予定义也可以由用户定义） 控制语句（如IF-THEN-ELSE）和循环 过程和函数 对象类型和方法（PL/SQL8.0版本以上）,四、变量的声明 在PL/SQL中声明变量与其他语言不太一样，它采用从右往左的方式声明 。 C语言声明变量方式：int v_id; PL/SQL声明变量方式：v_id number; 五、变量的赋值 不能用”=”应该用”:=”, 即形式为 v_id := 5;,六、数据类型 PL/SQL主要用于数据库编程，所以其所有数据类型跟oracle数据库里的字段类型是一一对应的，常用的数据类型大体上有数字型、布尔型、字符型和日期型。 数字型 用来存储整型或者浮点型数值。范围为E-130 10E125，其声明方式为：number （precision, scale） 其中（precision, scale）是可选的，precision表示所有数字的个数，scale表示小数点右边数字的个数。,实际数据 定义 在oralce中的数据,7456123.89 NUMBER 7456123.89 NUMBER(9) 7456123.89 NUMBER(9,2) 7456123.89 NUMBER(9,1) 7456123.89 NUMBER(6) 7456123.89 NUMBER(7,-2) 7456123.89 NUMBER(7,2),7456123.89,7456124,7456123.89,7456123.9,溢出,7456100,溢出,布尔型 布尔型BOOLEAN，用于逻辑判断，只有TRUE,FALSE两种值。 字符型 主要说明字符类型的存储格式。主要包括 char、varchar2和long等几种类型。 CHAR：用于描述定长的字符型数据，长度= 2000 字节。如果实际值不够定义的长度，系统 将以空格填充。,v_char char(10):=abcde; Length(v_char)=,10,VARCHAR2：用于描述变长的字符型数据，长度= 4000 字节。它的声明方式如下VARCHAR2(L)，L为字符串长度，没有缺省值。,v_char varchar2(10):=abcde; Length(v_char)=,5,NCHAR：来存储Unicode字符集的定长字符型数据，长度= 1000 字节。它的声明方式与CHAR相同。 NVARCHAR2：用来存储Unicode字符集的变长字符型数据，长度= 1000 字节。它的声明方式与VARCHAR2相同。 LONG：用来存储最大长度为2GB的变长字符数据。,日期型 DATE：用于存储日期与时间信息。虽然日期信息也可以用字符型变量或者数字型变量来表示，但是DATE型变量有它特有的属性。每一个DATE型的数据都存储了以下信息：年、月、日、小时、分钟以秒。,TO_DATE函数 TO_DATE函数可以将一个字符型变量按照程序员的要求转换成日期型变量。,假设有一DATE类型变量v_date，现在用to_date 函数将v_date赋值为2006年4月6日,V_date := to_date(2006-04-06,yyyy-mm-dd);,V_date := to_date(2006-04-06 10:10:10,yyyy-mm-dd hh24:mi:ss);,V_date := to_date(20060406,yyyymmdd);,V_date := to_date(2006/04/06,yyyy/mm/dd);,V_date := to_date(20060406101010,yyyymmddhh24miss);,七、条件逻辑 在PL/SQL中，可以使用命令if,else和elsif来控制可执行命令段的命令流。下面列出了可用的条件逻辑命令的格式（循环条件除外） 格式1： if then elsif then else end if;,格式2： if then if then end if; else end if; 总的说来除了格式上与C略有区别，其它都 是一样的,例：判断通过映射关系，将邮编信息填入到i_addr_zipcode字段中去 if i_area_id = 1 then i_addr_zipcode := 325200; elsif i_area_id =2 then i_addr_zipcode := 325400; elsif i_area_id = 3 then i_addr_zipcode := 325800; elsif i_area_id = 4 then i_addr_zipcode := 325100; elsif i_area_id = 5 then i_addr_zipcode := 325600; elsif i_area_id = 6 then i_addr_zipcode := 325700; elsif i_area_id = 7 then i_addr_zipcode := 325300; elsif i_area_id = 8 then i_addr_zipcode := 325500; else i_addr_zipcode := 325000; end if;,八、循环 在一个单独的PL/SQL代码块中可以使用循环处理多个记录。PL/SQL支持三种类型的循环。,简单循环,一个重复运动的循环，直到碰到循环中的exit或者exit when语句时才结束循环,WHILE循环,一个重复运行的循环，直到满足条件才退出,FOR循环,一个重复运行指定遍数的循环,例：一个简单循环举例 i_variable := 10; i_sum := 0; loop i_sum := i_sum + i_variable; i_variable := i_variable 1; exit when i_variable0; end loop;,例：用while循环来完成上例 i_variable := 10; i_sum := 0; while i_variable 0 loop i_sum := i_sum + i_variable; i_variable := i_variable 1; end loop; ,例：用for循环来完成简单循环 i_variable := 10; i_sum := 0; for i_variable in 010 loop i_sum := i_sum + i_variable; i_variable := i_variable 1; end loop; ,九、游标 当查询返回结果超过一行时，就需要一个显式游标，也就是我们常说的游标。 常用的游标 要在程序中使用游标，大致需要以下几个步骤： 1）定义游标 2）打开游标 3）从游标中提取数据 4）关闭游标,例： i_cust_name varchar2(20); i_cust_id number(13); cursor cur_cust_info is select name,cust_id from cust where sts=A; -a begin open cur_cust_info; -b loop fetch cur_cust_info into i_cust_name,i_cust_id; -c exit when cur_cust_info%notfound; end loop; close cur_cust_info; -d end;,带参数的游标 与存储过程和函数相似，可以将参数传递给游标并在查询中使用。这对于处理在某种条件下打开游标的情况非常有用,例： i_cust_name varchar2(20); i_cust_id number(13); i_sts varchar2(10):=A; cursor cur_cust_info(p_sts varchar2) is select name,cust_id from cust where sts= p_sts; -a begin open cur_cust_info(i_sts); -b loop fetch cur_cust_info into i_cust_name,i_cust_id; -c exit when cur_cust_info%notfound; end loop; close cur_cust_info; -d end;,REF CURSOR i_cust_name varchar2(20); i_cust_id number(13); type cur_ref is ref cursor; cur_cust_info cur_ref; -a begin open cur_cust_info for select name,cust_id from cust where sts = A; -b loop fetch cur_cust_info into i_cust_name,i_cust_id; -c exit when cur_cust_info%notfound; end loop; close cur_cust_info; -d end;,十、PL/SQL程序编写 块的介绍 PL/SQL程序基本结构是块（BLOCK）所有的PL/SQL程序都是由块组成的，这些块之间还可以相互嵌套。通常，程序中的每一块都实现一个逻辑操作，从而把不同的任务进行分割，由不同的块来实现 。,块的结构 DECLARE /* Declarative section - PL/SQL variables, types, cursors,and local subprograms go here. */ -声明部分 BEGIN /* Executable section - procedural and SQL statements go here.This is the main section of the block and the only one that is required. */ -执行部分 EXCEPTION /* Exception-handling section - error-handling statements go here. */ -出错部分 END;,Declare -声明部分 l_servid number(12); begin -执行部分 for rec in (select * from temp_ctzj_sts_cmp where serv_id_97 is null) loop begin select serv_id from serv_acc_nbr where nxx_nbr =rec.nxx_nbr and line_nbr = rec.line_nbr and sts = A) where rownum = 1; exception when no_data_found then null; end; begin update temp_ctzj_sts_cmp set serv_id_97 = to_char(l_servid) where nxx_nbr = rec.nxx_nbr and line_nbr = rec.line_nbr;,exception when others then dbms_output.put_line(i_servid); end; i_servid := null; end loop; exception -出错部分 when others then rollback; dbms_output.put_line(SQLERRM); end;,过程与函数 PL/SQL的过程和函数的运行方式非常类似于其他第3代程序设计语言使用的过程和函数。它们之间具有许多共同的特征属性。总体来说，过程和函数统称为子程序。,创建过程语句的语法如下所示： CREATE OR REPLACE PROCEDURE procedure_name ( argumentIN | OUT | IN OUT datatype, ,argumentIN | OUT | IN OUT datatype ) IS | AS procedure_body,IN/OUT/IN OUT参数,模式 说明,IN,OUT,IN OUT,当过程被调用时，实参的值将传入该过程。在该过程内部，形参类似PL/SQL使用的常数，即该值具有只读属性不能对其修改。当该过程结束时，控制将返回到调用环境，这时，对应的实参没有改变,当过程被调用时，实参具有的任何值将被忽略不计。在该过程内部，形参的作用类似没有初始化的PL/SQL变量，其值为空（ NULL）。该变量具有读写属性。当该过程结束时，控制将返回调用环境，形参的内容将赋予对应的实参。,该模式是模式IN 和OUT的组合。当调用过程时，实参的值将被传递到该过程中。在该过程内部，形参相当于初始化的变量，并具有读写属性。当该过程结束时，控制将返回到调用环境中，形参的内容将赋予实参,过程体 过程体是一种带有声明部分，可执行语句部分和异常部分的PL/SQL块。该声明部分是位于关键字IS或AS和关键字BEGIN之间的语句。可执行部分（该部分是必须要有的）是位于关键字BEGIN和EXCEPTION之间的语句。最后，异常部分位于关键字EXCEPTION和关键字END之间的语句。,综上所述，过程的结构应具有下面所示的特征： CREATE OR REPLACE PROCEDURE procedure_name parameter_list AS /* Declarative section is here */ -声明部分 BEGIN /* Executable section is here */ -执行部分 EXCEPTION /* Exception section is here */ -异常部分 END procedure_name;,函数 函数的创建与过程的创建十分相似，具体语法如下： CREATE OR REPLACE FUNCTION function_name (argument IN|OUT|IN OUT datatype ,argument IN|OUT|IN OUT datatype) RETURN datatype IS/AS FUCTION BODY;,包 包的说明（也叫做包头）包含了有关包内容的信息。然而，该部分中不包括包的代码部分。 CREATE OR REPLACE PACKAGE package_nameIS | AS type_definition| -结构定义 procedure_specification| -过程声明 function_specification| -函数声明 variable_declaration| -变量声明 exception_declaration| -出错声明 cursor_declaration| -游标声明 pragma_declaration END package_name;,通俗地讲：包头只是起到声明作用，其他具体的实现都是在包体中完成的。,包体介绍 包头和包体中的过程和函数的说明必须一致，其中包括子程序名和其参数名，以及参数的模式,包头 CREATE OR REPLACE PACKAGE pkg_val IS - Author : MRC - Created : 2005-4-8 14:07:30 - Purpose : VALIDATE THE VALUES OF EACH ENTITY INCLUDE CUST,ACCT,SERV,PROD,ORDER -修改：王兴明 2005/10/20 -客户信息校验 PROCEDURE prc_val_cust_src(i_src_fileset VARCHAR2, i_sib_process_status VARCHAR2, i_batch_no NUMBER); -账户信息校验 PROCEDURE prc_val_acct_src(i_src_fileset VARCHAR2, i_sib_process_status VARCHAR2, i_batch_no NUMBER); -用户信息校验 PROCEDURE prc_val_serv_src(i_src_fileset VARCHAR2, i_sib_process_status VARCHAR2, i_batch_no NUMBER); -子产品信息校验 PROCEDURE prc_val_prod_src(i_src_fileset VARCHAR2, i_sib_process_status VARCHAR2, i_batch_no NUMBER); -优惠信息校验 PROCEDURE prc_val_disc_src(i_src_fileset VARCHAR2, i_sib_process_status VARCHAR2, i_batch_no NUMBER); END;,CREATE OR REPLACE PACKAGE BODY pkg_val AS PROCEDURE prc_val_cust_src(i_src_fileset varchar2, i_sib_process_status varchar2, i_batch_no number) AS i_process_name VARCHAR2(50) :=; i_error_info VARCHAR2(2000); -错误详细描述 BEGIN END; PROCEDURE prc_val_acct_src(i_src_fileset varchar2, i_sib_process_status varchar2 i_batch_no number) AS END;,包的作用域 包头中声明的任何对象都是在其作用域中，并且可在其外部使用包名作为前缀对其进行引用。,begin arg1 datatype:=xxx; arg2 datatype:=xxx; package_name.procedure_name(arg1,arg2); end;,十一、常用工具与调试方式（SQL Navigator PL/SQL Developer） SQL Navigator调试 SQL Navigator的调试很简单，大致分为以下几步：,1进入Debug模式,2设置调试参数,3正式调试,4正常结束调试或者中断调试,Server output 按钮，可以显示调试过程中oracle执行的情况,Start debugger按钮，点击按钮进入调试模式，调试必须点这个按钮,进入DEBUG模式,Step over按钮，点击进入Console界面（调试控制台）,设置调试参数 需要完成两项工作,1）设置IN参数,2）设置提交方式,正式调试,直接运行,跑到断点处停下来，按钮F4。,单步调试，遇到调用其他存储过程或者函数时跳过，按钮F8。,单步调试，遇到调用其他存储过程或者函数时进入该存储过程或者函数中，按钮F7。,当调试时进入了调用存储过程或者函数后，该按钮可以帮助跳出该调用存储过程或者函数，回到调试的上层函数中，按钮Shift+F7。,断点设置 1）点击需要设置断点的代码行，点击右键，出现菜单，选择toggle breakpoint选项，该行应该成红色，此时断点即设置成功 。 2）点击需要设置断点的代码行，按按钮F5也能设置断点。 设置断点之后，按F4按钮，程序可以连续运行，直到碰到设置的断点为止。 当然，设置的断点需要在程序运行能够执行道的代码段上，否则程序将直接执行下去,察看变量 1）将鼠标移到变量上方，一秒之后SQL Navigator就会显示出变量的值。移开鼠标，变量值消失。 2）将鼠标移到变量上方，点击右键出现菜单，点击Add watch选项，弹出对话框，输入变量名称（SQL Navigator一般已经将需要观测的变量填入）后点击确定即可,正常结束调试或者中断调试 正常调试直到执行到存储过程或者函数最后一段代码之后，即可正常结束调试。此时SQL Navigator会根据之前选择的提交方式决定到底应该commit还是rollback，或者什么也不做。 由于某种原因需要中断本次调试时，只需要点击按钮， 即可结束此次调试。SQL Navigator既不会commit,也不会rollback。,PL/SQL Developer的调试 PL/SQL Developer的调试也比较简单，大致分为以下几步： 1新建调试窗口 2设置调试内容 3正式调试 4正常结束调试或者中断调试,新建调试窗口 点击菜单栏FileNewTest Windows，新建一个调试窗口,设置调试内容 可以看到，调试窗口中的内容是一个块（BLOCK），按照PL/SQL的方式将要调试的存储过程或者函数以及参数值填入这个块中，块的填写如下图所示,正式调试,跑到断点处停下来，按钮Ctrl+R,单步调试，遇到调用其他存储过程或者函数时跳过，按钮Ctrl+O,单步调试，遇到调用其他存储过程或者函数时进入该存储过程或者函数中，按钮Ctrl+N,当调试时进入了调用存储过程或者函数后，该按钮可以帮助跳出该调用存储过程或者函数，回到调试的上层函数中，按钮Ctrl+T,断点设置 将鼠标移到需要设置断点的代码行，在该行灰色框处单击，立即出现一个断点标记，如图所示：,察看变量 1）将鼠标移到变量上方，一秒之后PL/SQL Developer就会显示出变量的值。移开鼠标，变量值消失,2）将鼠标移到变量上方，点击右键出现菜单，点击Add Variable to Watches选项，变量即出现在左下方的观测窗口。,正常结束调试或者中断调试 正常调试直到执行到存储过程或者函数最后一段代码之后，即可正常结束调试。 由于某种原因需要中断本次调试时，只需要点击按钮 ，即可结束此次调试。,