PL/SQL基础知识

　　自己在学习Oracle是做的笔记及实验代码记录，内容挺全的，也挺详细，发篇博文分享给需要的朋友，共有1w多字的学习笔记吧。是以前做的，一直在压箱底，今天拿出来整理了一下，给大家分享，有不足之处还望大家批评指正。

　　PL/SQL定义：PL/SQL是由Oracle开发，专门用于Oracle的程序设计语言。 PL---Procedural Language. SQL—Structure QueryLanguage。PL/SQL包括过程化语句和SQL语句

PL/SQL的单位：块。一个块中可以嵌套子块。

块的三个组成部分：

一：定义部分（declare）

PL/SQL中使用的变量，常量，游标和异常的名字都必须先定义后使用。并且定义在以declare关键字开头的定义部分

二：可执行部分：（begin）

是PL/SQL的主题，包含该块的可执行语句，该部分定义了块的功能，是必须的部分。由关键字begin开始，end结束

三：异常处理部分：（exception）

该部分以exception开始，以end结束

Demo

        DECLARE –可选
            变量，常量，游标，用户自定义的特殊类型
        BEGIN –必须
            --SQL语句
            --PL/SQL语句
        EXCEPTION –可选
        --异常处理部分
        END; --必须
　　　　即由声明，执行，异常组成

DEMO:

        DECLARE
            V_value1 VARCHAE2(5);
        BEGIN
            SELECT cn_name
            INTO  v_value1
            FROM table_name;
        EXCEPTION
            WHEN exception_name  THEN
            --处理程序……
        END;

PL/SQL的优点：

　　1、 改善了性能：PL/SQL把整个语句块发送给服务器，这个过程在单次调用中完成，降低了网络拥挤

　　2、 可重用性：只要有Oracle的地方都能运行

　　3、 模块化：程序中的每一块都实现一个逻辑操作，有效的进行了分割。

PL/SQL块的类：

　　1、 匿名块：只能存储一次，不能存储在数据库中

　　2、 过程，函数和包（procedure，function，package）：是命了名的PL/SQL块，被存储在数据库中，可以被多次使用，可以用外部程序显示执行。

　　3、 触发器：是命名的PL/SQL块，被存储在数据库中，当触发某事件时自动执行。

PL/SQL中变量的命名规范：

　　1、 至多有30个字符

　　2、 不能是保留字

　　3、 必须以字母开头

　　4、 不允许和数据库中表的列名相同

　　5、 不可包括$,_和数字以外的字符

PL/SQL中的变量

　　1、 PL/SQL变量

　　　　a) 标量型：只能存放单一值

　　　　b) 复合型

　　　　c) 引用型

　　　　d) LOBx型：存放大数据

　　2、 定义变量语法

　　　　a) 变量名 变量类型 := 变量值

　　　　b) V_number NUMBER(2) NOT NULL :=20;

　　　　c) 常量的定义

　　 i. V_number CONSTANT

　　　　　　　　　 NUMBER(2,3) :=20.098;

DEMO：查询员工号为7369的员工，把其job存入v_job中并输出

 DECLARE

--定义存储job的变量v_job为引用变量与--emp.job的类型相同，用%TYPE实现

      v_job emp.job%TYPE;

     --定义员工号变量并赋初值（:=）

      n_empno emp.empno%TYPE:=7369;

 BEGIN

     --查询语句

     SELECT emp.job

     --把查出来的结果赋给v_job

     INTO   v_job

     FROM   emp

     WHERE emp.empno = n_empno;

     --打印输出结果

     Dbms_Output.put_line(v_job);

 END;

%TYPE属性:

通过%TYPE声明一个变量，实际上就是参照变量或表中的字段的类型作为变量的类型，并且保持同步。

变量将遵循下面的类型声明：

　　1. 已声明过的变量类型

　　2. 数据库中表字段的类型

demo1:

创建一个匿名块，输出hello world

  --创建一个匿名块，输出hello world

  DECLARE

    v_hello varchar2(20) :='Hello World';

 BEGIN

    Dbms_Output.put_line(v_hello);

  END;

 

  --创建一个匿名块，查询emp表，显示雇员名是’SCOTT‘的薪水，通过DBMS_OUTPUT包来显示。

  DECLARE

    v_sal  emp.sal%TYPE;

    v_name emp.ename%TYPE := 'SCOTT';

 BEGIN

   SELECT emp.sal

   INTO v_sal

   FROM emp

   WHERE emp.ename = v_name;

   dbms_output.put_line(v_sal);

 END;

