Oracle PL/SQL随堂笔记总结
2015-01-04 22:00 AlfredZhao 阅读(3043) 评论(0) 编辑 收藏 举报1.pl/sql编程
1.理解oracle的pl/sql的概念
2.掌握pl/sql编程技术（过程、函数、触发器）
pl/sql是标准sql语句的扩展
简介
1.过程、函数、触发器都是由pl/sql编写
2.过程、函数、触发器是在oracle中
3.pl/sql是非常强大的过程语言
4.过程、函数等可以在java程序被调用
学习必要性：
1.提高应用程序的性能
2.模块化的设计思想
3.减少网络传输量
4.提高安全性
不好的方面：
移植性差
pl/sql可以使用变量和逻辑控制语句
可编写:分页存储过程模块，订单处理存储过程模块，转账存储过程模块……
块：block 由三部分构成
定义部分，从declare开始，可选；
执行部分，从begin开始，必须；
例外处理部分，从exception开始，可选；
实例1-只包括执行部分的pl/sql块
set serveroutput on --打开输出选项 begin dbms_output.put_line('hello'); end; --说明：dbms_output是oracle所提供的包（类似于java的开发包），该包包含一些过程，put_line就是dbms_output包的一个过程。
实例2-包含定义部分和执行部分的pl/sql块
declare v_name varchar2(5); v_sal number(7,2); begin select ename,sal into v_name,v_sal from scott.emp where empno=&jingyu; dbms_output.put_line('员工名：'||v_name||' 员工薪水：'||v_sal); end; --如果返回值不惟一，需要用到参照变量；
实例3-包含定义部分，执行部分和例外处理部分
--例外处理部分，为了提高程序的健壮性，应该对可能的错误进行处理
oracle事先定义了一些例外，no_data_found就是找不到数据的例外。
上例就可以在end前加入如下异常处理部分代码：
exception when no_data_found then dbms_output.put_line('数据不存在，请重新输入！');
pl/sql编写规范
1.注释
单行注释 --
多行注释 /* */
2.标识符号的命名规范
1).定义变量时，用v_ 做前缀
2).定义常量时，用c_ 做前缀
3).定义游标时，用_cursor 做后缀
4).定义例外时，用e_ 做前缀
2.存储过程
a.最简单的存储过程：
create or replace procedure sp_pro1 is begin insert into mytest1 values('shunping','m123'); end;
b.调用存储过程：
exec sp_pro1;
c.存储过程示例：
1.编写一个存储过程，实现功能：可以输入雇员名，新工资，可以修改雇员的工资。
create or replace procedure sp_pro2(v_ename varchar2,v_newsal number) is begin update emp1 set sal=v_newsal where ename=v_ename; end;
2.演示java程序调用oracle的存储过程
pl/sql第二讲最后几分钟。
3.如何使用过程返回值
以后解决
3.函数
函数用于返回特定的数据，当建立函数时，在函数头部必须包含return子句，而在函数体内必须包含return语句返回的数据。
案例1：输入雇员的姓名，返回该雇员的年薪。
create function sp_fun1(spName varchar2) return number is yearSal number(7,2); begin select sal*12+nvl(comm,0)*12 into yearSal from emp1 where ename=spName; return yearSal; end;
sql>调用函数 var income number; call sp_fun1('SCOTT') into:income;
4.触发器
触发器是指隐含执行的存储过程。当定义一个触发器时，必须要指定触发的事件和触发的操作，常用的触发事件包括insert，update，delete语句，而触发操作实际就是一个pl/sql块。
5.包
包用于在逻辑上组合过程和函数，它由包规范和包体两部分组成。
实例1：创建包
create package sp_package is procedure update_sal(ename varchar2,newsal number); function annual_sal(name varchar2) return number; end;
--包的规范只包含了过程和函数的说明，没有过程和函数的实现代码。
包体用于实现包规范中的过程和函数
实例2：建立包体
create or replace package body sp_package is procedure update_sal(name varchar2,newSal number) is begin update emp1 set sal=newSal where ename=name; end; function annual_sal(name varchar2)return number is yearSal number; begin select sal*12+nvl(comm,0)*12 into yearSal from emp1 where ename=name; return yearSal; end; end;
实例3：如何调用包的过程和函数
调用过程：
call sp_package.update_sal('SMITH',133);
调用函数：
var abc number call sp_package.annual('SMITH') into:abc;
6.pl/sql基础 -定义并使用变量
在编写pl/sql程序时，可以定义变量和常量；在pl/sql程序中包括有：
①标量类型(scalar)
标量定义的案例
v_name varchar2(20); v_sal number(6,2); v_sal number(6,2):=5.4; v_hiredate date; v_valid boolean not null default false;
实例1：输入员工号，显示雇员姓名、工资、个人所得税（税率为0.03）。
declare c_taxRate number(3,2):=0.03; v_ename varchar2(5); v_sal number(7,2); v_taxSal number(7,2); begin select ename,sal into v_ename,v_sal from emp1 where empno=&no; v_taxSal:=v_sal*c_taxRate; dbms_output.put_line('雇员姓名：'||v_ename||' 工资：'||v_sal||' 个人所得税：'||v_taxSal); end;
ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小
当ename为MARTIN时，会报上述错误，解决方法，定义v_ename时，v_ename emp1.ename%type;
②复合类型(composite)
用于存放多个值的变量。主要包括这几种
1)pl/sql记录
类似于高级语言中的结构体（不知道结构体可以先简单理解成类）
实例1：
declare --定义一个pl/sql类型，emp_record_type，类型包含3个数据，分别是name，salary，title type emp_record_type is record (name emp1.ename%type,salary emp1.sal%type,title emp1.job%type); sp_record emp_record_type; begin select ename,sal,job into sp_record from emp1 where empno=&no; dbms_output.put_line('雇员姓名：'||sp_record.name||' 雇员工资'||sp_record.salary); end;
2)pl/sql表
相当于高级语言中的数组，但是需要注意的是在高级语言中数组的下标不能为负数，而pl/sql是可以为负数的，并且表元素的下标没有限制。
实例1：
declare
--定义一个pl/sql表类型,该类型用于存放emp1表中ename字段的这种数据 type emp_table_type is table of emp1.ename%type index by binary_integer; sp_table emp_table_type; begin select ename into sp_table(-1) from emp1 where empno=&no; dbms_output.put_line('名字是：'||sp_table(-1)); end;
3)嵌套表；
4)varray；
③参照类型(reference)
参照变量是指用于存放数值指针的变量，通过使用参照变量，可以使得应用程序共享相同对象，从而降低占用的空间。
在编写pl/sql时，可以使用游标变量（ref_cursor）和对象类型变量（ref_obj_type）两种参照变量类型。
1).游标变量（ref_cursor）
实例1：请使用pl/sql编写一个块，可以输入部门号，显示该部门所有员工的姓名及其工资。
--pl/sql参照变量 之游标变量 declare --定义一个游标类型 type sp_emp_cursor is ref cursor; --定义一个游标变量 test_cursor sp_emp_cursor; --定义变量 v_ename emp1.ename%type; v_sal emp1.sal%type; begin --将游标和select结合 open test_cursor for select ename,sal from emp1 where deptno=&no; --循环取出 loop fetch test_cursor into v_ename,v_sal; --判断何时退出循环 应该是test_cursor为空时退出。 exit when test_cursor%notfound; dbms_output.put_line('名字：'||v_ename||' 工资'||v_sal); end loop; end;
实例2：在实例1基础上，如果哪个员工的工资少于200元，就增加100元。
④lob(large object)；
7.pl/sql的进阶
控制结构
(1)使用if语句，
条件分支语句：if then；if then else；if then elsif then else；
案例1：编写一个过程，可以输入一个雇员名，如果该雇员的工资低于2000，就给该雇员工资增加10%。
--Method1： create procedure update_sal(name varchar2) is begin update emp1 set sal=1.1*sal where sal<2000 and ename=name; end;
--Method2： create or replace procedure update_sal2(name varchar2) is v_sal emp1.sal%type; begin select sal into v_sal from emp1 where ename=name; if v_sal<2000 then update emp1 set sal=1.1*sal where ename=name; --exec调用没有问题；但call调用有问题，在下次查询才会打印出来 dbms_output.put_line('此员工工资太低，已经给默认涨10%！'); end if; end;
案例2：编写一个过程，输入一个雇员名，如果他的补助不是0，就增加100.如果是0，就改成200；
create procedure update_comm(name varchar2) is v_comm emp1.comm%type; begin select comm into v_comm from emp1 where ename=name; if v_comm<>0 then update emp1 set comm=comm+100 where ename=name; else update emp1 set comm=200 where ename=name; end if; end;
案例3：编写一个过程，输入一个雇员编号，如果他是president，工资加1000，如果是manager，工资加500；如果是其他职位，工资加200；
create or replace procedure update_sal(spNo number) is --当上面参数写empNo时，实验是不可以的，过程可以成功创建，但是调用有问题。所以最好取不同名字。 v_job emp1.job%type; begin select job into v_job from emp1 where empno=spNo; if v_job='PRESIDENT' then update emp1 set sal=sal+1000 where empno=spNo; elsif v_job='MANAGER' then update emp1 set sal=sal+500 where empno=spNo; else update emp1 set sal=sal+200 where empno=spNo; end if; end;
(2)循环语句，
1.第一种循环结构：
loop
..
exit when v_num>=10;
end loop;
案例：user表中插入10条记录，编号1-10，姓名"小明"。
create or replace procedure loop_insert_data(name varchar2) is v_num number:=1; begin loop insert into user1 values(v_num,name); --判断退出条件 exit when v_num>=10; v_num:=v_num+1; end loop; end;
2.第二种循环结构：
while v_num<=20 loop
..
end loop;
案例：user表中插入10条记录，编号11-20，姓名"小亮"。
create or replace procedure loop_insert_data2(name varchar2) is v_num number:=11; begin while v_num<=20 loop insert into user1 values(v_num,name); v_num:=v_num+1; end loop; end;
3.第三种循环结构：for循环（不推荐使用，不够灵活）
begin for i in reverse 1..10 loop insert into user1 values (i,'顺平'); end loop; end;
(3)控制语句goto和null；
goto不推荐使用，降低了程序的可读性。
null 不执行任何操作，只是为了提高程序的可读性。
pl/sql进阶：综合案例之编写分页的过程。
1).无返回值的存储过程，向book表中添加书籍；
create table book(bookid number,bookname varchar2(50),publishhouse varchar2(50));
2).有返回值的存储过程，可以输入一个员工的编号，可以返回员工的姓名；
3).有返回值的存储过程(列表[结果集])，输入一个部门号，返回该部门所有员工的信息；
①创建一个包，定义一个类型test_cursor
②创建一个过程
③如何在java中调用
4).作业：有了上面的基础，相信大家可以完成分页的存储过程了，要求：可以输入表名，每页显示记录数，当前页。返回总记录数，总页数，和返回的结果集。
--1.建立一个包，定义一个游标的类型
create or replace package sp_package_fy as type sp_fy_cursor is ref cursor; end sp_package_fy;
--2.建立一个实现分页的存储过程_修正版
create or replace procedure sp_fenye(spTable in varchar2,spNum in number,spCurrentPage in number, spTotalRecord out number,spTotalPage out number,sp_cursor out sp_package_fy.sp_fy_cursor) is v_sql varchar2(1000); v_begin number:=spNum*(spCurrentPage-1)+1; v_end number:=spCurrentPage*spNum; begin --v_sql赋值 v_sql:='select * from (select rownum rn,t1.* from (select * from '||spTable||') t1 where rownum<='||v_end||') where rn>='||v_begin; --显示指定分页后详细的信息 open sp_cursor for v_sql; --显示总记录数 v_sql:='select count(*) from '||spTable; execute immediate v_sql into spTotalRecord; --显示总页数 if mod(spTotalRecord,spNum) <> 0 then spTotalPage:=spTotalRecord/spNum+1; else spTotalPage:=spTotalRecord/spNum; end if; --关闭游标 --close sp_cursor; end; --在java程序中调用分页存储过程。以后学java时再分析。
例外处理
例外的分类:Oracle将例外分为预定义例外，非预定义例外和自定义例外3类。
(1)预定义例外：定义了oracle常见的错误；
data_not_found
case_not_found
cursor_already_open
invalid number
too_many_rows
zero_divide
logon_denied
timeout_on_resourse
(2)非预定义例外：用于处理预定义例外不能处理的例外；
略
(3)自定义例外：用于处理与oracle错误无关的其他情况。
实例：编写一个存储过程，输入一个名字，工资加1000，如果没有该人，抛出自定义例外。
8.oracle的视图
视图是一个虚拟表，其内容由查询定义，同真实的表一样，视图包含一系列带有名称的行和列数据。但是视图并不在数据库中以存储的数据集形式存在，行和列数据来自由定义视图的查询所引用的表。
视图与表的区别
①表需要占用磁盘空间，视图不需要；
②视图不能添加索引；
③使用视图可以简化复杂查询；（比如学生选课系统）
④视图有利于提高安全性；（比如不同用户查看不同视图）
创建只读视图：create or replace view 视图名 as select 语句 with read only;
删除视图：drop view 视图名;
