2006年01月13日

if用法
select * from emp_t;

declare
  v_sal number(4);
begin
  select sal into v_sal
  from emp_t
  where empno=7902;
   if v_sal<2000 then
     update emp_t
     set sal=sal*1.2
     where empno=7902;
   else
     update emp_t
     set sal=sal*1.1
     where empno=7902;
   end if;
   commit;
end;
/

select * from emp_t;

declare
 v_sal number(4);
begin
 select sal into v_sal from emp_t where empno=7902;
 if v_sal<2000 then
  update emp_t set sal=sal*1.2 where empno=7902;
  update emp_t set comm=sal*0.2 where empno=7902;
 elsif v_sal<4000 then
  update emp_t set sal=sal*1.1 where empno=7902;
 elsif v_sal>5000 then
  dbms_output.put_line('the employee'' salary is to high');
 else
  dbms_output.put_line('the employee'' salary has been changed');
 end if;
 commit;
end;
/

select * from emp_t;

declare
 v_comm number;
begin
 select comm into v_comm from emp_t where empno=7902;
 if v_comm is null then
  update emp_t set comm=0 where empno=7902;
 else
  dbms_output.put_line(v_comm);
 end if;
 commit;
end;
/

select * from emp_t;

declare
begin
  if null=null then--null=null 返回null
    dbms_output.put_line('haha');
  else
    dbms_output.put_line('ok');
  end if;
end;
--ok

declare
begin
  if null is null then-- 返回true
    dbms_output.put_line('haha');
  else
    dbms_output.put_line('ok');
  end if;
end;
--haha

not null 返回null

循环LOOP

declare
 l_loops number:=0;
begin
 while l_loops<5 loop
  dbms_output.put_line('times:'||l_loops);
  l_loops:=l_loops+1;
 end loop;
end;
/

declare
begin
 for i in 1..5 loop
  dbms_output.put_line('times:'||i);
 end loop;
end;
/

select * from emp_t;
--插入5行
declare
 v_counter number(2):=1;
begin
 loop
  insert into emp_t(empno,ename,deptno)
   values(seq_empno.nextval,'lsh'||v_counter,10);
   v_counter:=v_counter+1;
   exit when v_counter>5;
  end loop;
   commit;
end;
/

游标的使用
declare
 v_emp emp_t%rowtype;
 cursor c_emp
 is
 select * from emp_t;
begin
 open c_emp;
 --loop
  fetch c_emp into v_emp;
  while c_emp%found loop
   dbms_output.put_line(v_emp.ename||' '||v_emp.JOB||chr(10));
  fetch c_emp into v_emp;
  end loop;
 close c_emp;
 commit;
end;
/

数据类型－记录

declare
 type emp_rec is record
 (empno number(4),
  ename varchar2(10),
  deptno number(2) default 10);
 
  t_emp emp_rec;
begin
 
 --t_emp.empno:=1000;--SEQ_EMPNO.NEXTVAL不可用,是不能直接赋值的
 t_emp.ename:='lsh';
 t_emp.deptno:=20;
 
 insert into emp_t(empno,ename,deptno) values(SEQ_EMPNO.NEXTVAL,t_emp.ename,t_emp.deptno);
 commit;
end;
/

数据类型－表

declare
 type tp is table of number index by binary_integer;
 tp1 tp;
 v_record number;
begin
 for i in 1..10 loop
  tp1(i):=i+1;
 end loop;
 dbms_output.put_line(tp1.next(7));
 tp1.delete(8);
 dbms_output.put_line(tp1.next(7));
 tp1.delete(10);
 v_record:=tp1.count;
 dbms_output.put_line(v_record);
 tp1.delete(2,5);--删除2-5行
 v_record:=tp1.count;
 dbms_output.put_line(v_record);
 dbms_output.put_line(tp1.first);
 dbms_output.put_line(tp1.next(2));
 dbms_output.put_line(tp1.prior(2));
 dbms_output.put_line(tp1.last);
end;
/

declare
type dept_table_type is table of dept_t%rowtype
  index by binary_integer;
 dept_table dept_table_type;
begin
 select * into dept_table(1) from dept_t where deptno=20;
 dbms_output.put_line(dept_table(1).deptno);
 dbms_output.put_line('  '||dept_table(1).dname);
 dbms_output.put_line('  '||dept_table(1).loc);
end;
/
--错误的代码，表类型跟表有什么区别？下面的语句为什么不对？
declare
type dept_table_type is table of dept_t%rowtype
  index by binary_integer;
 dept_table dept_table_type;
begin
 select * into dept_table from dept_t;
 dbms_output.put_line(dept_table(1).deptno);
 dbms_output.put_line('  '||dept_table(1).dname);
 dbms_output.put_line('  '||dept_table(1).loc);
end;
/