demo2:

 --从部门表中找到最大的部门号，将其输出到屏幕

 DECLARE

   v_deptno dept.deptno%TYPE;

 BEGIN

   SELECT MAX(dept.deptno)

   INTO v_deptno

   FROM dept;

   dbms_output.put_line(v_deptno);

 END;

demo3:

--PL/SQL嵌套和变量的作用域

DECLARE

   v_parent NUMBER :=10;

BEGIN

   DECLARE

     v_child NUMBER :=20;

   BEGIN

     dbms_output.put_line('chile='||v_child);

     dbms_output.put_line('parent='||v_parent);

   END;

   --dbms_output.put_line('chile='||v_child);  --注意变量的作用域

   dbms_output.put_line('chile='||v_parent);

END;

结果：

chile=20

parent=10

chile=10

demo4:

--选择并打印emp表中薪水总和

 

 --选择并打印emp表中薪水总和

 DECLARE

    v_sal emp.sal%TYPE;

 BEGIN

   SELECT sum(emp.sal)

   INTO v_sal

   FROM emp;

   dbms_output.put_line(v_sal);

 END

demo5:事务的操作

 

 DECLARE

   v_sal emp.sal%TYPE :=800;

 BEGIN

     UPDATE emp

     SET emp.sal = emp.sal+ v_sal

     WHERE emp.job='ANALYST';

   SAVEPOINT a;

     UPDATE emp

     SET emp.sal = emp.sal+ v_sal

     WHERE emp.job='ANALYST';

   SAVEPOINT b;

   ROLLBACK TO SAVEPOINT a;

   COMMIT;

 END;

编写控制结构

　　1、 条件分支语句

　　　　a) IF语句：　　

1. i. – IF – THEN – END IF
2. ii. – IF – THEN – ELSE – END IF
3. iii. – IF – THEN – ELSEIF – END IF

　　2、 条件语句语法

　　　　a) IF condition THEN

　　　　　　i. Statement;

　　　　b) [ELSIF condition THEN

　　　　　　i. Statement;]

　　　　c) [ELSE

　　　　　　i. Statement;]

　　　　d) ENDIF;

　　3、 DEMO:

　　　　a) IF v_name = ‘SCOTT’ AND SAL >= 3000 THEN

　　　　　　i. v_dept :=20;

　　　　b) END IF;

 1 DEMO:

  --null的处理

 DECLARE

   v_x NUMBER :=NULL;

   v_y NUMBER := NULL;

 BEGIN

   IF v_x = v_y THEN

    dbms_output.put_line('NULL等于NULL');

  ELSE

     dbms_output.put_line('NULL不等于NULL');

   END IF;

 END;

 结果：NULL不等于NULL  空是未知的东西

　　4.Case语句：语法(有返回值的)

 CASE demo:

 DECLARE

   v_sal emp.sal%TYPE;

   v_dept emp.deptno%TYPE;

   v_result VARCHAR(20);

 BEGIN

  SELECT emp.deptno

  INTO v_dept

   FROM emp

   WHERE emp.sal =

   (

         SELECT MAX(emp.sal)

         FROM emp

   );

   dbms_output.put_line(v_dept);

   v_result :=

   CASE v_dept

     WHEN 10 THEN '部门一'

     WHEN 20 THEN '部门二'

     ELSE '部门三'

   END;

   dbms_output.put_line(v_result);  

 END;

 输出结果：

 10

 部门一

5、 循环语句LOOP ：

DEMO：循环插入11条数据

DECLARE

v_count NUMBER := 0;

BEGIN

LOOP

--插入数据

INSERT INTO

test(name,id,password)

VALUES ('TEST'||v_count,v_count,'admin');

--变量加一

v_count := v_count+1;

--判断退出条件

EXIT WHEN v_count > 10;

13 END LOOP;

14 END;

b) FOR LOOP循环

DEMO：

DECLARE

v_counter NUMBER :=0;

BEGIN

--v_counter是自增的

FOR v_counter IN 0 .. 10 LOOP

DELETE FROM test

WHERE test.id = v_counter;

END LOOP;

END;

b) WHILE LOOP

DECLARE

v_count NUMBER := 0;

BEGIN

WHILE v_count<10 LOOP

--插入数据

