PL/SQL知识点(二)

(九) 游标变量

案例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;