中软首页
●  教育部授予:“软件工程专业大学生实习实训基地”资质
●  商务部授予:“中国服务外包示范培训中心”资质
●  工信部授予:国家信息技术紧缺人才培养工程“521计划”资格
●  获得腾讯授予:中国IT教育十大影响力品牌

≡ 技术知识库
当前位置:网站首页>技术知识库>>文章内容
每个程序员都需要了解的一个SQL技巧
作者:   来源:    时间:2016-06-03 17:28:42    字体:[大] [中] [小]

       对于数据过滤而言CHECK约束已经算是相当不错了。然而它仍存在一些缺陷,比如说它们是应用到表上面的,但有的时候你可能希望指定一条约束,而它只在特定条件下才生效。使用SQL标准的WITH CHECK OPTION子句就能完成这点,至少Oracle和SQL Server都实现了这个功能。下面是实现方式:

      CREATE TABLE books (

          id      NUMBER(10)              NOT NULL,

          title     VARCHAR2(100 CHAR)     NOT NULL,

          price    NUMBER(10, 2)            NOT NULL,

 

        CONSTRAINT pk_book PRIMARY KEY (id)

 );

/

CREATE VIEW expensive_books

AS

SELECT id, title, price

FROM books

WHERE price > 100 WITH CHECK OPTION;

 /

INSERT INTO books

VALUES (1, '1984', 35.90);

 

INSERT INTO books

VALUES(

     2,

    'The Answer to Life, the Universe, and Everything',

     999.90

);

      正如你看到的那样,expensive_books 是那些价格大于100块的书。这个视图只会返回第二本书:

      SELECT * FROM expensive_books;

      上述查询的输出是:

       ID   TITLE                                    PRICE

--   --------------------------------------------   -------

       2    The Answer to Life, the Universe, and ...    999.9

      不过由于我们使用了CHECK OPTION,我们还能防止用户往”昂贵的书籍”中插入那些廉价的。比如说,我们运行下这个查询:

       INSERT INTO expensive_books VALUES (

       3,

       '10 Reasons why jOOQ is Awesome',

       9.99

);

        它是无法生效的。你会看到:

        ORA-01402: view WITH CHECK OPTION where-clause violation

        我们也无法将贵的书更新成便宜的:

        UPDATE expensive_books

        SET price = 9.99;

        这个查询也会报出同样的ORA-01402错误。

        WITH CHECK OPTION内联

        如果你需要局部防止脏数据被插入到表中,你可以使用WITH CHECK OPTION的内联子句:

        INSERT INTO (

            SELECT *

            FROM expensive_books

            WHERE price > 1000

            WITH CHECK OPTION

        ) really_expensive_books

        VALUES (3, 'Modern Enterprise Software', 999.99);

        上述查询同样也会导到ORA-01402错误。

        使用SQL转换来生成特殊约束。

        CHECK OPTION对于已存储的视图非常有用,它使得那些无权直接访问底层表的用户能够获得正确的授权,而内联的CHECK OPTION主要是在应用的SQL中间转换层来进行动态SQL的转换。

        这个可以通过jOOQ的SQL转换功能来完成,比如说,你可以在SQL语句中对某个表进行约束,从根本上阻止了非法DML的执行。如果你的数据库没有本地提供行级别的安全性的话,这也是一个实现多租户的不错的方式。



中软国际教育科技微网页二维码
版权所有 © 2008-2014 北京中软国际教育科技股份有限公司 京ICP备09078625号-2 京公安备11010802014778号 隐私声明 | 联系我们 | 网站地图

华东

华南

华中

华北

西北

西南

东北