som@''
2017-08-25 07:16
采纳率: 80%
浏览 904

Sql Server语句改为为Orcale语句

  CREATE TABLE [dbo].[Table_1](
    [ID] [nvarchar](50) NULL,
    [Material] [nvarchar](50) NULL,
    [Projects] [nvarchar](50) NULL,
    [Calculate] [nvarchar](50) NULL,
    [Datass] [nvarchar](50) NULL,
    [Formula] [nvarchar](50) NULL,
    [Tolerance] [nvarchar](50) NULL,
    [Standard] [nvarchar](50) NULL
) ON [PRIMARY]

GO
INSERT INTO [Table_1] VALUES (1, '硫酸亚铁_含量', '质量', '否', 10, '', '', '');
INSERT INTO [Table_1] VALUES (2, '硫酸亚铁_含量', '体积', '否', 20, '', '', '');
INSERT INTO [Table_1] VALUES (3, '硫酸亚铁_含量', '标液', '否', 30, '', '', '');
INSERT INTO [Table_1] VALUES (4, '硫酸亚铁_含量', '含量', '是', '', ' 体积*标液*0.27801/质量', '≤0.4%', '');
INSERT INTO [Table_1] VALUES (5, '硫酸亚铁_二氧化钛', '质量1', '否', 0.25, '', '', '');
INSERT INTO [Table_1] VALUES (6, '硫酸亚铁_二氧化钛', '质量2', '否', 0.35, '', '', '');
INSERT INTO [Table_1] VALUES (7, '硫酸亚铁_二氧化钛', 'm', '否', 0.45, '', '', '');
INSERT INTO [Table_1] VALUES (8, '硫酸亚铁_二氧化钛', '含量', '是', '', '5*m/质量1', '≤0.02%', '');
INSERT INTO [Table_1] VALUES (9, '硫酸亚铁_水不溶物', '质量1', '否', 0.11, '', '', '');
INSERT INTO [Table_1] VALUES (10, '硫酸亚铁_水不溶物', 'm1', '否', 0.66, '', '', '');
INSERT INTO [Table_1] VALUES (11, '硫酸亚铁_水不溶物', 'm2', '否', 0.88, '', '', '');
INSERT INTO [Table_1] VALUES (12, '硫酸亚铁_水不溶物', '含量', '是', '', '(m2-m1)/质量1', '≤0.04%', '');
INSERT INTO [Table_1] VALUES (13, '烧碱', '质量', '否', 0.99, '', '', '');
INSERT INTO [Table_1] VALUES (14, '烧碱', '体积', '否', 0.44, '', '', '');
INSERT INTO [Table_1] VALUES (15, '烧碱', '标液', '否', 11.5, '', '', '');
INSERT INTO [Table_1] VALUES (16, '烧碱', '含量', '是', '', '体积*标液*0.8/质量', '≤0.1%', '≥30.0%');
INSERT INTO [Table_1] VALUES (17, '双氧水_含量', '质量', '否', 1.56, '', '', '');
INSERT INTO [Table_1] VALUES (18, '双氧水_含量', '体积', '否', 1.913, '', '', '');
INSERT INTO [Table_1] VALUES (19, '双氧水_含量', '标液', '否', 1.55, '', '', '');
INSERT INTO [Table_1] VALUES (20, null, null, null, null, null, null, null);

DECLARE @Material VARCHAR(20),@Projects VARCHAR(20),@Datass NUMERIC(6,4),@Formula VARCHAR(50),@Sql VARCHAR(50)
DECLARE my_cur1 CURSOR FOR --读取Material的公式
SELECT Material FROM Table_1 WHERE Calculate='是'
OPEN my_cur1

FETCH NEXT FROM my_cur1 INTO @Material
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @Formula = Formula FROM Table_1 WHERE Material=@Material

DECLARE my_cur2 CURSOR FOR --读取Project
SELECT Projects FROM Table_1 WHERE Material=@Material AND Calculate<>'是'
OPEN my_cur2
FETCH NEXT FROM my_cur2 INTO @Projects
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @Datass=Datass FROM Table_1 WHERE Material=@Material AND Projects=@Projects
SET @Formula=REPLACE(@Formula,@Projects,@Datass) --replace替换公式
FETCH NEXT FROM my_cur2 INTO @Projects
END
PRINT @Material 
PRINT @Formula 

SET @Sql='select '+@Formula
EXEC (@Sql)
PRINT @Datass

