ORACLE存储过程
1--------------/*操作表Users*/
create or REPLACE procedure userm_Users
(
state varchar2,
UserID rowID,
UserAccount varchar2,
UserName varchar2,
Password varchar2,
InsTime Date,
LastActivityDate Date,
States varchar2,
Note varchar2
)
as
v_num number:=0;
begin
select 1 into v_num from Users WHERE UserID=UserID and rownum<2;
if v_num=1 then
if state = 'UPDATE' then
begin
UPDATE Users SET UserAccount=UserAccount,UserName=UserName, Password=Password, InsTime=InsTime, LastActivityDate=LastActivityDate, States=States,
Note=Note WHERE UserID=UserID;
DELETE FROM UsersInRoles WHERE UserID=UserID;
DELETE FROM UsersInDepartments WHERE UserID=UserID;
end;
elsif state='DELETE' then
begin
DELETE FROM Users WHERE UserID=UserID and UserAccount=UserAccount;
DELETE FROM UsersInRoles WHERE UserID=UserID;
DELETE FROM UsersInDepartments WHERE UserID=UserID;
end;
end if;
else
if (state ='INSERT') then
INSERT INTO Users(UserID,UserAccount,UserName, Password, InsTime, LastActivityDate, States, Note)
VALUES(UserID,UserAccount, UserName, Password, InsTime, LastActivityDate, States, Note);
end if;
end if;
end;
2-------建立程序包
CREATE OR REPLACE package LMDC.pk_wtuser
is
type mytype is ref cursor;
procedure userm_TheID(mytable varchar2,IDValue rowID,mycs out mytype);
end;
CREATE OR REPLACE package BODY pk_wtuser
is
procedure userm_TheID(mytable varchar2,IDValue rowID,mycs out mytype)
as
begin
if mytable='RolesInPrivileges' then
open mycs for SELECT PrivilegeName FROM RolesInPrivileges WHERE RoleID=IDValue;
elsif mytable='UsersInRoles' then
open mycs for SELECT RoleID FROM UsersInRoles WHERE UserID=IDValue;
elsif mytable='UsersInDepartments' then
open mycs for SELECT * FROM UsersInDepartments WHERE UserID=IDValue;
elsif mytable='Privileges' then
open mycs for SELECT p.PrivilegeName,p.Note FROM Privileges p ,RolesInPrivileges r WHERE p. PrivilegeName=r.PrivilegeName AND r.RoleID=IDValue;
elsif mytable='Users' then
open mycs for SELECT * FROM Users WHERE UserID=IDValue;
elsif mytable='FactoryInfo' then
open mycs for SELECT * FROM FactoryInfo WHERE FaID='#';
elsif mytable='Departments' then
open mycs for SELECT * FROM Departments WHERE FaID='#';
elsif mytable='MachineToolGroups' then
open mycs for SELECT * FROM MachineToolGroups WHERE DeptID=IDValue;
elsif mytable='MachineToolsA' then
open mycs for SELECT M.*,D.DeptName,G.MaTolGrpName FROM MachineTools M,MachineToolGroups G,Departments D WHERE M.MaTolGrpID=IDValue AND
M.DeptID=D.DeptID AND M.MaTolGrpID=G.MaTolGrpID ;
elsif mytable='MachineToolsB' then
open mycs for SELECT M.*,D.DeptName,G.MaTolGrpName FROM MachineTools M,MachineToolGroups G,Departments D
WHERE M.DeptID=IDValue AND M.DeptID=D.DeptID AND M.MaTolGrpID=G.MaTolGrpID ;
elsif mytable='SiemensExtend' then
open mycs for SELECT * FROM SiemensExtend WHERE MaTolID=IDValue;
elsif mytable='LITERMINALInfo' then
open mycs for SELECT I.*, M.AccessPort FROM LITERMINALDefine M,LITERMINALInfoDefine I WHERE M.LITERMINALIP=I.LITERMINALIP AND I.MaTolID=IDValue;
end if;
end userm_TheID;
end pk_wtuser;
3、--------------------------/*操作表Roles*/
create or REPLACE procedure userm_Roles
(
state varchar2,
RoleID rowID,
RoleName varchar2,
InsTime Date,
Note varchar2
)
as
v_num number;
begin
select 1 into v_num from Roles WHERE RoleID=RoleID and rownum<2;
if v_num=1 then
if state='UPDATE' then
begin
UPDATE Roles
SET RoleName=RoleName,InsTime=InsTime,Note=Note WHERE RoleID=RoleID;
DELETE FROM RolesInPrivileges WHERE RoleID=RoleID;
end;
elsif(state='DELETE') then
begin
DELETE FROM Roles WHERE RoleID=RoleID;
DELETE FROM RolesInPrivileges WHERE RoleID=RoleID;
end;
end if;
else
if (state='INSERT') then
INSERT INTO Roles (RoleID,RoleName,InsTime,Note)Values(RoleID,RoleName,InsTime,Note);
end if;
end if;
/*SELECT @@RowCOUNT as counts;*/
end;
4--------------------------------------/*操作表MachineTools*/
2-------建立程序包
CREATE OR REPLACE package LMDC.pk_wtuserm_MachineTools
is
type mytype is ref cursor;
procedure userm_MachineTools(state varchar2,
MaTolID rowID,
MaTolGrpID rowID,
DeptID rowID,
MaTolName varchar2,
MaTolType varchar2,
MaTolPort number,
MaTolIP varchar2,
ColServerID varchar2,
InsTime Date,
Note varchar2,mycs out mytype);
end;
---------------------
CREATE OR REPLACE package BODY LMDC.pk_wtuserm_MachineTools
is
procedure userm_MachineTools
(
state varchar2,
MaTolID rowID,
MaTolGrpID rowID,
DeptID rowID,
MaTolName varchar2,
MaTolType varchar2,
MaTolPort number,
MaTolIP varchar2,
ColServerID varchar2,
InsTime Date,
Note varchar2,
mycs out mytype
)
as
MaTolName_o varchar2(100);
v_num number;
begin
if state='SELECT' then
open mycs for SELECT M.*,D.DeptName,G.MaTolGrpName FROM MachineTools M,MachineToolGroups G,Departments D
WHERE M.DeptID=D.DeptID AND M.MaTolGrpID=G.MaTolGrpID AND M.MaTolID=MaTolID and M.MaTolName=MaTolName ;
select 1 into v_num FROM MachineTools WHERE MaTolID=MaTolID and rownum<2;
if v_num=1 then
if (state='UPDATE') then
begin
SELECT MaTolName into MaTolName_o FROM MachineTools WHERE MaTolID=MaTolID;
UPDATE MachineTools
SET MaTolGrpID=MaTolGrpID,DeptID=DeptID, MaTolName=MaTolName, MaTolType=MaTolType, MaTolIP=MaTolIP,MaTolPort=MaTolPort, ColServerID=ColServerID, InsTime=InsTime, Note=Note
WHERE MaTolID=MaTolID;
UPDATE MachineBaseRunState SET MaTolName=MaTolName WHERE MaTolName=MaTolName_o;
UPDATE ProgramRunInfo SET MaTolName=MaTolName WHERE MaTolName=MaTolName_o;
UPDATE AlarmRunInfo SET MaTolName=MaTolName WHERE MaTolName=MaTolName_o;
UPDATE ClientMonitorInfo SET MaTolName=MaTolName WHERE MaTolName=MaTolName_o;
UPDATE MachineRunInfo SET MaTolName=MaTolName WHERE MaTolName=MaTolName_o;
DELETE FROM LITERMINALInfoDefine
WHERE MaTolID=MaTolID;
DELETE FROM LITERMINALDefine
WHERE MaTolID=MaTolID;
DELETE FROM SiemensExtend
WHERE MaTolID=MaTolID;
end;
elsif(state='DELETE') then
begin
DELETE FROM MachineTools
WHERE MaTolName=MaTolName and MaTolID=MaTolID;
DELETE FROM LITERMINALInfoDefine
WHERE MaTolID=MaTolID;
DELETE FROM LITERMINALDefine
WHERE MaTolID=MaTolID;
DELETE FROM SiemensExtend
WHERE MaTolID=MaTolID;
end;
end if;
else
if (state='INSERT') then
INSERT INTO MachineTools( MaTolID,MaTolGrpID, DeptID, MaTolName, MaTolType, MaTolIP, MaTolPort, ColServerID, InsTime, Note)
VALUES( MaTolID,MaTolGrpID, DeptID, MaTolName, MaTolType, MaTolIP, MaTolPort, ColServerID,InsTime,Note);
end if;
/*SELECT RowCOUNT as counts*/
end if;
end if;
end userm_MachineTools;
end pk_wtuserm_MachineTools;
5\-----------------/操作表MachineToolGroups*/
CREATE OR REPLACE package LMDC.pk_wtMachineToolGroups
is
type mytype is ref cursor;
procedure userm_MachineToolGroups(state varchar2,
MaTolGrpID rowid,
DeptID rowID,
MaTolGrpName varchar2,
InsTime Date,
Note varchar2,mycs out mytype);
end;
CREATE OR REPLACE package BODY LMDC.pk_wtMachineToolGroups
is
procedure userm_MachineToolGroups
(
state varchar2,
MaTolGrpID rowid,
DeptID rowID,
MaTolGrpName varchar2,
InsTime Date,
Note varchar2,
mycs out mytype
)
as
v_num number;
begin
if state='SELECT' then
if DeptID IS NULL then
open mycs for SELECT m.*,d.DeptName FROM MachineToolGroups m,Departments d
WHERE m.DeptID=d.DeptID;
else
open mycs for SELECT m.*, d.DeptName FROM MachineToolGroups m,Departments d WHERE m.DeptID=d.DeptID ;
end if;
select 1 into v_num FROM MachineToolGroups WHERE MaTolGrpID=MaTolGrpID and rownum=1;
elsif v_num=1 then
if state='UPDATE' then
UPDATE MachineToolGroups m SET DeptID=DeptID, MaTolGrpName=MaTolGrpName, InsTime=InsTime, Note=Note , m.DeptID=DeptID WHERE MaTolGrpID=MaTolGrpID;
elsif state='DELETE' then
DELETE FROM MachineToolGroups
WHERE MaTolGrpName=MaTolGrpName and MaTolGrpID=MaTolGrpID;
end if;
else
if state='INSERT' then
INSERT INTO MachineToolGroups( MaTolGrpID,DeptID, MaTolGrpName, InsTime, Note)
VALUES( MaTolGrpID,DeptID, MaTolGrpName, InsTime, Note);
end if;
end if;
end userm_MachineToolGroups;
end pk_wtMachineToolGroups;
6-------------------------------
/*查询指定表的所有信息*/
CREATE OR REPLACE package LMDC.pk_wtUserLogin
is
type mytype is ref cursor;
procedure userm_LoginOn(UserAccount varchar2,
password varchar2,mycs out mytype);
end;
CREATE OR REPLACE package BODY LMDC.pk_wtUserLogin
is
procedure userm_LoginOn (
UserAccount varchar2,
password varchar2,
mycs out mytype
)
as
begin
open mycs for SELECT UserID FROM Users WHERE UserAccount=UserAccount AND Password=password;
end userm_LoginOn;
end pk_wtUserLogin;
7-------------------------------------/*查询指定表的所有信息*/
CREATE OR REPLACE procedure userm_InsSiemensExtend
(
RecordID rowID,
MaTolID rowID,
AlarmServerName varchar2,
StateServerName varchar2,
AlarmNode varchar2
)
as
begin
INSERT INTO SiemensExtend(RecordID, MaTolID, AlarmServerName, StateServerName, AlarmNode)
VALUES(RecordID,MaTolID,AlarmServerName,StateServerName,AlarmNode);
end;
8-------------------------------/*查询指定表的所有信息*/
CREATE OR REPLACE procedure userm_InsLITERMINALExtend
(
RecordID rowID,
LITERMINALIP varchar2,
MaTolID rowID,
LITERMINALPort number,
High varchar2,
Low varchar2,
Note varchar2,
InsTime date
)
as
begin
INSERT INTO LITERMINALInfoDefine(RecordID, LITERMINALIP, MaTolID, LITERMINALPort, High, Low, Note, InsTime)
VALUES(RecordID,LITERMINALIP,MaTolID,LITERMINALPort,High,Low,Note,InsTime);
end;
9-------------------------------/*查询指定表的所有信息*/
CREATE OR REPLACE procedure userm_InsLITERMINALDefine
(
RecordID rowID,
LITERMINALIP varchar2,
MaTolID rowID,
AccessPort number,
ConnTime number,
InsTime date
)
as
begin
INSERT INTO LITERMINALDefine(RecordID, LITERMINALIP, MaTolID, AccessPort, ConnTime, InsTime)
VALUES(RecordID,LITERMINALIP,MaTolID,AccessPort,ConnTime,InsTime);
end;
GO
10-----------------------/*根据表名插入指定内容*/
CREATE OR REPLACE procedure userm_IDInID
(
mytable varchar2,
ID rowID,
A_ID rowID,
B_ID varchar2,
InsTime Date,
Note varchar2
)
as
begin
if mytable='RolesInPrivileges' then
begin
INSERT INTO RolesInPrivileges(RPID,RoleID, PrivilegeName, InsTime, Note)
VALUES(ID,A_ID,B_ID ,InsTime,Note);
end;
elsif mytable='UsersInRoles' then
begin
INSERT INTO UsersInRoles(URID,UserID, RoleID, InsTime, Note)
VALUES(ID,A_ID,cast(B_ID as RowID),InsTime,Note);
end;
elsif mytable='UsersInDepartments' then
begin
INSERT INTO UsersInDepartments(UDeptID,UserID, DeptID, InsTime, Note)
VALUES(ID,A_ID,cast(B_ID as RowID),InsTime,Note);
end;
end if;
end;
11-----------------------------------
/*操作表Departments*/
/*-----创建包----*/
CREATE OR REPLACE package LMDC.pk_userm_GetLITERMINALInfo
is
type mytype is ref cursor;
procedure userm_GetLITERMINALInfo(mycs out mytype);
end;
/*------创建包体
CREATE OR REPLACE package BODY LMDC.pk_userm_GetLITERMINALInfo
is
procedure userm_GetLITERMINALInfo(mycs out mytype)
as
begin
open mycs for SELECT DISTINCT InfoTag FROM DetailToBaseState WHERE MaTolType='LITERMINAL' ;
end userm_GetLITERMINALInfo;
end pk_userm_GetLITERMINALInfo;
12---------------
CREATE OR REPLACE procedure userm_FactoryInfo
(
FaID varchar2,
FaName varchar2,
InsTime date,
Note varchar2
)
as
begin
UPDATE FactoryInfo
SET FaName=FaName, InsTime=InsTime, Note=Note
WHERE FaID=FaID;
end;
13------------------
-----创建包
CREATE OR REPLACE package LMDC.pk_userm_userm_Departments
is
type mytype is ref cursor;
procedure userm_Departments(state varchar2,
DeptID rowID,
FaID varchar2,
DeptName varchar2,
InsTime Date,
Note varchar2,mycs out mytype);
end;
/*操作表Departments*/
CREATE OR REPLACE package BODY LMDC.pk_userm_userm_Departments
is
procedure userm_Departments
(
state varchar2,
DeptID rowID,
FaID varchar2,
DeptName varchar2,
InsTime Date,
Note varchar2,mycs out mytype
)
as
v_num number:=0;
begin
select 1 into v_num FROM Departments WHERE DeptID=DeptID and rownum=1;
if state='SELECT' then
begin
open mycs for SELECT d.*,f.FaName FROM Departments d,FactoryInfo f ;
end ;
elsif v_num=1 then
begin
if state='UPDATE' then
begin
UPDATE Departments SET DeptName=DeptName, InsTime=InsTime, Note=Note WHERE DeptID=DeptID;
end;
elsif state='DELETE' then
begin
DELETE FROM Departments WHERE DeptName=DeptName;
end;
end if;
end;
else
begin
if state='INSERT' then
begin
INSERT INTO Departments(DeptID,FaID, DeptName, InsTime, Note) VALUES(DeptID,FaID,DeptName,InsTime,Note);
end;
end if;
end;
end if;
end userm_Departments;
end pk_userm_userm_Departments;
14--------------------
/*查询指定表的所有信息*/
CREATE OR REPLACE procedure userm_ClientInfo
state varchar2,
RecordID rowID,
UserID rowID,
LoginIP varchar2,
IsOnline number,
InsTime date,
ReceivePort number
as
begin
if state='INSERT' then
begin
INSERT INTO ClientInfo(RecordID, UserID, LoginIP, IsOnline, InsTime, ReceivePort)
VALUES(RecordID,UserID,LoginIP,IsOnline,InsTime,ReceivePort);
end;
elsif state='DELETE' then
begin
DELETE FROM ClientInfo
WHERE UserID=UserID AND LoginIP=LoginIP;
end;
end if;
end;
15------------------------------------------------------------------------
/*查询指定表的所有信息*/
CREATE or replace procedure userm_AllInfo
(
mtable varchar2
)
as
sqlstr varchar2(100);
begin
sqlstr :='SELECT *FROM mtable '||mtable ;
execute immediate sqlstr ;
end;
16------------------------------------------------------------------------在两个数据库中都没有找到tfanuc0i;表或视图
CREATE OR REPLACE PROCEDURE spAddFanuc0iMachine
(
rowCount out int
)
AS
begin
select count(*) into rowCount from tfanuc0i;
end;
--------------------------------------------------------------------下面可能都是一个人写的存储过程了
17------------------------------
create OR REPLACE procedure Profu_InsSiemensAlarmInfo
(
MatolName varchar2,
AlarmID varchar2,
StartTime date,
During number,
AlarmInfo varchar2,
ProgramName varchar2,
SubScriptionName varchar2,
EventSource varchar2,
EventCategory varchar2,
StartDate date,
EndDate date
)
as
v_num number:=0;
begin
select 1 into v_num from alarmRunInfo
where MatolName=MatolName and AlarmID=AlarmID and StartTime Between StartDate and EndDate and rownum=1;
if v_num=0 then
begin
insert into AlarmRunInfo(MatolName,AlarmID,StartTime,AlarmDuring,AlarmInfo,ProgramName,SubScriptionName,EventSource,EventCategory)
values(MatolName,AlarmID,StartTime,During,AlarmInfo,ProgramName,SubScriptionName,EventSource,EventCategory);
end;
end if;
end;
18-----------------------------------------------------------
create OR REPLACE procedure Profu_InsProgramInfo
(
MtName varchar2,
ProgramID varchar2,
ProcessName varchar2,
StartTime Date,
During number,
PartName varchar2,
ToolID varchar2,
ToolNo varchar2
)
as
begin
insert into ProgramRunInfo(MatolName,FileName,ProgramName,StartTime,During,PartName,ToolID,ToolNo)
values(MtName,ProgramID,ProcessName,StartTime,During,PartName,ToolID,ToolNo);
end;
19-------------------------------------------------------
CREATE OR REPLACE procedure Profu_InsOrUpdateSynServerInfo
(
IP varchar2,
Port number
)
as
v_num number:=0;
begin
select 1 into v_num from SynServerInfo where rownum=1;
if v_num=1 then
begin
update SynServerInfo set SynServerIP=IP,SynServerPort=Port;
end;
else
begin
insert into SynServerInfo (synServerName,SynServerIP,SynServerPort)
values('同步服务器',IP,Port);
end;
end if;
end;
20-------------------------------------------------------------
CREATE OR REPLACE procedure Profu_InsMtBaseStateInfo
(
MatolName varchar2,
BaseState varchar2,
StartTime date,
During number
)
as
begin
insert into MachineBaseRunState(MatolName,BaseState,StartTime,BaseStateDuring)
values(MatolName,BaseState,StartTime,During);
end;
21----------------------------------------------------------------
CREATE OR REPLACE procedure Profu_InsMtAlarmInfo
(
MatolName varchar2,
AlarmID varchar2,
StartTime date,
During number
)
as
begin
insert into AlarmRunInfo(MatolName,AlarmID,StartTime,AlarmDuring)
values(MatolName,AlarmID,StartTime,During);
end;
/*set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON*/
22--------------------------------------------------------------------
CREATE OR REPLACE procedure Profu_InsMoxaAlarmInfo
(
MatolName varchar,
AlarmID varchar,
StartTime date,
During number
)
as
begin
insert into AlarmRunInfo(MatolName,AlarmID,StartTime,AlarmDuring)
values(MatolName,AlarmID,StartTime,During);
end
/*
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON*/
;
23-----------------------------------------------------------------------
CREATE OR REPLACE procedure Profu_InsMonitorServerInfo
(
LoginName varchar2,
LoginIP VARCHAR2,
InsFlag int
)
as
v_num number:=0;
begin
if InsFlag=1 then
begin
select 1 into v_num from MonitorServerInfo where LoginIP=LoginIP and rownum=1;
if v_num=1 then
BEGIN
DELETE FROM MonitorServerInfo where LoginIP=LoginIP;
insert into MonitorServerInfo (LoginName,LoginIP) values(LoginName,LoginIP);
end;
else
begin
DELETE FROM MonitorServerInfo where LoginIP=LoginIP;
end;
end if;
end;
end if;
end
/*set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON*/
;
24---------------------------------------------------------------------------没有成功：数据库中没有找到表：MMTInfo
CREATE OR REPLACE Procedure Profu_InsMMTInfo
(
UserName varchar2,
MaTolName varchar2
)
as
begin
insert into MMTInfo(UserName,MaTolName)
values(UserName,MaTolName) ;
end
/*
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
*/;
25------------------------------------------------------------------------------
create OR REPLACE procedure Profu_InsMacroAlarmInfo
(
MatolName varchar2,
AlarmID varchar2,
AlarmInfo varchar2,
StartTime date,
During number
)
as
begin
insert into AlarmRunInfo(MatolName,AlarmID,AlarmInfo,StartTime,AlarmDuring)
values(MatolName,AlarmID,AlarmInfo,StartTime,During);
end;
26-------------------------------------------------------------------------
CREATE OR REPLACE Procedure Profu_InsCMInfo
(
UserName varchar2,
MaTolName varchar2
)
as
begin
insert into ClientMonitorInfo(UserName,MaTolName) values(UserName,MaTolName) ;
end;
27--------------------------------------------------------------------------
-----创建包
CREATE OR REPLACE package LMDC.pk_Profu_GetTatolTimeOfState
is
type mytype is ref cursor;
procedure Profu_GetTatolTimeOfState(MtName varchar2,
StartTime date,EndTime date,mycs out mytype);
end;
----- 创建包体
CREATE OR REPLACE package BODY LMDC.pk_Profu_GetTatolTimeOfState
is
procedure Profu_GetTatolTimeOfState(MtName varchar2,
StartTime date,EndTime date,mycs out mytype)
as
begin
open mycs for select BaseState,(sum(BaseStateDuring)) as Total
from machineBaseRunState
where MATOLNAME=MtName and startTime between StartTime and EndTime AND BaseState!='ALARM'
group by BaseState
union
(select 'ALARM' as BaseState,( sum(alarmduring)) as Total
From AlarmRunInfo
where MATOLNAME=MtName and startTime between StartTime and EndTime ) ;
end Profu_GetTatolTimeOfState;
end pk_Profu_GetTatolTimeOfState;
28----------------------------------------
-----创建包
CREATE OR REPLACE package LMDC.pk_Profu_GetSyncServerInfo
is
type mytype is ref cursor;
procedure Profu_GetSyncServerInfo(mycs out mytype);
end;
------创建包体
CREATE OR REPLACE package BODY LMDC.pk_Profu_GetSyncServerInfo
is
procedure Profu_GetSyncServerInfo(mycs out mytype)
as
begin
open mycs for select SynServerIP,SynServerPort from SynServerInfo where rownum=1;
end Profu_GetSyncServerInfo;
end pk_Profu_GetSyncServerInfo;
29----------------------------------------------
------创建包
CREATE OR REPLACE package LMDC.pk_Pf_GetSiemensInfoByMtName
is
type mytype is ref cursor;
procedure Profu_GetSiemensInfoByMtName(MtName varchar2,mycs out mytype);
end;
------创建包体
CREATE OR REPLACE package BODY LMDC.pk_Pf_GetSiemensInfoByMtName
is
procedure Profu_GetSiemensInfoByMtName
(
MtName varchar2,
mycs out mytype
)
as
begin
open mycs for SELECT a.AlarmServerName ,a.StateServerName ,a.AlarmNode, b.MaTolIP,b.MaTolName
FROM siemensExtend a,machineTools b
WHERE a.MaTolID=b.MaTolID and b.MatolName=MtName and rownum=1;
end Profu_GetSiemensInfoByMtName;
end pk_Pf_GetSiemensInfoByMtName;
30---------------------------------------------------------------
------创建包
CREATE OR REPLACE package LMDC.pk_Profu_GetSiemensInfoByIP
is
type mytype is ref cursor;
procedure Profu_GetSiemensInfoByIP(ColServerIP varchar2,mycs out mytype);
end;
----创建包体
CREATE OR REPLACE package BODY LMDC.pk_Profu_GetSiemensInfoByIP
is
procedure Profu_GetSiemensInfoByIP
(
ColServerIP varchar2,
mycs out mytype
)
as
begin
open mycs for SELECT a.MatolName,a.MatolIP,c.AlarmServerName,c.StateServerName,c.AlarmNode
From machineTools a,colserverinfo b,SiemensExtend c
where a.MatolType='SIEMENS' AND ColServerIP=ColServerIP AND a.colServerID=b.colServerID and c.MatolID=a.MaTolID;
end Profu_GetSiemensInfoByIP;
end pk_Profu_GetSiemensInfoByIP;
31---------------------------------------------------------------没有成功（没有找到表MMTInfo）
------创建包
CREATE OR REPLACE package LMDC.pk_Profu_GetSiemensInfo
is
type mytype is ref cursor;
procedure Profu_GetSiemensInfo(UserName varchar2,mycs out mytype);
end;
CREATE OR REPLACE package BODY LMDC.pk_Profu_GetSiemensInfo
is procedure Profu_GetSiemensInfo
(
UserName varchar2,
mycs out mytype
)
as
begin
open mycs for select distinct c.StateServerName,a.MatolName
from MachineTools a ,MMTInfo b,siemensExtend c
where a.MatolName =b.MatolName and b.username=UserName and a.MatolType ='SIEMENS' and a.MatolID=C.MatolID;
end Profu_GetSiemensInfo;
end pk_Profu_GetSiemensInfo;
32---------------------------------------------
CREATE OR REPLACE package LMDC.pk_Pf_GetSiemensAlarmInfo
is
type mytype is ref cursor;
procedure Profu_GetSiemensAlarmInfo(UserName varchar2,mycs out mytype);
end;
CREATE OR REPLACE package BODY LMDC.pk_Pf_GetSiemensAlarmInfo
is procedure Profu_GetSiemensAlarmInfo
(
UserName varchar2,
mycs out mytype
)
as
begin
open mycs for select distinct c.AlarmServerName,a.MatolName
from MachineTools a ,MMTInfo b,siemensExtend c
where a.MatolName =b.MatolName and b.username=UserName and a.MatolType ='SIEMENS' and a.MatolID=C.MatolID;
end Profu_GetSiemensAlarmInfo;
end pk_Pf_GetSiemensAlarmInfo;
33-------------------------------------------
CREATE OR REPLACE package LMDC.pk_Pf_GetMtInfoByUserName
is
type mytype is ref cursor;
procedure Profu_GetMtInfoByUserName(UserName varchar2,
mStart number,
mEnd number,
mycs out mytype);
end;
CREATE OR REPLACE package BODY LMDC.pk_Pf_GetMtInfoByUserName
is procedure Profu_GetMtInfoByUserName
(
UserName varchar2,
mStart number,
mEnd number,
mycs out mytype
)
as
begin
open mycs for select distinct a.MatolName,a.MatolType
from machineTools a, clientMonitorInfo b
where a.matolname=b.matolname and b.username=UserName;
end Profu_GetMtInfoByUserName;
end pk_Pf_GetMtInfoByUserName;
34----------------------------------------------------------------------------------
CREATE OR REPLACE package LMDC.pk_Pf_GetMoxaSignalDefine
is
type mytype is ref cursor;
procedure Profu_GetMoxaSignalDefine(LITERMINALIP varchar2,
mycs out mytype);
end;
CREATE OR REPLACE package BODY LMDC.pk_Pf_GetMoxaSignalDefine
is procedure Profu_GetMoxaSignalDefine
(
LITERMINALIP varchar2,
mycs out mytype
)
as
begin
open mycs for select distinct LITERMINALPort,high,low ,c.matolName
from LITERMINALInfoDefine a,LITERMINALDefine b,machineTools c
where a.LITERMINALIP=b.LITERMINALIP and a.matolID=c.matolID;
end Profu_GetMoxaSignalDefine;
end pk_Pf_GetMoxaSignalDefine;
35-------------------------------------------晚上
CREATE OR REPLACE package LMDC.pk_Profu_GetMoxaInfoByIP
is
type mytype is ref cursor;
procedure Profu_GetMoxaInfoByIP(ColServerIP varchar2,
mycs out mytype);
end;
CREATE OR REPLACE package BODY LMDC.pk_Profu_GetMoxaInfoByIP
is procedure Profu_GetMoxaInfoByIP
(
ColServerIP varchar2,
mycs out mytype
)
as
begin
open mycs for select distinct c.LITERMINALIP
from machineTools a, colserverinfo b,LITERMINALInfoDefine c
where a.colserverid=b.colserverid and a.MatolType='LITERMINAL' and b.colServerIP=ColServerIP
AND a.matolID=c.matolID;
END Profu_GetMoxaInfoByIP;
end pk_Profu_GetMoxaInfoByIP;
36------------------------------------------
CREATE OR REPLACE package LMDC.pk_Profu_GetMoxaInfo
is
type mytype is ref cursor;
procedure Profu_GetMoxaInfo(UserName varchar2,
mycs out mytype);
end;
CREATE OR REPLACE package BODY LMDC.pk_Profu_GetMoxaInfo
is procedure Profu_GetMoxaInfo
(
UserName varchar2,
mycs out mytype
)
as
begin
open mycs for select distinct a.MatolIP ,a.MatolName
from MachineTools a ,MMTInfo b
where a.MatolName =b.MatolName and b.username=UserName and a.MatolType ='LITERMINAL';
end Profu_GetMoxaInfo;
end pk_Profu_GetMoxaInfo;
37---------------------------------
CREATE OR REPLACE package LMDC.pk_Pf_GetMoxaClientInfo
is
type mytype is ref cursor;
procedure Profu_GetMoxaClientInfo(LITERMINALIP varchar2,
mycs out mytype);
end;
CREATE OR REPLACE package BODY LMDC.pk_Pf_GetMoxaClientInfo
is procedure Profu_GetMoxaClientInfo
(
LITERMINALIP varchar2,
mycs out mytype
)
as
begin
open mycs for select distinct a.username, b.loginip,b.receiveport
from USERS a, clientInfo b,clientMonitorInfo c
where a.UserID=b.UserID and a.UserAccount=c.username AND b.IsOnline=1
and c.matolname in
(
select b.matolname
from machinetools b,
( SELECT DISTINCT A.LITERMINALIP,A.MATOLID
FROM LITERMINALInfoDefine A
WHERE LITERMINALIP=LITERMINALIP) c
where b.matolid=c.matolid
);
end Profu_GetMoxaClientInfo;
end pk_Pf_GetMoxaClientInfo;
----------------------------------------
38----------------------------------------------------
CREATE OR REPLACE package LMDC.pk_Pf_GetMonitorServerInfo
is
type mytype is ref cursor;
procedure Profu_GetMonitorServerInfo(mycs out mytype);
end;
CREATE OR REPLACE package BODY LMDC.pk_Pf_GetMonitorServerInfo
is procedure Profu_GetMonitorServerInfo(mycs out mytype)
as
begin
open mycs for select DISTINCT LoginName,LoginIP FROM MonitorServerInfo;
end;
end;
39-------------------------------------------------------------
CREATE OR REPLACE package LMDC.pk_Pf_GetMacHeidhInfoByIP
is
type mytype is ref cursor;
procedure Profu_GetMacHeidhInfoByIP(ColServerIP varchar2,MatolType varchar2,mycs out mytype);
end;
CREATE OR REPLACE package BODY LMDC.pk_Pf_GetMacHeidhInfoByIP
is procedure Profu_GetMacHeidhInfoByIP
(
ColServerIP varchar2,
MatolType varchar2,
mycs out mytype
)
as
begin
open mycs for SELECT a.MatolName,a.MatolIP,a.MatolPort,ColServerIP
From machineTools a,colserverinfo b
where a.MatolType=MatolType AND ColServerIP=ColServerIP AND a.colServerID=b.colServerID ;
END;
end;
40----------------------------------------------------------------------------------
CREATE OR REPLACE package LMDC.pk_Pf_GetLitSignalDefine
is
type mytype is ref cursor;
procedure Profu_GetLitSignalDefine(LitName varchar2,mycs out mytype);
end;
CREATE OR REPLACE package BODY LMDC.pk_Pf_GetLitSignalDefine
is procedure Profu_GetLitSignalDefine
(
LitName varchar2,
mycs out mytype
)
as
begin
open mycs for select distinct LITERMINALPort,high,low ,c.matolName
from LITERMINALInfoDefine a,LITERMINALDefine b,machineTools c
where a.LITERMINALIP=b.LITERMINALIP and c.matolName=LitName and a.matolID=c.matolID;
end;
end;
41-----------------------------------------------------------------------------------------
CREATE OR REPLACE package LMDC.pk_Pf_GetFanucStaticInfo
is
type mytype is ref cursor;
procedure Profu_GetFanucStaticInfo(MtName varchar2,
StartTime date,
EndTime date,mycs out mytype);
end;
CREATE OR REPLACE package BODY LMDC.pk_Pf_GetFanucStaticInfo
is procedure Profu_GetFanucStaticInfo
(
MtName varchar2,
StartTime date,
EndTime date,
mycs out mytype
)
as
begin
open mycs for select count(matolname) as amount, max(during) as maxTime,min(during) as minTime,avg(during) as avgTime
from Programruninfo
where MatolName=MtName and startTime between StartTime and EndTime ;
end;
end;
42-------------------------------------------------------------------------------------------------------
CREATE OR REPLACE package LMDC.pk_Pf_GetFanucInfoByName
is
type mytype is ref cursor;
procedure Profu_GetFanucInfoByName(FanucName varchar2,
mycs out mytype);
end;
CREATE OR REPLACE package BODY LMDC.pk_Pf_GetFanucInfoByName
is procedure Profu_GetFanucInfoByName
(
FanucName varchar2,
mycs out mytype
)
as
begin
open mycs for select distinct MatolName,MatolIP,MatolPort
from machineTools
where MatolType='FANUC' AND MatolName=FanucName;
end;
end;
43--------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE package LMDC.pk_Pf_GetFanucInfoByIP
is
type mytype is ref cursor;
procedure Profu_GetFanucInfoByIP(ColServerIP varchar2,
mycs out mytype);
end;
CREATE OR REPLACE package BODY LMDC.pk_Pf_GetFanucInfoByIP
is procedure Profu_GetFanucInfoByIP
(
ColServerIP varchar2,
mycs out mytype
)
as
begin
open mycs for SELECT a.MatolName,a.MatolIP,a.MatolPort
From machineTools a,colserverinfo b
where a.MatolType='FANUC' AND ColServerIP=ColServerIP AND a.colServerID=b.colServerID ;
END;
end;
44--------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE package LMDC.pk_Pf_GetColServInfoByIP
is
type mytype is ref cursor;
procedure Profu_GetColServInfoByIP(ColServerIP varchar2,
mycs out mytype);
end;
CREATE OR REPLACE package BODY LMDC.pk_Pf_GetColServInfoByIP
is procedure Profu_GetColServInfoByIP
(
ColServerIP varchar2,
mycs out mytype
)
as
begin
open mycs for SELECT ColServerID,ColServerName,ColServerIP,ColServerPort,HeidenhainPath,MacroPath
From ColServerInfo
where ColServerIP=ColServerIP and rownum=1;
END;
end;
45-------------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE package LMDC.pk_Pf_GetCtInfoByFanucName
is
type mytype is ref cursor;
procedure Profu_GetClientInfoByFanucName(MtName varchar2,
mycs out mytype);
end;
CREATE OR REPLACE package BODY LMDC.pk_Pf_GetCtInfoByFanucName
is procedure Profu_GetClientInfoByFanucName
(
MtName varchar2,
mycs out mytype
)
as
begin
open mycs for SELECT A.UserName as UserAccount , c.LoginIP,c.ReceivePort
From ( SELECT DISTINCT UserName FROM clientMonitorInfo WHERE MATOLNAME=MtName ) a,USERS b,ClientInfo c
WHERE a.UserName=b.UserAccount and c.UserID=b.UserID and c.IsOnline=1;
end;
end;
46--------------------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE package LMDC.pk_Profu_GetClientInfo
is
type mytype is ref cursor;
procedure Profu_GetClientInfo(MatolName varchar2,
mycs out mytype);
end;
CREATE OR REPLACE package BODY LMDC.pk_Profu_GetClientInfo
is procedure Profu_GetClientInfo
(
MatolName varchar2,
mycs out mytype
)
as
begin
open mycs for select distinct a.UserAccount as username, b.loginip,b.receiveport
from USERS a, clientInfo b,clientMonitorInfo c
where a.UserID=b.UserID and a.UserAccount=c.username and c.matolname=MatolName AND b.IsOnline=1;
end;
end;
47--------------------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE package LMDC.pk_Pf_GetCIBySiemensMtName
is
type mytype is ref cursor;
procedure Profu_GetCIBySiemensMtName(MtName varchar2,
mycs out mytype);
end;
CREATE OR REPLACE package BODY LMDC.pk_Pf_GetCIBySiemensMtName
is procedure Profu_GetCIBySiemensMtName
(
MtName varchar2,
mycs out mytype
)
as
begin
open mycs for SELECT A.UserName, c.LoginIP,c.ReceivePort
From ( SELECT DISTINCT UserName FROM clientMonitorInfo WHERE MATOLNAME=MtName ) a,USERS b,ClientInfo c
WHERE a.UserName=b.UserAccount and c.UserID=b.UserID and c.IsOnline=1;
end;
end;
48-------------------------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE package LMDC.pk_Profu_GetBaseState
is
type mytype is ref cursor;
procedure Profu_GetBaseState(MatolType varchar2,
mycs out mytype);
end;
CREATE OR REPLACE package BODY LMDC.pk_Profu_GetBaseState
is procedure Profu_GetBaseState
(
MatolType varchar2,
mycs out mytype
)
as
begin
SELECT InfoTag,BaseState
FROM detailToBaseState
WHERE MATOLTYPE=MatolType;
end;
end;
49-----------------------------------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE Paging_SubQuery (
(
Tables varchar2,
PK varchar2,
mSort varchar2,
PageNumber number:= 1,
PageSize number:= 10,
Fields varchar2 := '*',
Filter varchar2,
mGroup varchar2
)
AS
strPageSize varchar2(50);
strSkippedRows varchar2(50);
strFilter varchar2(1000);
strSimpleFilter varchar2(1000);
strGroup varchar2(1000);
begin
/*Default Sorting*/
IF mSort IS NULL OR mSort = '' then
mSort := PK;
end if;
mSort := ' ORDER BY ' + mSort + ' ';
/*Default Page Number*/
IF PageNumber < 1 then
PageNumber := 1;
end if;
/*Set paging variables.*/
strPageSize := cast(PageSize as varchar2);
strSkippedRows := cast(PageSize * (PageNumber - 1) as varchar2);
/*Set filter 3 variables.*/
IF Filter IS NOT NULL AND Filter <> '' then
BEGIN
strFilter := ' WHERE ' + Filter + ' ';
strSimpleFilter := ' AND ' + Filter + ' ';
END;
ELSE
BEGIN
strSimpleFilter := '';
strFilter := '';
END;
end if;
IF mGroup IS NOT NULL AND mGroup <> '' then
strGroup := ' GROUP BY ' + mGroup + ' ';
ELSE
strGroup := '';
end if;
IF PageNumber = 1 then
EXEC (
'SELECT TOP ' + strPageSize + ' ' + Fields + ' FROM ' + Tables +
strFilter + strGroup + mSort
) ;
ELSE
EXEC (
'SELECT ' + Fields + ' FROM ' + Tables + ' WHERE ' + PK + ' IN ' + '
(SELECT TOP ' + strPageSize + ' ' + PK + ' FROM ' + Tables +
' WHERE ' + PK + ' NOT IN
(SELECT TOP ' + strSkippedRows + ' ' + PK + ' FROM ' + Tables +
strFilter + strGroup + mSort + ') ' +
strSimpleFilter +
strGroup +
mSort + ') ' +
strSimpleFilter +
strGroup +
mSort
);
end if;
end;
