PL SQL基本内容(原创)

本节介绍PL SQL的基本内容

本节所举示例数据来源oracle用户scott下的emp表和dept表，数据如下：

一、plsql简介：

1、概念:procedural language，过程化sql语言，是面向过程的语言，在普通sql的基础上增加了编程语言的特点。PL/SQL的基本单元是块。

2、块的介绍：

（1）基本结构：

DECLARE

(声明部分)

BEGIN

(执行部分)

EXCEPTION

(异常处理部分)

END(结束标记)

红色部分为必须部分，不可缺少

3、块的分类：

（1）匿名块：只会执行一次，被动态构造。

（2）子程序：存储在数据库中的存储过程、函数、包等，完成一定功能，可以在其他程序上调用他们。

（3）当数据库发生操作时，会触发事件自动执行相应的程序。

（注：存储过程、触发器会在后面的博客中详细说明）

二、标识符：

1、概念：不能超过30个字符，第一个字符必须为字母，不能用减号“-”，并且不区分大小写。

2、变量的命名：为提高代码可读性，建议遵从以下规则：

(1)程序变量：v_variablename

(2)程序常量：v_constantname

(3)游标变量：cursorname_cursor

(4)异常标识：e_exceptionname

(5)记录类型：recordname_record

Example:查询雇员编号为7788的员工的工资雇员日期以及工资涨500后的结果。

DECLARE
  v_name VARCHAR2(20);
  v_sal NUMBER(7,2);
  v_hiredate DATE;
  c_addsal NUMBER(7,2):=500;
  v_newsal NUMBER(7,2);
BEGIN
  SELECT ename,sal,hiredate
  INTO v_name,v_sal,v_hiredate FROM emp WHERE empno=7788;
  v_newsal := v_sal + c_addsal;
  DBMS_OUTPUT.PUT_LINE(v_name||'的工资为'||v_sal||'雇员日期为'||v_hiredate||'工资涨后为'||v_newsal);
EXCEPTION
  WHEN  NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('没有员工数据');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

上面是一个简单的匿名块，只用来执行一次，DBMS_OUTPUT.PUTLINE()用来做打印输出,NO_DATA_FOUND是没有查找到数据，SQLERRM错误信息。

三、记录型变量和引用型变量：

1、引用型变量：

指数据类型和已经定义或数据库中某一列的数据类型相同：

Example：v_name emp.ename%TYPE;这里定义的变量v_name和emp表中的ename数据类型相同。

2、记录型变量：

返回一个记录类型，和数据库表的数据类型一致。

Example:emp_record emp%rowtype;emp_record和数据库表emp的数据类型相同，所以可以emp_record.ename

下面各举一个实例：

Example1:打印输出7788的员工工资

DECLARE
  v_name emp.ename%TYPE;
  v_sal emp.sal%TYPE;
BEGIN
  SELECT ename,sal
  INTO v_name,v_sal FROM emp WHERE empno=7788;
  DBMS_OUTPUT.PUT_LINE(v_name||'的工资是'||v_sal);
END;

Example2:打印输出7788的员工工资，使用记录型变量

DECLARE
  emp_record emp%ROWTYPE;
BEGIN
  SELECT * INTO emp_record FROM emp WHERE empno=7788;
  DBMS_OUTPUT.PUT_LINE(emp_record.ename||'的工资是'||emp_record.sal);
END;

四、流程控制语句：

1、概念：分为3类

（1）条件控制语句：IF语句，CASE语句

（2）循环语句：LOOP语句

（3）顺序语句：GOTO语句、NULL语句

Example1：根据输入的值判断等级并输出

DECLARE
  v_level CHAR(1):='&LEVEL';
BEGIN
  IF v_level='A' THEN
    DBMS_OUTPUT.PUT_LINE('优秀');
  ELSIF v_level='B' THEN
    DBMS_OUTPUT.PUT_LINE('良好');
  ELSIF v_level='C' THEN
    DBMS_OUTPUT.PUT_LINE('一般');
  ELSE
    DBMS_OUTPUT.PUT_LINE('输入有误');
  END IF;
END;

这里用到了&这个符号，用来给变量进行赋值，会弹出第二个图所示的窗口让user赋值，最后给出结果，

其中用到的ELSIF 和ELSE可根据情况不要，注意这里ELSIF 不是ELSEIF.

