oracel 数据库使用plsql（创建表，序列，存储过程，触发器） 查询条件为空

--删除表
drop table userinfo;

drop table usermes;

--删除序列
drop sequence seq_uid;

drop sequence seq_mid;


--建表
create table userinfo
(
   userid number(10) primary key,
   
   username nvarchar2(20) not null,
   
   userpwd nvarchar2(20) not null,
   
   usertitle nvarchar2(50),
   
   usermes nvarchar2(100)
    
);

create table userMes
(
    umid integer primary key,
    userid number(10) references userinfo(userid),
    umname nvarchar2(20)
);


--建立序列
create sequence seq_uid;

create sequence seq_mid;


--查询表
select * from userinfo;

select * from userMes;

--查询序列
select seq_uid.nextval from dual;

select seq_mid.nextval from dual;


--创建触发器(将序列绑定ID自增)（如果存在就替换）

create or replace trigger userinfotiger

before insert on usermes

for each row
  
begin 
  select seq_mid.nextval into :new.umid from dual;
end;

--添加数据
insert into userinfo values(seq_uid.nextval,'qq','123','qqtitle','this is a qq ,password is 123');
insert into userinfo values(seq_uid.nextval,'ww','456','wwtitle','this is a ww ,password is 456');
insert into userinfo values(seq_uid.nextval,'ee','789','eetitle','this is a ee ,password is 789');
insert into userinfo values(seq_uid.nextval,'aa','147','aatitle','this is a aa ,password is 147');
insert into userinfo values(seq_uid.nextval,'ss','159','sstitle','this is a ss ,password is 159');

--有选择的插入数据
insert into usermes(userid,umname) values(1,'finalshuju');

insert into usermes values(seq_mid.nextval,2,'bbbbb')
insert into usermes values(seq_mid.nextval,2,'bbbbb');
insert into usermes values(seq_mid.nextval,3,'ccccc');
insert into usermes values(seq_mid.nextval,4,'ddddd');

--提交事务
commit;

--分页查询
select * from (select userinfo.*, rownum rn  from (select * from userinfo) userinfo)  where rn between 1 and 3;


--创建一个添加数据的存储过程(如果存在就替换)
create or replace procedure proc_createuserinfo
(
    username in varchar2 default null,
    userpwd  in varchar2 default null,
    usertitle  in varchar2 default null,
    usermes  in varchar2 default null,
    uusermes in varchar2 default null,
    result   out number
)
is
   uuid number:=seq_uid.nextval;

begin 
  insert into userinfo(userid,username,userpwd,usertitle,usermes) values(uuid,username,userpwd,usertitle,usermes);
  insert into usermes(userid,umname) values(uuid,uusermes);
commit;
result:=sqlcode;
end;  

--sqlcode返回为0即为正常执行，在操作数据库时，可以根据这个判断是否操作成功

--调用存储过程
declare
  v_shuchu number;

begin
  proc_createuserinfo('qq','yy','vv','ww','ee',v_shuchu);
  --输出信息，在sql右边的输出中可以看到输出的信息
  dbms_output.put_line(v_shuchu);
end;


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

查询条件为空

select * from  表名 where 字段 is null