跳至主要內容

PL/SQL

苓渊大约 8 分钟oracle

相关信息

PL/SQL（Procedural Language/Structured Query Language）是 Oracle 数据库中用于编写复杂数据库逻辑的一种过程式编程语言。它是在 SQL 之上扩展的一种编程语言，允许开发者在数据库中嵌入过程控制语句（如条件、循环、异常处理等），以增强对数据库操作的控制。

PL/SQL 支持存储过程、函数、触发器、包等数据库编程对象，它可以在数据库服务器端执行，并具有高效的性能和强大的功能。

特点

  • 集成 SQL： PL/SQL 完全支持 SQL 语言，可以在其中嵌入 SELECT、INSERT、UPDATE、DELETE 等标准 SQL 语句。
  • 过程式编程： 支持 IF、LOOP 等过程控制语句，具备与其他高级编程语言相似的流程控制能力。
  • 异常处理： 提供了强大的异常处理机制，可以捕获和处理运行时错误。
  • 模块化： 支持存储过程、函数、包和触发器等模块化编程，提高了代码的可维护性和重用性。
  • 性能优化： PL/SQL 代码在 Oracle 数据库服务器中直接执行，减少了客户端与服务器之间的通信，从而提升了效率。

结构

PL/SQL 的程序单元（如块、过程、函数等）由三部分组成：

  • 声明部分（Declarative Section）： 声明变量、常量、游标等。
  • 执行部分（Executable Section）： 执行实际的逻辑操作，包含 SQL 语句和流程控制语句。
  • 异常处理部分（Exception Section）： 处理运行过程中出现的异常或错误。
DECLARE
  -- 声明部分（可选）
  v_variable_name  datatype;
  
BEGIN
  -- 执行部分
  -- 写 SQL 操作、变量赋值、流程控制等操作
  SELECT column_name INTO v_variable_name FROM table_name WHERE condition;
  
EXCEPTION
  -- 异常处理部分（可选）
  WHEN exception_name THEN
    -- 异常处理逻辑
END;

使用方式

匿名块

PL/SQL 匿名块是无需存储在数据库中的临时程序，可以直接执行。它是最简单的 PL/SQL 程序单元，常用于简单的数据库操作。

DECLARE
  v_employee_name VARCHAR2(50);
BEGIN
  SELECT name INTO v_employee_name FROM employees WHERE id = 1;
  DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name);
END;

在这个示例中，我们声明了一个变量 v_employee_name，并从 employees 表中选择员工名字并输出到控制台。

示例： 根据一个员工的工资加奖金。

declare
 v_sal emp.sal%type;
begin
 select sal into v_sal from emp where empno = 7876;
 if v_sal>3000 then
  update emp set comm = nvl(comm,0)+500
        where empno = 7876;
    elsif v_sal>2000 then
  update emp set comm = nvl(comm,0)+300
        where empno = 7876;
    elsif v_sal>1000 then
  update emp set comm = nvl(comm,0)+100
        where empno = 7876;
  end if;
commit;
end;

示例： 查询编号7876员工的信息。

declare
 row_emp emp%rowtype;
 v_name emp.ename%type;
begin
 select * into row_emp from emp where empno = '7876';
 select dname into v_name from dept where deptno = row_emp.deptno;
  dbms_output.put_line('工号'||row_emp.empno||'姓名'||row_emp.ename||'工资'||row_emp.sal||'部门名'||v_name);
end;

存储过程

存储过程是一种事先编写好的 PL/SQL 程序，可以存储在数据库中并在需要时调用。它通常用于执行特定的数据库操作，如插入数据、更新数据、执行业务逻辑等。

创建存储过程：

CREATE OR REPLACE PROCEDURE update_salary (
  p_employee_id IN NUMBER,
  p_new_salary IN NUMBER
) AS
BEGIN
  UPDATE employees
  SET salary = p_new_salary
  WHERE id = p_employee_id;
END;

调用存储过程：

BEGIN
  update_salary(101, 5000);
END;

存储函数

存储函数与存储过程类似，但它返回一个值。存储函数通常用于计算和返回特定的结果。

