oracle数据库(六)

PLSQL

PL/SQL是一种块结构的语言，这意味着PL/SQL程序被划分和编写代码的逻辑块。每块由三个子部分组成：

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

DECLARE
   message  varchar2(20):= 'Hello, World!';
BEGIN
   dbms_output.put_line(message);
END;

字符集

1.算数运算符

运算符	描述
+	加
-	减
*	乘
/	除
**	乘方

2.关系运算符

运算符	描述
=	等于
!= <> ~=	不等于
>	大于
<	小于
=	大于等于
<=	小于等于
BETWEEN AND	检测两值之间的内容
IN	匹配列表中的值
LIKE	模糊查询
IS NULL	非空值判断

3.逻辑运算符

运算符	描述
AND	两个表达式同时为真结果为真
OR	有一个为真则为真
NOT	取相反的逻辑值

条件结构

IF - THEN 语句

DECLARE
   a number(2) := 10;
BEGIN
   --判断条件是否成立
   IF( a < 20 ) THEN
      -- 成立则输出
      dbms_output.put_line('a is less than 20 ' );
   END IF;
   dbms_output.put_line('value of a is : ' || a);
END;

IF-THEN-ELSE语句

DECLARE
   a number(3) := 10;
BEGIN
   IF( a < 20 ) THEN
      dbms_output.put_line('a is less than 20 ' );
   ELSE
      dbms_output.put_line('a is not less than 20 ' );
   END IF;
   dbms_output.put_line('value of a is : ' || a);
END;

IF-THEN-ELSIF-THEN-ELSE语句

DECLARE
   a number(3) := 20;
BEGIN
   IF ( a = 10 ) THEN
      dbms_output.put_line('Value of a is 10' );
   ELSIF ( a = 20 ) THEN
      dbms_output.put_line('Value of a is 20' );
   ELSIF ( a = 30 ) THEN
      dbms_output.put_line('Value of a is 30' );
   ELSE
       dbms_output.put_line('None of the values is matching');
   END IF;
   dbms_output.put_line('Exact value of a is: '|| a ); 
END;

循环结构

LOOP-EXIT-END语句

DECLARE
   x number := 10;
BEGIN
   LOOP
      dbms_output.put_line(x);
      x := x + 10;
      IF x >=20 THEN
         exit; --满足条件退出循环
      END IF;
   END LOOP;
   dbms_output.put_line('After Exit x is: ' || x);
END;

LOOP-EXIT-WHEN-END语句

DECLARE
   x number := 10;
BEGIN
   LOOP
      dbms_output.put_line(x);
      x := x + 10;
      exit WHEN x > 50;
   END LOOP;
   dbms_output.put_line('After Exit x is: ' || x);
END;

WHILE-LOOP-END语句

DECLARE
   i number(3);
   j number(3);
BEGIN
   i := 2;
   LOOP
      j:= 2;
      LOOP
         exit WHEN ((mod(i, j) = 0) or (j = i));
         j := j +1;
      END LOOP;
   IF (j = i ) THEN
      dbms_output.put_line(i || ' is prime');
   END IF;
   i := i + 1;
   exit WHEN i = 50;
   END LOOP;
END;

FOR-IN-LOOP-END语句

DECLARE
   a number(2);
BEGIN
   FOR a in 10 .. 20 LOOP
       dbms_output.put_line('value of a: ' || a);
  END LOOP;
END;

选择和跳转

CASE语句

DECLARE
   grade char(1) := 'B';
BEGIN
   CASE grade
      when 'A' then dbms_output.put_line('Excellent');
      when 'B' then dbms_output.put_line('Very good');
      when 'C' then dbms_output.put_line('Well done');
      when 'D' then dbms_output.put_line('You passed');
      when 'F' then dbms_output.put_line('Better try again');
      else dbms_output.put_line('No such grade');
   END CASE;
END;

goto语句

DECLARE
   a number(2) := 10;
BEGIN
   <<loopstart>> --标号
   WHILE a < 20 LOOP
      dbms_output.put_line ('value of a: ' || a);
      a := a + 1;
      IF a = 15 THEN
         a := a + 1;
         GOTO loopstart;--跳转
      END IF;
   END LOOP;
END;

异常

DECLARE
   c_id customers.id%type := 8;
   c_name  customers.name%type;
   c_addr customers.address%type;
BEGIN
   SELECT  name, address INTO  c_name, c_addr
   FROM customers
   WHERE id = c_id;

   DBMS_OUTPUT.PUT_LINE ('Name: '||  c_name);
   DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr);
EXCEPTION
   WHEN no_data_found THEN
      dbms_output.put_line('No such customer!');
   WHEN others THEN
      dbms_output.put_line('Error!');
END;

.