PL/SQL介绍5

更新时间：2022-09-22 07:38:03

1. 条件判断

将工资低于2000的员工工资增加10%

create or replace procedure sp_proc6(spName varchar2) is
v_sal emp.sal%type;
begin
select sal into v_sal from emp where ename=spName;
if v_sal < 2000 then
update emp set sal=sal+sal*10% where ename=spname'
end if;
end;
/

执行：exec sp_pro6('scott');

将雇员补助不是0的为其增加100，是0的为之增加200

create or replace procedure sp_proc7(spName varchar2) is
v_comm emp.comm%type;
begin
select comm into v_comm from emp where ename=spName;
if v_comm <>0 then
update emp set comm=comm+100 where ename=spName;
else
update emp set comm=comm+200 where ename=spName;
end if;
end;
/

执行：exec sp_proc7

为president的工资增加1000， manager增加500，其他的增加200

create or replace procedure sp_pro8(spNo number) is
v_job emp.job%type;
begin
select job into v_job from emp where empno=spNo;
if v_job='president' then
update emp set sal=sal+1000 where empno=spNo;
elsif job='manager' then
update emp set sal=sal+500 where empno=spNo;
else
update emp set sal=sal+200 where empno=spNo;
end if;
end;
/

执行：exec sp_pro8(7839)

2. 循环结构

需求：增加10个用户到user表中，每次输入用户名则可

loop循环，先循环再执行

create or replace procedure sp_pro9(spName varchar2) is
--‘:=’表示赋值
v_num number:=1;

begin
loop
    --需要检查users表是否存在，字段是否匹配好，若表不存在，可能存在编译错误
    insert into users values(v_num, spName);
    exit when v_num=10;
    --v_num自增
    v_num:=v_num+1;
end loop;
end;
/

执行：exec sp_pro9('张三');

while循环

create or replace procedure sp_pro10(spName varchar2) is
--‘:=’表示赋值
v_num number:=11;
begin
while v_num <=20 loop
        --需要检查users表是否存在，字段是否匹配好，若表不存在，可能存在编译错误
        insert into users values(v_num, spName);
        --v_num自增
        v_num:=v_num+1;
end loop;
end;
/

执行：exec sp_pro10('李四');

create or replace procedure sp_p1(spname varchar2) is
v_id number:=1;
begin
loop
insert into t1 values(v_id, spname);
v_id:=v_id+1;
exit when v_id=100;
end loop;
end;
/

exec sp_p1('zhangsan')

其中表t1包含2个字段:id, name

for循环

由于语法上生涩，其每次的步长只能为1，且循环的次数需先确定，灵活性不是特别的大

create or replace procedure sp_pro11(spName varchar2) is
begin
    for i in reverse 21..30 loop
    insert into users values(i,spName)
    end loop;
end;
/

执行：exec sp_pro10('王五');

3. goto语句， null

goto语句用于跳转到特定的标号去执行。

declare
i int :=1;
begin
    loop
        dbms_output.put_line('输出值i='||i);
        if i=10 then
             goto end_loop;
        end if;
        i:=i+1;
    end loop;
    《end_loop》

dbms_output.put_line('循环结束！');
end;
/

null：什么都不做，只是为了增加可读性

4. Java调用存储有输入的存储过程

创建book表

create table book(bookId number, bookName varchar2(50), publishHouse varchar2(50));

编写存储过程：

--in:表示输入
--out:表述输出
create or replace procedure sp_pro12(spBookId in number, spBookName in varchar2, spPublishHouse in varchar2) is
begin
insert into book values(spBookId, spBookName, spPublishHouse);
end;
/

java调用存储过程

有输出的存储过程：

create or replace procedure sp_pro13(spno in number, spName out varchar2)
begin
select ename into spName from emp where empno=spno;
end;
/

调用该存储过程：

多个返回值的以此类同, 当Java去调用存储过程时，需要取出其中一部分的内容时，但是需要把所有变量都关联，否则出现没有关联的错误。取出来是可以不用都接收存储过程的返回值的。

