oracle 第五讲PLSQL.docx

1、oracle 第五讲PLSQLPL/SQL PL/SQL 是过程语言(Procedural Language)与结构化查询语言(SQL)结合而成的编程语言 PL/SQL 是对 SQL 的扩展 支持多种数据类型，如大对象和集合类型，可使用条件和循环等控制结构 可用于创建存储过程、触发器和程序包，给SQL语句的执行添加程序逻辑 PL/SQL程序块的总体结构declare （可选） 定义语句段 begin 执行语句段（还可以包含子程序块） exception (可选) 异常处理语句段 end; 注意：sqlplus中编辑代码块 可以使用ed命令切换到afiedt.buf中编辑 常量 变量常量名 co

2、nstant 类型标识符:=值; declare name constant varchar2(20):=东方不败; PI constant number(5,2):=3.146;begin dbms_output.put_line(name| |PI);-dbms_output内置程序包 用于控制输出end; -注意；结尾set serveroutput on 设置输出/* 这是多行注释 变量赋值方式 (1) := (2) select. into语句 返回结果要求有且只有一条记录*/declare myname varchar2(20); mydate date; mynum number

3、(8):=1000;begin myname:=东方不败; select hiredate into mydate from emp where empno=7788; dbms_output.put_line(myname|-|mydate);end; pl/sql常用数据类型 数字类型number binary_integer(带符号整数 -231-1-231-1)PLS_integer (带符号整数 -231-231,运算速度快) 字符类型 char varchar2 raw long/long raw 日期类型DateTimestamp 布尔类型 boolean (true false

4、 null) lob类型 （4G） BLOB 将大型二进制对象存储在数据库中 CLOB 将大型字符数据存储在数据库中 NCLOB 存储大型UNICODE字符数据 BFILE 将大型二进制对象存储在操作系统文件中-BFILE类型演示create table test_bfile( file_name varchar2(100), book_file bfile);-创建目录create or replace directory mydir as F:MyDocumentsORACLEoracle_accpPX_课件files; insert into test_bfile values(文本文件

5、,bfilename(MYDIR,book.txt);-目录名大写commit;-blob类型演示 create table test_blob( file_name varchar2(20), myimage blob);-插入数据declare v_bfile bfile; v_blob blob;begin -blob插入记录，先使blob为空 empty_blob() 表示设置blob类型为空 insert into test_blob values(fish,empty_blob() return myimage into v_blob ; v_bfile:=bfilename(MY

6、DIR,fish.gif);-读取文件到bfile中(指定目录名和文件名) dbms_lob.open(v_bfile,dbms_lob.file_readonly); -以只读方式打开bfile类型变量 dbms_lob.loadfromfile(v_blob,v_bfile,dbms_lob.getlength(v_bfile);-加载bfile指向的文件字节到blob变量中 dbms_lob.close(v_bfile);-关闭bfile commit;end; -CLOB演示create table test_clob(textid number(10),myclob clob);-插

7、入数据insert into test_clob values(100,秋天地上的小草渐渐枯黄，落叶树的叶子也开始变红或变黄，并随风飘落下来。桂花飘香，菊花争艳，一串红、鸡冠花木芙蓉等一些花儿也开放了蒲公英结出了白绒毛似的“小伞兵”，随风飘散，大地呈现出美丽的秋色。);commit;-读取clob数据select myclob from test_clob where textId=100; -通过dbms_lob 程序包读取clob数据declare v_clob clob; v_txt varchar2(3000); amount integer; -要读取的字符数 offset inte

8、ger; -起始位置begin select myclob into v_clob from test_clob where TEXTID=100; amount:=1000; offset:=1; dbms_lob.read(v_clob,amount,offset,v_txt);-读取clob变量到字符变量中 要读取的字符数及起始位置必须是变量 dbms_output.put_line(v_txt);end; 属性类型%type引用其他变量的类型或数据表中的字段的数据类型来声明变量的类型声明: 变量名 表.字段名%type;declare mydate scott.emp.hiredate

9、%type;begin select hiredate into mydate from emp where empno=7788; dbms_output.put_line(mydate);end;%rowtype 表示表中一行记录的类型声明:变量名 表%rowtype;declare mytable scott.emp%rowtype;begin select * into mytable from scott.emp where empno=7788 ; dbms_output.put_line(mytable.ename| |mytable.sal);end;%type与%rowtyp

10、e区别： 使用%type定义变量： 变量名 数据表.列名%type 使用%rowtype定义变量： 变量名 数据表%rowtype 引用%rowtype定义的变量时， 使用：变量名.列名 运算符及表达式字符表达式字符运算符 |关系表达式 大于 = 等于（不是赋值运算符:=）like in = 大于等于 != 不等于 between and逻辑表达式 优先次序为：NOT、AND、ORNOT：逻辑非 OR：逻辑或 AND：逻辑与 流程控制 /* 条件控制 1. if.then.else. end if;*/declare num integer; inputno number(10);begin

11、inputno :=&请输入; select sal into num from scott.emp where empno=inputno; if(num2000) then dbms_output.put_line(薪水高于2000); else dbms_output.put_line(薪水低于2000); end if;end;2.if.then.elsif.then. else.end if; declare num integer; inputno number(10);begin inputno :=&请输入; select sal into num from scott.emp

12、 where empno=inputno; if(num=2000 and num=3000) then dbms_output.put_line(薪水在3000-2000之间); else dbms_output.put_line(薪水高于3000); end if;end;3.if嵌套条件控制 语法结构： if 条件1 then if 条件2 then 语句段1; else 语句段2; end if; else 语句段3;end if;case语句 写法有两种-写法1case 字段名 when 常量 then 赋值表达式; when . then . else . end case;-目标

13、 给各部门员工加薪declare v_deptno number:=10; v_sal number;begin case v_deptno when 10 then v_sal:=1; when 20 then v_sal:=2; else v_sal:=3; end case; update scott.emp set sal=sal+v_sal ; commit;end;-写法2 case when 条件表达式 then 赋值表达式; when . then . else . end case;-目标 使用case 语句统计员工薪水等级select ename,sal,case when

14、 sal=2000 and sal=3000 and sal=7 and v_t=11 and v_t=13 and v_t17 then v_d:=下午; else v_d:=晚上; end case; dbms_output.put_line(v_d);end; 循环控制 1loopexitend loop循环控制：declare v_i int:=1;begin loop v_i:=v_i+1; if(v_i=20) then exit; -退出循环 另外：continue 跳出当次循环 return无条件退出代码块 end if; dbms_output.put_line(v_i);

15、end loop; end;2. loop.exit when.end loop循环控制 declare v_i int:=1;begin loop v_i:=v_i+1; exit when v_i=20; dbms_output.put_line(v_i); end loop; end;3 while.loop.end loop循环控制 -九九乘法表declare v_i number:=1; v_j number;begin while(v_iv_i; end loop; dbms_output.put_line(); v_i:=v_i+1; end loop;end;/*4. for

16、i in n.m loop.end loop; for 循环变量 in reverse 循环下界.循环上界 loop 循环处理语句段; end loop; */ declare v_sum number:=1;begin for i in 1.5 loop v_sum:=v_sum*i; end loop; dbms_output.put_line(阶乘结果:|v_sum);end;-reversebegin for i in reverse 1.10 loop dbms_output.put_line(i); end loop;end; 顺序控制 GOTO语句 无条件跳转的标签指定的语句 ,

17、标签: -pl/sql块中唯一 不能跳转到if、case、loop语句NULL语句 什么也不做，只是转到下一个语句，对于有些语法至少需要一个可执行语句，但又不需要执行任何操作的情况下使用;declare v_int number(2);begin v_int:=&input; if(v_int=1) then goto TOUPDATE; else goto QUIT; end if; dbms_output.put_line(GOTO跳转到这); NULL;end; 动态 SQL 指在PL/SQL程序执行时生成的 SQL 语句 DDL 语句命令和会话控制语句不能在 PL/SQL 中直接使用，

18、但是可以通过动态 SQL 来执行DDLexecute immediate 实现DDLdeclare v_ddl varchar2(200);begin v_ddl:=create table mytable as select ename,sal from emp; execute immediate v_ddl; dbms_output.put_line(mytable表已建);end;execute immediate 返回单行记录 execute immediate 查询语句 into 行数据类型 using 对应占位符变量 declare v_id emp.empno%type; v_

19、emp emp%rowtype; v_sql varchar2(100);begin v_id:=7788; v_sql:=select * from emp where empno=:id;-id为占位符 execute immediate v_sql into v_emp using v_id; dbms_output.put_line(v_emp.ename| |v_emp.sal);end; 异常处理 异常处理就是针对错误进行处理的程序段 分为系统预定义异常处理和自定义异常处理两部分系统预定义异常处理 异常名称 描述ACCESS_INTO_NULL 未初始化对象CURSOR_ALREA

20、DY_OPEN 试图打开已经打开的游标ZERO_DIVIDE 除数为零NO_DATA_FOUND 无返回记录INVALID_NUMBER 字符串转换为数字失败VALUE_ERROR 转换、截断、大小约束错误TOO_MANY_ROWS 执行select into语句返回多行记录OTHERS 其他异常例1:declare v_name emp.ename%type;begin select ename into v_name from emp where empno=&emp_no; dbms_output.put_line(员工姓名:|v_name); exception when no_dat

21、a_found then dbms_output.put_line(无记录返回);end;例2:-测试数据insert into dept values(50,技术部,我的公司);insert into emp(empno,ename,job,sal,deptno) values(8888,东方不败,保镖,9000,50);commit;declare v_name varchar2(10);begin select ename into v_name from scott.emp where deptno=&v_deptno; raise INVALID_NUMBER; -raise 显式引

22、发INVALID_NUMBER异常 dbms_output.put_line(正常执行); exception when TOO_MANY_ROWS then dbms_output.put_line(此处不能返回多行记录); when others then dbms_output.put_line(扑获其他异常);end;自定义异常处理1定义异常处理 异常名 exception;2引发异常处理 raise 异常名;3处理异常exceptionwhen 异常名1 then异常处理语句段1;when 异常名2 then异常处理语句段2;declare sal_exception excepti

23、on; -声明异常 v_emp scott.emp%rowtype;begin select * into v_emp from scott.emp where empno=&v_empno; if(v_emp.sal1000) then raise sal_exception; -显式引发异常 end if; dbms_output.put_line(v_emp.ename| 薪水:$|v_emp.sal); exception when sal_exception then -异常处理 raise_application_error(-20001,薪水太少哦.);-使用系统过程引发异常 错误编号为-20000到-20999之间的负整数end;