PLSQL代码块

一、匿名块的基本结构

匿名块就是数据库里面，一段没有名字的代码块。基本结构如下

declare
  变量声明的部分;
begin
  代码执行的部分;
end;

一个简单的例子：

declare 
  ----变量的声明
  n1 number;
  n2 number;
begin
  --变量的赋值
  n1:=10;
  n2:=20;
   --引用系统的包：输出
  dbms_output.put_line(n1+n2);
end;

二、变量

在PLSQL中，每个变量都是需要先声明才能被赋值的。

1.简单变量

声明方式：可以先声明再赋值；也可以在声明的同时赋值

--先声明后赋值
declare 
  ----变量的声明
  n1 number;
  n2 number;
begin
  --变量的赋值
  n1:=10;
  n2:=20;
   --引用系统的包：输出
  dbms_output.put_line(n1+n2);
end;

--在声明的同时去赋值
declare
  --变量的声明和赋值
  n1 number:=10;
  n2 number:=20;
begin
  --引用系统的包：输出
  dbms_output.put_line(n1+n2);
end;

2.引用型变量

引用型变量：使用表格中字段的类型来声明变量(意思是变量的数据类型和表格的某字段的数据类型相同)。

声明方式：变量名 表格.表格字段%type;

在PLSQL代码块里面，select查询必须包含有 into 的子句。

declare
  -- 使用emp表格里面的sal字段的数据类型
  s emp.sal%type;
begin
  select sal into s from emp where ename='SMITH';
  dbms_output.put_line(s);
end;

3.记录型变量

将一整行的表格的数据类型，全部都声明给一个变量，这个变量其实是个数组。

声明方式：变量名 表格%rowtype;

declare
  -- 将emp一整行的类型都声明给v_user变量
  v_user emp%rowtype;
begin
  select * into v_user from emp where ename='SMITH';
  dbms_output.put_line(v_user.empno);
  dbms_output.put_line(v_user.sal);
  dbms_output.put_line(v_user.job);
end;

三、输出

dbms_output.put_line(要输出的内容);      一次只能输出一行数据

put_line() 括号里面，只能有一个内容，输出打印一次只能打印一个数据。

如果想要打印多个数据，需要用管道符（||）将数据拼接成一个字符串。

declare 
  v_user emp%rowtype;
begin
  select * into v_user from emp where ename='SMITH';
  dbms_output.put_line(v_user.empno||'-'||v_user.ename);
end;

或者使用 put() 方法，将需要打印的数据先存放在内存中不要输出，等所有要打印的内容全部都存完了，再通过put_line()一起打印。

declare
  v_user emp%rowtype;
begin
  select * into v_user from emp where eanme='SMITH';
  dbms_output.put(v_user.empno);  --把v_user.empno放到内存中
  dbms_output.put('：');  --把‘-’放到内存中
  dbms_output.put_line(v_user.job); --上面放在内存中内容连同v_user.job一起打印出来
end;

四、输入

n number:=&提示信息;

select * from emp where empno=&请输入员工编号;

declare
  n1 number:=&请输入一个数字;
  n2 number:=&输入另外一个数字;
begin
  dbms_output.put_line(n1+n2);
end;

当输入的是字符串时&需要用单引号' '括起来，表示输入的是字符串类型

str varchar2(20):='&提示信息';

declare 
  name varchar2(20):='&请输入名字';
begin
  dbms_output.put_line('hello，'||name);
end;

五、逻辑结构（判断和循环）

5.1 分支判断  if

最完整的判断语句：

if  条件判断  then
     执行的sql语句;
elsif  条件判断  then
     执行的sql语句;
else 
     执行的sql语句;
end  if; 

可以只有If，也可以有if elsif elsif ，还可以有if else 

-- 只判断一次的语句
if  条件判断  then
     执行的sql语句;
end  if;

-- 多次判断
if  条件判断  then
     执行的sql语句;
elsif  条件判断  then
     执行的sql语句;
...
end  if;

-- 用else表示其他的剩余的条件
if  条件判断  then
     执行的sql语句;
else
     执行的sql语句;
end  if;

--练习：输入一个数字，判断是奇数还是偶数

declare 
  n number:=&请输入一个数字;
begin
  if n=0 then
    dbms_output.put_line('零');
  elsif mod(n,2)=1 then
    dbms_output.put_line('奇数');
  else
    dbms_output.put_line('偶数');
  end if;
end;

--练习：输入emp表中一个员工的编号，判断他是员工还是领导，   

    当job为'MANAGER'，'PRESIDENT'时是领导，为其他时是员工。

declare 
  v_empno emp.empno%type:=&请输入员工编号;
  v_job emp.job%type;
begin
  select job into v_job from emp where empno=v_empno;
  if v_job in ('MANAGER','PRESIDENT') then
    dbms_output.put_line('领导');
  else
    dbms_output.put_line('员工');
  end if;
end;

select 字段 into 变量 from 表名 ....；

1. into子句在查询的时候，结果必须要有数据，并且数据只能是一行（有且仅有一行数据）。
2. 查询结果是null值，或者结果有多行，select语句都会报错。

--练习：输入emp表中一个存在的员工编号，输出这个员工的工资等级，2000以下是C，3000以下是B，大于等于3000是A

declare
  v_empno emp.empno%type:=&请输入员工编号;
  v_sal emp.sal%type;
begin
  select sal into v_sal from emp where empno=v_empno;
  if v_sal<2000 then
    dbms_output.put_line('C');
  elsif v_sal>3000 then
    dbms_output.put_line('A');
  else
    dbms_output.put_line('B');
  end if;
end;

--练习：输入任意的员工编号，打印出有没有这个员工。

declare
  v_empno emp.empno%type:=&请输入员工编号;
  c number;