创建存储函数：

CREATE OR REPLACE FUNCTION get_employee_salary (
  p_employee_id IN NUMBER
) RETURN NUMBER AS
  v_salary NUMBER;
BEGIN
  SELECT salary INTO v_salary FROM employees WHERE id = p_employee_id;
  RETURN v_salary;
END;

调用存储函数：

DECLARE
  v_salary NUMBER;
BEGIN
  v_salary := get_employee_salary(101);
  DBMS_OUTPUT.PUT_LINE('Employee Salary: ' || v_salary);
END;

触发器

触发器是数据库中的一种特殊的存储程序，它会在某个事件发生时自动执行，如在插入、更新或删除记录时触发。触发器通常用于数据的自动化处理、审计或复杂的约束管理。

创建触发器：

CREATE OR REPLACE TRIGGER update_audit
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
  INSERT INTO audit_log (employee_id, old_salary, new_salary, update_time)
  VALUES (:OLD.id, :OLD.salary, :NEW.salary, SYSDATE);
END;

在这个示例中，每当 employees 表中的记录被更新时，触发器 update_audit 会自动插入一条日志记录到 audit_log 表中。

包

包是 PL/SQL 中用于组织相关过程、函数、变量、游标等的集合。包将相关的逻辑封装在一起，提供了模块化和复用的功能。

包的定义通常分为包规范和包体两部分：

  • 包规范：定义包的公共接口，包含过程、函数的声明等。
  • 包体：包含过程和函数的具体实现。

示例：

-- 包规范
CREATE OR REPLACE PACKAGE employee_pkg AS
  PROCEDURE update_salary(p_employee_id IN NUMBER, p_new_salary IN NUMBER);
  FUNCTION get_salary(p_employee_id IN NUMBER) RETURN NUMBER;
END employee_pkg;

-- 包体
CREATE OR REPLACE PACKAGE BODY employee_pkg AS
  PROCEDURE update_salary(p_employee_id IN NUMBER, p_new_salary IN NUMBER) IS
  BEGIN
    UPDATE employees SET salary = p_new_salary WHERE id = p_employee_id;
  END;

  FUNCTION get_salary(p_employee_id IN NUMBER) RETURN NUMBER IS
    v_salary NUMBER;
  BEGIN
    SELECT salary INTO v_salary FROM employees WHERE id = p_employee_id;
    RETURN v_salary;
  END;
END employee_pkg;

选择结构

/*
if 条件 then
 代码块;
 continue;跳出本次循环
 exit;退出循环
 return;退出程序
 goto 锚; 跳到描点继续执行
elsif 条件 then
 代码块
else
 代码块
end if;
*/

循环结构

用于遍历游标(集合)。

loop:直接开始循环，当满足指定条件后结束循环。

while for:当满足指定条件后开始循环。

示例： loop 循环输出1，2，3，5，6.

declare
 v_i number(2):=0;
begin
 loop
  exit when v_i>=6;--退出条件
  v_i:=v_i+1;
  if v_i = 4 then
   continue;--跳出本次循环
   /*exit; 退出循环*/
   /*return; 退出程序*/
  end if;
  dbms_output.put_line(v_i);
 end loop;
end;

示例： while 循环输出 1，2，3，4，5.

declare
 v_i number(2):=1;
begin
 while v_i<=5 loop
  dbms_output.put_line(v_i);
  v_i:=v_i+1;
 end loop;
end;

示例： for 循环输出 1，2，3，4，5.

declare
begin
 for v_i in 1..5 loop
  dbms_output.put_line(v_i);
 end loop;
end;

示例： 打印九九乘法表。

declare
begin
 for i in 1..9 loop
  for j in 1..9 loop
   if (j<=i) then
    dbms_output.put(j||'*'||i||'='||i*j||'  ');
   end if;
  end loop;
 dbms_output.put_line(chr(10));/*chr(10):换行*/
 end loop;
end;

异常

程序在执行时报错，导致无法继续运行。通过异常处理，让程序继续运行。

Oracle预定义的21种异常

declare
     v_i  emp.sal%type;
