如何从MySql转换为PlSql [英] How to convert from mySql to PlSql

问题描述

大家好，我对此很陌生.

我一直在尝试配置一个朋友存储过程以适合我的环境.但是问题是他正在使用mySql(因为他正在使用Sql数据库)，而我需要在Oracle环境中使用Pl/Sql来使用此功能.

有人可以将此函数转换为Pl-Sql函数，或者至少可以帮助我了解代码中正在执行的操作.

预先感谢您的帮助

Hi Everyone, I am very new to this.

I have been trying to configure a friends stored procedure to work for my environment. The problem however is that he is using mySql ( because he is working with a Sql database) and I need to use Pl/Sql to use this function in an oracle environment.

Could someone please convert this function into a Pl-Sql function, or atleast help me to understand what is being done in the code.

Thanks in advance for the help

USE [Box]
GO
/****** Object:  StoredProcedure [idf].[getInfo]    Script Date: 11/03/2011 15:38:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        <Author,,Name>
-- ALTER  date: <Create Date,,>
-- Description:   <Description,,>
-- =============================================
--  exec [idf].[getInfo] '2011-11-01 00:20', '2011-11-01 13:22'

ALTER PROCEDURE [idf].[getInfo] 
      
      @FromDate datetime,
      @ToDate datetime
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

DECLARE @SQL1 nvarchar(1000)
DECLARE @SQL2 nvarchar(1000)
DECLARE @Val nvarchar(1000)
DECLARE @DateTimeChar nvarchar(1000)
DECLARE @params nvarchar(1000)
DECLARE @Value real  
DECLARE @DateTime datetime 

SET @SQL2 = 'SELECT @Val = IP_TREND_TIME FROM OPENQUERY(IP21_BFC, ''SELECT IP_TREND_TIME, IP_TREND_VALUE FROM "PLC_EAST.PV" WHERE IP_TREND_TIME >= ''''' 
+ box.idf.DateTimeToIP21Date(@FromDate) + ''''' AND IP_TREND_TIME <= ''''' + box.idf.DateTimeToIP21Date(@ToDate) 
+ ''''' AND IP_TREND_VALUE > -0.5 ORDER BY IP_TREND_TIME DESC '')'

SET @params = '@Val nvarchar(1000) OUTPUT'  
EXEC sp_executesql @SQL2, @params, @Val = @DateTimeChar OUTPUT

SET @SQL1 = 'SELECT @Val = MAX(IP_TREND_VALUE) FROM OPENQUERY(IP21_BFC, ''SELECT IP_TREND_TIME, IP_TREND_VALUE FROM "PLC_EAST.PV" WHERE IP_TREND_TIME >= ''''' 
+ Box.idf.DateTimeToIP21Date(@FromDate) + ''''' AND IP_TREND_TIME <= ''''' + @DateTimeChar 
+ ''''' AND IP_TREND_VALUE > 0.3 ORDER BY IP_TREND_TIME DESC '')'

EXEC sp_executesql @SQL1, @params, @Val = @Value OUTPUT

SET @Value = CONVERT(real, @Value)
SET @DateTime = CONVERT(datetime, @DateTimeChar, 21)

IF @Value is null
begin
  SET @Value = 8.0
end
IF @DateTimeChar is null
begin
  SET @DateTime = @ToDate
end

SELECT @DateTime, @Value

END

推荐答案

这篇关于如何从MySql转换为PlSql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！