begin
  select count(1) into c from emp where empno=v_empno;
  if c=0 then
    dbms_output.put_line('没有这个员工');
  else
    dbms_output.put_line('有员工');
  end if;
end;

case when语句在代码块中也可以使用：

case when 条件判断 then

                sql执行语句;
         when 条件判断 then         

                sql执行语句;
         else

                sql执行语句;
end case;

-- 输入任意的员工编号，打印出有没有这个员工。

declare 
  v_empno number:=&请输入员工编号;
  c number;
begin
  select count(1) into c from emp where empno=v_empno;
  case when c=0 then
         dbms_output.put_line('没有这个员工');
       else
         dbms_output.put_line('有员工');
  end case;
end;

5.2 循环控制语句

不断的重复的做某一件事情。

a.for 循环

for 循环是在一个已知循环次数的场景中，去循环执行。

for  变量名字  in  循环范围  loop
    循环执行的sql语句;
end  loop;

--循环打印1到10

declare
begin
  for i in 1..10 loop
    dbms_output.put_line('循环的值是：'||i); 
  end loop;
end;

reverse 反向循环

declare
begin
  for i in reverse 1..10 loop
    dbms_output.put_line('循环的值是：'||i); 
  end loop;
end;
--结果为：10，9，8，7.......

-- 练习：打印1+2+3+..+100 的和

declare
  s number:=0;
begin
  for i in 1..100 loop
    s:=s+i;
  end loop;
  dbms_output.put_line(s); 
end;

-- 练习：打印100内所有奇数的和（1+3+5+7..+99 ）

declare
  s number:=0;
begin
  for i in 1..100 loop
    if mod(i,2)=1 then
      s:=s+i;
    end if;
  end loop;
  dbms_output.put_line(s); 
end;

--练习：打印出1-100以内，所有的包含了 7 的数字以及 7 的倍数的数字

declare 
begin
  for i in 1..100 loop
    if mod(i,7)=0 or instr(i,7)>0 then
      dbms_output.put_line(i); 
    end if;
  end loop;
end;

-- 练习：打印九九乘法表
 

declare 
begin
  for i in 1..9 loop
    for j in 1..i loop
      dbms_output_.put(j||'*'||i||'='||i*j||'   ');
    end loop;
    dbms_output.put_line('');
  end loop;
end;

-- 有35个动物，鸡和兔子，一共有94个脚，问有多少兔子和鸡

declare
  du number;
begin
  for ji in 0..35 loop
    du:=35-ji;
    if ji*2+du*4=94 then
      dbms_output.put_line(du||','||ji); 
    end if;
  end loop;
end;

-- 有个三位数，每一位都是 1 2 3 4 中的任意一个数字，
-- 1 2 3 4组合成的三位数中，百位、十位、个位都不重复的数字有多少个

declare
  c number:=0;
begin
  for bai in 1..4 loop
    for shi in 1..4 loop
      for ge in 1..4 loop
        if bai!=shi and bai!=ge and shi!=ge then
          c:=c+1;
        end if;
      end loop;
    end loop;
  end loop;
  dbms_output.put_line(c); 
end;

b. while 循环

while 循环是在循环之前先进行判断，如果符合条件的，那么就进入循环，如果条件不满足了就退出循环。

while  条件判断  loop
   循环执行的sql;
end  loop;

--用while循环打印1到10

declare
  n number:=1;
begin
  while n<=10 loop
    dbms_output.put_line(n);
    n:=n+1;
  end loop;
end;

--珠穆朗玛峰高度是8848米，纸厚度是1mm，纸要对着多少次，厚度才能超过山的高度

declare
  shi number:=1;
  shan number:=8848000;
  c number:=0;
begin
  while shi<=shan loop
    shi:=shi*2;
    c:=c+1;
  end loop;
  dbms_output.put_line(c); 
end;

c. loop 循环

无论如何先执行一次语句，然后再去判断条件，如果条件是满足的，那么就退出循环，如果条件不满足就继续循环。

loop
  循环执行的sql;
  exit  when  条件判断 ;
  循环执行的sql;
end  loop;

--用loop循环打印1到100

declare
  n number:=1;
begin
  loop
    dbms_output.put_line(n);
    exit when n>=100;
    n:=n+1;
  end loop;
end;

d.循环中的两个关键字 continue和exit

continue 是跳过当前这一次的循环，直接从下一次的循环开始

declare
begin
  for i in 1..10 loop
    if i=5 then
      continue;
    end if;
    dbms_output.out_line(i);
  end loop;
end;
-- 1,2,3,4,6,7,8,9,10

exit 是跳出循环，结束当前循环结构

declare
begin
  for i in 1..10 loop
    if i=5 then
      exit;
    end if;
    dbms_output.put_line(i);
  end loop;
end;
--1,2,3,4

六、游标结构（静态游标、动态游标、隐性游标)

6.1 静态游标

游标是一个从上往下读取表格每一行数据的虚拟出来的概念。游标会自己从上往下移动读取数据。

操作游标的基本方法：

1.声明一个游标

    cursor 游标名字 is select 查询语句;

2.打开游标

    open 游标名字;

3.用游标去获取表格结果集的行数据

    fetch 游标名字 into 变量;

4.关闭游标

    close 游标名字;

--一个简单例子

declare
  --声明一个游标
  cursor yb is select ename,job,sal from emp where deptno=20;
  --准备三个变量
  v_ename varchar2(50);
  v_job varchar2(50);
  v_sal number;
begin
  --打开游标
  open yb;
  --获取游标的内容存入到变量中
  fetch yb into v_ename,v_job,v_sal;
  --如果当前的游标找到了行数据，那就进行循环
  while yb%found loop
    dbms_output.put_line(v_ename||'-'||v_job||'-'||v_sal);
    fetch yb into v_ename,v_job,v_sal;
  end loop;
  close yb;
