Oracle PLSQL编程

落日映苍穹つ 2023-10-16 09:07 77阅读 0赞

#### 文章目录 ####

*  PLSQL 介绍
 *  1. dbms\_output用法
 *  2.赋值操作
 *   *  2.1 :=
     *  2.2 into
     *  2.3 属性类型
 *  3.控制语句
 *   *  if语句
     *  case语句
     *  无限循环
     *  有条件循环
     *  for循环
     *  goto
 *  4.动态SQL语句
 *  5.异常语句

## PLSQL 介绍 ##

PLSQL过程语言(Procedural Language)与结构化查询语言(SQL)结合而成的编程语言.通过增加变量、控制语句，使我们可以写一些逻辑更加复杂的数据库操作.

语法结构

declare
    	--声明变量  变量名称 v_ 开头，规范
    begin
       --执行具体的语句
       --异常处理
    end;

注意：

1.  赋值通过’:='完成
2.  begin和end之间必须有一行可执行的代码
3.  end之后必须跟上’;’
4.  如果没有需要声明的变量declare可以省略掉

declare
        v_hello varchar(20);
    begin
        v_hello := 'Hello Oracle';
        dbms_output.put_line(v_hello);
    end;
    
    begin
       dbms_output.put_line('hello');
    end;

> dbms\_output不输出的问题。执行如下命令即可
> 
> set serveroutput on;

## 1. dbms\_output用法 ##

dbms\_output包主要用于调试pl/sql程序，或者在sql\*plus命令中显示信息(displaying message)和报表，譬如我们可以写一个简单的匿名pl/sql程序块，而该块出于某种目的使用dbms\_output包来显示一些信息。

1.  enable：在serveroutput on的情况下，用来使dbms\_output生效(默认即打开)
2.  disable：在serveroutput on的情况下，用来使dbms\_output失效
3.  put：将内容写到内存，等到put\_line时一起输出
4.  put\_line：不用多说了，输出字符
5.  new\_line：作为一行的结束，可以理解为写入buffer时的换行符
6.  get\_line(value, index)：获取缓冲区的单行信息
7.  get\_lines(array, index)：以数组形式来获取缓冲区的多行信息

begin
       dbms_output.put('a1');
       dbms_output.put('b2');
       dbms_output.new_line(); -- 输出缓存中的信息，新起一行
       dbms_output.put_line('aaaaa'); -- 会输出缓存中的信息和当前的信息，不会换行
    end;

## 2.赋值操作 ##

### 2.1 := ###

-- 定义两个变量 v_a,v_b 计算和是多少
    declare
        v_a number(3); --- 声明变量
        v_b number(3) :=20 ; -- 声明变量同时赋值
        v_num number(3);
        v_f constant varchar(20) :='我是常量';
    begin
       -- v_f := 'aaa'; -- 常量不能够被修改
        v_a := 30;
        v_num := v_a + v_b;
        dbms_output.put_line(v_a||'+'|| v_b ||'='||v_num); -- || 字符串拼接我们通过 || 来实现
    end;

### 2.2 into ###

into我们在执行SQL操作的时候，需要把查询的字段信息赋值给变量。那么这时我们就可以通过into 关键字来实现。如果有多个字段要赋值。我们只需要在into的左右两侧建立好对应关系即可。

declare
       v_name varchar2(30);
       v_sex varchar2(3);
       v_dept varchar2(10);
    begin
       select name,sex,department into v_name,v_sex,v_dept from student where id = 901;
       dbms_output.put_line(v_name||'-'||v_sex||'-'||v_dept);
    end;
    
    
    -- 定义两个变量 v_a,v_b 计算和是多少
    declare
        v_a number(3) :=&请输入a; --- 声明变量
        v_b number(3) :=&请输入b; -- 声明变量同时赋值
        v_num number(3);
    begin
        v_num := v_a + v_b;
        dbms_output.put_line(v_a||'+'|| v_b ||'='||v_num); -- || 字符串拼接我们通过 || 来实现
    end;

### 2.3 属性类型 ###

1.  %type:变量和字段类型的绑定
2.  %rowtype：表结构中的一条记录的绑定

-- 变量的类型如果和字段的类型不一致怎么办?
    -- 属性类型
    declare
       v_name student.name%type;
       v_sex student.sex%type;
       v_dept student.department%type;
    begin
       select name,sex,department into v_name,v_sex,v_dept from student where id = 901;
       dbms_output.put_line(v_name||'-'||v_sex||'-'||v_dept);
    end;
    -- 表结构中有很多个字段。我们对于的就需要声明多少个变量，很繁琐。
    declare
       v_row student%rowtype;
    begin
       select * into v_row from student where id = 901;
       dbms_output.put_line(v_row.id||'-'||v_row.name||'-'||v_row.sex);
    end;

## 3.控制语句 ##

### if语句 ###

if语句的作用是控制程序的执行顺序。范围控制

declare
       v_age number(3) := &请输入年龄;
    begin 
       dbms_output.put_line('v_age='||v_age);
       if v_age = 18 then
          dbms_output.put_line('成年小伙');
       end if;
       dbms_output.put_line('-------');
       if v_age = 18 then
          dbms_output.put_line('成年小伙');
          else
          dbms_output.put_line('未知...');
       end if;
       dbms_output.put_line('-------');
       if v_age = 18 then
          dbms_output.put_line('成年小伙');
          elsif v_age < 18 then
          dbms_output.put_line('小孩子');
          elsif v_age > 18 then
          dbms_output.put_line('成年人');
          else
          dbms_output.put_line('未知...');
       end if;
    end;

