PL/SQL

最新推荐文章于 2022-09-05 10:38:51 发布

明夕何夕-

最新推荐文章于 2022-09-05 10:38:51 发布

阅读量573

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/qq_42150316/article/details/91367336

版权

数据库专栏收录该内容

7 篇文章 0 订阅

订阅专栏

PL/SQL

1.1PL/SQL简介

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

简单来说就是对于sql语言的过程化扩展。使得sql具有过程处理的能力。（减少数据库与代码的交互，提高执行效率）

在这里插入图片描述

① 声明部分：声明部分包含了变量和常量的定义。这个部分由关键字DECLARE开始，如果不声明变量或者常量，可以省略这部分。

② 执行部分：执行部分是 PL/SQL块的指令部分，由关键字BEGIN开始，关键字END结尾。所有的可执行PL/SQL语句都放在这一部分，该部分执行命令并操作变量。其他的PL/SQL块可以作为子块嵌套在该部分。PL/SQL块的执行部分是必选的。注意END关键字后面用分号结尾。

③ 异常处理部分：该部分是可选的，该部分用EXCEPTION关键字把可执行部分分成两个小部分，之前的程序是正常运行的程序，一旦出现异常就跳转到异常部分执行。


declare 
  -- Local variables here（声明变量，游标的地方，没有变量游标的话，是可以省略的）
  i integer;
begin
  -- Test statements here
  -- 执行分析业务逻辑
  -- 异常处理
  
end;

类型	符号	说明
赋值运算符	:=	Java和C#中都是等号，PL/SQL的赋值是：=
特殊字符	\|\|	字符串连接操作符。
	–	PL/SQL中的单行注释。
	/,/	PL/SQL中的多行注释，多行注释不能嵌套。
	<<,>>	标签分隔符。只为了标识程序特殊位置。
	…	范围操作符，比如：1…5 标识从１到５
算术运算符	+，-，*，/	基本算术运算符。
	**	求幂操作，比如：3**2=9
关系运算符	>，<,>=,<=,=	基本关系运算符，=表示相等关系，不是赋值。
	<>,!=	不等关系。
逻辑运算符	AND,OR,NOT	逻辑运算符。

1.2最简单的输出hello world

最简单的输出hello world

begin
  -- dbms_output相当于java中的system.out,put相当于print，put_line相当于println
  dbms_output.put_line('hello world');
end;

在sqlplus中执行输出语句，需要先运行

 set serveroutput on;

保证输出功能被打开

之后就是输入

begin
  -- dbms_output相当于java中的system.out,put相当于print，put_line相当于println
  dbms_output.put_line('hello world');
end;
/

在plsql中的命令行窗口中也需要加 /

1.3变量

普通数据类型（char，varchar2,date,number,boolean,long）
特殊变量类型（引用型变量，记录型变量）
声明变量的方式

–1.定义变量时，建议用v_作为前缀:v_ename
–2.定义常量时，建议用c_作为前缀:c_rate
–3.定义异常时，建议用e_作为前缀:e_error

变量名 变量类型（变量长度） 例如：  v_name varchar2(20) ;

变量赋值方式

1.直接赋值 :=

例子： v_name :=‘zhangsan’ ；

2.语句赋值 select 值 into 变量

例子： select ‘zhangsan’ into v_name

-- 打印人员个人信息，包括姓名，薪水，地址
declare
  v_name varchar2(20) := '张三'; --直接赋值
  v_scl  number(20);
  v_addr varchar2(200);

begin
  --直接赋值
  v_scl := 1580;
  --语句赋值 select 值 into 变量
  select '标准地址' into v_addr from dual;
  --打印
  dbms_output.put_line('姓名:' || v_name || ' 薪水:' || v_scl || ' 地址:' || v_addr);
end;

① 变量初始化时，可以使用DEFAULT关键字对变量进行初始化。

② 使用select…into语句对变量赋值，要求查询的结果必须是一行，不能是多行或者没有记录。

常量在声明时赋予初值，并且在运行时不允许重新赋值。使用CONSTANT关键字声明常量。

