--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;
--------------------------------------
测试结果为:
输出i=1
输出i=2
输出i=3
输出i=4
输出i=5
输出i=6
输出i=7
输出i=8
输出i=9
输出i=10
循环结束
---------------------------------------------------------------------------------------------------------------
案例:将7788雇员(SCOTT)的comm改为sal的0.1倍
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;
---------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------
分页案例:
--有一张book表,三个字段(书号,书名,出版社),编写存储过程向book表中添加书,用java调用该过程
create table book(
bookId number(3),
bookName varchar2(50),
publishHouse varchar2(50)
);
--编写过程(in表示一个输入参数,可以省略)
create or replace procedure sp_pro22
(spBookId in number,spbookName in varchar2,sppublishHouse in varchar2) is
begin
insert into book values (spBookId,spbookName,sppublishHouse);
end;
--java代码
/*
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","scott","jsd161102");
//创建CallableStatement
CallableStatement cs = conn.prepareCall("{call sp_pro22(?,?,?)}");
//给?赋值
cs.setInt(1, 10);
cs.setString(2, "笑傲江湖");
cs.setString(3, "人民出版社");
//执行
cs.execute();
*/
==================================================
--有输入和有返回值的存储过程
--输入雇员编号,返回雇员的姓名
create or replace procedure sp_pro33
(spNo in number,spName out varchar2) is
begin
select ename into spName from emp where empno=spNo;
end;
--java代码
/*
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","scott","jsd161102");
//创建CallableStatement
CallableStatement cs = conn.prepareCall("{call sp_pro33(?,?)}");
//给第一个?赋值
cs.setInt(1, 7788);
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
//执行
cs.execute();
//取出返回值,要注意?的顺序
String name = cs.getString(2);//第二个?,写2
System.out.println("7788的名字是:"+name);
*/
=================================================================================
扩展案例
--输入雇员编号,返回雇员的姓名,工资,岗位
create or replace procedure sp_pro44
(spNo in number,spName out varchar2,spSal out number,spJob out varchar2) is
begin
select ename,sal,job into spName,spSal,spJob from emp where empno=spNo;
end;
--java代码
/*
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","scott","jsd161102");
//创建CallableStatement
CallableStatement cs = conn.prepareCall("{call sp_pro44(?,?,?,?)}");
//给第一个?赋值
cs.setInt(1, 7788);
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
cs.registerOutParameter(3, oracle.jdbc.OracleTypes.DOUBLE);
cs.registerOutParameter(4, oracle.jdbc.OracleTypes.VARCHAR);
//执行
cs.execute();
//取出返回值,要注意?的顺序
String name = cs.getString(2);//第二个?,写2
double sal = cs.getDouble(3);
String job = cs.getString(4);
System.out.println("7788的名字是:"+name+"工资是:"+sal+"工作是:"+job);
*/