PL/SQL

PL/SQL编程基础

2019-05-21  本文已影响4人  Mr_J316

2019-05-11

PL(Procedural Language)/SQL概述

​ 在实际工作中，有些复杂的数据处理操作需要在客户端编程实现，但是客户端所处理的数据必须由数据库服务器通过网络传输而来，如果数据处理量非常大，采用客户端编程的方法就降低了数据处理速度。为了减少客户端与数据库服务器端之间的网络数据传输量，提高数据处理速度，可以直接在数据库服务器端编程，实现对数据的处理，处理完成后直接将处理结果返回客户端。为此Oracle提供了在数据库服务器端编程的语言，即PL/SQL。
​ SQL只是一种声明式语言，是非过程性的，语句之间相互独立。在实际工作中，许多事务处理应用都是过程性的，前后语句之间存在一定关联。PL/SQL是过程化SQL，可以弥补SQL语句的不足。可以通过IF和LOOP语句控制程序的执行流程；可以定义变量以便在语句之间传递数据信息。PL/SQL是对标准SQL语言的扩展， SQL语句完全可以嵌套在PL/SQL程序代码中，将SQL的数据处理能力和PL/SQL的过程处理能力结合在一起。

pl/sql的分类：

[ declare 定义语句段 ; ]    --声明部分，可选项，用于声明变量
begin
    执行语句段 ;              --执行部分，可包含流程控制和sql语句
[ exception 异常处理语句段 ; ]     --异常处理部分，可选项
end;

示例：根据用户输入的员工编号获得其工资和奖金

declare
    var_empno number;  --员工编号
    var_sal number;    --工资
    var_comm number;   --奖金
begin
    --接收用户输入的员工编号
    var_empno:=&var_empno;
    --获得工资
    select sal,nvl(comm,0) into var_sal,var_comm from scott.emp where empno=var_empno;
    dbms_output.put_line('员工编号是'||var_empno||'的工资是'||var_sal||'奖金是'||var_comm);
end;

示例：根据用户输入的员工编号进行涨工资10%，但是如果增长后的工资超过3200则不予增长

declare
    --员工编号
    var_empno number;
    --工资
    var_sal number;
begin
    --获得员工编号
    var_empno:=&var_empno;
    --根据编号获得其工资
    select sal into var_sal from scott.emp where empno=var_empno;
    --判断工资增长后是否超过3200
    if var_sal*1.1<=3200 then
        --更新工资
        update scott.emp set sal=sal*1.1 where empno=var_empno;
        commit;
    end if;
end;

变量与数据类型

变量名 数据类型 := 初值 ; 
变量名 数据类型  default  初值 ;

变量命名规范

标 识 符 命名规则 例 子
程序变量 v_name v_student_name
程序常量 c_name c_company_name
游标变量 cursor_name cursor_emp
异常标志 e_name e_too_many
表类型 name_table_type emp_record_type
表 name_table emp_table
记录类型 name_record emp_record
绑定变量 g_name g_year_sal

变量的标准数据类型：

 number：数字类型，可以代表整数和浮点数。

 int：整数型。

 pls_integer：整数型，计算速度快（直接由cpu执行），占用较少的存储空间。但产生溢出时将触发异常。

 binary_integer：带符号的整数型，不会出现溢出，但计算速度较慢（由oracle模拟执行）。

 char：定长字符，最长255个字符。

 varchar2：变长字符，最长2000个字符。

 long：变长字符，最长2gb。

 date：日期型。

 boolean：布尔型(true或false)。

流程控制语句

条件语句

if <表达式> then
    语句块;
end if;

if <表达式> then
    语句块1;
else
    语句块2;
end if;

if <表达式1> then
       语句块1;
elsif <表达式2> then
       语句块2;
else
       语句块3;
end if;

case语句

case <表达式>
    when <表达式1> then 语句块1;
    when <表达式2> then 语句块2;
      ……
    when <表达式n> then 语句块n;
   [ else 语句块 n+1 ]
end ;

循环语句

loop
  循环体; 
  exit when 循环退出条件
end loop;

while 循环条件
loop 
    循环体;
end loop;

for 循环控制变量 in [reverse] 循环下限 .. 循环上限 
loop
  循环体;
end loop

for循环示例

declare 
   sum_num number := 0;
begin
   for i in 1..100 loop
     sum_num := sum_num + i;
   end loop;
   dbms_output.put_line(sum_num);
end;

%TYPE变量

%TYPE用来定义与数据表中指定字段数据类型相同的变量。如果表中字段的数据类型或长度发生变化，%TYPE变量会自动随之变化。这样用户不必查看表中各个字段的数据类型，就可以确保所定义的变量能够正确存储字段数据。

变量名 方案名.表名.字段名%type

var_empno scott.emp.empno%type; --员工编号

自定义记录类型变量

自定义记录数据类型可以表示由多个字段值组成的一行数据。使用时首先要定义记录类型的结构，然后声明该类型的变量存储数据。

type 类型名称 is record (
     变量名称  数据类型 := 默认值 , 
     ……
     变量名称  数据类型 := 默认值 
) ;

示例1：输入职工编号，输出其姓名与工资

