欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

PL SQL基本内容(原创)

程序员文章站 2022-05-31 13:51:44
本节介绍PL SQL的基本内容 本节所举示例数据来源oracle用户scott下的emp表和dept表,数据如下: 一、plsql简介: 1、概念:procedural language,过程化sql语言,是面向过程的语言,在普通sql的基础上增加了编程语言的特点。PL/SQL的基本单元是块。 2、 ......

本节介绍PL SQL的基本内容

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

PL SQL基本内容(原创)PL SQL基本内容(原创)

一、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;

PL SQL基本内容(原创)

上面是一个简单的匿名块,只用来执行一次,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;

PL SQL基本内容(原创)

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;

PL SQL基本内容(原创)

四、流程控制语句:

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;

PL SQL基本内容(原创)

PL SQL基本内容(原创)

这里用到了&这个符号,用来给变量进行赋值,会弹出第二个图所示的窗口让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;

PL SQL基本内容(原创)

PL SQL基本内容(原创)

这里用了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;

PL SQL基本内容(原创)

这里用了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;

PL SQL基本内容(原创)

这里可以看到当数值为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;

PL SQL基本内容(原创)

这里用了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;

PL SQL基本内容(原创)

这里用了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;

PL SQL基本内容(原创)

PL SQL基本内容(原创)

 这里用到了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;

PL SQL基本内容(原创)

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

 

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

                                                2018-08-15    15:26:17