PL/SQL基础语法

有时候我们需要对数据库中的数据进行一些稍微复杂的操作,而且这些操作都是一次性的,用完之后就不再用了。

用存储过程的话就太麻烦,而且浪费,用完了还要去删除。而单个SQL无法满足需求。这时候用一下SQL的语句块就可以了。

如果你用的是Oracle数据库,那么你就可以用PL/SQL(Procedure Language/SQL),即过程化查询语言。这是第三代语言。而我们用的SQL是结构化查询语言,属于第四代语言。

PL/SQL能够实现更加复杂的逻辑操作,像我们使用Java,C等高级语言一样。但如果是在MYSQL/SQLSERVER数据库中,那PL/SQL就无法使用(PL/SQL是属于Oracle的过程查询语言)。如果你要在MYSQL/SQLSERVER实现复杂的逻辑查询,那你只能通过编写存储过程实现。

下面对PL/SQL常用的一些基础知识进行讲解。在讲解之前,先创建一个用于测试的表:

--创建测试表
create table t_plsql_test
(
  id varchar(10),
  name varchar(100),
  mDate date
);
--插入测试数据
insert into t_plsql_test values(1, 'one', to_date('1992/03/08', 'YYYY/MM/DD'));
insert into t_plsql_test values(2, 'one', to_date('1993/03/08', 'YYYY/MM/DD'));
insert into t_plsql_test values(3, 'one', to_date('1994/03/08', 'YYYY/MM/DD'));
commit;
--查看
select * from t_plsql_test;

一、PL/SQL的结构

[declare] 
  --变量声明(可以省略)
  --如:my_var varchar(200);
begin
  --SQL语句
[exception]  --异常声明(可以省略)  
end;

例如:

declare 
  nowDate date:= sysdate;
BEGIN
    update t_plsql_test 
    set mdate = nowDate
    where id = 3;
    commit;
END;
--查看
select * from t_plsql_test;

二、PL/SQL的基本规则

1、标识符不区分大小写,所有的名称在存储时自动改成大写。

2、标识符只允许字母、数字、下划线,并且以字母开头。

3、不能使用保留字,与保留字同名必须使用双引号括起来。

4、END后需要使用分号结束。

5、字符类型和日期类型需要使用单引号括起来。

建议的写作规范:

1、命名应以“_”的连接方式,而不是用大小写混合的方式,如:p_id(表示名字为id,"p"表示它是一个参数)。

2、变量前最好加上前缀,以表示该变量的数据类型、作用范围等。

3、每个变量都应加上注释。

4、建议用3个半角空格替代TAB健进行缩进。

5、逗号后面以及操作符前后都应加空格。

三、PL/SQL的注释

-- 单行注释

/* 
 * 多行注释
 */

四、变量声明

语法结构如下:

variable_name datatype [:= expression ];

以上语法结构最常见的组合方式有两种:

第一种,直接声明变量,不赋值,如:

v_id number;

第二种,声明变量并赋值,如:

v_id number := 22;   

在给变量定义类型的时候,除了可以定义成数据库常用的类型(NUMBER, VCHAR, LONG, DATE, TIMESTAMP)之外,还可以直接将数据库中某个字段的类型作为变量的类型,如:

v_productid productinfo.productid%TYPE;  --如果需要赋值,也是在后面加上“:=”即可。

比如,上面的一个例子可以改写成这样:

declare 
  nowDate t_plsql_test.mdate%type := sysdate;  --使用t_plsql_test表的mdate字段的类型作为nowDate的类型
BEGIN
    update t_plsql_test 
    set mdate = nowDate
    where id = 1;
    commit;
END;

以上的变量声明是最常用的变量声明,当然还有其他更复杂的变量类型,但不常用,这里不做叙述。

五、IF条件控制语句

IF语句有三种使用方式:IF....、IF....ELSE....、IF...ELSEIF.... 三种方式。

