Oracle plsql

PLSQL

PLSQL的基本使用

Posted by LQFGH on January 3, 2019

PL/SQL是一种高级数据库程序设计语言，该语言 专门用于在各种环境下对ORACLE 数据库进行访问。由 于该语言 集成于数据库服务器中， 所以PL/SQL 代码可以对数据进行快速高效的处理。除此之外，可以在 ORACLE数据库的某些客户端工具中，使用PL/SQL语言也是该语言的一个特点。本章的主要内容是讨论引入 PL/SQL语言的必要性和该语言的主要特点，以及了解PL/SQL语言的重要性和数据库版本问题。还要介绍一些 贯穿全书的更详细的高级概念，并在本章的最后就我们在本书案例中使用的数据库表的若干约定做一说明。

以下例子都使用SQL*PLUS进行编写 在使用之前需要先运行set serveroutput on 设置SQL*PLUS的环境参数 SERVEROUTPUT 为 on，否则看不到程序输出的结果。 ***

PL/SQL的基本语法

变量命名

变量命名在 PL/SQL 中有特别的讲究，建议在系统的设计阶段就要求所有编程人员共同遵守一定的要求， 使得整个系统的文档在规范上达到要求。下面是 建议的命名方法：

标识符 命名规则 例子
程序变量 V_name V_name
程序常量 C_Name C_company_name
游标变量 Name_cursor Emp_cursor
异常标识 E_name E_too_many
表类型 Name_table_type Emp_record_type
表 Name_table Emp_record_type
记录类型 Name_record Emp_record
SQL*Plus 替代变量 P_name P_sal
绑定变量 G_name G_year_sal

基本结构

PL/SQL 程序由三部分组成，即申明部分、执行部分、异常处理部分。 结构如下：

1
2
3
4
5
6
7

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

基本用法

其中declare和exception部分不需要时可以不写，但是begin和end是必不可少的部分。

1
2
3
4
5
6
7
8
9
10
11

DECLARE
	v_sal number(20);
	v_email varchar2(20);
	v_name varchar(20);
BEGIN 
	select salary,email,last_name into v_sal,v_email,v_name from employees where 					employee_id 			= 100;
    -- plsql输出语句
	dbms_output.put_line(v_sal||' '||v_email||' '||v_name);
--EXCEPTION
END;

动态获取表中字段的变量类型

1
2
3
4
5
6
7
8

DECLARE
	v_sal employees.salary%type;
	v_email employees.email%type;
	v_name employees.last_name%type;
BEGIN 
	select salary,email,last_name into v_sal,v_email,v_name from employees where 					employee_id 			= 100;
	dbms_output.put_line(v_sal||' '||v_email||' '||v_name);
END;

PL/SQL的记录类型

记录类型是把 逻辑相关 的数据作为一个单元存储起来 ，称作 PL/SQL RECORD 的域(FIELD) ，其作用是存 放互不相同但逻辑相关的信息。

定义记录类型语法如下

1
2
3
4
5

TYPE record_type IS RECORD(
Field1 type1 [NOT NULL] [:= exp1 ],
Field2 type2 [NOT NULL] [:= exp2 ],
. . . . . .
Fieldn typen [NOT NULL] [:= expn ] ) ;

举例

1
2
3
4
5
6
7
8
9
10
11
12
13
14

DECLARE
  TYPE emp_record is record(
			v_sal employees.salary%type,
			v_email employees.email%type,
			v_name employees.last_name%type
	);
	--定义一个记录类型的变量
	v_emp_record emp_record;
BEGIN 
-- 按照记录类型中定义变量的顺序将查出来的值放入记录类型中
  select salary,email,last_name into v_emp_record from employees where employee_id = 100;
  -- 分别打印记录类型中的变量
  dbms_output.put_line(v_emp_record.v_sal||' '||v_emp_record.v_email||' '||v_emp_record.v_name);
END;

变量赋值

