plsql开发子程序与包之前的简单回顾

--demo1
SELECT 
  IF t1.user_id='1' THEN '2012' 
  ELSIF t1.user_id='2' THEN '2013'
  ELSE '2015'
  END IF
FROM ts_user t1;
--demo2
SELECT 
  CASE t1.user_id 
  WHEN '1' THEN '2012' 
  WHEN '2' THEN '2013'
  WHEN '3' THEN '2014'
  ELSE '2015'
  END CASE
FROM ts_user t1;
--demo3
SELECT DECODE(t1.user_id,'1002','2012','1003','2013','1004','2013') 
FROM ts_user t1 
WHERE user_id IN('1002','1003','1004');
--demo4
SELECT 
  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 CASE
FROM ts_user t1;
--demo5
DECLARE
  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;
--demo6
DECLARE
  v_i NUMBER:=0;
BEGIN
  WHILE v_i<=10 LOOP
    dbms_output.put_line(v_i);
    v_i:=v_i+1;
  END LOOP;
END;
--demo7
BEGIN
  FOR i IN REVERSE 1..10 LOOP
    dbms_output.put_line(i);
  END LOOP;
END;
/
--demo8
BEGIN
  FOR i IN 1..10 LOOP
    dbms_output.put_line(i);
  END LOOP;
END;
/
--demo9
BEGIN
  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;
/
--demo10
BEGIN
  <<outer>>
  FOR i IN 1..3 LOOP
    dbms_output.put('i:'||i);
    <<inner>>
    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;
/
--demo11
SELECT 
  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 CASE
FROM ts_user t1;

EXTEND
TRIM
DELETE
EXISTS
COUNT
FIRST
LAST
PRIOR
NEXT

:=
NULL

SET
MULTISET UNION
MULTISET UNION DISTINCT
MULTISET INTERSECT
MULTISET EXCEPT

IS NULL
IS EMPTY
=
CARDINALITY
MEMBER OF
SUBMULTISET OF
IS A SET

FORALL
FORALL i IN DICICES
FORALL i IN VALUES
BULK COLLECT--SELECT INTO,FETCH INTO,DML返回子句

隐含游标
SQL%FOUND
SQL%NOTFOUND
SQL%ISOPEN
SQL%ROWCOUNT

显示游标
游标
DECLARE
CURSOR emp_cursor IS SELECT ename FROM emp WHERE eno:=&no;
v_ename ename%TYPE;
BEGIN
OPEN CURSOR;
LOOP
  FETCH emp_cursor INTO v_ename
  dbms...
END LOOP;
CLOSE CURSOR;
END;

DECLARE
CURSOR 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;
BEGIN
OPEN 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 UPDATE
UPDATE ... 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 THEN

IF 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;