《oracle补充》PPT课件.ppt

,PL-SQL编程,2,目标,掌握如何定义变量并赋值 掌握如何输出显示数据 掌握IF、WHILE、CASE逻辑控制语句,3,PL/SQL块,PL/SQL程序由三个块组成，即声明部分、执行部分、异常处理部分。 PL/SQL块的结构如下： DECLARE /* 声明部分: 在此声明PL/SQL用到的变量,类型及游标，以及局部的存储过程和函数 */ BEGIN /* 执行部分: 过程及SQL 语句 , 即程序的主要部分 */ EXCEPTION /* 执行异常部分: 错误处理 */ END; 其中 执行部分是必须的。,4,注释,在PL/SQL里，可以使用两种符号来写注释，即： 使用双 - ( 减号) 加注释 PL/SQL允许用 来写注释，它的作用范围是只能在一行有效。如： V_Sal NUMBER(12,2); - 工资变量。 使用 /* */ 来加一行或多行注释，如： /*/ /* 文件名： statistcs_sal.sql */ /*/,5,PL/SQL 变量类型,PL/SQL支持多种数据类型，本节讨论代码中最常用并且最实用的数据类型。 Varchar2 Number Date Boolean,6,Varchar2,Varchar2为可变长的字母数字数据类型。在PL/SQL中，varchar2的最大长度为32767字节。 在declare部分，其定义以分号(；)结束，所有varchar2变量的定义类似于下面的定义： variable_name varchar2(max_length); 括号中的长度值为本变量的最大长度且必须是正整数，如： vc_field varchar2(10); 在定义变量时，可以同时对其进行初始化，格式为： vc_field varchar2(10):=STARTVALUE;,7,Number,number类型数据可用来表示所有的数值数据，说明格式为： num_field number(precision,scale); 其中，precision可以有138个数字位，而scale表示在precision位数字中小数点后的数字位数，下面的定义： num_fieldnumber(12,2); 表示num_field是一个整数部分最多10位、小数部分最多2位的变量。,8,Date,此数据类型用于保存固定长度的日期值，date变量的说明为： date_field date; 在缺省时，Oracle以DD-MON-YY格式显示日期。因此，2004年9月9日显示为09-SEP-04。 PL/SQL中编程处理日期必须使用这种格式。是否可以说用户必须用数据库缺省的日期格式，除非用户用TO_CHAR或TO_DATE是否能提醒用户用自己的TO_DATE/TO_CHAR定义的格式而不依赖于缺省的日期格式。 格式由初始化参数nls_date_format设置 也可通过set设置 alter session set nls_date_format=yyyy-mm-dd hh24:mi:ss,9,Boolean,这种数据类型只有两个值：true或false。在使用boolean变量时，如果测试结果为true，则做某事，否则做另外的事。例如，如果试图考察某公司是否分发了一个10KB的2000年年度预算表，可用boolean变量。若该公司分发了此表，则该变量值为true。 布尔值只有TRUE, FALSE及 NULL 三个值,10,11,变量,（1）声明变量 声明变量的语句格式如下： Variable_name CONSTANT datatype NOT NULL:=|DEFAULT expression 注意：可以在声明变量的同时给变量强制性的加上NOT NULL约束条件，此时变量在初始化时必须赋值。 （2）给变量赋值 给变量赋值有两种方式： 直接给变量赋值 通过SQL SELECT INTO 给变量赋值,12,v_ename varchar2(10); v_sal number(6,2); v_tax_rate constant number(3,2):=5.5 v_hiredate DATE;,13,declare x varchar(20); begin x:=This is .; dbms_output.put_line(x:|x); end;,14,数据库赋值是通过 SELECT语句来完成的，每次执行 SELECT语句就赋值一次，一般要求被赋值的变量与SELECT中的列名要一一对应。 DECLARE emp_id emp.empno%TYPE; emp_name emp.ename%TYPE; wages emp.sal%TYPE; BEGIN Emp_id:=7788; SELECT ename, NVL(sal,0) + NVL(comm,0) INTO emp_name, wages FROM emp WHERE empno = emp_id; Dbms_output.put_line(emp_name|-|to_char(wages); END;,15,可转换的类型赋值,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.dd hh24:mi:ss) ;,16,使用代替变量输入雇员编号，分别保存员工名，工资，显示输出各变量的值 declare v_name varchar2(5); v_sal number(6,2); begin select ename,sal into v_name,v_sal from emp where empno= /,17,练习,使用代替变量输入雇员编号，分别保存员工名，工资，个人所得税，显示输出各变量的值 其中个人所得数计算方式工资的3%,18,使用%TYPE,%TYPE：使一个变量的数据类型与另一个变量（尤其是表的列）数据类型一致，oracle提供的一种定义方式,19,复合变量,ORACLE 在 PL/SQL 中除了提供象前面介绍的各种类型外,还提供一种称为复合类型的类型-记录和表 定义记录类型语法如下: TYPE record_type IS RECORD( Field1 type1 NOT NULL := exp1 , Field2 type2 NOT NULL := exp2 , . . . . . . Fieldn typen NOT NULL := expn ) ;,20,DECLARE TYPE test_rec IS RECORD( Code VARCHAR2(10), Name VARCHAR2(30) NOT NULL :=a book); V_book test_rec; BEGIN V_book.code :=123; V_ :=C+ Programming; DBMS_OUTPUT.PUT_LINE(v_book.code|v_); END;,21,%ROWTYPE,%ROWTYPE：使一个变量的数据类型与参照表的数据类型一致，oracle提供的一种定义方式,22,PL/SQL语法,RECORD：记录类型。类似于c语言中的structure,23,插入语句,declare v_deptno dept.deptno%type; v_dname dept.dname%type; begin v_deptno:= /,24,使用子查询插入,declare v_deptno dept.deptno%type:= /,25,更新数据,使用表达式更新列 使用子查询更新列,26,删除记录,使用变量删除数据 使用子查询删除记录 declare v_ename emp.ename%type:= /,27,PL/SQL结构控制语句,If语句 if then ; else ; end if if 条件表达式1 then ; elsif 条件表达式2 then ; elsif 条件表达式3 then ; else ; end if 例,28,SQL get E:jhrdocument备课oracle脚本if1.txt 1 declare 2 v_score integer; 3 begin 4 select score into v_score 5 from courseadmin.courseresults 6 where sid=200301 and cid=1; 7 if v_score=60 then 8 dbms_output.put_line(及格); 9 else 10 dbms_output.put_line(不及格); 11 end if; 12* end; SQL / 及格,29,PL/SQL结构控制语句,Case结构 Case 变量或表达式 when 条件表达式1 then ; when 条件表达式2 then ; when 条件表达式3 then ; else ; end case； 例,30,SQL get E:jhrdocument备课oracle脚本case.txt 1 declare 2 v_score integer; 3 begin 4 select score into v_score 5 from courseadmin.courseresults 6 where sid=200301 and cid=1; 7 case 8 when v_score=90 then dbms_output.put_line(优秀); 9 when v_score=80 and v_score=70 and v_score=60 and v_score / 中等,31,课堂练习,课堂练习： 请根据平均分和下面的评分规则，编写PL-SQL语句输入学生学号查询学员的成 绩，如上图所示。 优 ：90分以上 良 ：8089分 中 ：7079分 差 ：6069分 不及格 ：60分以下 如果输入s25303，则显示该学生成绩：中,学员成绩分析,32,PL/SQL结构控制语句,Loop Loop exit /exit when 条件表达式 循环体 end loop 例子,33,34,PL/SQL结构控制语句,While语句 While 条件表达式 loop 循环体 end loop 例子 declare i integer; begin i:=1; while i 100 loop i:=i+1; end loop; dbms_output.put_line(i); end;,35,PL/SQL结构控制语句,For 语句 For 循环变量 in 初值表达式终值表达式 loop 循环体 end loop 例 declare vsum integer; begin vsum:=0; for i in 1100 loop vsum:=vsum+i; end loop; dbms_output.put_line(vsum); end;,36,标号和GOTO,PL/SQL中GOTO语句是无条件跳转到指定的标号去的意思。语法如下： GOTO label; . . . . . . /*标号是用括起来的标识符 */,37,declare x number; begin x:=0; x:=x+1; dbms_output.put_line(x); if x3 then goto repeat_loop; end if; end;,38,DECLARE V_counter NUMBER := 1; BEGIN LOOP DBMS_OUTPUT.PUT_LINE(V_counter的当前值为:|V_counter); V_counter := v_counter + 1; IF v_counter 10 THEN GOTO l_ENDofLOOP; END IF; END LOOP; DBMS_OUTPUT.PUT_LINE(V_counter的当前值为:|V_counter); END ;,39,NULL 语句,在PL/SQL 程序中，可以用 null 语句来说明“不用做任何事情”的意思，相当于一个占位符，可以使某些语句变得有意义，提高程序的可读性。如： DECLARE . . . BEGIN IF v_num IS NULL THEN GOTO print1; END IF; NULL; - 不需要处理任何数据。 END;,40,练习,创建一个表temp_table ，其中只有一个字段num_col 类型number 用loop循环插入编号为1-10的记录 用while循环插入编号11-20的记录 用for循环插入编号21-30的记录 用for循环插入编号40-31的记录 用goto语句循环插入41-50的记录,41,游标,42,游标有两种类型：显式游标和隐式游标。 在前述程序中用到的SELECT.INTO.查询语句，一次只能从数据库中提取一行数据，对于这种形式的查询和DML操作，系统都会使用一个隐式游标。 但是如果要提取多行数据，就要由程序员定义一个显式游标，并通过与游标有关的语句进行处理。显式游标对应一个返回结果为多行多列的SELECT语句。 游标一旦打开，数据就从数据库中传送到游标变量中，然后应用程序再从游标变量中分解出需要的数据，并进行处理。,什么是游标？,43,隐式游标,如前所述，DML操作和单行SELECT语句会使用隐式游标，它们是： 插入操作：INSERT。 更新操作：UPDATE。 删除操作：DELETE。 单行查询操作：SELECT . INTO .。 当系统使用一个隐式游标时，可以通过隐式游标的属性来了解操作的状态和结果，进而控制程序的流程。 隐式游标可以使用名字SQL来访问，但要注意，通过SQL游标名总是只能访问前一个DML操作或单行SELECT操作的游标属性。所以通常在刚刚执行完操作之后，立即使用SQL游标名来访问属性。游标的属性有四种，如表7-1所示。,44,隐式游标属性,45,使用隐式游标的属性，判断对雇员工资的修改是否成功。 SET SERVEROUTPUT ON BEGIN UPDATE emp SET sal=sal+100 WHERE empno=1234; IF SQL%FOUND THEN DBMS_OUTPUT.PUT_LINE(成功修改雇员工资！); COMMIT; ELSE DBMS_OUTPUT.PUT_LINE(修改雇员工资失败！); END IF; END;,隐式游标例子,46,显式游标,游标的使用分成以下4个步骤。 1声明游标 在DECLARE部分按以下格式声明游标： CURSOR 游标名 (参数1 数据类型，参数2 数据类型.) IS SELECT语句; 参数是可选部分，所定义的参数可以出现在SELECT语句的WHERE子句中。如果定义了参数，则必须在打开游标时传递相应的实际参数。 SELECT语句是对表或视图的查询语句，甚至也可以是联合查询。可以带WHERE条件、ORDER BY或GROUP BY等子句，但不能使用INTO子句。在SELECT语句中可以使用在定义游标之前定义的变量。,47,2打开游标 在可执行部分，按以下格式打开游标：OPEN 游标名(实际参数1，实际参数2.); 打开游标时，SELECT语句的查询结果就被传送到了游标工作区。 3提取数据 在可执行部分，按以下格式将游标工作区中的数据取到变量中。提取操作必须在打开游标之后进行。 FETCH 游标名 INTO 变量名1，变量名2.;或 FETCH 游标名 INTO 记录变量; 游标打开后有一个指针指向数据区，FETCH语句一次返回指针所指的一行数据，要返回多行需重复执行，可以使用循环语句来实现。控制循环可以通过判断游标的属性来进行。,显式游标,48,获取数据格式： 第一种格式中的变量名是用来从游标中接收数据的变量，需要事先定义。变量的个数和类型应与SELECT语句中的字段变量的个数和类型一致。 第二种格式一次将一行数据取到记录变量中，需要使用%ROWTYPE事先定义记录变量，这种形式使用起来比较方便，不必分别定义和使用多个变量。 定义记录变量的方法如下： 其中的表必须存在，游标名也必须先定义。 变量名 表名|游标名%ROWTYPE； 4关闭游标 CLOSE 游标名; 显式游标打开后，必须显式地关闭。游标一旦关闭，游标占用的资源就被释放，游标变成无效，必须重新打开才能使用。,游标,49,显式游标,声明游标 cursor 游标名 is select语句 打开游标 open 游标名 提取游标 fetch 游标名 into 变量表 关闭游标 close 游标名,50,显式游标属性,虽然可以使用前面的形式获得游标数据，但是在游标定义以后使用它的一些属性来进行结构控制是一种更为灵活的方法。显式游标的属性如表所示。 游标名%属性 要判断游标emp_cursor是否处于打开状态，可以使用属性emp_cursor%ISOPEN。如果游标已经打开，则返回值为“真”，否则为“假”。具体可参照以下的训练。,51,【1】 用游标提取emp表中7788雇员的名称和职务。 SET SERVEROUTPUT ON DECLARE v_ename VARCHAR2(10); v_job VARCHAR2(10); CURSOR emp_cursor IS SELECT ename,job FROM emp WHERE empno=7788; BEGIN OPEN emp_cursor; FETCH emp_cursor INTO v_ename,v_job; DBMS_OUTPUT.PUT_LINE(v_ename|,|v_job); CLOSE emp_cursor; END;,例子1,52,【训练2】 用游标提取emp表中7788雇员的姓名、职务和工资。 SET SERVEROUTPUT ON DECLARE CURSOR emp_cursor IS SELECT ename,job,sal FROM emp WHERE empno=7788; emp_record emp_cursor%ROWTYPE; BEGIN OPEN emp_cursor; FETCH emp_cursor INTO emp_record; DBMS_OUTPUT.PUT_LINE(emp_record.ename|,|emp_record.job|,| emp_record.sal); CLOSE emp_cursor; END;,例子2,53,【训练3】 显示工资最高的前3名雇员的名称和工资。 SET SERVEROUTPUT ON DECLARE V_ename VARCHAR2(10); V_sal NUMBER(5); CURSOR emp_cursor IS SELECT ename,sal FROM emp ORDER BY sal DESC; BEGIN OPEN emp_cursor; FOR I IN 13 LOOP FETCH emp_cursor INTO v_ename,v_sal; DBMS_OUTPUT.PUT_LINE(v_ename|,|v_sal); END LOOP; CLOSE emp_cursor; END;,例子3,54,练习,1、编写PL/SQL块，定义游标，显示高收入的员工编号、姓名、工作类型及工资 2、 查询前10名员工的信息。 3、给工资低于1200 的员工增加工资50。 4、使用多种方式，输出工资最高的前n个员工的姓名和工资。,55,SET SERVEROUTPUT ON DECLARE V_empno NUMBER(5); V_ename VARCHAR2(10); CURSOR emp_cursor(p_deptno NUMBER, p_job VARCHAR2) IS SELECT empno, ename FROM emp WHERE deptno = p_deptno AND job = p_job; BEGIN OPEN emp_cursor(10, CLERK); LOOP FETCH emp_cursor INTO v_empno,v_ename; EXIT WHEN emp_cursor%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_empno|,|v_ename); END LOOP; END;,游标参数的传递,56,带参数的显式游标,声明显式游标时可以带参数以提高灵活性 声明带参数的显式游标的语法如下： CURSOR ( ) IS select_statement;,SQL SET SERVEROUTPUT ON SQL DECLARE desig VARCHAR2(20); emp_code VARCHAR2(5); empnm VARCHAR2(20); CURSOR emp_cur(desigparam VARCHAR2) IS SELECT empno, ename FROM employee WHERE designation=desigparam; BEGIN desig:= test; OPEN emp_cur(desig); LOOP FETCH emp_cur INTO emp_code,empnm; EXIT WHEN emp_cur%NOTFOUND; DBMS_OUTPUT.PUT_LINE(emp_code| |empnm); END LOOP; CLOSE emp_cur; END;,57,练习,编写PL/SQL，定义参数游标（参数：岗位）,使用替代变量输入岗位，并显示该岗位所有员工的姓名和工资,58,【训练1】使用特殊的FOR循环形式显示全部雇员的编号和名称。 SET SERVEROUTPUT ON DECLARE CURSOR emp_cursor IS SELECT empno, ename FROM emp; BEGIN FOR Emp_record IN emp_cursor LOOP DBMS_OUTPUT.PUT_LINE(Emp_record.empno|Emp_record.ename); END LOOP;END; 说明：可以看到该循环形式非常简单，隐含了记录变量的定义、游标的打开、提取和关闭过程。Emp_record为隐含定义的记录变量，循环的执行次数与游标取得的数据的行数相一致。,游标循环,59,【训练2】 另一种形式的游标循环。 SET SERVEROUTPUT ON BEGIN FOR re IN (SELECT ename FROM EMP) LOOP DBMS_OUTPUT.PUT_LINE(re.ename) END LOOP; END; 说明：该种形式更为简单，省略了游标的定义，游标的SELECT查询语句在循环中直接出现。,游标,60,游标FOR循环,存储过程,62,创建存储过程,CREATE OR REPLACE PROCEDURE procedure_name (parameter1 model datatype1,parameter2 model datatype2 .) IS AS BEGIN PL/SQL Block; END procedure_name; 其中： procedure_name是存储过程的名字，parameter用于指定参数，model用于指定参数模式，datatype用于指定参数类型， IS AS用于开始PL/SQL代码块。 注：当定义存储过程的参数时，只能指定数据类型，不能指定数据长度,63,存储过程是如何进行定义和维护的？,CREATE OR REPLACE PROCEDURE USP_OutTime IS BEGIN DBMS_OUTPUT.PUT_LINE(SYSDATE); END USP_OutTime;,64,存储过程是如何进行定义和维护的？,CREATE OR REPLACE PROCEDURE USP_Learing ( p_para1 varchar2 := 参数一, p_para2 nvarchar2 default 参数二, p_para3 out varchar2, p_para4 in out varchar2 ) IS BEGIN DECLARE v_para5 varchar2(20); BEGIN v_para5 := 输入输出：|p_para4; p_para3 := 输出：|p_para1|p_para2; p_para4 := v_para5; END; END USP_Learing;,65,删除存储过程,删除存储过程 DROP PROCEDURE procedure_name; 编译存储过程 ALTER PROCEDURE procedure_name COMPILE;,66,与存储过程相关的几个查询,-查看无效的存储过程 SELECT object_name FROM USER_OBJECTS WHERE STATUS=INVALID AND OBJECT_TYPE=PROCEDURE -查看存储过程的代码 SELECT TEXT FROM USER_SOURCE WHERE NAME= procedure_name 其中： procedure_name是存储过程的名字,67,如何调用存储过程,执行(或调用)存储过程的人是过程的创建者或是拥有EXECUTE ANY PROCEDURE系统权限的人或是被拥有者授予EXECUTE权限的人。执行的方法如下： 方法1： EXECUTE 模式名.存储过程名(参数.); 方法2： BEGIN 模式名.存储过程名(参数.); END;,68,传递的参数必须与定义的参数类型、个数和顺序一致(如果参数定义了默认值，则调用时可以省略参数)。参数可以是变量、常量或表达式，用法参见下一节。 如果是调用本账户下的存储过程，则模式名可以省略。要调用其他账户编写的存储过程，则模式名必须要添加。 以下是一个生成和调用简单存储过程的训练。注意要事先授予创建存储过程的权限。,69,？【训练1】 创建一个显示雇员总人数的存储过程。 步骤1：登录SCOTT账户(或学生个人账户)。 步骤2：在SQL*Plus输入区中，输入以下存储过程： CREATE OR REPLACE PROCEDURE EMP_COUNT AS V_TOTAL NUMBER(10); BEGIN SELECT COUNT(*) INTO V_TOTAL FROM EMP; DBMS_OUTPUT.PUT_LINE(雇员总人数为：|V_TOTAL); END;,70,步骤3： 如果存在错误，就会显示: 警告: 创建的过程带有编译错误。 如果存在错误，对脚本进行修改，直到没有错误产生。 如果编译结果正确，将显示： 过程已创建。 步骤4：调用存储过程，在输入区中输入以下语句并执行： EXECUTE EMP_COUNT; 显示结果为： 雇员总人数为：14 PL/SQL 过程已成功完成。,71,【训练2】 在PL/SQL程序中调用存储过程。 步骤1：登录SCOTT账户。 步骤2：授权STUDENT账户使用该存储过程，即在SQL*Plus输入区中，输入以下的命令： GRANT EXECUTE ON EMP_COUNT TO STUDENT 授权成功。 步骤3：登录STUDENT账户，在SQL*Plus输入区中输入以下程序： SET SERVEROUTPUT ON BEGIN SCOTT.EMP_COUNT; END;,72,【训练3】 编写显示雇员信息的存储过程EMP_LIST，并引用EMP_COUNT存储过程。 步骤1：在SQL*Plus输入区中输入并编译以下存储过程： CREATE OR REPLACE PROCEDURE EMP_LIST AS CURSOR emp_cursor IS SELECT empno,ename FROM emp; BEGIN FOR Emp_record IN emp_cursor LOOP DBMS_OUTPUT.PUT_LINE(Emp_record.empno|Emp_record.ename); END LOOP; EMP_COUNT; END;,73,步骤2：调用存储过程，在输入区中输入以下语句并执行： EXECUTE EMP_LIST 显示结果为： 7369SMITH 7499ALLEN 7521WARD 7566JONES 执行结果： 雇员总人数为：14 PL/SQL 过程已成功完成。,74,练习1,编写显示部门信息的存储过程DEPT_LIST，要求统计出部门个数。,75,参数传递,参数的作用是向存储过程传递数据，或从存储过程获得返回结果。正确的使用参数可以大大增加存储过程的灵活性和通用性。 参数的类型有三种,76,参数传递,参数名 IN 数据类型 DEFAULT 值； 定义一个输入参数变量，用于传递参数给存储过程。在调用存储过程时，主程序的实际参数可以是常量、有值变量或表达式等。DEFAULT 关键字为可选项，用来设定参数的默认值。 参数名 OUT 数据类型； 定义一个输出参数变量，用于从存储过程获取数据，即变量从存储过程中返回值给主程序。在存储过程中，参数变量只能被赋值而不能将其用于赋值，在存储过程中必须给输出变量至少赋值一次。,77,参数传递,参数名 IN OUT 数据类型 DEFAULT 值 在存储过程中，变量接收主程序传递的值，同时可以参加赋值运算，也可以对其进行赋值。在存储过程中必须给变量至少赋值一次。 如果省略IN、OUT或IN OUT，则默认模式是IN。,78,【训练1】 编写给雇员增加工资的存储过程CHANGE_SALARY，通过IN类型的参数传递要增加工资的雇员编号和增加的工资额。 CREATE OR REPLACE PROCEDURE CHANGE_SALARY ( P_EMPNO IN NUMBER DEFAULT 7788, P_RAISE NUMBER DEFAULT 10) AS V_ENAME VARCHAR2(10); V_SAL NUMBER(5); BEGIN SELECT ENAME,SAL INTO V_ENAME,V_SAL FROM EMP WHERE EMPNO=P_EMPNO; UPDATE EMP SET SAL=SAL+P_RAISE WHERE EMPNO=P_EMPNO; DBMS_OUTPUT.PUT_LINE(雇员|V_ENAME|的工资被改为|TO_CHAR(V_SAL+P_RAISE); COMMIT; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(发生错误，修改失败！); ROL