一、 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: DECLARE2: v_sname VARCHAR2(20);
3: BEGIN4: SELECT sname5: INTO v_sname6: FROM student7: WHERE sno = '001';8: dbms_output.put_line('学号为001的学生的姓名: '||v_sname);9: EXCEPTION10: WHEN NO_DATA_FOUND THEN11: dbms_output.put_line('ERR:Invalid Student NO 001');12: WHEN OTHERS THEN13: dbms_output.put_line('ERR:An error occurred with info :'||TO_CHAR(SQLCODE)||' '||SQLERRM);14: END;4、嵌套异常
1: DECLARE2: v_sname VARCHAR2(20);
3: BEGIN4: BEGIN5: SELECT sname6: INTO v_sname7: FROM student8: WHERE sno = '001';9: dbms_output.put_line('The lowest Student NO is: 001 '||v_sname);10: EXCEPTION WHEN NO_DATA_FOUND THEN11: INSERT INTO student(sno,sname) VALUES ('001','Smith');12: COMMIT;13: END;14: BEGIN15: SELECT sname16: INTO v_sname17: FROM student18: WHERE sno='010';19: dbms_output.put_line('The highest Student NO is: Code 010 '||v_sname);20: EXCEPTION WHEN NO_DATA_FOUND THEN21: dbms_output.put_line('ERR:Invalid Data for Student NO 010');22: END;23: EXCEPTION24: WHEN OTHERS THEN25: 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: DECLARE2: exp_no001 EXCEPTION; -- a user-defined exception3: v_cnt NUMBER;
4: BEGIN5: SELECT COUNT(*)6: INTO v_cnt7: FROM sc8: WHERE sno='001';9: IF (v_cnt=0)THEN10: --explicitly raising the user-defined exception11: RAISE exp_no001;
12: END IF;13: EXCEPTION14: --handling the raised user-defined exception15: WHEN exp_no001 THEN16: dbms_output.put_line('There are no Student NO 001');17: WHEN OTHERS THEN18: dbms_output.pu t_line('ERR:An error occurred with info :'||TO_CHAR(SQLCODE)||' '||SQLERRM);19: END;