photocheck.sql

采用Delphi7+Oracle. 该软件是由交警人员对各个路口闯红灯违法照片进行有效保存

connect / as sysdba 
/
create user photocheck identified by photocheck
/
grant dba to photocheck
/
grant create any table to photocheck
/
grant select any table to photocheck
/
connect photocheck/photocheck
/

prompt
prompt Creating table P_CODE
prompt =====================
prompt
create table PHOTOCHECK.P_CODE
(
  DMLB VARCHAR2(10) not null,
  DMZ  VARCHAR2(20) not null,
  DMSM VARCHAR2(30)
)
tablespace SYSTEM
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

prompt
prompt Creating table P_CROSSING
prompt =========================
prompt
create table PHOTOCHECK.P_CROSSING
(
  COMPUTERID VARCHAR2(10),
  CROSSNAME  VARCHAR2(20),
  CROSSMEMO  VARCHAR2(50)
)
tablespace SYSTEM
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

prompt
prompt Creating table P_FILEPARAM
prompt ==========================
prompt
create table PHOTOCHECK.P_FILEPARAM
(
  MODES         VARCHAR2(55),
  EQUIPMENTID  VARCHAR2(55),
  YEAR         VARCHAR2(40),
  MONTH        VARCHAR2(40),
  DAY          VARCHAR2(40),
  HOUR         VARCHAR2(40),
  MINUTE       VARCHAR2(40),
  SECOND       VARCHAR2(40),
  REDLIGHT     VARCHAR2(55),
  RUNDIRECTION VARCHAR2(55),
  PICSEQ       VARCHAR2(55),
  MEMOBIT      VARCHAR2(55),
  SPEED        VARCHAR2(55),
  INSERTTIME   DATE not null
)
tablespace SYSTEM
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

prompt
prompt Creating table P_OPERATELOG
prompt ===========================
prompt
create table PHOTOCHECK.P_OPERATELOG
(
  USERID      VARCHAR2(50) not null,
  USERNAME    VARCHAR2(50) not null,
  OPERATEDATE DATE not null,
  OPERATEITEM VARCHAR2(55) not null
)
tablespace SYSTEM
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

prompt
prompt Creating table P_PICINFO
prompt ========================
prompt
create table PHOTOCHECK.P_PICINFO
(
  XH          VARCHAR2(12) not null,
  ONLYID      VARCHAR2(50),
  SERIALID    VARCHAR2(30),
  EQUIPID     VARCHAR2(50),
  ZPSJ        DATE,
  REDLATER    VARCHAR2(50),
  ZPSERIAL    VARCHAR2(5),
  LOOPNO      VARCHAR2(10),
  WHOLENAME   VARCHAR2(55) not null,
  ISAVAILABLY VARCHAR2(1) default 1 not null,
  ISSAVELIST  VARCHAR2(1) default 0 not null,
  ISDELETE    VARCHAR2(1) default 0 not null,
  ISCOMPARE   VARCHAR2(1) default 0 not null,
  COMPAREDATE DATE,
  USERID      VARCHAR2(25)
)
tablespace SYSTEM
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
alter table PHOTOCHECK.P_PICINFO
  add constraint P_PICINFO_PK11201595121879 primary key (XH)
  using index 
  tablespace SYSTEM
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

prompt
prompt Creating table P_PICPHOTO
prompt =========================
prompt
create table PHOTOCHECK.P_PICPHOTO
(
  XH    VARCHAR2(12) not null,
  PHOTO BLOB
)
tablespace SYSTEM
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

prompt
prompt Creating table P_SAVELIST
prompt =========================
prompt
create table PHOTOCHECK.P_SAVELIST
(
  HPHM VARCHAR2(50) not null,
  HPZL VARCHAR2(50),
  LRR  VARCHAR2(25),
  LRSJ DATE,
  BZ   VARCHAR2(55)
)
tablespace SYSTEM
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

prompt
prompt Creating table P_USER
prompt =====================
prompt
create table PHOTOCHECK.P_USER
(
  USERID   NUMBER(10) not null,
  USERNAME VARCHAR2(10) not null,
  PASSWORD VARCHAR2(10) not null
)
tablespace SYSTEM
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
alter table PHOTOCHECK.P_USER
  add constraint USERID primary key (USERID)
  using index 
  tablespace SYSTEM
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

prompt
prompt Creating table P_USERPOWER
prompt ==========================
prompt
create table PHOTOCHECK.P_USERPOWER
(
  USERID   VARCHAR2(10) not null,
  MENUITEM VARCHAR2(20) not null,
  MENUMAIN VARCHAR2(30) not null
)
tablespace SYSTEM
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

prompt
prompt Creating sequence P_PHOTOXH
prompt ===========================
prompt
create sequence PHOTOCHECK.P_PHOTOXH
minvalue 1
maxvalue 9999999999999999999999999999
start with 1
increment by 1
cache 20
order;

prompt
prompt Creating package PACK_PHOTO
prompt ===========================
prompt
CREATE OR REPLACE PACKAGE PHOTOCHECK."PACK_PHOTO" as
    TYPE PXH is table of varchar2(12) index by binary_integer;
    procedure getPhotoXh(ponlyid varchar2,pserialid varchar2,pequipid varchar2,
              pzpsj varchar2,predlater varchar2,pzpserial varchar2,ploopno varchar2,
              pwholename varchar2,photoxh out PXH);  
end;
/

prompt
prompt Creating procedure OPERATELOG
prompt =============================
prompt
CREATE OR REPLACE PROCEDURE PHOTOCHECK."OPERATELOG" (
  puserid in varchar2,
  pusername in varchar2,
  poperatedate in date,
  poperateitem in varchar
)
as
begin
  INSERT INTO photocheck.P_OPERATELOG (userid,username,
  operatedate,operateitem) values 
  (puserid,pusername,poperatedate,poperateitem);
  commit;
end;
/

prompt
prompt Creating package body PACK_PHOTO
prompt ================================
prompt
CREATE OR REPLACE PACKAGE BODY PHOTOCHECK."PACK_PHOTO" as
  --TYPE PXH is table of varchar2(12) index by binary_integer;
    procedure getPhotoXh(ponlyid varchar2,pserialid varchar2,pequipid varchar2,
              pzpsj varchar2,predlater varchar2,pzpserial varchar2,ploopno varchar2,
              pwholename varchar2,photoxh out PXH)
is
  pxh varchar2(12);
begin
  select photocheck.P_PHOTOXH.nextval into pxh from dual;
   
  INSERT INTO photocheck.P_PICINFO 
  (xh,onlyid,serialid,equipid,zpsj,redlater,zpserial,loopno,wholename)          
  values (pxh,ponlyid,pserialid,pequipid,TO_DATE(pzpsj ,'YYYY-MM-DD HH24:MI:SS'),predlater,pzpserial,
          ploopno,pwholename);
  commit; 
  photoxh(1):=pxh;
end;
 
end;
/


disconnect;
exit;