Oracle:存储过程、变量、流程控制、游标

原创已于 2024-09-08 22:57:36 修改 · 1.4k 阅读

20 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #数据库

于 2024-08-16 19:08:21 首次发布

一、PL/SQL块

1、PL/SQL块结构

语法：

declare

  --声明变量、游标;

begin

  --程序主体;

--[异常处理];

end;

说明：

declare部分用来声明变量或游标（结果集类型变量），如果程序中无变量声明可以省略

2、例子

写一个过程，输出‘hello word’

（1）PL/SQL编写：

--打印hello word

begin

  dbms_output.put_line('hello word');

end;

说明：dbms_output.put_line()函数

1、dbms_output：oracle内置的程序包

用于调试PL/SQL程序或SQL*PLUS命令中显示输出信息。

dbms_output包提供了一系列存储过程和函数，用于在PL/SQL程序或SQL*PLUS环境中输出信息

2、put_line：可以将任何字符串作为参数，并将其打印出来

函数的最大行大小为32,767字节。‌如果超过此值，‌会收到ORA-20000和ORU-10028错误

缓冲区的范围没有限制，‌但默认最大为20,000字节，‌最小为2,000字节。‌如果文本对于缓冲区来说太大，‌会收到ORA-20000错误和ORU-10027错误。

（2）在SQL*PLUS中执行

--打印hello word

set serveroutput on;



begin

  dbms_output.put_line('hello word');

end;

/

说明：

1、SQL*PLUS客户端默认情况下输出选项是被关闭的，我们需要打开后才可以将结果打印到控制台。命令：set serveroutput on;

2、SQL*PLUS中执行编写结束后，是并没有执行的，需要加上斜杠‘/’，再回车

二、存储过程

存储过程结构包括：声明部分、执行部分、异常处理

语法：

create [or replace] procedure 存储过程名

[(parameter1 in|out 数据类型，parameter2 in|out 数据类型,……)] --参数

is/as

--内部变量定义

begin

程序主体部分;

[exception]

[异常处理语句;]

end [存储过程名];

参数说明：

parameter是存储过程调用/执行时用到的参数，关键字in表示输入参数，out表示输出参数；它并不是存储过程定义的内部变量，内部变量需要在is|as和begin之间进行定义，并使用分号结束
创建存储过程时，is|as的效果是相同的

1、无参数的存储过程

--向emp表插入一条记录
create or replace procedure sp_test
is
begin
  insert into dept values(12,'研发部','数据架构');
  commit;
  dbms_output.put_line('数据插入成功');
end;

--调用存储过程
begin
    sp_test;
end;

--查看表数据
select * from dept;

2、in模式参数的存储过程

in参数只能根据被传入的指定值为存储过程

--向emp表插入给定记录
create or replace procedure sp_test(
    v_deptno in number,
    v_dname in varchar2,
    v_loc in varchar2
)
is
begin
  insert into dept values(v_deptno,v_dname,v_loc);
  commit;
  dbms_output.put_line('数据插入成功');
end;

--调用存储过程
begin
sp_test(15,'运营部','分析运营岗');
end;

--查看表数据
select * from dept;

3、out模式参数的存储过程

out参数只能等待赋值，不能为存储过程本身提供数据

--查询指定人员的薪资
create or replace procedure sp_test(
    v_name in varchar2,
    v_row out emp.sal%type
)
is
begin
    select sal into v_row from emp where ename = v_name;
    --异常处理：指定人员不存在时打印提示信息
exception
  when no_data_found
    then dbms_output.put_line('无此人');
end;

--调用存储过程
declare
  v_row  emp.sal%type;--声明变量，对应存储过程中的out模式的v_row
begin
  sp_test('ADAMS',v_row);--传入姓名，并输出薪资
  dbms_output.put_line(v_row);
end;

说明：dbms_output.put_line只能用于打印字符串或可以隐式转换为字符串的数据类型。对于记录类型（如 emp%ROWTYPE），需要指定要打印的字段

4、in out模式参数的存储过程

in out参数在存储过程被调用时，从外界向该类型的参数传入值，在存储过程执行完成后，可以将该参数的值返回给外界

--计算传入数值的次方值
create or replace procedure sp_test(
       num in out number,
       i in number          --计算i次方
)
is
begin
  if num is not null
    then num := power(num,i);
      else 
        dbms_output.put_line('输入参数无效');
      end if;
    
end;

--调用存储过程
declare
      num number;
      i number;
begin
  num := 3;
  i :=3;
  sp_test(num,i);
  dbms_output.put_line(num);
end;

三、常量和变量

1、常量

（1）概念

在程序运行过程中不可被改变的数据存储结构

（2）语法

常量名 constant 数据类型 := 常量值；

i constant number := 10;

2、变量

整体概述

变量分两大类：普通变量类型（char、varchar2、date、number、boolean、long）

特殊变量类型（引用型变量、记录型变量）

变量使用：

先定义后赋值，语法：变量名数据类型(长度); 变量名 := 初始值；
定义的同时进行赋值，语法：变量名数据类型(长度) := 初始值；（如：i varchar2(10) := '大学'）

说明：

引用型变量是指变量的类型取决于表中字段的类型；记录型变量是指接收的变量不是一个字段的值，是一整块记录的值

（1）普通变量

概念：先定义后赋值的变量称为普通变量

