Oracle总结三

怼烎@ 2022-10-09 12:25 111阅读 0赞

> PLSQL

### 数据类型 ###

#### 数据类型 ####

*  Number 数字型
 *  Int 整数型
 *  Pls\_integer 整数型，产生溢出时出现错误
 *  Binary\_integer 整数型，表示带符号的整数
 *  Char 定长字符型，最大255个字符
 *  Varchar2 变长字符型，最大2000个字符
 *  Long 变长字符型，最长2GB
 *  Date 日期型
 *  Boolean 布尔型（TRUE、FALSE、NULL三者取一）
 *  在PL/SQL中使用的数据类型和Oracle数据库中使用的数据类型，有的含义是完全一致的，有的是有不同的含义的。

#### 定义方式 ####

##### 基本数据类型定义方式 #####

注意：声明是要放在declare和begin之间的，不能随便放在其他位置。

*  变量名 类型标识符 \[not null\]:=值;

declare
            age number(4):=10; --长度为4，初始值为10
        begin
            -- commit; 
        end;

##### 常量数据类型定义方式 #####

*  常量名 constant 类型标识符 \[not null\]:=值;

declare
            pi constant number(10):=3.1415926;--为pi的数字型常量，长度为10，初始值为3.1415926
        begin
            -- commit;
        end;

### 运算表达式 ###

#### 算术 ####

\+（加法）、-（减法）、\*（乘法）、/（除法）、\*\*（乘方）

**例：**

set serveroutput on 
        declare
           res integer;
        begin
           res:=10+3*4-20+5**2;
           dbms_output.put_line('运算结果是：'||to_char(res));
        end;/

set serveroutput on 只需要申明一次即可，后续就不需要在申明

dbms\_output只能输出字符串类型，所以需要将最终的结果转为字符串，而且需要在控制窗口运行而不是sql窗口！！！

#### 字符 ####

连接运算符 “||”

例：

set serveroutput on 
       declare
        begin
           dbms_output.put_line('连接'|| '运算符');
        end;/

#### 关系 ####

< 小于
    > 大于
    = 等于（不是赋值运算符:=）
    <= 小于等于
    >= 大于等于
    != 不等于 或<>
    like 类似于
    in 在……之中
    between 在……之间

注意：关系型表达式运算符两边的表达式的数据类型必须一致。

#### 逻辑 ####

*  NOT：逻辑非
 *  AND：逻辑与
 *  OR：逻辑或

注意：运算的优先次序为NOT、AND和OR。

### 申明类型 ###

#### 值拷贝(赋值) ####

set serveroutput on
    declare
           v_number1 number := 100;
           v_number2 number;
       begin
           v_number2:=v_number1; --将v_number2的值直接赋值给v_number1
           v_number1:=200;
           dbms_output.put_line(v_number1); --200
           dbms_output.put_line(v_number2); --100
       end;

#### 记录类型的record ####

定义一个组合record(),其中可以放多种类型的声明，这些类型可以通过组合名获取，有点类似于对象。使用的时候可以直接 组合名.变量名即可使用。

set serveroutput on
     declare
           type t_first is record(
                id number(6),
                name varchar2(20)
           );
           -- 给表取别名为v_first，也可以直接使用表记录名
           v_first t_first; 
      begin
         v_first.id:=1022;
         v_first.name:='tang';
         dbms_output.put_line(v_first.id);
         dbms_output.put_line(v_first.name);
      end;

#### 表类型变量table ####

type 表类型 is table of 类型 index by binary\_integer;

类型可以是前面的类型定义，index by binary\_integer子句代表以符号整数为索引，这样访问表类型变量中的数据方法就是“表变量名(索引符号整数)”。table类型，相当于java中的Map容器，就是一个可变长的数组，key（符号整数索引）必须是整数，可以是负数，value（类型）可以是标量，也可以是record类型。可以不按顺序赋值，但必须先赋值后使用。

*  一维表类型变量

set serveroutput on
     declare
          type t_tb is table of varchar2(20) index by binary_integer;
          v_tb t_tb;
       begin
          v_tb(100):='hello';
          v_tb(98):='world';
          dbms_output.put_line(v_tb(100));
          dbms_output.put_line(v_tb(98));
       end;

