PL/SQL编程

最新推荐文章于 2020-12-23 17:32:42 发布

＇简单

最新推荐文章于 2020-12-23 17:32:42 发布

阅读量161

点赞数

分类专栏： Oracle数据库

本文链接：https://blog.csdn.net/li571327732/article/details/100135060

版权

Oracle数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

什么是 PL/SQL
PL/SQL是结合了Oracle过程语言和结构化查询语言的一种扩展语言.

PL/SQL具有编程语言的特点,它能把一组SQL语句放到一个模块中.使其更具模块化程序的特点.
PL/SQL可以采用过程性语言控制程序的结构.也就是说,我们可以在PL/SQL中增加逻辑结构,如判断循环等程序结构.
同其他的编程语言一样,PL/SQL可以堆成秀中的错误进行自动处理,使程序能够在遇到错误时不会中断.即它的异常处理机制.
PL/SQL程序块具有更好的可移植性,可以移植到另一个Oracle数据库中.
PL/SQL程序减少了网络的交互,有助于提高程序性能.

PL/SQL块的结构:

[DECLARE] 
 --声明部分:在此声明PL/SQL用到的变量 类型及游标,以及局部的存储过程和函数
BEGIN
 --执行部分:过程及SQL语句,即程序的主要部分.
[EXCEPTION]
 --异常处理部分:错误处理
 END;
 其中执行部分不能省略

常量和变量的声明
在PL/SQL块的可执行部分引用变量和常量前,必须先对其进行声明.变量和常量在PL/SQL块的部分声明,在PL/SQL块的可执行部分被使用.
声明变量语法:

v_ename varchar2(20);             //只声明
v_sal number(7,2)  :=6000.00;    //声明并赋值

声明常量语法:

c_company_name CONSTANT  varchar2(20) :='北京市';

PL/SQL块代码:

--声明部分
 DECLARE
  comm        number(10); --声明变量
  v_sal_temp  number(10); --临时薪水
  v_sal_temp1 number(10) := 100;
  v_sal_temp2 number(10) := 500;
  v_sal_temp3 number(10) := 1000;
  v_name_temp varchar2(10) :='SMITH'; --临时名字
  --执行部分
BEGIN
  SELECT sal INTO v_sal_temp FROM employee WHERE ename = v_name_temp;
   IF v_sal_temp < 5000 THEN
    UPDATE employee
       SET comm =v_sal_temp3
     WHERE ename = v_name_temp;
  ELSIF v_sal_temp < 6000 then
    UPDATE employee
       SET comm =v_sal_temp2
     WHERE ename = v_name_temp;
  ELSIF v_sal_temp < 8000 then
    UPDATE employee
       SET comm = v_sal_temp1
    WHERE ename = v_name_temp;
  ELSE
    UPDATE employee SET comm =50 WHERE ename = v_name_temp;
  END IF;
  COMMIT;
  --异常部分
EXCEPTIO
  WHEN others THEN
    DBMS_OUTPUT.PUT_LINE('发生异常');
END;

在这里插入图片描述

PL/SQL数据类型

标量数据类型
标量数据类型包含单个值,没有内部组件.标量数据包括 数字字符布尔值和日期时间值四类
Oracle使用的变量类型 :

char
varchar2
binary_integer
number(p,s)
long date
boolean

LOB类型
Oracle 提供了LOB类型,用于存储打的数据对象的类型.Oracle目前主要支持:

BFILE(二进制文件)
BLOB(二进制文件)
CLOB(文本文件)
NCLOB(文本文件)

属性类型
属性类型用于引用变量或数据库列的数据类型,以及表示表中一行的记录类型,PL/SQL支持以下两种属性类型:

%Type
定义一个变量,其数据类型与已经定义的某个数据变量 (尤其是表的某一列) 的数据类型相一致.这时可以使用 %Type
使用 %Type的属性优点在于:
(1)可以不必知道所引用的数据库列的数据类型.
(2)所引用的数据列的数据类型可以实时改变,容易保持一致,不用修改PL/SQL程序.
%RowType
返回一个记录类型,其数据类型个数据库表的数据结构相一致,这时可以使用**%RowType**
使用 %RowType的属性优点在于:
(1)可以不必知道所引用我的数据库中列的个数和数据类型.
(2)所引用的数据库中列的个数和数据类型可以实时改变,容易保持一致,不用修改PL/SQL程序.
代码如下:

DECLARE
  v_empno employee.empno %TYPE := 7369;
  v_rec   employee %ROWTYPE; //将employee 表的类型交给临时变量 v_rec 
BEGIN
  SELECT  *  INTO   v_rec   FROM  employee  WHERE   empno = v_empno;  //查询的数据交给  临时表类型(它会自动按照类型赋值)
  DBMS_OUTPUT.PUT_LINE('姓名为:' || v_rec.ename || '工资为:' || v_rec.sal);  //赋值完成后 输出它的值信息
END;

PL/SQL控制语句

条件控制
(1) if 语句语法如下:

