学习笔记：13编写控制结构

13-1:简单的条件判断
	DECLARE
	  v_sal NUMBER(6,2);
	BEGIN
	  SELECT sal INTO v_sal FROM emp
	  WHERE lower(ename)=lower('&&name');
	  IF v_sal<2000 THEN
	    UPDATE emp SET sal=v_sal+200
	    WHERE lower(ename)=lower('&name');
	  END IF;
	END;
	/
13-2:二重条件分支
	DECLARE
	  v_comm NUMBER(6,2);
	BEGIN
	  SELECT comm INTO v_comm FROM emp
	  WHERE empno=&&no;
	  IF v_comm<>0 THEN
	    UPDATE emp SET comm=v_comm+100
	    WHERE empno=&no;
	  ELSE
	    UPDATE emp SET comm=200
	    WHERE empno=&no;
	  END IF;
	END;
	/
13-3:多重条件分支
	DECLARE
	  v_job VARCHAR2(10);
	  v_sal NUMBER(6,2);
	BEGIN
	  SELECT job,sal INTO v_job,v_sal
	  FROM emp WHERE empno=&&no;
	  IF v_job='PRESIDENT' THEN
	    UPDATE emp SET sal=v_sal+1000 WHERE empno=&no;
	  ELSIF v_job='MANAGER' THEN
	    UPDATE emp SET sal=v_sal+500 WHERE empno=&no;
	  ELSE
	    UPDATE emp SET sal=v_sal+200 WHERE empno=&no;
	  END IF;
	END;
	/
13-4:在case语句中使用单一选择符进行等值比较
	DECLARE
	  v_deptno emp.deptno%TYPE;
	BEGIN
	  v_deptno:=&no;
	  CASE v_deptno
	    WHEN 10 THEN
	      UPDATE emp SET comm=100 WHERE deptno=v_deptno;
	    WHEN 20 THEN
	      UPDATE emp SET comm=80 WHERE deptno=v_deptno;
	    WHEN 30 THEN
	      UPDATE emp SET comm=50 WHERE deptno=v_deptno;
	   ELSE
	     dbms_output.put_line('不存在该部门');
	  END CASE;
	END;
	/
13-5:在case语句中使用多种条件比较
	DECLARE
	  v_sal emp.sal%TYPE;
	  v_ename emp.ename%TYPE;
	BEGIN
	  SELECT ename,sal INTO v_ename,v_sal
	  FROM emp WHERE empno=&no;
	  CASE
	    WHEN v_sal<1000 THEN
	       UPDATE emp SET comm=100 WHERE ename=v_ename;
	   WHEN v_sal<2000 THEN
	       UPDATE emp SET comm=80 WHERE ename=v_ename;
	   WHEN v_sal<6000 THEN
	       UPDATE emp SET comm=50 WHERE ename=v_ename;
	  END CASE;
	END;
	/
13-6:基本循环
	CREATE TABLE temp(cola INT);
	DECLARE
	  i INT:=1;
	BEGIN
	  LOOP
	    INSERT INTO temp VALUES(i);
	    EXIT WHEN i=10;
	    i:=i+1;
	  END LOOP;
	END;
	/
13-7:while循环
	DECLARE
	  i INT :=1;
	BEGIN
	  WHILE i<=10 LOOP
	    INSERT INTO temp VALUES(i);
	    i:=i+1;
	  END LOOP;
	END;
	/
13-8:for循环
	BEGIN
	  FOR i IN REVERSE 1..10 LOOP
	    INSERT INTO temp VALUES(i);
	  END LOOP;
	END;
	/
13-9:嵌套循环和标号
	DECLARE
	  result INT;
	BEGIN
	  <<outer>>
	  FOR i IN 1..100 LOOP
	    <<inter>>
	    FOR j IN 1..100 LOOP
	      result:=i*j;
	      EXIT outer WHEN result=1000;
	      EXIT WHEN result=500;
	    END LOOP inner;
	    dbms_output.put_line(result);
	  END LOOP outer;
	  dbms_output.put_line(result);
	END;
	/
13-10:exit和exit when语句
DECLARE
  i INT:=1;
BEGIN
  LOOP
    DBMS_OUTPUT.PUT_LINE(i);
    EXIT WHEN i=5;
    i:=i+1;
  END LOOP;
END;
/

13-11:countinue和continue　when语句
DECLARE
  i INT:=0;
BEGIN
  LOOP
    i:=i+1;
    CONTINUE WHEN i=3;
    DBMS_OUTPUT.PUT_LINE(i);
    EXIT WHEN i=5;
  END LOOP;
END;
/

13-12:goto
	DECLARE
	  i INT:=1;
	BEGIN
	  LOOP
	    INSERT INTO temp VALUES(i);
	    IF i=10 THEN
	      GOTO end_loop;
	    END IF;
	    i:=i+1;
	  END LOOP;
	<<end_loop>>
	  dbms_output.put_line('循环结束');
	END;
	/
13-13:null
	DECLARE
	  v_sal emp.sal%TYPE;
	  v_ename emp.ename%TYPE;
	BEGIN
	  SELECT ename,sal INTO v_ename,v_sal
	  FROM emp WHERE empno=&no;
	  IF v_sal<3000 THEN
	    UPDATE emp SET comm=sal*0.1 WHERE ename=v_ename;
	  ELSE
	     NULL;
	  END IF;
	END;
	/