在 PL/SQL 编程中，变量赋值是一个值得注意的地方，它的语法如下： variable := expression ; variable 是一个 PL/SQL 变量, expression 是一个 PL/SQL 表达式.

1
2
3
4
5
6
7
8
9
10
11
12

DECLARE
    TYPE salary_record IS RECORD(
         v_name VARCHAR(20),
         v_salary NUMBER(8,2)
    );
    v_salary_record salary_record;
BEGIN 
    v_salary_record.v_name := '华仔';
    v_salary_record.v_salary := 200000;
    dbms_output.put_line( v_salary_record.v_name||'  '||  v_salary_record.v_salary);
END;

使用%ROWTYPE

PL/SQL 提供%ROWTYPE 操作符, 返回一个记录类型, 其数据类型和数据库表的数据结构相一致。 使用%ROWTYPE 特性的优点在于：

  • 所引用的数据库中列的个数和数据类型可以不必知道；
  • 所引用的数据库中列的个数和数据类型可以实时改变。
1
2
3
4
5
6
7

DECLARE
	v_emp_record employees%ROWTYPE;
    
BEGIN 
	SELECT * INTO v_emp_record FROM employees WHERE employee_id = 100;
	dbms_output.put_line( v_emp_record.last_name||'  '||	v_emp_record.salary);
END;

更新操作

其实和查询只是sql语句之间的区别，但是这里还是放一个例子

1
2
3
4
5
6
7
8
9
10
11
12

DECLARE
    v_emp_id NUMBER(10);
    
BEGIN 
    v_emp_id :=100;
    UPDATE employees e
    SET e.salary = e.salary + 100
    WHERE e.employee_id = v_emp_id;
    
    dbms_output.put_line('执行成功！');
END;

删除和插入这里就不列出来了，都只是sql上的区别，用法是一样的

PL/SQL流程控制语句

介绍 PL/SQL 的流程控制语句, 包括如下三类:

  • 控制语句: IF 语句
  • 循环语句: LOOP 语句, EXIT 语句
  • 顺序语句: GOTO 语句, NULL 语句

条件语句

1、基本语法

1
2
3
4
5
6
7
8
9

IF <布尔表达式> THEN
	PL/SQL 和 SQL 语句;
ELSIF < 其它布尔表达式> THEN
	其它语句;
ELSIF < 其它布尔表达式> THEN
	其它语句;
ELSE
	其它语句;
END IF;

2、 举例

1
2
3
4
5
6
7
8
9
10
11
12
13

DECLARE
    v_sal employees.salary%TYPE;
    v_msg VARCHAR2(20);
BEGIN 
  SELECT e.salary INTO v_sal FROM employees e WHERE e.employee_id = 100;  

  IF v_sal >= 1000 THEN v_msg := '工资大于10000';
  ELSIF v_sal < 10000 AND v_sal > 6000 THEN  v_msg := '工资大于6000小于10000';
  ELSE  v_msg := '工资小于6000';
  END IF;
  dbms_output.put_line(v_msg);

END;

CASE 表达式

1、基本语法

1
2
3
4
5
6

CASE selector
    WHEN expression1 THEN result1
    WHEN expression2 THEN result2
    WHEN expressionN THEN resultN
    [ ELSE resultN+1]
END;

2、举例

1
2
3
4
5
6
7
8
9
10
11
12
13

DECLARE
    V_grade char(1) ;
    V_appraisal VARCHAR2(20);
BEGIN
    V_appraisal :=
    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||’ Appraisal: ‘|| v_appraisal);
END;

循环

1、简单循环
1
2
3
4

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

例：

输出0-i

1
2
3
4
5
6
7
8
9
10
11
12
13

DECLARE 
    v_num NUMBER(3) := 0;

BEGIN 
    LOOP
      EXIT WHEN v_num >= 100;
      v_num := v_num + 1;
      dbms_output.put_line(v_num);
    
    END LOOP;
END;
2、WHILE 循环( 相较 1 ，推荐使用 2)
1
2
3

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

例：

输出0-i

1
2
3
4
5
6
7
8

