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

最新推荐文章于 2023-07-17 21:06:44 发布

巷中人

最新推荐文章于 2023-07-17 21:06:44 发布

阅读量138

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/charlie-badegg/p/3309121.html

版权

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

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

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

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

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

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

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

View Code

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

 1 CREATE OR REPLACE PROCEDURE INSERTINFO_PROC 
 2 (
 3   UserId VARCHAR2 ,
 4   Username varchar2,
 5   password varchar2,
 6   Gender number,
 7   PassQuestion varchar2,
 8   PassAnswer varchar2,
 9   Email varchar2,
10   TelNo varchar2,
11   Address varchar2 :=null,
12   IdCardNo varchar2,
13   UserRole number:=5,
14   Money number :=0,
15   UserState number:=1
16 ) AS 
17 BEGIN
18   Insert into userinfo values(UserId,UserName,PassWord,UserRole,Gender,PassQuestion,PassAnswer,Email,TelNO,
19   Address,IdCardNO, Money, UserState);
20   commit;
21   if  sql%NOtFound then
22     dbms_output.put_line('注册失败');
23   end if;
24 END INSERTINFO_PROC;

View Code

　　2.管理员个人管理模块

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

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

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

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

1 create or replace PROCEDURE USERINFO_PROC
2 (P_UserId varchar2)
3 AS 
4 BEGIN
5   update userinfo set uerrole=3 where userid=p_userid;
6   commit;
7 END USERINFO_PROC;

View Code

查看会员信息：

 1 create or replace PACKAGE PKG_USERINFO AS 
 2 
 3   /* TODO enter package declarations (types, exceptions, methods etc) here */ 
 4   type userinfos is ref cursor;
 5   procedure select_userinfo(p_userid varchar2,p_userinfo out userinfos);
 6 END PKG_USERINFO;
 7 
 8 
 9 create or replace PACKAGE BODY PKG_USERINFO AS
10 
11   procedure select_userinfo(p_userid varchar2,p_userinfo out userinfos) AS
12   sqlstr varchar2(500);
13   BEGIN
14     -- TODO: procedure PKG_USERINFO.select_userinfo所需的实施
15     sqlstr:='select UserID,UserName,PassWord,Gender,PassQuesstion,PassAnswer,Email,TelNo,Address,
16 IDCardNo,Money from UserInfo  where UserID=:v_userid';
17    open p_userinfo for sqlstr using p_userid;
18   END select_userinfo;
19 
20 END PKG_USERINFO;

View Code

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

 1 truncate table userinfo;
 2 alter table userinfo add constraint fk_roleinfo foreign key (uerrole) references roleinfo (roleid);
 3 
 4 
 5 create table RoleInfo
 6   2  (RoleId number(2) primary key,     --角色ID
 7   3   RoleName varchar2(50),            --角色名称
 8   4   RoleDesc varchar2(50),            --角色描述
 9   5   DisCount number(2)                --会员折扣
10   6  );

View Code

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

 1 create global temporary table temptable (col varchar2(30)) on commit delete rows;
 2 
 3 CREATE OR REPLACE PROCEDURE GETUSERID_PROC (p_str varchar2)AS
 4 result_str varchar2(30);
 5 i number :=1;
 6 s number:=1;
 7 j number:=0;
 8 BEGIN
 9   while i!=0 loop
10   i:=instr(p_str,',',1,s);
11   if(i=0) then
12   result_str:=substr(p_str,j+1,length(p_str)-j);
13   else
14   --xiaomi,maf,an
15   --         j      i
16   --         7     11
17   result_str:=substr(p_str,j+1,i-j-1);
18   end if;
19   j:=i;
20   s:=s+1;
21   dbms_output.put_line(result_str);
22  insert into temptable values(result_str);
23   end loop;
24 END GETUSERID_PROC;

View Code

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

 1  create or replace procedure updaterole_proc  --修改角色
 2   2  (UserID_String  varchar2,
 3   3   parm_UserRole varchar2)
 4   4  as
 5   5   begin
 6   6       getuserid_proc(UserID_String);    --多个用户ID之间用逗号隔开
 7   7     update UserInfo
 8   8     set UserRole=parm_UserRole
 9   9     where Userid in(select col from temptable);
10  10     commit;
11  11    end;
12  12  /
13 
14 过程已创建。
15 
16 SQL> create or replace procedure DeleteUser_proc   --删除用户
17   2  (UserID_String varchar2)
18   3  as
19   4   begin
20   5    getuserid_proc(UserID_String);
21   6    delete userinfo
22   7    where userid in(select col from temptable);
23   8    commit;
24   9   end;
25  10  /

View Code

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

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

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

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

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

创建所有菜单功能表：

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

View Code

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

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

