Plsql

啥也不会，一学就废

已于 2025-03-14 17:44:29 修改

阅读量864

点赞数 6

文章标签： oracle 数据库

于 2025-03-10 20:34:42 首次发布

本文链接：https://blog.youkuaiyun.com/dhasdha/article/details/146161940

版权

plsql ---过程化查询语言
---plsql 才是 oracle 核心语言
---plsql 自己的核心变量判断循环
---plsql 就是 sql 的一个进阶

程序块

1. 无名块：没有名字，只能执行一次,不能存储
2. 有名块：有名字的 , 永久保存到数据库中，随时拿来调用 to_char

无名块的结构

declare -------------------声明部分(可选)
--声明变量、变量类型、游标...
begin ---------------------程序的开始，执行部分(核心)

--可以直接使用dml语句 delete from emp where deptno = 10;
--可以直接使用tcl语句 commit;
--可以使用select into语句
--不能直接使用ddl语句，但是可以通过动态sql来实现ddl语句。

exception -----------------异常部分(可选)
end -----------------------结束部分，必须有

declare
 变量1  变量类型; ---在程序块当中每完成一句话 必须加;
 变量2  变量类型;
 .............................. 
begin

end;

给变量赋值

1. 在 begin 里面用 := 给变量赋值

declare
 V_a varchar2(30);
begin
  V_a:='hello world'; 
  dbms_output.put_line(V_a);
end;

2. 在 declare 后面用 := 赋值

declare
 V_a varchar2(30) := 'hello world'  ; ---初始值   
begin
  dbms_output.put_line(V_a);
end;

3. 通过 select into 语句给变量赋值

declare
 V_ename  varchar2(30);
 V_job    varchar2(20);
 V_sal    number;---默认的是38 个长度
begin
  select ename,job,sal 
    into V_ename,V_job,V_sal ---注意 变量的个数 顺序 属性 要和 查询结果一致
     from emp 
      where empno=7839;
  dbms_output.put_line( V_ename||V_job||V_sal); --变成1个字符
end;
---注意 此时 变量 只能接受一个值，，只能处理一行数据

4.手动输入 &

declare
 V_empno number := '&提示词1' ;
 V_ename varchar2(30) := '&提示词2';---建议加上单引号
                                    ---提示词不能一样
 V_ename varchar2(20);
 V_job   varchar2(30);
 V_sal   number;
begin
  select ename,job,sal 
   into V_ename,V_job,V_sal 
    from emp 
     where empno=V_empno;   
 dbms_output.put_line(V_ename||V_job||V_sal);    
end;
-----------(注:若提示词相同，则输入弹窗只有一个输入口，输入一个值同时给两个变量赋值)

变量类型

number varchar date -----普通类型
1. 引用型变量类型 %type %rowtype
2. 记录型变量类型 record

%type

指引用型变量类型，可以引用某张表的某字段的属性，还可以引用已经存在的变量类型
优点： 1 . 不需要知道字段的属性的
2. 可以跟随字段的属性发生变化

语法：

declare
 变量1  表名.列名%type; --引用某张表的某字段的属性
 变量2  变量1%type;   ---引用已经存在的变量类型
begin
end;

%rowtype

row--行
定义：是引用某张表的一行（所有字段）的数据类型 --复合类型，此时变量是复合变量
优点
1. 可以不需要知道表的字段的属性
2. 也可以跟着改变
缺点
1. 固定不灵活

declare
 V_emp    emp%rowtype; ---引用emp所有字段的属性 表名%rowtype
                       ---8个属性 复合类型
                       ---v_emp  复合变量 8个小变量 V_emp.empno  V_emp.ename v_emp.job....
 V_dname  dept.dname%type;
begin
 select ename,job,sal,emp.deptno,dname
  into  V_emp.ename,V_emp.job,V_emp.sal,V_emp.deptno,V_dname
   from emp 
    left join dept 
     on emp.deptno=dept.deptno
      where empno=7566 ;   
  dbms_output.put_line(V_emp.ename||V_emp.job||V_emp.sal||V_emp.deptno||V_dname);   
end;
--复合变量在使用时候 要拆开使用

record

优点灵活用几个变量写几个
缺点声明时候麻烦一点
语法：

declare
 type 类型名 is record (变量1 变量类型,变量2 变量类型,变量3 变量类型....);----声明出一个变量类--复合类型
 变量名 类型名;  --复合变量 变量名.变量1   变量名.变量2 ....
begin
end;

在程序块当中 dml语句的使用

insert .
update
delete

returning 列名1,列名2 ...... into 变量1 ,变量2 .....

--注意：在 insert 插入数据的时候 returning 是将插入的数据交给变量

---update 的时候 returning 是将修改后的数据交给变量

---delete 的时候 returning 是删除的数据交给变量

ddl语句在程序块当中的使用

--需要动态sql 来实现

