44，数据库（06）

/*
达内学习 Oracle day42 2013-10-31
*/
多重循环的退出
declare
  var_m number;
  var_n number;
begin
  while var_m < 4 loop
  end loop;
end;

begin
    for var_m in 1..3 loop
        for var_n in 1..3 loop
             dbms__output.put_line(var_n);
             if var_n =2 then
                goto endouter;
           end if;
         end loop;
   end loop;
<<endouter>>
NULL;
end;

    1,在内层循环中修改外层循环条件
    2,标签 goto
-------------------------------------------------------------
SQL 在plsql中的分类
1，select 语句在plsql语句中使用时 要和 into 结合变量使用
  select first_name into var_name from s_emp where id = var_id;
2，insert  delete  update DML
  commit rollback savepoint TCL
  这写语句可以直接在plsql中使用
  declare
    var_id number :=1;
   var_name varchar2(30):='test';
  begin
 insert into testplsql values(2,'app');
    commit;
  end;
3,DDL 
 create table  drop table    alter table
这些语句不能直接在Plsql中使用，需要动态sql

把sql语句变成字符串
动态sql:把一个字符串对应的sql语句，当成真正的sql来执行。
declare
  sqlstr varchar2(100);
begin
  sqlstr:='create table testplsql(id) number)';
   sqlstr:=substr(sqlstr,1,length(sqlstr)-1);
  sqlstr:=sqlstr||','||'name varchar2(20)';
  /*把字符串 对应 sql当真正sql来执行*/
    execute immediate sqlstr;
end;
4,DML语句的
declare
 var_id number:=1;
 var_name varchar2(30):='test';
 sqlstr varchar2(100);
