个人工作笔记（orcale）

----电脑不能操作复制粘贴

开始-运行：输入regsvr32 Shdocvw.dll 回车 regsvr32 Oleaut32.dll 回车regsvr32 Actxprxy.dll 回车regsvr32 Mshtml.dll 回车regsvr32 Urlmon.dll 回车，重启如果还不行，输入regsvr32 Shell32.dll 回车。

-------------------------------------------------
 ----<1 LOOP分别计算1-10的累计和 ，并存入打印输出
declare
num_n number(7);
sum_s number(7);
begin
 num_n:=0;
 sum_s:=0;
 loop
    sum_s:=sum_s+num_n;
    num_n:=num_n+1;
    exit when num_n>10;
  end loop;
  dbms_output.put_line(sum_s);
end;


 ----<2 LOOP分别计算1-10的累计和 ，并存入打印输出
declare 
num_1 number:=0;
sum_1 number:=0;

begin 
  loop
    sum_1:=sum_1+num_1;
   if num_1>=10 then
     exit;
     end if;
     num_1 :=num_1+1;
     
    end loop;
    dbms_output.put_line(sum_1);
    end;
   ----<3 while分别计算1-10的累计和 ，并存入打印输出 
   declare 
   num_1 number :=1;
   sum_1 number:=0; 
   begin
     while num_1<10 loop
        sum_1:=sum_1+num_1;
        num_1:=num_1+1;
      end loop;
      dbms_output.put_line(sum_1);
      end;
      
    -----<4for i in 1..10 循环，分别计算1-10的累计和 ，并存入打印输出 
    declare 
    num_1 number:=1;
    sum_1 number:=0;
    begin
      for  i in 1..10 loop
        sum_1:=sum_1+num_1;
        num_1:=num_1+1;
        end loop;
         dbms_output.put_line(sum_1);

-------------------------------------------------------
declare
 v_sname varchar2(10);
 v_crad_no ba_person_info.CARD_NO%type;--定义类型与表字段类型一致

  begin
     select f.FULL_NAME,f.CARD_NO
    into v_sname,v_crad_no
   from ba_person_info f where f.CARD_NO in('10007717','6092001797');
   dbms_output.put_line('员工:'||v_sname||v_crad_no);
   exception---异常处理
     when no_data_found then
   dbms_output.put_line('输入的工号不存在'||v_sname);
       end;
       
----异常-----
   EXCEPTION
      WHEN OTHERS THEN
        dbms_output.put_line('执行出错了，老板！');

--如果除数是零，捕获异常
declare
v_rslt number(10):=0;
v_b number(10);
begin
  v_b:=100/v_rslt;
  dbms_output.put_line('结果是：'|| v_rslt);
  exception
  when zero_divide then
  dbms_output.put_line('除数是零！默认用1代替，结果是：'||100/1);
end;



---自定义复合类型---
DECLARE

  TYPE V_DD IS RECORD(
    Y_ID   BA_PERSON_INFO.CARD_NO%TYPE,
    Y_NAME BA_PERSON_INFO.FULL_NAME%TYPE);
  V_DD_1 V_DD;

BEGIN
  V_DD_1.Y_ID := 10080207;
  SELECT CARD_NO,
         FULL_NAME
    INTO V_DD_1
    FROM BA_PERSON_INFO
   WHERE CARD_NO = V_DD_1.Y_ID;
  DBMS_OUTPUT.PUT_LINE('工号:' || V_DD_1.Y_ID || V_DD_1.Y_NAME);
END;
---------------------------------------------
declare
 v_crad_no ba_person_info%rowtype;--所有字段

  begin
     select *
    into v_crad_no
   from ba_person_info 
   where CARD_NO='10080207';
   exception---异常处理
     when no_data_found then
   dbms_output.put_line('输入的工号不存在'||v_crad_no.FULL_NAME);
       end;
   --------------修改性别-------------------

declare
v_id ba_person_info.CARD_NO%type;
v_name ba_person_info.FULL_NAME%type;
begin
  v_id:='6092001797';
  select full_name into v_name from ba_person_info
  where CARD_NO=v_id;
  if v_name='范会' then
    update ba_person_info
    set sex='男'
    where CARD_NO=v_id;
    end if;
    end;
 --------------if else-----------------------