begin
     dbms_output.put_line('程序开始执行');
     select sal into v_i from emp where empno=7788; 
     dbms_output.put_line('程序执行完成');
     /*异常处理模块,写在最后*/
     exception
            when TOO_MANY_ROWS then   
                dbms_output.put_line('返回多行结果');
            when others then
             dbms_output.put_line('r');
end;

自定义异常名称：

declare
     /*自定义异常类型的名称*/
     NO_ClASS_ID exception;
     /*把自定义的异常类型名称跟异常编号进行绑定*/
     pragma exception_init(NO_ClASS_ID,-02291);
begin
     update studentinfo set  sclassid=99 where stuid=11;
     exception
            when NO_ClASS_ID then   
                    dbms_output.put_line('没有该班级编号');
end;

游标

游标(cursor)：相当于Java中的集合。 处理多行记录使用游标。

不能重复打开一个游标，关闭游标后可以重新打开游标提取数据。

定义游标->打开游标->提取游标数据->关闭游标

/*定义游标*/
cursor cursor_name sys_refcursor;--系统数据类型
type cur_emp_type is ref cursor return emp%rowtype;--自定义游标类型
/*打开游标*/
open cursor_name is select...
/*提取游标数据*/
loop
 exit when cursor_name%notfound;
 fetch cursor_name into v_i;
 dbms_output.put_line(v_i);
/*关闭游标*/
close cursor;

显式游标

显式游标是定义的游标，分为静态和动态游标。

静态游标

静态游标在定义时里面的数据就固定了。

--loop循环遍历游标
declare
  row_dept dept%rowtype;
  cursor cur_dept is select * from dept;
begin
  open cur_dept;
  loop
    exit when cur_dept%notfound;
    fetch cur_dept into row_dept;
    dbms_output.put_line('部门编号：'||row_dept.deptno||',部门名：'||row_dept.dname);
  end loop;
  close cur_dept;
end;
--for循环遍历游标
declare
  row_dept dept%rowtype;
  cursor cur_dept is select * from dept;
begin
  for row_dept in cur_dept loop
   dbms_output.put_line('部门编号：'||row_dept.deptno||',部门名：'||row_dept.dname);
  end loop;
end;

动态游标

在程序运行时不能确定需要的数据，需要动态的获取。

declare
cursor cur_dept is select * from dept;
row_dept cur_dept%rowtype;
/*定义动态游标的数据类型：ref cursor*/
/*return emp%rowtype指定该类型游标只能存放emp的数据*/
type cur_emp_type is ref cursor return emp%rowtype;
/*定义游标变量，变量类型是cur_emp_type*/
cur_emp cur_emp_type;
/*cur_emp sys_refcursor 系统动态游标类型*/
row_emp cur_emp%rowtype;

begin
 open cur_dept;
  loop
   fetch cur_dept into row_dept;/*fetch循环将游标d数据赋给变量*/
   exit when cur_dept%notfound;/*游标没有数据了就返回true*/
   dbms_output.put_line('部门名称'||row_dept.dname);
   open cur_emp for select * from emp where deptno = row_deptno;
   loop
    fetch cur_emp into row_emp;
    exit when cur_emp%notfound;
    dbms_output.put_line('员工姓名'||ename)
   end loop;
   close cur_emp;
  end loop;
 close cur_dept;

隐式游标

当进行insert，update，delete操作时，系统会默认生成隐式游标。名字默认为sql

declare
begin
 update emp set sal = 666 where empno = 7788;
 /*isopen:游标是否打开，当insert，update，delete语句执行完后隐式游标自动关闭*/
 if sql%isopen then 
  dbms_output.put_line('sql游标打开');
 else 
  dbms_output.put_line('sql游标未打开');
 end if;
 /*
 found:用于隐式游标判断sql语句是否执行成功
    用于显式游标判断是否还有数据
 not found:与found相反
 */
 if sql%found then
  dbms_output.put_line('sql游标有数据');
 else
  dbms_output.put_line('sql游标没有数据');
 end if;
 /*rowcount:返回影响的行数*/
 dbms_output.put_line(sql%rowcount);
end;
上次编辑于:
贡献者: lingyuan