Oracle总结三

唐微港

于 2021-06-24 20:06:53 发布

阅读量224

点赞数

分类专栏：数据库文章标签： oracle

本文链接：https://blog.csdn.net/William_TWG/article/details/118195518

版权

数据库专栏收录该内容

7 篇文章 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 是将一整张表的数据赋值给另一个表，然后就可以对表中的字段进行相应的操作

实例：

新建表和数据

    -- 新建一个简单的学生表
    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');

使用%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;

使用%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;

使用%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 过程名称;

无参存储

存储过程创建

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

存储过程调用

begin
   pro_1;
end;

在命令窗口中调用方式

exec pro_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;

存储过程调用

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

在命令窗口中调用方式

exec  pro_2(7499);

输入输出存储

存储过程创建

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;

调用方式

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 [函数名];

无参存储函数

创建函数

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

调用方式(匿名块)

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

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

select fun1() from dual

带入参存储函数

创建函数

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;

调用方式

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

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

select fun2(7788) from dual;

带出参存储函数

创建函数

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;

调用方式

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;

唐微港

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
Oracle总结三

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

扫一扫

专栏目录