plsql 实现表的创建以及增删改查。

plsql 实现表的创建以及增删改查。

 
  --创建表  
create table music(
id  number  primary key,--歌曲id
name  varchar2(10),--歌曲名称
singer varchar2(10),--歌手名称
album varchar2(10),--所在专辑
time varchar2(10)--年份
);    

    ---drop table music;

---------------------------------
--创建序列
create or sequence seq_music  increment  by 1 start  with 1  nomaxvalue nocycle;
----------------------------------------------------------------------------

----------------------------------------------------------------------------------修改进程
create  or   replace procedure update_music(v_id music.id%type) is  
begin
    update music  set name='笑哈哈',singer='赵本山',album='sowhat',time= '2016'  where id=v_id;  
end;
   --   exec update_music(3);

   ---show error




----------------------------------------------------------------------------------插入进程
create  or   replace procedure insert_music(insert_num number) is  
    v_num  number:=0;
begin
    loop
      v_num:=v_num+1;
      insert into music  values(seq_music.nextval,'呵呵哒','周星','what','2015');
       dbms_output.put_line('恭喜插入');
      exit when  v_num=insert_num;
   end loop;
end;
   --   exec insert_music(3);
   ---show error

   -----------------------------------------------------------------查询 进程
create  or   replace procedure query_music is  ----(query_num number)先不带参数，
    --定义变量接受数据
    v_name music.name%type;
    v_singer music.singer%type;
     v_album music.album%type;
      v_time music.time%type;
      v_id  music.id%type;
    begin  --查询数据
      select    name,singer,album,time,id into  v_name,v_singer,v_album,v_time,v_id      from music where id=&v_id;--获取输入
      dbms_output.put_line(v_id||'    '||v_name||'     '||v_singer||'    '||v_album||'    '||v_time);
    end;

    --   exec query_music;  
   ---show error
   ---------别忘了      commit;
   --------------------------------------------
      -----------------------------------------------------------------查询多条的 进程  分页

create  or   replace procedure query_music(v_minnum  music.id%type ,v_maxnum  music.id%type ) is  ----(query_num number)先不带参数，
    --定义变量接受数据
    v_name music.name%type;
    v_singer music.singer%type;
     v_album music.album%type;
      v_time music.time%type;
      v_id   music.id%type;


    cursor cursor_music is
   select *from 
   (  select rownum  rn,a.* from
         (select  id eid,name ename  from music    order  by id  ) a      
   )where rn>= v_minnum and rn<= v_maxnum;
   v_onemusic cursor_music%rowtype;
 begin
     --获取数据
     open cursor_music;
     --遍历获取数据
     loop
       --遍历游标
       fetch  cursor_music  into v_onemusic;
         exit when cursor_music%notfound;
           --打印数据
       dbms_output.put_line(v_onemusic.rn||'    '||v_onemusic.eid||'    '||v_onemusic.ename||'    '||v_onemusic.ename||'    '||v_onemusic.ename);
    end loop;
end;
    ------------>>>>   exec query_music(4 ,7 ) ;
    ---show error;   
   ---------别忘了      commit;
-----------------------------------------------------------------删除 进程------------
 create  or   replace procedure delete_music(v_id music.id%type)  is
   --定义变量
   begin
   --删除数据
   delete from music where id=v_id;
   --输出
   dbms_output.put_line('恭喜删除');
   end;
    -- exec delete_music(47);

   ---show error

-------------------------