数据库培训-PLSQL课件.ppt

1、1数据库培训PL/SQL基础电信网通BU2007年05月2培训内容 PL/SQL程序结构 基本语法要素 流程控制 事务处理 游标 异常处理 存储过程和函数 程序包 触发器3PL/SQL程序结构PL/SQL：过程化SQL语言 PL/SQL块语法 DECLARE-declaration statements BEGIN-executable statements EXCEPTION-exception statements END 4基本语法要素 常量 变量 符合数据类型变量 表达式 函数 ODS变量命名规范5常量 语句格式：常量名常量名 CONSTANT 类型标识符类型标识符 not null:

2、=值值;例：avc_acct_id CONSTANT VARCHAR2(5)NOT NULL:=AP001;6变量语句格式 变量名变量名 类型标识符类型标识符 not null:=值值;例：avc_serv_id VARCHAR2(5)NOT NULL:=SV001;基本数据类型 number int char()varchar()varchar2()long date boolean:ture false null 7复合数据类型变量 使用type%定义变量 avc_no dept.deptno%type;定义记录类型变量 使用rowtype%定义变量 一维表类型变量 多维表类型变量 8表达

3、式 算术表达式 逻辑表达式 字符表达式 关系表达式9函数 数字函数 字符函数 转换函数 日期函数 常规函数10ODS变量命名规范create or replace procedure p_tf_acct_income_mon(/*统计分析_收入情况（月）模块名称：p_tf_acct_income_mon 生成周期：按月执行 数据来源：FAS.TF_ACCT_ITEM 数据目标：统计分析_收入情况（月）（TF_ACCT_INCOME_MON）*/avc_cycleid varchar2,-数据周期 avc_lastcycleid varchar2,-上一次成功数据周期 an_return out

4、 number,-返回值(0:表示成功-1:表示失败)an_syserr out number,-系统错误号 an_record out number,-总记录数 avc_syserrtext out varchar2 -系统错误文本 )is n_point varchar2(10);vc_sql varchar2(4000);begin .end p_tf_acct_income_mon;11流程控制 条件控制条件控制 循环控制循环控制12条件控制条件控制If 条件条件1 then 语句段语句段1;Elsif 条件条件2 then if(条件条件4)then 语句段语句段2;end if;E

5、lse 语句段语句段3;End if;13循环控制循环控制 Loop循环循环 While 循环循环 For 循环循环14Loop循环循环Loop 循环语句循环语句1 If 条件语句条件语句 then exit；else 语句语句2 end if;End loop;15Loop循环循环Loop 循环语句循环语句1 语句语句2exit when 条件语句条件语句;End loop;16For 循环循环 for 循环变量循环变量 in reverse 循环上届循环上届.下届循环下届循环 loop 循环处理语句；循环处理语句；End loop;17事务处理 Commit 命令 用 set auto o

6、noff;来打开，关闭自动提交 Rollback 命令 Savepoint命令18游标 游标的作用 隐式游标 显示游标 游标属性 引用游标/动态游标19游标的作用 从数据库中提取出数据，以临时表的形式放在内存中。初始指向首记录，利用fetch移动指针，对游标中的数据进行处理，然后写到结果表中。20显示游标select语句上 使用显式游标，明确能访问结果集 FOR循环游标(常用的一种游标)转换函数 fetch游标 参数游标21FOR循环游标定义游标定义游标变量使用for循环来使用这个游标 前向游标 只能往一个方向走 效率很高declare -类型定义 cursor cc is select em

7、pno,ename,job,sal from emp where job=MANAGER;-定义一个游标变量 ccrec cc%rowtype;begin -for循环 for ccrec in cc loop dbms_output.put_line(ccrec.empno|-|ccrec.ename|-|ccrec.job|-|ccrec.sal);end loop;end;22fetch游标使用的时候 必须要明确的打开和关闭declare -类型定义 cursor cc is select empno,ename,job,sal from emp where job=MANAGER;-定

8、义一个游标变量 ccrec cc%rowtype;begin -打开游标 open cc;-loop循环 loop -提取一行数据到ccrec中 fetch cc into ccrec;-判断是否提取到值，没取到值就退出 -取到值cc%notfound 是false -取不到值cc%notfound 是true exit when cc%notfound;dbms_output.put_line(ccrec.empno|-|ccrec.ename|-|ccrec.job|-|ccrec.sal);end loop;-关闭 close cc;end;23游标属性 游标的属性4种%notfound

