pl/sql进阶一控制结构

时间:2021-10-05 05:12:23

在任何计算机语言(c,java,c#,c++)都有各种控制语句(条件语句,循环结构,顺序控制结构…),在pl/sql中也存在这样的控制结构。

在本部分学校完毕后,希望大家达到:

1)使用各种if语句

2)使用循环语句

3)使用控制语句---共同和null

 

n  条件分支语句

pl/sql中提供了三种条件分支语句if---then,if---then---else,if---then---elsif---elsif...---else。

简单的条件判断 if---then

 
 

if 条件表达式 then

--执行语句...

end if;

 

 

 

 

 

 

 

?       编写一个过程,可以输入一个雇员名,如果该雇员的工资低于2000,就给该雇员工资增加10%。

create or replace procedure pro1(v_in_ename varchar2) is

v_sal emp.sal%type;

begin

  select sal into v_sal from emp where ename=v_in_ename;

  if v_sal<2000 then

    update emp set sal=sal*1.1 where ename=v_in_ename;

  end if;

end;

 

二重条件分支if---then---else

 
 

if 条件表达式 then

--执行语句...

--执行

else

--执行语句...

end if;

 

 

 

 

 

 

 

 

 

 

?       编写一个过程,可以输入一个雇员名,如果该雇员的补助不是0,就在原来的基础上增加100,如果补助为0就把补助设为200;

create or replace procedure pro1(v_in_ename varchar2) is

v_comm emp.comm%type;

begin

  select comm into v_comm from emp where ename=v_in_ename;

  if v_comm<>0 then

    update emp set comm=comm+100 where ename=v_in_ename;

  else

    update emp set comm=200 where ename=v_in_ename;

  end if;

end;

 

 

 

 

多重条件分支if---then---elsif---elsif...---else

 
 

if 条件表达式 then

--执行语句...

--执行

elsif 添加条件表达式 then

--执行语句...

elsif......

else

--执行语句...

end if;

 

 

 

 

 

 

 

 

 

 

 

 

 

?       编写一个过程,可以输入一个雇员编号,如果该雇员的职位是PRESIDENT就给他的工资增加1000,如果该雇员的职位是MANAGER就给他的工资增加500,其它职位的雇员工资增加200.

create or replace procedure pro1(v_in_empno number) is

v_job emp.job%type;

v_sal emp.sal%type;

begin

  select job into v_job from emp where empno=v_in_empno;

  if v_job='PRESIDENT' then

    update emp set sal=sal+1000 where empno=v_in_empno;

  elsif v_job='MANAGER' then

    update emp set sal=sal+500 where empno=v_in_empno;

  else

    update emp set sal=sal+200 where empno=v_in_empno;

  end if;

end;

 

特别说明:pl/sql中字符串的比较是用=,

 

 

循环语句---loop

是pl/sql中最简单的循环语句,这种循环语句以loop开头,以end loop结尾,这种循环至少会被执行一次

 

 

 

 

loop

执行语句...;

exit when 条件表达式

end loop

 

说明:这里的条件表达式表示如果为true就继续执行,否则退出

 

 

 

 

 

 

 

 

 

 

 

 

案例:现有一张表users,表结构如下:请编写一个过程,可输入用户名和添加用户的个数n;循环添加n个用户到users表中,用户编号从1开始增加,直到n。

用户ID

用户名

 

 

create table users5(id number primary key,name varchar2(32));

 

create or replace procedure pro1(v_in_name varchar2,v_in_number number) is

v_number number:=1;

v_name varchar2(32);

begin

  loop

    insert into users5 values(v_number,v_in_name);

    exit when v_number=v_in_number;

    v_number:=v_number+1;

  end loop;

end;

 

 

循环语句--while循环

基本循环至少要执行循环体一次,而对于while循环来说,只有条件为true时,才会执行循环体语句,while循环以while...loop开始,以end loop结束。

 
 

语法规则:

while 条件表达式 loop

执行语句...;

end loop;

 

 

 

 

 

 

 

 

create or replace procedure pro1(v_in_name varchar2,v_in_number number) is

v_number number:=200;

begin

  while v_number<=200+v_in_number loop

    insert into users5 values(v_number,v_in_name);

    v_number:=v_number+1;

  end loop;

      end;

看下面题判断是否正确

下面的过程是否正确,如果不正确,应该怎么改?

 

 

 

 
   

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

循环语句----for循环

基本for循环的基本机构如下

 
 

begin

for i in reverse  1..10 loop

insert into users values (i,’顺平’);

end loop;

end;

 

 

 

 

 

 

 

 

我们可以看到控制变量i,在隐含中就在不停的增加

推存使用loop循环结构,不推存使用for循环

 

顺序控制语句---gotonull

①goto语句

goto语句用于跳转到特定标号去执行语句,注意由于使用goto语句会增加城乡的复杂性,并使得应用程序可读性变差,所以在做一般应用开发时,建议大家不要轻易使用goto语句。

基本语法如下:goto lable,其中label是已经定义好的标号名。

