——————-目标 利用存储过程 游标 包 实现分页查询 并通过java 调用 这个存储过程
select * from book;
——添加表字段
alter table book add bprice number(4,2);
desc book;
——修改表字段
alter table book rename column bplud to bpublish;
desc book;
——修改字段类型
alter table book modify bpublish varchar2(20);
desc book;
——删除表一个字段
alter table book add btemp number(4,2);
desc book;
alter table book drop column btemp;
desc book;
—————————————————————-
alter table book add bookId number;
———————编写过程
drop table book;
create table book (bookid number,bookname varchar2(20),bprice number(4,2));
desc book;
——-in 默认的可以省略 输入参数
create or replace procedure sp_book_pro(spbpBookid in number, spbpBookName in varchar2,spbpbprice in number)is
begin
insert into book values (spbpBookid,spbpBookName,spbpbprice);
end;
/
—pro_IP_158.java 调用 无返回信息
select * from book;
commit;
————————————–编写过程能返回书的信息
update book set bookid=2 where bprice=19.1;
desc book;
–create or replace procedure sp_book_proV(spbpbookid in number,spbpbookname out varchar2, spbpbprice out number)is
–begin
– select bookname into spbpbookname, bprice into spbpbprice from book where bookid = spbpbookid;
–end;
create or replace procedure sp_book_proV(spbpbid in number,spbpbookname out varchar2 , spbpprice out number )
is
begin
select bookname,bprice into spbpbookname,spbpprice from book where bookid = spbpbid;
end;
/
commit;
desc book;
select * from book;
–
select bookname , bprice from book where bookid=1;
————————-返回结果集
—包 –过程–
alter table book add bsorts number;
desc book;
select * from book;
insert into book values (4,’beiji4ng’,30.5,2);
insert into book values (5,’beij5ing’,30.5,3);
insert into book values (6,’beij6ing’,30.5,3);
insert into book values (7,’beij7ing’,30.5,2);
commit;
–创建一个 包 用 AS
–在包定义了一个 类型 游标类型的 即内部声明
create or replace package bysortpackage as
type bsp_cursor is ref cursor;
end bysortpackage;
/
commit;
—创建过程
create or replace procedure sp_bysortsbooks(spbooksorts in number, sp_cursor out bysortpackage.bsp_cursor)is
begin
open sp_cursor for select * from book where bsorts=spbooksorts;
end;
/
commit;
———————————————-分页显示
–oracle 分页 可以当一个模板使用
select t1.* ,rownum rn from(select * from book order by bookid desc) t1;
select t1.* ,rownum rn from(select * from book order by bookid desc) t1 where rownum<=5;
select * from (
select t1.* ,rownum rn from(select * from book order by bookid desc) t1 where rownum<=5
)
where rn>=2;
–分页的过程
create or replace package fenyepackage as
type fy_cursor is ref cursor;
end fenyepackage;
/
commit;
create or replace procedure pageno
(tableName in varchar2,
pagesize in number,—每页显示条数
pageNow in number, —显示第几页
myrows out number,–总记录数
mypagecounts out number, –总页数
p_cursor out fenyepackage.fy_cursor –返回的记录数
)is
–定义一个SQL 语句 字符串
v_sql varchar2(1000);
v_begin number:=(pageNow-1)*pagesize+1;
v_end number:=pageNow*pagesize;
begin
v_sql:=’select * from (select t1.* ,rownum rn from(select * from ‘||
tableName||’ order by bookid desc) t1 where rownum<=’||v_end||’)where rn>=’||v_begin;
–打开游标 和sq语句关联
open p_cursor for v_sql;
–计算 myrows mypagecounts
–组织一个sql语句
v_sql:=’select count(*) from ‘||tablename;
–执行sql并把返回的值 赋给myrows
execute immediate v_sql into myrows;
–计算mypagecounts mod取余
if mod(myrows,pagesize)=0 then
mypagecounts:=myrows/pagesize;
else
mypagecounts:=myrows/pagesize+1;
end if;
– 关闭游标
– close p_cursor;
end;
/
create or replace procedure pageno
(tableName in varchar2,
pagesize in number,
pageNow in number,
myrows out number,
mypagecounts out number,
p_cursor out fenyepackage.fy_cursor
)is
v_sql varchar2(1000);
v_begin number:=(pageNow-1)*pagesize+1;
v_end number:=pageNow*pagesize;
begin
v_sql:=’select * from (select t1.* ,rownum rn from(select * from ‘||
tableName||’ order by bookid desc) t1 where rownum<=’||v_end||’)where rn>=’||v_begin;
open p_cursor for v_sql;
v_sql:=’select count(*) from ‘||tablename;
execute immediate v_sql into myrows;
if mod(myrows,pagesize)=0 then
mypagecounts:=myrows/pagesize;
else
mypagecounts:=myrows/pagesize+1;
end if;
end;
/
—————————————————–分页完成
——————————————————–例外处理
declare
v_bname book.bookname%type;
begin
select bookname into v_bname from book where bookid=&gno;
dbms_output.put_line(‘bookname:’||v_bname);
exception
when no_data_found then
dbms_output.put_line(‘the bookid is not exist’);
end;
/
set serveroutput on;
–自定义开发
create or replace procedure ex_test(spno in number)
is
myex exception;
begin
update book set bookname=bookname+’ex’ where bookid=spno;
–sql%notfound 表示没有 update
–raise myex 触发myex
if sql%notfound then
raise myex;
end if;
exception
when myex then
dbms_output.put_line(‘no data’);
end;
/
create or replace procedure ex_test(spno in number)
is
myex exception;
begin
update book set bookname=bookname+’ex’ where bookid=spno;
if sql%notfound then
raise myex;
end if;
exception
when myex then
dbms_output.put_line(‘no data’);
end;
/
set serveroutput on;
exec ex_test(56);
select * from book;
alter table book modify bookname varchar2(20);
desc book;
转载于:https://my.oschina.net/wmsjhappy/blog/272540