触发器
1.编写一个触发器实现如下功能：
对修改职工薪金的操作进行合法性检查：
a) 修改后的薪金要大于修改前的薪金
b) 工资增量不能超过原工资的10%
c) 目前没有单位的职工不能涨工资
create or replace trigger tr1
after update of sal on emp
for each row
begin
if :new.sal <= :old.sal then
raise_application_error(-20001,'修改后的薪金要大于修改前的薪金');
elsif :new.sal > :old.sal * 1.1 then
raise_application_error(-20002,'工资增量不能超过原工资的10%');
elsif :old.deptno is null then
raise_application_error(-20003,'没有单位的职工不能涨工资');
end if;
end;
2. 在emp表上编写一个触发器，实现如下功能：
当插入或删除的职工记录属于10号部门时，记录下操作时间,语句的种类(插入/删除),
和涉及的员工号
&nbs ......
如何使表MTL_RESERVATIONS和表oe_order_lines_all之间的联接关系?
DEMAND_SOURCE_LINE_ID NUMBER Line ID for demand source.
For Sales Order: OE_ORDER_LINES.LINE_ID
MTL_TRX_SOURCE_TYPES.Transaction_Source_type_id=MTL_REVERSATIONS.Demand_Source_type_id, OE_order_lines_all.Line_id=MTL_REVERSATIONS.Demand_Source_Line_id,）然后MTL_TRX_SOURCE_TYPES.Transaction_Source_type_Name='Sales Order'
---------------------------------------------------
WSH_DELIVERY_DETAILS.RELEASED_STATUS的意義
B Backordered Line failed to be allocated in Inventory
C Shipped Line has been shipped
D Cancelled Line is Cancelled
I Interfaced Line has been shipped and interfaced to Order Management and Inventory
N Not Ready to Release Line is not ready to be released
R Ready to Release Line is ready to be released
S Released to Warehouse Line has been released to Inventory for processing
X Not Applicable Line is not applicable for Pick Release
Y Staged/Pick Confirmed Line has been picked and stag ......
Entered
oe_order_headers_all 1 record created in header table
oe_order_lines_all Lines for particular records
oe_price_adjustments When discount gets applied
oe_order_price_attribs If line has price attributes then populated
oe_order_holds_all If any hold applied for order like credit check etc.
Booked
oe_order_headers_all Booked_flag=Y Order booked.
wsh_delivery_details Released_status Ready to release
Pick Released
wsh_delivery_details Released_status=Y Released to Warehouse (Line has been released to Inventory for processing)
wsh_picking_batches After batch is created for pick release.
mtl_reservations This is only soft reservations. No physical movement of stock
Full Transaction
mtl_material_transactions No records in mtl_material_transactions
mtl_txn_request_headers
mtl_txn_request_lines
wsh_delivery_details Released to warehouse.
wsh_new_deliveries if Auto-Create is Yes then data populated.
wsh_delivery_assignments deliveries get assigned
Pick Confirmed
w ......
拆分一列为多行的问题
表数据如下
id,name,code
1,abc,c001/c002/c007
2,efg,c001/c003
3,ttt,c008/c010
怎么能转换成
id,name,code
1,abc,c001
1,abc,c002
1,abc,c007
2,efg,c001
2,efg,c003
3,ttt,c008
3,ttt,c010
SQL> create table h_t(id number ,name varchar2(20),code varchar2(50));表已创建。
SQL> begin
2 insert into h_t values (1, 'abc', 'c001/c002/c007');
3 insert into h_t values (2, 'efg', 'c001/c003');
4 insert into h_t values (3, 'ttt', 'c008/c010');
5 commit;
6 end;
7 /
PL/SQL 过程已成功完成。
SQL> select * from h_t;
ID NAME CODE---------- -------------------- --------------------------------------------------
1 abc c001/c002/c007
2 efg c001/c003
3 ttt c008/c010
SQL> create table h_t_1 as select * from h_t where rownum <1;表已创建。
SQL> declare
2 l_i number;
3 l_p number;
4 l_times number;
5 l_str varchar2(10);
6 begin
7 for x in (select * from h_t) loop
8 l_i := 1;
9 l_times := length(x.code) - length(replace(x.code, '/', '')) + 1;
......
通过实例简要介绍case函数的用法。
1.创建测试表:
DROP SEQUENCE student_sequence;
CREATE SEQUENCE student_sequence START WITH 10000 INCREMENT BY 1;
DROP TABLE students;
CREATE TABLE students (
id NUMBER(5) PRIMARY KEY,
first_name VARCHAR2(20),
last_name VARCHAR2(20),
major VARCHAR2(30),
current_credits NUMBER(3),
grade varchar2(2));
INSERT INTO students (id, first_name, last_name, major, current_credits,grade)
VALUES (student_sequence.NEXTVAL, 'Scott', 'Smith', 'Computer Science', 98,null);
INSERT INTO students (id, first_name, last_name, major, current_credits,grade)
VALUES (student_sequence.NEXTVAL, 'Margaret', 'Mason', 'History', 88,null);
INSERT INTO students (id, first_name, last_name, major, current_credits,grade)
VALUES (student_sequence.NEXTVAL, 'Joanne', 'Junebug', 'Computer Science', 75,null);
INSERT INTO st ......
30个Oracle语句优化规则详解
1.选用适合的Oracle优化器
Oracle的优化器共有3种：
a.RULE(基于规则)
b.COST(基于成本)
c.CHOOSE(选择性)
设置缺省的优化器，可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明，如RULE、COST、CHOOSE、ALL_ROWS、FIRST_ROWS。你当然也在SQL句级或是会话(session)级对其进行覆盖。
为了使用基于成本的优化器(CBO，Cost-Based Optimizer)，你必须经常运行analyze命令，以增加数据库中的对象统计信息(object statistics)的准确性。
如果数据库的优化器模式设置为选择性(CHOOSE)，那么实际的优化器模式将和是否运行过analyze命令有关。如果table已经被analyze过，优化器模式将自动成为CBO，反之，数据库将采用RULE形式的优化器。
在缺省情况下，Oracle采用CHOOSE优化器，为了避免那些不必要的全表扫描(full table scan)，你必须尽量避免使用CHOOSE优化器，而直接采用基于规则或者基于成本的优化器。
2.访问Table的方式Oracle采用两种访问表中记录的方式：
a.全表扫描
全表扫描就是顺序地访问表中每条记录。Oracle采用一次读入多个数据块(database block)的方式优化全表扫描。
b. 通过ROWID访问表
你可以采用基于ROWID的访问方式� ......
最新文章 : Oracle