declare

i number:=1;

begin

goto 标号;

<<标号>>

 

  <<start_loop>>

      loop

        dbms_output.put_line('输出i='||i);

        if i=12 then

          goto end_loop;

        end if;

        i:=i+1;

        if i=10 then

          goto start_loop;

        end if;

      end loop;

   <<end_loop>>

   dbms_output.put_line('循环结束!');

end;

--输出什么?

--goto语句的作用是可以直接跳转到指定的标号去继续执行

输出i=1

输出i=2

输出i=3

输出i=4

输出i=5

输出i=6

输出i=7

输出i=8

输出i=9

输出i=10

输出i=11

输出i=12

循环结束!

 

null

null语句不会执行任何操作,并且会直接将控制传递到下一条语句。使用null语句的主要好处是可以提高pl/sql的可读性。其实是什么都不做。

看看下面代码输出什么?

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;

  if v_sal<3000 then

    update emp set comm=sal*0.1 where ename=v_ename;

  else

    null;

  end if;

end;

 

 

pl/sql进阶——编写分页过程

介绍

分页是任何一个网站(BBS、网上商城、BLOG)都会使用的技术,因此学校pl/sql编程开发就一定要掌握该技术。

无返回值的存储过程

古人云:欲速则不达,为了让大家比较容易接受分页过程编写,我还是从简单到复杂,循序渐进的给大家讲解。首先是掌握最简单的存储过程,无返回值的存储过程:

案例:现有一张BOOK表,表结构如下:

 

字段名

字段类型

id

number(5)

name

varchar2(100)

pubHouse

varchar2(100)

 

请编写一个过程,可以向book表添加书,要求通过java程序调用该过程。

提示:查看jdk,看看CallableStatement是怎么调存储过程的。

有返回值的存储过程(非列表)

案例:编写一个过程,可以输入雇员的编号,返回该雇员的姓名。

案例扩展:编写一个过程,可以输入雇员的编号,返回该雇员的姓名、工资、岗位

 

1.编写过程

create or replace procedure pro1(v_in_ename in number,v_out_ename out varchar2) is

begin

  select ename into v_out_ename from emp where empno=v_in_ename;

end;

2.在java中去调用该过程,并接受返回的用户名

package com.lsz.test;

import java.sql.*;

public class TestProcedure {

   

    public static void main(String[] args) {

        Connection ct=null;

        CallableStatement cs=null;

        ResultSet rs =null;

        try {

            //加载驱动

            Class.forName("oracle.jdbc.driver.OracleDriver");

            //2.得到连接

    ct=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ORCL","scott","tiger");

            //3.创建CallableStatement

            cs=ct.prepareCall("{call pro1(?,?)}");

            //cs.setString(1,"7839");

            cs.setInt(1, 7839);

            //给第二个?注册(因为是输出值)

            cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);

            //4.执行

            cs.execute();

            //5.取出输出值

            String ename=cs.getString(2);

            System.out.println("用户的名字是:"+ename);

        } catch (Exception e) {

            e.printStackTrace();

        }finally{

            try {

               

            } catch (Exception e2) {

            }

        }

    }

}

说明:

1.对于过程的输入值,使用setXXX,对于输出值,使用registerOutParameter,问号的顺序要对应,同时要考虑类型。

2.取出过程返回的方法是CallableStatement提供的getXXXX(输出参数的位置);同时要考虑输出的参数类型

 

 

有返回值的存储过程(列表【结果集】)

案例:编写一个过程,输入部门号,返回该部门所有雇员信息。

对该题分析如下:

由于oracle存储过程没有返回值,它的所有返回值都是通过out参数来替代的,列表同样也不例外,但由于是集合,所有不能用一般的参数,必须要用package了,步骤如下:

①建一个包改包中定义一个游标类型。

create or replace package pack1 is

--定义一个游标数据类型

type my_cursor is ref cursor;

end;

②建立存储过程。

create is replace procedure pro1(v_in_deptno in number,v_out_result out pack1.my_crusor) is

begin

  open v_out_sult for select * from emp where deptno_v_in_deptno;

  --为了让java程序可以使用游标,不能在这里关闭游标

  --close v_out_result;

end;

③下面看看如何在java程序中调用。

package com.lsz.test;

import java.sql.*;

public class TestProcedure2 {

    public static void main(String[] args) {

        Connection ct=null;

        CallableStatement cs=null;

        ResultSet rs=null;

        try {

            Class.forName("oracle.jdbc.driver.OracleDriver");

            ct=DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:ORCL","scott","tiger");

            cs=ct.prepareCall("{call pro1(?,?)}");

            cs.setInt(1, 10);

            cs.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);

            cs.execute();

            //这里是关键

            rs=(ResultSet)cs.getObject(2);

            while(rs.next()){

                System.out.println(rs.getString("ename")+" "+rs.getString("sal"));

            }

           

        } catch (Exception e) {

            e.printStackTrace();

        }finally{

           

        }

    }

 

}