-- 常量
declare 
PI constant number :=3.14;
  i integer;
begin
 /* PI :=10;*/
dbms_output.put_line(PI);

end;

类型	说明
VARCHAR2(长度)	可变长度字符串，Oracle SQL定义的数据类型，在PL/SQL中使用时最常32767字节。在PL/SQL中使用没有默认长度，因此必须指定。
NUMBER(精度，小数)	Oracle SQL定义的数据类型，
DATE	Oracle SQL定义的日期类型，
TIMESTAMP	Oracle SQL定义的日期类型，
CHAR(长度)	Oracle SQL定义的日期类型，固定长度字符，最长32767字节，默认长度是1，如果内容不够用空格代替。
LONG	Oracle SQL定义的数据类型，变长字符串基本类型，最长32760字节。在Oracle SQL中最长2147483647字节。
BOOLEAN	PL/SQL附加的数据类型，逻辑值为TRUE、FALSE、NULL
BINARY_INTEGER	PL/SQL附加的数据类型，介于-231和231之间的整数。
PLS_INTEGER	PL/SQL附加的数据类型，介于-231和231之间的整数。类似于BINARY_INTEGER，只是PLS_INTEGER值上的运行速度更快。
NATURAL	PL/SQL附加的数据类型，BINARY_INTEGER子类型，表示从0开始的自然数。
NATURALN	与NATURAL一样，只是要求NATURALN类型变量值不能为NULL。
POSITIVE	PL/SQL附加的数据类型，BINARY_INTEGER子类型，正整数。
POSITIVEN	与POSITIVE一样，只是要求POSITIVE的变量值不能为NULL。
REAL	Oracle SQL定义的数据类型，18位精度的浮点数
INT,INTEGER,SMALLINT	Oracle SQL定义的数据类型，NUMBERDE的子类型，38位精度整数。
SIGNTYPE	PL/SQL附加的数据类型，BINARY_INTEGER子类型。值有：1、-1、0。
STRING	与VARCHAR2相同。

PL/SQL中标量数据类型。

引用型变量

变量的类型和长度取决于表中字段的类型和长度。

通过表名.列名%TYPE 指定变量的类型和长度。

使用这个引用型变量的原因是保证与原表中的类型相同，防止由于类型原因导致报错，比如varchar2（20）与varchar2（1），这种情况就可能会报错，因为存不下数据，并且之后可能会对于原表改字段类型，导致出现问题（这种情况很少，毕竟是企业级开发，对于数据库定义更改情况真的少，还是建议使用这种方法）。

例子：
```
v_name   yanxy_test1.a_name%TYPE  := '张三';
```
```
-- 打印 yanxy_test1表中的a_no=1数据的信息
declare
  v_name   yanxy_test1.a_name%TYPE ;
  v_date   yanxy_test1.a_date%TYPE ;

begin
  --查询
  select a_name,a_date into v_name,v_date from  yanxy_test1 where a_no=1;
  --打印
  dbms_output.put_line('姓名:' || v_name || ' 时间'|| v_date);
end;
```
这个例子中，最重要的是查询语句

需要把查询字段赋值给对应的declare中的字段，并且要一一对应。

下面是简单的建立了一个表，对应增加数据有三种方法。
```
 create table yanxy_test1(
   a_no number,
   a_name varchar2(20),
   a_date date);
```
1.insert 插入数据

2.既然在plsql中，就可以使用rowid

3.使用for update
记录型变量

接收表中的一条记录

语法：

变量名称表名%ROWTYPE

虽然看起来使用简单，不需要建立那么多的变量，但*会增加数据库运行的效率，很不建议使用。

-- 打印 yanxy_test1表中的a_no=1数据的信息
declare
  v_person  yanxy_test1%ROWTYPE;

begin
  --查询
  select * into v_person from  yanxy_test1 where a_no=1;
  --打印
  dbms_output.put_line('姓名:' || v_person.a_name || ' 时间'|| v_person.a_date);
end;

使用时会产生的错误：

1.以一个数据接收一条数据

