Oracle的序列、存储过程、游标

序列Sequence

创建序列

create sequence sequenceName
minvalue 最小值
maxvalue 最大值
start with 开始值
increment by 间隔值
cycle/nocycle 是否循环
cache 缓存个数 /nocache 

使用序列

select ht_test_id.nextval from dual; --获取序列的下一个值
select ht_test_id.currval from dual; --获取序列的当前值

修改序列

alter sequence sequenceName
increment by 间隔值
nomaxvalue/maxvalue 最大值 
nominvalue/minvalue 最小值
nocycle/cycle
cache 缓存数/nocache

--序列的修改不能变更初始值 start with 

查看所有的序列

select * from user_sequences;    --当前用户创建的所有序列
select * from all_sequences;    --当前用户可以使用的所有序列
select * from dba_sequences;    --dba权限下的所有序列

存储过程

创建

create or replace procedure()--无参数时可以省略括号
as/is     --相当于delcare，不可以省略
... --声明变量、游标等的地方
begin
...
end;

带参数、变量的例子

create or replace procedure test_pro(m1 varchar2,
                                     m2 out varchar2,
                                     m3 in out varchar2) 
--注意，定义参数时，参数仅仅需要指定类型varchar2就可以了，不要指定其类型的大小，比如varchar2(10)
as
  p1 varchar2(10); --定义变量时，可以指定类型的大小
  p2 varchar2(10);
  p3 varchar2(10);

begin
  p1 := 'n1';
  p2 := m1;
  dbms_output.put_line(p1);
  dbms_output.put_line(p2);
end;

游标的例子

create or replace procedure println_menu as
  cursor menu_msg is 
         select menu_name,url from menu where menu_id > 1000;
begin
  for msg in menu_msg 
    loop 
      dbms_output.put_line(msg.menu_name);
      dbms_output.put_line(msg.url);
    end loop;
end;

调用

plsql中调用

declare 
  a varchar2(10) := '900';
  b varchar2(10) := '800';
  c varchar2(10) := '700';
begin
  test_pro(a,b,c); --按照顺序进行传参
  test_pro(m3 => c,m1 => a,m2 => b); --不按顺序传参，和上面的结果一致
  println_menu();
end;

在命令窗口调用

--无参数时调用
exec println_menu;
--有参数时调用
exec test_pro('a','b','c');

在程序中调用

Session session = this.getHibernateTemplate().getSessionFactory().openSession();
Connection conn = session.connection();
try {
	String sql = "{call test_pro(?,?)}";
	CallableStatement call = conn.prepareCall(sql);
	call.setInt(1, htbaId);//设置第一个参数
	call.registerOutParameter(2, OracleType.STYLE_INT);//注册输出的参数
	call.execute();
	int re = call.getInt(2);
	if (re == 1) {
		flag= true;
	}
} catch (Exception e) {
	e.printStackTrace();
} finally {
	if (conn != null) {
		try {
			conn.close();
			session.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}

存储过程中调用存储过程

create or replace procedure println_menu as
  cursor menu_msg is 
         select menu_name,url from menu where menu_id > 1000;
begin
  test_pro('a','b','c'); --直接调用就可以
  for msg in menu_msg 
    loop 
      dbms_output.put_line(msg.menu_name);
      dbms_output.put_line(msg.url);
    end loop;
end;

存储过程删除

DROP PROCEDURE  test_pro;

游标的使用及遍历

1、显式地使用游标 for - in - loop 

declare 
cursor info is select menu_name,url from menu;
begin 
  for item in info loop
    dbms_output.put_line('menu_name::' || item.menu_name || '    url::' || item.url);
    end loop;
end;

注意：for - in中后面的loop必不可少

2、隐式地使用游标for - in - loop 

begin
  for item in (select menu_name,url from menu) 
    loop
       dbms_output.put_line('menu_name::' || item.menu_name || '    url::' || item.url);
    end loop;
end;

3、loop - exit when - fetch

declare
  cursor info is
    (select menu_name, url from menu where menu_id > 10000);
  item info%rowtype;  --定义变量，和游标的类型（单行的类型一致）
begin
  open info;
  loop
    fetch info
      into item;
    exit when info%notfound; 
    --info%notfound 游标只有取出后才能返回的状态，就是说最近获取值的状态
    dbms_output.put_line('menu_name::' || item.menu_name || '    url::' ||
                         item.url);
  end loop;
end;

4、fetch - (while - loop - fetch)

declare 
cursor info is (select menu_name,url from menu where menu_id > 10000);
item info%rowtype;
begin
    open info;
    fetch info into item;
    while info%found 
     loop 
        dbms_output.put_line('menu_name::' || item.menu_name || '    url::' ||
                         item.url);
        fetch info into item;
     end loop
    close info;
end;

5、fetch - bulk collect  - exit - forall - in

declare
  cursor info is(
    select menu_name, url from menu where menu_id >= 1000); --定义游标
  type item_table_type is table of info%rowtype;
  item item_table_type;

begin
  open info;
  loop
    fetch info bulk collect    
      into item limit 100;    --批量从游标抓取数据到变量中去
    exit when item.count = 0;
    forall i in item.first .. item.last 
    --forall in 遍历使用，后面直接跟dml语句，否则报错
      insert into te_menu values (item(i).menu_name, item(i).url);
  end loop;
  close info;
end;