9、 fetch是否提到数据 没有true 提到false%found fetch是否提到数据 有true 没提到false%rowcount 已经取出的记录的条数%isopen 布尔值 游标是否打开declare -类型定义 cursor cc is select empno,ename,job,sal from emp where job=MANAGER;-定义一个游标变量 ccrec cc%rowtype;begin -打开游标 open cc;-loop循环 loop -提取一行数据到ccrec中 fetch cc into ccrec;-判断是否提取到值，没取到值就退出 -取到值cc%n

10、otfound 是false -取不到值cc%notfound 是true exit when(cc%notfound or cc%rowcount=3);dbms_output.put_line(cc%rowcount|-|ccrec.empno|-|ccrec.ename|-|ccrec.job|-|ccrec.sal);end loop;-关闭 close cc;end;24参数游标按部门编号的顺序输出部门经理的名字 declare -部门 cursor c1 is select deptno from dept;-参数游标c2,定义参数的时候 -只能指定类型,不能指定长度 -参数只能出

11、现在select语句=号的右侧 cursor c2(no number,pjob varchar2)is select emp.*from emp where deptno=no and job=pjob;c1rec c1%rowtype;c2rec c2%rowtype;-定义变量的时候要指定长度 v_job varchar2(20);begin -部门 for c1rec in c1 loop -参数在游标中使用 for c2rec in c2(c1rec.deptno,MANAGER)loop dbms_output.put_line(c1rec.deptno|-|c2rec.ename

12、);end loop;end loop;end;25综合例子题目 求购买的商品包括了顾客“Dennis”所购买商品的顾客（姓名）；create table purcase(productid number,customerid number)；create table customer(customerid number,name varchar(30)；思路：Dennis(A,B)别的顾客(A,B,C)(A,C)(B,C)C 26 declare -Dennis所购买的商品 cursor cur_dennis is select productid from purcase where cu

13、stomerid=(select customerid from customer where name=Dennis);-除Dennis以外的每个顾客 cursor cur_cust is select customerid from customer where name Dennis;-每个顾客购买的商品 cursor cur_prod(id varchar2)is select productid from purcase where customerid=id;j number;i number;rec_dennis cur_dennis%rowtype;rec_cust cur_c

14、ust%rowtype;rec_prod cur_prod%rowtype;avc_name varchar2(10);begin -顾客循环 for rec_cust in cur_cust loop i:=0;j:=0;for rec_dennis in cur_dennis loop i:=i+1;-每个顾客买的东西 for rec_prod in cur_prod(rec_cust.customerid)loop if(rec_prod.productid=rec_dennis.productid)then j:=j+1;end if;end loop;end loop;if(i=j)

15、then select name into avc_name from customer where customerid=rec_cust.customerid;DBMS_output.put_line(avc_name);end if;end loop;end;27隐式游标隐式游标也叫sql游标，是用来处理所有sql语句的环境区域指针；不能通过专门的语句来打开，PL/SQL隐式的打开sql游标处理完后自动关闭。单条sql语句所产生的结果集合 用关键字SQL表示隐式游标 4个属性%rowcount 影响的记录的行数 整数%found 影响到了记录 true%notfound 没有影响到记录

16、true%isopen 是否打开 布尔值 永远是false 多条sql语句 隐式游标SQL永远指的是最后一条sql语句的结果 主要使用在update 和 delete语句上 28游标变量/动态游标select语句是动态的 declare -定义一个类型(ref cursor)弱类型 type cur is ref cursor;-定义一个ref cursor类型的变量 cura cur;c1rec emp%rowtype;c2rec dept%rowtype;begin DBMS_output.put_line(输出员工);open cura for select*from emp;loop

17、fetch cura into c1rec;exit when cura%notfound;DBMS_output.put_line(c1rec.ename);end loop;DBMS_output.put_line(输出部门);open cura for select*from dept;loop fetch cura into c2rec;exit when cura%notfound;DBMS_output.put_line(c2rec.dname);end loop;close cura;end;29异常处理 系统预定义的异常 自定义异常 声明：异常名 EXCEPION；产生异常：r

18、aise语句 异常处理 30存储过程和函数没有名字的PL/SQL块(匿名)有名字的PL/SQL块(子程序-存储过程和函数)存储过程 create or replace procedure p1 as begin exception end;create or replace procedure p_jd as hello varchar2(20);begin select Hello World into hello from dual;dbms_output.put_line(hello);end;执行存储过程的方法 execute p_jd;(SQL*PLUS中SQL)begin p_jd

19、;end;31带参数的存储过程-输入参数in -不写in的参数都是输入参数 -根据部门编号查员工姓名 create or replace procedure p_getemp(no number)as cursor c1 is select*from emp where deptno=no;c1rec c1%rowtype;begin -no:=20;输入参数是不能赋值的 for c1rec in c1 loop dbms_output.put_line(c1rec.ename);end loop;end;32带参数的存储过程-输出参数out-根据部门编号查出部门的平均工资,返回平均工资的值-

20、in 输入(在procedure中是不能赋值的)-out 输出(在procedure中是能赋值的)-定义参数是不能指定长度的-定义变量是必须指定长度的 create or replace procedure p_getavgsal(no number,avgsal out number)-no 输入参数 -avgsal 输出参数 as aa varchar2(10);-变量 begin select avg(sal)into avgsal from emp where deptno=no;end;调用它只能使用PL/SQL块 declare av number;begin p_getavgsa

21、l(10,av);dbms_output.put_line(平均工资:|round(av,2);end;33带参数的存储过程-一个参数同时可以输入,也可以输出-输入输出参数 create or replace procedure p_getavgsal(n in out number)as begin select avg(sal)into n from emp where deptno=n;end;declare av number;begin av :=10;p_getavgsal(av);dbms_output.put_line(平均工资:|round(av,2);end;34带多个参数

22、的存储过程-带多个参数的存储过程 create or replace procedure p_getM(no number,pjob varchar2)as -参数游标c2,定义参数的时候 -只能指定类型,不能指定长度 -参数只能出现在select语句=号的右侧 cursor c2(no1 number,pjob1 varchar2)is select*from emp where deptno=no1 and job=pjob1;c2rec c2%rowtype;-定义变量的时候要指定长度 v_job varchar2(20);begin -参数在游标中使用 for c2rec in c2(

23、no,pjob)loop dbms_output.put_line(c2rec.deptno|-|c2rec.ename);end loop;end;调用方法:execute p_getm(10,MANAGER);-按位置 -no=10,pjob=MANAGER execute p_getm(pjob=MANAGER,no=10);-按参数的名字 来传值35 函数 必须要有返回值 只能返回一个值36函数例子-根据部门编号查出部门的平均工资,返回平均工资的值(利用函数)create or replace function f_getavgsal(no number)return number a

24、s avgsal number(7,2);begin select avg(sal)into avgsal from emp where deptno=no;-返回值 return avgsal;end;37一个函数返回2个值-带输出参数-每个部门的平均工资和工资总额-一个函数返回2个值create or replace function f_getavgsal(no number,sumsal out number)return number as avgsal number(7,2);begin -平均工资 select avg(sal)into avgsal from emp where

25、 deptno=no;-工资总额 select sum(sal)into sumsal from emp where deptno=no;-返回值 return avgsal;end;38函数的调用方法块调用 declare aa number;begin aa:=f_getavgsal(10);dbms_output.put_line(to_char(aa);end;SQL语句来调用(DML)select f_getavgsal(10)from dual;select deptno,f_getavgsal(deptno)from dept;含有修改语句，select语句是无法调用 creat

26、e or replace function f1 return number as update emp set comm=1000 where job=CLERK;return sql%rowcount;end;-select语句是无法调用它的,因为其中含有修改语句39程序包 PACKAGE用途:模块化-公司的员工的管理 1.增加一个员工 2.员工离职包中的变量是全局变量返回结果集合40PACKAGE的例子 create or replace package test_pack-包头 as procedure getname(eno number,enm out varchar2);func

27、tion f_get_name(eno number)return varchar2;end;create or replace package body test_pack-包体 as procedure getname(eno number,enm out varchar2)as begin select ename into enm from emp where empno=eno;end;function f_get_name(eno number)return varchar2 as n varchar2(20);begin select ename into n from emp

28、where empno=eno;return n;end;end;调用方法 declareenm varchar2(30);begin test_pack.getname(10,enm);end;41触发器用途:加强约束 条件 的 -希望在表中插入的数据的日期大于系统时间 Create table test(xh number(2)primary key,hdate date check(hdate sysdate);数据库无法建立的 Create table test(xh number(2)primary key,hdate date check(hdate to_date(2005050

29、1,yyyymmdd);触发器实现42触发器建立触发器的语法 create or replace trigger after/before insert/update/delete on for each row begin exception end;-触发器中的PL/SQL块(DML/tcl)可以写什么样的SQL语句呢？DML语句 别的都不能写(COMMIT/rollback都不能写)after/before 以后/以前 insert/update/delete 触发的SQL语句for each row 行级-语句级sql语句和触发器的Pl/SQL形成一个整体的事务43触发器例子-希望在表

30、中插入的数据的日期大于系统时间 Create table test(xh number(2)primary key,hdate date);create or replace trigger tr_test after insert on test for each row begin if:new.hdate=sysdate then raise_application_error(-20001,小于系统时间);end if;end;insert into test values(1,to_date(20050401,yyyymmdd);insert into test values(1,sysdate+1);