\ NUMBER(20) NOT NULL , \ VARCHAR2(100),
PRIMARY KEY (\ \ VALIDATE ,
FOREIGN KEY (\ REFERENCES \ (\ VALIDATE ) TABLESPACE \（3）创建入库管理表R_PRO
CREATE TABLE \ ( \ NUMBER(11) NOT NULL , \ DATE NOT NULL , \ DATE NOT NULL , \ NUMBER(10) NOT NULL , \ VARCHAR2(100),
PRIMARY KEY (\ \ VALIDATE ,
FOREIGN KEY (\ REFERENCES \ (\ VALIDATE ) TABLESPACE \
（4）出库产品管理C_PRO
CREATE TABLE \ ( \ NUMBER(11) NOT NULL , \ DATE NOT NULL , \ DATE NOT NULL , \ NUMBER(10) NOT NULL , \ VARCHAR2(100),
PRIMARY KEY (\ \ VALIDATE ,
FOREIGN KEY (\ REFERENCES \ (\ VALIDATE ) TABLESPACE \ （5）管理规则PRO_LIM
CREATE TABLE \ ( \ NUMBER(11) NOT NULL , \ NOT NULL , \ NUMBER(10) NOT NULL , \ NUMBER(5) NOT NULL ,
\ VARCHAR2(100),
PRIMARY KEY (\ VALIDATE ,
FOREIGN KEY (\ REFERENCES \ (\ VALIDATE ) TABLESPACE\ （6）管理员表ADMIN
CREATE TABLE \\
\\
PRIMARY KEY (\
2、序列的创建
CREATE SEQUENCE C_R_VIEW NOCYCLE NOORDER NOCACHE NOMAXVALUE MINVALUE 100 INCREMENT BY 1 START WITH 100
3、视图的创建
（1）产品的出入库情况C_R_PRO_VIEW
CREATE OR REPLACE VIEW C_R_PRO_VIEW AS
SELECT CPROID,RPROID,PRONAME,RDATE,CDATE,CBIRDATE,RBIRDATE,CNUM,RNUM FROM PRO, (SELECT
C_PRO.PROID AS CPROID,R_PRO.PROID AS RPROID,RDATE,CDATE,C_PRO.BIRDATE AS CBIRDATE,R_PRO.BIRDATE AS RBIRDATE,CNUM,RNUM FROM C_PRO FULL JOIN R_PRO ON C_PRO.PROID=R_PRO.PROID and CDATE=RDATE ORDER BY RPROID) R_C WHERE PRO.PROID=CPROID AND PRO.PROID=RPROID
（2）产品总数量:SNUM
CREATE OR REPLACE VIEW SNUM AS
SELECT PRO.PROID,SUM(ZNUM) SNUM FROM PRO,PRO_MAN WHERE PRO.PROID=PRO_MAN.PROID GROUP BY PRO.PROID
（3）产品的库存信息:ZNUM_PRO_VIEW
CREATE OR REPLACE VIEW ZNUM_PRO_VIEW AS
SELECT PRO_MAN.PROID,PRONAME,BIRDATE,BDATE,ZNUM FROM PRO,PRO_MAN WHER PRO.PROID=PRO_MAN.PROID
4、PL/SQL程序设计
（1）仓库产品少于最少数量或者大于最大数量时显示出产品数量、生产日期及保质期来: CREATE OR REPLACE FUNCTION MIN_MAX_NUM_SHOW (
V_PROID NUMBER,V_BIRDATE OUT DATE,V_BDATE OUT NUMBER) RETURN NUMBER AS
V_SNUM NUMBER;V_MINUM NUMBER;V_MAXUM NUMBER; BEGIN
SELECT SUM(ZNUM) INTO V_SNUM FROM PRO_MAN WHERE PROID=V_PROID;
SELECT MINUM,MAXUM INTO V_MINUM,V_MAXUM FROM PRO_LIM WHERE PROID=V_PROID; SELECT BIRDATE INTO V_BIRDATE FROM PRO_MAN WHERE PROID=V_PROID; SELECT BDATE INTO V_BDATE FROM PRO WHERE PROID=V_PROID; IF V_SNUM-V_MINUM<=0 OR V_SNUM-V_MAXUM>=0 THEN RETURN V_SNUM; END IF; END;
（2）计算距保质期时间:
CREATE OR REPLACE PROCEDURE PRO_B( V_PROID NUMBER) AS
V_DAYS NUMBER; V_LBIRDATE NUMBER;
CURSOR C_BIRDATE IS SELECT BIRDATE FROM
PRO_MAN WHERE PROID=V_PROID; BEGIN
SELECT LBIRDATE INTO V_LBIRDATE FROM PRO_LIM WHERE PROID=V_PROID;
FOR V_BIRDATE IN C_BIRDATE LOOP
IF V_DAYS:=(SYSDATE-V_BIRDATE)>=V_LBIRDATE THEN DBMS_OUTPUT.PUTLINE(V_DAYS); END IF; END LOOP; END;
(3)计算出库后数量:
CREATE OR REPLACE FUNCTION A_C_NUM( V_PROID NUMBER,V_BIRDATE OUT DATE) RETURN NUMBER AS
V_CZNUM NUMBER; BEGIN
SELECT BIRDATE INTO V_BIRDATE FROM C_PRO WHERE PROID=V_PROID; SELECT ZNUM-CNUM INTO V_CZNUM FROM (
SELECT PRO_MAN.PROID, PRO_MAN.BIRDATE CBIRDATE,CNUM,ZNUM FROM C_PRO,PRO_MAN WHERE
PRO_MAN.PROID=C_PRO.PROID AND PRO_MAN.BIRDATE=C_PRO.BIRDATE) A_C WHERE PROID=V_PROID AND A_C.CBIRDATE=V_BIRDATE; RETURN V_CZNUM; END;
(4)计算入库后数量：
CREATE OR REPLACE FUNCTION A_R_NUM( V_PROID NUMBER,V_BIRDATE OUT DATE) RETURN NUMBER AS
V_RZNUM NUMBER; BEGIN
SELECT BIRDATE INTO V_BIRDATE FROM R_PRO WHERE PROID=V_PROID; SELECT ZNUM+RNUM INTO V_RZNUM FROM (
SELECT PRO_MAN.PROID, PRO_MAN.BIRDATE RBIRDATE,RNUM,ZNUM FROM R_PRO,PRO_MAN WHERE
PRO_MAN.PROID=R_PRO.PROID AND PRO_MAN.BIRDATE=R_PRO.BIRDATE) A_R WHERE PROID=V_PROID AND A_R.RBIRDATE=V_BIRDATE; RETURN V_RZNUM; END;
五、 总结
在这次的数据库实践中，我学到了很多，也收获了很多经验。刚刚开始时由于认识不足以及搜素资料不完善，将此数据库系统设计不完善，使系统无法实现。在老师的教导下，我又重新将仓库管理系统进行分析，并找同学讨论，不断完善，经过一番努力后，将此系统完成，虽然不是很复杂，功能也不是很多，但是这注入了我全部的思想。
由此次试验，我学会了做事情要循序渐进，不可能一次性就能将事情做到十全十美，在做的过程中不断思考，将事情渐渐完善。
此次的实践还让我的数据库知识更扎实，在试验中运用了存储过程，视图等功能，让我学到的知识能更加熟练掌握。
Oracle仓库管理系统课程设计报告（数据库Oracle代码）