declare
v_co st_contract.contract_no%type;
v_te st_contract.TRADE_TYPE%type;
v_COUNTRY st_contract.country%type;
begin
  v_co:=&456;
  select TRADE_TYPE,COUNTRY into v_te,v_country from st_contract
  where contract_no=v_co;
  if v_te is null then
    update st_contract
       set TRADE_TYPE='' ,LAST_UPDATED_DATE=sysdate
       WHERE contract_no=v_co;
    else
      update st_contract
      set country='US',LAST_UPDATED_DATE=sysdate
      WHERE CONTRACT_NO=V_CO;
      END IF;
      
  EXCEPTION
      WHEN OTHERS THEN
        dbms_output.put_line('执行出错了，老板！');
        END;


-----------case---------------

DECLARE
  V_ID   BA_PERSON_INFO.CARD_NO%TYPE;
  V_NAME BA_PERSON_INFO.FULL_NAME%TYPE;
  V_CO   BA_PERSON_INFO.COMPUTER_FNO%TYPE;
BEGIN
  V_ID := &CARD_NO;
  SELECT FULL_NAME INTO V_NAME FROM BA_PERSON_INFO WHERE CARD_NO = V_ID;
  CASE V_NAME
    WHEN '桂猛' THEN
      DBMS_OUTPUT.PUT_LINE('你好' || V_NAME || V_CO);
    WHEN '江运华' THEN
      DBMS_OUTPUT.PUT_LINE('你好' || V_NAME || V_CO);
    
  --  ELSE
    --DBMS_OUTPUT.PUT_LINE('没有找到用户:' || V_ID);
    
  END CASE;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('输入的工号不存在' || V_ID);
END;

-------------------loop循环1+...10数据写入表----------------------------------
declare
type v_fanhui is record(v_ex1 fanhui.ex1%type,v_ex2 fanhui.ex2%type);
v_fh v_fanhui;
v_i int:=1;
v_sum int:=0;
begin
  loop
    v_sum:=v_sum+v_i;
    insert into fanhui(ex1,ex2) values(v_i,v_sum);
    exit when v_i=10;
    v_i:=v_i+1;
    end loop;
    dbms_output.put_line(v_fh.v_ex1||v_fh.v_ex2);
  end;
-----------------if--------------------------
declare
v_if_con number(10):=1;
begin
  <<basic_loop>>
loop
  dbms_output.put_line('当前v_if_con变量的值是：'||v_if_con);
  v_if_con:=v_if_con+1;
  if v_if_con>5 then
    dbms_output.put_line('退出！当前V_if_con的值是'||v_if_con);
    exit basic_loop;
    end if;
    end loop;
    dbms_output.put_line('LOOP循环已经结束');


--------------wile..if--------------
DECLARE
  V_IF_CON NUMBER(10) := 1;
BEGIN

  WHILE V_IF_CON <5
  LOOP
    V_IF_CON := V_IF_CON + 1;
      DBMS_OUTPUT.PUT_LINE('当前v_if_con变量的值是：' || V_IF_CON);
      END LOOP;
  DBMS_OUTPUT.PUT_LINE('退出！当前V_if_con的值是' || V_IF_CON);
  DBMS_OUTPUT.PUT_LINE('LOOP循环已经结束');
END;

---------------cursor-----

declare
v_se varchar2(100);
v_ty varchar2(100);
cursor fh
is select CARD_NO,FULL_NAME from fanhui where rownum<10
and card_no=v_se;
begin 
  v_se:=&card_no;
  open fh;
  dbms_output.put_line('员工工号，姓名');
  loop
    fetch fh into v_se,v_ty; 
    exit when fh%notfound;
    dbms_output.put_line(V_SE|| V_TY);
    END LOOP;
    CLOSE FH;
    END;
    



---
DECLARE
  V_SE VARCHAR2(100);
  V_TY VARCHAR2(100);

BEGIN
  V_SE := &CARD_NO;
  DBMS_OUTPUT.PUT_LINE('员工工号，姓名');

  FOR COUR IN (SELECT CARD_NO,
                      FULL_NAME
                 FROM FANHUI
                WHERE ROWNUM < 10)
  LOOP
    DBMS_OUTPUT.PUT_LINE(COUR.CARD_NO || COUR.FULL_NAME);
  END LOOP;
END;


