PL/SQL之--变量

简介: 一、PL/SQL 简介   PL/SQL也是一种程序语言,叫做过程化SQL语言(Procedural Language/SQL)。PL/SQL是oracle对sql语句的一种扩展,在普通SQL语句的使用上增加了编程语言的特点,所以PL/SQL就是把数据操作和查询语句组织在PL/SQL代码的过程性单元中,通过逻辑判断、循环等操作实现复杂的功能或者计算的程序语言,只能在oracle中运行。

一、PL/SQL 简介

  PL/SQL也是一种程序语言,叫做过程化SQL语言(Procedural Language/SQL)。PL/SQL是oracle对sql语句的一种扩展,在普通SQL语句的使用上增加了编程语言的特点,所以PL/SQL就是把数据操作和查询语句组织在PL/SQL代码的过程性单元中,通过逻辑判断、循环等操作实现复杂的功能或者计算的程序语言,只能在oracle中运行。当然别的数据库也有自己的"pl/sql",这不是oracle特有的。如mysql也有,但是每种数据库的都不一样,功能也有些差别。oracle中的PL/SQL就比mysql中的强大许多。

  PL/SQL 优点如下:

  • 能够使一组SQL语句的功能更具模块化程序特点;
  • 采用了过程性语言控制程序的结构;
  • 可以对程序中的错误进行自动处理,使程序能够在遇到错误的时候不会被中断;
  • 具有较好的可移植性,可以移植到另一个Oracle数据库中;
  • 集成在数据库中,调用更快;
  • 减少了网络的交互,有助于提高程序性能

   PL/SQL块由四个基本部分组成:声明、执行体开始、异常处理、执行体结束。四个部分的基本结构如下:

DECLARE -- 可选部分
-- 变量、常量、游标、用户定义异常的声明
BEGIN -- 必要部分
-- SQL语句和PL/SQL语句构成的执行程序 
EXCEPTION -- 可选部分
-- 程序出现异常时,捕捉异常并处理异常 
END;-- 必须部分

 二、PL/SQL变量

  1、程序变量

  PL/SQL支持SQL中的数据类型,包括NUMBER,VARCHAR2,DATE等Oracle SQL数据类型。声明变量必须指明变量的数据类型,也可以声明变量时对变量设置初始值,变量声明必须在DECLARE部分。声明变量的语法如下:

  变量名 数据类型 := 初始值;     --设置初始值

  变量名 数据类型;      --不设置初始值

  变量的赋值必须在begin与end直接进行。可以直接使用 := 赋值,也可以使用select语句中的into进行赋值。代码示例如下:

DECLARE
    v_name VARCHAR2(50); --声明变量
    v_nickname VARCHAR2(50) := '小王'; --声明变量,并设置初始值
  v_age NUMBER;
BEGIN
  v_name := '王五'; --为变量赋值
  SELECT age INTO v_age FROM person WHERE id = 1; --通过查询的方式设置值
    -- 使用oracle dbms输出每个变量的值
    dbms_output.put_line('v_name:'||v_name);
    dbms_output.put_line('v_nickname:'||v_nickname);
    dbms_output.put_line('v_age:'||v_age);
END;

  2、程序常量

  对于查询常量来说,必须在声明的时候给它赋值。而且不能再次设置它的值。这类似于java中的常量。程序常量使用constant在声明的时候进行修饰,声明语法如下:

  变量名 constant 数据类型 := 初始值; --设置初始值

  实例代码如下;

DECLARE 
    c_nickname constant VARCHAR2(50) := '小王'; --声明一个常量,并设置初始值 ,不设置初始值会报错
BEGIN
    --c_nickname := '王五'; --为常量赋值会报错
    dbms_output.put_line('c_nickname:'||c_nickname); --打印常量的值
END;

  3、绑定变量

    在sql plus中可以使用绑定变量,使用方式如下:

  声明:

var v_name varchar2(50);

  赋值

execute :v_name :='hello world';

  打印结果

 print v_name;

  打印结果如下:

  该变量仅存在于当前会话中,如果当前会话关闭,重新开启一个连接,则该变量就会不存在。

三、变量类型

  以下示例代码中person表结构如下:

id NUMBER(11) NOT NULL ,
username VARCHAR2(255 ) NULL ,
age NUMBER(11) NULL ,
password VARCHAR2(255) NULL ,
PRIMARY KEY (id)
)

-- ----------------------------
-- Records of PERSON
-- ----------------------------
INSERT INTO PERSON VALUES ('1', '张三', '20', 'zhang123');
INSERT INTO PERSON VALUES ('2', '李四', '20', 'lisi123');
INSERT INTO PERSON VALUES ('3', '王五', '20', 'wang123');
INSERT INTO PERSON VALUES ('4', '赵六', '20', 'zhao123');

  1、%type

    %type主要是用来定义某个变量的数据类型与已知的变量数据类型或表中某个类的数据类型相同。使用%type的好处如下:  一、当我们不知道数据表中的某个字段的数据类型我们就可以使用;二、数据库中字段的数据类型可以在运行时已经发生改变,此时我们无需修改程序,因为程序中的%type会随字段类型发生相应的变坏。

  声明语法如下:

    变量名称  表名.字段名%type;

  示例代码如下:

