序列
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;