### case语句 ###

case语句是一个非常强大的关键字。既可以实现类似于Java中的switch语句的作用。也可以像if语句一样来实现范围的处理。

-- case 语句
    declare
       v_age number(3) := &输入年龄;
    begin
       case
         when v_age < 18 then
          dbms_output.put_line('小朋友');
          when v_age > 18 then 
          dbms_output.put_line('成年人');
          else
          dbms_output.put_line('刚好成年');
       end case;
    end;
    -- case 语句可以实现类似于Java中的switch语句。在 case 和when之间声明变量就可以
    -- 如果是在when 和 then 之间指定条件那么和if语句是类似的
    declare
       v_age number(3) := &输入年龄;
    begin
       case v_age
         when 18 then
          dbms_output.put_line('18');
          when 19 then 
          dbms_output.put_line('19');
          else
          dbms_output.put_line('未知');
       end case;
    end;

### 无限循环 ###

loop循环可以通过exit来指定条件跳出循环。如果不指定那么就是无限循环

-- 输出1~10
    declare
      v_i number(3) := 1;
    begin
      loop
         dbms_output.put_line(v_i);
         exit when v_i >= 10; -- 退出循环
         v_i := v_i + 1;
      end loop;
    end;

### 有条件循环 ###

通过while来指定循环的条件

declare
       v_i number(3) := 1;
    begin
       while v_i <= 10 loop
          dbms_output.put_line(v_i);
          -- 修改变量
          v_i := v_i + 1;
       end loop;
    end;

### for循环 ###

--for循环
    begin
       for i in 1..10 loop
        dbms_output.put_line(i);
       end loop;
    end;
    select * from student;
    begin
      for cur_row in (select id,name,sex,department  from student) loop
         dbms_output.put_line(cur_row.id||'-'|| cur_row.name ||'-' || cur_row.sex || '-' || cur_row.department);
      end loop;
    end;

### goto ###

顺序控制用于按顺序执行语句,goto关键字会跳转到我们指定的位置开始自上而下执行。

-- goto
    declare
       v1 number(3) := &请输入v1的值;
    begin
       if v1 > 10 then
            goto c1;
       elsif v1 = 10 then 
          goto c2; 
       else
          dbms_output.put_line('其他');
       end if;
            dbms_output.put_line('666');
       <<c1>>
       dbms_output.put_line('大于10');
       <<c2>>
       dbms_output.put_line('等于10');
       
       dbms_output.put_line('----1----');
       dbms_output.put_line('----2----');
    end;

## 4.动态SQL语句 ##

动态 SQL 是指在PL/SQL程序执行时生成的SQL 语句。

语法结构为：

EXECUTE IMMEDIATE dynamic_sql_string
          [INTO  define_variable_list]
          [USING bind_argument_list];

案例

-- 可以根据名字或者性别来查询学生的信息
    declare
        v_name student.name%type := '&请输入姓名';
        v_sex student.sex%type :='&请输入性别';
        v_sql varchar2(200);
        v_row student%rowtype;
    begin
        v_sql := 'select * from student where 1=1 ';
        if v_name is not null then
            v_sql := v_sql || ' and name like ''%'||v_name||'%''' ;
        end if;
      
        if v_sex is not null then
           v_sql := v_sql || ' and sex = '''|| v_sex||'''' ;
        end if;
        execute immediate v_sql into v_row ;
      
      
        dbms_output.put_line(v_row.name||'---'||v_row.sex||'---'||v_row.department);
    end;

如果查询的结果不存在或者返回的记录过多那么都会爆出异常信息

## 5.异常语句 ##

在运行程序时出现的错误叫做异常  
发生异常后，语句将停止执行，控制权转移到PL/SQL 块的异常处理部分  
异常有两种类型

*  预定义异常 - 当 PL/SQL 程序违反 Oracle 规则或超越系统限制时隐式引发
 *  用户定义异常 - 用户可以在 PL/SQL 块的声明部分定义异常，自定义的异常通过 RAISE 语句显式引发

处理系统预定义异常：

-- 异常的应用
    -- 系统预定义异常： 
    -- too_many_rows 多行数据
    -- no_data_found 找不到
    -- others 其他异常
    declare
       v_name student.name%type;
       
    begin
       select name into v_name from student where id = 900 ;
       dbms_output.put_line(v_name);
       
       -- 异常语句块
       exception
           when too_many_rows then
            dbms_output.put_line('返回太多行');
           when no_data_found then
            dbms_output.put_line('找不到数据');
           when others then
            dbms_output.put_line('其他错误');
    end;

自定义异常：

步骤：

1.  需要显示的声明自定义的异常
2.  在业务逻辑代码中通过raise关键字抛出自定义异常
3.  我们需要在异步部分来声明自定义异常满足条件的处理方案

-- 自定义异常
    declare
       myException exception; -- 声明异常
       v_name varchar2(30) := '张三1';
    begin
       if v_name not in ('张三','李四','王五') then
          -- 满足条件就抛出异常
          raise myException;
        else
          dbms_output.put_line('---------------------');
       end if;
        dbms_output.put_line('---------66666------------');
       
       exception
          when myException then
            dbms_output.put_line('---------触发了自定义异常------------');
          when others then
           dbms_output.put_line('---------其他异常------------');
    end;