oracle PL/SQL编程进阶

最新推荐文章于 2022-01-06 15:52:19 发布

小小八卦姐

最新推荐文章于 2022-01-06 15:52:19 发布

阅读量923

点赞数

分类专栏：关系型数据库

本文链接：https://blog.csdn.net/hadoop_mapreduce/article/details/25077923

版权

关系型数据库专栏收录该内容

19 篇文章 0 订阅

订阅专栏

1、控制结构

（1）条件分支语句

三种条件分支语句： if?then, if?then-else, if ? then-elsif?then-else

eg：create or replace procedure pro6(userName varchar2) is

v_sal emp.sal%type;

begin

select sal into v_sal from emp where ename = userName;

if v_sal<2000 then

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

end if;

end;

（2）循环语句

① loop

以loop开头，以end loop结尾，这种循环至少会被执行一次。

②while

只有条件为true时，才会执行循环体语句，while循环以while ？ loop开始，以end loop结束。

begin

while v_num<=20 loop

--执行体

end loop;

end;

③for

基本结构：i在隐含中增加

begin

for i in reverse 1..10 loop

insert into users values(i,'scott');

end loop;

end;

（3）顺序控制语句（goto，null）

①goto语句

goto语句用于跳转到特定标号执行语句。由于使用goto语句会增加程序的复杂性，并使得应用程序可读性变差，所以在做一般应用开发时，不建议使用goto语句。

declare

i int:=1;

begin

loop

dbms_output.put_line(i);

if i=10 then

goto end_loop;

end if;

i:=i+1;

end lpop;

<<end_loop>>

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

end;

②null

null语句不会执行任何操作，并且会直接将控制传递到下一个语句，使用null语句的主要好处是提高pl/sql的可读性。

2、综合应用案例

①有输入和输出的存储过程

create or replace procedure pro8

(no in number, userName out varchar2) is --in表示输入值，out表示输出值

begin

select ename into userName from emp where empno=no;

end;

java端取出out值：

<span style="font-family:Microsoft YaHei;font-size:14px;">CallableStatement  cs = ct.prepareCall("{call  pro8(?,?)}");
cs.setInt(1,7788);
cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);//第二个参数是类型
cs.execute();
String  name = cs.getString(2);</span>

②有返回值的存储过程（返回结果集）

由于Oracle的存储过程没有返回值，它的所有返回值都是通过out参数来替代的，列表同样也不例外，但由于是集合，所以不能用一般的参数，必须要用package,分两部分：

a:建立一个包，在包中定义test_cursor，是一个游标类型

create or replace package testpackage as

type test_cursor is ref cursor;

end testpackage;

b:建立存储过程

create procedure pro8 (no in number,p_cursor out testpackage.test_cursor) is

begin

open p_cursor for select * from emp where deptno=no;

end;

c:Java调用

<span style="font-family:Microsoft YaHei;font-size:14px;">CallableStatement  cs = ct.prepareCall("{call  pro8(?,?)}");
cs.setInt(1,10);
cs.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);//第二个参数是类型
cs.execute();
ResultSet  rs=(ResultSet)cs.getObject(2);
while(rs.next())
{
	int  no = rs.getInt(1);
	String name = rs.getString(2);
}
</span>

③分页

create or replace package testpackage as

type test_cursor is ref cursor;

end testpackage;

create or repalce procedure fenye

(tableName in varchar2,

pageSize in number,

pageNow in number,

totalRows out number,

pageCount out number,

p_cursor out testpackage.test_cursor

) is

v_sql varchar2(1000);

v_begin number:=(pageNow-1)*pageSize + 1;

v_end number:= pageSize*pageNow;

begin

v_sql='select * from (select t1.*,rownum rn from (select * from '||tableName||'

) t1 where rownum<='||v_end||') where rn>='||v_begin;

open p_cursor from v_sql;

v_sql:='seclect count(*) from '||tableName;

--执行sql，并把返回的值赋给myrows

execute immediate v_sql into totalRows;

if mod(totalRows,pageSize)=0 then

pageCount :=totalRows/pageSize;

else

pageCount:=totalRows/pageSize +1;

end if;

--关闭游标

close p_cursor;

end;

java测试：

<span style="font-family:Microsoft YaHei;font-size:14px;">CallableStatement  cs = ct.prepareCall("{call  fenye(?,?,?,?,?,?)}");
cs.setInt(1,"emp");
cs.setInt(2,5);
cs.setInt(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();
int  totalRow = cs.getInt(4);
ResultSet  rs=(ResultSet)cs.getObject(6);
while(rs.next())
{
	int  no = rs.getInt(1);
	String name = rs.getString(2);
}
</span>