CLOSE my_cur2
DEALLOCATE my_cur2

FETCH NEXT FROM my_cur1 INTO @Material
END
CLOSE my_cur1
DEALLOCATE my_cur1

并且把结果添加到临时表 显示出所有项,

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

5条回答 默认 最新

  • 听雨停了 2017-08-25 08:57
    已采纳

    我建议你有时间的话可以自己研究研究,反正可以上网,不懂得就百度下。不然网络用来干嘛的,不就用来学习的啊。什么都别人帮你弄好对你的成长没啥好处的

    打赏 评论
  • xianglanyue_net 2017-08-25 07:21

    他它俩语法大部分一样,可以先贴过去试试

    打赏 评论
  • cloudyzhao 2017-08-25 07:27

    写法跟oracle 差不多 不用go 变量名一般不用@开头 写出存储过程
    declare
    name varchar(50);
    begin
    end;

    打赏 评论
  • cloudyzhao 2017-08-25 08:40

    CREATE TABLE test_table(
    id number NULL,
    Material varchar(50) NULL,
    Projects varchar(50) NULL,
    Calculate varchar(50) NULL,
    Datass varchar(50) NULL,
    Formula varchar(50) NULL,
    Tolerance varchar(50) NULL,
    Standard1 varchar(50) NULL
    )

    INSERT INTO test_table VALUES (1, '硫酸亚铁_含量', '质量', '否', 10, '', '', '');
    INSERT INTO test_table VALUES (2, '硫酸亚铁_含量', '体积', '否', 20, '', '', '');
    INSERT INTO test_table VALUES (3, '硫酸亚铁_含量', '标液', '否', 30, '', '', '');
    INSERT INTO test_table VALUES (4, '硫酸亚铁_含量', '含量', '是', '', ' 体积*标液*0.27801/质量', '≤0.4%', '');
    INSERT INTO test_table VALUES (5, '硫酸亚铁_二氧化钛', '质量1', '否', 0.25, '', '', '');
    INSERT INTO test_table VALUES (6, '硫酸亚铁_二氧化钛', '质量2', '否', 0.35, '', '', '');
    INSERT INTO test_table VALUES (7, '硫酸亚铁_二氧化钛', 'm', '否', 0.45, '', '', '');
    INSERT INTO test_table VALUES (8, '硫酸亚铁_二氧化钛', '含量', '是', '', '5*m/质量1', '≤0.02%', '');
    INSERT INTO test_table VALUES (9, '硫酸亚铁_水不溶物', '质量1', '否', 0.11, '', '', '');

    DECLARE Material VARCHAR(20),@Projects VARCHAR(20),@Datass NUMERIC(6,4),@Formula VARCHAR(50),@Sql VARCHAR(50)
    my_cur1 CURSOR FOR --读取Material的公式
    SELECT Material FROM Table_1 WHERE Calculate='是'
    OPEN my_cur1
    begin
    FETCH NEXT FROM my_cur1 INTO @Material
    WHILE @@FETCH_STATUS=0
    BEGIN
    SELECT @Formula = Formula FROM Table_1 WHERE Material=@Material

    DECLARE my_cur2 CURSOR FOR --读取Project
    SELECT Projects FROM Table_1 WHERE Material=@Material AND Calculate<>'是'
    OPEN my_cur2
    FETCH NEXT FROM my_cur2 INTO @Projects
    WHILE @@FETCH_STATUS=0
    BEGIN
    SELECT @Datass=Datass FROM Table_1 WHERE Material=@Material AND Projects=@Projects
    SET @Formula=REPLACE(@Formula,@Projects,@Datass) --replace替换公式
    FETCH NEXT FROM my_cur2 INTO @Projects
    END
    PRINT @Material
    PRINT @Formula

    SET @Sql='select '+@Formula
    EXEC (@Sql)
    PRINT @Datass

    CLOSE my_cur2
    DEALLOCATE my_cur2

    FETCH NEXT FROM my_cur1 INTO @Material
    END
    CLOSE my_cur1
    DEALLOCATE my_cur1

    打赏 评论
  • 听雨停了 2017-08-25 08:55

    http://download.csdn.net/download/shaoyy/3694238
    你去这个网址下载这个东西看看行不行,我刚找到的,不过没试过。本来想装个Orcale帮你试试,但是这边没有安装包,懒得下载去了

    打赏 评论

相关推荐 更多相似问题