1 / 33

PL/SQL

PL/SQL. PL/SQL. PL/SQL:Procedural Language/SQL 目的：弥补交互式 SQL 的不足，支持过程化 应用： 编写存储过程（ Procedure ） 存储过程由用户、应用程序或触发器显式调用 编写触发器（ Trigger ） 触发器则是当触发事件发生时由系统触发执行. PL/SQL 的块结构. 定义部分 DECLARE …… 变量，常量 游标 异常等 执行部分 BEGIN …… SQL 语句、流程控制语句 EXCEPTION …… 异常处理部分 END;. 例题 1.

latifah-warren
Download Presentation

PL/SQL

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. PL/SQL

  2. PL/SQL PL/SQL:Procedural Language/SQL 目的：弥补交互式SQL的不足，支持过程化 应用： 编写存储过程（Procedure） 存储过程由用户、应用程序或触发器显式调用 编写触发器（Trigger） 触发器则是当触发事件发生时由系统触发执行

  3. PL/SQL的块结构 定义部分 DECLARE ……变量，常量 游标 异常等 执行部分 BEGIN ……SQL 语句、流程控制语句 EXCEPTION ……异常处理部分 END;

  4. 例题1 向关系Student中插入10个学生，学号从1001-1010 DECLARE sno INT;

  5. 例题1（续） BEGIN sno:=1001; WHILE sno<1011 LOOP Insert into Student(Sno) Values(to_char(sno，’9999’)); sno:=sno+1; END LOOP; END;

  6. PL/SQL 基本语法结构（1） • 说明：以KingbaseES4.0为例 • 变量、常量定义 • identifier [CONSTANT] datatype [NOT NULL] [:= | DEFAULT expr]; • 举例 Declare birthdayDATE; age int NOT NULL := 27; name VARCHAR (13) := 'Levi'; magic CONSTANT int := 77;--常量 valid BOOLEAN NOT NULL := TRUE; • 注意事项 • 定义的常量必须有初值，且不能改变 • 变量缺省初值为NULL • 不支持列表形式的定义 age, empno SMALLINT;

  7. PL/SQL 基本语法结构（2） • 表达式注意事项 • 连接字符 'Renmin' || 'University‘ • KingbaseES的存储过程在处理空值时遵循如下规则： • 涉及空值的比较结果为FALSE； • 涉及空值的加减乘除运算结果为NULL； • 举例 a := 100; b := NULL; IF a != b THEN < SequenceOfStatements >;--会执行吗？？ END IF;

  8. PL/SQL 基本语法结构（3） • 控制结构 • 条件语句 IF < ConditionExpression > THEN SequenceOfStatements1 > END IF; IF < ConditionExpression > THEN SequenceOfStatements1 > ELSE < SequenceOfStatements2 > END IF; • 只有在条件为假或NULL时，ELSE后的语句序列才会被执行

  9. PL/SQL 基本语法结构（4） 例如：检查学生是否在注册表中，若在，则登陆次数增加一次，否则把该学生插入注册表中 create table mylog( who varchar (30), logon_num int );

  10. PL/SQL 基本语法结构（5） DECLARE cnt int; user VARCHAR(20) := 'Levi'; BEGIN select count(*) into cnt from mylog where who = user; if cnt > 0 then update mylog set logon_num = logon_num + 1 where who = user; else insert into mylog values(user, 1); end if; end;

  11. PL/SQL 基本语法结构（6） create table number_table( num int ); • 循环结构 • 简单的Loop • While-Loop • For-Loop • 退出语句（与循环结构搭配） • Exit --强制无条件退出循环 • Exit－When --满足条件退出循环 • 举例

  12. PL/SQL 基本语法结构（7） • 举例： 简单Loop • Create or replaceprocedure loop1() as DECLARE i int := 1; BEGIN LOOP INSERT INTO number_table VALUES(i); i := i + 1; EXIT WHEN i > 10; END LOOP; END; • Perform procedure loop1();

  13. PL/SQL 基本语法结构（8） • 举例： While-Loop • Create or replace procedure loop2() as DECLARE TEN int:=10; i int :=1; BEGIN WHILE i <= TEN LOOP INSERT INTO number_table VALUES(i); i := i + 1; END LOOP; END; • Perform procedure loop2();

  14. PL/SQL 基本语法结构（9） • 举例： For-Loop • Create or replace procedureloop3() as DECLARE i int; BEGIN FOR i IN 1..10 LOOP INSERT INTO number_table VALUES(i); END LOOP; END; • Perform procedure loop3();

  15. PL/SQL 基本语法结构（10） • 异常处理 • RAISE { NOTICE | EXCEPTION } FormatString [, SimpleVariableName [, ... n]]; • 举例： • CREATE TABLE testRaise(i INT);  • CREATE PROCEDURE TestRaise(i INT) AS BEGIN INSERT INTO testRaise VALUES(i); IF 0 < i AND i < 10 THEN --下面的％指代变量i RAISE NOTICE 'your input value "%" is valid.',i; ELSE RAISE EXCEPTION 'your input value "%" is invalid.',i; END IF; INSERT INTO testRaise VALUES(i); END;

  16. PL/SQL 基本语法结构（11） • 游标的使用：类似与ESQL中的定义和使用 • CREATE TABLE emp(emp_no CHAR(10), salary INT); • CREATE PROCEDURE proc_open() AS DECLARE Para1 CHAR(10); Para2 INT; CURSOR mycursorFOR --后面紧跟SQL语句 SELECT emp_no, salary FROM emp; BEGIN OPEN mycursor; LOOP FETCH mycursor INTO para1,para2; EXIT WHEN mycursor%NOTFOUND; END LOOP; CLOSE mycursor; END;

  17. PL/SQL 基本语法结构（12） • KingbaseES中游标的状态判断 • CursorName%ISOPEN • 获取游标的属性，查看游标是否打开。 • CursorName%NOTFOUND • 获取游标的属性，对于声明的游标，查看上次fetch语句是否取到数据。 • 存储 过程中调用其它存储过程 • PERFORM ProcedureName( < ExpressionList >);

  18. 综合例子：把一个表中的数据复制到另一个表中综合例子：把一个表中的数据复制到另一个表中 CREATE PROCEDURE proc_isopen() AS DECLARE c_no CHAR(4); c_name CHAR(20); CURSOR mycursor FOR SELECT lno,lname FROM leader; BEGIN OPEN mycursor; IF mycursor%ISOPEN THEN LOOP FETCH mycursor INTO c_no,c_name; EXIT WHEN (mycursor%NOTFOUND); INSERT INTO temp vALUES(c_no,c_name); END LOOP; END IF; CLOSE mycursor; END;

  19. 如何在存储过程中应用PL/SQL 创建Procedure： CREATE [ OR REPLACE ] PROCEDURE ProcedureName ( [< ParameterList >[, ...n]] )AS [DECLARE [< VariableDeclaration >] [< CursorDeclaration >]] BEGIN < SequenceOfStatements >END; 执行Procedure： PERFORM PROCEDURE ProcedureName( [< ExpressionList >] );

  20. 例1(续) 创建存储过程 CREATE OR REPLACE PROCEDURE Insertsno() AS DECLARE snum INT; BEGIN snum:=1001; WHILE snum<1011 LOOP Insert into Student(Sno) Values(snum); snum:=snum+1; END LOOP; END; 执行存储过程 PERFORM PROCEDURE Insertsno() ；

  21. 存储过程的优点 • 执行效率高 • 传统SQL解释执行，而存储过程经编译和优化后存储在服务器中。因此提供了在服务器端快速执行SQL的有效途径。 • 降低客户端和服务器的网络流量 • 方便实施企业规则。

  22. 存储过程的定义（1） • KingbaseES 中的定义形式 CREATE [ OR REPLACE ] PROCEDURE ProcedureName ( [< ParameterList >[, ...n]] ) AS [DECLARE [< VariableDeclaration >] [< CursorDeclaration >] ] BEGIN < SequenceOfStatements > END;

  23. 存储过程的定义（2） • 参数定义 {[IN | OUT | INOUT] ParameterName < DataType >} [, ...n] • 使用说明： IN，OUT，INOUT参数模式，定义了形式参数的行为。IN将值传给被调用的子程序; OUT参数将返回值传给调用者; INOUT参数将初始值传给被调用的子程序，并将更新后的值返回给调用者; 缺省为IN类型。注意，OUT 参数在存储过程执行开始时，自动初始化为NULL值，所以OUT参数参与存储过程的运算，首先需要赋值。

  24. 存储过程的定义（4） 执行Procedure： PERFORM PROCEDURE ProcedureName( [< ExpressionList >] ); 删除 procedure： Drop PROCEDURE ProcedureName( [< ExpressionList >] );

  25. 创建触发器（1） • CREATE TRIGGER TriggerName { BEFORE | AFTER } <DmlEventClause > { < ExecuteProcedureStatement> | AS < PlsqlBlock > }; • 意义： • BEFORE 表示在触发事件之前执行触发器。 • AFTER 表示在触发事件之后执行触发器. • DmlEventClause 表示触发事件，即激活触发器的DML语句。 • < DmlEventClause > ::={ DELETE | INSERT | UPDATE } [OR ...n]ON TableName FOR EACH { ROW | STATEMENT }

  26. Trigger的作用和意义 • 提供保证数据完整性的方法 • 维护数据完整性 • 提高共享度，提高效率 • 把多个应用程序访问数据库遵循的规则通过触发器实现，便于推行规则的实施

  27. 操作 new表 old表 Insert 存放新插入的新行 delete 存放被插入的行 Update 存放要更新的新行 存放更新前的行 Trigger中的临时专用表 • DBMS为每个触发器建立两个临时专用表 • Kingbase： new表和old表 • SQLServer：inserted表和deleted表

  28. Trigger中的after和before • S(sno,sname,sage,scsum)、SC(sno,sco,grade)、C(cno,cname) • 例1：根据学生选课信息自动修改S表中的scnum • 例2：插入选课记录前检查该学生是否在S的sno表中

  29. 创建触发器说明（2） • 说明： • a.      DELETE 当从TableName中删除记录时激活触发器。 • b.      INSERT 当向TableName中插入记录时激活触发器。 • c.      UPDATE 当TableName中的任意column值发生改变时激活触发器。 • d.      OR 在指定对象上设置多个激活事件。

  30. 创建触发器说明（2） • TableName 确定创建的触发器所在的表名。 • FOR EACH ROW 表示触发器为行级触发器，即对于触发语句影响到的每条记录都执行一次触发器动作。 • FOR EACH STATEMENT 表示触发器为语句级触发器，即只要相关触发语句执行便激活触发器进行执行。 • 执行存储过程语句 PERFORM PROCEDURE ProcedureName ()

  31. 触发器举例(1) • CREATE TABLE tab1(col int); CREATE TABLE tab2(col int); INSERT INTO tab1 VALUES(10); INSERT INTO tab1 VALUES(20); • 行级触发 • CREATE TRIGGER insert_salary_changes BEFORE UPDATE ON tab1 FOR EACH ROW AS DECLARE valuediff INT; BEGIN valuediff := new.col - old.col; INSERT INTO tab2 VALUES(valuediff); END; • 执行：UPDATE tab1 SET col = 100;

  32. 触发器举例(2) • CREATE TABLE tab1(col int); CREATE TABLE tab2(col char(15)); INSERT INTO tab1 VALUES(10); INSERT INTO tab1 VALUES(20); • CREATE PROCEDURE InsertInTab() AS BEGIN INSERT INTO tab2 VALUES('tab1 deleted!'); END; • CREATE TRIGGER MyTrigger1 AFTER DELETE ON tab1 FOR EACH ROW EXECUTE PROCEDURE InsertInTab(); • 执行 delete from tab1；结果如何？

  33. 触发器举例(3) • 语句级触发 CREATE TRIGGER MyTrigger1 AFTER DELETE ON tab1 FOR EACH STATEMENT EXECUTE PROCEDURE InsertInTab(); • 执行 delete from tab1；结合例子2，结果如何？

More Related