代码改变世界

Oracle PL/SQL随堂笔记总结

2015-01-04 22:00  AlfredZhao  阅读(2827)  评论(0编辑  收藏  举报

1.pl/sql编程

2.存储过程

3.函数

4.触发器

5.包

6.pl/sql基础 -定义并使用变量

7.pl/sql的进阶

8.oracle的视图

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 视图名;