oracle的视图如何使用,[SQL]视图(View)创建使用

最新推荐文章于 2024-04-01 17:28:34 发布

yypmzqdf

最新推荐文章于 2024-04-01 17:28:34 发布

阅读量488

点赞数

文章标签： oracle的视图如何使用

为了将复杂的sql 给非技术部门的人眼使用，可以通过创建视图的方法将复杂的sql简单化。

Oracle对象教程：视图(View)创建使用，视图(View)实际上是一张或者多张表上的预定义查询，这些表称为基表。从视图中查询信息与从表中查询信息的方法完全相同。只需要简单的SELECT…FROM即可。

视图具有以下优点：

1. 可以限制用户只能通过视图检索数据。这样就可以对最终用户屏蔽建表时底层的基表。

2. 可以将复杂的查询保存为视图。可以对最终用户屏蔽一定的复杂性。

3. 限制某个视图只能访问基表中的部分列或者部分行的特定数据。这样可以实现一定的安全性。

4. 从多张基表中按一定的业务逻辑抽出用户关心的部分，形成一张虚拟表。

语法结构：创建视图

CREATE [OR REPLACE] [{FORCE|NOFORCE}] VIEW view_name

SELECT查询

[WITH READ ONLY CONSTRAINT]

语法解析：

1. OR REPLACE：如果视图已经存在，则替换旧视图。

2. FORCE：即使基表不存在，也可以创建该视图，但是该视图不能正常使用，当基表创建成功后，视图才能正常使用。

3. NOFORCE：如果基表不存在，无法创建视图，该项是默认选项。

4. WITH READ ONLY：默认可以通过视图对基表执行增删改操作，但是有很多在基表上的限制(比如：基表中某列不能为空，但是该列没有出现在视图中，则不能通过视图执行insert操作)，WITH READ ONLY说明视图是只读视图，不能通过该视图进行增删改操作。现实开发中，基本上不通过视图对表中的数据进行增删改操作。

案例3：基于EMP表和DEPT表创建视图

原查询sql：

WITH liugang_01 AS

(select T.DATA_SOURCE,

T.WORK_ORDER_NO,

T.WINIT_ORDER_NO,

T.HOLDING_LOCATOR_SERNO,

T.PICKING_SUBAREA_NAME,

T.HOLDING_LOCATOR_NO,

T.PICKING_TASK_NO,

T.ITEM_SERNO,

T.MERCHANDISE_SERNO,

T.UPDATED,

ROW_NUMBER() OVER(PARTITION BY T.DATA_SOURCE, T.PICKING_TASK_NO ORDER BY T.UPDATED) RN

from XX_xx.XXXXXXXXXXXt

where T.IS_ACTIVE = 'Y'

AND T.IS_DELETE = 'N'

AND T.UPDATEDBY NOT LIKE 'winittest%'

AND T.UPDATED >= TRUNC(SYSDATE, 'MM'))

SELECT A.DATA_SOURCE,

A.PICKING_DATE,

A.PRODUCT_CLASS,

A.SI_COUNT,

A.ACT_CONSUM_HOUR,

FZ.拣选辅助工时,

R.SPLIT_RATIO 辅助工时分摊比例,

A.SI_COUNT / A.ACT_CONSUM_HOUR 效率_不含辅助,

A.SI_COUNT /

(A.ACT_CONSUM_HOUR + NVL(FZ.拣选辅助工时, 0) * NVL(R.SPLIT_RATIO, 0)) 效率_含辅助

