1 / 133

学习情境 5 统计分析与报表

学习情境 5 统计分析与报表. 情境导入. 项目应用的需求： 对雇员信息统计分析与报表输出 统计 分析 报表. 2. 工作任务. 任务 1 ：不同条件修改员工信息 任务 2 ：查询符合条件的多个员工的信息 任务 2 ：按部门统计员工信息 任务 4 ：修改或删除多个员工信息 任务 5 ：查询满足条件的雇员信息 任务 6 ：在另一个用户下执行查找功能 任务 7 ：查找满足条件的雇员名字 任务 8 ：将不同的功能集成 任务 9 ：限制用户修改信息的时间. 3. 工作任务. 任务 10 ：插入信息的限制条件 任务 11 ：记录对信息的操作时间和用户

cade
Download Presentation

学习情境 5 统计分析与报表

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 学习情境5 统计分析与报表

  2. 情境导入 • 项目应用的需求： 对雇员信息统计分析与报表输出 • 统计 • 分析 • 报表 2

  3. 工作任务 • 任务1：不同条件修改员工信息 • 任务2：查询符合条件的多个员工的信息 • 任务2：按部门统计员工信息 • 任务4：修改或删除多个员工信息 • 任务5：查询满足条件的雇员信息 • 任务6：在另一个用户下执行查找功能 • 任务7：查找满足条件的雇员名字 • 任务8：将不同的功能集成 • 任务9：限制用户修改信息的时间 3

  4. 工作任务 • 任务10：插入信息的限制条件 • 任务11：记录对信息的操作时间和用户 • 任务12：自动生成用户信息数据 • 任务13：记录对信息的操作时间和用户以及行为 • 任务14：对用户查询信息的修改 • 任务15：屏蔽掉若干检查或限制功能 • 任务16：按用户输入的日期查询 • 任务17：将雇员查询统计信息输出 • 任务18：将雇员查询统计结果输出到文件中 4

  5. 学习目标 理解PL/SQL语言的概念。 掌握PL/SQL程序块的结构 了解数据类型及其用法。 理解控制结构的内容。 了解错误处理的方法。 创建和使用子程序 、程序包。 理解触发器的作用、分类。 掌握各种触发器的使用。

  6. 基本知识：PL/SQL 简介 PL/SQL 是过程语言(Procedural Language)与结构化查询语言(SQL)结合而成的编程语言 PL/SQL 是对 SQL 的扩展 支持多种数据类型，如大对象和集合类型，可使用条件和循环等控制结构 可用于创建存储过程、触发器和程序包，给SQL语句的执行添加程序逻辑 与 Oracle 服务器和 Oracle 工具紧密集成，具备可移植性、灵活性和安全性

  7. PL/SQL的优点 2-1 Procedure Begin ProcedureCall SQL Command … End Oracle • 支持 SQL，在 PL/SQL 中可以使用： • 数据操纵命令 • 事务控制命令 • 游标控制 • SQL 函数和 SQL 运算符 • 支持面向对象编程 (OOP) • 可移植性，可运行在任何操作系统和平台上的Oralce 数据库 • 更佳的性能，PL/SQL 经过编译执行 用户将整个语句块发送给 Oracle

  8. PL/SQL 的优点 2-2 SQL PL/SQL • 与 SQL 紧密集成，简化数据处理。 • 支持所有 SQL 数据类型 • 支持 NULL 值 • 支持 %TYPE 和 %ROWTYPE 属性类型 • 安全性，可以通过存储过程限制用户对数据的访问

  9. PL/SQL的体系结构 Oracle 服务器 PL/SQL引擎 过程语句执行器 SQL语句执行器 • PL/SQL 引擎驻留在 Oracle 服务器中 • 该引擎接受 PL/SQL 块并对其进行编译执行 将PL/SQL块发送给 Oracle 服务器 将结果发送给用户 执行过程语句 用户 引擎将 SQL 语句发送给SQL 语句执行器 执行 SQL 语句

  10. PL/SQL 块简介 • PL/SQL 块是构成 PL/SQL 程序的基本单元 • 将逻辑上相关的声明和语句组合在一起 • PL/SQL 分为三个部分，声明部分、可执行部分和异常处理部分 [DECLARE declarations] BEGIN executable statements [EXCEPTION handlers] END; DECLARE qty_on_hand NUMBER(5); BEGIN SELECT quantity INTO qty_on_hand FROM Products WHERE product = '芭比娃娃' FOR UPDATE OF quantity; IF qty_on_hand > 0 THEN UPDATE Products SET quantity = quantity + 1 WHERE product = '芭比娃娃'; INSERT INTO purchase_record VALUES ('已购买芭比娃娃', SYSDATE); END IF; COMMIT; EXCEPTION /* 异常处理语句 */ WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('出错:'|| SQLERRM); END; 声明部分定义变量、游标和自定义异常 包含 SQL 和 PL/SQL 语句的可执行部分 指定出现错误时需要执行的操作

  11. 变量和常量 1-2 PL/SQL 块中可以使用变量和常量 在声明部分声明，使用前必须先声明 声明时必须指定数据类型，每行声明一个标识符 在可执行部分的 SQL 语句和过程语句中使用 声明变量和常量的语法： identifier [CONSTANT] datatype [NOT NULL] [:= | DEFAULT expr]; • 给变量赋值有两种方法： • 使用赋值语句 := • 使用 SELECT INTO 语句

  12. 变量和常量 2-2 DECLARE icode VARCHAR2(6); p_catg VARCHAR2(20); p_rate NUMBER; c_rate CONSTANT NUMBER := 0.10; BEGIN ... icode := 'i205'; SELECT p_category, itemrate * c_rate INTO p_catg, p_rate FROM itemfile WHERE itemcode = icode; ... END;

  13. 数据类型 • PL/SQL 支持的内置数据类型 数据类型 引用类型 标量类型 LOB类型 复合类型 存储非结构化数据块 数字 BFILE 属性类型 数组类型 字符 BLOB 布尔型 CLOB 记录类型 日期时间 NCLOB 表类型

  14. 数字数据类型 • 指定数值的存储格式 数字数据类型 BINARY_INTEGER NUMBER PLS_INTEGER 存储有符号整数，所需存储空间少于NUMBER类型值 存储整数、实数和浮点数 存储有符号整数，可使算术计算快速而有效 NATURAL DECIMAL NATURALLN FLOAT POSITIVE INTEGER REAL POSITIVEN SIGNTYPE

  15. 字符数据类型 字符数据类型包括： CHAR VARCHAR2 LONG RAW LONG RAW PL/SQL 的数据类型与 SQL数据类型的比较

  16. 日期时间和布尔数据类型 日期时间类型 存储日期和时间数据 常用的两种日期时间类型 DATE TIMESTAMP 布尔数据类型 此类别只有一种类型，即BOOLEAN类型 用于存储逻辑值(TRUE、FALSE和NULL) 不能向数据库中插入BOOLEAN数据 不能将列值保存到BOOLEAN变量中 只能对BOOLEAN变量执行逻辑操作

  17. LOB 数据类型 2-1 • 用于存储大文本、图像、视频剪辑和声音剪辑等非结构化数据。 • LOB 数据类型可存储最大 4GB的数据。 • LOB 类型包括： • BLOB将大型二进制对象存储在数据库中 • CLOB将大型字符数据存储在数据库中 • NCLOB存储大型UNICODE字符数据 • BFILE 将大型二进制对象存储在操作系统文件中

  18. LOB 数据类型 2-2 LOB 类型的数据库列仅存储定位符，该定位符指向大型对象的存储位置 DBMS_LOB程序包用于操纵 LOB 数据 SET SERVEROUTPUT ON DECLARE clob_var CLOB; amount INTEGER; offset INTEGER; output_var VARCHAR2(100); BEGIN SELECT chapter_text INTO clob_var FROM my_book_text WHERE chapter_id=5; amount := 24; -- 要读取的字符数 offset := 1; -- 起始位置 DBMS_LOB.READ(clob_var,amount,offset,output_var); DBMS_OUTPUT.PUT_LINE(output_var); END; / 从表中选择 CLOB 定位符 到 clob_var变量中 从CLOB数据中读取24个字符存储到 output_var 变量中 显示读到的信息

  19. 复合类型 PL/SQL语言的复合类型是用户定义的，复合类型是标量类型的组合，使用这些数据类型可以拓宽应用范围。先定义，再声明，最后才能使用。 常用的复合类型： • 属性类型 • 记录类型 • 表类型 • 数组类型

  20. 属性类型 用于引用数据库列的数据类型，以及表示表中一行的记录类型 属性类型有两种： %TYPE - 引用变量和数据库列的数据类型 %ROWTYPE - 提供表示表中一行的记录类型 使用属性类型的优点： 不需要知道被引用的表列的具体类型 如果被引用对象的数据类型发生改变，PL/SQL 变量的数据类型也随之改变 empcode emp.empno%TYPE; emp_rec emp%ROWTYPE;

  21. 记录类型 • 由一组相关的记录成员组成的，通常用来表示对应数据库表中的一行。使用PL/SQL记录时应自定义记录类型和记录变量，也可以使用%ROWTYPE属性定义记录变量。引用记录成员时，必须要记录变量作为前缀。 • 自定义记录类型和记录变量的语法： TYPE <记录类型名> IS RECORD（ <数据项 1> <数据类型>[NOT NULL[:=<表达式 1>]], <数据项 2> <数据类型>[NOT NULL[:=<表达式 2>]], …… <数据项 n> <数据类型>[NOT NULL[:=<表达式 n>]]）; <记录变量名> <记录类型名>;

  22. 记录类型简例 例 6-2将雇员信息定义为记录类型如下： declare type emp_record_type is record (v_ename emp.ename%type, v_job emp.job%type, v_sal emp.sal%type); emp_rec emp_record_type; begin select ename,job,sal into emp_rec from emp where empno=&eno; dbms_output.put_line(emp_rec.v_ename||':'|| emp_rec.v_job||':'||emp_rec.v_sal); end;

  23. 表类型 • 是一种复合数据类型，保存在数据缓冲区中的没有特别的存储次序的、可以离散存储的数据结构，它可以是一维的，也可以是二维的。 • 语法： TYPE <表类型名> IS TABLE OF <数据类型> INDEX BY BINARY_INTEGER; <表变量名> <表类型名>; 表类型名是用户定义的，数据类型是表中元素的数据类 型，表中所有元素的数据类型是相同的，索引变量缺省为 BINARY_INTEGER（范围介于-231-1~231-1之间）类型的变量，用于指定索引表元素下标的数据类型。

  24. 表类型简例 例6.3 索引表类型的定义 SQL> DECLARE TYPE ename_table_type IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER; Ename_table ename_table_type; BEGIN SELECT ename INTO ename_table(1) FROM emp WHERE empno=7902; Dbms_output.put_line('员工名：'|| ename_table(1)); END; /

  25. 数组类型 • 数组也是一种复合类型，与表不同的是声明了一个数组，就确定了数组中元素的数目。同时，数组存储时，其元素的次序是固定且连续的，而且索引变量从 1 开始一直到其定义的最大值为止。 • 语法如下： TYPE <数组类型名> IS VARRAY （<MAX_SIZE>）OF <数据类型>; <表变量名> <表类型名>; • 数组类型名是用户定义的，数据类型是数组中元素的数据类型，所有数组元素的数据类型是一致的，MAX_SIZE 指明数组元素个数的最大值。

  26. 引用类型 PL/SQL 语言中的引用类型是用户定义的指向某一数据缓冲区的指针，与 C 语言中的指针类似。游标即为 PL/SQL 语言的引用类型。

  27. 表达式 PL/SQL 语言常见的表达式分为： 算术表达式 字符表达式 关系表达式 逻辑表达式

  28. 算术表达式 算术表达式 算术表达式是由数值型常量、变量、函数和算术运算符组成的。算术表达式的计算结果是数值型数据，它使用的运算符主要包括：（）、∗∗、∗、/、+、－等，运算的优先次序为括号→乘方→乘除→加减。

  29. 字符表达式 字符表达式是由字符或字符串型常量、变量、函数和字符运算符组成，字符表达式的计算结果仍然是字符型。唯一的字符运算符是并置（‖），这个运算符将两个或者多个字符串连接在一起。 如果并置运算中的所有操作数是 CHAR 类型，那么表达式的结果也为 CHAR 类型。如果所有操作数都为 VARCHAR2 类型，那么表达式的结果就为 VARCHAR2 类型。 例如：’PL’‖’/SQL’的结果为’PL/SQL’。

  30. 关系表达式 由字符表达式或者算术表达式与关系运算符组成的。 格式： <表达式> <关系运算符> <表达式> 关系运算符两边表达式的数据类型必须一致，只有相同类型的数据才能比较。 关系表达式的运算结果为逻辑值，若关系表达式成立，结果为真（TRUE），否则为假（FALSE）。 关系运算符主要有如下六种： <、 >、=、<=、>=、!=。 谓词操作符 LIKE、BETWEEN 和 IN，也可以作为关系运算符。

  31. 逻辑表达式 逻辑表达式是由关系表达式和逻辑运算符组成。逻辑表达式的运算结果为逻辑值。逻辑运算符包括：NOT、OR、AND。逻辑运算符的运算优先次序为 NOT → AND → OR。逻辑表达式的一般格式为： <关系表达式> <逻辑运算符> <关系表达式> 关系表达式和逻辑表达式实际上都是布尔表达式，其值为布尔值（TRUE，FALSE 或者NULL）。

  32. 绑定变量 绑定变量也称为主机变量。这些变量在SQL*PLUS环境中声明，匿名块不带任何参数。 好处： 绑定变量可以作为参数传递给过程和参数。 能够重复利用执行计划。 声明绑定变量的语法是： VARIABLE variablename datatype 例：SQL> variable gno number

  33. Sql*plus 中使用绑定变量 sql> variable emp_no number; sql> exec :emp_no := 7900; PL/SQL 过程已成功完成。 sql> SELECT ename,sal,deptno from emp WHERE empno =:emp_no;

  34. 使用局部变量v_num和绑定变量g_num 绑定变量g_num在SQL*PLUS中用VARIABLE语句来声明。程序块用冒号前缀（：）引用。 局部变量不需使用冒号前缀。 SQL> variable g_num number SQL> declare 2 v_num number; 3 begin 4 v_num:=3; 5 :g_num:=v_num * 2; 6 end; 7 / SQL> print g_num

  35. PL/SQL中的替换变量 PL/SQL没有输入能力。通过“&”加标识符即为替换变量，通过替换变量可以在PL/SQL中进行输入，并可以方便地达到创建通用脚本的目的。 SQL> variable g_num number SQL> declare v_num number; begin v_num:=&p_num; :g_num:=v_num * 2; end; /

  36. PL/SQL中的替换变量 例：创建通用脚本，Employees表中插入数据。 SQL>insert into emp(empno,ename,sal,deptno) values (&empno ,&ename,&sal,&deptno); SQL> / 输入 empno 的值: 7811 输入 ename 的值: 'jamw' 输入 sal 的值: 3000 输入 deptno 的值: 30 已创建 1 行。

  37. 控制结构 PL/SQL 支持的流程控制结构： • 条件控制 • IF 语句 • CASE 语句 • 循环控制 • LOOP 循环 • WHILE 循环 • FOR 循环 • 顺序控制 • GOTO 语句 • NULL 语句

  38. 条件控制 2-1 IF 语句根据条件执行一系列语句，有三种形式：IF-THEN、IF-THEN-ELSE 和 IF-THEN-ELSIF DECLARE v_job VARCHAR2(10); v_sal number(6,2); BEGIN SELECT job,sal INTO v_job,v_sal FROM emp WHERE empno=&&no; IF v_job = ‘CLERK’ THEN UPDATE emp SET sal=v_sal+200 WHERE empno=&&no; ELSIF v_job= ‘SALESMAN’ THEN UPDATE emp SET sal=v_sal+100 WHERE empno=&&no; ELSE UPDATE emp SET sal=v_sal+500 WHERE empno=&&no; END IF; END;

  39. 条件控制 2-2 • CASE 语句用于根据单个变量或表达式与多个值进行比较 • 执行 CASE 语句前，先计算选择器的值 BEGIN CASE ‘&grade’ WHEN ’A’ THEN DBMS_OUTPUT.PUT_LINE(’优异’); WHEN ’B’ THEN DBMS_OUTPUT.PUT_LINE (优秀’); WHEN ’C’ THEN DBMS_OUTPUT.PUT_LINE (’良好’); WHEN ’D’ THEN DBMS_OUTPUT.PUT_LINE (’一般’); WHEN ’F’ THEN DBMS_OUTPUT.PUT_LINE (’较差’); ELSE DBMS_OUTPUT.PUT_LINE (’没有此成绩’); END CASE; END;

  40. 循环控制 • 循环控制用于重复执行一系列语句 • 循环控制语句包括： • LOOP、EXIT 和 EXIT WHEN • 循环控制的三种类型： • LOOP - 基本循环，无条件循环 • WHILE - 根据条件循环 • FOR - 循环固定的次数 LOOP sequence_of_statements END LOOP; WHILE condition LOOP sequence_of_statements END LOOP; FOR counter IN [REVERSE] value1..value2 LOOP sequence_of_statements END LOOP;

  41. 基本循环 例 使用基本循环。该例程当X值为1010时退出循环。 SQL> DECLARE X INT :=100; Y INT; BEGIN LOOP X:=X+10; EXIT WHEN X>1000; END LOOP; Y:=X; END; /

  42. WHILE循环 例使用WHILE循环。该例程当X值为1010时退出循环。 SQL> declare x number:=100; y number:=0; begin while x<=1000 loop x:=x+10; end loop; y:=x; end; /

  43. FOR循环 例使用FOR循环。X累计加10，循环10次后退出。 SQL> DECLARE x number:=100; y number:=0; begin FOR v_counter in 1..10 loop x:=x+10; END LOOP; y:=x; end;

  44. 任务1：不同条件修改员工信息使用顺序控制结构任务1：不同条件修改员工信息使用顺序控制结构 例6.10 使用GOTO语句。该例程当员工薪水小于800时转到update 标签处修改员工薪水提高100。否则转quit标签什么也不做 SQL>declare salary emp.sal%type; begin select sal into salary from emp where empno=7369; if salary < 800 then goto update; else goto quit; end if; <<update>> update emp set sal=salary + 100 where empno=7369; <<quit>> NULL; end; / • 顺序控制用于按顺序执行语句 • 顺序控制语句包括： • GOTO 语句 - 无条件地转到标签指定的语句 • NULL 语句 - 什么也不做的空语句

  45. 动态 SQL 动态 SQL 是指在PL/SQL程序执行时生成的 SQL 语句 编译程序对动态 SQL 不做处理，而是在程序运行时动态构造语句、对语句进行语法分析并执行 DDL 语句命令和会话控制语句不能在 PL/SQL 中直接使用，但是可以通过动态 SQL 来执行 执行动态 SQL 的语法： EXECUTE IMMEDIATE dynamic_sql_string [INTO define_variable_list] [USING bind_argument_list]; DECLARE sql_stmt VARCHAR2(200); emp_id NUMBER(4) := 7566; emp_rec emp%ROWTYPE; BEGIN EXECUTE IMMEDIATE ‘CREATE TABLE COM (id NUMBER,com NUMBER)’; sql_stmt := 'SELECT * FROM emp WHERE empno = :id'; EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id; END;

  46. 游标简介 2-1 Oracle服务器 数据缓冲区 执行 PL/SQL 程序 检索行 保存到游标中 提取行 一次处理一行

  47. 游标简介 2-2 逐行处理查询结果，以编程的方式访问数据 游标的类型： 游标类型 隐式游标 显式游标 REF游标 在 PL/SQL 程序中执行DML SQL 语句时自动创建隐式游标。 显式游标用于处理返回多行的查询。 REF 游标用于处理运行时才能确定的动态 SQL 查询的结果

  48. 隐式游标 4-1 在PL/SQL中使用DML语句时自动创建隐式游标 隐式游标自动声明、打开和关闭，其名为 SQL 通过检查隐式游标的属性可以获得最近执行的DML 语句的信息 隐式游标的属性有： %FOUND – SQL 语句影响了一行或多行时为 TRUE %NOTFOUND – SQL 语句没有影响任何行时为TRUE %ROWCOUNT – SQL 语句影响的行数 %ISOPEN - 游标是否打开，隐式游标始终为FALSE

  49. 隐式游标 4-2 SQL> SET SERVEROUTPUT ON SQL> BEGIN UPDATE toys SET toyprice=270 WHERE toyid= 'P005'; IF SQL%FOUND THEN DBMS_OUTPUT.PUT_LINE(‘表已更新'); END IF; END; / 只有在 DML 语句影响一行 或多行时，才返回 True

  50. 隐式游标 4-3 SQL> SET SERVEROUTPUT ON SQL> DECLARE v_TOYID TOYS.ID%type := '&TOYID'; v_TOYNAME TOYS.NAME%Type := '&TOYNAME'; BEGIN UPDATE TOYS SET NAME = v_TOYNAME WHERE toyid=v_TOYID; IF SQL%NOTFOUND THEN DBMS_OUTPUT.PUT_LINE('编号未找到。'); ELSE DBMS_OUTPUT.PUT_LINE(‘表已更新'); END IF; END; / 如果 DML 语句不影响任何行，则返回 True

More Related