oracle 20130914

一、日志

循环：

Loop

       If条件 then

              Exit;

       Endif;

End loop;

 

Loop

       Exitwhen 条件;

End loop;

 

 

While 条件 loop

       …

End loop;

 

For 变量 in 1.. 10 loop

       …

End loop;

 

For 行类型变量 in 游标 loop

       …

End loop;

 

 

存储过程的参数类型

过程：

createorreplaceprocedure mypro(inid1 in Integer,outid2outInteger,inoutinoutInteger)is

BEGIN

dbms_output.put_line('inid1:'||inid1);--in:只能接收传值进来

dbms_output.put_line('outid2:'||outid2);-- out:只能返回值

dbms_output.put_line('inout:'||inout);-- in out:能接收值也能返回值

outid2:=500;

inout:=5001;

commit;

end;

 

 

 

 

 

 

 

函数定义：

createorreplacefunction myfunc(innum inoutnumber)returnnumberis--return之后的类型只能是数据库中的数据类型

  Resultnumber;

begin

  Result:=innum+10;

  innum:=500;

  return(Result);

end myfunc;

 

 

--过程调用

call mypro();

begin

mypro;

end;

--函数调用

declare

cc number;

begin

cc:=myfunc;

end;

 

 

游标：

Cursor 游标名称 is 查询语句;

使用游标之前必须先打开:open 游标名称;

使用完之后必须关闭游标。

 

动态执行sql

 EXECUTE IMMEDIATE ‘sql语句’;

 

异常

 

错误号

异常错误信息名称

说明

ORA-0001

Dup_val_on_index

违反了唯一性限制

ORA-0051

Timeout-on-resource

在等待资源时发生超时

ORA-0061

Transaction-backed-out

由于发生死锁事务被撤消

ORA-1001

Invalid-CURSOR

试图使用一个无效的游标

ORA-1012

Not-logged-on

没有连接到ORACLE

ORA-1017

Login-denied

无效的用户名/口令

ORA-1403

No_data_found

SELECT INTO没有找到数据

ORA-1422

Too_many_rows

SELECT INTO 返回多行

ORA-1476

Zero-divide

试图被零除

ORA-1722

Invalid-NUMBER

转换一个数字失败

ORA-6500

Storage-error

内存不够引发的内部错误

ORA-6501

Program-error

内部错误

ORA-6502

Value-error

转换或截断错误

ORA-6504

Rowtype-mismatch

宿主游标变量与 PL/SQL变量有不兼容行类型

ORA-6511

CURSOR-already-OPEN

试图打开一个已处于打开状态的游标

ORA-6530

Access-INTO-null

试图为null对象的属性赋值

ORA-6531

Collection-is-null

试图将Exists以外的集合( collection)方法应用于一个null pl/sql表上或varray上

ORA-6532

Subscript-outside-limit

对嵌套或varray索引得引用超出声明范围以外

ORA-6533

Subscript-beyond-count

对嵌套或varray索引得引用大于集合中元素的个数.

 

Eg：

DECLARE

BEGIn

    insertinto test1(id)values('abx') ;

    commit;

    exception--异常处理

    when zero_dividethen--捕获不能被0除异常

    dbms_output.put_line('0');

    when invalid_numberthen--不能将字符串转换到数值

    dbms_output.put_line('不能将字符串转成数值');

END;

 

 

程序包：

--1.包结构定义

createorreplacepackage t_package

is

  --定义过程

  procedure append_proc(tvarchar2,aoutvarchar2);

  --定义函数

  function append_fun(tvarchar2)returnvarchar2;

end;

 

 

--2.构件包主体

createorreplacepackagebody t_package

is

  --实现过程

  v_t varchar2(30);

  procedure append_proc(tvarchar2,aoutvarchar2)is

  begin

   a := t||'hello';

  end;

  --实现函数

  function append_fun(tvarchar2)

  returnvarchar2is

  begin

     v_t := t||'hello';

     return v_t;

  end;

end;

--使用程序包

declare

rs varchar2(20):='abc';

begin

t_package.append_proc('d',rs);

dbms_output.put_line(rs);

end;

 

 

自主事务：

Eg1：

createorreplaceprocedure mypro is

PRAGMAAUTONOMOUS_TRANSACTION;--自主事务执行

BEGIN

    insertinto test1(id)values(238);

    commit;--不会提交 insert intotttt(id) values(742);操作

end;

eg2：

createorreplaceprocedure mypro is

BEGIN

    insertinto test1(id)values(238);

    commit;--会提交 insert intotttt(id) values(742);操作

end;

 

createorreplaceprocedure mypro2 is

BEGIN

    insertinto tttt(id)values(742);

    mypro;

    rollback;

end;

 

数据库备份

 

--导入数据

imp username/password@orcl full=y file=保存有数据库文件的完整路径.dmp ignore=y

--导出数据

exp username/password@orcl file=存放数据库文件的完整路径.dmp

********************************************************************************************************

二、代码

--循环 loop 形式1
declare
sss  integer;--变量定义
sss1  integer;--变量定义
begin
sss:=1 ;--变量赋值
sss1:=1 ;--变量赋值
loop
      sss1:=1;
      loop
            insert into test1(id,money) values(sss,sss1);
            commit;
            sss1:=sss1+1;
            if sss1 >10 then
               exit;--退出循环
            end if;
      end loop;
      sss:=sss+1;
      if sss >10 then
         exit;
      end if;
