PL/SQL详述

1.PL/SQL简介

PL/SQL语言（Procedural Language/SQL，过程化SQL语言）是Oracle推出的过程化的SQL编程语言，使用PL/SQL可以为SQL语言引入结构化的程序处理能力，例如可以在PL/SQL中定义常量、变量、游标、存储过程等，可以使用条件、循环等流程控制语句。

PL/SQL的这种特性使得开发人员可以在数据库中添加业务逻辑，并且由于业务逻辑与数据均位于数据库服务器端，比客户端编写的业务逻辑能提供更好的性能。

PL/SQL是一种块结构的语言，它将一组语句放在一个块中，一次性发送给服务器，PL/SQL引擎分析收到的PL/SQL语句块中的内容，把其中的过程控制语句由PL/SQL引擎自身去执行，把PL/SQL块中的SQL语句交给服务器的SQL语句执行器执行。

PL/SQL块发送给服务器后，先被编译然后执行，对于有名称的PL/SQL块（如存储过程、函数、触发器、程序包）可以单独编译，永久的存储在数据库中，随时准备执行。

两个注意点：

1.在 END; 结束符的下一行开头加“/”作为结束标志。

2.运行SET SERVEROUTPUT ON; 语句。激活dbms_output包。

2.PL/SQL程序结构：（仅展示代码部分）

PL/SQL程序结构_月应是绝色306的博客-CSDN博客https://blog.csdn.net/qq_62687015/article/details/128119296?spm=1001.2014.3001.5501

--变量的定义和初始化。SET SERVEROUTPUT ON;DECLARE--声明部分标识v_job VARCHAR2(9);v_count BINARY_INTEGER DEFAULT 0;v_total_sal NUMBER(9,2):=0;v_date DATE:=SYSDATE;c_tax_rate CONSTANT NUMBER(3,2):=8.25;v_valid BOOLEAN NOT NULL:=TRUE;BEGINv_job:='MANAGER';--在程序中赋值DBMS_OUTPUT.PUT_LINE(v_job);--输出变量v_job的值DBMS_OUTPUT.PUT_LINE(v_count);--输出变量v_count的值DBMS_OUTPUT.PUT_LINE(v_date); --输出变量v_date的值DBMS_OUTPUT.PUT_LINE(c_tax_rate);--输出变量c_tax_rate的值END;--使用SELECT INTO语句赋值变量SET SERVEROUTPUT ON;DECLARE--声明部分标识v_job VARCHAR2(9);v_count BINARY_INTEGER DEFAULT 0;v_total_sal NUMBER(9,2):=0;v_date DATE:=SYSDATE;c_tax_rate CONSTANT NUMBER(3,2):=8.25;v_valid BOOLEAN NOT NULL:=TRUE;BEGINSELECT 'MANAGER' INTO v_job FROM dual;DBMS_OUTPUT.PUT_LINE(v_job);--输出变量v_job的值END;--根据职员表emp计算7788号职员的奖金。奖金的计算方法是：职员的工资(sal) *15%。DECLARE bonus_rateCONSTANT NUMBER(2,2) :=0.15;bonusNUMBER(7,2);emp_id NUMBER(4):= 7788; BEGINSELECT sal * bonus_rate INTO bonus FROMempWHERE empno= emp_id;DBMS_OUTPUT.PUT_LINE ( 'Employee: ' || TO_CHAR(emp_id)|| ' Bonus: ' || TO_CHAR(bonus) || ' Bonus Rate: ' || TO_CHAR(bonus_rate)); END;--程序输出结果为：--Employee: 7788 Bonus: 450 Bonus Rate: .15

--根据表中字段定义变量。DECLAREv_ename emp.ename%TYPE;--根据字段定义变量BEGINSELECT ename INTO v_ename FROM emp WHERE empno=7788;DBMS_OUTPUT.PUT_LINE(v_ename); --输出变量的值END;

3.PL/SQL控制结构

