07_PLSQL编程.doc

PL / SQL编程Oracle中的高级编程 PL/SQLSQL语句上的一个分支，但是PL/SQL只适合于oracleSQL语句没什么区别：按固定格式编写即可：declare变量begin执行SQL语句，也可以执行任何的输出代码end;/默认情况下，Oracle中的系统输出是关闭的，即，没有任何内容显示在屏幕上declarei number ;begin- 输出ii := 10 ;dbms_output.put_line(i = |i) ;end ;/set serveroutput on 打开系统输出完成以下功能：要求用户可以自己输入雇员ID，同时打印出个人全部信息在Oracle中使用&可以完成信息的输入select * from emp where empno=&empno ;希望编写一个PL/SQL块，PLSQL块之中编写用户输入信息的提示，同时，查找数据，要求输入一个雇员编号，将雇员的名字打印出来。declareeno number ;name varchar(20) ;begindbms_output.put_line(请输入雇员编号：) ;- 输出ieno := &empno ;- 最终结果要的是将名字取出，同时打印SELECT ename INTO name FROM emp WHERE empno=eno ;dbms_output.put_line(姓名是 = |name) ;end ;/在PLSQL之中如果没有合适的数据，则默认情况下会产生错误，与java没加try块一样declareBeginexceptionEnd;/declareeno number ;name varchar(20) ;begindbms_output.put_line(请输入雇员编号：) ;- 输出ieno := &empno ;- 最终结果要的是将名字取出，同时打印SELECT ename INTO name FROM emp WHERE empno=eno ;dbms_output.put_line(姓名是 = |name) ;exception- 匹配错误WHEN no_data_found THENdbms_output.put_line(没有这个员工) ;end ;/在PL/SQL之中同样包含了程序的控制语言：循环、条件判断循环 循环的初始条件 循环的结束条件 循环条件的变更LOOP循环的主体 （语句）Exit WHEN 条件 ;修改条件END LOOP ;现在打印110declare i number ;begini := 1 ;loopdbms_output.put_line(i = |i) ;exit when i=10 ;i := i + 1 ;end loop ;end ;/问题？给所有的员工工资增加10%。 游标如果只用循环，只能确定循环的个数 count函数LOOP . END LOOP循环至少执行了一次，先执行再判断WHILE LOOP先判断再执行declare i number ;begini := 1 ;while(i=10 thendbms_output.put_line(记录大于10条。) ;end if ;end;/If else . end ifdeclarei number ;beginselect count(empno) INTO i from emp ;if i=10 thendbms_output.put_line(记录小于10条。) ;elsedbms_output.put_line(记录大于10条。) ;end if ;end;/多条件分支If then .elsif then elsif else end if ;declarei number ;beginselect count(empno) INTO i from emp ;if i10 and i3000 thendbms_output.put_line(高工资，工资为：|s) ;elsif s=2000 and s3000 thendbms_output.put_line(中等工资，工资为：|s) ;elsif s5000 thenupdate emp set sal=5000 where empno = eno ;elseupdate emp set sal=sal*1.1 where empno=eno ;end if ;elsif dno=20 thenif s*1.25000 thenupdate emp set sal=5000 where empno = eno ;elseupdate emp set sal=sal*1.2 where empno=eno ;end if ;elsif dno=30 thenif s*1.35000 thenupdate emp set sal=5000 where empno = eno ;elseupdate emp set sal=sal*1.3 where empno=eno ;end if ;end if ;end;/Goto语句：跳转，程序跳转到某个程序段之中，但是程序段必须有一个声明，声明跳转的名称declarei number ;begin i := 2 ;if i=1 thengoto p1 ;elsegoto p2 ;end if ;dbms_output.put_line(p1点) ;dbms_output.put_line(p2点) ;end;/PL/SQL异常关于语句返回多行的错误情况declare - 在oracle中使用表名称.字段名称%type表示一个类型dno emp.deptno%type ;name emp.ename%type ;begindno := &deptno ;select ename into name from emp where deptno=dno ;exceptionwhen too_many_rows thendbms_output.put_line(返回的结果多于1行) ;when no_data_found thendbms_output.put_line(没有查询结果) ;end ;/If count0 thenraise myexp raise 跟 java中的 throw一样，表示抛出一个异常- 目的：插入数据，如果编号已经存在，则抛出一个错误，显示数据已经存在declaredno dept.deptno%type ;dna dept.dname%type ;dlo dept.loc%type ;myexp EXCEPTION ;coun number ;begin dbms_output.put_line(输入部门编号：) ;dno := &deptno ;- 确定该部门是否存在select count(deptno) into coun from dept where deptno=dno ;if coun0 thenraise myexp ;elsedbms_output.put_line(输入部门名称：) ;dna := &dname ;dbms_output.put_line(输入部门位置：) ;dlo := &loc ;INSERT INTO dept(deptno,dname,loc) values (dno,dna,dlo) ;DBMS_OUTPUT.put_line(部门信息添加成功！) ;end if ;exceptionwhen myexp thendbms_output.put_line(错误：部门编号已经存在！) ;end;/- 目的：插入数据，如果编号已经存在，则抛出一个错误，显示数据已经存在declaredno dept.deptno%type ;dna dept.dname%type ;dlo dept.loc%type ;myexp EXCEPTION ;coun number ;begin dbms_output.put_line(输入部门编号：) ;dno := &deptno ;dbms_output.put_line(输入部门名称：) ;dna := &dname ;dbms_output.put_line(输入部门位置：) ;dlo := &loc ;- 确定该部门是否存在select count(deptno) into coun from dept where deptno=dno ;if coun0 thenraise myexp ;elseINSERT INTO dept(deptno,dname,loc) values (dno,dna,dlo) ;DBMS_OUTPUT.put_line(部门信息添加成功！) ;end if ;exceptionwhen myexp thendbms_output.put_line(错误：部门编号已经存在！) ;end;/PL/SQL基础部分就这么多，下面全部是PL/SQL应用：游标、过程、函数游标EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO- - - - - - - - 7369 SMITH CLERK 7902 17-12月-80 800 20 7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30 7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30 7566 JONES MANAGER 7839 02-4月 -81 2975 20 7654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 30 7698 BLAKE MANAGER 7839 01-5月 -81 2850 30 7782 CLARK MANAGER 7839 09-6月 -81 2450 10 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20 7839 KING PRESIDENT 17-11月-81 5000 10 7844 TURNER SALESMAN 7698 08-9月 -81 1500 0 30 7876 ADAMS CLERK 7788 23-5月 -87 1100 20 7900 JAMES CLERK 7698 03-12月-81 950 30 7902 FORD ANALYST 7566 03-12月-81 3000 20 7934 MILLER CLERK 7782 23-1月 -82 1300 10是可以针对查询出的所有数据进行操作的，如果查询出的数据有50条，那么就可以对50条记录进行依次的操作，例如：为每一个员工的工资增加工资10 部门 10%20部门 20%30部门 30% coursefetch通过此语句可以使游标的指针向下移动游标中可以加入游标%FOUNDdeclareCURSOR mycur IS SELECT * FROM emp ;erow emp%ROWTYPE ;begin- 打开游标open mycur ;fetch mycur into erow ;while (mycur%FOUND) loopdbms_output.put_line(erow.empno| - |erow.ename) ;fetch mycur into erow ;end loop ;end;/declareCURSOR mycur IS SELECT * FROM emp ;erow emp%ROWTYPE ;begin- 打开游标open mycur ;fetch mycur into erow ;while (mycur%FOUND) loop- dbms_output.put_line(erow.empno| - |erow.ename) ;if erow.deptno=10 thenif erow.sal*1.15000 thenupdate emp set sal=5000 where empno = erow.empno ;elseupdate emp set sal=sal*1.1 where empno=erow.empno ;end if ;elsif erow.deptno=20 thenif erow.sal*1.25000 thenupdate emp set sal=5000 where empno = erow.empno ;elseupdate emp set sal=sal*1.2 where empno=erow.empno ;end if ;elsif erow.deptno=30 thenif erow.sal*1.35000 thenupdate emp set