如何在Oracle中进行此动态更新? [英] How can I do this dynamical update in Oracle?

问题描述

我的模型表定义如下:

MODEL (id NUMBER, capacity NUMBER ) 

和航班表的定义如下:

FLIGHT (flight_number NUMBER, available_seats NUMBER)

我想每次在FLIGHT表中插入新行时，使用MODEL中定义的容量编号来初始化FLIGHT中的available_seats字段.我也想在available_seats倒数到零时通知用户.我怎样才能做到这一点?

I want to initialize the available_seats field in FLIGHT with the capacity number defined in MODEL each time a new row is inserted in FLIGHT table. Also I want to notify the user when the available_seats counts down to zero. How can I do this?

推荐答案

我将编写一个称为REGISTER FLIGHT的存储过程，该存储过程将接受MODEL表的PK(以及任何其他需要的参数)作为参数.在MODEL表上查找，然后将初始行插入飞行表.

I would write a stored procedure called REGISTER FLIGHT, which would accept as parameters the PK of the MODEL table (and any other parameters needed. The have the procedure do the lookup on the MODEL table, and insert the initial rows into the flight table.

然后我将执行第二个名为BOOK_SEAT的过程，该过程将在预订航班的过程中减少航班中的可用座位数.如果预订将available_seats减少为零(或更少)，则会抛出异常(例如OVERBOOKED_EXCEPTION)，并期望调用方捕获并处理该问题.

I would then have a second procedure called BOOK_SEAT, which would in the process of booking the flight, decrease the number of available seats in the flight. If the booking decreases the available_seats to zero (or fewer), it would throw an exception (e.g. OVERBOOKED_EXCEPTION) and expect the caller to catch and handle the problem.

这篇关于如何在Oracle中进行此动态更新?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！