PLSQL编程语言学习总结

来源：互联网发布：嵇康山涛知乎编辑：程序博客网时间：2024/05/22 14:39

下面这段话是百度百科对PLSQL的解释：

PL/SQL Developer是一个集成开发环境，专门开发面向Oracle数据库的应用。PL/SQL也是一种程序语言，叫做过程化SQL语言（Procedural Language/SQL）。PL/SQL是Oracle数据库对SQL语句的扩展。在普通SQL语句的使用上增加了编程语言的特点，所以PL/SQL把数据操作和查询语句组织在PL/SQL代码的过程性单元中，通过逻辑判断、循环等操作实现复杂的功能或者计算。PL/SQL 只有 Oracle 数据库有。 MySQL 目前不支持 PL/SQL 的。

我们接下来开始学习PL/SQL 数据库编程语言：

语言的格式如下：

  declare        -- 这部分声明变量       begin        --程序的执行部分       exception        --针对begin块抛出的异常进行处理       end;

   例子: 找出100号员工的工资
       -- 如果想要在命令窗口打印需要在执行前执行 set serveroutput on

       declare
       -- 这部分声明变量
           v_sal varchar2(20);--或v_sal emp.sal%type; //表示我声明的v_sal这个变量跟表emp中sal字段类型一致

       -- v_sal := 'hahah';--注意pl/sql中给变量赋值:=

       begin
       --程序的执行部分
       select sal into v_sal from emp where empno=7499;
       --打印出来就像java中的system.out.println();注意, 使用前要在plus执行set serveroutput on
       dbms_output.put_line(v_sal);
       --针对begin块抛出的异常进行处理
       end;

       -- 接着输入'/' 表示在命令窗口运行
       -- 或者直接在sql窗口运行

   声明一个类型用以封装变量,就像在JAVA中建一个类

       declare
        -- 声明一个记录类型,这个类型里有属性v_sal,v_name
        type emp_record is RECORD(
       v_sal emp.sal%type, --表示我声明的v_sal这个变量跟表emp中sal字段类型一致
       v_name emp.ename%type
        );
        -- 定义一个记录类型的变量
        v_emp_record emp_record;

         begin
        --程序的执行部分
        select sal,ename into v_emp_record from emp where empno=7499;
        dbms_output.put_line(v_emp_record.v_sal||','||v_emp_record.v_name);--打印出来就像java中的system.out.println();
        --针对begin块抛出的异常进行处理
       end;

   --更新表数据
   declare
       v_emp_id number(10);

   begin
       v_emp_id := 7499;
       --程序的执行部分
       update emp set sal = sal + 100 where empno = v_emp_id;

       --针对begin块抛出的异常进行处理
   end;


   流程控制
      其实就是if else 循环,跟JAVA中类似

      例子
        查询员工号为150的员工工资，如果大于10000则打印:sal>=10000
        否则如果工资大于5000 则打印‘sal>=5000 少于 10000’
        否则打印sal<10000

      用elsif

        declare
          v_sal emp.sal%type;
          v_temp varchar(20);

       begin

          --程序的执行部分
          select sal into v_sal from emp where empno = 7499;

          if v_sal >= 10000 then
            v_temp := 'sal>=10000';
          elsif v_sal >= 5000 then
            v_temp := '5000<= sal< 10000';
          else
            v_temp := 'sal<5000';
          end if;
          dbms_output.put_line('工资' || v_temp);

       end;

       使用case then实现

           declare
          v_sal emp.sal%type;
          v_temp varchar(20);

       begin

          --程序的执行部分
          select sal into v_sal from emp where empno = 7499;
          -- case只能跟固定值不能有表达式,所以
       -- TRUNC（number,[num_digits]）函数
       -- num_digits参数不指定时表示0 ，截取整数,不会四舍五入

          v_temp := case TRUNC(v_sal / 5000)
               when 0 --说明薪水少于5000
                   then 'sal<5000'
               when 1 -- 薪水大于或者等于5000 而且小于一万
                   then '5000<= sal< 10000'
               else 'sal >= 10000 '
           end;

               dbms_output.put_line(' 工资 '||v_temp);

          end;


   循环
       例子使用循环打印1-100

       declare
          --(1)放初始化条件
          v_i number(5) := 1;

       begin
          loop
            --(2) 开始循环
            dbms_output.put_line(v_i);
            --(4) 相当于java循环中的i++
            v_i := v_i + 1;
            --(3) 循环条件
            exit when v_i >= 100;

          end loop;
       end;

       使用while实现

       declare
          --(1)放初始化条件
          v_i number(5) := 1;

       begin
          while v_i <= 100 --(3) 循环条件
           loop
            --(2) 开始循环
            dbms_output.put_line(v_i);
            --(4) 相当于java循环中的i++
            v_i := v_i + 1;

          end loop;
       end;

       使用for实现
       declare

       begin
          for c in 1 .. 100 loop
            --in reverse 1..100 //表示反过来100-1
           -- goto mylable; --// GOTO 语句是命令程序无条件跳转到指定的标号
            dbms_output.put_line(c);
          end loop;
          --<<mylable>>
       -- dbms_output.put_line('111');--goto标签不能直接跟着end
       end;

   游标的使用
       * 用以处理多行记录有点类似java中Iterator迭代器

       例子打印出部门所有员工的工资
       declare
          v_sal   emp.sal%type; -- 工资变量
          v_empid emp.empno%type; --定义雇员变量
          --定义游标
          cursor emp_sal_cursor is
            select sal, empno from emp where DEPTNO = '30';
       begin
          --打开游标
          open emp_sal_cursor;
          --提取游标
          fetch emp_sal_cursor
            into v_sal, v_empid;
          while emp_sal_cursor%found loop
            -- %found判断是否还有值有就返回true
            dbms_output.put_line(v_empid || ',' || v_sal);
            fetch emp_sal_cursor
              into v_sal, v_empid;
          end loop;
          --关闭游标
          close emp_sal_cursor;
       end;


       **用for循环实现游标更简洁
       declare
          v_sal   emp.sal%type; -- 工资变量
          v_empid emp.empno%type; --定义雇员变量
          --定义游标
          cursor emp_sal_cursor is
            select sal, empno from emp where deptno = '30';
       begin
          for c in emp_sal_cursor loop
            v_sal :=c.sal;
            v_empid :=c.empno;
            dbms_output.put_line(v_empid || ',' || v_sal);
          end loop;
       end;

       例子利用游标调整员工工资
       0-5000       涨5%
       5000-10000       涨3%
       10000-15000      涨2%
       15000 -       涨1%

       declare
          v_sal   number(7, 2); -- 工资变量
          v_empid emp.empno%type; --定义雇员变量
          v_temp number(4, 2); --调整基数
          --定义游标
          cursor emp_sal_cursor is
            select sal, empno from emp;
       begin
          for c in emp_sal_cursor loop
            v_sal   := c.sal;
            v_empid := c.empno;
           -- dbms_output.put_line(v_empid || ',' || v_sal);
            if v_sal < 5000 then
              v_temp := 0.05;
            elsif 5000 <= v_sal and v_sal < 10000 then
              v_temp := 0.03;
            elsif 10000 <= v_sal and v_sal < 15000 then
              v_temp := 0.02;
            else
              v_temp := 0.01;
            end if;
            dbms_output.put_line(v_empid || ',' || v_sal);
            update emp set sal = sal * (1 + v_temp) where empno = v_empid;
            dbms_output.put_line(v_empid || ',' || v_sal);
          end loop;
       end;

   * 隐式游标
      例子更新指定ID的员工的工资,如果找不着该员工,则打印‘查无此人’

   begin
      update emp
         set sal = sal + 100 --涨一百块
       where empno = 100;

      if sql%notfound then
        dbms_output.put_line('查无此人'); -- where字句未找到数据时触发隐式游标属性SQL%NOTFOUND
      end if;
   end;

   异常错误处理
      * 预定义异常
       declare
          v_temp varchar(23);
       begin
          select empno into v_temp from emp;

       exception
          --捕捉异常
          when TOO_MANY_ROWS then -- 返回的是个集合，对我们v_temp来说太多
            dbms_output.put_line('返回的行数太多,请使用游标');

          when others then
            dbms_output.put_line('其他错误');
       end;

      *非预定义异常
       declare
          e_deleted_exception exception;
          pragma exception_init(e_deleted_exception, -2292); --将其定义好的异常情况，与标准的 ORACLE 错误联系起来

       begin
          delete from emp where empno = 100;

       exception
          --捕捉异常
          when e_deleted_exception then
            dbms_output.put_line('违反完整性的约束条件,所以不能删除该用户');

       end;

      * 用户自定义异常

          例子：查询指定用户的工资,如果此人工资大于1000, 那就抛出异常
          declare
          e_toohigh_sal_exception exception;
          v_sal emp.sal%type;
       begin
          select sal into v_sal from emp where empno = 100;
          if v_sal > 10000 then
            raise e_toohigh_sal_exception; --raise 相当于JAVA中的throw 抛出
          end if;
       exception
          --捕捉异常
          when e_toohigh_sal_exception then
            dbms_output.put_line('此人工资太高');
          when NO_DATA_FOUND then
            dbms_output.put_line('没有找到数据');
       end;




   存储函数与存储过程
        * 可以把 PL/SQL 程序存储在数据库中，并可以在任何地方来运行它。这样就叫存储过程或函数
        * 过程和函数统称为 PL/SQL 子程序，他们是被命名的 PL/SQL 块，均存储在数据库中，并
          通过输入、输出参数或输入/输出参数与其调用者交换信息。过程和函数的唯一区别是函数总向调
          用者返回数据，而过程则不返回数据。

        存储函数
       例子：返回一个helloworld的字符串

           create or replace function hello_world
                   return varchar2 is
           begin
              return 'helloworld';
           end;

           调用方式：1、在PLSQL程序中使用
                  2、select hello_world from dual;
       带参数的存储过程
           create or replace function hello_world(mystr varchar)
                   return varchar2 is
           begin
              return 'helloworld' || mystr;
           end;

           调用方式：1、在PLSQL程序中使用
                  2、select hello_world('我的口袋') from dual;

       例子：定义一个函数获取给定部门的工资总和，要求：部门号为参数，返回值为工资总和
           create or replace function getSal(deptId number) return number is
              v_sumsal number(10):= 0;
              cursor sal_cursor is
                select sal from emp where deptno = deptId;
           begin
              for c in sal_cursor loop
                v_sumsal := v_sumsal + c.sal;
              end loop;
              return v_sumsal;
           end;

           调用方式：1、在PLSQL程序中使用
                   declare
                       v_deptid:=100;
                   begin
                       dbms_output.put_line(getSal(v_deptid));
                   end;
                  2、select getSal(100) from dual;


       out型的参数
           之前的函数中只能返回一个值,PL/SQL程序可以通过out 型参数实现获取多个在函数中计算的值

           函数的参数以下标记
           in : 表示传递给函数的参数值在函数执行中不改变
           out :表示一个值在函数中进行运算并通过该参数传递给调用语句
           in out :混合型标记，表示传递给函数的值可以变化并传递给调用的语句

           如果省略以上三种标记则默认标记为in

           例子:要求获取指定部门工资总额和该部门员工总数

           create or replace function getSal(deptId number,total_sal out number)
                  return number
                  is
               v_sumsal number(10):=0;
               cursor sal_cursor is select sal from emp where deptno = deptId;
                  begin
               total_sal:=0;
               for c in sal_cursor loop
                  v_sumsal := v_sumsal + c.sal;
                  total_sal := total_sal + 1;
               end loop;
               return v_sumsal;
           end;

           调用：
               1、在PLSQL程序中使用
                      declare
                   v_deptid    number := 10;
                   v_total_sal number := 0;
                      begin
                   dbms_output.put_line(getSal(v_deptid, v_total_sal));
                      end;


           在获取函数的返回值同时也能获取到我们指定要计算的值

   存储过程
       关键字：procedure
       可以被多个应用程序调用,可以向存储过程传递参数,也可以向存储过程传回参数
       注意：存储过程没有返回值
       例子：定义一个存储过程，获取指定部门的工资总额

       由于没有返回值，所以要用到out标记参数

       create or replace procedure myproduce(deno number, total_sal out number)
       is
          cursor sal_cursor is
            select sal from emp where deptno = deno;
       begin
          dbms_output.put_line(deno);
          total_sal := 0;
          for c in sal_cursor loop
            dbms_output.put_line(c.sal);
            total_sal := total_sal + c.sal;
          end loop;
       end;


       1、ORACLE 使用 EXECUTE 语句来实现对存储过程的调用：
           EXEC[UTE] Procedure_name( parameter1, parameter2…);
       2、 declare
              v_deptid    number := 30;
              v_total_sal number := 0;
            begin
              myproduce(v_deptid, v_total_sal);
              dbms_output.put_line(v_total_sal || '111');
            end;

   触发器关键字：TRIGGER
       与存储过程不同,存储过程是通过其他程序调用，而触发器是由一个事件触发启动运行
       比如当一个插入事件发生时触发运行
       oracle的触发事件指的是对数据表进行insert,update,delete或者在视图上做类似操作,以及数据库的启动关闭等

       组成
           1、事件
           如：insert,update,delet
           2、触发时间
           触发事件之前还是之后？before,after
           3、触发器本身
           也就是触发事件发生后触发器要做什么事情
           4、触发频率
           该触发器内定义的程序执行次数
               statement
                   语句级触发器,指当触发某事件时,该触发器执行一次
               ROW
                   行级触发器,当触发事件时,对受到该操作影响的每一行数据,触发器都单独执行一次

       例子：要求，每更新一次数据表就输出helloworld


       create or replace trigger update_trigger
          after update on emp for each row
       begin
          dbms_output.put_line('helloworld');
       end

       当触发器被触发时，要使用被插入、更新或删除的记录中的列值，有时要使用操作前、后列的值.
       实现:
       :NEW 修饰符访问操作完成后列的值
       :OLD 修饰符访问操作完成前列的值

       create or replace trigger update_trigger
          after update on emp
          for each row --省略 FOR EACH ROW 选项时，BEFORE 和 AFTER 触发器为语句触发器
       begin
          dbms_output.put_line('旧的值' || :OLD.sal || ',新值' || :NEW.sal);

end;

1 0