1、IF结构结构

IF condition THEN 
  statments;
END IF;

2、IF...ELSE...结构

IF condition THEN
  statments;
ELSE
  statments;
END IF;

3、IF...ELSEIF...结构

IF condition THEN
  statements;
ELSEIF condition THEN
  statements;
[ELSE statements]
END IF;

范例:

declare 
  i number := 1;
BEGIN
    if i = 1 then
      update t_plsql_test 
      set name = '1' 
      where id = 1;
      commit;
    end if;
END;

六、CASE控制语句

1、简单CASE语句

语法格式:

CASE case_operand
WHEN value THEN ....
WHEN value THEN ....
.....
END CASE;

范例:

declare 
  i number := 3;
BEGIN
    case i
    when '1' then
      --SQL
    when '2' then
      --SQL
    when '3' then
     --SQL
    end case;
END;

运行可以发现,t_plsql_test表中id为3的记录的name字段已经改变。

2、搜索式Case语句

搜索式与简单case语句的一个不同是:搜索式when后面跟的是表达式,简单式后面跟的是值。

语法格式:

CASE
WHEN expression THEN statement;
WHEN expression THEN statement;
......
END CASE;

范例:

declare 
  i number := 150;
BEGIN
    case
    when i > 0 and i < 100 then
      --SQL
    when i >= 100 and i < 200  then
      --SQL
    when i >= 200 and i < 300 then
      --SQL
    end case;
END;

七、LOOP循环控制语句

LOOP语句有以下四种:

· LOOP

· WHILE...LOOP;

` FOR...LOOP;

` CURSOR FOR LOOP;

1、基本的LOOP

<<basic_loop>>
LOOP
  --SQL语句
  EXIT basic_loop WHEN ...;
END LOOP;

其中<<basic_loop>>是LOOP语句的标签。

如:

declare 
  i number := 3;
BEGIN
   <<myloop>>
   loop
      update t_plsql_test set name = i where id = 1;
      commit;
      i := i + 1;
      exit myloop when i >10;
   end loop;
END;

执行结果是:ID为1的记录的name字段值为10。

2、WHILE...LOOP语句

WHILE expression
LOOP
  statement...
END LOOP;

范例:

declare 
  i number := 3;
BEGIN
   while i < 5
   loop 
     update t_plsql_test set name = i where id = 1;
     commit;
     i := i + 1;
   end loop;
END;

执行结果是:ID为1的记录的name字段值为4。

3、FOR...LOOP语句

FOR index_name IN [REVERSE] lower_bound .. upper_bound
LOOP
statement...
END LOOP;

表示index_name从lower_bound增加到upper_bound,类似于for循环。

其中REVERSE表示循环方式从upper_bound降到lower_bound。

其中lower_bound和upper_bound要用".."连接。

从lower_bound增加到upper_bound:

declare 
  i number := 3;
BEGIN
   FOR inx IN 1..10 LOOP
       i := i + 1;  --加10次
   END LOOP;
   update t_plsql_test set name = i where id = 1;
   commit;
END;

结果是:13

从upper_bound减到lower_bound:

declare 
  i number := 3;
BEGIN
   FOR inx IN reverse 1..10 LOOP
       i := i + 1;  --加10次
   END LOOP;
   update t_plsql_test set name = i where id = 1;
   commit;
END;

结果是:13

此外,你也可以利用FOR...LOOP循环将从数据库查到的记录循环取出,例如:

DECLARE
v_id varchar2(12);
v_price number;
BEGIN
FOR rec IN ( select * from product)
LOOP
   v_id = rec.id;
   v_price = rec.price;
END LOOP;
END;

八、异常处理

定义当DML语句发生异常时,如何处理。例如:

DECLARE
   v_catgid VARCHAR2(10) := 0;
   v_bol BOOLEAN := TRUE;
