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  /

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

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

}分配角色功能：列出所有功能菜单，菜单分为两级，只列出子菜单，把选中的菜单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='' or cno=''

   select * from student where cno=''

   union

   select * from student where cno=''

 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;

巴特西

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

最新文章

热门文章