DECLARE
    v_id person.id%TYPE ; -- v_id变量数据类型是person表中id的数据类型
    v_username person.username%TYPE ; -- v_id变量数据类型是person表中username的数据类型
    v_age person.age%TYPE ;  -- v_id变量数据类型是person表中age的数据类型
    v_password person.password%TYPE ; -- v_id变量数据类型是person表中password的数据类型
BEGIN
    SELECT
        ID, username, age, password 
    INTO 
        v_id, v_username, v_age, v_password
    FROM
        person
    WHERE
        ID = 1 ; 
    dbms_output.put_line ('id:' || v_id) ; 
    dbms_output.put_line ('username:' || v_username) ; 
    dbms_output.put_line ('age:' || v_age) ; 
    dbms_output.put_line ('password:' || v_password) ;
END ;

  查询id为1的记录,并打印结果。

  2、%rowtype

    %rowtype也是用于定义不确定类型的变量,可以理解成数据库记录一行提取出来的一个副本。通过%rowtype,我们可以获取一行记录,然后再使用变量.属性名,获取单个的属性值。声明%rowtype的语法如下:
  变量名  表名%rowtype;

  示例代码如下:

 1 DECLARE
 2     r_person person%rowtype; -- 表示person表的一行
 3 BEGIN 
 4     -- 方式一赋值
 5   SELECT id, username, age, password INTO r_person.id, r_person.username, r_person.age, r_person.password FROM person WHERE id=1;
 6     dbms_output.put_line('id:'||r_person.id);
 7   dbms_output.put_line('username:'||r_person.username);
 8   dbms_output.put_line('age:'||r_person.age);
 9   dbms_output.put_line('password:'||r_person.password);
10     -- 方式二赋值
11     SELECT * INTO r_person FROM person WHERE id = 2;
12   dbms_output.put_line('id:'||r_person.id);
13   dbms_output.put_line('username:'||r_person.username);
14   dbms_output.put_line('age:'||r_person.age);
15   dbms_output.put_line('password:'||r_person.password);
16 END ;

  我们可以使用上面两种方式进行赋值。在游标中可以用到。

  3、varray

    varray(varing array)是PL/SQL中的动态数组类型.我们可以动态地对数组的大小进行扩展,但是扩展后的总大小,不能超过声明时候的大小。使用varray的时候,需要先声明一个varray数组的类型,然后声明一个变量并把这个类型赋值给它。使用的时候都是操作这个声明的变量。

    示例如下:

 1 DECLARE
 2     TYPE arrays IS VARRAY(7) OF VARCHAR2(10) ; -- 定义一个数组,数组元素是5个,每个元素类型为varchar2(10)
 3     -- arrays 是一个对象,使用前需要用一个变量去引用,v_list是变量
 4     v_list arrays := arrays ('zhangsan', 'lisi', 'wangwu', 'zhaoliu', 'wangw') ; 
 5     asize NUMBER ; -- 记录数组大小
 6 BEGIN
 7     dbms_output.put_line (v_list(1)) ; 
 8     dbms_output.put_line (v_list(2)) ; 
 9     dbms_output.put_line (v_list(3)) ; 
10     dbms_output.put_line (v_list(4)) ; 
11     dbms_output.put_line (v_list(5)) ; 
12     --    dbms_output.put_line(v_list(6)); -- 此时打印会保持下标越界。
13     asize := v_list.COUNT() ; 
14     dbms_output.put_line ('total:' || asize) ; 
15     -- 对v_list大小进行增加
16     v_list.EXTEND(2) ; -- 此时增加后的总共元素不能超过声明时候的大小
17     asize := v_list.COUNT() ; 
18     dbms_output.put_line('total:' || asize) ; 
19     v_list (6) := 'xiaozhao' ; 
20     v_list (7) := 'xiaoli' ; 
21     dbms_output.put_line (v_list(6)) ; 
22     dbms_output.put_line (v_list(7)) ; 
23     -- 对v_list大小进行减少
24     v_list.TRIM(2) ; 
25     asize := v_list.COUNT() ; 
26     dbms_output.put_line('total:' || asize) ;
27 END ;

   16行对数组进行扩展,17行对数组大小进行减小。运行结果如下:

  4、table

  table 类型与javascript中数组类似,可以理解成可变数组。可以理解成它的大小是无穷的,我们可以给它的任何索引对应的空间进行赋值,声明的时候也无需指定它的大小。

  示例代码如下:

 1 DECLARE
 2    TYPE strings IS TABLE OF VARCHAR2(10) --元素类型是varchar2(10)
 3    INDEX BY binary_integer;
 4    v_list strings; -- 定义的table不能直接使用,必须赋给另一个变量
 5 BEGIN
 6   v_list(1) := 'hello';
 7   v_list(9999) := 'world';
 8   v_list(99) := 33; --赋值数字,会被转换成字符 ,如果类型是数字,赋值字符的时候,则会报错
 9   dbms_output.put_line(v_list(1));  
