PL/SQL CURRENT_DATE if NULL-CodePudding

I need some help for figuring out how to use the CURRENT_DATE if the p_start_time is NULL when doing an insert. The code below is what I have figured out so far (except for the insert statement). The code I need help for is the last code block.

Using 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
    );

CodePudding user response：

See if this helps; I presumed trip_id is somehow automatically generated so you'll be just returning its value to the caller.

Sample table:

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.

Procedure:

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.

Testing:

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.

What if there's no 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>

Result is

SQL> SELECT * FROM bc_trip;

   TRIP_ID BICYCLE_ID TRIP_START MEMBERSHIP_ID
---------- ---------- ---------- -------------
         1          1 08.03.2022           100

SQL>

CodePudding user response：

Just write default value for that parameter:

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         
  )...