Oracle课程设计报告-仓库管理系统

1、Oracle实验题目：仓库管理系统专业班级计算机应用与技术1440302学号：20210030226姓名：胡 强 指导教师：王122一、系统描述仓库管理系统的功能： 1）、产品入仓管理：在货物送到仓库时，关于货物信息进行了解并且相关信息输入到数据库中，同时更新数据库。2、产品出仓管理：在货物运出仓库时，进行货物信息统计并且将据保存到数据库中，同时更新数据库。 3）仓库货物信息提醒：当货物少于某一规则的值时，或者货物保期将至时将发出提 醒 。 4）、借出管理：凭借条借出，然后能够还库。5、查询修改：管理员可以进行一系列查询：查询货物类别及货物的信息、数量和货物流动的规那么关于货物出仓进仓的要求；

2、可以关于以上查询进行修改可以查询某一天货物流动的信息以及出仓及入仓的详细信息。6、初始化库存设置：设置库存的初始化值，库存的上下警戒限制。7、盘库情况：反映没有、年的库存情况二、系统需求分析利用用例图、类图、E-R 图关于系统进行需求分析。图及E-R仓库管理系统用例图类图表 1：产品信息表 PRO字段名数据类型长度约束说明PROINUMBER11主码产品编号PRONAMEVARCHAR220NOT NULL产品名称PRORESSVARCHAR250产品地址REPHONENUMBER20产品地址号码BATENUMBER5NOT NULL保质天数EMOVARCHAR2100说明344表 2：产品信

3、息管理 PRO_MAN字段名数据类型长度约束说明PROINUMBER11主码，外码产品编号BIRATEATE主码产品Th产日期ZNUMNUMBER20NOT NULL产品数量EMOVARCHAR2100说明3C_PRO字段名数据类型长度约束说明PROINUMBER11主码，外码产品编号CATEATE主码出库日期BIRATEATENOT NULLTh产日期ZNUMNUMBER10NOT NULL出库数量EMOVARCHAR2100说明4R_PRO字段名数据类型长度约束说明PROINUMBER11主码，外码产品编号RATEATE主码入库日期BIRATEATENOT NULLTh产日期RNUMNUM

4、BER10NOT NULL入库数量EMOVARCHAR2100说明表 5：产品仓库限制规那么字段名数据类型长度约束说明PROINUMBER11主码，外码产品编号BIRATEATE主码产品Th产日期ZNUMNUMBER20NOT NULL产品数量EMOVARCHAR2100说明表 6：管理员表 AMIN字段名数据类型长度约束说明INUMBER11主码管理员编号USARNAME PASSORVARCHAR210VARCHAR211NOT NULL NOT NULL管理员账号账号密码E-R三、数据库关于象设计1、表设计62、序列设计5 PAGE PAGE 10为了方便仓库产品管理在数据库中用以下序列

5、产Th相编号C_R_VIEW：产Th产品编号，起始值100。3、视图设计Th存信息及产品的总数量，创立以下视图。1创立名为“C_R_PRO_VIEW的视图，用于察看产品的出入库汇总情况，包括出库的产品编号，入库的产品编号，产品名称，库日期，出库日期，出库产品的Th产日期，入库产品的Th产日期，出库数量，入库数量。2创立名为“SNUM的视图，用于查询一种产品的总库存量，囊括产品编号，产品总数量。创立名“ZNUM_PRO_VIEW的视图用于查询产品的库存信息囊括不同Th产日期的库存量4、PL/SQL 功能模块设计利用PL/SQL程序创立以下各种数据库关于象。1创立一个当仓库产品少于最少数量或者大于

6、最大数量时显示出产品数量、Th产日期及保质期的函数。2创立一个计算距保质期时间的存储进程。四、数据库关于象创立描述数据库关于象中表的创立、序列的创立、视图的创立、存储进程的创立，触发器的创立等内容，实现系统的根本功能。1、表的创立PRO:CREATETABLEUSERS.PRO( PROINUMBER(11)NOTNULLPRONAMEVARCHAR2(20)NOTNULLPRORESSVARCHAR2(50),REPHONENUMBER(20),BATENUMBER(5)NOTNULLEMOVARCHAR2(100),PRIMARYKEY(PROI)VALIATETABLESPACEUSER

7、SPRO_MANCREATETABLEUSERS.PRO_MANPROINUMBER(11)NOTNULL, BIRATEATENOTNULL, ZNUMNUMBER(20)NOTNULL, EMOVARCHAR2(100),PRIMARYKEY(PROI,BIRATE)VALIATE, FOREIGNKEY(PROI)REFERENCESSYS.PROROI)VALIATETABLESPACEUSERS3创立入库管理表 R_PROCREATETABLEUSERS.R_PROPROINUMBER(11)NOTNULL, RATEATENOTNULL, BIRATEATENOTNULL, RNU

