PL/SQL块中只能直接嵌入SELECT,DML(INSERT,UPDATE,DELETE)

以及事务控制语句(COMMIT,ROLLBACK,SAVEPOINT),

而不能直接嵌入DDL语句(CREATE,ALTER,DROP)和DCL语句(GRANT,REVOKE)

 

1.检索单行数据

  1.1使用标量变量接受数据

  1. v_ename emp.ename%type; 
  2.  
  3.  v_sal   emp.sal%type; 
  4.  
  5.  select ename,sal into v_ename,v_sal from emp where empno=&no;

  1.2使用记录变量接受数据

  1. type emp_record_type is record( 
  2.  
  3.  ename emp.ename%type,sal emp.sal%type); 
  4.  
  5.  emp_record emp_record_type; 
  6.  
  7.  select ename,sal into emp_record from emp where empno=&no;

  1.3嵌入SELECT语句注意事项:

  使用SELECT INTO语句时,必须要返回一条数据,并且只能返回一条数据

  no_date_found:

  select into没有返回数据

  too_many_rows:

  select into返回多条数据

  where子句使用注意事项:

  使用的变量名不能与列名相同,否则触发TOO_MANY_ROWS例外.

2.操纵数据

  2.1使用VALUES子句插入数据

  1. v_deptno dept.deptno%type; 
  2.  
  3.  v_dname dept.dname%type; 
  4.  
  5.  v_deptno:=no; 
  6.  
  7.  v_dname:='&name'; 
  8.  
  9.  insert into dept (deptno,dname) values(v_deptno,v_dname);

  2.2使用子查询插入数据

  1. v_deptno emp.deptno%type:=&no; 
  2.  
  3.  insert into employee select * from emp where deptno=v_deptno;

  2.3更新数据

  使用表达式更新列值

  1. v_deptno dept.deptno%type:=no; 
  2.  
  3.   v_loc dept.loc%type:='&loc'; 
  4.  
  5.   update dept set loc=v_loc where deptno=v_deptno;

  2.4使用子查询更新列值

  1. v_ename emp.ename%type:='&name'; 
  2.  
  3. update emp set (sal,comm) = (select sal,comm from emp where ename=v_ename) where job = (select job from emp where ename=v_ename) ;

  2.5删除数据

  使用变量删除数据

  1. v_deptno dept.deptno%type:=&no; 
  2.  
  3. delete from dept where deptno=v_deptno; 

  2.6使用子查询删除数据

  1. v_ename emp.ename%type:='&name'; 
  2. delete from emp where deptno=(select deptno from emp where ename=v_ename);

3.SQL游标

  游标是指向上下文区的指针,包括隐含游标(SQL游标)和显式游标两种类型

  SQL游标用于处理SELECT INTO ,INSERT,UPDATE以及DELETE语句.

  显式游标用于处理多行的SELECT语句

  SQL游标包括:SQL%FOUND,SQL%NOTFOUND,SQL%ROWCOUNT,SQL%ISOPEN等四种属性

  3.1 SQL%ISOPEN:执行时,会隐含的打开和关闭游标.因此该属性的值永远都是FALSE

 

  3.2 SQL%FOUND:用于确定SQL语句执行是否成功.当SQL有作用行时,为TRUE,否则为FALSE

  1.  v_deptno emp.deptno%type:=&no; 
  2.  
  3. update emp set salsal=sal*1.1 where deptno=v_deptno; 
  4.  
  5. if sql%found  
  6.        then dbms_output.put_line('执行成功');  
  7. else  
  8.        dbms_output.putline('失败'); 
  9. endif 

  3.3 sql%notfound:确定SQL语句执行是否成功,当SQL有作用行时,为false,否则为true

 

  3.4 sql%rowcount:返回SQL语句所作用的总计行数

  1. v_deptno emp.deptno%type:=&no; 
  2.  
  3. update emp set salsal=sal*1.1 where deptno=v_deptno; 
  4.  
  5. dbms_output.put_line('修改了'||sql%rowcount||'行'); 

