pl sql项目演练--B2C商城项目

　　项目学习视频下载地址：点击下载

 

　　1.注册会员及找回密码模块

}该模块主要功能有注册会员和找回密码

}注册会员：所需信息主要有：登录号、密码、真实姓名、性别、密码问题、密码答案、Email、地址、电话、身份证

}找回密码：主要根据登录号、密码问题及密码答案，重置密码

此系统为在线点卡销售系统，用户信息还应该包括：用户角色，用户余额，用户状态

 

SQL> create table UserInfo
  2  (UserId varchar2(50) not null primary key,  --用户ID
  3  UserName varchar2(50) not null, --真实姓名
  4  PassWord varchar2(50) not null, --密码
  5  UserRole number(2), --角色
  6  Gender number(1) not null check(Gender in(0,1)),  --性别
  7  PassQuestion varchar2(50) not null,  --密码提示问题
  8  PassAnswer  varchar2(50) not null ,  --密码提示答案
  9  Email   varchar2(50) not null,  --电子邮箱
 10  TelNO   number(11) not null,    --电话号码
 11  Address varchar2(50),          --联系地址
 12  IdCardNO  number(18),          --身份证号
 13  Money   number,                --用户余额
 14  UserState number               --用户状态
 15  );

 创建用户注册存储过程完成用户注册

CREATE OR REPLACE PROCEDURE INSERTINFO_PROC 
(
  UserId VARCHAR2 ,
  Username varchar2,
  password varchar2,
  Gender number,
  PassQuestion varchar2,
  PassAnswer varchar2,
  Email varchar2,
  TelNo varchar2,
  Address varchar2 :=null,
  IdCardNo varchar2,
  UserRole number:=5,
  Money number :=0,
  UserState number:=1
) AS 
BEGIN
  Insert into userinfo values(UserId,UserName,PassWord,UserRole,Gender,PassQuestion,PassAnswer,Email,TelNO,
  Address,IdCardNO, Money, UserState);
  commit;
  if  sql%NOtFound then
    dbms_output.put_line('注册失败');
  end if;
END INSERTINFO_PROC;

 

　　2.管理员个人管理模块

}该模块主要包括：修改个人资料和审核操作。

}修改个人资料与会员注册所需资料完全一致，主要有：登录号、密码、真实姓名、性别、密码问题、密码答案、Email、地址、电话、身份证。

}审核操作：主要是对新注册的会员和会员的汇款信息进行审核，可以把新会员设置成为正式会员，根据会员汇款的数额，对会员的个人帐户充值。

把新会员设置成为正式会员:

create or replace PROCEDURE USERINFO_PROC
(P_UserId varchar2)
AS 
BEGIN
  update userinfo set uerrole=3 where userid=p_userid;
  commit;
END USERINFO_PROC;

 查看会员信息：

create or replace PACKAGE PKG_USERINFO AS 

  /* TODO enter package declarations (types, exceptions, methods etc) here */ 
  type userinfos is ref cursor;
  procedure select_userinfo(p_userid varchar2,p_userinfo out userinfos);
END PKG_USERINFO;


create or replace PACKAGE BODY PKG_USERINFO AS

  procedure select_userinfo(p_userid varchar2,p_userinfo out userinfos) AS
  sqlstr varchar2(500);
  BEGIN
    -- TODO: procedure PKG_USERINFO.select_userinfo所需的实施
    sqlstr:='select UserID,UserName,PassWord,Gender,PassQuesstion,PassAnswer,Email,TelNo,Address,
IDCardNo,Money from UserInfo  where UserID=:v_userid';
   open p_userinfo for sqlstr using p_userid;
  END select_userinfo;

END PKG_USERINFO;

对角色信息扩展，增加角色表：

truncate table userinfo;
alter table userinfo add constraint fk_roleinfo foreign key (uerrole) references roleinfo (roleid);


create table RoleInfo
  2  (RoleId number(2) primary key,     --角色ID
  3   RoleName varchar2(50),            --角色名称
  4   RoleDesc varchar2(50),            --角色描述
  5   DisCount number(2)                --会员折扣
  6  );

 同时对多个用户操作首先获取多个用户的userid

create global temporary table temptable (col varchar2(30)) on commit delete rows;

CREATE OR REPLACE PROCEDURE GETUSERID_PROC (p_str varchar2)AS
result_str varchar2(30);
i number :=1;
s number:=1;
j number:=0;
BEGIN
  while i!=0 loop
  i:=instr(p_str,',',1,s);
  if(i=0) then
  result_str:=substr(p_str,j+1,length(p_str)-j);
  else
  --xiaomi,maf,an
  --         j      i
  --         7     11
  result_str:=substr(p_str,j+1,i-j-1);
  end if;
  j:=i;
  s:=s+1;
  dbms_output.put_line(result_str);
 insert into temptable values(result_str);
  end loop;
END GETUSERID_PROC;

同时对多个用户删除，修改角色

 create or replace procedure updaterole_proc  --修改角色
  2  (UserID_String  varchar2,
  3   parm_UserRole varchar2)
  4  as
  5   begin
  6       getuserid_proc(UserID_String);    --多个用户ID之间用逗号隔开
  7     update UserInfo
  8     set UserRole=parm_UserRole
  9     where Userid in(select col from temptable);
 10     commit;
 11    end;
 12  /