-------------游标 打印输出fanhui表中的姓名，工号
 
  DECLARE
    V_ID   FANHUI.CARD_NO%TYPE;
    V_NAME FANHUI.FULL_NAME%TYPE;
     cursor cur_s  is          --定义游标cur_s 
     SELECT FANHUI.CARD_NO,
           FANHUI.FULL_NAME
          FROM FANHUI ;
  BEGIN
    open cur_s; 
        LOOP
          fetch cur_s into v_id,v_name;
      DBMS_OUTPUT.PUT_LINE(V_ID || '   ' || V_NAME);
        exit when cur_s%notfound;
         END LOOP;
         close cur_s;
  END;

-------带参数游标----
DECLARE
  CURSOR FH(CD VARCHAR2) IS
    SELECT * FROM BA_PERSON_INFO WHERE CARD_FNO = CD;
  FH_H BA_PERSON_INFO%ROWTYPE;
BEGIN

  OPEN FH('20010530004373');
    
  FETCH FH INTO FH_H;
  DBMS_OUTPUT.PUT_LINE(FH_H.CARD_FNO || ' ' || FH_H.FULL_NAME || ' ' ||
                       FH_H.SEX || ' ' || FH_H.WORK_TYPE_NO);
  IF FH%NOtFOUND THEN
    DBMS_OUTPUT.PUT_LINE('输入有误');
  END IF;
  CLOSE FH;
END;


----输入批次号，查询出对应的金额
declare
V_DELIVERY_NO ST_DELIVERY_BATCH.DELIVERY_NO%TYPE;
v_tal NUMBER(30,8);
v_CONTRACT_NO st_contract.contract_no%type;
CURSOR COU_1/*(V_DELIVERY_NO VARCHAR2)*/
IS 

 SELECT SUM(BB.TOAL) AS v_tal,
         BB.CONTRACT_NO AS v_CONTRACT_NO
    FROM (SELECT BX.CONTRACT_NO,
                 (FO.QTY *
                 DECODE(NVL(EP.PRICE, '0'), '0', '0.000001', '0.000000',
                         '0.000001', EP.PRICE)) AS TOAL
            FROM BAS_DELIVERY_BOX_RELATION RE
            JOIN BAS_BOXDETAIL_INFO FO ON FO.BOX_NO = RE.BOX_NO
            JOIN BAS_PRICE_ERP EP ON EP.ITEM_NO = FO.ITEM_NO
                                 AND EP.ORG_ID = '635'
            JOIN ST_DELIVERY_BATCH CH ON CH.DELIVERY_NO = RE.DELIVERY_NO
            JOIN BAS_BOX_INFO BX ON BX.BOX_NO = RE.BOX_NO
            JOIN ST_CONTRACT CT ON CT.CONTRACT_NO = BX.CONTRACT_NO
           WHERE RE.DELIVERY_NO = V_DELIVERY_NO) BB 
   GROUP BY CONTRACT_NO;
        BEGIN
          V_DELIVERY_NO:='DA1411030068'; 
         open cou_1/*('DA1411030068')*/;
        
          loop
           fetch cou_1 into v_tal,v_CONTRACT_NO ;
            exit when cou_1%notfound;
         dbms_output.put_line(v_tal||','||v_CONTRACT_NO);
                 
        end loop;
       close cou_1;
         end;

------输入用户名，如果不存在则插入一条，存在则打印输出 
 declare
  spname varchar2(100);
  v_num number;
  v_no  fanhui.card_no%type;
  begin
    spname:=&name;
   select count(1) into v_num from fanhui where full_name=spname;
   if v_num=0 then
     v_no:='6092001797';
   insert into fanhui(card_no,full_name)values(v_no,spname);
    dbms_output.put_line('插入成功'||spname||' '||v_no);  
     elsif v_num>0 then
       dbms_output.put_line('存在'||spname||' '||v_no);  
       end if;
  end;

-------------------存储过程 ---------
create or replace procedure sql_pro1---存储过程名字,如果存在，则替换
is
begin
  ---执行部分
  insert into fanhui values('123','AAA');
end;

-------------输入员工姓名，如果存则删除，不存在则插入
CREATE OR REPLACE PROCEDURE SP_FANHUI
(
  SP_NAME VARCHAR2,
  SP_CANO VARCHAR2
) IS
  V_NUM  NUMBER;
  V_NO   VARCHAR(100);
  V_NAME VARCHAR(100);
  ---定义变量