select a_no into v_person from  yanxy_test1 where a_no=1;

2.以一条数据的量接收多行数据

select * into v_person from  yanxy_test1 where a_no in(1,2);

1.4条件判断

1.4.1 if

java中if语法	PL/SQL中IF语法
if (条件){ //条件结构体 }	IF 条件 THEN --条件结构体 END IF;

**案例1：**查询1号员工的工资，如果大600元，则发奖金8%。

declare 
 salary1 emp_test.salary%type;
begin
select a.salary into salary1 from s_emp a where a.id=1; 
if salary1>600 then 
   update emp_test 
   set commission_pct=8
   where id=1;
  end if;
  commit;
end;

代码演示：IF-THEN应用

-- Created on 2019/5/29 by 墨 
declare 
 salary1 s_emp.salary%type;
begin
select a.salary into salary1 from s_emp a where a.id=1; 
if salary1>900 then 
  update 
  set
  end if;
      dbms_output.put_line(salary1);
end;

代码解析：

① 先判断条件，如果条件为TRUE，则执行条件结构体内部的内容。

② 在PL/SQL块中可以使用事务控制语句，该COMMIT同时也能把PL/SQL块外没有提交的数据一并提交，使用时需要注意。

java中if语法	PL/SQL中IF语法
if (条件){ //条件成立结构体 } else{ //条件不成立结构体 }	IF 条件 THEN --条件成立结构体 ELSE --条件不成立结构体 END IF;

对于值进行判断，并将不同的情况进行不同的分析。

注意：在条件判断中有 elsif ，这与java中有一定的不同，对于这个来说，要注意。

实际操作时候不要忘记if then end if;

-- 打印s_emp表中的记录是否超过某个值
declare
  v_count number;

begin
  --查询
  select count(1) into v_count from s_emp;
  if v_count > 26 then
    dbms_output.put_line('数==量' || v_count);
  elsif v_count >= 10 then
    dbms_output.put_line('数..量' || v_count);
  else
    dbms_output.put_line('数//量' || v_count);
  
  end if;

end;

1.4.2 case

case有两种使用方法

1.根据情况输出

declare
  v_gender char(3) := upper('&gender');

begin
  case v_gender
    when 'A' then
      dbms_output.put_line('this is A good');
    
    when 'B' then
      dbms_output.put_line('this is B good');
    else
      dbms_output.put_line('this is C good');
  end case;
end;

2.根据情况赋值

DECLARE
  v_grade CHAR(1) := UPPER('&grade');
  p_grade VARCHAR(20);
BEGIN
  p_grade := 
             CASE v_grade
               WHEN 'A' THEN
                'Excellent'
               WHEN 'B' THEN              
                'Very Good'             
               WHEN 'C' THEN               
                'Good'             
               ELSE               
                'No such grade'      
             END;
  dbms_output.put_line('Grade:' || v_grade || ',the result is ' || p_grade);
END;

当然也可以换种方法赋值

这种赋值方式只是将case 后面的值换到后面

DECLARE
  v_grade CHAR(1) := UPPER('&grade');
  p_grade VARCHAR(20);
BEGIN
  p_grade := CASE          
               WHEN v_grade = 'A' THEN            
                'Excellent'         
               WHEN v_grade = 'B' THEN              
                'Very Good'            
               WHEN v_grade = 'C' THEN               
                'Good'            
               ELSE              
                'No such grade'            
             END;
  dbms_output.put_line('Grade:' || v_grade || ',the result is ' || p_grade);
END;

1.5循环

oracle有三种循环方式，打印99乘法表就能看出一定的区别.所有的循环不要忘记加结束end loop；

1.5.1 loop循环

–LOOP循环条件成立时退出
LOOP
EXIT [when 条件];
语句序列
END LOOP;

在判断条件处，可以以if做判断，exit跳出循环loop，或者以exit when 跳出循环

--1-100求和

declare
  i   number(10) := 0;
  sums number(10) := 0;

begin
  loop
    i:= i + 1;
    sums := sums + i;
    if i >= 100 then
      exit;
    end if;
    --exit when i>=100;
  end loop;
  dbms_output.put_line('sum'||to_char(sums));
end;

--打印数字1-10
declare
  v_id number := 1;
begin
  loop
    exit when v_id > 10; --退出循环条件
    dbms_output.put_line(v_id);
    v_id := v_id + 1;
  end loop;
end;

-- 打印99乘法表
declare
  i number := 1;
  j number := 1;
  m number := 1;
begin
  loop
    loop
      m := i * j;
      dbms_output.put(j || '*' || i || '=' || m || ' ');
      j := j + 1;
      dbms_output.put('');
      exit when j > i;
    end loop;
    dbms_output.new_line;
    i := i + 1;
    j := 1;
    exit when i > 9;
  end loop;
end;

1.5.2WHILE循环

–WHILE 条件成立时循环
WHILE total<=25000 LOOP
语句序列
total:=tatal+salary;
END LOOP;

-- 99乘法表
declare
  i number := 1;
  j number ;
  m number ;
begin
  while i <= 9 loop
    j:=1;
    while j <= i loop
      m := i * j;
      dbms_output.put(j || '*' || i || '=' || m || ' ');
            j := j + 1;
    end loop;
    dbms_output.new_line;--换行
    i := i + 1;
  end loop;
end;

1.5.3FOR循环

–FOR循环 1…3表示区间
FOR I IN 1…3 LOOP
语句序列;

END LOOP;

--for循环99乘法表
declare
  i number;
  j number;
  m number;
begin
  for i in 1 .. 9 loop
    for j in 1 .. i loop
      m := i * j;
      dbms_output.put(j || '*' || i || '=' || m || ' ');
    end loop;
    dbms_output.new_line;--换行
  end loop;
end;

使用REVERSE递减

DECLARE
v_i NUMBER;
BEGIN
  FOR v_i IN REVERSE 1..18 LOOP
    dbms_output.put_line(v_i);
  END LOOP;
END;

1.6顺序结构

在程序顺序结构中有两个特殊的语句。GOTO和NULL

GOTO语句将无条件的跳转到标签指定的语句去执行。标签是用双尖括号括起来的标示符，在PL/SQL块中必须具有唯一的名称，标签后必须紧跟可执行语句或者PL/SQL块。GOTO不能跳转到IF语句、CASE语句、LOOP语句、或者子块中。

NULL语句什么都不做，只是将控制权转到下一行语句。NULL语句是可执行语句。NULL语句在IF或者其他语句语法要求至少需要一条可执行语句，但又不需要具体操作的地方。比如GOTO的目标地方不需要执行任何语句时。

DECLARE
  sumsal emp_test.salary%TYPE;
BEGIN
  SELECT SUM(salary) INTO sumsal FROM EMP_test;
  IF sumsal > 20000 THEN
    GOTO first_label;
  ELSE
    GOTO second_label;
  END IF;
  <<first_label>>
  dbms_output.put_line('ABOVE 20000:' || sumsal);
  <<second_label>>
  NULL;
END;

1.7动态sql

declare
  sql_stmt    VARCHAR2(200);
  v_id        dept_test.id%type := 10;
  v_name      dept_test.name%type := 'ASDWE';
  v_region_id dept_test.region_id%type := 2;
  emp_rec     dept_test%ROWTYPE;
begin
  --创建表写法,动态执行一个完整的SQL语句。
  EXECUTE IMMEDIATE 'CREATE TABLE bonus1 (id NUMBER, amt NUMBER)';
  --插入sql语句写法,SQL语句中存在3个参数分别标识为：[:1、:2、:3]，因此需要用USING关键字对三个参数分别赋值。
  sql_stmt := 'INSERT INTO dept_test VALUES (:1, :2, :3)';
  EXECUTE IMMEDIATE sql_stmt
    using v_id, v_name, v_region_id;
  --查询表写法,对动态查询语句可以使用INTO子句把查询的结果保存到一个变量中，要求该结果只能是单行。
  sql_stmt := 'SELECT * FROM dept_test WHERE id = :id';
  EXECUTE IMMEDIATE sql_stmt
    INTO emp_rec
    USING v_id;
  --在Oracle的insert，update，delete语句都可以使用RETURNING子句把操作影响的行中的数据返回，对SQL语句中存在RETURNING子句时，在动态执行时可以使用RETURNING INTO来接收。
  sql_stmt := 'UPDATE emp_test SET salary = 2000 WHERE id = :1
      RETURNING salary INTO :2';
  EXECUTE IMMEDIATE sql_stmt
    USING emp_id
    RETURNING INTO salary;
  --动态执行参数中可以是：[:数字]也可以是[:字符串]。
  EXECUTE IMMEDIATE 'DELETE FROM dept_test WHERE id = :num'
    USING dept_id;
end;

2.1 游标

2.1.1什么是游标

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

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

语法：

声明游标

cursor 游标名[()] is 查询语句；

打开游标：

open 游标名；

读取游标：

fetch 游标名 into 变量列表；

关闭游标；

close 游标名；

–显式游标属性:
%FOUND 找到是否找到数据，有数据TRUE，没有则FALSE
%ISOPEN 判断游标是否打开，打开则返回TRUE，没有打开则返回FALSE
%NOTFOUND 返回FETCH …INTO…是否有数据如果没有返回TRUN，有则为FALSE
%ROWCOUNT 返回执行FETCH 语句所返回的行数，初始为0,每执行一行则%ROWCOUNT增加1

2.2显式游标

--取emp_test表中的两列数据，并循环输出
DECLARE
  CURSOR emp_cur IS
    SELECT id, last_name FROM emp_test; --定义游标
  v_id   emp_test.id%TYPE; --定义变量ID
  v_name emp_test.last_name%TYPE;
BEGIN
  OPEN emp_cur; --打开游标

  LOOP
    FETCH emp_cur
      INTO v_id, v_name; ---提示取游标
    EXIT WHEN emp_cur%notFOUND; --判断是否还有数据
    dbms_output.put_line('员工编号' || v_id || ',员工姓名:' || v_name);
  END LOOP;
  CLOSE emp_cur; --关闭游标
END;

--ROWCOUNT实例
DECLARE
  V_NAME  VARCHAR2(50); --定义变量姓名
  V_DNAME VARCHAR2(50); --定义变量部门名称
  CURSOR CUR_E IS --定义游标
    SELECT ENAME, DNAME FROM EMP, DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO;
BEGIN
  OPEN CUR_E; --打开游标
  LOOP
    --使用循环来读取游标
    FETCH CUR_E
      INTO V_NAME, V_DNAME; --提取游标
    EXIT WHEN CUR_E%NOTFOUND; --判断游标是否还有内容
    DBMS_OUTPUT.PUT_LINE(CUR_E%ROWCOUNT || '  员工姓名:' || V_NAME || ' 部门名称:' ||
                         V_DNAME);                            --输出内容
  
  END LOOP;
  CLOSE CUR_E; --关闭游标
END;

DECLARE
  V_EMPNAME emp_test.last_name%TYPE;
  V_DNAME   DEPT_TEST.NAME%TYPE;
  CURSOR EMP_CUR IS
    SELECT E.last_NAME, D.NAME FROM emp_test E, DEPT_TEST D WHERE E.DEPT_ID = D.ID;
BEGIN
  OPEN EMP_CUR;
  LOOP
    FETCH EMP_CUR
      INTO V_EMPNAME, V_DNAME;
    EXIT WHEN EMP_CUR%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('员工姓名 :' || V_EMPNAME || '，部门名称 :' || V_DNAME);
  END LOOP;
  CLOSE EMP_CUR;
END;

--
DECLARE
  CURSOR CUR_EMP IS
    SELECT * FROM EMP_test;
  V_EMPROW Emp_Test%ROWTYPE;
BEGIN
  IF CUR_EMP%ISOPEN THEN
    NULL;--按照保证不重复开启的原则，可以判断一下
  ELSE
    OPEN CUR_EMP;
  END IF;
  FETCH CUR_EMP
    INTO V_EMPROW;
  WHILE CUR_EMP%FOUND LOOP
    DBMS_OUTPUT.PUT_LINE('员工姓名: ' || V_EMPROW.LAST_NAME || '，职位: ' ||
                         V_EMPROW.TITLE || ' ,工资' || V_EMPROW.SALARY);
    FETCH CUR_EMP
      INTO V_EMPROW;
  END LOOP;
  CLOSE CUR_EMP;
END;

--使用for循环,使用FOR循环操作游标不仅代码简单，而且可以将游标的状态交给系统去完成，尽量使用FOR循环为主
DECLARE 
CURSOR cur_emp IS SELECT * FROM emp_test;
BEGIN
  FOR emp_row1 IN cur_emp LOOP
    DBMS_OUTPUT.PUT_LINE('员工姓名: ' || emp_row1.last_name || '，职位: ' ||
                         emp_row1.manager_id || ' ,工资' || emp_row1.salary);
     END LOOP;
END;

2.3REF动态游标

TYPE 类型名 IS REF CURSOR [RETURN]数据类型
游标名类型名
OPEN 游标名 FOR 查询语句
–强类型:带RETURN

DECLARE
  TYPE REF_EMP IS REF CURSOR RETURN EMP%ROWTYPE; --定义一个REF动态游标，并返回类型
  CUR_EMP REF_EMP; --定义一个变量类型是上面的REF动态游标也称游标变量
  V_EMP   EMP%ROWTYPE; --定义一个变量，类型和REF游标返回类型相同，行类型
BEGIN
  OPEN CUR_EMP FOR
    SELECT * FROM EMP; --打开游标，并关联查询语句
  LOOP
    FETCH CUR_EMP
      INTO V_EMP; --提取游标数据
    EXIT WHEN CUR_EMP%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(CUR_EMP%ROWCOUNT||'  员工编号:' || V_EMP.EMPNO || ' 员工姓名:' || V_EMP.ENAME);
  END LOOP;
  CLOSE CUR_EMP;
END;

–弱类型:不带RETURN

DECLARE
  TYPE REF_EMP IS REF CURSOR; --定义一个REF动态游标，并返回类型
  CUR_EMP REF_EMP; --定义一个变量类型是上面的REF动态游标也称游标变量
  V_EMP   EMP%ROWTYPE; --定义一个变量，类型和REF游标返回类型相同，行类型
  V_DEPT  DEPT%ROWTYPE; --定义一个变量，类型和REF游标返回类型相同，行类型
BEGIN
  --员工表
  OPEN CUR_EMP FOR
    SELECT * FROM EMP; --打开游标，并关联查询语句
  LOOP
    FETCH CUR_EMP
      INTO V_EMP; --提取游标数据
    EXIT WHEN CUR_EMP%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(CUR_EMP%ROWCOUNT || '  员工编号:' || V_EMP.EMPNO ||
                         ' 员工姓名:' || V_EMP.ENAME);
  END LOOP;
  CLOSE CUR_EMP;
  ------------下面是部门表  
  OPEN CUR_EMP FOR
    SELECT * FROM DEPT; --打开游标，并关联查询语句
  LOOP
    FETCH CUR_EMP
      INTO V_DEPT; --提取游标数据
    EXIT WHEN CUR_EMP%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(CUR_EMP%ROWCOUNT || '  部门编号:' || V_DEPT.DEPTNO ||
                         ' 部门名称:' || V_DEPT.DNAME);
  END LOOP;
  CLOSE CUR_EMP;
END;

在Oracle9i之后为了方便用户使用弱类型游标变量，可以使用  
SYS_REFCURSOR 来替代  TYPE REF_EMP IS REF CURSOR
上面的声明可以换为:
  CUR_EMP  SYS_REFCURSOR; --定义一个变量类型是上面的REF动态游标也称游标变量
  V_EMP   EMP%ROWTYPE; --定义一个变量，类型和REF游标返回类型相同，行类型
  V_DEPT  DEPT%ROWTYPE; --定义一个变量，类型和REF游标返回类型相同，行类型