4.事务控制语句(TCL)

  事务控制语句包括COMMIT,ROLLBACK以及SAVEPOINT等三种语句

  1. v_sal emp.sal%type:=&salary; 
  2.  
  3. v_ename emp.ename%type:='&name'; 
  4.  
  5. update emp set sal=v_sal where ename=v_ename; 
  6.  
  7. commit; 
  8.  
  9. exception 
  10.  
  11.     when others then 
  12.  
  13.     rollback; 

  1. insert into temp values(1); 
  2.  
  3.   savepoint a1; 
  4.  
  5.   insert into temp values(2); 
  6.  
  7.   savepoint a2; 
  8.  
  9.   insert into temp values(3); 
  10.  
  11.   savepoint a3; 
  12.  
  13.   rollback to a2; 
  14.  
  15.   commit;
5.控制结构
  条件分支语句
  5.1简单条件判断
  1. v_sal number(6,2); 
  2.  
  3. select sal into v_sal from emp where 
  4.  
  5. lower(ename)=lowe('&&name'); 
  6.  
  7. if v_sal<2000 then update emp set 
  8.  
  9. sal=v_sal+200 where lower(ename)=lower('&name') 
  10.  
  11. end if;


  5.2二重条件分支


  1. v_comm number(6,2); 
  2.  
  3. select comm into v_comm from emp where empno=&&no; 
  4.  
  5. if v_comm<>0 then update emp set comm=v_comm+100 where empno=&no; 
  6.  
  7. else update emp set comm=200 where empno=&no; 
  8.  
  9. end if 


  5.3多重条件分支


  1. v_job varchar2(10); 
  2.  
  3. v_sal number(6,2); 
  4.  
  5. select job,sal into v_job,v_sal from emp where empno=&&no; 
  6.  
  7. if v_job='president' then  
  8.  
  9. update emp set sal=v_sal+1000 where empno=&no; 
  10.  
  11. else if v_job='manager' then  
  12.  
  13. update emp setsal=v_sal+500 where empno=&no; 
  14.  
  15. else  
  16.  
  17. update emp set sal=v_sal+200 where empno=&no; 
  18.  
  19. end if; 


  5.4 CASE语句:


  在CASE语句中使用单一选择符进行等值比较


  1. declare 
  2.  
  3. v_deptno emp deptno%type; 
  4.  
  5. begin 
  6.  
  7. v_deptno:=&no; 
  8.  
  9. case v_deptno 
  10.  
  11. when 10 then update emp set comm=100 where deptno=v_deptno; 
  12.  
  13. when 20 then update emp set comm=80  where deptno=v_deptno; 
  14.  
  15. when 30 then update emp set comm=50  where deptno=v_deptno; 
  16.  
  17. else 
  18.  
  19.     dbms_output.put_line("不存在'); 
  20.  
  21. end case; 
  22.  
  23. end; 


   5.5 在CASE语句中使用多种条件比较


  1. declare 
  2.  
  3. v_sal emp.sal%type; 
  4.  
  5. v_ename emp.ename%type; 
  6.  
  7. begin 
  8.  
  9. select ename,sal into v_ename,v_sal from emp where empno=&no; 
  10.  
  11. case 
  12.  
  13. when v_sal<1000 then update emp set comm=100 where ename=v_ename; 
  14.  
  15. when v_sal<2000 then update emp set comm=80  where ename=v_ename; 
  16.  
  17. when v_sal<6000 tehn update emp set comm=50  where ename=v_ename; 
  18.  
  19. end case; 
  20.  
  21. end; 


   5.6循环语句


   有基本循环,WHILE循环,FOR循环


   基本循环:一定要包含EXIT语句,定义循环控制变量


  1. create table temp(cola int); 
  2.  
  3. eclare 
  4.  
  5.   i int:=1; 
  6.  
  7.   begin 
  8.  
  9.     loop 
  10.  
  11.       insert into temp values(i); 
  12.  
  13.       exit when i=10; 
  14.  
  15.        ii:=i+1; 
  16.  
  17.     end loop; 
  18.  
  19.  end; 


  5.7 WHILE循环:定义循环控制变量,并在循环体内改变循环控制变量的值


  1. declare 
  2.  
  3.    i int:=1; 
  4.  
  5.  begin  
  6.  
  7.    while i<=10 loop 
  8.  
  9.      insert into temp values(i); 
  10.  
  11.      ii:=i+1; 
  12.  
  13.    end loop; 
  14.  
  15.  end; 


  5.8 for循环:使用FOR循环时,ORACLE会隐含定义循环控制变量.


  1. for counter in[reverse] 
  2.  
  3.   lower_bound..upper_bound loop 
  4.  
  5.     statement1; 
  6.  
  7.     statement2; 
  8.  
  9.     ....... 
  10.  
  11.   end loop; 


  5.9 counter是循环控制变量,并且该变量由ORACLE隐含定义,不需要显示定义;


      lower_bound和upper_bound分别对应循环控制变量的上下界值.默认情况下,


      FOR循环,每次会自动增一,指定REVERSE选项时,每次循环控制变量会减一


  1. begin 
  2.  
  3.     for i in reverse 1..10 loop 
  4.  
  5.       insert into temp values(i); 
  6.  
  7.     end loop; 
  8.  
  9.   end; 


  5.10嵌套循环和标号:通过在嵌套循环中使用标号,可以区分内层循环和外层循环


      ,并且可以在内层循环中直接退出外层循环


  1. declare 
  2.  
  3.    result int; 
  4.  
  5.    begin 
  6.  
  7.    <<outer>> 
  8.  
  9.    for i in 1..100 loop 
  10.  
  11.    <<inter>> 
  12.  
  13.    for j in 1..100 loop 
  14.  
  15.    result:=i*j; 
  16.  
  17.    exit outer when result=1000; 
  18.  
  19.    exit when result=500; 
  20.  
  21.    end loop inner; 
  22.  
  23.    dbms_ouput.put_line(result); 
  24.  
  25.    end loop outer; 
  26.  
  27.    dbms_output.put_line(result); 
  28.  
  29.    end;  


6.顺序控制语句


  PL/SQL不仅提供了条件分支语句和循环控制语句,而且还提供了顺序控制语句GOTO


  和NULL.一般情况下不使用


  6.1 GOTO:用于跳转到特定标号处去执行语句.  


  1. GOTO LABEL_NAME; 



  1. declare 
  2.  
  3.   i int :=1; 
  4.  
  5.   begin 
  6.  
  7.     loop 
  8.  
  9.       insert into temp values(i); 
  10.  
  11.       if i=10 then 
  12.  
  13.          goto end_loop 
  14.  
  15.       end if; 
  16.  
  17.       ii:=i+1; 
  18.  
  19.    end loop; 
  20.  
  21.    <<end_loop>> 
  22.  
  23.    dbms_output.put_line('循环结束'); 
  24.  
  25.    end; 


   6.2 null:不会执行任何操作,并且会直接将控制传递到下一条语句.


  1. declare 
  2.  
  3.      v_sal emp.sal%type; 
  4.  
  5.      v_ename emp.ename%type; 
  6.  
  7.    begin 
  8.  
  9.      select ename,sal into v_ename,v_sal from 
  10. emp where empno=&no; 
  11.  
  12.      if v_sal<3000 then update emp set 
  13. comm=sal*0.1 where ename=v_ename; 
  14.  
  15.      else  
  16.  
  17.        null; 
  18.  
  19.      end if; 
  20.  
  21.    end; 



感谢April-MyHou指导！