操作数据常用用方法

序列

SQL> create sequence seq_id 

  2  increment by 1
  3  start with 1
  4  nomaxvalue;

 

触发器

SQL>create or replace trigger tri_userid   

  2  before insert on userinfo
  3  for each row
  4  declare new_key number;
  5  begin
  6    select seq_id.nextval into new_key from dual;
  7    :new.userid:=new_key;
  8* end;

 

存储过程

SQL>create or replace procedure insert_userinfo  

  2  (parm_username varchar2,
  3   parm_userpwd varchar2,
  4   parm_ename varchar2,
  5   parm_logo varchar2,
  6   parm_result out number)   --返回值  1:成功     0:失败
  7  as
  8    n number;
  9  begin
 10    select count(*) into n from userinfo where username=parm_username;
 11    if n=0 then
 12      parm_result:=1;
 13      insert into userinfo(username,userpwd,ename,logo)
 14      values(parm_username,parm_userpwd,parm_ename,parm_logo);
 15    else
 16      parm_result:=0;
 17    end if;
 18  end;

 

函数

SQL>  create or replace function  checklogin  

  2   (parm_username varchar2,
  3    parm_userpwd varchar2)
  4   return number
  5  as
  6    n number;
  7  begin
  8    select count(*) into n from userinfo where username=parm_username and userpwd=parm_userpwd;
  9    if n>0 then
 10      return 1;
 11    else
 12      return 0;
 13    end if;
 14   end;
 15  /

 

包

包头

SQL> create or replace package pkg_userinfo    

  2  as
  3   type cur_userinfo is ref cursor;
  4   procedure select_userinfo
  5   (parm_userid number:='%',
  6    parm_username varchar2:='%',
  7    parm_result out cur_userinfo
  8   );
  9  end pkg_userinfo;
 10  /
包体

SQL>create or replace package body pkg_userinfo
  2  as
  3   procedure select_userinfo
  4   (parm_userid number:='%',
  5    parm_username varchar2:='%',
  6    parm_result out cur_userinfo)
  7    as
  8     begin
  9       open parm_result for
 10        select logo,userid,username,ename,email from userinfo
 11        where userid like parm_userid and username like parm_username
 12          and username<>'Tsoft';
 13     END;
 14* END pkg_userinfo;