end;

--用loop循环改成上面的SQL

declare
  --声明一个游标
  cursor yb is select ename,job,sal from emp where deptno=20;
  --准备三个变量
  v_ename varchar2(50);
  v_job varchar2(50);
  v_sal number;
begin
  open yb; --打开游标
  loop
    fetch yb into v_ename,v_job,v_sal; --获取游标的内容存入到变量中
    exit when yb%notfound;
    dbms_output.put_line(v_ename||','||v_job||','||v_sal); 
  end loop;
  close yb;    --关闭游标
end;

--用for循环

declare
  cursor yb is select ename,job,sal from emp where deptno=20;
begin
  for i in yb loop
    dbms_output.put_line(i.ename||','||i.job||','||i.sal); 
  end loop;
end;

--for循环也可以直接读表格行数据

declare
begin
  for i in (select ename,job,sal from emp where deptno=20) loop
    dbms_output.put_line(i.ename||','||i.job||','||i.sal); 
  end loop;
end;

6.2 动态游标

静态和动态游标，都称为显性的游标，也就是游标的操作是我们写脚本的手动操作的。

静态就是在声明的部分去指定游标的内容，动态在赋值的时候去指定游标的内容的。静态游标一个游标只能是一个结果集的内容，动态游标可以修改游标里面存储的结果集的内容。

1.声明一个动态游标的数据类型

        type 动态游标的类型 is ref cursor;

2.声明一个游标的变量，数据类型是上面自己声明的类型

        游标的变量 动态游标的类型;

3.在begin里面，在打开游标的时候，给游标赋值

        open 游标的变量 for select 查询语句;

4.抓取当前游标所在的行的内容

        fetch 游标的变量 into 其他的变量;

5.关闭游标

        close 游标的变量;

--例子

-- 分批次查询emp表3个不同的部门有哪些员工

declare
  type dongtaiyb is ref cursor; --声明一个动态游标的数据类型
  yb dongtaiyb;    --声明一个动态游标类型的变量
  v_ename varchar2(50);  --声明一个变量存储员工的名字 
begin 
  for i in (select distinct deptno from emp order by deptno) loop
    dbms_output.put_line('部门：'||i.deptno);
    open yb for select ename from emp where deptno=i.deptno;  --在打开游标的时候，给游标赋值
    loop 
      fetch yb into v_ename;
      exit when yb%notfound;
      dbms_output.put_line(v_ename);
    end loop;
    close yb;  --关闭游标
  end loop;
end;

动态游标的执行语法，只能用loop和while来搞定，不能使用for循环，但是for循环有类似的解决方法：

-- 分批次查询3个不同的部门员工信息

declare 
begin
  for i in (select distinct deptno from emp order by deptno) loop
    dbms_output.put_line('部门：'||i.deptno); 
    for j in (select ename from emp where deptno=i.deptno) loop
      dbms_output.put_line(j.ename);      
    end loop;  
  end loop;
end;

6.3 隐性游标

在查询和数据更新的过程中，使用隐性游标，查看数据访问的范围。

隐性游标有个统一的名字：sql

它有三个常见的属性：

        sql%rowcount    查询隐性游标操作数据的行数

        sql%found      游标是否找到数据

        sql%notfound    游标是否没找到数据

declare
  v_empno number:=&请输入员工编号;
begin
  delete from emp where empno=v_empno;-- 光看delete的表现，是看不出来员工数据是否被删除的
  -- 通过隐性游标来判断这一次delete的dml操作是否成功了
  if sql%found then
    dbms_output.put_line('删除了'||v_empno||'这个员工');
  else
    dbms_output.put_line('没有删除任何员工');
  end if;
end;

隐性游标只有在commit或者在rollback之前使用才是有效的，一旦提交了，隐性游标的数据就会被清空。

如果有多次DML或者DQL的操作，隐性游标永远只会记录最后一次操作。

-- 更新emp表格的员工工资，如果工资低于2000的，统一涨薪10%
-- 更新完成之后，统计一下，涨薪的员工占总员工数量的百分之多少

declare
  c1 number;
  c2 number;
begin
  select count(1) into c1 from emp;
  update emp set sal=sal*1.1 where sal<2000;
  c2:=sql%rowcount;  --把隐性游标操作数据的行数赋值给c2
  dbms_output.put_line('修改的比例是'||round(c2/c1*100,2)); 
end;

七、异常数据处理

7.1 系统的预定义异常

数据库一开始就内置的一些常见的错误和异常，内置了21种。这些错误在数据库中是有错误编号和错误名字的。如：

        too_many_rows       select查询返回多行结果

        no_data_found           select查询没有结果

        value_error       赋值的错误，值和变量的类型不一致

        zero_divide         除数为0的错误

        dup_value_on_index        在主键和唯一约束上插入了重复的数据

        ...

异常捕获的基本格式如下：

declare
  声明
begin
  执行
exception
  when 错误的类型 then
     错误的时候执行的sql;
end;

--例子

declare
  n1 number:=&请输入数字;
  n2 number:=&请输入另一个数字;
begin
  dbms_output.put_line(n1/n2); 
exception
  when zero_divide then  --zero_divide表示除数为0的错误
    dbms_output.put_line('n2当前的值是'||n2); 
end;

在when的错误分支判断的位置，写具体的错误名字，可能无法捕捉所有的错误，所以我们用others进行代替，others表示任意的一种错误。

declare
  n1 number:=&请输入数字;
  n2 number:=&请输入另一个数字;
begin
  dbms_output.put_line(n1/n2); 