oracle选择结构和循环结构_月应是绝色306的博客-CSDN博客https://blog.csdn.net/qq_62687015/article/details/128120847?spm=1001.2014.3001.5501

--判断emp表中记录是否超过20条，10-20之间，或者10条以下。DECLARE--声明变量接受emp表中的记录数V_COUNT NUMBER;BEGIN--查询emp表中的记录数赋值给变量SELECT COUNT(*) INTO V_COUNT FROM EMP;--判断打印IF V_COUNT > 20 THENDBMS_OUTPUT.PUT_LINE('EMP表中的记录数超过了20条为：' || V_COUNT || '条。');ELSIF V_COUNT >= 10 THENDBMS_OUTPUT.PUT_LINE('EMP表中的记录数在10~20条之间为：' || V_COUNT || '条。');ELSEDBMS_OUTPUT.PUT_LINE('EMP表中的记录数在10条以下为：' || V_COUNT || '条。');END IF;END;

4.游标

游标：是系统开设的一个数据缓冲区，存放SQL语句的执行结果。

作用：用户可通过游标获取记录，并赋给变量。

当对数据库的查询操作返回一组结果集时，存入游标，以后通过对游标的操作来获取结果集中的数据信息。

游标分：显式游标和隐式游标。当查询语句返回多条记录时，必须显式地定义游标以处理每一行。其他的SQL语句(更新操作或查询操作只返回一条记录)都使用隐式游标。

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

游标的使用方式：声明—>打开—>读取—>关闭

游标的定义：--CURSOR  IS ;--例：CURSOR c_emp IS SELECT * FROM emp WHERE dno=3; --当需要操作结果集时，须完成：打开游标、使用FETCH语句将游标里的数据取出以及关闭游标操作。--游标声明：CURSOR游标名 IS 查询语句;--游标的打开：OPEN 游标名;--游标的取值：FETCH 游标名 INTO 变量列表;--游标的关闭：CLOSE 游标名;

--使用游标查询emp表中所有员工的姓名和工资，并将其依次打印出来。：DECLARECURSOR c_emp IS --声明游标SELECT ename, sal FROM emp; --声明变量用来接受游标中的元素v_ename emp.ename%TYPE;v_sal emp.sal%TYPE;BEGINOPEN c_emp; --打开游标LOOP--遍历游标中的值FETCH c_emp into v_ename, v_sal ;--通过FETCH语句获取游标中的值并赋值EXIT WHEN c_emp%NOTFOUND; --判断是否有值,有值打印,没有则退出循环DBMS_OUTPUT.PUT_LINE('姓名:' || v_ename || ',薪水:' || v_sal)END LOOP;CLOSE c_emp; --关闭游标END;--使用游标查询并打印某部门的员工的姓名和薪资，部门编号为运行时手动输入。DECLARECURSOR c_emp(v_empno emp.empno%TYPE) ISSELECT ename, sal FROM emp WHERE empno = v_empno;v_ename emp.ename%TYPE;v_sal emp.sal%TYPE;BEGINOPEN c_emp(10); --打开游标LOOP--遍历游标中的值FETCH c_emp INTO v_ename, v_sal ;--通过FETCH语句获取游标中的值并赋值EXITWHEN c_emp%NOTFOUND; --判断是否有值,有值打印,没有则退出循环DBMS_OUTPUT.PUT_LINE('姓名:' || v_ename || ',薪水:' || v_sal)END LOOP;CLOSE c_emp; --关闭游标END;

带锁游标： PL/SQL提供了一种加锁后删除或更新游标中刚取出那条记录的方法。

要想操纵数据库中的数据，在定义游标的查询语句时，必须加上FOR UPDATE子句，表示要先对表加锁；然后在UPDATE或DELETE语句中加上WHERE CURRENT OF子句，指定从游标工作区中取出的当前行需要被更新或删除。当会话打开一个带FOR UPDATE子句的游标时，在游标区中的所有行拥有一个行级排他锁，其他对话只能查询，不能更新或删除。