10   dbms_output.put_line(v_list(99));
11   dbms_output.put_line(v_list(9999));   
12 END;

  运行结果如下:

  5、record 

    record可以理解成java中的集合,可以存放多种类型的数据。record也是需要先声明,然后用声明变量来赋值,再操作变量。

    示例代码如下:

 1 DECLARE
 2     TYPE v_record IS RECORD (
 3         id number,
 4         username person.username%TYPE,
 5         r_tb_person person%ROWTYPE
 6     );
 7   v_person v_record; -- record 不能直接使用,必须赋值给变量
 8 BEGIN
 9     /*  select id, username, age, password into  v_person.r_tb_person.id,  v_person.r_tb_person.username,   v_person.r_tb_person.age,   v_person.r_tb_person.password   from person where id=1; 该种方式赋值也可以*/
10     SELECT * INTO  v_person.r_tb_person FROM person WHERE id = 1 ;
11     dbms_output.put_line('id:'||v_person.r_tb_person.id);
12   dbms_output.put_line('username:'||v_person.r_tb_person.username);
13   dbms_output.put_line('age:'||v_person.r_tb_person.age);
14   dbms_output.put_line('password:'||v_person.r_tb_person.password);
15 END ;

目录
相关文章
|
1月前
|
SQL 数据采集 监控
局域网监控电脑屏幕软件:PL/SQL 实现的数据库关联监控
在当今网络环境中,基于PL/SQL的局域网监控系统对于企业和机构的信息安全至关重要。该系统包括屏幕数据采集、数据处理与分析、数据库关联与存储三个核心模块,能够提供全面而准确的监控信息,帮助管理者有效监督局域网内的电脑使用情况。
19 2
|
4月前
|
SQL Oracle 关系型数据库
SQL与PL/SQL:数据库编程语言的比较
【8月更文挑战第31天】
72 0
|
5月前
|
SQL 运维 分布式计算
DataWorks产品使用合集之ODPPS中如何使用SQL查询从表中获取值并将其赋值给临时变量以供后续使用
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
|
6月前
|
SQL 存储 分布式计算
MaxCompute产品使用问题之odps sql如何定义变量
MaxCompute作为一款全面的大数据处理平台,广泛应用于各类大数据分析、数据挖掘、BI及机器学习场景。掌握其核心功能、熟练操作流程、遵循最佳实践,可以帮助用户高效、安全地管理和利用海量数据。以下是一个关于MaxCompute产品使用的合集,涵盖了其核心功能、应用场景、操作流程以及最佳实践等内容。
199 0
|
6月前
|
SQL 数据库 Perl
PL/SQL中执行按钮变为灰色后如何恢复【已解决】
PL/SQL中执行按钮变为灰色后如何恢复【已解决】
517 0
|
6月前
|
SQL 缓存 Oracle
SQL调优之绑定变量用法简介
SQL调优之绑定变量用法简介
|
7月前
|
SQL Oracle 关系型数据库
Oracle的PL/SQL游标自定义异常:数据探险家的“专属警示灯”
【4月更文挑战第19天】Oracle PL/SQL中的游标自定义异常是处理数据异常的有效工具,犹如数据探险家的警示灯。通过声明异常名(如`LOW_SALARY_EXCEPTION`)并在满足特定条件(如薪资低于阈值)时使用`RAISE`抛出异常,能灵活应对复杂业务规则。示例代码展示了如何在游标操作中定义和捕获自定义异常,提升代码可读性和维护性,确保在面对数据挑战时能及时响应。掌握自定义异常,让数据管理更从容。
|
7月前
|
SQL Oracle 安全
Oracle的PL/SQL游标异常处理:从“惊涛骇浪”到“风平浪静”
【4月更文挑战第19天】Oracle PL/SQL游标异常处理确保了在数据操作中遇到的问题得以优雅解决,如`NO_DATA_FOUND`或`TOO_MANY_ROWS`等异常。通过使用`EXCEPTION`块捕获并处理这些异常,开发者可以防止程序因游标问题而崩溃。例如,当查询无结果时,可以显示定制的错误信息而不是让程序终止。掌握游标异常处理是成为娴熟的Oracle数据管理员的关键,能保证在复杂的数据环境中稳健运行。
|
7月前
|
SQL Oracle 安全
Oracle的PL/SQL异常处理方法:守护数据之旅的“魔法盾”
【4月更文挑战第19天】Oracle PL/SQL的异常处理机制是保障数据安全的关键。通过预定义异常(如`NO_DATA_FOUND`)和自定义异常,开发者能优雅地管理错误。异常在子程序中抛出后会向上传播,直到被捕获,提供了一种集中处理错误的方式。理解和善用异常处理,如同手持“魔法盾”,确保程序在面对如除数为零、违反约束等挑战时,能有效保护数据的完整性和程序的稳定性。
|
SQL
SQL*Plus中替换变量与定义变量
替换变量 SQL*Plus中的替换变量又叫替代变量,它一般用来临时存储相关数据;在SQL语句之间传递值。一般使用&或&&前缀来指定替换变量. 关于使用替换变量,一般是利用其创建通用的脚本或达到和用户交换目的。
1231 0