set serveroutput on
       declare
            type t_rd is record(id number,name varchar2(20));
            type t_tb is table of t_rd index by binary_integer;
            t_new t_tb;
       begin
            t_new(100).id:=1;
            t_new(100).name:='hello';
            --dbms_output.put_line(t_new(100).id);
            --dbms_output.put_line(t_new(100).name);
            dbms_output.put_line(t_new(100).id||' '||v_tb2(100).name);
       end;

*  多维表类型变量

declare
          type tabletype1 is table of testtable%rowtype index by binary_integer;
          table1 tabletype1;
       begin
           select * into table1(60) from tempuser.testtable where recordnumber=60;
           dbms_output.put_line(table1(60).recordnumber||table1(60).currentdate);
       end;

#### %type和%rowtype ####

*  %type 为了让PL/SQL中变量的类型和数据表中的字段的数据类型一致，Oracle 9i提供了%type定义方法。这样当数据表的字段类型修改后，PL/SQL程序中相应变量的类型也自动修改。
 *  %rowtype 是将一整张表的数据赋值给另一个表，然后就可以对表中的字段进行相应的操作

实例：

1.  新建表和数据

-- 新建一个简单的学生表
        create table student(
           STUDENT_NO number,
           STUDENT_NAME varchar2(20),
           STUDENT_AGE number(3,0),
           STUDENT_SEX varchar2(10)
        );
        
        -- 给表中插入一条数据
       insert into student(STUDENT_NO,STUDENT_NAME,STUDENT_AGE,STUDENT_SEX)           values(12301,'susu',23,'BOY');

1.  使用%type查找单个字段

set serveroutput on
    declare
           变量1 表名.字段1%type;
        begin
           select 字段1 into 变量1 from student where rownum=1;
           dbms_output.put_line(变量1);
        end;

set serveroutput on
    declare
           v_name student.STUDENT_NAME%type;
        begin
           select STUDENT_NAME into v_name from student where rownum=1;
           dbms_output.put_line(v_name);
        end;

1.  使用%type查找多个字段

declare
            变量1 表名.表字段1%type;
            变量2 表名.表字段2%type;
            变量3 表名.表字段3%type;
        begin
          select 表字段1,表字段2,表字段3 into 变量1,变量2,变量3 from 表名 where rownum=1;
          dbms_output.put_line(v_no||' '||v_name||' '||v_age);
        end;

declare
            v_no student.student_no%type;
            v_name student.student_name%type;
            v_age student.student_age%type;
        begin
          select student_no,student_name,student_age into v_no,v_name,v_age from student where rownum=1;
          dbms_output.put_line(v_no||' '||v_name||' '||v_age);
        end;

1.  使用%rowtype赋值表

语法：

set serveroutput on
        declare
           声明变量名 表名%rowtype;
        begin
           select * into 声明变量名 from 表名 where rownum=1;
           dbms_output.put_line(声明变量名.表字段||' '||声明变量名.表字段||' '||声明变量名.表字段);
    end;

set serveroutput on
        declare
           v_student student%rowtype;
        begin
           select * into v_student from student where rownum=1;
           dbms_output.put_line(v_student.student_no||' '||v_student.student_name||' '||v_student.student_age);
    end;

注意：只有insert，update，delete，select 可以使用以上的两种方式。

### 流程控制 ###

#### 判断 ####

##### if #####

语法：

declare
           flag boolean:=true;
    begin if 条件 then
             dbms_output.put_line('ok');
          end if;
    end;

例子：

declare
           flag boolean:=true;
    begin if flag then
             dbms_output.put_line('ok');
          end if;
    end;

##### if else #####

语法：

declare
            flag boolean:=true;
    begin
         if 条件 then
            dbms_output.put_line('ok');
         else -- 不满足则
            dbms_output.put_line('false');
         end if; -- 结束判断
    end;

例子：

declare
            flag boolean:=true;
    begin
         if flag then
            dbms_output.put_line('ok');
         else
            dbms_output.put_line('false');
         end if;
    end;

##### if elsif else #####

语法：

