PL/SQL CURRENT_DATE if NULL

Question

如果在执行插入时p_start_time为NULL，我需要一些帮助来弄清楚如何使用CURRENT_DATE。下面的代码是我到目前为止已经理解的(除了insert语句(。我需要帮助的代码是最后一个代码块。

使用PL/SQL btw.

create or replace procedure BEGIN_TRIP_SP (
p_trip_id               OUT INTEGER,        -- an output parameter
p_bicycle_id            IN INTEGER,         -- Must not be NULL.  Must match value value in BC_BICYCLE and BC_DOCK tables.
p_start_time            IN DATE,            -- If NULL, use CURRENT_DATE system date value
p_membership_id         IN INTEGER          -- Must not be NULL.  Must match value in BC_MEMBERSHIP table.
)
IS
lv_membership_id_exist  INTEGER;
lv_bicycle_id_exist     INTEGER;
lv_Error_Message        VARCHAR2(200);
ex_Exception            EXCEPTION;
BEGIN
IF p_bicycle_id IS NULL THEN
lv_Error_Message := 'Missing mandatory value for bicycle id in BEGIN_TRIP_SP. No trip added.';
RAISE ex_Exception;
END IF;
IF p_start_time IS NULL THEN
NVL(trip_start_time, '08-03-2022') 
END IF;
INSERT INTO bc_trip (
Trip_id,
Bicycle_id,
Trip_start_time,
Membership_id
)
VALUES (
p_trip_id,
p_bicycle_id,
NVL(p_start_time, current_date),
p_membership_id
);

Answer 1

看看这是否有帮助；我认为trip_id是以某种方式自动生成的，所以您只需将其值返回给调用者。

样品表：

SQL> CREATE TABLE bc_trip
2  (
3     trip_id           NUMBER GENERATED ALWAYS AS IDENTITY,
4     bicycle_id        NUMBER,
5     trip_start_time   DATE,
6     membership_id     NUMBER
7  );
Table created.

程序：

SQL> CREATE OR REPLACE PROCEDURE begin_trip_sp
2    (p_trip_id           OUT INTEGER, -- an output parameter
3     p_bicycle_id     IN     INTEGER, -- Must not be NULL.  Must match value value in BC_BICYCLE and BC_DOCK tables.
4     p_start_time     IN     DATE, -- If NULL, use CURRENT_DATE system date value
5     p_membership_id  IN     INTEGER -- Must not be NULL.  Must match value in BC_MEMBERSHIP table.
6    )
7  IS
8  BEGIN
9     IF p_bicycle_id IS NULL
10     THEN
11        raise_application_error (
12           -20000,
13           'Missing mandatory value for bicycle id in BEGIN_TRIP_SP. No trip added.');
14     ELSE
15        INSERT INTO bc_trip (bicycle_id, trip_start_time, membership_id)
16             VALUES (p_bicycle_id,
17                     NVL (p_start_time, CURRENT_DATE),
18                     p_membership_id)
19          RETURNING trip_id
20               INTO p_trip_id;
21     END IF;
22  END;
23  /
Procedure created.

测试：

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2     l_trip_id  NUMBER;
3  BEGIN
4     begin_trip_sp (l_trip_id,
5                    1,
6                    NULL,
7                    100);
8     DBMS_OUTPUT.put_line ('Inserted trip ID = ' || l_trip_id);
9  END;
10  /
Inserted trip ID = 1
PL/SQL procedure successfully completed.

如果没有P_BICYCLE_ID怎么办？

SQL> l5
5*                   1,
SQL> c/1/null/
5*                   null,
SQL> /
DECLARE
*
ERROR at line 1:
ORA-20000: Missing mandatory value for bicycle id in BEGIN_TRIP_SP. No trip
added.
ORA-06512: at "DP_4005.BEGIN_TRIP_SP", line 11
ORA-06512: at line 4

SQL>

结果是

SQL> SELECT * FROM bc_trip;
TRIP_ID BICYCLE_ID TRIP_START MEMBERSHIP_ID
---------- ---------- ---------- -------------
1          1 08.03.2022           100
SQL>

Answer 2

只需为该参数写入默认值：

create or replace procedure BEGIN_TRIP_SP (
p_trip_id               OUT INTEGER,        
p_bicycle_id            IN INTEGER,         
p_start_time            IN DATE DEFAULT  sysdate,--if the parameter is not given it will take sysdate (current date)         
p_membership_id         IN INTEGER         
)...