——————-目标 利用存储过程 游标 包 实现分页查询 并通过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
发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/109812.html原文链接:https://javaforall.cn
【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛
【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...