IF <布尔表达式> THEN
    PL/SQL和SQL语句
   ELSIF <其他布尔表达式> THEN
    其他语句
   ELSIF <其他布尔表达式> THEN
    其他语句
  ELSE
    其他语句
 END IF;
 //需注意 这里的 ELSIF 不要习惯性的写成  else if

(2) case 语句语法如下:

CASE 条件表达式
     WHEN 条件表达式结果1  THEN
           语句段1
     WHEN 条件表达式结果2 THEN
            语句段2
     ......
     WHEN 条件表达式结果n THEN
           语句段n
    [ELSE  语句段]
 END CASE;
 //还有一种语法   剩下的位置都一样 就简写一下  如下:
CASE
WHEN 条件表达式1  THEN
语句段1

循环控制
(1)LOOP循环语法如下:(相当于 java中的 do while循环)

    LOOP                                       
             要执行的语句;
             EXIT  WHEN <条件语句>  --条件满足,退出循环
    END LOOP;

例如:

--使用loop 打印数字 1-100
Declare
v_i Number(5):=1;  //声明变量
Begin
Loop
dbms_output.put_line(v_i);   //打印变量值
Exit When v_i>=100;     //条件判断
v_i:=v_i+1;                     //迭代
End Loop;
End;

(2)WHILE循环语法如下:(相当于 java中的 while循环)

WHILE <布尔表达式> LOOP
          要执行的语句;
END LOOP;

例如 :

 --使用while 打印数字 1-100
Declare
v_i Number(5):=1;
Begin
While v_i<=100 Loop
dbms_output.put_line(v_i);
v_i:=v_i+1;
End Loop;
End;

(3)FOR循环语法如下:(相当于 java中的 for循环)

FOR  循环计数器  IN  [reverse] 下限  **. .** 上限  LOOP      //reverse就代表反着来 (i- -的效果)
         要执行的语句;
END LOOP;

例如:

--使用for循环 打印数字 1-100
Begin
For i In  1..100 Loop
dbms_output.put_line(i);
End Loop;
End;

goto 语句
代码如下:

Begin
For i In  1..100 Loop
If  i=5 Then Goto a;    //当i等于5 时  就跳转到 a 标签那边
End If;
dbms_output.put_line(i);
End Loop;
<<a>>                           //a标签位置
dbms_output.put_line('打印结束'); //如果只想跳出本次循环是   在此行直接写 NULL; 即可
End;

异常处理

(1)自定义异常

DECLARE
  temp_ex  exception;   //声明异常
BEGIN
  RAISE  temp_ex;     //抛出异常
EXCEPTION                 
 WHEN   temp_ex    THEN    //发生该异常
  DBMS_OUTPUT.PUT_LINE('发生异常');   //输出该异常的信息
  END;

(2)系统异常
省略 ,不做介绍 .与自定义相比 ,少了声明异常和手动抛异常. 直接在exception 异常部分做判断,其他异常用 others .

游标

游标,是指查询和处理多条记录的工具,指向查询结果内存的指针.
游标类型: 静态游标和动态游标
静态游标: 显式游标和隐式游标. ( 这里我们就记录下显式游标)

1.显式游标
带参数的:

--带参数的游标
Declare
--声明游标    带参数动态的给赋值 (for 循环时才将值赋给 v_deptno )
Cursor cursor_emp (v_deptno employee.deptno%Type) Is Select *  From employee Where deptno=v_deptno;
Begin
For c In cursor_emp(v_deptno=>20) Loop
dbms_output.put_line('工号:'||c.empno||','||'薪水:'||c.sal);
End Loop;
End;

不带参数的:
显式游标代码如下
LOOP循环方式

DECLARE
  --声明游标
  CURSOR  cursor_emp  IS SELECT sal, comm   FROM  employee;
  v_sal  employee.sal%TYPE; //声明两个变量接收值
  v_comm employee.comm%TYPE;
BEGIN
  --打开游标
 OPEN cursor_emp;
  LOOP                                        //循环
    --提取游标
    FETCH cursor_emp INTO v_sal, v_comm;
   EXIT WHEN cursor_emp%NOTFOUND;         //游标没有结果时就退出
   DBMS_OUTPUT.PUT_LINE('薪水:' || v_sal || ',' || '福利:' || v_comm);
  END LOOP;
  --关闭游标
  CLOSE cursor_emp; 
END;

WHILE循环方式:

Declare
--声明游标
Cursor cursor_emp Is Select *  From employee Where deptno=20;
v_temp employee%Rowtype;
Begin
--打开游标
If Not cursor_emp%isopen Then
Open cursor_emp;
End If;
--提取游标(与loop循环不一样的地方就是 循环外面需要提取游标,循环内部也需提取游标,用来判断游标是否还有值)
Fetch  cursor_emp  Into v_temp;
While cursor_emp%found Loop
 DBMS_OUTPUT.PUT_LINE('薪水:' || v_temp.sal || ',' || '福利:' ||v_temp.comm);
 Fetch  cursor_emp  Into v_temp;
End Loop;
--关闭游标
Close cursor_emp;
End;

