数据库培训-PLSQL.ppt

数据库培训 PL/SQL基础,电信网通BU 2007年05月,2,培训内容,PL/SQL程序结构 基本语法要素 流程控制 事务处理 游标 异常处理 存储过程和函数 程序包 触发器,3,PL/SQL程序结构,PL/SQL： 过程化SQL语言 PL/SQL块语法 DECLARE -declaration statements BEGIN -executable statements EXCEPTION -exception statements END,4,基本语法要素,常量 变量 符合数据类型变量 表达式 函数 ODS变量命名规范,5,常量,语句格式： 常量名 CONSTANT 类型标识符 not null := 值 ; 例：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,表达式,算术表达式 逻辑表达式 字符表达式 关系表达式,9,函数,数字函数 字符函数 转换函数 日期函数 常规函数,10,ODS变量命名规范,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 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; Else 语句段3; End if ;,13,循环控制,Loop循环 While 循环 For 循环,14,Loop循环,Loop 循环语句1 If 条件语句 then exit； else 语句2 end if; End loop;,15,Loop循环,Loop 循环语句1 语句2 exit when 条件语句 ; End loop;,16,For 循环,for 循环变量 in reverse 循环上届下届循环 loop 循环处理语句； End loop ;,17,事务处理,Commit 命令 用 set auto onoff; 来打开，关闭自动提交 Rollback 命令 Savepoint命令,18,游标,游标的作用 隐式游标 显示游标 游标属性 引用游标/动态游标,19,游标的作用,从数据库中提取出数据，以临时表的形式放在内存中。初始指向首记录，利用fetch移动指针，对游标中的数据进行处理，然后写到结果表中。,20,显示游标,select语句上 使用显式游标，明确能访问结果集 FOR循环游标 (常用的一种游标)转换函数 fetch游标 参数游标,21,FOR循环游标,定义游标 定义游标变量 使用for循环来使用这个游标 前向游标 只能往一个方向走 效率很高 declare -类型定义 cursor cc is select empno,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;,22,fetch游标,使用的时候 必须要明确的打开和关闭 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%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 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%notfound 是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,定义参数的时候 -只能指定类型,不能指定长度 -参数只能出现在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); 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 customerid=( 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_cust%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_ductid = rec_ductid) then j := j + 1; end if; end loop; end loop; if (i=j) 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 没有影响到记录 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 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； 产生异常：raise语句 异常处理,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; 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 -根据部门编号查出部门的平均工资,返回平均工资的值 - 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_getavgsal(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,带多个参数的存储过程,-带多个参数的存储过程 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(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 as 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 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语句是无法调用 create or replace function f1 return number as update emp set comm = 1000 where job=CLERK; return sql%rowcount; end; -select语句是无法调用它的,因为其中含有修改语句,39,程序包,PACKAGE用途: 模块化 -公司的员工的管理 1.增加一个员工 2.员工离职 包中的变量是全局变量 返回结果集合,40,PACKAGE的例子,create or replace package test_pack -包头 as procedure getname(eno number,enm out varchar2); function 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 where empno = eno; return n;