begin
 sqlstr:='insert into testplsql values(2,''app'')';
 dbms_output.put_line(sqlstr);
 sqlstr:= 'insert into testplsql values('||var_id||','''||var_name||''')';

替代字符串拼接 —— 使用占位符
sqlstr:='insert into testplsql values(:b0,:b1)';
execute immediate sqlstr using var_id,var_name;
5,select 语句动态sql
 只能返回一个结果
declare
 var_name s_emp.first_name%type;
  sqlstr varchar2(300);
begin
 sqlstr='select first_name from s_emp where id =2';
 execute immedaite sqlstr into var_name;
end;
6,一次可以放多条数据的数据类型  游标cursor

cursor 的使用步骤
1，声明cursor
   没有declare关键字    cursor 游标名 is sql语句；
2, 打开cursor
   打开游标 open 游标名；
3, 提取数据，处理数据
   fetch 游标名 into 变量名；
4，关闭游标
   close 游标名；

declare
 cursor empcursor  is select * from s_emp;
 var_emp  empcursor%rowtype;
begin
open  empcursor;
fetch empcursor into var_emp;
dbms_output.put_line(var_emp.id||'  '||var_emp.first_name||'  '||var_emp.salary);
fetch empcursor into var_emp;
dbms_output.put_line(var_emp.id||'  '||var_emp.first_name||'  '||var_emp.salary);
close empcursor;
end;

b,游标的遍历
游标的属性
found  提取数据时，提取到返回真 ，没有提取到返回假（游标必须处于打开），不打开就是非法游标，打开不fetch使用这个属性返回 NULL。  游标名%found

notfound   游标必须处于打开状态，打开不fetch 则返回 null,如果提取到新数据 返回假，没有提取到新数据 就返回真。

isopen   游标是否打开 ,打开返回真，关闭返回假，打开的不能再打开，关闭的不能再关闭
rowcount  当前游标的指针位移量

使用loop简单循环 结合notfound 遍历游标
declare
 cursor empcursor  is select * from s_emp;
 var_emp  empcursor%rowtype;
begin
open  empcursor;
loop
fetch empcursor into var_emp;
exit when empcursor%notfound;
dbms_output.put_line(var_emp.id||'  '||var_emp.first_name||'  '||var_emp.salary);
end loop;
close empcursor;
end;
-----------------------使用while found
declare
 cursor empcursor  is select * from s_emp;
 var_emp  empcursor%rowtype;
begin
open  empcursor;
while empcursor%found loop
dbms_output.put_line(var_emp.id||'  '||var_emp.first_name||'  '||var_emp.salary);
fetch empcursor into var_emp;
end loop;
close empcursor;
end;

智能循环（for循环）
declare
 cursor empcursor is select * from s_emp;
begin
 for var_emp in empcursor loop --不用定义变量，不用 open  fetch close
  dbms_output.put_line (var_emp.id||':'||var_emp.salary);
 end loop;
end;

c，带参游标
plsql中的参数不能有任何长度修饰
但是可以使用 %type
但参游标  只要打开游标时 传入实参即可
declare
 cursor empcursor（var_id number)  is select * from s_emp where id>var_id;
 var_emp  empcursor%rowtype;
begin
open  empcursor(10);   --传参
loop
fetch empcursor into var_emp;
exit when empcursor%notfound;
dbms_output.put_line(var_emp.id||'  '||var_emp.first_name||'  '||var_emp.salary);
end loop;
close empcursor;
end;
----
for 循环写法
declare
 cursor empcursor(var_id number) is select * from s_emp where id>var_id;
begin
 for var_emp in empcursor(10) loop --传参 不用定义变量，不用 open  fetch close
  dbms_output.put_line (var_emp.id||':'||var_emp.salary);
 end loop;
end;

d,参考游标
 把一个游标对应的sql语句关联到一个sql字符串上， 动态sql 和游标 的结合
declare
  type myrefcursor is ref cursor;
 /*使用 参考游标类型  定义游标变量*/
   empcursor myrefcursor;
   sqlstr varchar2(100):='select * from s_emp where id >:b0';
    var_emp s_emp%rowtype;
    var_id s_emp.id%type:=15;
begin
/*打开游标关联到字符串*/
 open empcursor for sqlstr using var_id;
/*  后边和正常的游标一样*/
 loop
fetch empcursor into var_emp;
exit when empcursor%notfound;
dbms_output.put_line(var_emp.salary);
end loop;
end;
-------------------------------------------------------------------------
存储过程 和 函数
1，存储过程  procedure
  给我们的匿名块 起名子 存储到数据库中
设计两个整数参数 打印其中最大值
create or replace procedure getmax (x number,y number)
is
  var_x number;
  var_y number;
begin
  var_x:=x;
  var_y:=y;
  if var_x<var_y then
  dbms_output.put_line(var_x);
  else
  dbms_output.put_line(var_y);  
  end if;
end;

出现重名函数  drop  function getmax

2,察看存储过程参数 desc getmax
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 X                              NUMBER                  IN
 Y                              NUMBER        
参数的模式： in(传入）  out（传出）   in out（传入又传出）
default: 参数的默认值
3.调用存储过程
  exec  getmax(100,200);
  call getmax(1,201);
  匿名块调用 或者  有名块调用
declare
a number :1;
b number :99;
begin
      /*参数的位置赋值*/
  call getmax(a,b);
--  call getmax(x=>a,y=>b); 参数的名字赋值
end

设计一个存储过程  把两个整数参数和放入第三个参数中
create or replace procedure getmax (x in number,y in number,z out number)
is
begin
  z:=x+y;
end;
调用：
declare
  var_z number:=0;
begin
   getmax(1,1,var_z);
end;

----------------------
设计一个存储过程  传入两个整数参数
要求输出两个整数参数最大值  还要求把两个参数的和放入第二个参数中
察看存储过程 并调用

create or replace procedure mypro(x in number,y in out number,z out number)
is
begin
 if x<y then
 z:=y;
 else
 z:=x;
 end if;
 y:=x+y;
end;

declare
a number:=0;
b number:=4;
c number:=0;
begin
 mypro(2,b,c);
 dbms_output.put_line(b||' '||c);
end;

察看存储过程的源代码
desc user_source;
select text from user_source where name ='MYPRO';
------------------------------------------------------------------
函数
 函数和存储过程的区别
 1，关键字不同   procedure  function
 2,过程没有返回值和返回值类型，函数可以有返回值和返回值类型
     return
 3,存储过程可以直接在匿名块或有名块里调用，函数必须组成表达式或者在sql语句中使用。
 
设计一个函数，传入两个整数参数  返回两个参数的最大值
create or replace function wfungetmax(x in number,y in number) return number
is
begin
    if x<y then
      return y;
    end if;
    return x;
end;
察看函数  desc  fungetmax
---------------------------------------------------------------
设计一个函数，传入两个整数参数，要求返回两个整数参数的最大值，并且要求把两个参数的和放入第二个参数中。
察看函数 调用函数
create or replace function fungetmaxsum(x in number,y in out number) return number
is
begin
  if x>y then
   y:=x+y;
  return x;
  end if;
   x:=x+y;
   y:=x-y;
   x:=x-y;
   y:=x+y;
  return x;
end;
-----------------------------------------
create or replace function fungetmaxsum(x in number,y in out number) return number
is
   var_temp number;  --这里可以设临时变量
begin
   var_temp:=y;
    y:=x+y;
    if x<var_temp then
          return var_temp;
   end if;
     return x;
end;
---------------------------------------------------------
参数的默认值
create or replace function testpardef(x number, y number:=123)  return number  -- 默认值必须靠右，参数有默认值他右侧必须也有默认值
is
begin
   if x<y then
       return y;
   else
       return x;
   end if;
end;
测试：
select testpatdef() from dual;   --123
select testpatdef(46545) from dual;  --46545
select testpatdef(11,12) from dual;  --12
---------------
1,建立存储过程的语法
create  or replace procedure 过程名 （参数名 参数模式 参数类型）
is
 临时变量
begin
end;
2，察看存储过程
desc  过程名
参数名   类型   参数模式（in   out   in out) 参数默认值
3，调用存储过程
sqlplus  call  exec
 匿名块 有名块 调用
declare
 /*变量定义*/
begin
  /*过程调用*/
end;
4,删除存储过程
 drop procedure 过程名；
5，函数和存储过程的区别
  1，关键字
    procedure   function
  2,过程没有返回值，函数有返回值和返回值类型   return
  3,调用方式不同，过程可以直接调用，函数必须组成表达式
  4，参数的默认值
----------------------------------------------------
包：package
系统提供的包
dbms_output  系统输入输出包
dbms_random  随机数包
  desc ddbms_random;
   select trunc(dbms_random.value(1,100)) from dual;
dbms_job     定时任务调度包
   定时调用存储过程
   submit(job binary_integer,what varchar2,next_date date,interval varchar2)
    会把定时任务提交给系统，系统会为这个人物分配一个编号 放入job中
    next_date 第一次调用的时间
    interval 下一次调用的时间
   run(job binary_integer);  运行定时任务
    remove(job binary_integer); 删除定时任务

1，建立一张表
  create  table testjob(
    id number primary key,
     name varchar2(30),
   );
2,建立一个序列
  create sequence testjob_id;
3, 写一个存储过程 向表添加一条数据
create or replace procedure insert_job
is
begin
 insert into testjob values(testjob_id.nextval,'test'||testjob_id.currval);
 commit;
end;
4,把存储过程的调用 交给 dbms_job
  declare
   jobno  binary_integer;
  begin
   dbms_job.submit(jobno,'insert_job();',sysdate,'sysdate+1/24/60');
   dbms_output.put_line('jobbo='||jobno);
    dbms_job.run(jobno);
  end;
5，删除
begin
 dbms_job.remove(jobno );
end;
----------------------------
自定义包  package
  把一组相关的函数  变量  过程等仿佛一个逻辑结构中
 相当于头文件
 create or replace package mypack
is
  procedure getmax(x number,y number);
  function getmin(x number,y number) return number;
end;

包体：相当与C中的实现文件
create or replace package body mypack
is
 procedure getmax(x number,y number)
  is
  begin
  if x<y then
    dbms_output.put_line(y);
  else
     dbms_output.put_line(x);
   end if;
  end;
  function getmin(x number,y number) return number
   is
  begin
  if x<y then
    dbms_output.put_line(x);
  else
     dbms_output.put_line(y);
   end if;
  end;

/*如何调用  数据前加包名*/
begin
   mypack.getmin(1,2) from ;
end;