8、MNUMBER(10)NOTNULL, EMOVARCHAR2(100),PRIMARYKEY(PROI,RATE)VALIATE, FOREIGNKEY(PROI)REFERENCESSYS.PROI)VALIATE) TABLESPACE4出库产品管理 C_PROCREATETABLEUSERS.C_PROPROINUMBER(11)NOTNULL, CATEATENOTNULL, BIRATEATENOTNULL, CNUMNUMBER(10)NOTNULL,EMOVARCHAR2(100),PRIMARYKEY(PROI,FOREIGNKEY(PROI)I)VALIATE) TABLE

9、SPACE5管理规那么 PRO_LIMCREATETABLEUSERS.PRO_LIMPROINUMBER(11)NOTNULL, MINUMNUMBER(10)NOTNULL, MAXUMNUMBER(10)NOTNULL, LBIRATENUMBER(5)NOTNULL, EMOVARCHAR2(100),PRIMARYKEY(PROI)VALIATEVALIATE, SYS.PRO PAGE PAGE 11FOREIGNKEY(PROI)REFERENCESSYS.PRO(PROI)VALIATE) TABLESPACEUSERS6管理员表 AMINCREATETABLEUSERS.AM

10、ININUMBER(11)NOTNULL,USERNAMEVARCHAR2(10)NOTNULL, PASSWORVARCHAR2(11)NOTNULLPRIMARYKEY(I)VALIATETABLESPACEUSERS2、序列的创立CREATESEQUENCEC_R_VIEWNOCYCLENOORERNOCACH NOMAXVALUEMINVALUE100INCREMENTBY1START1003、视图的创立1产品的出入库情况CREATEORREPLACEVIEWC_R_PRO_VIEWASSELECT CPROI,RPROI,PRONAME,RATE,CATE,CBIRATE,RBIRA

11、TE,CNUM,RNUMFROM(SELECTASASASASRBIRATE,CNUM,RNUMFROM C_PRO FULL JOIN R_PRO ON an CATE=RAT E ORER BY RPROI) R_C WHERE PRO.PROI=CPROI AN P RO.PROI=RPROI2产品总数量:SNUMCREATEORREPLACEVIEWASSELECTPRO.PROI,SUM(ZNUM)SNUMFROMPRO,PRO_MANREGROUPBY3产品的库存信息:ZNUM_PRO_VIEW CREATEORREPLACEVIEWASSELECTPRO_MAN.PROI,PRO

12、NAME,BIRATE,BATE,ZNUMFROMPRO1,PRO_MANWHER4、PL/SQL 程序设计1仓库产品少于最少数量或者大于最大数量时显示出产品数量、Th产日期保质期来：CREATEORREPLACEFUNCTIONMIN_MAX_NUM_SHOW(V_PROINUMBER,V_BIRATEOUTATE,V_BATEOUTNUMBER)RETURNASV_SNUMNUMBER;V_MINUMNUMBER;V_MAXUMBEGINSELECTSUM(ZNUM)INTOV_SNUMFROMPRO_MANPROI=V_PROI;SELECTMINUM,MAXUMINTOV_MINUM,

13、V_MAXUMFROMMWHEREPROI=V_PROI;SELECTBIRATEINTOV_BIRATEFROMPRO_MANWHERE12PROI=V_PROI;SELECTBATEINTOV_BATEFROMPROWHEREPROI;IFV_SNUM-V_MINUM=0RETURNV_SNUM;ENEN;2计算距保质期时间:CREATEORREPLACEPROCEUREV_PROINUMBER)ASV_AYSNUMBER; V_LBIRATE13CURSORC_BIRATEISSELECTBIRATEFROMPRO_MANWHEREBEGINSELECTLBIRATEINTOV_LBIR

14、ATEFROMPRO_LIMWHEREPROI=V_PROI;FORV_BIRATEINC_BIRATELOOPIFIRATETHENBMS_OUTPUT.PUTLINE(V_AYS); ENIF;ENEN；14(4)计算入库后数量：CREATEORREPLACEFUNCTIONA_R_NUM(V_PROINUMBER,V_BIRATEOUTATE)RETURNNUMBRASV_RZNUMBEGINSELECTBIRATEINTOV_BIRATEFROMR_PROWHEREROI=V_PROI;SELECTZNUM+RNUMINTOV_RZNUMFROM(SELECTPRO_MAN.PROI,

15、RBIRATE,RNUM,ZNUMFROMR_PRO,PRO_MANWHEREANPRO_MAN.BIRATE=R_PRO.BIRATE)A_RWHEREPROI=V_PROIANA_R.RBIRATE=V_BIRATE;RETURNEN;15Javaimportjava.awt.BorerLayout; import java.awt.EventQueue;importjavax.swing.JFrame; importjavax.swing.anel;importjavax.swing.borer.EmptyBorer; import javax.swing.JButton;public

