(九) 游标变量
案例1、
declare --定义部分
--定义一个游标 sp_emp_cursor
type sp_emp_cursor is ref cursor;
--再定义一个游标变量test_cursor
test_cursor sp_emp_cursor;
--定义变量
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
--执行
--把test_cursor 和一个select结合
open test_cursor for select ename,sal from emp where deptno=&no;
--循环取出
loop
fetch test_cursor into v_ename,v_sal ;
--判断是否test_curso 为空
exit when test_cursor%notfound;
dbms_output.put_line('name is ' || v_ename || ' , salary is ' || v_sal);
end loop ;
close test_cursor;
end;
执行结果
执行结果;
SQL> declare --....
--...... sp_emp_cursor
2 3 type sp_emp_cursor is ref cursor;
4 --.........test_cursor
5 test_cursor sp_emp_cursor;
6 --....
7 v_ename emp.ename%type;
8 v_sal emp.sal%type;
9 begin
10 --..
11 --.test_cursor ...select..
12 open test_cursor for select ename,sal from emp where deptno=&no;
13 --....
14 loop
15 fetch test_cursor into v_ename,v_sal ;
16 --....test_curso ..
17 exit when test_cursor%notfound;
18 dbms_output.put_line('name is ' || v_ename || ' , salary is ' || v_sal);
19 end loop ;
20 close test_cursor;
21 end;
22 /
Enter value for no: 20
old 12: open test_cursor for select ename,sal from emp where deptno=&no;
new 12: open test_cursor for select ename,sal from emp where deptno=20;
name is SMITH , salary is 800
name is JONES , salary is 2975
name is SCOTT , salary is 120
name is ADAMS , salary is 1100
name is FORD , salary is 3000
PL/SQL procedure successfully completed.
(十) 控制结构
分支结构
案例1
create or replace procedure sp_pro6(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*1.1 where ename = spName;
end if;
end;
执行结果
SQL> create or replace procedure sp_pro6(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*1.1 where ename = spName;
end if;
end; 2 3 4 5 6 7 8
9 /
Procedure created.
调用
SQL> exec sp_pro6('SCOTT');
PL/SQL procedure successfully completed.
案例2
create or replace procedure sp_pro7(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;
案例3
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
v_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;
执行结果
SQL> create or replace procedure sp_pro8(spNo number) is
2 v_job emp.job%type;
3
4 begin
5 select job into v_job from emp where empno=spNo ;
6 if v_job ='PRESIDENT' then
7 update emp set sal =sal+1000 where empno =spNo;
8 elsif
9 v_job ='MANAGER' then
10 update emp set sal=sal +500 where empno =spNo;
11 else
12 update emp set sal=sal +200 where empno =spNo;
13 end if;
14 end;
15 /
Procedure created.
调用
SQL> exec sp_pro8(7839);
PL/SQL procedure successfully completed.
循环结构
案例1
create or replace procedure sp_pro9(spName varchar2) is
--定义
v_num number:=1;
begin
loop
insert into users values (v_num ,spName) ;
--判断是否要退出循环
exit when v_num=10;
--自增
v_num:=v_num+1;
end loop;
end;
执行结果:
SQL> create table users(userNo number,userName varchar2(40));
Table created.
SQL> create or replace procedure sp_pro9(spName varchar2) is
--..
v_num number:=1;
begin
loop
insert into users values (v_num ,spName) ;
--.........
exit when v_num=10;
--..
v_num:=v_num+1;
end loop;
end; 2 3 4 5 6 7 8 9 10 11 12
13 /
Procedure created.
SQL> exec sp_pro9('buchaqian');
PL/SQL procedure successfully completed.
案例2
create or replace procedure sp_pro10(spName varchar2) is
--定义
v_num number:=11;
begin
while v_num <= 20 loop
insert into users values(v_num,spName);
v_num := v_num+1;
end loop ;
end;
执行结果
SQL> create or replace procedure sp_pro10(spName varchar2) is
--..
v_num number:=11;
begin
while v_num <= 20 loop
insert into users values(v_num,spName);
v_num := v_num+1;
end loop ;
end; 2 3 4 5 6 7 8 9
10 /
Procedure created.
(十一) 分页
--书籍表
create table book (bookId number,bookName varchar2(50), publishHouse varchar2(50));
--in代表这是一个输入参数
--out:表示一个输出参数
create or replace procedure sp_pro11(spBookId in number,
spBookName in varchar2,spPublishHouse in varchar2) is
begin
insert into book values ((spBookId ,spBookName ,spPublishHouse);
end;
(十二) 返回结果集的过程
--创建一个包,在该报中,定义类型test_cursor,是个游标
第一步:
create or replace package testpackage as
type test_cursor is ref cursor;
end testpackage ;
第二步:
创建过程
create or replace procedure sp_pro13(spNo in number,p_cursor out testpackage.test_cursor) is
begin
open p_cursor for select * from emp where deptno =spNo;
end;
(十三) 分页和返回结果
分页回顾
select t1.*,rownum rn from (select * from emp) t1
select t1.*,rownum rn from (select * from emp) t1 where rownum <=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_cursor 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_cursor --返回一个记录集
) is
--定义部分,定义sql语句
v_sql varchar2(1000);
--定义两个整数
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 ||') t1 where rownum <='|| v_end || ') where rn >='||v_begin;
--把游标和sql关联
open p_cursor for v_sql;
--计算myrows和myPageCount
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;
end if;
--关闭游标
--close p_cursor ;
end;
执行结果
SQL> create or replace procedure fenye (tableName in varchar2,
Pagesize in number,
pageNow in number,
2 3 4 myrows out number,--....
5 myPageCount out number ,--...
6 p_cursor out testpackage.test_cursor --.......
7 ) is
8 --....,..sql..
9 v_sql varchar2(1000);
10 --......
11 v_begin number:=(pageNow - 1)*Pagesize + 1 ;
12 v_end number:= pageNow*Pagesize ;
13 begin
14 v_sql:='select * from ( select t1.*,rownum rn from (select * from '|| tableName ||') t1 where rownum <='|| v_end || ')
15
16 where rn >='||v_begin;
17 --....sql..
18 open p_cursor for v_sql;
19 --..myrows.myPageCount
20 v_sql:='select count(*) from ' || tableName ;
21 --..sql..........myrows
execute immediate v_sql into myrows;
22 23 --..myPageCount
24 if mod(myrows,Pagesize )=0 then
25 myPageCount := myrows/Pagesize;
26 else
27 myPageCount := myrows/Pagesize + 1;
28
29 end if;
30 --....
31 --close p_cursor ;
32 end;
33 /
Procedure created.
(十四) 例外
案例1
declare
v_ename emp.ename%type;
begin
select ename into v_ename from emp where empno =&no;
dbms_output.put_line('name is :' || v_ename);
exception
when no_data_found then
dbms_output.put_line('there is no the empno ,sorry !');
end;
案例2
自定义例外
create or replace procedure ex_test(spNo number)
is
myex exception ;--定义一个意外
begin
update emp set sal=sal+1000 where empno=spNo;
if sql%notfound then --sql%notfound 这是表示没有update
raise myex;--触发myex
end if;
exception
when myex then
dbms_output.put_line('has no any update ');
end;
执行结果
SQL> create or replace procedure ex_test(spNo number)
2
3 is
4 myex exception ;--......
5 begin
6 update emp set sal=sal+1000 where empno=spNo;
7 if sql%notfound then --sql%notfound ......update
8 raise myex;--..myex
9 end if;
10 exception
11 when myex then
12 dbms_output.put_line('has no any update ');
13 end;
14 /
Procedure created.
调用
SQL> exec ex_test(3);
has no any update
PL/SQL procedure successfully completed.
(十五) 视图
1、创建视图
SQL> conn system /1234567 as sysdba;
Connected.
SQL> create view myView as select * from scott.emp where sal < 1000;
View created.
案例2
SQL> create view myview2 as select scott.emp.ename,scott.emp.empno,scott.dept.dname from scott.emp,scott.dept where scott.emp.deptno=scott.dept.deptno;
View created.
2、删除视图
drop view myview;
(十六) 触发器
create or replace procdure sp_pro12 (spno in number,spName out varchar2) is
begin
select ename into spName from emp where empno =spno;
end;