INSERT INTO

test(name,id,password)

VALUES ('TEST'||v_count,v_count,'admin');

--变量加一

v_count := v_count+1;

END LOOP;

END;

三：复合类型

　　1、 复合数据类型

　　　　a) 一个复合变量可以存放多个值

　　　　b) 复合变量创建后可以多次使用

　　　　c) 如同枚举类型和数组

　　2、 PL/SQL记录

　　　　a) 每个记录内都有很多的不同类型的字段

　　　　b) 无初始值的字段为NULL

　　　　c) Record 类型声明用户自定义的类型

　　3、 定义一个记录

　　　　a) 语法：

1 i. TYPE type_name IS RECORD(

2 ii. 字段名1 字段类型1，

3 iii. 字段名2 字段类型2

4 iv. )；

b) DEMO

i. TYPE emp_record_name IS RECORD(

ii. V_name varchar(20),

iii. V_password varchar(10)

iv. );

v. Emp_record emp_record_name;

--记录的定义与使用

DECLARE

TYPE test_record_name

IS RECORD(

v_name test.name%TYPE,

v_id test.id%TYPE,

v_password test.password%TYPE

);

test_record test_record_name;

BEGIN

SELECT test.name,test.id,test.password

INTO test_record

FROM test

WHERE test.name='TEST0';

dbms_output.put_line(test_record.v_name||test_record.v_id||test_record.v_password);

END;

5 记录的另一种定义：表名%ROWTYPE

　　a) Exp_row table_name%ROWTYPE

　　DEMO:

 --记录的定义与使用

 DECLARE

  emp_record emp%ROWTYPE;



 BEGIN

   SELECT *

   INTO   emp_record

   FROM emp

   WHERE emp.empno='7788';

   dbms_output.put_line(emp_record.empno||'  '||emp_record.sal);

 END;

编写游标

　　1、 游标的定义：游标是Oracle在数据库中开辟的一个工作区，用来存放SELECT语句查询的结果。

　　2、 游标的分类

　　　　a) 隐式游标：PL/SQL隐式建立并管理这一游标。

　　　　b) 显示游标：由程序员定义并控制，从数据库中读出多行数据，并从多行数据中一行一行的处理。

　　3、 游标的声明：

　　　　a) 语法：CURSOR cursor_name IS select_statement;

　　　　b) 在游标声明中SELECT语句不能使用INTO语句，可以在字句子中使用ORDER字句。

　　c) Demo:

CURSOR emp_cursor IS

SELECT *

FROM emp;

　　4、 打开游标

　　　　a) 语法：OPEN cursor_name;

　　　　b) 使用游标之前应打开游标

　　　　c) 打开游标实际上是执行定义游标时的SELECT语句，将查询结果检索到工作区中。

　　　　d) 如果没有要返回的行没有异常

　　5、 从游标中提取数值

　　　　a) 语法

　　　　　　 i. FETCH cursor_name INTO [v1,v2……]|record_name]

　　　　b) 在使用FETCH时先把游标打开，不然没法使用。

　　　　c) 对游标第一次使用FETCH时，游标指向的是第一条记录，使用后游标指向下一条记录。

　　　　d) 游标只能向下移动不能回退，如果想回退到上一条记录，只有把游标关闭后在打开。

　　　　e) INTO字句中的变量个数、顺序、数据类型必须和工作区中的保持一致；

　　6、 关闭游标

　　　　a) 语法：CLOSE cursor_name

　　　　b) 处理完数据后必须关闭游标，如果需要可以再次打开游标，游标一旦关闭不可再从游标中提取数据，当关闭游标后所有和游标相关的资源都会被关闭。

　　7.游标的使用Demo

 1 --游标的使用

 DECLARE

   --定义临时变量来存放游标中的内容

   emp_empno emp.empno%TYPE;

 emp_ename emp.ename%TYPE;

   --定义名为emp_cursor的游标

   CURSOR emp_cursor IS

   SELECT emp.empno,emp.ename

   FROM emp;

 BEGIN

   --打开游标

   OPEN emp_cursor;

   --循环输出游标

   FOR i IN 1..5 LOOP

     --提取游标中的内容

     FETCH emp_cursor

     INTO emp_empno,emp_ename;

     dbms_output.put_line(emp_empno||'  '||emp_ename);

 END LOOP;

   --关闭游标

 CLOSE emp_cursor;

 END;