Oracle SQL脚本学习6

——————-目标 利用存储过程 游标 包 实现分页查询 并通过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;