Oracle读书笔记-PL/SQL基础

Oracle读书笔记-PL/SQL基础

PL/SQL概述

PL/SQL是Oracle对标准SQL规范的扩展，全面支持SQL的数据操作、事物控制等。

PL/SQL基本语法

1、PL/SQL语句
在PL/SQL中可以使用的SQL语句有:
INSERT、UPDATE、DELETE、SELECT INTO、COMMIT、ROLLBACK 和 SAVEPOINT
在编写PL/SQL语句时应当遵循如下规则:
* 语句可以写成多行，如sql语句一样
* 各个关键字、字段名称等通过空格分隔
* 每条语句必须以分号结束

2、PL/SQL命名规则
* 变量名长度不能超过30个字符
* 第一个字符必须为字母
* 不区分大小写
* 不能含有SQL关键

变量种类	命名规范	例子
程序变量	以v_作为前缀	v_name
程序常量	以c_作为前缀	c_name
游标变量	以_cursor作为后缀	emp_cursor
异常标识	以e_作为前缀	e_too_many
表类型	以_table_type作为后缀	emp_table_type
表变量	以_table作为后缀	emp_table
记录变量	以_record作为后缀	emp_record
SQL*Plus替代变量	以p_作为前缀	p_emp
绑定变量	以g_作为前缀	g_emp

3、注释
* 单行注释 --注释内容
* 多行注释 /*注释内容*/

4、基本语言块
PL/SQL的基本单位为“块”，所有的PL/SQL程序都是由一个或多个PL/SQL块构成的，这些块可以相互进行嵌套。一个基本的块有三部分组成。

[DECLARE
    ... --声明部分]
BEGIN
    ... --执行部分
[EXCEPTION
    ... --异常处理部分]
END;
/ --用于在命令窗口中执行以上内容，在sql窗口中可不用

例子：

declare 
  i integer;  --定义变量i，类型为integer
begin
  SELECT COUNT(*) INTO i FROM TEST_TREE; --要执行的sql
  DBMS_OUTPUT.put_line(i);  --输出语句
end;

结构说明：
* 声明部分(DECLARE)：主要用于声明变量、常量、数据类型、游标、异常处理名词以及局部子程序定义等。包含了变量和常量的数据类型和初始值。
* 执行部分(BEGIN)：执行部分是PL/SQL块的功能实现部分。这部分通过变量赋值、流程控制、数据查询、数据操纵、数据定义、事务控制、游标处理等实现块的功能。
* 异常处理部分(EXCEPTION)：在这一部分中处理异常或错误
* /： PL/SQL程序块需要使用正斜线(/)结尾，才能被执行。(根据环境的不同可用可不用)
* 可以在一个块的执行部分或异常处理部分嵌套其它的PL/SQL块
* 每一条语句必须以分号结尾

5、变量与常量
变量声明及初始化

variable_name data_type [ [ NOT NULL ] { := | DEFAULT } value ] ;

* variable_name 变量名
* data_type 变量数据类型
* := | DEFAULT := 为赋值操作(:=中间不能有空格) DEFAULT 为设置默认值
* NOT NULL 指定变量不允许为空，在变量声明时需指定一个不为空的初始值，且在程序其它部分不允许修改其值为NULL

常量声明及初始化

constant_name CONSTANT data_type { := | DEFAULT }  value ;

6、复合数据类型
常用的复合数据类型主要有三种：%TYPE类型、自定义记录类型和%ROWTYPE类型
* %TYPE类型
* 记录类型
* %ROWTYPE类型

表结构

SQL> desc test_tree;
Name         Type         Nullable Default Comments 
------------ ------------ -------- ------- -------- 
DEPT_ID      NUMBER                                 
DEPT_NAME    VARCHAR2(20)                           
PARE_DEPT_ID NUMBER       Y                         

%TYPE ：定义一个变量使其数据类型与已定义的某个数据变量的类型相同或者与数据库表的某个列的数据类型相同

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2  v_id test_tree.dept_id%TYPE;
  3  BEGIN
  4       SELECT test_tree.dept_id INTO v_id FROM test_tree WHERE test_tree.dept_id=2;
  5       DBMS_OUTPUT.put_line('部门ID:'||v_id);
  6  END;
  7  /

部门ID:2

PL/SQL procedure successfully completed

记录类型(以后补充)

%ROWTYPE ：根据数据表中行的结构定义数据类型，并存储数据表中检索到的一行数据

SQL> SET SERVEROUTPUT ON
SQL> DECLARE 
  2      row_record test_tree%ROWTYPE;
  3  BEGIN
  4      SELECT * INTO row_record FROM test_tree WHERE test_tree.dept_id=2;
  5      DBMS_OUTPUT.put_line('DEPT_ID:'||row_record.dept_id);
  6      DBMS_OUTPUT.put_line('DEPT_NAME:'||row_record.dept_name);
  7      DBMS_OUTPUT.put_line('PARE_DEPT_ID:'||row_record.pare_dept_id);
  8  END;
  9  /

DEPT_ID:2
DEPT_NAME:一级节点02
PARE_DEPT_ID:0

PL/SQL procedure successfully completed

流程控制

1、条件分支语句

    IF condition THEN
        statements1;
    ELSEIF condition THEN
        statements2;
    ELSE
        statements3;
    END IF;

    CASE search_expression   --待求值的表达式
        WHEN expression1 THEN result1;  --expression1与表达式的结果进行比较
        WHEN expression2 THEN result2;  --若值匹配则执行对应的result2后退出case
        [ ELSE default_result ; ] --若前面无匹配的值则使用此处定义的默认值
    END CASE;

    CASE 
        WHEN condition1 THEN result1;
        WHEN condition2 THEN result2;
        [ELSE default_result;]
    END CASE;

2、循环控制语句

• 无条件循环

    LOOP 
        statements --statements至少会被执行一次
        EXIT [ WHEN condition] --当condition=true时退出循环
    END LOOP;

• WHILE循环

    WHILE condition LOOP --只有condition=true时才执行statements
        statements
    END LOOP;

• FOR循环

    FOR loop_variable IN [REVERSE] lower_bound...upper_bound LOOP
        statements
    END LOOP;

loop_variable 循环变量
REVERSE 指定每次循环中循环变量递减，默认递增
lower_bound 指定循环的起始值
upper_bound 指定循环的终止值

本文内容为《Oracle 从基础到应用》读书笔记