动态sql : 是指 sql语句加上单引号交给变量

语法:

declare
 变量    varchar2(300);  --------1. 声明变量
begin
 变量 :='sql语句';        -------2.定义动态sql 
                          -------1.sql必须是正常的能执行的sql语句

 execute immediate 变量   -------3.立即执行动态sql 
    [into  变量1.......] ;   -------将执行的结果交给变量1.....

end;

判断 if --分支判断

语法

declare 
begin
  if   条件1.... 
   then 执行代码1 ;-----当满足条件1 时要执行的代码
    elsif 条件2 .......
     then 执行代码2 ;---当满足条件2 时要执行的代码
    elsif 条件3 .......
     then 执行代码3 ;---当满足条件3 时要执行的代码
    elsif 条件4 .......
     then 执行代码4 ;---当满足条件4 时要执行的代码 
      .....................
    else  其他执行代码;---当不满足以上所有条件时 要执行的代码
   end if; ------------结束判断      
--注意：当满足了其中一个条件，就要执行对应的代码，然后直接执行 end if 
end;

循环

1. loop 循环：是一个无限循环死循环，必须配合退出循环条件
2. while 循环：自带条件的循环
3. for 循环：最主要的最常用的最好用最牛的循环

loop 循环

是一个无限循环死循环，必须配合退出循环条件

语法：

declare
begin
  loop             ---------循环的开始 
                   ---------1.循环主体  循环的内容  
                   ---------2.控制循环次数 --可选
     exit when 条件;  -------3.按照条件退出 满足条件退出循环
  end loop;  ---------------结束循环
end;

while 循环

语法：

declare
begin 
 while 条件    ----------------------进入循环的条件  满足条件才能进入循环
  loop         ----------------------循环的开始
   ----------------------------------1.循环主体 循环的内容
   ----------------------------------2.控制循环次数 可选
  end loop;   -----------------------结束循环
end;

for 循环*

语法：

declare
begin
  for  变量名 in x..y | (sql语句) | 游标  -----------------for循环自带的变量      
   loop  
     ----------------循环主体 循环的内容
   end loop;
end;

for循环的反转reverse

declare
begin
  for i in  reverse 1..100 loop
   if mod(i,2)=1 then 
      dbms_output.put_line(i);
   end if;
  end loop;   
end;

for 循环后跟SQL 语句

语法:

begin
   for 变量 In (sql语句) ---sql语句 必须加（） ，正常的语句 select ename from emp whenre empno=V_empno
    loop 
     ---循环主体
    end loop;
end;

declare
begin
  for i  in  (select ename,job from emp ) ----此时 变量 是复合变量  i.ename   i.job
    loop
      dbms_output.put_line(   i.ename||i.job  );
    end loop;   
end;

游标 cursor

定义：游标本质上是一个变动的光标，指向查询结果的每条数据，初始的时候指向第一条数据
作用：处理多行数据 ----for sql

语法:

declare
 cursor 游标名 is sql语句;  ----1.声明游标 定义游标
begin
 open 游标名;              -----2.打开游标
  fetch 游标名 into 变量名.... ; -----3. 提取游标 ***********
  ---1. 提取游标  2. 将提取的值交给变量 3.指针下一
 close  游标名;              --------4. 关闭游标
end;

---游标需要配合循环来使用

游标的属性
1. 游标名%found --当游标有值的时候返回 true 真条件成立
2. 游标名%notfound --当游标没有值的时候返回 true 真条件成立
3. 游标名%isopen ---判断游标是否打开如果是则返回 true
4. 游标名%rowcount --记录游标处理的行数返回的是数值

for 配合游标
1. for 循环会自动的打开和关闭游标
2. for 循环会自动的 fetch 游标

declare
 cursor c1 is select ename,job,sal from emp; --声明游标
begin
  for i in c1 ---游标 
    loop
      dbms_output.put_line(i.ename||i.job||i.sal);
    end loop;
end;

等价写法

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

有名块

函数 function

自定义函数
--特点：有且只有一个返回值
mod(4,2) --number

创建函数语法:

create [or replace] function  函数名(形参1 形参类型,形参2 形参类型.....)
return 返回值的类型
--------------------------------------上面的类型不能写长度
is
  --声明部分
begin
  --核心 实现函数的过程  
  return  结果; -----函数最终的得到的结果
end;
---当使用函数的时候 放入的参数的个数顺序属性 要和创建时一致

存储过程

--数据库对象之一
--和函数相比没有返回值
--存储过程是将要执行的任务或者程序存储起来
--随时拿来调用以及可以外接其他工具
--存储过程要比普通的代码块效率高

创建存储过程 procedure:

create [or replace] procedure 存储过程名(形参1 形参类型,形参2 形参类型.....)
is
begin
end;

调用存储过程
1. call 存储过程();
2. 程序块调用

