PL/SQL详述

最新推荐文章于 2024-09-26 20:04:34 发布

努力的小羽儿

最新推荐文章于 2024-09-26 20:04:34 发布

阅读量1.2w

点赞数 33

分类专栏： oracle数据库（含sql语句）文章标签： sql 数据库 oracle

本文链接：https://blog.csdn.net/qq_62687015/article/details/128208819

版权

oracle数据库（含sql语句）专栏收录该内容

26 篇文章 8 订阅

订阅专栏

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;
	BEGIN
		v_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;

	BEGIN
		SELECT 'MANAGER' INTO v_job FROM dual;
		DBMS_OUTPUT.PUT_LINE(v_job);    --输出变量v_job的值
	END;


--根据职员表emp计算7788号职员的奖金。奖金的计算方法是：职员的工资(sal) *15%。
DECLARE 
  bonus_rate  CONSTANT NUMBER(2,2) :=0.15;
  bonus        NUMBER(7,2);
  emp_id       NUMBER(4)  := 7788; 
BEGIN
  SELECT sal * bonus_rate INTO bonus FROM  emp
    WHERE 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

--根据表中字段定义变量。
DECLARE
	v_ename emp.ename%TYPE;--根据字段定义变量
BEGIN
	SELECT 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 THEN
    DBMS_OUTPUT.PUT_LINE('EMP表中的记录数超过了20条为：' || V_COUNT || '条。');
  ELSIF V_COUNT >= 10 THEN
    DBMS_OUTPUT.PUT_LINE('EMP表中的记录数在10~20条之间为：' || V_COUNT || '条。');
  ELSE
    DBMS_OUTPUT.PUT_LINE('EMP表中的记录数在10条以下为：' || V_COUNT || '条。');
  END IF;
END;

4.游标

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

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

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

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

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

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

游标的定义：
--CURSOR <游标名> IS <SQL语句>;
--例：
CURSOR c_emp IS SELECT * FROM emp WHERE dno=3; 

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

--使用游标查询emp表中所有员工的姓名和工资，并将其依次打印出来。：
DECLARE
  CURSOR c_emp IS 	--声明游标
  SELECT ename, sal FROM emp;	 --声明变量用来接受游标中的元素
  v_ename emp.ename%TYPE;
  v_sal emp.sal%TYPE;
  BEGIN
  OPEN 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;

--使用游标查询并打印某部门的员工的姓名和薪资，部门编号为运行时手动输入。
DECLARE
  CURSOR c_emp(v_empno emp.empno%TYPE) IS
    SELECT ename, sal FROM emp WHERE empno = v_empno;
  v_ename emp.ename%TYPE;
  v_sal emp.sal%TYPE;
  BEGIN
  OPEN c_emp(10);	 --打开游标
  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;

带锁游标： 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.5501 Oracle 函数_月应是绝色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;
BEGIN
	SELECT 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) IS
	BEGIN
  	SELECT sal INTO o_sal FROM emp WHERE empno = i_empno;
	END;

--调用存储过程
DECLARE
  	v_sal emp.sal%TYPE;	 --声明一个变量接受存储过程的输出参数
BEGIN
  	P_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_sal
AFTER UPDATE OF sal ON  emp 
FOR EACH ROW
BEGIN
  INSERT INTO e_audit VALUES( :OLD.empno, SYSDATE,:NEW.sal,:OLD.sal); 
  -- 在行触发器中，被修改记录的每列都有两个相关名称，对应旧值和新值。:OLD.<属性名>和:NEW<属性名>
END;

--操作相关表，使得某部门职工的薪水增加15%。
--给指定部门的员工加薪，首先要把该部门的员工选出来，再对这些员工的薪水进行改动。
--这里用存储过程表示，将要加薪的部门作为参数：
CREATE OR REPLACE PROCEDURE add_sal (deptname varchar2)
AS   
BEGIN
        UPDATE emp
        SET 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 ;