DECLARE
    v_num NUMBER(3) := 0;
BEGIN 
    WHILE v_num <= 100 LOOP
        dbms_output.put_line(v_num);
        v_num := v_num + 1;
    END LOOP;
END;
3、数字式循环
1
2
3

FOR 循环计数器 IN [ REVERSE ] 下限 .. 上限 LOOP
	要执行的语句;
END LOOP;

每循环一次，循环变量自动加 1；使用关键字 REVERSE，循环变量自动减 1。跟在 IN REVERSE 后面的数字必 须是从小到大的顺序， 而且必须是整数，不能是变量或表达式。可以使用 EXIT 退出循环。

例：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

CREATE TABLE temp_table(num_col NUMBER);

DECLARE
`V_counter NUMBER := 10;
BEGIN
    INSERT INTO temp_table(num_col) VALUES (v_counter );
    FOR v_counter IN 20 .. 25 LOOP
   	 INSERT INTO temp_table (num_col ) VALUES ( v_counter );
    END LOOP;
    INSERT INTO temp_table(num_col) VALUES (v_counter );
    FOR v_counter IN REVERSE 20 .. 25 LOOP
   	 INSERT INTO temp_table (num_col ) VALUES ( v_counter );
    END LOOP;
END ;

DROP TABLE temp_table;

游标

为了处理 SQL 语句，ORACLE 必须分配一片叫上下文( context area )的区域来处理所必需的信息，其中 包括要处理的行的数目，一个指向语句被分析以后的表示形式的指针以及查询的活动集(active set)。 游标是一个 指向上下文的句柄( handle) 或指针。通过游标，PL/SQL 可以控制上下文区和处理语句时上 下文区会发生些什么事情。 对于不同的 SQL 语句，游标的使用情况不同： | SQL语句 | 游标 | | ———- | ——- | | 显示的 | 隐式的 | | 结果是单行的查询语句 | 隐式的或显示的 | | 结果是多行的查询语句 | 非查询语句 |

举例1

游标基本用法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

DECLARE
    v_sal employees.salary%TYPE;
    v_emp_id employees.employee_id%TYPE;
    -- 定义游标
    CURSOR c_emp IS SELECT e.salary,e.employee_id FROM employees e WHERE e.department_id = 80;
    
BEGIN 
  --打开游标
    OPEN c_emp;
    -- 提取游标数据
    FETCH c_emp INTO v_sal,v_emp_id;

    WHILE c_emp%FOUND LOOP
          dbms_output.put_line(v_sal||'   '||v_emp_id);
          -- 提取游标数据
          FETCH c_emp INTO v_sal,v_emp_id;
    END LOOP;
    -- 关闭游标
    CLOSE c_emp;

END;

举例2

记录类型结合游标使用

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

DECLARE
    TYPE emp_record IS RECORD(
        v_sal employees.salary%TYPE,
        v_emp_id employees.employee_id%TYPE
    );

    v_emp_record emp_record;
    CURSOR c_emp IS SELECT e.salary,e.employee_id FROM employees e WHERE e.department_id = 80;
    
BEGIN 
  --打开游标
    OPEN c_emp;
    -- 提取游标数据
    FETCH c_emp INTO v_emp_record;

    WHILE c_emp%FOUND LOOP
          dbms_output.put_line(v_emp_record.v_sal||'   '||v_emp_record.v_emp_id);
          FETCH c_emp INTO v_emp_record;
    END LOOP;
    -- 关闭游标
    CLOSE c_emp;

END;

游标属性

  • %FOUND 布尔型属性，当最近一次读记录时成功返回,则值为 TRUE；
  • %NOTFOUND 布尔型属性，与%FOUND 相反；
  • %ISOPEN 布尔型属性，当游标已打开时返回 TRUE；
  • %ROWCOUNT 数字型属性，返回已从游标中读取的记录数。
CATALOG
    FEATURED TAGS
    mysql mysql高级 MyBatis Source Code springboot
    FRIENDS