declare
            str varchar2(20):='tang';
    begin
         if 条件 then
            dbms_output.put_line('tang');
         elsif 条件 then
            dbms_output.put_line('wei');
         else -- 不满足则
            dbms_output.put_line('gang');
         end if; -- 结束判断
    end;

例子：

declare
            str varchar2(20):='tang';
    begin
         if str='tang' then
            dbms_output.put_line('tang');
         elsif str='wei' then
            dbms_output.put_line('wei');
         else
            dbms_output.put_line('gang');
         end if;
    end;

#### 循环 ####

##### loop #####

需要先申明变量

语法：

set serveroutput on
    declare
           声明变量 binary_integer :=0;
    begin
         loop
             exit when 声明变量>10; -- 满足条件就退出否则就继续执行
             声明变量 :=声明变量+1;
             dbms_output.put_line('hehe');
         end loop; -- 结束循环
             dbms_output.put_line('ok');
    end;

例子：

set serveroutput on
    declare
           num binary_integer :=0;
    begin
         loop
             exit when v_i>10;
             num :=num+1;
             dbms_output.put_line('hehe');
         end loop;
             dbms_output.put_line('ok');
    end;

##### while #####

需要先申明变量

语法：

declare
           申明变量 binary_integer:=0;
    begin
           while 申明变量<10 loop -- 满足条件继续执行
                 dbms_output.put_line('hello'||num );
                 申明变量:=申明变量+1;
           end loop; -- 结束循环
           dbms_output.put_line('over');
    end;

例子：

declare
           num binary_integer:=0;
    begin
           while num<10 loop
                 dbms_output.put_line('hello'||num );
                 num:=num+1;
           end loop;
           dbms_output.put_line('over');
    end;

##### for #####

不需要申明变量

语法：

begin
         for 任意标识 in 待循环值 loop -- 开启训话标志
             dbms_output.put_line('hello'||i);
         end loop; -- 结束循环标志
             dbms_output.put_line('循环结束');
    end;

例子：

begin
         for i in 0..10 loop
             dbms_output.put_line('hello'||i);
         end loop;
             dbms_output.put_line('循环结束');
    end;

### 异常 ###

#### 异常定义 ####

*  异常申明

异常名 EXCEPTION；

*  抛出异常

RAISE 异常名

*  异常处理

exception
    when 自定义抛出的异常名 then 
      dbms_output.put_line('sqlcode='||sqlcode);
      dbms_output.put_line('sqlerrm='||sqlerrm);
     when others then
       dbms_output.put_line('sqlcode='||sqlcode);
       dbms_output.put_line('sqlerrm='||sqlerrm);

#### 异常分类 ####

*  内部异常

又分为预定义异常（有错误号+常量定义） 和 非预定义例外 （仅有错误号，无常量定义）