16、class jiemian2 extens JFrame 16 PAGE PAGE 19private anel contentPane;public anel getContentPane()return contentPane;/* Launch the application.*/public static voi main(String args) EventQueue.invokeLater(new Runnable()public voi run() try jiemian2 frame = newjiemian2(); frame.setVisible(true); catch

17、(Exception e)e.printStackTrace(););/* Create the frame.*/public jiemian2() setefaultCloseOperation(JFrame.EXIT_ON_CLOSE); setBouns(100, 100, 450, 300);contentPane = new anel(); contentPane.setBorer(new EmptyBorer(5, 5,5);setContentPane(contentPane); contentPane.setLayout(null);package 仓库管理;importjav

18、a.awt.BorerLayout; import java.awt.CarLayout; import java.awt.EventQueue;import javax.swing.JFrame;import javax.swing.JMenuItem; importjavax.swing.JOptionPane; import javax.swing.anel;importjavax.swing.borer.EmptyBorer; import javax.swing.JTextFiel;import javax.swing.JLabel;import java.awt.Font;impo

19、rt java.awt.event.ActionEvent; importjava.awt.event.ActionListener;import javax.swing.JButton;publicclassjiemian1extensActionListenerprivate aneljiemianjm;CarLayoutanel ; jiemian2 jm2; anel2;implements PAGE PAGE 21private static oublepublic anel getContentPane() return contentPane;public voi setCont

20、entPane(anel contentPane)this.contentPane = contentPane;private JTextFielprivate JLabel label_1; private JLabel label_2; private JTextFielprivate JTextFielprivate JButton jb1; private JButton jb2;/*Launch theapplication.*/*Create theframe.*/public jiemian1() =jiemian.get(); cl=jiemian.getCl();setefa

21、ultCloseOperation(JFrame.EXIT_ON_CLOSE); setBouns(100, 100, 450, 300);contentPane = new anel(); contentPane.setBorer(new EmptyBorer(5, 5,5);setContentPane(contentPane); contentPane.setLayout(null);text1 = new JTextFiel(); text1.setBouns(193, 22, 192,contentPane.a(text1); text1.setColumns(10);JLabell

22、abel=JLabel(u5E93u5B58u521u59CBu5316u503CuFF1A22);16);label.setFont(new Font( 微软雅黑, Font.PLAIN,label.setBouns(46, 21, 113,contentPane.a(label);label_1=JLabel(u4E0Au8B66u6212u7EBFuFF1A);label_1.setFont(newFont(Font.PLAIN, 16);label_1.setBouns(79, 70, 80,contentPane.a(label_1);label_2=JLabel(u4E0Bu8B6

23、6u6212u7EBFuFF1A);label_2.setFont(newFont(Font.PLAIN, 16);label_2.setBouns(79, 125, 80,contentPane.a(label_2);text2 = newtext2.setColumns(10); PAGE PAGE 29text2.setBouns(193, 75, 192,contentPane.a(text2);text3 = new JTextFiel(); text3.setColumns(10); text3.setBouns(193, 130, 192,contentPane.a(text3)

24、;16);16);jb1 = new JButton(u786Eu5B9A); jb1.setFont(new Font( 华文琥珀, Font.PLAIN,jb1.setBouns(76, 194, 122,contentPane.a(jb1);jb2 = new JButton(u8F4u56E); jb2.setFont(new Font( 华文琥珀, Font.PLAIN,jb2.setBouns(262, 194, 122,contentPane.a(jb2); jb1.aActionListener(this); jb2.aActionListener(this); jm2=new

25、 jiemian2();2=jm2.getContentPane(); .a(2,two);Overriepublic voi actionPerforme(ActionEvent e) / TOO Auto-generate methoif(e.getSource()=jb1)int i=0; trychushi=new ouble(text1.getText().toString();up=new ouble(text2.getText().toString(); own=newouble(text3.getText().toString(); jm.m1_mi1.setEnable(tr

26、ue); jm.m1_mi2.setEnable(true); jm.m2_mi1.setEnable(true); jm.m2_mi2.setEnable(true);jm.m2_mi3.setEnable(true); jm.m3_mi1.setEnable(true); jm.m3_mi2.setEnable(true);JOptionPane.showMessageialog(null,成功！);catch(Exception e1)JOptionPane.showMessageialog(null,不能为空！);i=1;if(i!=1) cl.show(,if(e.getSource

27、()=jb2)cl.show(,main);package 仓库管理;importjava.awt.BorerLayout; import java.awt.CarLayout; import java.awt.EventQueue;import javax.swing.JFrame; import javax.swing.JMenuItem; importjavax.swing.JOptionPane; import javax.swing.anel;importjavax.swing.borer.EmptyBorer; import javax.swing.JTextFiel;import

28、 javax.swing.JLabel;import java.awt.Font;import java.awt.event.ActionEvent; importjava.awt.event.ActionListener;import javax.swing.JButton;publicclassjiemian1extensActionListenerimplementsprivate aneljiemianjm;CarLayoutanel ; jiemian2 jm2; anel2;private static oublepublic anel getContentPane() retur

29、n contentPane;public voi setContentPane(anel contentPane)this.contentPane = contentPane;private JTextFielprivate JLabel label_1; private JLabel label_2; private JTextFielprivate JTextFielprivate JButton jb1; private JButton jb2;/*Launch theapplication.*/*Create theframe.*/public jiemian1() =jiemian.

30、get(); cl=jiemian.getCl();setefaultCloseOperation(JFrame.EXIT_ON_CLOSE); setBouns(100, 100, 450, 300);contentPane = new anel(); contentPane.setBorer(new EmptyBorer(5, 5,5);setContentPane(contentPane); contentPane.setLayout(null);text1 = new JTextFiel();text1.setBouns(193, 22, 192,contentPane.a(text1

31、); text1.setColumns(10);JLabellabel=JLabel(u5E93u5B58u521u59CBu5316u503CuFF1A);16);label.setFont(new Font( 微软雅黑, Font.PLAIN,label.setBouns(46, 21, 113,contentPane.a(label);label_1=JLabel(u4E0Au8B66u6212u7EBFuFF1A);label_1.setFont(newFont(Font.PLAIN, 16);label_1.setBouns(79, 70, 80,contentPane.a(labe

32、l_1);label_2=JLabel(u4E0Bu8B66u6212u7EBFuFF1A);label_2.setFont(newFont(Font.PLAIN, PAGE PAGE 3216);16);label_2.setBouns(79, 125, 80,contentPane.a(label_2);text2 = new JTextFiel(); text2.setColumns(10); text2.setBouns(193, 75, 192,contentPane.a(text2);text3 = new JTextFiel(); text3.setColumns(10); te

33、xt3.setBouns(193, 130, 192,contentPane.a(text3);jb1 = new JButton(u786Eu5B9A); jb1.setFont(new Font( 华文琥珀, Font.PLAIN,jb1.setBouns(76, 194, 122,contentPane.a(jb1);jb2 = new JButton(u8F4u56E); jb2.setFont(new Font( 华文琥珀, Font.PLAIN,16);jb2.setBouns(262, 194, 122,contentPane.a(jb2); jb1.aActionListene

34、r(this); jb2.aActionListener(this); jm2=new jiemian2(); 2=jm2.getContentPane(); .a(2,two);Overriepublic voi actionPerforme(ActionEvent e) / TOO Auto-generate methoif(e.getSource()=jb1)int i=0; trychushi=new ouble(text1.getText().toString();up=new ouble(text2.getText().toString(); own=newouble(text3.

35、getText().toString(); jm.m1_mi1.setEnable(true); jm.m1_mi2.setEnable(true); jm.m2_mi1.setEnable(true); jm.m2_mi2.setEnable(true); jm.m2_mi3.setEnable(true); jm.m3_mi1.setEnable(true); jm.m3_mi2.setEnable(true);JOptionPane.showMessageialog(null,成功！);catch(Exception e1)JOptionPane.showMessageialog(nul

36、l,不能为空！);i=1;if(i!=1) cl.show(,if(e.getSource()=jb2)33cl.show(, main);package 仓库管理;importjava.awt.BorerLayout; import java.awt.CarLayout; import java.awt.EventQueue;import javax.swing.JFrame; importjavax.swing.JOptionPane; importjavax.swing.anel;importjavax.swing.borer.EmptyBorer; importjavax.swing.

37、JLabel;import java.awt.Font;importjavax.swing.JTextFiel; import javax.swing.JButton; PAGE PAGE 39importjava.awt.event.ActionListener; import java.awt.event.ActionEvent;publicclassjiemian3extensJFrameActionListenerprivate anel contentPane; private JTextFiel textFiel; private JTextFielprivate JTextFie

38、lprivate JTextFieljiemian jm;CarLayout cl; anel ;private JButton jb1,jb2;/*Launch theapplication.*/*Create theframe.*/public jiemian3() setefaultCloseOperation(JFrame.EXIT_ON_CLOSE); setBouns(100, 100, 450, 300);contentPane = new anel(); contentPane.setBorer(new EmptyBorer(5, 5,5);setContentPane(con

39、tentPane); contentPane.setLayout(null);JLabellabel=JLabel(u5165u5E93u4EA7u54C1u540u79F0uFF1A);label.setFont(new Font(Font.BOL, label.setBouns(38, 19, 127, 28); contentPane.a(label);JLabellabel_1=JLabel(u5165u5E93u4EA7u54C1u6570u91CFuFF1A);label_1.setFont(newFont( 黑体,Font.BOL,15);label_1.setBouns(38,

40、 57, 127,contentPane.a(label_1);JLabellabel_2=JLabel(u5165u5E93u4EA7u54C1u7F16u53F7uFF1A);15);label_2.setFont(newFont( 黑体,Font.BOL,label_2.setBouns(38, 95, 114,contentPane.a(label_2);JLabellabel_3=JLabel(u5165u5E93u4EA7u54C1u4EF7u683CuFF1A);15);label_3.setFont(newFont( 黑体,Font.BOL,label_3.setBouns(3

41、8, 133, 114,contentPane.a(label_3);textFiel = new JTextFiel(); textFiel.setFont(new Font(Font.BOL,15);15);15);15);textFiel.setBouns(196, 22, 162,contentPane.a(textFiel); textFiel.setColumns(10);textFiel_1 = new JTextFiel(); textFiel_1.setFont(newFont(Font.BOL,textFiel_1.setColumns(10); textFiel_1.se

42、tBouns(196, 60, 162,contentPane.a(textFiel_1);textFiel_2 = new JTextFiel(); textFiel_2.setFont(newFont(Font.BOL,textFiel_2.setColumns(10); textFiel_2.setBouns(196, 98, 162,contentPane.a(textFiel_2);textFiel_3 = new JTextFiel(); textFiel_3.setFont(newFont(Font.BOL,textFiel_3.setColumns(10); textFiel_

43、3.setBouns(196, 136, 162,contentPane.a(textFiel_3);jb1 = new JButton(u5165u5E93); jb1.aActionListener(new ActionListener()public voi actionPerforme(ActionEvent e);jb1.setFont(newFont(Font.BOL,jb1.setBouns(72, 179, 111, 34); contentPane.a(jb1);jb2 = new JButton(u8F4u56E); jb2.setFont(newFont(Font.BOL

44、,jb2.setBouns(247, 179, 111, 34); contentPane.a(jb2);cl=jm.getCl(); =jm.get(); .a(contentPane,three); jb1.aActionListener(this); jb2.aActionListener(this);Overriepublic voi actionPerforme(ActionEvent e) / TOO Auto-generate methoif(e.getSource()=jb1)JOptionPane.showMessageialog(null,入库成if(e.getSource

45、()=jb2)cl.show(,two);package 仓库管理;importjava.awt.BorerLayout; import java.awt.CarLayout; import java.awt.EventQueue;import javax.swing.JFrame; PAGE PAGE 43importjavax.swing.JOptionPane; importjavax.swing.anel;importjavax.swing.borer.EmptyBorer; importjavax.swing.JLabel;import java.awt.Font;import ja

46、va.awt.event.ActionEvent; importjava.awt.event.ActionListener;importjavax.swing.JTextFiel; import javax.swing.JButton;publicclassjiemian4extensActionListenerprivate anel contentPane; private JTextFiel textFiel; private JTextFielprivate JTextFieljiemian jm;CarLayout cl; anel ;implementsJButton jb1,jb

47、2;/*/*Launch theapplication.*/*Create theframe.*/public jiemian4() setefaultCloseOperation(JFrame.EXIT_ON_CLOSE); setBouns(100, 100, 450, 300);contentPane = new anel(); contentPane.setBorer(new EmptyBorer(5, 5,5);setContentPane(contentPane); contentPane.setLayout(null);JLabellabel=JLabel(u51FAu5E93u4EA7u54C1u540u79F0uFF1A);15);label.setFont(new Font(Font.BOL, label.setBouns(36, 35, 112, 28); contentPane.a(label);textFiel = new JTextFiel(); textFiel.setFont(new Font(Font.BOL,textFiel.setColumns(10); te