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;