BEGIN
  V_NO   := SP_CANO;
  V_NAME := SP_NAME;
  ---输入员工姓名，如果存则删除，不存在则插入
  SELECT COUNT(1) INTO V_NUM FROM FANHUI WHERE FULL_NAME = SP_NAME;

  IF V_NUM > 0 THEN
      DELETE FROM FANHUI WHERE FULL_NAME = SP_NAME;
      DBMS_OUTPUT.PUT_LINE('删除成功');
    else
      insert into fanhui(card_no,full_name,ldaty) values(v_no,v_name,sysdate);
      dbms_output.put_line('插入成功'||v_name||' '||v_no);
       END IF;
        EXCEPTION WHEN OTHERS THEN
        ROLLBACK;
    RETURN;
END;


---调用该过程
exec 过程名(参数值..，参数值2..)
call 过程名(参数值..，参数值2..)


=========随机函数========
v_if_con:=dbms_random.value(100,200);
    end;

---------------建立表fanhui，把另一个表的值赋予给这个表----------------
create table fanhui
as select t.CARD_NO,t.FULL_NAME,t.WORK_TYPE_NO
 from ba_person_info t where rownum<=10  and t.CARD_No like '6%';

insert into 表名 （字段，字段2）select from 字段1，字段2 from表明
update 表明 set 字段名1 select 字段名 from表名,字段名1，select 字段名2 from 表名
---------------alter table fanhui---------

SELECT  UPPER(SUBSTR('abc',1,1)) FROM DUAL---大写，取第一位
select lower(substr('ABC',2,LengTh('ABC')-1)) FROM DUAL;--截取长度，小写
select lower(substr('ABC',2,LengTh('ABC')-1)) || UPPER(SUBSTR('abc',1,1))FROM DUAL;--合并
select replace (full_name,'A','钟' )from fanhui--替换 A替换成钟
alter table fanhui
rename alter table\column 表名\字段名 to 新字段名
alter table fanhui1 rename  to fanhui--修改表名
add 字段 varchar2(100)
-------------给用户授权------------
1.grant create session to test;--赋予create session的权限
2.grant create table,create view,create trigger, create sequence,create procedure to test;--分配创建表，视图，触发器，序列，过程 权限
3.grant unlimited tablespace to test; --授权使用表空间
--截断日期trunc
select sysdate,trunc(sysdate,'dd') from dual; 

select BILL_ID,l.CREATE_DATE from ST_JOURNAL L 
where trunc(l.CREATE_DATE, 'dd')=to_date('2015-9-11','yyyy-mm-dd')
等同于
select BILL_ID,l.CREATE_DATE from ST_JOURNAL L 
where tto_date(to_char(l.CREATE_DATE,'yyyy-mm-dd'),'yyyy-mm-dd')=to_date('2015-9-11','yyyy-mm-dd')
CAST字符转换成int类型
cast( 列名/值 as 数据类型 )
SELECT CAST('123' AS int) as result from dual;
select cast(empno as varchar2(10)) as empno from emp;

select * from user_tab_privs;--查询当前用户所以表的权限
grant select on tab to wangwu //对表tab的查询条件授予给wangwu用户 
grant all on tab to wangwu //对表tab的所有操作授予给wangwu用户 
revoke select on tab from wangwu 
revoke all on tab from wangwu 
grant ceate any table to lisi with admin option; //李四用户可以将此权限传递给其它用户 
create(drop) role myrole 
grant create session to myrole 
grant create table to myrole 
grant myrole to lisi 
grant select on tabA to lisi with grant option; 

---排除异常
  IF vn_exist = 0
         THEN
            raise_application_error (-20000, '单板信息不存在，请重新输入');
            RETURN;
         END IF;
-----------
FOR XY IN(SELECT 一个查询处理的值)

LOOP
//对查询出来的值循环进行处理，一般是保存insert
END LOOP;



