---最小齐套日期
SELECT @COMPLETE_DATE = MIN(COMPLETE_DATE)
FROM MC_MATERIAL_COMPLETE
WHERE MPS_NO = @MPS_NO AND
PLAN_NO IS NULL
IF @KC_TAG <> 'R' AND (@COMPLETE_DATE IS NULL OR @COMPLETE_DATE = NULL)
BEGIN
RETURN (-6)
END
IF @KC_TAG = 'R'
BEGIN
SELECT @COMPLETE_DATE = GETDATE()
END
---最大齐套数量
SELECT @QTY_COMPLETE = MAX(QTY_COMPLETE)
FROM MC_MATERIAL_COMPLETE
WHERE MPS_NO = @MPS_NO AND
PLAN_NO IS NULL
IF @KC_TAG <> 'R' AND (@QTY_COMPLETE IS NULL OR @QTY_COMPLETE = NULL)
BEGIN
RETURN (-6)
END
SELECT @TIME_MAUN = ISNULL(TIME_MAUN,0)
FROM MODEL_MANU_TIME
WHERE MODEL_NO = @MODEL_NO
IF @KC_TAG <> 'R' AND (@TIME_MAUN IS NULL OR @TIME_MAUN = NULL OR @TIME_MAUN = 0)
BEGIN
RETURN (-1)
END
IF @PRODUCE_DEPART IS NULL OR @PRODUCE_DEPART = NULL OR @PRODUCE_DEPART = ''
---获取最优生产部门
BEGIN
SELECT @PRODUCE_DEPART = DEPARTMENT_NO
FROM MODEL_MANU_PRDDEPART_SEQ
WHERE MODEL_NO = @MODEL_NO AND
SEQ_NO = (SELECT MIN(SEQ_NO)
FROM MODEL_MANU_PRDDEPART_SEQ
WHERE MODEL_NO = @MODEL_NO)
IF @PRODUCE_DEPART IS NULL OR @PRODUCE_DEPART = NULL
BEGIN
RETURN (-2)
END
END
IF @QTY_SCHEDULE IS NULL OR @QTY_SCHEDULE = NULL OR @QTY_SCHEDULE = 0
---获取待排产数量
BEGIN
IF @QTY_UNSCHEDULE IS NULL OR @QTY_UNSCHEDULE = NULL OR @QTY_UNSCHEDULE = 0
BEGIN
RETURN (-3)
END
ELSE
BEGIN
SELECT @QTY_SCHEDULE = @QTY_UNSCHEDULE
END
END
IF @QTY_SCHEDULE > @QTY_UNSCHEDULE
BEGIN
SELECT @QTY_SCHEDULE = @QTY_UNSCHEDULE
END
IF @KC_TAG <> 'R' AND @QTY_SCHEDULE > @QTY_COMPLETE
BEGIN
SELECT @QTY_SCHEDULE = @QTY_COMPLETE
END
IF @QTY_INTOHOUR IS NULL OR @QTY_INTOHOUR = NULL OR @QTY_INTOHOUR = 0
---获取应投入工时
BEGIN
IF @KC_TAG <> 'R'
BEGIN
IF @TIME_MAUN = 0
BEGIN
SELECT @QTY_INTOHOUR = 0
END
ELSE
BEGIN
SELECT @QTY_INTOHOUR = @QTY_SCHEDULE / @TIME_MAUN
END
END
ELSE
BEGIN
SELECT @QTY_INTOHOUR = 0
END
END
IF @START_DATE <> NULL AND NOT @START_DATE IS NULL
---检查开始日期是否在工作日历范围内
BEGIN
IF NOT EXISTS(SELECT * FROM PERIOD_DATE WHERE BEGIN_DATE <= @START_DATE AND END_DATE >= @START_DATE)
BEGIN
RETURN (-5)
END
END
IF @END_DATE <> NULL AND NOT @END_DATE IS NULL
---检查结束日期是否在工作日历范围内
BEGIN
IF NOT EXISTS(SELECT * FROM PERIOD_DATE WHERE BEGIN_DATE <= @END_DATE AND END_DATE >= @END_DATE)
BEGIN
RETURN (-5)
END
END
IF @END_DATE <> NULL OR @END_DATE IS NOT NULL
---提供结束日期
BEGIN
SELECT @FREEHOUR = 0
SELECT @ORIENT = 'E'
SELECT @OUT_RANGE = 'N'
SELECT @TEMP_START_DATE = @END_DATE
SELECT @TOTAL_QTY_SCHEDULE = 0
SELECT @TOTAL_QTY_INTOHOUR = 0
SELECT @QTY_COMPLETE_LAST = 0
SELECT @QTY_SCHEDULE_WAIT = 0
SELECT @QTY_SCHEDULE_LAST = 0
WHILE (@TOTAL_QTY_SCHEDULE < @QTY_SCHEDULE AND @OUT_RANGE = 'N' )
BEGIN
---待排数量
SELECT @QTY_SCHEDULE_WAIT = @QTY_SCHEDULE - @TOTAL_QTY_SCHEDULE
---剩余工时
SELECT @FREEHOUR = ISNULL(QTY_LOCKPLANFREEHOUR,0)
FROM WORK_DATE_PLAN
WHERE GROUP_ID = @PRODUCE_DEPART AND
CONVERT(VARCHAR(10),WORKDATE,121) = CONVERT(VARCHAR(10),@TEMP_START_DATE,121)
---根据工时算出的可排产数量
SELECT @QTY_SCHEDULE_LABOUR = CEILING(@FREEHOUR * @TIME_MAUN)
---本期齐套数量
SELECT @QTY_COMPLETE_THIS = ISNULL(QTY_COMPLETE,0)
FROM MC_MATERIAL_COMPLETE
WHERE MPS_NO = @MPS_NO AND
PLAN_NO IS NULL AND
COMPLETE_DATE = (SELECT MAX(COMPLETE_DATE)
FROM MC_MATERIAL_COMPLETE
WHERE MPS_NO = @MPS_NO AND
PLAN_NO IS NULL AND
CONVERT(VARCHAR(10),COMPLETE_DATE,121) <= CONVERT(VARCHAR(10),@TEMP_START_DATE,121))
IF @QTY_COMPLETE_LAST IS NULL OR @QTY_COMPLETE_LAST = NULL OR @QTY_COMPLETE_LAST = 0
BEGIN
SELECT @QTY_COMPLETE_LAST = @QTY_COMPLETE_THIS
END
---后期齐套数量减后期排产数量等于齐套待排数量
SELECT @QTY_SCHEDULE_MATERIAL = @QTY_COMPLETE_LAST - @QTY_SCHEDULE_LAST
---取根据工时算出的可排产数量、本期齐套数量、齐套待排数量和待排产数量的最小值作为当天排产数量
SELECT @MIN_STRING = CONVERT(VARCHAR(20),@QTY_SCHEDULE_LABOUR) +','+CONVERT(VARCHAR(20),@QTY_COMPLETE_THIS)+','
+ CONVERT(VARCHAR(20),@QTY_SCHEDULE_MATERIAL)+','+CONVERT(VARCHAR(20),@QTY_SCHEDULE_WAIT)
EXECUTE USP_MIN @MIN_STRING,'N',@MIN_STRING OUTPUT
---当天排产数量
SELECT @QTY_SCHEDULE_DETAIL = CEILING(CONVERT(NUMERIC(16,4),@MIN_STRING))
---当天应占用工时
SELECT @QTY_INTOHOUR_DETAIL = @QTY_SCHEDULE_DETAIL / @TIME_MAUN
---总计排产数量
SELECT @TOTAL_QTY_SCHEDULE = @TOTAL_QTY_SCHEDULE + @QTY_SCHEDULE_DETAIL
---总计排产工时
SELECT @TOTAL_QTY_INTOHOUR = @TOTAL_QTY_INTOHOUR + @QTY_INTOHOUR_DETAIL
---后期排产数量
SELECT @QTY_SCHEDULE_LAST = @QTY_SCHEDULE_DETAIL
SELECT @START_DATE = @TEMP_START_DATE
---判断日期是否超出范围
IF NOT EXISTS(SELECT * FROM PERIOD_DATE
WHERE CONVERT(VARCHAR(10),BEGIN_DATE,121) <= CONVERT(VARCHAR(10),@TEMP_START_DATE,121) AND
CONVERT(VARCHAR(10),END_DATE,121) >= CONVERT(VARCHAR(10),@TEMP_START_DATE,121)) OR
CONVERT(VARCHAR(10),@TEMP_START_DATE ,121) < CONVERT(VARCHAR(10),GETDATE(),121) OR
CONVERT(VARCHAR(10),@TEMP_START_DATE ,121) < CONVERT(VARCHAR(10),@COMPLETE_DATE,121)
BEGIN
SELECT @OUT_RANGE = 'Y'
END
ELSE
BEGIN
SELECT @OUT_RANGE = 'N'
END
---插入到临时表
IF @QTY_SCHEDULE_DETAIL > 0
BEGIN
INSERT INTO #TEMP_MPS_PLAN_DETAIL
( MPS_NO,
PRODUCE_DEPART,
QTY_SCHEDULE,
QTY_INTOHOUR,
START_DATE,
END_DATE,
PRODUCE_DATE,
QTY_SCHEDULE_DETAIL,
QTY_INTOHOUR_DETAIL)
VALUES ( @MPS_NO,
@PRODUCE_DEPART,
@QTY_SCHEDULE,
@QTY_INTOHOUR,
@START_DATE,
@END_DATE,
@TEMP_START_DATE,
@QTY_SCHEDULE_DETAIL,
@QTY_INTOHOUR_DETAIL)
END
SELECT @TEMP_START_DATE = DATEADD(DAY,-1,@TEMP_START_DATE)
END
IF @OUT_RANGE = 'Y'
BEGIN
DELETE FROM #TEMP_MPS_PLAN_DETAIL
SELECT @START_DATE = @COMPLETE_DATE
SELECT @ORIENT = 'S'
END
END
IF @ORIENT <> 'E'
BEGIN
IF @START_DATE IS NULL OR @START_DATE = NULL
BEGIN
SELECT @START_DATE = GETDATE()
END
IF @START_DATE < @COMPLETE_DATE
BEGIN
SELECT @START_DATE = @COMPLETE_DATE
END
SELECT @FREEHOUR = 0
SELECT @OUT_RANGE = 'N'
SELECT @TEMP_END_DATE = @START_DATE
SELECT @QTY_SCHEDULE_DETAIL = 0
SELECT @QTY_INTOHOUR_DETAIL = 0
SELECT @TOTAL_QTY_SCHEDULE = 0
SELECT @TOTAL_QTY_INTOHOUR = 0
SELECT @QTY_COMPLETE_LAST = 0
WHILE @TOTAL_QTY_SCHEDULE < @QTY_SCHEDULE AND @OUT_RANGE = 'N'
BEGIN
---待排数量
SELECT @QTY_SCHEDULE_WAIT = @QTY_SCHEDULE - @TOTAL_QTY_SCHEDULE
---剩余工时
SELECT @FREEHOUR = ISNULL(QTY_LOCKPLANFREEHOUR,0)
FROM WORK_DATE_PLAN
WHERE GROUP_ID = @PRODUCE_DEPART AND
CONVERT(VARCHAR(10),WORKDATE,121) = CONVERT(VARCHAR(10),@TEMP_END_DATE,121)
---根据工时算出的可排产数量
SELECT @QTY_SCHEDULE_LABOUR = CEILING(@FREEHOUR * @TIME_MAUN)
---本期齐套数量
SELECT @QTY_COMPLETE_THIS = ISNULL(QTY_COMPLETE,0)
FROM MC_MATERIAL_COMPLETE
WHERE MPS_NO = @MPS_NO AND
PLAN_NO IS NULL AND
COMPLETE_DATE = (SELECT MAX(COMPLETE_DATE)
FROM MC_MATERIAL_COMPLETE
WHERE MPS_NO = @MPS_NO AND
PLAN_NO IS NULL AND
CONVERT(VARCHAR(10),COMPLETE_DATE,121) <= CONVERT(VARCHAR(10),@TEMP_END_DATE,121))
---后期齐套数量减已排产数量等于齐套待排数量
SELECT @QTY_SCHEDULE_MATERIAL = @QTY_COMPLETE_THIS - @TOTAL_QTY_SCHEDULE
---取根据工时算出的可排产数量、齐套待排数量和待排产数量的最小值作为当天排产数量
SELECT @MIN_STRING = CONVERT(VARCHAR(20),@QTY_SCHEDULE_LABOUR) +','
+ CONVERT(VARCHAR(20),@QTY_SCHEDULE_MATERIAL)+','+CONVERT(VARCHAR(20),@QTY_SCHEDULE_WAIT)
EXECUTE USP_MIN @MIN_STRING,'N',@MIN_STRING OUTPUT
---当天排产数量
SELECT @QTY_SCHEDULE_DETAIL = CEILING(CONVERT(NUMERIC(16,4),@MIN_STRING))
---当天应占用工时
SELECT @QTY_INTOHOUR_DETAIL = @QTY_SCHEDULE_DETAIL / @TIME_MAUN
---总计排产数量
SELECT @TOTAL_QTY_SCHEDULE = @TOTAL_QTY_SCHEDULE + @QTY_SCHEDULE_DETAIL
---总计排产工时
SELECT @TOTAL_QTY_INTOHOUR = @TOTAL_QTY_INTOHOUR + @QTY_INTOHOUR_DETAIL
---后期排产数量
SELECT @QTY_SCHEDULE_LAST = @QTY_SCHEDULE_DETAIL
SELECT @END_DATE = @TEMP_END_DATE
---判断日期是否超出范围
IF NOT EXISTS(SELECT * FROM PERIOD_DATE
WHERE CONVERT(VARCHAR(10),BEGIN_DATE,121) <= CONVERT(VARCHAR(10),@TEMP_END_DATE,121) AND
CONVERT(VARCHAR(10),END_DATE,121) >= CONVERT(VARCHAR(10),@TEMP_END_DATE,121)) OR
CONVERT(VARCHAR(10),@TEMP_END_DATE ,121) < CONVERT(VARCHAR(10),GETDATE(),121) OR
CONVERT(VARCHAR(10),@TEMP_END_DATE ,121) < CONVERT(VARCHAR(10),@COMPLETE_DATE,121)
BEGIN
SELECT @OUT_RANGE = 'Y'
END
ELSE
BEGIN
SELECT @OUT_RANGE = 'N'
END
IF @QTY_SCHEDULE_DETAIL > 0
BEGIN
---插入到临时表
INSERT INTO #TEMP_MPS_PLAN_DETAIL
( MPS_NO,
PRODUCE_DEPART,
QTY_SCHEDULE,
QTY_INTOHOUR,
START_DATE,
END_DATE,
PRODUCE_DATE,
QTY_SCHEDULE_DETAIL,
QTY_INTOHOUR_DETAIL)
VALUES ( @MPS_NO,
@PRODUCE_DEPART,
@QTY_SCHEDULE,
@QTY_INTOHOUR,
@START_DATE,
@END_DATE,
@TEMP_END_DATE,
@QTY_SCHEDULE_DETAIL,
@QTY_INTOHOUR_DETAIL)
END
SELECT @TEMP_END_DATE = DATEADD(DAY,1,@TEMP_END_DATE)
END
IF @OUT_RANGE = 'Y'
BEGIN
DELETE FROM #TEMP_MPS_PLAN_DETAIL
END
END
SELECT @START_DATE = MIN(PRODUCE_DATE),
@END_DATE = MAX(PRODUCE_DATE)
FROM #TEMP_MPS_PLAN_DETAIL
UPDATE #TEMP_MPS_PLAN_DETAIL
SET START_DATE = @START_DATE,
END_DATE = @END_DATE
SELECT MPS_NO,
PRODUCE_DEPART,
QTY_SCHEDULE,
QTY_INTOHOUR,
START_DATE,
END_DATE,
PRODUCE_DATE,
QTY_SCHEDULE_DETAIL,
QTY_INTOHOUR_DETAIL
FROM #TEMP_MPS_PLAN_DETAIL
ORDER BY MPS_NO,PRODUCE_DATE
5、通过oradim.exe命令,在服务里生成一个新的实例管理服务,启动方式为手工
set ORACLE_SID=test
D:\Oracle\Ora81\bin\oradim -new -sid test -startmode manual -pfile "D:\Oracle\admin\test\pfile\inittest.ora"
6、生成各种数据库对象
D:\>svrmgrl
--创建数据库
connect INTERNAL/oracle
startup nomount pfile="D:\Oracle\admin\test\pfile\inittest.ora"
CREATE DATABASE test
LOGFILE 'D:\Oracle\oradata\test\redo01.log' SIZE 2048K,
'D:\Oracle\oradata\test\redo02.log' SIZE 2048K,
'D:\Oracle\oradata\test\redo03.log' SIZE 2048K
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXLOGHISTORY 1
DATAFILE 'D:\Oracle\oradata\test\system01.dbf' SIZE 58M REUSE AUTOEXTEND ON NEXT 640K
MAXDATAFILES 254
MAXINSTANCES 1
CHARACTER SET ZHS16GBK
NATIONAL CHARACTER SET ZHS16GBK;
控制文件、日志文件在上面语句执行时生成
connect INTERNAL/oracle
--修改系统表空间
ALTER TABLESPACE SYSTEM DEFAULT STORAGE ( INITIAL 64K NEXT 64K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 50);
ALTER TABLESPACE SYSTEM MINIMUM EXTENT 64K;
--创建回滚表空间
CREATE TABLESPACE RBS DATAFILE 'D:\Oracle\oradata\test\rbs01.dbf' SIZE 256M REUSE
AUTOEXTEND ON NEXT 5120K
MINIMUM EXTENT 512K
DEFAULT STORAGE ( INITIAL 512K NEXT 512K MINEXTENTS 8 MAXEXTENTS 4096);
--创建用户表空间
CREATE TABLESPACE USERS DATAFILE 'D:\Oracle\oradata\test\users01.dbf' SIZE 128M REUSE
AUTOEXTEND ON NEXT 1280K
MINIMUM EXTENT 128K
DEFAULT STORAGE ( INITIAL 128K NEXT 128K MINEXTENTS 1 MAXEXTENTS 4096 PCTINCREASE 0);
--创建临时表空间
CREATE TABLESPACE TEMP DATAFILE 'D:\Oracle\oradata\test\temp01.dbf' SIZE 32M REUSE
AUTOEXTEND ON NEXT 640K
MINIMUM EXTENT 64K
DEFAULT STORAGE ( INITIAL 64K NEXT 64K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0) TEMPORARY;
--创建工具表空间
CREATE TABLESPACE TOOLS DATAFILE 'D:\Oracle\oradata\test\tools01.dbf' SIZE 64M REUSE
AUTOEXTEND ON NEXT 320K
MINIMUM EXTENT 32K
DEFAULT STORAGE ( INITIAL 32K NEXT 32K MINEXTENTS 1 MAXEXTENTS 4096 PCTINCREASE 0);
--创建索引表空间
CREATE TABLESPACE INDX DATAFILE 'D:\Oracle\oradata\test\indx01.dbf' SIZE 32M REUSE
AUTOEXTEND ON NEXT 1280K
MINIMUM EXTENT 128K
DEFAULT STORAGE ( INITIAL 128K NEXT 128K MINEXTENTS 1 MAXEXTENTS 4096 PCTINCREASE 0);