FROM ( -- 标准任务拣选数量、耗时

SELECT DATA_SOURCE,

TRUNC(UPDATED, 'DD') PICKING_DATE,

PRODUCT_CLASS,

COUNT(ITEM_SERNO) SI_COUNT,

SUM(ACT_CONSUM_HOUR) ACT_CONSUM_HOUR

FROM ( -- 计算每个单品的拣选工时

SELECT J.DATA_SOURCE,

J.HOLDING_LOCATOR_SERNO,

J.PICKING_SUBAREA_NAME,

J.PICKING_TASK_NO,

J.ITEM_SERNO,

P.PRODUCT_CLASS,

J.UPDATED,

(J.UPDATED - J2.UPDATED) * 24 ACT_CONSUM_HOUR,

J.RN

FROM XXXX J

LEFT JOIN xxxxx.xxxxP

ON P.PRODUCT_CODE = J.MERCHANDISE_SERNO

LEFT JOIN xxxx J2

ON J2.DATA_SOURCE = J.DATA_SOURCE

AND J2.PICKING_TASK_NO = J.PICKING_TASK_NO

AND J2.RN = J.RN - 1

WHERE J.RN >= 2

AND J.PICKING_TASK_NO IS NOT NULL

and J.UPDATED - J2.UPDATED < 0.021 --剔除任务间隔超过半小时的数据

)

WHERE UPDATED < TRUNC(SYSDATE, 'DD')

-- AND ACT_CONSUM_HOUR>0

and DATA_SOURCE NOT IN ('CWM_USTX', 'CWM_UKBH', 'CWM_BEMO')

GROUP BY DATA_SOURCE, TRUNC(UPDATED, 'DD'), PRODUCT_CLASS) A

LEFT JOIN ( -- 待分摊的拣选辅助工时

SELECT TRUNC(C.FINISHED_DATE, 'DD') FINISH_DATE,

WH.WAREHOUSE_CODE_OW,

C.SU_CODE,

W.SU_NAME,

SUM(C.ACTUAL_WORKTIME) 拣选辅助工时

FROM xxxx.cccccc C

left join xxx .xxxxx WH

on WH.WAREHOUSE_VALUE = C.WAREHOUSE_CODE

LEFT JOIN BI_Dw.D_T_SU W

ON W.SU_CODE = C.SU_CODE

WHERE C.FINISHED_DATE >= TRUNC(SYSDATE, 'MM')

AND C.FINISHED_DATE < TRUNC(SYSDATE, 'DD')

AND W.SU_NAME = '订单拣选'

AND C.WORKTIME_TYPE = '辅助工时'

and C.ACTUAL_WORKTIME > 0

GROUP BY TRUNC(C.FINISHED_DATE, 'DD'),

WH.WAREHOUSE_CODE_OW,

C.WORKTIME_TYPE,

C.SU_CODE,

W.SU_NAME) FZ

ON FZ.WAREHOUSE_CODE_OW = A.DATA_SOURCE

AND FZ.FINISH_DATE = A.PICKING_DATE

LEFT JOIN ( -- 计算高位拣选量，以计算分摊比例

SELECT DATA_SOURCE,

PICKING_DATE,

SUBAREA_SIGN,

PRODUCT_CLASS,

SI_COUNT,

SUM(SI_COUNT) OVER(PARTITION BY DATA_SOURCE, PICKING_DATE) AS TOTAL_SICOUNT,

SI_COUNT / SUM(SI_COUNT) OVER(PARTITION BY DATA_SOURCE, PICKING_DATE) SPLIT_RATIO

FROM (SELECT T.DATA_SOURCE,

TRUNC(T.UPDATED, 'DD') PICKING_DATE,

L.SUBAREA_SIGN,

P.PRODUCT_CLASS,

COUNT(T.ITEM_SERNO) SI_COUNT

FROM xxxxxx T

LEFT xxxx.xxxxx WH

ON WH.WAREHOUSE_CODE_OW = T.DATA_SOURCE

LEFT JOIN xxx.qqqqq L

ON L.WAREHOUSE_CODE = WH.WAREHOUSE_VALUE

-- AND L.SUBAREA_NAME=T.PICKING_SUBAREA_NAME

AND L.LOCATOR_SERNO = T.HOLDING_LOCATOR_SERNO

AND L.IS_ACTIVE = 'Y'

AND L.IS_DELETE = 'N'

LEFT JOIN xxxx .xxxxxx P

ON P.PRODUCT_CODE = T.MERCHANDISE_SERNO

WHERE L.SUBAREA_SIGN = '存储库位'

AND T.ITEM_SERNO IS NOT NULL -- 异常拣选情况下单品条码为空

GROUP BY T.DATA_SOURCE,

TRUNC(T.UPDATED, 'DD'),

L.SUBAREA_SIGN,

P.PRODUCT_CLASS)) R