end loop;
end;

--循环 loop 形式2
declare
sss  integer;--变量定义
sss1  integer;--变量定义
begin
sss:=1 ;--变量赋值
sss1:=1 ;--变量赋值
loop
      sss1:=1;
      loop
            insert into test1(id,money) values(sss,sss1);
            commit;
            sss1:=sss1+1;
            exit when sss1 >10; --当条件满足时退出循环
      end loop;
      sss:=sss+1;
       exit when sss >10;
end loop;
end;

-- for 循环 形式一
declare
sss   integer;--变量定义
sss1  integer;--变量定义
begin
sss:=1 ;--变量赋值
sss1:=1 ;--变量赋值
for sss in 1..10 loop --sss从1取到10
    for sss1 in 1..10  loop
            insert into test1(id,money) values(sss,sss1);
            commit;
      end loop;
end loop;
end;

-- for 循环 形式二 游标控制
declare
sss  integer;--变量定义
sss1  integer;--变量定义
myrow test1%rowtype;--行类型
mycol test1.money%type;--列类型
cursor mycur is select * from test1;
begin
sss:=1 ;--变量赋值
sss1:=1 ;--变量赋值
for myrow in mycur loop
    dbms_output.put_line(myrow.money);
end loop;
end;


declare
sss  integer;--变量定义
sss1  integer;--变量定义
myrow test1%rowtype;--行类型
mycol test1.money%type;--列类型
cursor mycur is select * from test1;--定义显式游标


begin
sss:=1 ;--变量赋值
sss1:=1 ;--变量赋值
IF not mycur%ISOPEN THEN  --游标没打开 返回值为 false  
   dbms_output.put_line('游标未打开');
END IF;
open mycur;--打开游标
IF mycur%ISOPEN THEN  -- 游标打开 true
   dbms_output.put_line('游标打开');
END IF;
fetch mycur into myrow;
while mycur%found loop --游标没到末尾 true
    dbms_output.put_line(myrow.money);
    dbms_output.put_line('ROWCOUNT:'||mycur%ROWCOUNT);--获取游标的当前行号
    if mycur%notfound then --判断游标是否到末尾 到末尾 true
        dbms_output.put_line('找不到');
    end if;
    fetch mycur into myrow;
end loop;
 if mycur%notfound then
        dbms_output.put_line('找不到');
 end if;
 close mycur;--打开游标
end;

--隐式游标
begin
insert into test1(id) values(20);
dbms_output.put_line(sql%rowcount);
if sql%isopen then
   dbms_output.put_line('游标打开');
end if;
if sql%found then
   dbms_output.put_line('游标有记录');
end if;
commit;
if sql%notfound then
   dbms_output.put_line('游标没有记录');
end if;
end;





DECLARE

TYPE ttt_curtype IS REF CURSOR RETURN ttt%ROWTYPE;--1.定义一个ref游标类型的返回值为ttt%ROWTYPE类型的 ttt_curtype类型
ttt_curvar ttt_curtype;--定义ttt_curtype类型的变量
ttt_curvar1 ttt_curtype;
ttt_rec ttt%ROWTYPE;
BEGIN
    if 2<1 then
       goto qq;--顺序控制 跳转到<<qq>> 标识
    else
       goto yyy;--顺序控制 跳转到<<yyy>> 标识
    end if;
      OPEN ttt_curvar FOR SELECT * FROM ttt;--打开游标时需要查询sql
      FETCH ttt_curvar INTO ttt_rec;
    dbms_output.put_line(ttt_rec.id);
      CLOSE ttt_curvar;
    <<qq>>
    dbms_output.put_line('qqq');
    OPEN ttt_curvar1 FOR SELECT * FROM ttt where id>2;--打开游标时需要查询sql
      FETCH ttt_curvar1 INTO ttt_rec;
    dbms_output.put_line(ttt_rec.id);
      CLOSE ttt_curvar1;
    <<yyy>>
    dbms_output.put_line('yyy');
 END;



DECLARE
BEGIn
    insert into test1(id) values('abx') ;
    commit;
    exception --异常处理
    when zero_divide then --捕获不能被0除异常
    dbms_output.put_line('0');
    when invalid_number then--不能将字符串转换到数值
    dbms_output.put_line('不能将字符串转成数值');
END;


--程序包
--1.定义程序包的结构
create package  mypackage is
function myfunc return number;
procedure mypro;
end mypackage;

--2.定义程序包体
create package body mypackage is
function myfunc return number is
    begin
         return 500;
    end;
procedure mypro is
begin
    insert into test1(id) values(159);
    commit;
end;
end;
--调用
begin
mypackage.mypro;
end;





--1.包结构定义
create or replace package t_package
is
  --定义过程
  procedure append_proc(t varchar2,a out varchar2);
  --定义函数
  function append_fun(t varchar2) return varchar2;
end;


--2.构件包主体
create or replace package body t_package
is
  --实现过程
  v_t varchar2(30);
  procedure append_proc(t varchar2,a out varchar2) is
  begin
   a := t||'hello';
  end;
  --实现函数
  function append_fun(t varchar2)
  return varchar2 is
  begin
     v_t := t||'hello';
     return v_t;
  end;
end;
--使用程序包
declare
rs varchar2(20):='abc';
begin
t_package.append_proc('d',rs);
dbms_output.put_line(rs);
end;