过程已创建。

SQL> create or replace procedure DeleteUser_proc   --删除用户
  2  (UserID_String varchar2)
  3  as
  4   begin
  5    getuserid_proc(UserID_String);
  6    delete userinfo
  7    where userid in(select col from temptable);
  8    commit;
  9   end;
 10  /

 　　3.

}角色管理：主要包括添加角色、修改角色、删除角色、分配角色功能、浏览所有角色功能、设置会员优惠价。

}添加角色：包括角色名称、角色描述。

}分配角色功能：列出所有功能菜单，菜单分为两级，只列出子菜单，把选中的菜单id及菜单的父菜单id添加到指定的角色下，可以随时修改角色对应的功能菜单。

}浏览所有角色功能：按角色分别列出这些角色对应的权限菜单。

}设置会员优惠价：设置不同角色的优惠比例。

 创建所有菜单功能表：

create table sysfun   --存放菜单功能基本信息
    (nodeid number(2) not null,     --菜单编号
      displayname varchar2(50) not null, --菜单名称
   nodeurl varchar2(50) not null,  --菜单连接地址
     displayorder number(2) not null,  --菜单显示顺序
  parentnodeid  number(2)not null  --父节点
     );

将各个功能与相应角色对应

 create table roleright
  (rolerightid number(2) not null,
  roleid number(2),
   nodeid number(2) );

   alter table sysfun add constraint pk_sysfun primary key (nodeid);
   alter table roleright
  add constraint fk_roleid foreign key(roleid) references roleinfo(roleid);
  alter table roleright 
  add constraint fk_nodeid foreign key(nodeid) references sysfun(nodeid);

 

 添加角色：

create sequence seq_id
increment by 1
start with 1;

CREATE OR REPLACE TRIGGER TRI_ROLEID 
BEFORE INSERT ON ROLEINFO 
for each row
declare new_key number(2);
BEGIN
  new_key:=seq_id.nextval;
  :new.roleid:=new_key;
END;

create or replace PROCEDURE INSERTROLE 
(
  P_ROLENAME IN VARCHAR2 ,
  p_roledesc in varchar2
) AS 
BEGIN
  insert into roleinfo(rolename,roledesc) values(
  p_rolename,p_roledesc
  );
  commit;
END INSERTROLE;

设置优惠：

create or replace procedure U_discount   --设定优惠价比
  2  (parm_rolename varchar2,
  3   parm_discount number)
  4  as
  5   begin
  6     update roleinfo
  7     set discount=parm_discount
  8     where rolename=parm_rolename;
  9   end;
 10  /

 　　4.sql优化

1.表名顺序

table  A    1000条
table  B    1

select count(*)
from A, B    (最佳方法)


select count(*)
from B,A  (不好方法)


2.where 子句条件采用自下而上顺序解析
 (低效方法)
   select ...
   from emp E
   where sal>2000
   and   job='MANAGER'
   and 25<(select count(*) from where mgr=E.EMPNO)

  (高效方法)
  select ...
   from emp E
   where 25<(select count(*) from where mgr=E.EMPNO)
   and   job='MANAGER'
   and  sal>2000


3.减少访问数据库的次数
(低效方法)
 select ename,sal,job from emp
 where empno=7369;
 select ename,sal,job from emp
 where empno=7499;

 (高效方法)
 select A.ename, A.sal, A.job,B.ename,B.sal, B.job
 from emp A, emp B
 where empno=7369 and empno=7499;



4. decode减少处理时间
  select count(*),sum(sal)from emp
  where deptno=20 and ename like 'SMITH%';
   select count(*),sum(sal)from  emp
  where deptno=30 and ename like 'SMITH%';

 高效方法
  select count(decode(deptno,20,'*',0)) as count20,
   count(decode(deptno,30,'*',0)) as count30,
   sum(decode(deptno,20,sal,0)) as sumsal20,
   sum(decode(deptno,30,sal,0)) as sumsal20,
   from emp
   where ename like 'SMITH%';

5.删除重复记录
 (高效方法)
  delete from emp E
  where E.rowid>(select min(X.ROWID) from emp X where X.empno=E.empno);

6.where替换having 
 先后顺序
 on
 where 
having

  
7.使用表的别名 
   select A.COL1,A.col2,B.COL1
   from test1  A, test2 B

8.exists   distinct
(低效)
  select distinct deptno,deptname from dept D, emp E
  where D.deptno=E.deptno;
 
(高效)
 select distinct deptno,deptname from dept D
  where EXISTS(select * from emp where E.deptno=D.deptno);
 

9.索引不要使用在计算字段上
  select ... from emp where sal*12>20000  (低效)
  select ... from emp where sal>20000/12 (高效)

10.
  select * from student where cno='1' or cno='2'
  
  select * from student where cno='1'
  union 
  select * from student where cno='1'

11.
 select ename,sal from emp
 where sal+2000>5000;(停用索引)

 select ename,sal from emp
 where sal>3000;(启用索引)

12.耗费资源的操作
  distinct,union,intersect, order by

13.优化group by
(低效)
  select job,avg(sal)
  from emp
  group by job
  having job='MANAGER' or job='CLERK';




(高效)
  select job,avg(sal)
  from emp
   where job='MANAGER' or job='CLERK'
  group by job;