ON R.DATA_SOURCE = A.DATA_SOURCE

AND R.PICKING_DATE = A.PICKING_DATE

AND R.PRODUCT_CLASS = A.PRODUCT_CLASS

ORDER BY A.DATA_SOURCE, A.PICKING_DATE, A.PRODUCT_CLASS

此SQL 用了临时表，在创建视图的时候需要先把这个sql的结果集取到之后才能创建视图，那么就需要在这个sql外面在包一层 select * from ( 。。查询的SQL。。)；视图创建如下：

create view bi_wdddx.qssssuery_efficiency as select * from (

WITH ssss AS

(select T.DATA_SOURCE,

T.WORK_ORDER_NO,

T.WINIT_ORDER_NO,

T.HOLDING_LOCATOR_SERNO,

T.PICKING_SUBAREA_NAME,

T.HOLDING_LOCATOR_NO,

T.PICKING_TASK_NO,

T.ITEM_SERNO,

T.MERCHANDISE_SERNO,

T.UPDATED,

ROW_NUMBER() OVER(PARTITION BY T.DATA_SOURCE, T.PICKING_TASK_NO ORDER BY T.UPDATED) RN

from xxxx.sssssdf t

where T.IS_ACTIVE = 'Y'

AND T.IS_DELETE = 'N'

AND T.UPDATEDBY NOT LIKE 'winittest%'

AND T.UPDATED >= TRUNC(SYSDATE, 'MM'))

SELECT A.DATA_SOURCE,

A.PICKING_DATE,

A.PRODUCT_CLASS,

A.SI_COUNT,

A.ACT_CONSUM_HOUR,

FZ.拣选辅助工时,

R.SPLIT_RATIO 辅助工时分摊比例,

A.SI_COUNT / A.ACT_CONSUM_HOUR 效率_不含辅助,

A.SI_COUNT /

(A.ACT_CONSUM_HOUR + NVL(FZ.拣选辅助工时, 0) * NVL(R.SPLIT_RATIO, 0)) 效率_含辅助

FROM ( -- 标准任务拣选数量、耗时

SELECT DATA_SOURCE,

TRUNC(UPDATED, 'DD') PICKING_DATE,

PRODUCT_CLASS,

COUNT(ITEM_SERNO) SI_COUNT,

SUM(ACT_CONSUM_HOUR) ACT_CONSUM_HOUR

FROM ( -- 计算每个单品的拣选工时

SELECT J.DATA_SOURCE,

J.HOLDING_LOCATOR_SERNO,

J.PICKING_SUBAREA_NAME,

J.PICKING_TASK_NO,

J.ITEM_SERNO,

P.PRODUCT_CLASS,

J.UPDATED,

(J.UPDATED - J2.UPDATED) * 24 ACT_CONSUM_HOUR,

J.RN

FROM xxxxxx J

LEFT JOIN xxxxx.ssssss P

ON P.PRODUCT_CODE = J.MERCHANDISE_SERNO

LEFT JOIN xxxxx J2

ON J2.DATA_SOURCE = J.DATA_SOURCE

AND J2.PICKING_TASK_NO = J.PICKING_TASK_NO

AND J2.RN = J.RN - 1

WHERE J.RN >= 2

AND J.PICKING_TASK_NO IS NOT NULL

and J.UPDATED - J2.UPDATED < 0.021 --剔除任务间隔超过半小时的数据

)

WHERE UPDATED < TRUNC(SYSDATE, 'DD')

-- AND ACT_CONSUM_HOUR>0

and DATA_SOURCE NOT IN ('CWM_USTX', 'CWM_UKBH', 'CWM_BEMO')

GROUP BY DATA_SOURCE, TRUNC(UPDATED, 'DD'), PRODUCT_CLASS) A

LEFT JOIN ( -- 待分摊的拣选辅助工时

SELECT TRUNC(C.FINISHED_DATE, 'DD') FINISH_DATE,

WH.WAREHOUSE_CODE_OW,

C.SU_CODE,

W.SU_NAME,

SUM(C.ACTUAL_WORKTIME) 拣选辅助工时

FROM xxxx.sssss C

left join xxxxx WH

on WH.WAREHOUSE_VALUE = C.WAREHOUSE_CODE

LEFT JOIN sssss W

ON W.SU_CODE = C.SU_CODE

WHERE C.FINISHED_DATE >= TRUNC(SYSDATE, 'MM')

AND C.FINISHED_DATE < TRUNC(SYSDATE, 'DD')

AND W.SU_NAME = '订单拣选'

AND C.WORKTIME_TYPE = '辅助工时'

and C.ACTUAL_WORKTIME > 0

GROUP BY TRUNC(C.FINISHED_DATE, 'DD'),

WH.WAREHOUSE_CODE_OW,

C.WORKTIME_TYPE,

C.SU_CODE,

W.SU_NAME) FZ

ON FZ.WAREHOUSE_CODE_OW = A.DATA_SOURCE

AND FZ.FINISH_DATE = A.PICKING_DATE

LEFT JOIN ( -- 计算高位拣选量，以计算分摊比例

SELECT DATA_SOURCE,

PICKING_DATE,

SUBAREA_SIGN,

PRODUCT_CLASS,

SI_COUNT,

SUM(SI_COUNT) OVER(PARTITION BY DATA_SOURCE, PICKING_DATE) AS TOTAL_SICOUNT,

SI_COUNT / SUM(SI_COUNT) OVER(PARTITION BY DATA_SOURCE, PICKING_DATE) SPLIT_RATIO

FROM (SELECT T.DATA_SOURCE,

TRUNC(T.UPDATED, 'DD') PICKING_DATE,

L.SUBAREA_SIGN,

P.PRODUCT_CLASS,

COUNT(T.ITEM_SERNO) SI_COUNT

FROM cccccT

LEFT JOIN xcxcccx WH

ON WH.WAREHOUSE_CODE_OW = T.DATA_SOURCE

LEFT JOIN csacsacsa L

ON L.WAREHOUSE_CODE = WH.WAREHOUSE_VALUE

-- AND L.SUBAREA_NAME=T.PICKING_SUBAREA_NAME

AND L.LOCATOR_SERNO = T.HOLDING_LOCATOR_SERNO

AND L.IS_ACTIVE = 'Y'

AND L.IS_DELETE = 'N'

LEFT JOIN assadsadsa P

ON P.PRODUCT_CODE = T.MERCHANDISE_SERNO

WHERE L.SUBAREA_SIGN = '存储库位'

AND T.ITEM_SERNO IS NOT NULL -- 异常拣选情况下单品条码为空

GROUP BY T.DATA_SOURCE,

TRUNC(T.UPDATED, 'DD'),

L.SUBAREA_SIGN,

P.PRODUCT_CLASS)) R

ON R.DATA_SOURCE = A.DATA_SOURCE

AND R.PICKING_DATE = A.PICKING_DATE

AND R.PRODUCT_CLASS = A.PRODUCT_CLASS

ORDER BY A.DATA_SOURCE, A.PICKING_DATE, A.PRODUCT_CLASS)

