PL/SQL基本语法(ORACLE)

一、 if…elsif….end if

例子：

   1: DECLARE

   2:   a number :=50;

   3:   b number :=-20;

   4: BEGIN

   5:   IF (a>b)THEN

   6:     dbms_output.put_line('A is greater than B');

   7:   ELSIF (a<b)THEN

   8:     dbms_output.put_line('A is < than B');

   9:   ELSE

  10:     dbms_output.put_line('A is equal to B');

  11:   END IF;

  12: END;

 

二、循环

loop…exit when…end loop

   1: DECLARE

   2:   line_length NUMBER :=50;

   3:   seperator VARCHAR2(1):='=';

   4:   actual_line VARCHAR2(150);

R2(150);

   5:   i NUMBER :=1;

   6: BEGIN

   7:   LOOP

   8:     actual_line :=actual_line ||seperator;

   9:     EXIT WHEN i =line_length;

  10:     i:=i +1;

  11:   END LOOP;

  12:   DBMS_OUTPUT.PUT_LINE(actual_line);

  13: END;

 

for…loop

   1: DECLARE

   2:   line_length NUMBER :=50;

   3:   seperator VARCHAR2(1):='=';

   4:   actual_line VARCHAR2(150);

   5: BEGIN

   6:   FOR idx in 1..line_length LOOP

   7:     actual_line :=actual_line ||seperator;

   8:   END LOOP;

   9:   DBMS_OUTPUT.PUT_LINE(actual_line);

  10: END;

while…loop

   1: DECLARE

   2:   line_length NUMBER :=50;

   3:   seperator VARCHAR2(1):='=';

   4:   actual_line VARCHAR2(150);

   5:   idx NUMBER :=1;

   6: BEGIN

   7:   WHILE (idx<=line_length)LOOP

   8:     actual_line :=actual_line ||seperator;

   9:     idx :=idx +1 ;

  10:   END LOOP;

  11:   DBMS_OUTPUT.PUT_LINE(actual_line);

  12: END;

 

三、case

例子：

   1: declare

   2:   a number :=20;

   3:   b number :=-40;

   4:   string varchar2(50);

   5: begin

   6:   string :=case

   7:              when (a>b)then 'A is greater than B'

   8:              when (a<b)then 'A is less than B'

   9:            else

  10:              'A is equal to B'

  11:            end;

  12:   dbms_output.put_line(string);

  13: end;

 

四、块的嵌套

 

说明：块的嵌套主要用于截取异常。

   1: DECLARE

   2:   v_item_code VARCHAR2(6);

   3:   v_item_descr VARCHAR2(20);

   4:   v_num NUMBER(1);

   5: BEGIN

   6:   v_item_code :='ITM101';

   7:   v_item_descr :='Spare parts';

   8:   BEGIN

   9:     SELECT 1

  10:     INTO v_num

  11:     FROM items_tab

  12:     WHERE item_code =v_item_code;

  13:   EXCEPTION

  14:     WHEN NO_DATA_FOUND THEN

  15:       v_num :=0;

  16:     WHEN OTHERS THEN

  17:       dbms_output.put_line('Error in SELECT:'||SQLERRM);

  18:       RETURN;

  19:   END;

  20:   IF (v_num =0)THEN

  21:     INSERT INTO items_tab VALUES (v_item_code,v_item_descr);

  22:     Commit;

  23:   END IF;

  24:   dbms_output.put_line('Successful Completion');

  25: EXCEPTION WHEN OTHERS THEN

  26:   dbms_output.put_line(SQLERRM);

  27: END;

 

五、异常

 

异常的几种情况

1、EXCEPTION WHEN NO_DATA_FOUND THEN
2、EXCEPTION WHEN OTHERS THEN
3、使用SQLERRM（系统错误提示）和SQLCODE（系统错误代码）

   1: DECLARE

   2:   v_sname VARCHAR2(20);

   3: BEGIN

   4:   SELECT sname

   5:   INTO v_sname

   6:   FROM student

   7:   WHERE sno = '001';

   8:   dbms_output.put_line('学号为001的学生的姓名: '||v_sname);

   9: EXCEPTION

  10:   WHEN NO_DATA_FOUND THEN

  11:       dbms_output.put_line('ERR:Invalid Student NO 001');

  12:   WHEN OTHERS THEN

  13:     dbms_output.put_line('ERR:An error occurred with info :'||TO_CHAR(SQLCODE)||' '||SQLERRM);

  14: END;

4、嵌套异常

   1: DECLARE

   2:   v_sname VARCHAR2(20);

   3: BEGIN

   4:   BEGIN

   5:     SELECT sname

   6:     INTO v_sname

   7:     FROM student

   8:     WHERE sno = '001';

   9:     dbms_output.put_line('The lowest Student NO is: 001 '||v_sname);

  10:   EXCEPTION WHEN NO_DATA_FOUND THEN

  11:     INSERT INTO student(sno,sname) VALUES ('001','Smith');

  12:     COMMIT;

  13:   END;

  14:   BEGIN

  15:     SELECT sname

  16:     INTO v_sname

  17:     FROM student

  18:     WHERE sno='010';

  19:     dbms_output.put_line('The highest Student NO is: Code 010 '||v_sname);

  20:   EXCEPTION WHEN NO_DATA_FOUND THEN

  21:     dbms_output.put_line('ERR:Invalid Data for Student NO 010');

  22:   END;

  23: EXCEPTION

  24:   WHEN OTHERS THEN

  25:     dbms_output.put_line('ERR:An error occurred with info :'||TO_CHAR(SQLCODE)||' '||SQLERRM);

  26: END;

5、捕获Declare（声明变量）中的异常

EXCEPTION
  WHEN VALUE_ERROR THEN
    dbms_output.put_line('Value error occurred');
END;

6、自定义异常

   1: DECLARE

   2:   exp_no001 EXCEPTION;   -- a user-defined exception

   3:   v_cnt NUMBER;

   4: BEGIN

   5:   SELECT COUNT(*)

   6:   INTO v_cnt

   7:   FROM sc

   8:   WHERE sno='001';

   9:   IF (v_cnt=0)THEN

  10:     --explicitly raising the user-defined exception

  11:     RAISE exp_no001;

  12:   END IF;

  13: EXCEPTION

  14:   --handling the raised user-defined exception

  15:   WHEN exp_no001 THEN

  16:     dbms_output.put_line('There are no Student NO 001');

  17:   WHEN OTHERS THEN

  18:     dbms_output.pu t_line('ERR:An error occurred with info :'||TO_CHAR(SQLCODE)||' '||SQLERRM);

  19: END;