Oracle 的存储过程及游标(又学习)

Oracle 的存储过程及游标

Oracle中的存储过程和游标:
 select myFunc(参数1,参数2..) to dual;  --可以执行一些业务逻辑
一:Oracle中的函数与存储过程的区别:
   A:函数必须有返回值,而过程没有.
   B:函数可以单独执行.而过程必须通过execute执行.
   C:函数可以嵌入到SQL语句中执行.而过程不行.
   其实我们可以将比较复杂的查询写成函数.然后到存储过程中去调用这些函数.
二:如何创建存储过程:
  A:格式
    create or replace procedure <porcedure_name>
    [(参数名参数类型以及描述,....)]     ---注意,没有返回值
    is
    [变量声明]
    begin
    [过程处理];----------null;
    exception
      when 异常名 then
    end;
    注意:参数中默认是按值传递.是in方式.也可以是out和in out方式.这些特点和函数一样.
  B:举例1:
  create or replace procedure myPro----create or replace proc myPro  出错  不能简写
(a in int:=0,b in int:=0)
is
c int:=0;
begin
c:=a+b;
dbms_output.put_line('C is value'||c);
end;
执行:
execute myPro(10,20);   ---在Sql Server中.执行存储过程是不需要括弧的.注意 分号不要调了.
exec myPro(10,20);   --可以简写
 C:举例2:
   如果在一个函数里面包含Select语句的话,那么该Select语句必须有into,过程同样也需要.
  create or replace procedure myPro1
(a int:=0,b int:=0)
is
c int:=0;
begin
select empno+a+b into c from emp where ename='FORD';
dbms_output.put_line('C is values '||c);
end;
执行:
execute myPro1(10,20)

  D:假如在一个过程里面要返回一个结果集，怎么办?大家注意.就必须要用到游标了!用游标来处理这个结果集.
  create or replace procedure Test
(
varEmpName emp.ename%type
)
is begin            ------会报错.错误原因没有into子句.
select * from emp where ename like '%'||varEmpName||'%';
end;

这个程序我们无法用into，因为在Oracle里面没有一个类型去接受一个结果集.这个时候我们可以声明游标对象去接受他.

PL/SQL游标:
   A:分类:
     1:隐式游标:非用户明确声明而产生的游标.   你根本看不到cursor这个关键字.
     2:显示游标:用户明确通过cursor关键字来声明的游标.

   B:什么是隐式游标:
     1:什么时候产生:
       会在执行任何合法的SQL语句(DML---INSERT UPDATE DELETE   DQL-----SELECT)中产生.他不一定存放数据.也有可能存放记录集所影响的行数.
       如果执行SELECT语句,这个时候游标会存放数据.如果执行INSERT UPDATE DELETE会存放记录影响的行数.
   C:隐式游标叫什么名字:
      名字叫sql
       关于sql的游标变量到底有哪些呢?
         作用:返回最近一次执行SQL语句所涉及的游标相关信息.因为每执行一次SQL语句，都会产生一个隐式游标.那么当前执行的SQL语句就是当前的隐式游标.
          sql%found
          sql%notfound
          sql%rowcount
          sql%isopen
   D:关于隐式游标的例子:
    
  create table 学生基本信息表
(
StuID int,
StuName varchar2(20)
)

alter table 学生基本信息表 add constraint PK_STUID primary key(StuID)

declare
num int:=0;
begin
num:=&num;
delete from 学生基本信息表 where StuID=num;
if sql%notfound then
dbms_output.put_line('该行数据没有发现');
else
dbms_output.put_line('数据被发现并删除,影响的行数为:'||sql%rowcount);
end if;
end;

E:关于显示游标的例子:
  1:如何定义显示游标
     declare cursor <cursor_name> is [select语句];
     declare cursor mycur is select empno,ename,job from scott.emp;
  2:如何打开游标:
    open <cursor_name>;
    open mycur;
  3:如何通过游标来读取数据
    fetch <cursor_name> into <variable_list>
  4:如何关闭游标:
    close <cursor_name>;
    close mycur;
   注意:在Oracle中,不需要显示销毁游标.因为在Oracle中,很多东西是由JAVA写的.Oracle会自动销毁游标.
  5:举例:
   
declare
cursor mycur is select empno,ename,job from emp;
vempno emp.empno%type;
vename emp.ename%type;
vjob emp.job%type;
begin
open mycur;
fetch mycur into vempno,vename,vjob;
dbms_output.put_line('I Found You!'||mycur%rowcount||'行');
dbms_output.put_line('读取的数据为'||vempno||'   '||vename||'    '||vjob);
close mycur;
end;

因为只读出来一条,所以要遍历一下:
declare
cursor mycur is select empno,ename,job from emp;
vempno emp.empno%type;
vename emp.ename%type;
vjob emp.job%type;
begin
open mycur;
loop
fetch mycur into vempno,vename,vjob;
exit when mycur%notfound;
if mycur%found then
dbms_output.put_line('读取的数据为'||vempno||'   '||vename||'    '||vjob);
end if;
end loop;
dbms_output.put_line('I Found You!'||mycur%rowcount||'行');
close mycur;
end;
   6:通常情况下我们在读取表数据的时候,我们需要动态的去查询.所以能不能在Oracle中给游标带参数呢?可以!
    1):如何定义带参数的游标:
       declare cursor <cursor_name>(参数名称 参数类型描述) is select xxxxx from  bbbbb where   aaa==??? and ccc=???;
    2):例子:
       游标是一个集合,读取数据有两种方式
        第一种方式:   open   fetch  close
        第二种方式:    for     一但使用了for循环   在循环刚刚开始的时候,相当于执行open,在处理循环的时候,相当于执行fetch,
                               在退出循环的时候,相当于执行了close
declare
cursor query(vname varchar) is select empno,ename,job from emp where ename like'%'||vname||'%';

begin
for line in query('A')
loop
dbms_output.put_line(line.empno||'   '||line.ename||'   '||line.job);
end loop;
end;

实现动态输入:
declare
cursor query(vname varchar) is select empno,ename,job from emp where ename like'%'||vname||'%';

name1 varchar(10);
begin
name1:=upper('&name1');
for line in query(name1)
loop
dbms_output.put_line(line.empno||'   '||line.ename||'   '||line.job);
end loop;
end;