--demo1SELECT   IF t1.user_id='1' THEN '2012'   ELSIF t1.user_id='2' THEN '2013'  ELSE '2015'  END IFFROM ts_user t1;--demo2SELECT   CASE t1.user_id   WHEN '1' THEN '2012'   WHEN '2' THEN '2013'  WHEN '3' THEN '2014'  ELSE '2015'  END CASEFROM ts_user t1;--demo3SELECT DECODE(t1.user_id,'1002','2012','1003','2013','1004','2013') FROM ts_user t1 WHERE user_id IN('1002','1003','1004');--demo4SELECT   CASE    WHEN t1.user_id='1' THEN '2012'   WHEN t1.user_id='2' THEN '2013'  WHEN t1.user_id='3' THEN '2014'  ELSE '2015'  END CASEFROM ts_user t1;--demo5DECLARE  v_i NUMBER:=0;BEGIN  LOOP    v_i:=v_i+1;    dbms_output.put_line(v_i);    EXIT WHEN v_i=10;  END LOOP;END;--demo6DECLARE  v_i NUMBER:=0;BEGIN  WHILE v_i<=10 LOOP    dbms_output.put_line(v_i);    v_i:=v_i+1;  END LOOP;END;--demo7BEGIN  FOR i IN REVERSE 1..10 LOOP    dbms_output.put_line(i);  END LOOP;END;/--demo8BEGIN  FOR i IN 1..10 LOOP    dbms_output.put_line(i);  END LOOP;END;/--demo9BEGIN  FOR i IN 1..3 LOOP    dbms_output.put('i:'||i);    FOR j IN 1..2 LOOP      dbms_output.put(' j:'||j);    END LOOP;    dbms_output.put_line('');  END LOOP;END;/--demo10BEGIN  <
     
      >  FOR i IN 1..3 LOOP    dbms_output.put('i:'||i);    <
      
       >    FOR j IN 1..2 LOOP      dbms_output.put(' j:'||j);      EXIT outer WHEN i=2;      EXIT inner WHEN j=1;    END LOOP;    dbms_output.put_line('');  END LOOP;END;/--demo11SELECT   CASE    WHEN t1.user_id='1' THEN '2012'   WHEN t1.user_id='2' THEN '2013'  WHEN t1.user_id='3' THEN '2014'  ELSE NULL  END CASEFROM ts_user t1;EXTENDTRIMDELETEEXISTSCOUNTFIRSTLASTPRIORNEXT:=NULLSETMULTISET UNIONMULTISET UNION DISTINCTMULTISET INTERSECTMULTISET EXCEPTIS NULLIS EMPTY=CARDINALITYMEMBER OFSUBMULTISET OFIS A SETFORALLFORALL i IN DICICESFORALL i IN VALUESBULK COLLECT--SELECT INTO,FETCH INTO,DML返回子句隐含游标SQL%FOUNDSQL%NOTFOUNDSQL%ISOPENSQL%ROWCOUNT显示游标游标DECLARECURSOR emp_cursor IS SELECT ename FROM emp WHERE eno:=&no;v_ename ename%TYPE;BEGINOPEN CURSOR;LOOP  FETCH emp_cursor INTO v_ename  dbms...END LOOP;CLOSE CURSOR;END;DECLARECURSOR emp_cursor IS SELECT ename FROM emp WHERE eno:=&no;TYPE ename_table_type IS TABLE OF emp.ename%TYPE;ename_table ename_table_type;BEGINOPEN CURSOR;  FETCH emp_cursor BULK COLLECT INTO ename_table;CLOSE CURSOR;END;FETCH .. BULK COLLECT INTO ... LIMIT ..;CURSOR emp_cursor IS SELECT ename,sal FROM emp;emp_record emp_cursor;BEGIN    OPEN emp_cursor;    LOOP      FETCH emp_cursor INTO emp_record;      EXIT WHEN emp_cursor%NOTFOUND;  END LOOP;END;CURSOR emp_cursor(no NUMBER) IS ...CURSOR emp_cursor(no NUMBER) IS ... FOR UPDATEUPDATE ... WHERE CURRENT OF emp_cursor;FOR emp_record INTO emp_cursor LOOP...END LOOP;TYPE emp_cursor_type IS REF CURSOR RETURN emp_table_type;emp_cursor emp_cursor_type;BEGIN    OPEN emp_cursor FOR SELECT ename,sal FROM ... WHERE ...;END;CURSOR ... IS(SELECT dname,CURSOR(SELECT ename,sal FROM emp WHERE deptno=a.deptno) FROM emp WHERE ...);e_integrity EXCEPTION;PRAGMA EXCEPTION_INIT(e_integrity,-2019);WHEN e_integrity THENIF SQL%NOTFOUND THEN  RAISE e_integrity;END IF;RAISE_APPLICATION_ERROR(-20001,'雇员无补助');ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';ALTER SYSTEM SET PLSQL_WARNINGS='ENABLE:ALL';SET SERVEROUTPUT ON;