declare
    type employee_type is record (  --声明自定义记录类型
         ename  scott.emp.ename%type, 
         sal  scott.emp.sal%type
);
    var_emp employee_type ; --声明自定义记录类型变量
    var_empno scott.emp.empno%type;
begin
    var_empno:=&var_empno;
    select ename, sal into var_emp from scott.emp where  empno=var_empno ; 
     dbms_output.put (var_emp.ename) ;
     dbms_output.put_line(' '||var_emp.sal) ; 
end;

示例2：允许同一记录类型的两个变量整体赋值。

declare
    type employee_type is record ( 
         ename  scott.emp.ename%type, 
         sal  scott.emp.sal%type
);
    var_emp1 employee_type ;
    var_emp2 employee_type ; 
    var_empno scott.emp.empno%type;
begin
    var_empno:=&var_empno;
    select ename, sal into var_emp1 from scott.emp where empno=var_empno ;
    var_emp2:=var_emp1; 
    dbms_output.put (var_emp2.ename) ;
    dbms_output.put_line(' '||var_emp2.sal) ; 
end;

%ROWTYPE变量

%ROWTYPE类型可以根据数据表的行结构定义数据类型，用于存储从数据表中检索到的一行完整的数据。如果数据库表的结构发生变化，记录变量中的结构也将随之改变。

变量名 方案名.表名%rowtype

示例：输入职工编号，输出其详细信息

declare
    var_emp  scott.emp%rowtype ; --数据行变量
    var_empno scott.emp.empno%type;
begin
    var_empno:=&var_empno;
    select * into var_emp from scott.emp where  empno=var_empno ;
    dbms_output.put (var_emp.ename) ;
    dbms_output.put_line(' '||var_emp.job) ; 
    dbms_output.put_line(' '||var_emp.sal) ;
end;

异常处理

PL/SQL程序运行时出现的错误称为异常。可以分为两类：

 预定义异常： 当 PL/SQL 程序违反 Oracle 规则或超越系统限制时隐式引发

 用户自定义异常：用户可以声明自定义异常，自定义的异常通过 raise 语句手动引发

系统预定义异常

begin
     过程及sql语句;
exception
     when 异常名称then
          过程及sql语句;
     when others then
          过程及sql语句;
end;
命名的系统异常 产生原因
ACCESS_INTO_NULL 未定义对象 ，视图给一个没有初始化的对象赋值
CASE_NOT_FOUND CASE 中若未包含相应的 WHEN ，并且没有设置 ELSE 时
COLLECTION_IS_NULL 集合元素未初始化
CURSER_ALREADY_OPEN 游标已经打开
DUP_VAL_ON_INDEX 唯一索引对应的列上有重复的值
INVALID_CURSOR 在不合法的游标上进行操作
INVALID_NUMBER 内嵌的 SQL 语句不能将字符转换为数字 ，即将一个非有效的字符串转换成数字
NO_DATA_FOUND 使用 select into 未返回行，或应用索引表未初始化的元素时 ；查询语句无返回数据，或者引用了一个被删除的元素，或者引用了一个没有被初始化的元素
TOO_MANY_ROWS 执行 select into 时，结果集超过一行
ZERO_DIVIDE 除数为 0
SUBSCRIPT_BEYOND_COUNT 元素下标超过嵌套表或 VARRAY 的最大值
SUBSCRIPT_OUTSIDE_LIMIT 使用嵌套表或 VARRAY 时，将下标指定为负数
VALUE_ERROR 赋值时，变量长度不足以容纳实际数据
LOGIN_DENIED PL/SQL 应用程序连接到 oracle 数据库时，提供了不正确的用户名或密码
NOT_LOGGED_ON PL/SQL 应用程序在没有连接 oralce 数据库的情况下访问数据
PROGRAM_ERROR PL/SQL 内部问题，可能需要重装数据字典＆ pl./SQL 系统包
ROWTYPE_MISMATCH 宿主游标变量与 PL/SQL 游标变量的返回类型不兼容
SELF_IS_NULL 使用对象类型时，在 null 对象上调用对象方法
STORAGE_ERROR 运行 PL/SQL 时，超出内存空间
SYS_INVALID_ID 无效的 ROWID 字符串
TIMEOUT_ON_RESOURCE Oracle 在等待资源时超时

可以使用others子句捕获各种异常，同时结合两个函数向用户报告信息：

 sqlcode函数：返回出错码

 sqlerrm函数：返回出错信息

begin
  insert into scott.emp(empno , ename , job , sal , deptno)
  values('7999','ATG','CLERK',1500,'ABC');
exception
  when dup_val_on_index then
    dbms_output.put_line('捕获dup_val_on_index异常');
    dbms_output.put_line('该主键值已经存在');
  when others then  -- others子句必须放在各种异常处理的最后
    dbms_output.put_line( '错误号：' || sqlcode );
    dbms_output.put_line( '错误描述：' ||sqlerrm );
end;

用户自定义异常

程序开发人员可以根据具体的业务逻辑规则，自定义特定异常。当用户操作违反了业务逻辑规则后，可以手动引发该异常，从而中断程序的正常执行，转到自定义的异常处理代码。

自定义异常的处理步骤：

 定义异常处理：异常名 exception ;

 触发异常处理：raise 异常名

上一篇下一篇

猜你喜欢

热点阅读