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>
3、例外处理
Oracle将例外分为预定义例外,非预定义例外和自定义例外三种
预定义例外用于处理常见的oracle错误
非预定义例外用于处理预定义例外不能处理的例外
自定义例外用于处理与Oracle错误无关的其它情况
(1)处理预定义例外
预定义例外时由pl/sql提供的系统例外。当pl/sql应用程序违反了Oracle规定的限制时,会隐含的触发一个内部例外。
①case_not_found
在开发pl/sql块中编写case语句时,如果在when子句中没有包含必要的条件分支,就会触发case_not_found例外。
②cursor_already_open
当重新打开已经打开的游标时,会隐含的触发例外。
③dup_val_on_index
在唯一索引所对应的列上插入重复的值时,会隐含的触发例外
④invalid_cursor
当试图在不合法的游标上执行操作时,会触发该例外。
例如:试图从没有打开的游标提取数据,或是关闭没有打开的游标。
⑤invalid_number
当输入的数据有误时,会触发该例外。
⑥no_data_found
当执行select into没有返回行,就会触发该例外。
⑦too_many_rows
当执行select into 语句时,如果返回超过了一行,则会触发该例外。
⑧zero_divide
分母为0时。
⑨value_error
当在执行赋值操作时,如果变量的长度不足以容纳实际数据,则会触发该例外value_error
其他预定义例外
①login_denide
当用户非法登录时,会触发该例外
②not_logged_on
如果用户没有登录就执行dml操作,就会触发该例外
③storage_error
如果超出了内存空间或是内存被损坏
④timeout_on_resource
oracle在等待资源时,出现了超时就会触发该例外。
(2)非预定义例外
非预定义例外用于处理与预定义例外无关的oracle错误。使用预定义例外只能处理21个oracle错误,而当使用pl/sql开发应用程序时,可能会遇到其他的一些Oracle错误。比如在pl/sql块中执行dml语句时,违反了约束规定等等。
(3)自定义例外
预定义例外和自定义例外都是与Oracle错误相关的,并且出现的oracle错误会隐含的触发相应的例外;而自定义例外与oracle错误没有任何关联,它是由开发人员为特定情况所定义的例外。
create procedure ex_test(no number)
is
myex exception
begin
update emp set sal=sal+1000 where empno=no;
if sql%notfound then --sql%notfound表示没有update成功
raise myex; --触发Myex例外
endif;
exception
when myex then
dbms_output.putline("没有更新")
end;
4、视图
视图是一个虚拟表,其内容由查询定义。同真实的表一样,视图包含一系列带有名称的列和行数据。但是,视图并不在数据库中以存储数据值集形式存在。行和列数据来自由定义视图的查询所引用的表,并且在应用视图时动态生成。
视图和表的区别:
①表需要占用磁盘空间,视图不需要
②视图不能添加索引
③使用视图可以简化复杂查询
④视图利于提高安全性
(1)创建视图
create view 视图名 as select语句 [with read only]
(2)创建或修改视图
create or replace view 视图名 as select语句 [with read only]
(3)删除视图
drop view 视图名
eg:创建视图,把emp表的sal<1000的雇员映射到该视图
create view myview as select * from emp where sal<1000;
用一个视图显示雇员编号、姓名和部门名称
create view view2 as select emp.empno,emp.ename,dept.dname from emp,dept where emp.dept=dept.deptno
select * from view2;
视图之间也可以做联合查询。