SQL编程(四)

十一放假,回来继续更新........

--IF 语句
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 ='job'THEN
     UPDATE emp SET sal=v_sal +1000 WHERE dempno=&no;
  ELSE IF v_job='Man' THEN
     UPDATE emp SET sal = v_sal +2000 WHERE dempno=&no;
  ELSE
     UPDATE emp SET sal = v_sal +3000 WHERE dempno=&no;
  END IF;
END; 
               
--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=50 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=30 WHERE ename =v_ename;
 END CASE;
END;               

--LOOP循环

DECLARE
  i INT:=1;
BEGIN
  LOOP
  INSERT INTO temp values(i);
  EXIT WHEN i=10;
  i:=i+1;
  END LOOP;
END;

--WHILE 循环
DECLARE
  i INT:=1;
BEGIN
  WHILE i<=10 LOOP
  INSERT INTO temp values(i);
  i:=i+1;
  END LOOP;
END;
--FOR循环
DECLARE
  i INT:=1
BEGIN
   FOR i IN REVERSE 1..10 LOOP
     INSERT INTO temp VALUES(i);
    END LOOP;
END;

-- 嵌套循环
DECLARE
 result INT;
BEGIN
 <<outer>>
 FOR i IN 1..100 LOOP
 <<inner>>
 FOR j IN 1..100 LOOP
 result:=i*j;
 EXIT outer WHEN result =1000;
 EXIT WHEN result =500;
 END LOOP inner;
 END LOOP outer;
END;
--%ROWTYPE 定义记录变量
DECLARE
dept_record dept%ROWTYPE;
BEGIN
 dept_record.dno:='50';
 dept_record.dname:='admin';
 INSERT INTO dept values dept_record;
END;
--索引表
DECLARE
  TYPE a_table_type AS TABLE OF emp.ename%TYPE
       INDEX BY　VARCHAR2(10);
       a_table a_table_type;
BEGIN
  a_table('沈阳'):=1;
END;
--嵌套表
DECLARE
TYPE ename_table_type IS TABLE OF emp.ename%TYPE;
   ename_table ename_table_type;
BEGIN
   ename_table:=ename_table_type('1','2','3');--构造函数
SELECT ename INTO ename_table(2) FROM emp
  WHERE empno='&no';
END;
--嵌套表插入数据
BEGIN
 INSERT INTO employee VALUES(1,'fei'.7000,phone_type('123456','9877665'));
EBD;
--检索嵌套表
DECLARE
 p_table phone_table_type;
 BEGIN
   SELECT phone INTO p_table
     FROM employee WHERE id =1;
    FOR i IN 1..p_table.count loop
     END LOOP;
 END;
--VARRAY()实现多维数组
DECLARE
   TYPE al_varray_type IS VARRAY(10) OF INT;
   TYPE nal_varray_type IS VARRAY(10) OF al_varray_type;
   nvl nal_varray_type:=nal_varray_type(
   al_varray_type(58,100,102),
   al_varray_type(44,22,33)
   );
BEGIN
  FOR i IN 1..nvl.count LOOP
   FOR j IN 1..nvl(i).count LOOP
   END LOOP;
  END LOOP;
END;
--批量绑定插入
DECLARE
 TYPE id_table_type AS TABLE OF NUMBER(6)
 INDEX BY BINARY_INTEGER;
 TYPE name_table_type AS TABLE OF VARCHAR2(10)
 INDEX BY BINARY_INTEGER;
 id_table id_table_type;
 name_table name_table_type;
 start_time NUMBER(10);
 end_time NUMBER(10);
BEGIN
 FOR i IN 1..5000 LOOP
  id_table(i):=i;
  name_table(i):='name'||to_char(i);
 END LOOP;
 FORALL i IN 1..id_table.count
   INSERT INTO demo VALUES (id_table(i),name_table(i));
END;