FOR循环方式(更简单,无需手动开关游标和提取游标) 推荐此方式处理游标

Declare
--声明游标 (此方式最简单,能自动的开关游标和提取游标)
Cursor cursor_emp Is Select *  From employee Where deptno=20;
Begin
 For t In cursor_emp Loop
 DBMS_OUTPUT.PUT_LINE('薪水:' || t.sal || ',' || '福利:' ||t.comm);
 End Loop;
End;

2.显示游标属性:
%FOUND:只有在 DML语句影响一行或多行时,%FOUND属性才会返回 true.
%NOTFOUND: 与 %FOUND 属性相反.如果 DML语句没有影响任何行, 则**%NOTFOUND属性返回 true.
%ROWCOUNT:返回 DML语句影响行数.如果 DML语句没有影响任何行.则%ROWCOUNT**返回0.
%ISOPEN:返回游标是否已打开.

3.使用显示游标删除或更新
使用游标时,如果处理过程中需要删除或更新行,在定义游标时必须使用 SELECT…FOR UPDATE语句,而在执行DELETE和UPDATE时使用 WHERE CURRENT OF子句指定游标的当前行.
语法:
一般使用场景为:多表

Declare
Cursor cursor_emp_dept_comm (v_dname dept.dname%Type)
Is Select e.deptno From employee e Inner Join dept d On e.deptno=d.deptno 
Where d.dname=v_dname  For Update Of  e.sal;     // For Update Of 锁定 employee表的 sal 列
Begin
For c In cursor_emp_dept_comm('ACCOUNTING')Loop
Update employee Set sal=sal-10000 Where Current Of cursor_emp_dept_comm;//Current Of更新表为锁定行所在的表
End  Loop;
End;

4.NO_DATA_FOUND 和 %NOTFOUND 的区别
(1)select … into 语句返回0条和多条纪录时触发NO_DATA_FOUND .
(2)当update或delete语句的 where子句未找到时,触发 %NOTFOUND .
(3)在提取循环中用 %NOTFOUND 或 %FOUND 来确定循环的退出条件,而不用NO_DATA_FOUND.

存储过程

**存储过程:**是执行某些操作的子程序,是执行特定任务的模块.从根本上讲,存储过程就是命名PL/SQL程序块,它可以被赋予参数并存储在数据库中,然后由一个应用程序或其他PL/SQL程序调用.
存储过程的基本用法:
(1).创建存储过程
(2).调用存储过程的访问权限

①用户命令在SQL提示符下调用.
②在PL/SQL块调用

(3).存储过程的参数模式
(4)存储过程的访问权限
(5)删除存储过程

1.创建存储过程

--存储过程(类似在 数据库定义一个JAVA的方法,完事调用)也可以做异常处理
Create Or replace Procedure s_employee(v_deptno  Number)  //带参数的(这里的参数只需给出类型 无需给大小)
Is
v_sum Number(10):=0;       //局部声明(类似Declare 可以看作为Declare )
--声明游标
Cursor cursor_sal Is Select sal From employee e Where e.deptno=v_deptno;
Begin
 For c In cursor_sal Loop
 v_sum:=v_sum+c.sal;
 End Loop;
 dbms_output.put_line('部门编号为'||v_deptno||' 的总工资为:'||v_sum);   //输出部门 的总薪水
End;

2.调用存储过程
如果是在命令窗口需加上EXEC 关键字

Begin
s_employee(20); //调用  就会输出部门 的总薪水
End;

3.存储过程的参数模式
参数传递的模式有三种: IN OUT和 IN OUT,即输入参数、输出参数和输入/输出参数.
IN模式: 只能将实参传递给形参,进入函数内部,但只能读不能输出,函数返回时实参的值不变.(默认就是IN模式 )
OUT 模式: 会忽略调用时的实参值(或者说形参的初始值总是NULL),但在函数内部可以被读或写,函数返回时形参的值会赋给实参.
IN OUT模式: 具有前两种模式的特性,即调用时,实参的值总是传递给形参.结束时,形参的值传递给实参.
4.存储过程的访问权限

--授予 A_oe 执行 add_employee存储过程的 权限
Grant Execute On  add_employee To A_oe;

--撤销 A_oe 执行 add_employee存储过程的 权限
Revoke Execute On add_employee From A_oe;

5.删除存储过程

Drop Procedure add_employee;   //删除存储过程

存储过程的调试和追踪
在 PL/SQL Developer工具中找到测试窗口,在里面写调用存储过程的语句.类似于 MyEclipse的调试,这里就不写了.

＇简单

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
PL/SQL编程

什么是 PL/SQLPL/SQL是结合了Oracle过程语言和结构化查询语言的一种扩展语言.PL/SQL具有编程语言的特点,它能把一组SQL语句放到一个模块中.使其更具模块化程序的特点.PL/SQL可以采用过程性语言控制程序的结构.也就是说,我们可以在PL/SQL中增加逻辑结构,如判断循环等程序结构.同其他的编程语言一样,PL/SQL可以堆成秀中的错误进行自动处理,使程序能够在遇到错...
复制链接

扫一扫