1.
select *
from mtl_interface_temp;
2.
SELECT *
FROM mtl_interface_temp mit
WHERE mit.wip_supply_type = 3;
3.
SELECT mit.wip_supply_type,
COUNT(*)
FROM mtl_interface_temp mit
GROUP BY mit.wip_supply_type;
4.
SELECT *
FROM mtl_interface_temp mit
WHERE mit.creation_date >SYSDATE;
5.
UPDATE mtl_interface_temp mit
SET mit.COST = decode(mit.shrinkage_rate,
1,
15,
2,
18,
mit.cost);
6.
CREATE TABLE cux_first_table
(Department_ID NUMBER NOT NULL,
Department_Name Varchar2(30) NOT NULL,
Description Varchar2(200),
Creation_date DATE NOT NULL);
7.
ALTER TABLE cux_first_table ADD Created_by NUMBER NOT NULL;
COMMENT ON COLUMN cux_first_table.Created_by IS '??????';
8.
DECLARE
CURSOR rec IS
SELECT mit.ROWID,
mit.*
FROM mtl_interface_temp mit;
l_processed_counter1 NUMBER := 0;
l_processed_counter2 NUMBER := 0;
l_processed_counter3 NUMBER := 0;
BEGIN
FOR c IN rec LOOP
IF c.wip_supply_type = 1 THEN
UPDATE mtl_interface_temp mit SET mit.wip_supply_subinventory = 'G001' WHERE ROWID = c.ROWID;
l_processed_counter1 := l_processed_counter1 + 1;
ELSIF c.wip_supply_type = 2 THEN
UPDATE mtl_interface_temp mit SET mit.wip_supply_subinventory = 'T002' WHERE ROWID = c.ROWID;
l_processed_counter2 := l_processed_counter2 + 1;
ELSIF c.wip_supply_type = 3 THEN
UPDATE mtl_interface_temp mit SET mit.wip_supply_subinventory = 'F003' WHERE ROWID = c.ROWID;
l_processed_counter3 := l_processed_counter3 + 1;
END IF;
END LOOP;
dbms_output.put_line('WIP_SUPPLY_TYPE: 1 ??????? ' || to_char(l_processed_counter1)||' ??');
dbms_output.put_line('WIP_SUPPLY_TYPE: 2 ??????? ' || to_char(l_processed_counter2)||' ??');
dbms_output.put_line('WIP_SUPPLY_TYPE: 3 ??????? ' || to_char(l_processed_counter3)||' ??');
END;
9.
DECLARE
CURSOR rec IS
SELECT mit.ROWID,
mit.*
FROM mtl_interface_temp mit;
l_processed_counter1 NUMBER := 0;
l_processed_counter2 NUMBER := 0;
l_processed_counter3 NUMBER := 0;
e_first_exception EXCEPTION;
BEGIN
FOR c IN rec LOOP
IF c.lead_time_lot_size = 21 THEN
RAISE e_first_exception;
END IF;
IF c.wip_supply_type = 1 THEN
UPDATE mtl_interface_temp mit SET mit.wip_supply_subinventory = 'G001' WHERE ROWID = c.ROWID;
l_processed_counter1 := l_processed_counter1 + 1;
ELSIF c.wip_supply_type = 2 THEN
UPDATE mtl_interface_temp mit SET mit.wip_supply_subinventory = 'T002' WHERE ROWID = c.ROWID;
l_processed_counter2 := l_processed_counter2 + 1;
ELSIF c.wip_supply_type = 3 THEN
UPDATE mtl_interface_temp mit SET mit.wip_supply_subinventory = 'F003' WHERE ROWID = c.ROWID;
l_processed_counter3 := l_processed_counter3 + 1;
END IF;
END LOOP;
dbms_output.put_line('WIP_SUPPLY_TYPE: 1 ??????? ' || to_char(l_processed_counter1)||' ??');
dbms_output.put_line('WIP_SUPPLY_TYPE: 2 ??????? ' || to_char(l_processed_counter2)||' ??');
dbms_output.put_line('WIP_SUPPLY_TYPE: 3 ??????? ' || to_char(l_processed_counter3)||' ??');
EXCEPTION
WHEN e_first_exception THEN
dbms_output.put_line('????? LEAD_TIME_LOT_SIZE ? 21 ????!');
WHEN OTHERS THEN
NULL;
END;
pl/sql 练习题.
3星 · 超过75%的资源 需积分: 10 173 浏览量
2009-09-07
14:33:29
上传
评论
收藏 49KB RAR 举报
老丁老丁老丁老丁老丁
- 粉丝: 8
- 资源: 46
最新资源
- 蓝桥杯2024年第十五届省赛真题-前缀总分
- com.qihoo.appstore_300101305-1.apk
- tensorflow-gpu-2.7.1-cp37-cp37m-manylinux2010-x86-64.whl
- tensorflow-2.7.2-cp37-cp37m-manylinux2010-x86-64.whl
- tensorflow-2.7.1-cp39-cp39-manylinux2010-x86-64.whl
- 蓝桥杯2024年第十五届省赛真题-传送阵
- com.qihoo.appstore_300101305.apk
- linux之线程同步一.doc
- keil5配色方案10种
- python烟花代码.doc
资源上传下载、课程学习等过程中有任何疑问或建议,欢迎提出宝贵意见哦~我们会及时处理!
点击此处反馈