可以设置条件的视图：

CREATE OR REPLACE VIEW QUERY_EFFICIENCY AS

select "DATA_SOURCE","PICKING_DATE","PRODUCT_CLASS","SI_COUNT","ACT_CONSUM_HOUR","拣选辅助工时","辅助工时分摊比例","效率_不含辅助","效率_含辅助" from (

WITH ssss AS

(select T.DATA_SOURCE,

T.WORK_ORDER_NO,

T.WINIT_ORDER_NO,

T.HOLDING_LOCATOR_SERNO,

T.PICKING_SUBAREA_NAME,

T.HOLDING_LOCATOR_NO,

T.PICKING_TASK_NO,

T.ITEM_SERNO,

T.MERCHANDISE_SERNO,

T.UPDATED,

ROW_NUMBER() OVER(PARTITION BY T.DATA_SOURCE, T.PICKING_TASK_NO ORDER BY T.UPDATED) RN

from fdsafdsgfds t

where T.IS_ACTIVE = 'Y'

AND T.IS_DELETE = 'N'

AND T.UPDATEDBY NOT LIKE 'winittest%'

AND T.UPDATED >= TRUNC(SYSDATE, 'MM'))

SELECT A.DATA_SOURCE,

A.PICKING_DATE,

A.PRODUCT_CLASS,

A.SI_COUNT,

A.ACT_CONSUM_HOUR,

FZ.拣选辅助工时,

R.SPLIT_RATIO 辅助工时分摊比例,

A.SI_COUNT / A.ACT_CONSUM_HOUR 效率_不含辅助,

A.SI_COUNT /

(A.ACT_CONSUM_HOUR + NVL(FZ.拣选辅助工时, 0) * NVL(R.SPLIT_RATIO, 0)) 效率_含辅助

FROM ( -- 标准任务拣选数量、耗时

SELECT DATA_SOURCE,

TRUNC(UPDATED, 'DD') PICKING_DATE,

PRODUCT_CLASS,

COUNT(ITEM_SERNO) SI_COUNT,

SUM(ACT_CONSUM_HOUR) ACT_CONSUM_HOUR

FROM ( -- 计算每个单品的拣选工时

SELECT J.DATA_SOURCE,

J.HOLDING_LOCATOR_SERNO,

J.PICKING_SUBAREA_NAME,

J.PICKING_TASK_NO,

J.ITEM_SERNO,

P.PRODUCT_CLASS,

J.UPDATED,

(J.UPDATED - J2.UPDATED) * 24 ACT_CONSUM_HOUR,

J.RN

FROM ssadsad J

LEFT JOIN fdsfdsfds P

ON P.PRODUCT_CODE = J.MERCHANDISE_SERNO

LEFT JOIN fddsfds J2

ON J2.DATA_SOURCE = J.DATA_SOURCE

AND J2.PICKING_TASK_NO = J.PICKING_TASK_NO

AND J2.RN = J.RN - 1

WHERE J.RN >= 2

AND J.PICKING_TASK_NO IS NOT NULL

and J.UPDATED - J2.UPDATED < 0.021 --剔除任务间隔超过半小时的数据

)

WHERE UPDATED < TRUNC(SYSDATE, 'DD')

-- AND ACT_CONSUM_HOUR>0

and DATA_SOURCE NOT IN ('CWM_USTX', 'CWM_UKBH', 'CWM_BEMO')

GROUP BY DATA_SOURCE, TRUNC(UPDATED, 'DD'), PRODUCT_CLASS) A

LEFT JOIN ( -- 待分摊的拣选辅助工时

SELECT TRUNC(C.FINISHED_DATE, 'DD') FINISH_DATE,

WH.WAREHOUSE_CODE_OW,

C.SU_CODE,

W.SU_NAME,

SUM(C.ACTUAL_WORKTIME) 拣选辅助工时

FROM safdsafdsfds C

left join sdfdsfdsfdsf WH

on WH.WAREHOUSE_VALUE = C.WAREHOUSE_CODE

LEFT JOIN BI_Dw.D_T_SU W

ON W.SU_CODE = C.SU_CODE

WHERE C.FINISHED_DATE >= TRUNC(SYSDATE, 'MM')

AND C.FINISHED_DATE < TRUNC(SYSDATE, 'DD')

AND W.SU_NAME = '订单拣选'

AND C.WORKTIME_TYPE = '辅助工时'

and C.ACTUAL_WORKTIME > 0

GROUP BY TRUNC(C.FINISHED_DATE, 'DD'),

WH.WAREHOUSE_CODE_OW,

C.WORKTIME_TYPE,

C.SU_CODE,

W.SU_NAME) FZ

ON FZ.WAREHOUSE_CODE_OW = A.DATA_SOURCE

AND FZ.FINISH_DATE = A.PICKING_DATE

LEFT JOIN ( -- 计算高位拣选量，以计算分摊比例

SELECT DATA_SOURCE,

PICKING_DATE,

SUBAREA_SIGN,

PRODUCT_CLASS,

SI_COUNT,

SUM(SI_COUNT) OVER(PARTITION BY DATA_SOURCE, PICKING_DATE) AS TOTAL_SICOUNT,

SI_COUNT / SUM(SI_COUNT) OVER(PARTITION BY DATA_SOURCE, PICKING_DATE) SPLIT_RATIO

FROM (SELECT T.DATA_SOURCE,

TRUNC(T.UPDATED, 'DD') PICKING_DATE,

L.SUBAREA_SIGN,

P.PRODUCT_CLASS,

COUNT(T.ITEM_SERNO) SI_COUNT

FROM dsafdfds T

LEFT JOIN dfsds.fds WH

ON WH.WAREHOUSE_CODE_OW = T.DATA_SOURCE

LEFT JOIN sdfdf .fdsfds L

ON L.WAREHOUSE_CODE = WH.WAREHOUSE_VALUE

-- AND L.SUBAREA_NAME=T.PICKING_SUBAREA_NAME

AND L.LOCATOR_SERNO = T.HOLDING_LOCATOR_SERNO

AND L.IS_ACTIVE = 'Y'

AND L.IS_DELETE = 'N'

LEFT JOIN fdsds.dfdsfds P

ON P.PRODUCT_CODE = T.MERCHANDISE_SERNO

WHERE L.SUBAREA_SIGN = '存储库位'

AND T.ITEM_SERNO IS NOT NULL -- 异常拣选情况下单品条码为空

GROUP BY T.DATA_SOURCE,

TRUNC(T.UPDATED, 'DD'),

L.SUBAREA_SIGN,

P.PRODUCT_CLASS)) R

ON R.DATA_SOURCE = A.DATA_SOURCE

AND R.PICKING_DATE = A.PICKING_DATE

AND R.PRODUCT_CLASS = A.PRODUCT_CLASS

ORDER BY A.DATA_SOURCE, A.PICKING_DATE, A.PRODUCT_CLASS);

查询实例：

SELECT 仓库名称,

工单号,

M码,

商品规格,

ABC分类,

移入库位,

移入分区,

移入区域,

可用库存,

移库时间,

移库单品数,

操作人

FROM (select T.*, ROWNUMRN

from WMS_TRANS_WORK_CHECK_USKY_V T

WHERE 移库时间 >= TRUNC(SYSDATE - 1, 'DD')

AND 移库时间 < TRUNC(SYSDATE, 'DD')

AND 仓库名称 IN ('USKY Warehouse', 'USKY2 Warehouse', 'USKY3 Warehouse')

AND 移入区域='拣选库位'

)

WHERE RN

<= 100;

yypmzqdf

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle的视图如何使用,[SQL]视图(View)创建使用

为了将复杂的sql 给非技术部门的人眼使用，可以通过创建视图的方法将复杂的sql简单化。Oracle对象教程：视图(View)创建使用，视图(View)实际上是一张或者多张表上的预定义查询，这些表称为基表。从视图中查询信息与从表中查询信息的方法完全相同。只需要简单的SELECT…FROM即可。视图具有以下优点：1. 可以限制用户只能通过视图检索数据。这样就可以对最终用户屏蔽建表时底层的基表。2. ...
复制链接

扫一扫