***在项目中，更常用的是需要返回结果集的情况，比如传入一个部门，返回这个部门下的所有员工。由于一般的变量都是返回一个个的值，不能完成需求，这时就需要用到package了。

--返回结果集的过程
--1. 创建一个包，该包中，定义类型test_cursor,是一个游标
create or replace package testpackage as
type test_cursor is ref cursor;
end testpackage;
/

--2.建立存储过程
create or replace procedure sp_pro14(spNo in number, p_cursor out testpackage.test_cursor) is
begin
open p_cursor for select * from emp where deptno=spNo;
end;
/

用Java调用该存储过程返回的结果集：

注意点：1、注册的类型为CURSOR 2、Java取值用getObject, 用ResultSet接收。

5. Oracle的分页

--取出rownum
select t1.*, rownum rn from (select * from emp) t1;

--取出前10条记录
select t1.*, rownum rn from (select * from emp) t1 where rownum < 10;

--取出第6条到第10条记录, 在分页时可以作为模板使用
select * from (
select t1.*, rownum rn from (select * from emp) t1 where rownum < 10
) where rn >=6;

--开发游标相关的包
create or    replace package testpackage as type test_coursor is ref cursor;
end testpackage;

--开始编写分页的存储过程
create or replace procedure fenye(tableName in varchar2,
pageSize in number,
pageNow in number,
myrows out number,
myPageCount out number,
p_cursor out testpackage.test_coursor ) is
--定义部分
--定义sql语句字符串
v_sql varchar2(1000);
--定义两个整数, 表示页的起始number号和最后的number号
v_begin number:=(pageNow-1)* pageSize + 1;
v_end number:= pageNow*pageSize ;
begin
--执行部分, 按照薪水由低到高排序，再进行分页
v_sql:='select * from (
select t1.*, rownum rn from (select * from '|| tableName ||' order by sal) t1 where rownum < '|| v_end ||') where rn >='|| v_begin    ||';';
--把游标和sql关联
open p_cursor for v_sql;
--计算myrows和myPageCount
--组织一个sql语句
v_sql:='select count(*) from '|| tableName;
--执行sql，并把返回的值，赋给myrows
execute immediate v_sql into myrows;
--计算myPageCount
if mod(myrows,PageSize)=0 then
    myPageCount:=myrows/Pagesize;
else
    myPageCount:=myrows/Pagesize + 1;
endif;
--关闭游标
close p_cursor;
end;
/

Java调用分页存储过程：

Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManage.getConnection("jdbc:oracle:thin@127.0.0.1:1521:wilson","scott","tiger");
//通过连接，取得存储过程
CallableStatement cs = conn.prepareCall("{call fenye(?,?,?,?,?,?)}");
//对里面的内容赋值
cs.setString(1，"emp");
cs.setInt(2,5);
cs.set(3,1);

//注册输出的参数
cs.registerOutParameter(4,oracle.jdbc.OracleTypes.INTEGER);
cs.registerOutParameter(5,oracle.jdbc.OracleTypes. INTEGER);
//注册游标类型的返回类型
cs.registerOutParameter(6,oracle.jdbc.OracleTypes.CURSOR);

//执行存储过程
cs.execute();
//getInt中的4是由该参数的位置决定的
int rowNum=cs.getInt(4);
int pageCount=cs.getInt(5);
System.out.println("总行数："+ rowNum);
System.out.println("总页数："+ pageCount);

ResultSet rs = (ResultSet)cs.getObject(6);//取出返回值，在该存储过程中第6个变量是返回值
while(rs.next()){
//取出的是整个emp表中的第1列和第2列
System.out.println(rs.getInt(1)+", " + rs.getString(2));
}
//释放资源
cs.close();
conn.close();

本文转自 tianya23 51CTO博客，原文链接：http://blog.51cto.com/tianya23/261517，如需转载请自行联系原作者

上一篇 : ：windows server2003如何配置II6的Gzip压缩下一篇 : Linux基础知识---计划任务

PL/SQL介绍5

相关阅读

推荐文章