View Code

1    alter table sysfun add constraint pk_sysfun primary key (nodeid);
2    alter table roleright
3   add constraint fk_roleid foreign key(roleid) references roleinfo(roleid);
4   alter table roleright 
5   add constraint fk_nodeid foreign key(nodeid) references sysfun(nodeid);

View Code

添加角色：

 1 create sequence seq_id
 2 increment by 1
 3 start with 1;
 4 
 5 CREATE OR REPLACE TRIGGER TRI_ROLEID 
 6 BEFORE INSERT ON ROLEINFO 
 7 for each row
 8 declare new_key number(2);
 9 BEGIN
10   new_key:=seq_id.nextval;
11   :new.roleid:=new_key;
12 END;
13 
14 create or replace PROCEDURE INSERTROLE 
15 (
16   P_ROLENAME IN VARCHAR2 ,
17   p_roledesc in varchar2
18 ) AS 
19 BEGIN
20   insert into roleinfo(rolename,roledesc) values(
21   p_rolename,p_roledesc
22   );
23   commit;
24 END INSERTROLE;

View Code

设置优惠：

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

View Code

　　4.sql优化

  1 1.表名顺序
  2 
  3 table  A    1000条
  4 table  B    1
  5 
  6 select count(*)
  7 from A, B    (最佳方法)
  8 
  9 
 10 select count(*)
 11 from B,A  (不好方法)
 12 
 13 
 14 2.where 子句条件采用自下而上顺序解析
 15  (低效方法)
 16    select ...
 17    from emp E
 18    where sal>2000
 19    and   job='MANAGER'
 20    and 25<(select count(*) from where mgr=E.EMPNO)
 21 
 22   (高效方法)
 23   select ...
 24    from emp E
 25    where 25<(select count(*) from where mgr=E.EMPNO)
 26    and   job='MANAGER'
 27    and  sal>2000
 28 
 29 
 30 3.减少访问数据库的次数
 31 (低效方法)
 32  select ename,sal,job from emp
 33  where empno=7369;
 34  select ename,sal,job from emp
 35  where empno=7499;
 36 
 37  (高效方法)
 38  select A.ename, A.sal, A.job,B.ename,B.sal, B.job
 39  from emp A, emp B
 40  where empno=7369 and empno=7499;
 41 
 42 
 43 
 44 4. decode减少处理时间
 45   select count(*),sum(sal)from emp
 46   where deptno=20 and ename like 'SMITH%';
 47    select count(*),sum(sal)from  emp
 48   where deptno=30 and ename like 'SMITH%';
 49 
 50  高效方法
 51   select count(decode(deptno,20,'*',0)) as count20,
 52    count(decode(deptno,30,'*',0)) as count30,
 53    sum(decode(deptno,20,sal,0)) as sumsal20,
 54    sum(decode(deptno,30,sal,0)) as sumsal20,
 55    from emp
 56    where ename like 'SMITH%';
 57 
 58 5.删除重复记录
 59  (高效方法)
 60   delete from emp E
 61   where E.rowid>(select min(X.ROWID) from emp X where X.empno=E.empno);
 62 
 63 6.where替换having 
 64  先后顺序
 65  on
 66  where 
 67 having
 68 
 69   
 70 7.使用表的别名 
 71    select A.COL1,A.col2,B.COL1
 72    from test1  A, test2 B
 73 
 74 8.exists   distinct
 75 (低效)
 76   select distinct deptno,deptname from dept D, emp E
 77   where D.deptno=E.deptno;
 78  
 79 (高效)
 80  select distinct deptno,deptname from dept D
 81   where EXISTS(select * from emp where E.deptno=D.deptno);
 82  
 83 
 84 9.索引不要使用在计算字段上
 85   select ... from emp where sal*12>20000  (低效)
 86   select ... from emp where sal>20000/12 (高效)
 87 
 88 10.
 89   select * from student where cno='1' or cno='2'
 90   
 91   select * from student where cno='1'
 92   union 
 93   select * from student where cno='1'
 94 
 95 11.
 96  select ename,sal from emp
 97  where sal+2000>5000;(停用索引)
 98 
 99  select ename,sal from emp
100  where sal>3000;(启用索引)
101 
102 12.耗费资源的操作
103   distinct,union,intersect, order by
104 
105 13.优化group by
106 (低效)
107   select job,avg(sal)
108   from emp
109   group by job
110   having job='MANAGER' or job='CLERK';
111 
112 
113 
114 
115 (高效)
116   select job,avg(sal)
117   from emp
118    where job='MANAGER' or job='CLERK'
119   group by job;
120 
121 
122 
123 
124 
125 
126 
127 
128 
129 
130 
131 
132 
133  
134 
135 
136 
137   
138