存储过程的三种参数
IN 输入参数 --可以省略不写, 默认就是 in 输入参数
OUT 输出参数
IN OUT 输入输出参数

--OUT 输出参数
--打印输出7788的员工姓名(用输出参数方式来做)
CREATE OR REPLACE PROCEDURE sp_emp_out(v_name OUT VARCHAR2)
IS
BEGIN
  SELECT ename INTO v_name FROM emp WHERE empno=7788;
END;

--调用
DECLARE
v_name VARCHAR2(20);
BEGIN
  sp_emp_out(v_name);
  DBMS_OUTPUT.PUT_LINE(v_name);    
END;

触发器

[数据库对象之一] 由对一张表的dml操作引起的一系列触发事件
--关键字 TRIGGER
创建语法:

CREATE OR REPLACE TRIGGER 触发器名称 
AFTER | BEFORE  
[UPDATE] [OR] [DELETE] [OR] [INSERT]
ON 表名  
[FOR EACH ROW]
BEGIN
  --触发代码
END;
----------------------------------------------------------------
FOR EACH ROW 行级触发器  --dml影响了几行数据,触发器就会执行几次
如果不加 FOR EACH ROW 表级触发器  --无论dml影响了多少行数据, 触发只会执行一次!!!

触发器三种判断属性

inserting  --判断当前操作是否为 insert ,若是 返回TRUE
updating --判断当前操作是否为 update,若是 返回TRUE
deleting --判断当前操作是否为 delete ,若是 返回TRUE

行级触发器的2大属性:

:NEW   -- 更新之后的每一行数据
:OLD    --更新之前的每一行数据
------
UPDATE
:NEW  有
:OLD  有
-----
DELETE
:NEW  无
:OLD  有
-----
INSERT
:NEW  有
:OLD  无

异常 EXCEPTION

异常: 网络异常,硬件异常,软件异常
软件异常:预定义异常, 非预定义异常, 自定义异常

预定义异常 --数据库自带异常
异常组成: 异常代码,异常信息

DECLARE
v_name VARCHAR2(20);
BEGIN
  DBMS_OUTPUT.PUT_LINE(1/0); 
  SELECT ename INTO v_name FROM emp;
EXCEPTION   --异常处理(不是修正错误!!!)
   WHEN ZERO_DIVIDE THEN
     dbMS_OUTPUT.PUT_LINE('除数为0,请修改');
   WHEN TOO_MANY_ROWS THEN
      dbMS_OUTPUT.PUT_LINE('返回多行');

未知异常: OTHERS
错误信息: SQLERRM
错误代码: SQLCODE

DECLARE
v_name VARCHAR2(20);
BEGIN
  DBMS_OUTPUT.PUT_LINE(1/0); 
  SELECT ename INTO v_name FROM emp;
EXCEPTION   --异常处理(不是修正错误!!!)
   WHEN OTHERS  THEN
     dbMS_OUTPUT.PUT_LINE(SQLCODE||'   '||SQLERRM);
END;

非预定义异常

DECLARE
myexp   EXCEPTION ;  --第一步   定义一个异常名称
PRAGMA EXCEPTION_INIT(myexp,-00904) ;   --第二步 将异常的名字和异常代码进行绑定 
V_J VARCHAR2(11);
BEGIN
  SELECT JJJJ INTO V_J FROM EMP;
  EXCEPTION
    WHEN OTHERS THEN
      dbMS_OUTPUT.PUT_LINE(SQLCODE || SQLERRM);       
END;

自定义异常

DECLARE
myexp EXCEPTION;
PRAGMA EXCEPTION_INIT(myexp,-20001);  --自定义异常代码范围  [-20001  --  -20999]
BEGIN
  raise_application_error(-20001,'自定义');--自定义异常需要手动抛出
    EXCEPTION
    WHEN OTHERS THEN
      dbMS_OUTPUT.PUT_LINE(SQLCODE || SQLERRM); 
END;

包

由两部分组成包头和包体 (一系列存储过程或函数的集合)
PACKAGE 包头
PACKAGE BODY 包体

第一步创建包头

create or replace package 包名
is
  声名部分;--可以声名常量，变量等
  存储过程的声名;--只需要声名它名字和参数，不需要实现
  函数的声名; --函数声名名字，参数和返回值类型
end 包名;
---------------------------------------
CREATE OR REPLACE PACKAGE pak_tool
IS
  PROCEDURE SP_TEST1(V_DEPTNO NUMBER);
  PROCEDURE SP_TEST2(V_EMPNO NUMBER);
  FUNCTION F_EMP(V_EMPNO NUMBER) RETURN NUMBER;
END;

第二步创建包体

create or replace package body 包名     --名字跟包头的名字一致
IS
    声名部分;--包声名中的变量都要在这里声名，其次还可以声名一些包声名中没有变量
    存储过程的实现;
    函数的实现;
end;