for cur in(...select ...) loop (循环体）...更新


---排序
null值，如果是ASC升序则排在最后，DESC降序则排在最前 
select * from fanhui fh order by fh.ldaty asc Nulls first
select * rom fanhui fh  order by  fh.ldaty desc nulls last
--union all 跟unio
A表 id nam               B表 id nam 
    1   小明             1   小明       
    2   小红             2   小红
    3   小丽             3   小丽
    4   小明             4   小黑
1.  select id nama A
    union all
    select id nama B


1 1   小明                  
2 2   小红             
3 3   小丽            
4 4   小明            
5 1   小明  
6 2   小红
7 3   小丽
8 4   小黑


2.  select id nama A
    union 
    select id nama B

1 1   小明                  
2 2   小红             
3 3   小丽            
4 4   小明 
5 4   小黑

---查询第5-10行的数据 (分页逻辑)
select * from 
(select rownum rn, de.* from (select * from app_wms.st_summary_barcode b where rownum<10)de )
where rn>=5 and rn<=7;

--创建临时表Linshi
1.create global temporary table Linshi 
 as select* from fanhui fh where fh.full_name='范会'

2. select * from  Linshi 
--往临时表插入数据
3.insert into  Linshi  select* from fanhui fh where fh.full_name='范会'--不要commit;
--把临时表插入到永久表
4.insert into fanhui fh select * from Linshi ---commit临时表数据被清空;
5.select * from fanhui 

删除临时表：
 
truncate table  pic_temp ;
drop table  pic_temp ;

---内，左，右链接
 select  B.箱号，B.箱长，B.宽，B.高，（长 * 宽 * 高） as 体积
 from a,b where a.id=b.id(内连接)/a.id(+)=b.id(右连接)/a.id=b.id(+)(左连接)

--新建一个表，把一个表的数据插入到新表

把一个表中的数据插入到另一个表中去 
insert into 表2(字段名1，字段名2,.....) 
select 字段1，字段2,... 
from 表1 
where ...
----------------------------------------

存储过程1不存在插入，存在删除
CREATE OR REPLACE PROCEDURE SP_FANHUI
(
  SP_NAME VARCHAR2,
  SP_CANO VARCHAR2
) IS
  V_NUM  NUMBER;
  V_NO   VARCHAR(100);
  V_NAME VARCHAR(100);
  ---定义变量
BEGIN
  V_NO   := SP_CANO;
  V_NAME := SP_NAME;
  ---输入员工姓名，如果存则删除，不存在则插入
  SELECT COUNT(1) INTO V_NUM FROM FANHUI WHERE FULL_NAME = SP_NAME;

  IF V_NUM > 0 THEN
      DELETE FROM FANHUI WHERE FULL_NAME = SP_NAME;
      DBMS_OUTPUT.PUT_LINE('删除成功');
    else
      insert into fanhui(card_no,full_name,ldaty) values(v_no,v_name,sysdate);
      dbms_output.put_line('插入成功'||v_name||' '||v_no);
       END IF;
        EXCEPTION WHEN OTHERS THEN
        ROLLBACK;
    RETURN;
END;

-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-----输入姓名，判断，如果不存在则插入， 存在就删除
declare
v_carno varchar2(100);
v_full_name varchar2(100);
v_num number ;

begin
  v_full_name:='范会';
--创建临时表Linshi
create global temporary table Linshi  as select* from fanhui fh where fh.full_name='范会';
 
--往临时表插入数据
insert into  Linshi  select* from fanhui fh where fh.full_name='范会';
 ---查询名字叫范会的记录
select count(*) into v_num from fanhui where full_name=v_full_name;

--判断是否存在
if(v_num=0) then
  --不存在则插入
  insert into fanhui 
(CARD_NO,
FULL_NAME,
EX1,
EX2,
LDATY
) 
select * from Linshi where full_name=v_full_name;
--- 影响行，是否插入成功
IF SQL%ROWCOUNT >0 
 THEN
     dbms_output.put_line('插入成功') ;
     

 else
     dbms_output.put_line('没有插入数据') ;
      END IF;

--存在  
else 
  delete  fanhui where full_name=v_full_name;
    end if;
  
---影响行，是否删除成功  
IF SQL%ROWCOUNT >0 
 THEN
     dbms_output.put_line('删除成功') ; 
 else
     dbms_output.put_line('删除失败');  
      END IF;
    end;
----------------------------------------------------------------------------------- 
-----------------------------------------------------------------------------------
---编写一个函数
输入雇员名字，返回工资
create or replace function sp_fun1(spName varchar2)---参数
return number is yearsal number(7,2)--返回类型
begin
  select sal*12+nvl(comm,0)*12 into yearsal from emp where ename=spName;
  return yearsal;
  end;
--调用函数
var abc number;
call sp_fun2('scott')into abc;
----------------------------------------------------------------------------------- 
-----------------------------------------------------------------------------------
--创建包：用于逻辑上组合过程和函数，有包体，

----创建一个包：sp_package
create package sp_package is
--声明一个存储过程
procedure udpate_sal(name varchar2,newsal number);
--声明一个函数
function annual(name varchar2) return number;
end;

----给sp_package实现包体
create package body sp_package is
procedure udpate_sal(name varchar2,newsal number);
is
begin
  update emp set sal=newsal where ename=name;
    end;
 function annual(name varchar2) return number is
 annual_salary number;
 begin
   select sal*12+nvl(comm,0)*12 into annual from emp
   where ename=name;
   return annual_salary;
   end;
   end;

--调用
call  sap_package.uppate_sal('sccrt');

-------------------------------------------------------------------
-------------------------------------------------------------------
条件分支语句
if ...then ...else...end if;
if...then ...elsif then...else...end if;
case when...then
while ...loop...end loop...
-------------------------------------------------------------------
-------------------------------------------------------------------

 ----定义一个游标，查询某一个箱明细,箱号，代码，数量
 declare
 v_box_no st_summary_item.box_no%type;
 v_item_no st_summary_item.item_no%type;
 v_qty st_summary_item.qty%type;
 no_date_found exception;--声明变量异常
 cursor sum_item_cur --定义一个游标变量
 is 
 select  box_no,item_no,qty from st_summary_item where box_no=v_box_no;
  begin
  v_box_no:='320012021508310345';
  open sum_item_cur;--打开游标
  dbms_output.put_line('箱号                     代码   数量');
  loop ---循环取出游标数据
    fetch sum_item_cur into v_box_no,v_item_no,v_qty;
    exit when sum_item_cur%notfound;--全部取出就退出循环
    dbms_output.put_line(v_box_no||'  '||v_item_no||'  '||v_qty );
    end loop;
    close sum_item_cur;
    ---异常处理
    exception when no_date_found then
    dbms_output.put_line('没有这个箱');
    end;
    
-------------------------------------------------------------------
-------------------------------------------------------------------
cursor 游标名for update
更新 update 表明 set...  where current of
删除 delete from 表明 where current of 游标名

-----定义一个游标，fanhui,根据不同条件修改字段ex1的值
declare
v_ex1 fanhui.ex1%type;
v_full_name fanhui.full_name%type;
no_date_found exception;
cursor fh_ex1_cur is
select  ex1,full_name from fanhui
for update;--更新语法
begin
open fh_ex1_cur;
loop
fetch fh_ex1_cur into v_ex1,v_full_name;
exit when fh_ex1_cur%notfound;
case
  when v_full_name='范会' then
    update fanhui set ex1='test_cur' where  current of fh_ex1_cur;--必须使用 where  current of 游标名
  when v_ex1='1' then
    update fanhui set ex1='test_cur' where  current of fh_ex1_cur;
    else
      select ex1 into v_ex1 from fanhui where full_name='范会';
      end case;
      end loop;
      close fh_ex1_cur;
---影响行，是否删除成功  
IF SQL%ROWCOUNT >0 
 THEN
     dbms_output.put_line('修改成功') ; 
 else
     dbms_output.put_line('修改失败');  
      END IF;
  
      exception when no_date_found then
      dbms_output.put_line('没有符合条件的数据');
  end;

------------------------------------------------------------------------------
死循环了？为什么
-----定义一个游标，删除数据  语法：delete from 表明 where current of 游标名
declare
v_ex2 fanhui.ex2%type;
v_ex1 fanhui.ex1%type;
no_data_found exception;
cursor fanhui_Drop_cur is
select ex2,ex1  from fanhui for update;
begin
 open fanhui_Drop_cur;
 fetch fanhui_Drop_cur into v_ex2,v_ex1;
 while fanhui_Drop_cur%found 
loop 
   if (v_ex2 ='55' and v_ex1='10') then 
  delete from fanhui  where current of fanhui_Drop_cur;
  
  end if;
  fetch fanhui_Drop_cur into v_ex2,v_ex1 ;
  end loop;
  close fanhui_Drop_cur;
  if sql%rowcount>0 then
      dbms_output.put_line('删除成功');
      else
      dbms_output.put_line('删除失败');
      end if;
      exception when no_data_found then
        dbms_output.put_line('异常');
  end; 

----------------------------------------------------------------------------------
游标for 循环
declare
v_ex2 fanhui.ex2%type;
v_ex1 fanhui.ex1%type;
no_data_found exception;

begin
for cur in (select * from fanhui)
  loop
    delete from app_wms.fanhui t where  t.ex1='10' and t.ex2='50';
    if sql%rowcount >0 then
      dbms_output.put_line('成功！');
      else
        dbms_output.put_line('失败！');
      end if;
      end loop;
  end; 
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
loop...end loop循环


---每次循环自增1，直到变量值大于5
declare
v_num number(8):=1;
begin
  <<basic_loop>>--loop标签
  loop
  dbms_output.put_line('当前v_num变量的值是：'||v_num);
  v_num:=v_num+1;
  if v_num>5 then
    dbms_output.put_line('退出，当前v_num的值是：'||v_num);
    exit  basic_loop;--退出循环
    end if;
    end loop;
  dbms_output.put_line('loop循环已经结束');
  end;
  ------exit ..when 退出------------------
  declare 
  v_num number(8):=1;
  begin
    <<tc_loop>>
    loop
      dbms_output.put_line('当前V_NUM的变量的值是：'||v_num);
      v_num:=v_num+1;
      exit tc_loop when v_num>5;
      end loop;
      dbms_output.put_line('退出!当前V_num的值是：'||v_num);
      dbms_output.put_line('loop循环已结束！');
    end;



---分页
DECLARE
  RNUM      NUMBER := 12;--用户输入每页显示数量
  YEMA      NUMBER := 4;---第几页
  S_UM      NUMBER;---截止行
  S_TAR     NUMBER;---开始行
  num_ber   NUMBER;---行数
  
  V_BOX_NO  APP_WMS.ST_SUMMARY_BARCODE.BOX_NO%TYPE;
  V_NAME_NO APP_WMS.ST_SUMMARY_BARCODE.ITEM_NO%TYPE;
  CURSOR FENYE IS
    SELECT BOX_NO,
           ITEM_NO,
            rownum
      FROM (SELECT ROWNUM RN,
                   DE.*
              FROM (SELECT *
                      FROM APP_WMS.ST_SUMMARY_BARCODE B
                     WHERE ROWNUM <= S_UM) DE)
     WHERE RN >= S_TAR;
BEGIN
  S_UM  := RNUM * YEMA;
  S_TAR := (RNUM * (YEMA - 1))+1;
   OPEN FENYE;
   
  LOOP
     FETCH FENYE
      INTO V_BOX_NO,
           V_NAME_NO,
           num_ber ; 
    EXIT WHEN FENYE%NOTFOUND; --全部取出就退出循环
    DBMS_OUTPUT.PUT_LINE(num_ber ||' '||V_BOX_NO   ||'  '||    V_NAME_NO||' ');
  END LOOP;
  CLOSE FENYE;
END;

----函数：输入名字，返回工号
CREATE OR REPLACE FUNCTION TEST(V_NAME IN VARCHAR2) RETURN  number 

IS
CA_NO number;
V_COUNT NUMBER;
BEGIN

SELECT COUNT(*) INTO V_COUNT FROM FANHUI WHERE full_name = v_name; 
SELECT FANHUI.CARD_NO INTO CA_NO FROM FANHUI WHERE full_name = v_name; 

IF V_COUNT > 0 THEN 
RETURN CA_NO ;

END IF;
EXCEPTION
when NO_DATA_FOUND THEN
 DBMS_OUTPUT.PUT_LINE('没有查到数据');
END;

---****----
declare 
VS_OUNT number;
begin
FOR CUR IN (
SELECT T.CONTRACT_NO FROM  (Select sc.contract_no,count(*) from app_wms.st_contract sc group by sc.contract_no having count(*)>1 ) T)
LOOP
  SELECT COUNT(*) INTO VS_OUNT FROM 
  (SELECT SC.SALE_CODE,SC.PROJECT_NO FROM APP_WMS.ST_CONTRACT SC WHERE SC.CONTRACT_NO=CUR.CONTRACT_NO
  group by SC.SALE_CODE,SC.PROJECT_NO)S;
 IF   VS_OUNT > 1 THEN
   DBMS_OUTPUT.put_line(CUR.CONTRACT_NO);
   
 END IF;
  
END LOOP;
end;



  ----输入数字，如果数字为1，那么打印输出你的性别是女，如果数据为2 ，你的性别是男    
  DECLARE
    NUM_N  VARCHAR2(50);
    NUM_N2 VARCHAR2(50);
  BEGIN
    NUM_N2 := '1';
    NUM_N  := CASE NUM_N2 
    WHEN '1' THEN '我的性别是女' 
     WHEN '2' THEN '我的性别是男'
        ELSE ''-- 必须要有else 
  END  ; --end 结束
   DBMS_OUTPUT.PUT_LINE(NUM_N);
    end;



---插入数据
 declare
v_sql varchar2(2000);--定义动态
v_tablename varchar2(200);--定义动态表名
   begin 
    v_tablename:='APP_WMS.ST_FINANCE_CARRYOVER' ;
   v_sql:= 'INSERT INTO ' ||v_tablename|| '
  VALUES
  (''7897897'',
   ''SS4TR2009122401DSL'',
   ''1ED52FE791267031E0538A111E0A66F2'',
   NULL,
   SYSDATE,
   ''ECC'',
   ''604572027'',
   ''604572027'',
   SYSDATE,
   ''604572027'',
   SYSDATE)';
   execute immediate v_sql;
 end ;
 --第几行到第几行分页
 select * from (select rownum r1, fa.* from fanhui fa ) where r1 between 10 and 20

-------********------------
表名可用变量，但一般需要用到动态sql，举例如下：
declare 
v_date varchar2(8);--定义日期变量
v_sql varchar2(2000);--定义动态sql
v_tablename varchar2(20);--定义动态表名
begin 
select to_char(sysdate,'yyyymmdd') into v_date from dual;--取日期变量
v_tablename := 'T_'||v_date;--为动态表命名 
vsql := 'create table '||v_tablename||' (id int, name varchar2(20))';--为动态sql赋值
 dbms_output.put_line(v_sql);--打印sql语句 
execute immediate v_sql;--执行动态sqlend;



-------------orcale函数------
--创建同义词
create synonym table1 for user1.table1 
--截取数据
select * from (select * from st_journal j order by j.last_updated_date desc) a where rownum < 82
---比较数据大小，返回最大值
select greatest (to_date('2015-02-06 10:25:53','yyyy-mm-dd  hh24:mi:ss') ,sysdate) from dual
---比较数据大小，返回最小值
select least (to_date('2015-02-06 10:25:53','yyyy-mm-dd  hh24:mi:ss') ,sysdate) from dual
--如果价格为空，则返回0，如果价格为0.000000，则价格返回0.000001，如果价格0，则返回0.000001
decode(nvl(p.price,'0'),'0.000000','0.000001','0','0.000001',p.price) as price, 
--case语句
select  (case  when  DUMMY='X'  then  0  else  1  end)  as  flag  from  dual;  
语法： 
instr(sourceString,destString,start,appearPosition) 

instr（'源字符串' , '目标字符串' ,'开始位置','第几次出现'）
select instr('dfdfdfd','fd',1,3) from dual 返回位置 

条件ORG_LEVEL=4且不 为空，或者ORG_LEVEL=5且不为空
and ((RE.ORG_LEVEL = '4' AND RE.THIRD_ORG_NO IS NULL) OR
       (RE.ORG_LEVEL = '5' AND RE.THIRD_ORG_NO IS NOT NULL))

--四色五入
.ROUND和
TRUNC
select trunc(124.1666,-2) trunc1,trunc(124.16666,2) from dual; 
------------输出--------
100 124.16  

--取整数 FLOOR
select floor(2345.67) from dual; 

--显示当前月份的第几周
select TO_CHAR(SYSDATE, 'iw') from dual


select ltrim('123st t','2') from dual;
select length(123) from dual;
select lower('Aa') FROM DUAL;
select lpad('123',14,'A') from dual

select trim('1213st t') from dual; ---去空格
select RTrim('1213st t') from dual;--右边开始匹配截取
select LTrim('1213st t') from dual;--左边开始匹配截取
求两个记录的交集   minus 
select   *   from   table   where   条件1  minus  
select   *   from   table   where  条件2

DBlink拉数据
truncate table app_wms.bas_box_info;
insert into  app_wms.bas_box_info
select * from app_wms.bas_box_info@db_scm_prod;

--查询当前用户下所有的表
select table_name from user_tables；

PRAGMA AUTONOMOUS_TRANSACTION 自制事物
http://blog.sina.com.cn/s/blog_4f925fc30100h8ld.html
  
高级  设置
;10.*.*.*;*.zte.com.cn;192.168.*.*;*.zte.intra;*.zteict.com
10.*;*.zte.com.cn;192.168.*.*