声明变量方式： 变量名变量类型（长度）

v_name varchar2(10);

变量赋值的2种方式：

直接赋值‘:=’
语句赋值：select 值 into 变量 from 源表

这里的值可以是指定结果，也可以是表中查询的数据结果

declare
    v_name varchar2(6);
    v_class varchar2(6);
    v_age number(10);
begin
  --变量直接赋值
  v_name := '小a';
  v_class := '2班';
  
  --变量语句赋值
  select 18 into v_age from dual;  
  
  dbms_output.put_line(v_name||'今年'||v_age||'岁了，在高一'||v_class);
end;

（2）引用型变量

概念：在定义变量的时候引用已有表字段类型、长度

使用方法：

变量的类型、长度取决于表中字段的类型、长度

通过‘表名.列名%TYPE’指定变量的类型、长度

比如：

v_name emp.ename%TYPE;

好处

使用普通变量定义方式，需要知道表中列的类型，而使用引用型变量则不需要考虑列的类型

--查询员工BLAKE的工资信息
declare
v_name emp.ename%TYPE;
v_sal emp.sal%TYPE;
begin
  --变量语句赋值
  select ename,sal into v_name,v_sal from emp where ename = 'BLAKE' ;
  dbms_output.put_line('姓名：'||v_name||',薪资：'||v_sal);
end;

（3）记录型变量

语法：变量名称表名%ROWTYPE

v_emp emp%rowtype;

适用场景

适用于查询表中全部字段，且表字段个数很多的情况。比如说，有一张表有100个自动，如果需要使用这100个字段的话，普通变量或引用型变量一个个声明就很麻烦，这时候使用记录型变量就很

缺点

因为记录型变量用到‘select *’，因此会增加数据库的负担，可以根据实际场景酌情使用

--查询员工BLAKE的所有信息
declare
v_emp  emp%rowtype;
begin
  --变量语句赋值
  select * into v_emp from emp where ename = 'BLAKE' ;
  
  dbms_output.put_line('姓名：'||v_emp.ename||',薪资：'||v_emp.sal);
end;

四、流程控制

1、条件分支

语法：

begin

if   条件1   then   执行1

   elsif 条件2 then 执行2

    else 执行3

   end if;

end;

--统计表中数据记录数
declare
  v_count number;
begin
  select count(*) into v_count from emp;
  
  if v_count = 0 then
    dbms_output.put_line('表为空');
    elsif v_count between 1 and 100  then
      dbms_output.put_line('表中记录数小于100条，共：'||v_count);
      else
        dbms_output.put_line('表中记录数大于100条，共：'||v_count);
  end if;
end;

2、循环

（1）loop

作用：loop语句会先执行一次循环，然后进行退出循环条件判断，如果满足条件则退出，否则将再次执行循环

语法：

begin

loop

执行语句；

exit when 退出循环条件；

end loop;

end;

--输出数据1-10
declare
  v_count number;
  
begin
  v_count := 0;
  
  loop
    v_count := v_count+1;
    dbms_output.put_line(v_count);
    exit when v_count > 9;  
  end loop;
  
end;

（2）while

作用：whie语句先根据循环条件进行判断，为true则执行循环，false则退出循环

语法：

while 循环条件 loop

执行语句；

end loop;

--输出数据1-10
declare
    num number;
begin
  num :=0;
  while num <10 loop
    num := num+1;
    dbms_output.put_line(num);
    end loop;
end;

（3）for

作用：for语句是可以设置循环次数的循环控制语句，当循环次数超过上限值时则退出循环

语法：

for 变量 in [reverse] 下限 .. 上限 loop

执行

end loop;

说明：reverse表示计数器的值会随循环递减，默认情况下计数器的值会循环递增

--输出数据1-10
declare
    num number;
begin
  num :=0;
  for i in 1..10 loop
    num := num +1;
    dbms_output.put_line(num);
  end loop;
end;

--输出数据1-10（使用reverse关键字）
declare
num number;
begin
  num :=0;
  for i in reverse 1..10 loop
    num := num +1;
    dbms_output.put_line(num);
  end loop;
end;

两者的输出结果一致

五、游标

1、概念

用来临时存储一个查询返回的多行数据，通过遍历游标，可以逐行访问处理该结果集的数据

2、使用方法

声明——>打开——>读取——>关闭

（1）声明：cursor 游标名[(参数列表)] is 查询语句；

参数是可选的，有的话会带入到查询语句中进行查询

（2）打开：open 游标名;

（3）读取：fetch 游标名 into 变量列表；

（4）关闭：close 游标名；

3、游标的属性

--不带参数的游标

declare
    --声明游标
    cursor v_st is select id from student;
    --声明变量用来接收游标数据
    v_row  student.id%type;
begin
  --打开游标
  open v_st;
  
  loop
    fetch v_st into v_row;
    dbms_output.put_line(v_row);
    exit when v_st%notfound;
  end loop;
  
  close v_st;
  
end;

--带参数的游标

declare
    --声明游标
    cursor v_st(v_id student.id%type) is select id from student where  id=v_id;
    --声明变量用来读取游标数据        
    v_row  student.id%type;
begin
  --打开游标
  open v_st('001');
  
  loop
    fetch v_st into v_row;
    dbms_output.put_line(v_row);
    exit when v_st%notfound;
  end loop;
  
  close v_st;
  
end;