BEGIN
   SELECT CATEGORYID INTO v_catgid
   FROM CATEGORYINFO
    
   EXCEPTION 
      WHEN NO_DATA_FOUND THEN
       ....
      WHEN CASE_NOT_FOUND  THEN
       ...
      WHEN OTHERS THEN
      ...
END;

Oracle中的异常可以分为三类:

①预定义异常;

②非预定义异常;

③自定义异常。

其中预定义异常是指Oracle已定义好的异常,我们可以直接调用,常用的预定义异常有:

至于非预定义异常和自定义异常这里不做介绍。一般情况下,我们可以在存储过程的异常处理模块中将出错的信息保存到特定的系统表中,这样我们就可以根据日志记录得知执行的错误。

如下面的一个异常处理模型将信息保存在了T_SYS_SQL_ERR表中:

--异常处理
        EXCEPTION  
            WHEN OTHERS THEN
              err_code := sqlcode;  --sqlcode是ORACLE中已定义变量,代表错误代码
              err_text := sqlerrm;   --sqlerrm代表错误信息
              INSERT INTO T_SYS_SQL_ERR
                (ID, MODEL_NAME, ERR_CODE, ERR_TEXT, TIME, OTHERS)
              VALUES
                (SEQ_SQL_ERR.nextval,
                 'PACK_LAND_EXCHANGE.IMPL_LAND_DATA_CHENGJIAO',
                 err_code,
                 err_text,
                 sysdate,
                 '[土地出让数据交换 -> 成交表数据交换]出错。'); 

在上面中,通过异常处理模块可以快速定位到出错的模块,并且可以得到出错的原因。  

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏java工会

Java后端开发猿不可不知的,Mybatis几种使用方式总结

1653
来自专栏Python爬虫实战

MySQL 从零开始:09 计算字段

在数据库中存储公司信息,一般用两个表列分别表示公司名和公司地址。 如果想要在一个字段中既显示公司名,又要显示公司地址,那么就需要对已有字段进行处理了,这个处理过...

722
来自专栏栗霖积跬步之旅

单例模式

定义:   单例模式,是一种常用的软件设计模式。在它的核心结构中只包含一个被称为单例的特殊类。通过单例模式可以保证系统中一个类只有一个实例。即一个类只有一个对象...

1979
来自专栏Java呓语

第11章、数据类型

关键字 INT 是 INTEGER 的别名,关键字 DEC 和 FIXED 是 DECIMAL的别名。 在 MyISAM/MEMORY/InnoDB和NDB表...

952
来自专栏CaiRui

SQLAlchemy外键的使用

orm可以将数据库存储的数据封装成对象,同时,如果封装的好的话,所有的数据库操作都可以封装到对象中。这样的代码在组织结构上会非常的清晰,并且相对与使用sql语句...

2635
来自专栏数据和云

SQL 使用like &#39;%ABC&#39; 和 like &#39;%ABC%&#39;的优

一般情况下,sql中使用col_name like 'ABC%‘的情况才能使用到col_name字段上的索引。那么如果是col_name like '%ABC%...

5038
来自专栏xingoo, 一个梦想做发明家的程序员

Java几种单例模式的实现与利弊

多线程环境下无法保证单例效果,会多次执行 instance=new Singleton(),需要考虑到多线程

2402
来自专栏C#

C#常用的IO操作方法

public class IoHelper { /// <summary> /// 判断文件是否存在 /...

22610
来自专栏小筱月

SSM框架的sql中参数注入(#和$的区别)

ORDER BY ${order} 和模糊查询 username LIKE '%${username}%' 是用$符号,其他的大多是用 #{} 来获取传递的参数...

1752
来自专栏乐沙弥的世界

PL/SQL --> 语言基础

PL/SQL是过程化的SQL语言,是ORACLE对SQL语言的扩展,在普通SQL语句的基础上增加了编程语言的特点。使得该语言不仅具有过程编程语

1073

扫码关注云+社区