exception
  when others then
    dbms_output.put_line(sqlcode); --sqlcode只查看错误的编号
    dbms_output.put_line(sqlerrm); --sqlerrm查看错误的编号以及错误的描述
end;

7.2 系统的非预定义错误

在oracle中有错误的编号，但是没有错误的名字的异常，叫做非预定义异常。

-- 给表格先添加一个主外键的关联
alter table dept add constraints pk_dept primary key (deptno);
alter table emp add constraints fk_dept foreign key (deptno)
references dept(deptno);

declare
begin
  delete from dept where deptno=10;
  commit;
exception
  when others then
    dbms_output.put_line(sqlerrm); 
end;


declare
begin
  insert into emp(empno,ename,deptno) values(7890,'HAN',77);
  commit;
exception
  when others then
    dbms_output.put_line(sqlerrm); 
end;

7.3 自定义异常

不是语法导致的错误，我们自己定义的业务流程的错误，不允许数据走到某个业务的流程中，如果走到了，就报错禁止。

  raise_application_error(-错误编号, '错误描述');

错误编号范围：-20000 ~ -20999

--删除一个员工的数据，但不允许删除老板和经理。

declare
  v_empno number:=&员工编号;
  j varchar2(50);
begin
  select job into j from emp where empno=v_empno;
  if j in ('MANAGER','PRESIDENT') then 
    -- 手动报错,报错后plsql停止在报错的地方，不会执行下面的SQL
    raise_application_error(-20001,'不能删除领导！'); 
  end if;
  delete from emp where empno=v_empno;    -- 不允许删除老板和经理
exception
  when others then
    dbms_output.put_line(sqlerrm); 
end;

在数据的操作过程中，有的时候不希望因为偶尔出现的错误导致整个程序被中断，希望能够捕获和保存出现的错误，将没有出现错误的数据进行正常的操作。

declare
  n number;
  e varchar2(50);
begin
  for i in (select * from emp) loop
    begin
      n:=i.empno;
      e:=i.ename;
      if i.comm is null then
        raise_application_error(-20001,'奖金为空的数据不要！');
      else
        insert into emp_copy(empno,ename,sal,comm)
        values(i.empno,i.ename,i.sal,i.comm);
        commit;
      end if;
    exception
      when others then
        dbms_output.put_line(n||','||e); 
    end;
  end loop;  
end;

八、动态SQL

在代码块中，DCL语句还有DDL语句都无法直接执行，运行会报错，这些语句都只能通过动态sql来执行。

动态sql就是用字符串定义和存储sql语句，然后用 execute immediate 来执行这个字符串的操作。

动态sql执行场景

        1.当需要执行的sql是DCL或者DDL语句的时候：

declare
  s varchar2(4000);
begin
  s:='truncate table emp_copy';
  execute immediate s;
end;

        2.需要循环的查询某个表格，通过动态sql查询，可以起到一个加速的作用

        在oracle数据库中有一个缓存池，如果是动态sql执行的查询结果，将结果保存到缓存池中,下一次在查询相同的句子和表格，直接从缓存池中拿数据。

declare
  v_ename varchar2(20);
  v_sal number;
begin
  for i in (select empno from emp) loop
    execute immediate 
    'select ename,sal from emp where empno=:1' into v_ename,v_sal using i.empno;
    dbms_output.out_line(v_ename)
  end loop;
end;

        3.当我们需要操作的数据的条件，是存放在另一个表格中的时候，需要通过拼接字符串的方式才能实现sql语句的执行：

create table new_sal(
empno number,
new_s number,
next_year number
);
insert into new_sal values(7369,1200,2024);
insert into new_sal values(7788,3300,2024);

declare 
begin
  for i in (select * from new_sal) loop
    execute immediate
    'update emp set sal=:1 where empno=:2' using i.new_s,i.empno;
    commit;
  end loop;
end;

在动态sql中，:1 :2 :3 通过using传值的方式，只能写在 = 后面，如果不是 = 后面的动态数据，只能用拼接字符串的方式来实现。

declare
  tb varchar2(50):='emp_copy';
begin
  execute immediate 'truncate table '||tb;
end;

练习题1

--每天都创建一个表格的备份表，备份表的内容和结构与原表相同
--备份表的名字是   原表名_0718    
--emp  备份表就是  emp_0718

declare
  s varchar2(4000);
begin
  for i in (select table_name from user_tables) loop
    s:=
    'create table '||i.table_name||'_'||to_char(sysdate,'mmdd')
    ||' as select * from '||i.table_name;  
    execute immediate s;
  end loop;
end;

--删除所有今天日期的备份表

declare
  s varchar2(2000);
begin
  for i in (select table_name from user_tables where substr(table_name,-4)=to_char(sysdate,'mmdd')) loop
    execute immediate 'drop table '||i.table_name;
  end loop;
end;

练习题2

--有一个分区表

create table sales_part(
saleid number,
saleman varchar2(50),
price number,
saledt date
)partition by range(saledt)(
   partition d202407015 values less than (date'2024-7-26'),
   partition d202407016 values less than (date'2024-7-27')
);

select * from user_tab_partitions where table_name='SALES_PART';

--编写一个匿名块，为sales_part分区表创建昨天的分区
--先判断昨天的分区是否存在，不存在的话，就创建一个

declare
  c number;
  s varchar2(4000);