1. 常见预定于异常
    
     ```sql

–- NO\_DATA\_FOUND （没找到数据）

–- TOO\_MANY\_ROWS （列数超过范围）

–- INVALID\_CURSOR (初始化错误)

–- ZERO\_DIVIDE （被除数不能为0）

–- DUP\_VAL\_ON\_INDEX （重复键值）

– VALUE\_ERROR （数据赋值错误）  
\`\`\`

演示实列：

set serveroutput on
    declare
      v_result number(3);
    begin
      v_result := 10 / 0; -- 模拟异常
      dbms_output.put_line('异常之后的代码将不再执行！'); -- 模拟异常后不再执行
      exception -- 异常体
      when zero_divide then
        dbms_output.put_line('被除数不能为0！');
        dbms_output.put_line(SQLCODE || '----' || SQLERRM);
      when others then
        dbms_output.put_line('其他未知异常');
      ROLLBACK;
    end;

*  自定义异常

演示实列

set serveroutput on
    declare
      v_data number; -- 定义一个数字类型的变量
      v_myexp exception; -- 定义一个异常变量
    begin
      v_data :=&inputData; -- 手动输入数据
    if v_data>1 and v_data<100 then
      raise v_myexp; -- 满足条件就抛出异常
      dbms_output.put_line('模拟异常抛出后不在往下执行');-- 模拟异常抛出后不在往下执行
    else
      dbms_output.put_line('输入数据超出范围');
    end if; -- 结束判断
    exception
    when v_myexp then -- 当有自定义的异常被抛出时就执行以下的逻辑
      dbms_output.put_line('输入的数据在1~100之间');
      dbms_output.put_line('sqlcode='||sqlcode);
      dbms_output.put_line('sqlerrm='||sqlerrm);
    end;

**注意：** 发生异常时，有时候希望了解当时发生的Oracle错误号和相关描述信息，Oracle 提供了两个内置函数 SQLCODE 和 SQLERRM 分别用来返回Oracle 错误号和错误描述。

*  语法：

declare
                异常名 exception; -- 申明异常
        begin
                dbms_output.put_line('hello');
                raise 异常名; -- 使用raise抛出异常，抛出后跳转到自定义的myException ，执行其里面的 逻辑处理，再跳到end处，结束PL/SQL块，raise接后面的语句不会继续执行。
                dbms_output.put_line('world');
                dbms_output.put_line(1/0);
        exception
                when 异常名 then -- 满足抛出的异常时执行
                	dbms_output.put_line('异常处理');
                    dbms_output.put_line(sqlcode); --当前会话执行状态，错误编码
                    dbms_output.put_line(sqlerrm); --当前错误信息
                when others then -- 不满足抛出的异常时执行
                    dbms_output.put_line('error');
        end;

#### 多异常处理 ####

同时定义多个异常处理方式

set serveroutput on
    declare 
        a int; 
        ex_1 exception;
        ex_2 exception;
        ex_3 exception;
    begin
       a:=&inputData;
       if a=1 then
        raise ex_1;
       elsif a=2 then 
          raise ex_2;
       else
       	  raise ex_3;
        end if;
        
        exception
          when ex_1 then
             dbms_output.put_line('捕获错误1');
          when ex_2 then 
             dbms_output.put_line('捕获错误2');
          when ex_3 then 
             dbms_output.put_line('捕获错误3');
          end;

#### others异常处理 ####

declare 
        a  int:=0; 
        ex_1 exception;
    begin  
       if a=0 then
        raise ex_1;
        end if;
         
        exception 
          when others then
            dbms_output.put_line('捕获了全局错误');
     end;

### 游标 ###

游标是一块内存区域，可以用以提高数据库数据处理速度。游标的工作机制是一种能从包括多行数据记录的结果集中每次提取一行记录的机制，即游标提供了在逐行的基础上操作表中数据的方法，有些许类似Java中迭代器。

#### 游标的类型 ####

*  隐式游标：在 PL/SQL 程序中执行DML SQL 语句时自动创建隐式游标；
 *  显式游标：显式游标用于处理返回多行的查询；
 *  REF 游标：REF 游标用于处理运行时才能确定的动态 SQL 查询的结果；

#### 游标的属性 ####

游标的属性分为以下几类。

*  %found：布尔型属性，当最近一次提取游标操作 FETCH成功则为 TRUE,否则为 FALSE；，如果对未打开的游标使用则报ORA-1001异常。
 *  %notfound：与%found行为相反。
 *  %isopen：判断游标是否打开打开时返回true。
 *  %rowcount：数字型属性，返回已从游标中读取的记录数。若未打开就引用，返回ORA-1001。

#### 游标的处理方式 ####

在做查询的操作时，显式游标主要是用于对查询语句的处理，；而对于DML，如修改、删除、更新操作，则由 ORACLE 系统自动地为这些操作设置游标并创建，对于隐式游标的操作，如定义、打开、取值及关闭操作，都由 ORACLE 系统自动地完成，无需用户进行处理。

#### 流程 ####

set serveroutput on
    declare
    		变量1，变量2 ....
              cursor 游标名 is select ... ; -- 申明游标
        begin
              open 游标名; -- 开启游标
              fetch 游标名 into 变量 -- 将游标中取出的值赋值给变量
              dbms_output.put_line(sqlerrm);
              close 游标名; -- 关闭游标
              dbms_output.put_line(sqlerrm);
        end;

#### 完整流程实例 ####

declare
              v_student student%rowtype;
              cursor c_student(v_id binary_integer) is select * from student where student_no>v_id;
        begin
              open c_student(10);
              fetch c_student into v_student;
              close c_student;
              dbms_output.put_line(v_student.student_name);
        end;

#### 使用实列 ####

游标也类似于JDBC中的resultSet集合

##### 普通游标 #####

set serveroutput on
    declare
      cursor emp_cursor is select ename, sal from emp where sal < 1000; -- 申明游标emp_cursor1
      v_name emp.ename%type; -- 申明 v_name并赋值结构
      v_sal  emp.sal%type;-- 申明 v_sal并赋值结构
    begin
      open emp_cursor; -- 开启游标
      loop
        fetch emp_cursor into v_name, v_sal; -- 从游标中获取值
        exit when emp_cursor%notfound; -- 取不到值的时候就结束
        dbms_output.put_line(v_name || '---' || v_sal);
      end loop; -- 结束循环
      close emp_cursor; -- 切记 使用完游标之后要关闭游标
    end;

##### 带参游标 #####

declare
    -- (此处的参数是给的默认参数，当重新设置时候，此默认值失效)
      cursor emp_cursor(cursor_sal number default 2000) is
        select ename, sal from emp where sal <= cursor_sal; -- 申明游标emp_cursor
        v_name emp.ename%type; -- 根据表中已有字段申明一个新字段
        v_sal  emp.sal%type;-- 根据表中已有字段申明一个新字段
    begin
      open emp_cursor(cursor_sal => 3000); -- 开启游标并重新设置参数值
      loop
        fetch emp_cursor into v_name, v_sal; -- 从游标中获取值
        exit when emp_cursor%notfound; -- 满足条件时退出
        dbms_output.put_line(v_name || '---' || v_sal);
      end loop;
      dbms_output.put_line('rowcount:' || emp_cursor%rowcount); --(游标记录数)
      close emp_cursor;
    end;

##### 带参带返回值游标 #####

declare
      type emp_record_type is record(
        v_no   emp.empno%type,
        v_name emp.ename%type,
        v_sal  emp.sal%type
      );-- 定义的record组合
      emp_record emp_record_type; -- 给定义的类型取个别名
      
      cursor emp_cursor(dept_no number) return emp_record_type
      is select empno, ename, sal from emp where deptno = dept_no; 
      -- 申明游标deptno是数据库字段dept_no是游标参数
    
    begin
      open emp_cursor(dept_no => 20);
      loop
        fetch emp_cursor into emp_record;
        if emp_cursor%found then
          dbms_output.put_line(emp_record.v_name || '---' || emp_record.v_sal);
        else
          dbms_output.put_line('结果集处理完毕');
          exit;
        end if;
      end loop;
      close emp_cursor; -- 关闭游标
    end;

##### for循环无参游标 #####

declare
      cursor emp_cursor is select ename, sal,deptno from emp; -- 申明游标
    begin
      for v in emp_cursor loop -- 循环游标，获取其中每一列的值
        dbms_output.put_line(v.ename || '----' || v.sal || '----' || v.deptno);
      end loop;
    end;

##### for循环有参游标 #####

declare
      cursor emp_cursor(dept_no number) is
        select ename, sal from emp where deptno = dept_no; -- 申明一个游标并且传参为dept_no的数字
    begin
      for v in emp_cursor(30) loop -- 传入一个数字，这里就是传入的部门编号
        dbms_output.put_line(v.ename || '------' || v.sal);
      end loop;
    end;

### 存储过程 ###

#### 意义 ####

存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合，存储过程可以复用，调用存储过程可以简化开发，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的，并且存储过程能够有效的防止SQL注入，有一定的安全性。

#### 定义 ####

注意：当使用replace的时候，如果系统中已经存在有相同名的存储过程，那么就会进行替换覆盖，如果不使用，系统存在着相同名的存储过程时候就会报错。

create [or replace] procedure 过程名称 [(参数列表)] is
    begin
    -- 执行体
    end 过程名称;

#### 无参存储 ####

1.  存储过程创建

create or replace procedure pro_1 is
    begin
       -- 执行体
       dbms_output.put_line('hello world');
    end pro_1;

1.  存储过程调用

begin
       pro_1;
    end;

1.  在命令窗口中调用方式

exec pro_1;

#### 有参存储 ####

1.  存储过程创建

--存储过程，要求：调用的时候传入员工编号，自动控制打印
    create or replace procedure pro_2(in_empno IN emp.empno%TYPE) as
      --声明变量
      v_ename emp.ename%TYPE;
      v_sal   emp.sal%TYPE;
    begin
      SELECT ename, sal INTO v_ename, v_sal FROM emp WHERE empno = in_empno;
      --打印变量
      dbms_output.put_line('姓名：' || v_ename || '薪水：' || v_sal);
    end pro_2;

1.  存储过程调用

begin
       pro_2(7499); -- 传入定义的参数
    end;

1.  在命令窗口中调用方式

exec  pro_2(7499);

#### 输入输出存储 ####

1.  存储过程创建

create or replace procedure pro_3(in_empno IN emp.empno%TYPE,ou_sal OUT emp.sal%TYPE) as
    -- 声明变量
           v_ename emp.ename%TYPE;
           v_sal   emp.sal%TYPE;
    begin
    -- 这里的ou_sal其实就是要返回出去的值，这样在调用的时候在外面用一个对于变量即可接受到返回去的值
      SELECT sal,sal,ename INTO v_sal,ou_sal,v_ename FROM emp WHERE empno = in_empno; 
       --打印变量
       dbms_output.put_line('姓名：' || v_ename || '薪水：' || v_sal || '输出薪水：' || ou_sal);
    END pro_3;

1.  调用方式

DECLARE
      -- 声明变量
      v_sal emp.sal%TYPE;
    begin
     -- 调用存储过程
      pro_3(7782,v_sal);
      dbms_output.put_line('薪水：'||v_sal);
    end;

#### 查看所有存储过程 ####

select * from dba_objects a where a.object_type='procedure' and owner='train';

### 存储函数 ###

#### 意义 ####

oracle中的存储函数，类似于系统内的函数，可以像调用系统函数一样调用存储函数。它与存储过程的唯一区别就是存储过程没有return返回值而函数有，存储函数可以与存储过程互换，存储函数可以在存储过程中调用。函数的参数列表可以有，也可以没有，当没有时，函数名后面不要带括号。

#### 定义 ####

注意：当使用replace的时候，如果系统中已经存在有相同名的函数，那么就会进行替换覆盖，如果不使用，系统存在着相同名的函数时候就会报错。

create [or replace] function 函数名[(参数名 in|out 参数类型)]   return 返回值数据类型 is
    -- 声明变量部分;
    begin
    代码块;
    return 结果变量;
    end [函数名];

#### 无参存储函数 ####

1.  创建函数

create or replace function fun1 return varchar2
    as
    str varchar2(10):='good';
    begin
    dbms_output.put_line(str);
      return 'twg';
    end fun1;

1.  调用方式(匿名块)

set serveroutput on
    declare
       name varchar2(10);
    begin
       name := fun1();
       dbms_output.put_line(name);
    end;

1.  查询调用方式, 和系统定义的函数一样很多地方都可以使用

select fun1() from dual

#### 带入参存储函数 ####

1.  创建函数

create or replace function fun2(in_empno in emp.empno%type) return number as
    　psal emp.sal%type;
    　pcomm emp.comm%type;
    begin
    　select sal into psal from emp where empno = in_empno;
    　return psal * 12 + nvl(pcomm, 0);
    end fun2;

1.  调用方式

declare
    sal int;
    begin
    	sal:=yearsal(7369)
    　　dbms_output.put_line(sal);
    end;

1.  查询调用方式，和系统定义的函数一样很多地方都可以使用

select fun2(7788) from dual;

#### 带出参存储函数 ####

1.  创建函数

create or replace function fun3(eno in number,pname out varchar2,psal out number,
                                     pjob out varchar2) as
    begin
      select ename, sal, empjob into pname, psal, pjob from emp where empno = eno;
    end fun3;

1.  调用方式

set serveroutput on
    declare
    pname varchar2(32);
    psal number;
    pjob varchar2(32);
    begin
      fun3(7369,pname,psal,pjob);
      dbms_output.put_line('姓名'||pname || '工资' || psal || '岗位' || pjob);
    end;