Example2：根据输入的值判断等级并输出

DECLARE
  v_level CHAR(1):='&LEVEL';
BEGIN
  CASE v_level
    WHEN 'A' THEN
      DBMS_OUTPUT.PUT_LINE('优秀');
    WHEN 'B' THEN
      DBMS_OUTPUT.PUT_LINE('良好');
    WHEN 'C' THEN
      DBMS_OUTPUT.PUT_LINE('一般');
    ELSE
      DBMS_OUTPUT.PUT_LINE('输入有误');
  END CASE;
END;

这里用了CASE语句，他可以和IF语句相互转换，喜欢用哪个看个人爱好。

Example3:打印输出1,2,3,4,5

DECLARE
  v_num INT:=1;
BEGIN
  LOOP
    DBMS_OUTPUT.PUT_LINE(v_num);
    EXIT WHEN v_num=5;
    v_num :=v_num+1;
  END LOOP;
END;

这里用了loop的基本循环。注意一定要有退出循环的条件，不然就会无限循环变为死循环。这里用到了EXIT WHEN语句，是有条件的退出循环，

还有一个EXIT用来直接退出循环不加条件，另外在oracle 11g中有一个新特性，CONTINUE和CONTINUE WHEN ，其中CONTINUE用于跳过当

前循环，CONTINUE WEHN 用于有条件的跳过当前循环,实例如下：

Example3.2:打印输出1,2,3,5

DECLARE
  v_num INT:=0;
BEGIN
  WHILE v_num<5 LOOP
    v_num :=v_num+1;
    CONTINUE WHEN v_num=4;
    DBMS_OUTPUT.PUT_LINE(v_num);
  END LOOP;
END;

这里可以看到当数值为4是跳出了这轮循环并没有输出4.

Example4:打印输出1,2,3,4,5

DECLARE
  v_num INT:=1;
BEGIN
  WHILE v_num<=5 LOOP
    DBMS_OUTPUT.PUT_LINE(v_num);
    v_num :=v_num+1;
  END LOOP;
END;

这里用了while循环，和上个例子相比，只是将退出循环的条件从EXIT WHEN 改为了WHILE,没有特别大的不同，用法看个人喜好。

Example5:倒序打印5,4,3,2,1

BEGIN
    FOR i IN REVERSE 1..5 LOOP
        DBMS_OUTPUT.PUT_LINE(i);
    END LOOP;
END;

这里用了For循环用来控制循环次数，也是起到一个退出循环的条件，但是更加精确次数，其中的REVERSE起的是倒序的作用，也可以去掉变为正序。

Example6:根据输入的值判断等级并输出,如果输入D则什么都不做

DECLARE
  v_level CHAR(1):='&LEVEL';
BEGIN
  CASE v_level
    WHEN 'D' THEN
      GOTO the_next;
    WHEN 'A' THEN
      DBMS_OUTPUT.PUT_LINE('优秀');
    WHEN 'B' THEN
      DBMS_OUTPUT.PUT_LINE('良好');
    WHEN 'C' THEN
      DBMS_OUTPUT.PUT_LINE('一般');
   END CASE;
   <<the_next>>
   null;
END;

 这里用到了GOTO，无条件跳转到同一个程序的标签，但不建议在任何编程语言中使用它，它会使程序难以跟踪流程，难以维护。

同时用到了null语句，顾名思义，不做任何事情，其意义所在，是某些语句变得有意义，提高语句的可读性和完整性。

五、嵌套循环

1、概念：指在一个循环中嵌套另一个循环的语句，用于标记嵌套循环的叫标号，使用<<label_name>>表示。

 Example:打印输出1x1到5x5的乘法口诀吧

BEGIN
  <<outter>>
  FOR i IN 1..5 LOOP
    <<inner>>
    FOR j IN 1..5 LOOP
      CONTINUE WHEN j>i;
      DBMS_OUTPUT.PUT_LINE(i||'*'||j||'='||i*j);
    END LOOP inner;
  END LOOP outter;
END;

用了2层嵌套循环，CONTINUE WHEN 做跳出当前循环的条件，<<inner>><<outter>>分别用于结束内层循环和外层循环。

 

至此，plsql的基本内容完毕，其中还有些存储过程和触发器会在后面分节做精细说明。

　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　2018-08-15    15:26:17