实例：为emp表的员工增加10％的工资,总额限制在90万元以内。

5.存储过程与存储函数：

PLSQL编程——存储过程_月应是绝色306的博客-CSDN博客https://blog.csdn.net/qq_62687015/article/details/128124551?spm=1001.2014.3001.5501Oracle 函数_月应是绝色306的博客-CSDN博客https://blog.csdn.net/qq_62687015/article/details/128154882?spm=1001.2014.3001.5501

查询并打印某个员工的姓名和薪水（有参）--创建存储过程CREATE OR REPLACE PROCEDURE P_QUERYNAMEANDSAL(i_empno IN emp.empno%TYPE) IS--声明变量接受查询结果v_ename emp.ename%TYPE;v_sal emp.sal%TYPE;BEGINSELECT ename, sal INTO v_ename, v_sal from emp WHERE empno = i_empno; --根据用户传递的员工号查询姓名和薪水DBMS_OUTPUT.PUT_LINE('姓名:' || v_ename || ',薪水:' || v_sal);--打印结果END;--调用存储过程call P_QUERYNAMEANDSAL(7839)--输入员工号查询某个员工信息，将薪水作为返回值输出，给调用的程序使用（带有输出参数）--创建存储过程CREATE OR REPLACE PROCEDURE P_QUERYSAL_OUT(i_empno IN emp.empno%TYPE,o_sal OUT emp.sal%TYPE) ISBEGINSELECT sal INTO o_sal FROM emp WHERE empno = i_empno;END;--调用存储过程DECLAREv_sal emp.sal%TYPE; --声明一个变量接受存储过程的输出参数BEGINP_QUERYSAL_OUT(7839, v_sal); --注意参数的顺序DBMS_OUTPUT.PUT_LINE(v_sal);END;

6.触发器

--在 emp表上创建一个触发器，该触发器是一个行级触发器。--当对 emp表的某职员sal工资列修改后，会引发该触发器并在审计表e_audit中写一条记录。--(1) 首先创建存放审计记录的表e_audit。CREATE TABLE e_audit(audit_id NUMBER(4),up_date DATE,new_sal NUMBER(7,2),old_sal NUMBER(7,2) );--(2) 再创建触发器audit_sal。CREATE OR REPLACE TRIGGER audit_salAFTER UPDATE OF sal ONemp FOR EACH ROWBEGININSERT INTO e_audit VALUES( :OLD.empno, SYSDATE,:NEW.sal,:OLD.sal); -- 在行触发器中，被修改记录的每列都有两个相关名称，对应旧值和新值。:OLD.和:NEWEND;

--操作相关表，使得某部门职工的薪水增加15%。--给指定部门的员工加薪，首先要把该部门的员工选出来，再对这些员工的薪水进行改动。--这里用存储过程表示，将要加薪的部门作为参数：CREATE OR REPLACE PROCEDURE add_sal (deptname varchar2)AS BEGINUPDATE empSET emp.salary=emp.salary*1.15 WHERE emp.eno IN(SELECT eno FROM dept WHERE dname= deptname);END ; --利用触发器建立追踪--通过对emp表的salary属性创建一个触发器，来监视其更新并进行记录，以追踪薪水变动情况：CREATE OR REPLACE TRIGGER salary_change 　BEFORE DELETE OR INSERT OR UPDATE ON emp　--触发事件FOR EACH ROW　　　　　 -- 每更新一行都需要调用此触发器DECLARE --只有触发器的声明需要DECLARE，过程和函数都不要salary1 NUMBER;BEGIN--:new与:old分别代表该行在修改前、改后的记录salary1=:new.salary - old.salary;DBMS_OUTPUT.PUT_LINE('old salary is:'|| :old.salary);DBMS_OUTPUT.PUT_LINE('new salary is:'|| :new.salary); DBMS_OUTPUT.PUT_LINE('add is:'||to_char(salary1)); END ;