CREATE OR REPLACE PROCEDURE Get_Code_Quantity
(
v_Code IN NUMBER DEFAULT NULL ,
v_WareHouseNo IN NUMBER DEFAULT NULL ,
v_Quantity1 OUT NUMBER,
/*小单位库存*/v_LockUnit1 OUT NUMBER,
/*小单位冻结*/v_Quantity2 OUT NUMBER,
/*大单位库存*/v_LockUnit2/*大单位冻结*/ OUT NUMBER
)
AS
BEGIN
--declare @Quantity Money
--declare @LockUnit Money
SELECT Nvl(SUM(UnGiveMaxQuantity),0),
Nvl(SUM(UnGiveMinQuantity),0)
INTO v_LockUnit1,
v_LockUnit2
FROM YKGL_RANGE
WHERE Code = v_Code
AND WarehouseNo = v_WareHouseNo
AND DelMark = 0;
SELECT Nvl(SUM(MinUnitSurplus),0),
Nvl(SUM(MaxUnitSurplus),0)
INTO v_Quantity1,
v_Quantity2
FROM YKGL_IMPORT
WHERE Code = v_Code
AND WarehouseNo = v_WareHouseNo
AND StatMark = 9;
END;
CREATE OR REPLACE PROCEDURE ExportDicData
AS
Cursor c1
IS
select * from
(
Select Upper(TableName) as TableName from Xt_Downtables
union
Select Upper(Table_Name) as TableName from table_name where download = 1
) AA where Upper(TableName) = 'PACS_RPT';
v_TableName varchar2(50);
v_SQL varchar(2000);
begin
Open c1;
Loop
FETCH c1 Into v_TableName;
IF c1%NOTFOUND then Exit;
End if;
v_SQL := 'Insert into ' || v_TableName || + '@His_Res select * from ' || v_TableName;
begin
execute immediate v_SQL;
Commit;
Exception
when Others then dbms_output.put_line(v_TableName||'同步失败！');
end;
END Loop;
end;
CREATE OR REPLACE PROCEDURE getUniqueID
(
v_maxidentity IN NUMBER DEFAULT NULL ,
v_Fstation IN NUMBER DEFAULT NULL ,
v_Foperator IN NUMBER DEFAULT NULL ,
v_Fdatetime IN VARCHAR2 DEFAULT NULL ,
v_Frandom IN NUMBER DEFAULT NULL ,
v_Fidentity OUT NUMBER
)
AS
BEGIN
SELECT ( SELECT onlyid
FROM XT_IDENTITY
--WHERE ( station = @Fstation ) and ( operator=@Foperator ) and (random=@Frandom))
--WHERE onlyid>@maxidentity and station = @Fstation and operator=@Foperator and dt=RTrim(LTrim(@Fdatetime)) and random=@Frandom)
WHERE random = v_Frandom
AND dt = v_Fdatetime
AND station = v_Fstation
AND operator = v_Foperator
AND onlyid > v_maxidentity AND ROWNUM <= 1 )
INTO v_Fidentity
FROM DUAL ;
END;