begin
  select count(1) into c from user_tab_partitions where table_name='SALES_PART'
  and partition_name='D'||to_char(sysdate-1,'yyyymmdd');
  if c=0 then
    s:='alter table sales_part add partition d'||to_char(sysdate-1,'yyyymmdd')||
    ' values less than (date'''||to_char(sysdate,'yyyy-mm-dd')||''')';
    dbms_output.put_line(s); 
    execute immediate s;
  end if;
end;

练习题3

--有下面两个表格

create table products(
product number,
protype varchar2(10),
price number,
prolevel varchar2(10)
);
insert into products values(101,'A',300,null);
insert into products values(102,'B',45,null);
insert into products values(103,'A',160,null);
insert into products values(104,'B',500,null);

create table conditions(
condition varchar2(500),
results varchar2(10)
);
insert into conditions values('protype=''A'' and price>200','优');
insert into conditions values('protype=''A'' and price<=200','良');
insert into conditions values('protype=''B'' and price>200','优');
insert into conditions values('protype=''B'' and price<=200','差');

--根据conditions表格的条件，将results对应的结果更新到products表格的prolevel字段

declare
  s varchar2(2000);
begin
  for i in (select * from conditions) loop
    s:='update products set prolevel='''||i.results||''' where '||i.condition;
  end loop;
end;

练习题4

--查询出距离下一个2月29号还有多少天

declare
  d date:=trunc(sysdate,'dd');
  c number:=0;
begin
  while to_char(d,'mmdd')!='0229' loop
    d:=d+1;
    c:=c+1;
  end loop;
  dbms_output.put_line(c); 
end;

九、存储过程

上面学的匿名块都是临时运行的代码块，如果一个代码块只是当下需要运行，使用匿名块，如果是经常需要被调用的，使用存储过程。存储过程就是一个有名字的代码块。

和匿名块的区别，只是存储过程需要被创建。

1.没有参数的存储过程

create or replace procedure 存储过程的名字
as
    变量的声明部分;
begin
    代码的执行部分;
exception
    异常捕获部分;
end;

2.有输入参数的存储过程（参数的数据类型是没有长度和精度的概念的。如 s in varchar2）

create or replace procedure 存储过程的名字(参数名字 in 数据类型)
as
    变量的声明部分;
begin
    代码的执行部分;
exception
    异常捕获部分;
end;

3.有输入输出参数的存储过程(有out输出参数的存储过程，需要通过代码块的方式才能实现调用）

create or replace procedure 存储过程的名字(参数名字 in 数据类型, 参数 out 数据类型)
as
    变量的声明部分;
begin
    代码的执行部分;
exception
    异常捕获部分;
end;

存储过程的调用：

1.通过call语句调用
call 存储过程名字();
2.通过其他的代码块调用
declare
begin
  存储过程名字();
end;

--例子：没有参数的存储的创建

create or replace procedure pcd01
as
  n1 number;
  n2 number;
begin
  n1:=2;
  n2:=54;
  dbms_output.put_line(n1+n2);
end;

--通过call语句调用
call sp01();
--通过其他的代码块调用
declare
begin
  sp01();
end;

--例子：有参数的存储的创建(参数的数据类型是没有长度和精度的概念的)

--如str in varchar2后面不用写长度

create or replace procedure sp02(n1 in number,n2 in number)
as
begin
  dbms_output.put_line(n1+n2);
end; 

call sp02(9,18);

--例子：有输出参数的存储过程

create or replace procedure sp03(s in varchar2,le out number)
as
begin
  le:=length(s); 
end;

--有out输出参数的存储过程，需要通过代码块的方式才能实现调用
declare
  l number;
begin
  sp03('hello',l);
  dbms_output.put_line('当前的长度是'||l); 
end;

变量和参数的区别：

1. 变量是有长度和精度，参数只有类型
2. 变量是在as里面声明的，参数是定义在create的部分
3. 输入的参数在begin中是不能被重新赋值的

在什么时候会使用存储过程？

1. 一个代码块经常需要被调用，使用存储过程来定义
2. 使用存储过程运行效率比普通的sql或者匿名块要更高（存储过程在create的阶段被编译的，在调用的时候直接运行就可以了）
3. 编写的sql语句的逻辑比较复杂，使用存储过程将复杂的逻辑进行拆分，分步运行

十、自定义函数

概念

在数据库的系统中有很多的预制的函数，但是不是所有的函数都满足现在项目组的业务和需求，所以需要自己创建一些函数来满足特殊的要求。

创建

create or replace function 函数名(输入参数 类型)
return 返回的数据的类型
as
    变量的声明部分;
begin
    代码执行的部分;
    return 值;
exception
    异常处理的部分;
end;

--例子

create or replace function upperEnd(s varchar2)
return varchar2
as
  os varchar2(2000);
begin
  os:=concat(lower(substr(s,1,length(s)-1)),upper(substr(s,-1)));
  return os;
end;

--调用
select upperEnd(ename) from emp;

函数和存储过程的区别：

1. 函数是专门处于数据的格式转换或者数据的计算的，存储过程的内容是没有什么限制的，可以进行数据的迁移、复制、数据的增删改都行；
2. 在存储过程中，任何的sql语句都可以写，但是自定义函数中，只能写数据的查询，无法编写DML DDL DCL TCL；
3. 存储过程是在call里面或者代码块中执行的，自定义函数是在查询中或者是DML过程中执行的；
4. 存储过程有in和Out两种参数，自定义函数只有in的参数，输出是通过return来实现的，存储过程的代码中也可以写return，只不过没有返回数据的，作用是结束整个存储过程的运行。

练习题1

--编写一个函数，作用是输入一个英文字符串，将奇数位的字母都小写，偶数位的字母都大写

create or replace function trans_strings(s varchar2)
return varchar2
as
  s2 varchar2(400);
begin
  for i in 1..length(s) loop
    if mod(i,2)=1 then
      s2:=s2||lower(substr(s,i,1));
    else
      s2:=s2||upper(substr(s,i,1));
    end if;
  end loop;
  return s2;
end;

select trans_strings(ename) from emp;

练习题2

--现有一个表格test25如下

create table test25(
ename varchar2(50),
hiredate date,
sal number,
comm number
);
insert into test25 values('aa',date'2023-7-8',1000,null);
insert into test25 values('bb',date'2019-4-6',1500,null);
insert into test25 values('cc',date'2019-3-9',1300,300);
insert into test25 values('dd',date'2014-1-19',1800,null);
insert into test25 values('ee',date'2008-10-23',2100,null);

-- 编写一个函数，计算奖金为多少
--（入职5年以内奖金是0,大于等于5年小于10年奖金是工资的5%，10年到15是10%，每多5年就多5%的奖金）

create or replace function get_comm(dt date,s number)
return number
as
  c number;
begin
  c:=floor(months_between(sysdate,dt)/12/5)*0.05*s;
  return c;
end;

-- 编写一个存储过程，对整个test25的表格进行comm字段的更新，只更新comm空的行

create or replace procedure set_comm
as
begin
  for i in (select * from test25 where comm is null) loop
    update test25 set comm=get_comm(i.hiredate,i.sal) 
    where ename=i.ename;
    commit;
  end loop;
end;

call set_comm();

练习题3

创建函数RANDOM_CHAR来产生指定长度的随机字符串，这个函数有两个输入的参数，分别是：
        P_LENGTH                        产生随机字符的长度
        P_INCLUDE_CHAR         产生的随机字符是否包含字母，否则只能为数字
例如
        RANDOM_CHAR(5, 1)    返回5个字符长度的随机英文+数字
        RANDOM_CHAR(10, 0)   返回10个字符长度的随机数字

 

create or replace function random_char(p_length number,p_include_char number)
return varchar2
as
  s varchar2(2000);
begin
  if p_include_char not in (0,1) then
    raise_application_error(-20000,'p_include_char只能是0或者1！！');
  end if;
  for i in 1..p_length loop
    if p_include_char=1 then
      s:=s||dbms_random.string('x',1);
    else
      s:=s||substr(dbms_random.random(),-1);
    end if;
  end loop;
  return s;
end;

select random_char(5,1) from dual;

保存点

设置存档点：savepoint 存档点名字;

回到存档点：rollback to 存档点名字;

declare
begin
  savepoint a;
  insert into dept values(50,'curry','beijing');
  savepoint b;
  insert into dept values(60,'james','beijing');
  savepoint c;
  insert into dept values(50,'kobe','beijing');
exception
  when others then
    rollback to a;
end;

返回表格类型数据的自定义函数

上面所有的自定义函数，返回的都是单一的数据类型（时间 数字 字符串），假如需要对数据进行拆分，将拆分后的数据进行返回，那么得到的结果不一定是一行，也有可能是多行，需要返回表类型数据的自定义函数。

--假如有一个字符串 abcdefg，拆分这个字符串，每个字母单独占一行。

1. 创建一个表类型
2. 创建一个自定义函数，返回的类型是表类型
3. 对数据进行转换和计算等，给表类型一行行的赋值
4. 通过 table() 以表格的形式，展示返回的数据

--1. 创建一个表类型
--is table的语法，暂时只能添加一个字段在虚拟的表格中
create type tb is table of char(1);

--2. 创建一个自定义函数，返回的类型是表类型
create or replace function split_strings(s varchar2)
return tb
as
  --在内存中初始化一个虚拟的表格，这个表格在初始化之后暂时是没有任何行
  t tb:=tb();
begin
  for i in 1..length(s) loop
    --对数据进行转换和计算等，给表类型一行行的赋值
    t.extend();     --先表格扩展一个空的行
    --3. 添加行数据的时候，给表格标注具体是第几行插入数据，i刚好是序号    
    t(i):=substr(s,i,1);
  end loop;
  --最后返回表格数据
  return t;
end;

--4. 通过 table() 以表格的形式，展示返回的数据
select * from table(split_strings('abcdefg'));

-- 输入一个有分隔符的字符串，例如 aaaa-bbbb-ccc-dddd         ffff/gggg/cccc

-- 定义一个函数，有两个参数，第一个参数是输入的字符串，第二个参数是分隔符，输出字符串为表格的形式，对字符串做拆分

create type tb is table of char(1);

create or replace function split_string02(str varchar2,sep varchar2)
return tb_string
as
  t tb_string:=tb_string();
  s varchar2(100);
  c number:=1;
begin
  for i in 1..length(str) loop
    if substr(str,i,1)!=sep then  --判断当前的字符是否是分隔符
      s:=s||substr(str,i,1);  --不是分隔符就拼接字符串
    else
      --遇到了分隔符，创建空行，插入刚才拼接的字符串
      t.extend();
      t(c):=s;
      c:=c+1;  --c用来记录表格的行数
      s:='';  --插入字符串后清空s
    end if;
  end loop;
  --最后拼接的字符串没有分隔符的，所以需要手动插入一次数据
  t.extend();
  t(c):=s;
  return t;
end;

select * from table(split_string02('aaaa-bbbb-ccc-dddd','-'));

create type tb_string is table of char(1);

create or replace function split_string03(str varchar2,sep varchar2)
return tb_string
as
  le number;
  s varchar2(2000):=str;  --因为参数不能重新赋值，所以我定义了一个变量
  t tb_string:=tb_string();
  c number:=1;
begin
  --查询有几个分隔符
  le:=regexp_count(str,sep);
  for i in 1..le loop
    --先找第一个分隔符的位置，将分隔符前面的所有的字符一起赋值给表格当前行
    t.extend();
    t(c):=substr(s,1,instr(s,sep)-1);
    --将赋值完的数据截取删除掉
    s:=substr(s,instr(s,sep)+1);
    c:=c+1;
  end loop;
  t.extend();
  t(c):=s;
  return t;
end;

select * from table(split_string03('aaaa/fff/ccc/dddd','/'));

--有下面一个game_a表

create table game_a(
qq number,
games varchar2(200)
);
insert into game_a values(6789901,'lol-王者荣耀-qq飞车');
insert into game_a values(88887763,'王者荣耀-奇迹暖暖-dnf-幻塔');

--编写一个函数把game_a表的数据显示成下面的样子

6789901 lol
6789901 王者荣耀
6789901 qq飞车

1.创建一个数据库对象，在对象里面定义多个字段

        create type 对象名字 is object (列名 数据类型, 列名 数据类型);

2.创建一个表类型，表类型的字段来自数据库对象的

        create type 表类型名字 is table of 对象名字;

-- 先创建表格和数据
create table game_a(
qq number,
games varchar2(200)
);
insert into game_a values(6789901,'lol-王者荣耀-qq飞车');
insert into game_a values(88887763,'王者荣耀-奇迹暖暖-dnf-幻塔');
--将表格的字符串进行拆分

create type qq_obj is object (qq number,game varchar2(50));
create type qq_tbl is table of qq_obj;

create or replace function split_qq(v_qq number,v_games varchar2)
return qq_tbl
as
  le number;
  s varchar2(2000):=v_games;  --因为参数不能重新赋值，所以我定义了一个变量
  t qq_tbl:=qq_tbl();
  c number:=1;
begin
  --查询有几个分隔符
  le:=regexp_count(v_games,'-');
  for i in 1..le loop
    --先找第一个分隔符的位置，将分隔符前面的所有的字符一起赋值给表格当前行
    t.extend();
    t(c):=qq_obj(v_qq, substr(s,1,instr(s,'-')-1));
    --将赋值完的数据截取删除掉
    s:=substr(s,instr(s,'-')+1);
    c:=c+1;
  end loop;
  t.extend();
  t(c):=qq_obj(v_qq, s);
  return t;
end;

declare
begin
  for i in (select * from game_a) loop
    for j in (select qq,game from table(split_qq(i.qq, i.games))) loop
      dbms_output.put_line(j.qq||' '||j.game); 
    end loop;
  end loop;
end;

十一、触发器

触发器分为前置触发器(before)和后置(after)触发器。基本格式如下：

create or replace trigger 触发器名字

before | after insert or update or delete on 表名

for each row

begin

触发器的内容

end;

:new 特指新出现的数据，这个数据之前在表格中不存在

:old 指现在就存在于表格中的数据

1.前置触发器

当想要去写入数据到表格中的时候，提前对数据进行检查，查看数据是否符合规则，如何符合允许数据写入，否则就拒绝

--如果插入的emp表格的用户，工资超过了3000，那么就禁止这个用户写入到emp表格中
create or replace trigger tri_ins
before insert on emp
for each row
begin
  if :new.sal>3000 then
    raise_application_error(-20000,'新员工工资不能超过3000！！！');
  end if;
end;

insert into emp (empno,ename,sal) values(1234,'aaa',3100);

--更新用户的数据，更新的奖金不能超过现在工资的10%
create or replace trigger tri_upd
before update on emp
for each row
begin
  if :new.comm>:old.sal*0.1 then
    raise_application_error(-20000,'奖金太高了');
  end if;
end;

--新增员工的时候，员工的岗位不能是老板，删除员工的时候也不能删除老板
create or replace trigger tri_emp
before insert or delete on emp
for each row
begin
  -- inserting  updating  deleting
  if inserting then 
    if :new.job='PRESIDENT' then
      raise_application_error(-20000,'新员工不能是老板');
    end if;
  elsif deleting then
    if :old.job='PRESIDENT' then
      raise_application_error(-20001,'不能删除老板');
    end if;
  end if;
end;

2.后置触发器

当数据已经写入到表格之后，对数据做其他的操作，例如进行数据的备份或者日志的更新等其他操作

--当emp表数据发生了变化，实时的对dept_sal_sum工资总和字段进行变更
create table dept_sal_sum(
deptno number,
sum_sal number
);

insert into dept_sal_sum select deptno,sum(sal) from emp group by deptno;

select * from dept_sal_sum;
1	30	9400
2	20	16352
3	10	9750

select * from emp;
update emp set sal=1000 where ename='SMITH';
insert into emp(empno,ename,sal,deptno) values(1234,'aaaa',500,10);
delete from emp where empno=1234;

create or replace trigger tri_dept_sal
after insert or update or delete on emp
for each row
begin
  if inserting then
    update dept_sal_sum set sum_sal=sum_sal+:new.sal 
    where deptno=:new.deptno;
  elsif updating then
    update dept_sal_sum set sum_sal=sum_sal+(:new.sal-:old.sal)
    where deptno=:new.deptno;
  elsif deleting then
    update dept_sal_sum set sum_sal=sum_sal-:old.sal 
    where deptno=:old.deptno;
  end if;
end;

--创建一个dept的日志信息表
create table dept_log(
deptno number,
dname varchar2(50),
loc varchar2(50),
operation varchar2(50),   --新增  更新之前  更新之后  删除
updatedt date
);

50  aa    shenzhen  新增      2024-7-20 10:02:18
20  SALES DALAS     更新之前  2024-7-20 10:03:18
20  SALES BEIJING   更新之后  2024-7-20 10:03:18
50  aa    shenzhen  删除      2024-7-20 10:05:22

create or replace trigger tri_dept_log
after insert or update or delete on dept
for each row
begin
  if inserting then
    insert into dept_log values(:new.deptno,:new.dname,:new.loc,
    '新增',sysdate);
  elsif updating then
     insert into dept_log values(:old.deptno,:old.dname,:old.loc,
    '更新之前',sysdate);
     insert into dept_log values(:new.deptno,:new.dname,:new.loc,
    '更新之后',sysdate);
  elsif deleting then
    insert into dept_log values(:old.deptno,:old.dname,:old.loc,
    '删除',sysdate);
  end if;
end;

--练习

create table user_info(
userid number,
username varchar2(50),
card char(18),
age number
);
--每次插入数据的时候，只会写入 userid,username,card 三个字段
--age是使用触发器通过card自动识别和写入的
create or replace trigger tri_user
before insert on user_info
for each row
begin
  :new.age:=floor(months_between(sysdate,to_date(substr(:new.card,7,8),'yyyymmdd'))/12);
end;

insert into user_info(userid,username,card) values(1,'aa','765432199807069807');

select * from user_info;

select * from yinhang;
select * from yh_lalian;
truncate table yinhang;
truncate table yh_lalian;

create table yinhang(
lilv varchar2(10),
update_dt date
);

create table yh_lalian(
lilv varchar2(10),
startdt date,
enddt date
);

insert into yinhang values('4.1%',date'2020-11-20');

create or replace trigger tri_yinhang
after insert on yinhang
for each row
begin
  declare
    c number;
    v_lilv varchar2(10);
  begin
    -- 判断当前写入的数据是否是第一条数据
    select count(1) into c from yh_lalian; 
    if c=0 then
      insert into yh_lalian values(:new.lilv,:new.update_dt,date'2999-12-31');
    else
      --先查询yinhang表格的最后一行数据的利率
      select lilv into v_lilv from (
      select lilv, 
      row_number() over(order by rowid desc) r
      from yh_lalian) where r=1;
      --对比新增的利率和上一个利率是否一样，不一样就修改拉链表
      if v_lilv!=:new.lilv then
         update yh_lalian set enddt=trunc(:new.update_dt-1,'dd') where enddt=date'2999-12-31';
         insert into yh_lalian values(:new.lilv,:new.update_dt,date'2999-12-31');
      end if;
    end if;
  end;
end;

十二、包

包本身是没有逻辑的，是为了方便管理存储过程和自定义函数的，在工作中都是将相同功能或者模块的代码块放在同一个包里面进行管理。

包分成两个部分，包规范（包定义）和包体。

先创建包规范：

create or replace package 包名
as
  procedure 过程名字(参数 in|out 类型);
  function 函数名字(参数 类型) return 返回的类型;
end 包名;

再去创建包体的部分：

create or replace package body 包名
as
  procedure 过程名字(参数 in|out 类型)
  as
  begin
  end;
  
  function 函数名字(参数 类型) return 返回的类型
  as
  begin
  end;
end 包名;

--例子

--创建一个练习用的表格
create table new_emp(
empno number,
ename varchar2(50),
sal number,
comm number,
deptno number
);

--包规范
create or replace package pkg_emp
as
  procedure export_emp(v_empno in number);
  
  function generate_empno(v_empno number) return number;
end pkg_emp;

--包体
create or replace package body pkg_emp
as
  procedure export_emp(v_empno in number)
    as
    begin
      execute immediate 'truncate table new_emp';
      if v_empno is null then
        dbms_output.put_line('所有数据'); 
        insert into new_emp(empno,ename,sal,comm,deptno)
        select empno,ename,sal,comm,deptno from emp;
      else
        dbms_output.put_line('个人数据'); 
        insert into new_emp(empno,ename,sal,comm,deptno)
        select empno,ename,sal,comm,deptno from emp where empno=v_empno;
      end if;
      commit;
    end;
  
  function generate_empno(v_empno number) return number
    as
    begin
      return substr(abs(dbms_random.random()),-4);
    end;
end pkg_emp;

select * from new_emp;
call pkg_emp.export_emp(7369);

select ename,pkg_emp.generate_empno(empno) from emp;

十三、定时任务

在oracle里面，创建一个自己写好的定时运行的程序和任务，通过 user_jobs 来查看当前所有的定时任务。

declare
  job number;
begin
  dbms_job.submit(
    job => job,    --这里会生成一个任务的id编号
    what => '存储过程的名字();',   --定义要运行的存储过程
    next_date => 时间格式,     --设置从什么时候开始运行
    interval => '时间的间隔'  --隔多久运行一次
  );
end;

-- 创建一个测试用的表格
create table test26(
vals varchar2(50),
updatedt date
);

-- 创建一个定时任务
declare
  job number;
begin
  dbms_job.submit(
    job => job,
    what => 'sp_test26();',
    next_date => sysdate,
    interval => 'sysdate+1/(24*60)'
  );
  commit;
end;

-- 删除一个定时任务
declare
begin
  dbms_job.remove(24);   --24是当前任务的编号
  commit;
end;

-- 禁用某个任务
declare
begin
  dbms_job.broken(25,true);
  commit;
end;

-- 重新打开任务
declare
begin
  dbms_job.broken(25,false);
  commit;
end;

-- 查看任务
select * from user_jobs;

--练习

create table yewu_info(
yw_id number,
yewu varchar2(50)
);
insert into yewu_info values(1,'银行-基金-证券-股票');
insert into yewu_info values(2,'基金-股票');
insert into yewu_info values(3,'证券-基金-股票');

create table yewu_base(
id number,
yewu_content varchar2(50)
);
insert into yewu_base values(1,'银行');
insert into yewu_base values(2,'基金');
insert into yewu_base values(3,'证券');
insert into yewu_base values(4,'股票');

查询yewu_info表格，将中文的内容，用对应的编码来进行代替：
1   1-2-3-4
2   2-4
3   3-2-4

select 
  a.yw_id, 
  listagg(b.id,'-') within group (order by instr(a.yewu,b.yewu_content)) yw
from yewu_info a join yewu_base b on instr(a.yewu,b.yewu_content)>0
group by a.yw_id;