Binding data to a variable in a CASE WHEN statement-CodePudding

I am writing an oracle PL/SQL compound trigger. In the code I'm querying a single value and putting it into a variable.

From there I check whether the variable is null or not. If it is, I need to assign 0 to another variable, if it isn't I assign 1.

I think this is possible with a CASE WHEN statement.

An example is as follows

//line 23 ↓
SELECT contract_end INTO contractEnd FROM contract WHERE contract_id = :new.expense_job;
//line 25 ↓
SELECT CASE WHEN contractEnd IS NOT NULL THEN
  contractIDCollection(iterator).ended := 1
ELSE
  contractIDCollection(iterator).ended := 0
END
FROM dual;

However, when I do this the compiler throws an error and says that I have not finished the statement.

Is this the correct way to go about doing this?

contractIDCollection is a record with parameters, the definition code is working properly

LINE/COL ERROR
-------- -----------------------------------------------------------------
23/5     PL/SQL: SQL Statement ignored
24/44    PL/SQL: ORA-00905: missing keyword

CodePudding user response：

Do not try to switch from the PL/SQL scope to the SQL scope (with a SELECT statement) to try to assign the variable; just do it all in the PL/SQL scope:

IF contractEnd IS NOT NULL THEN
  contractIDCollection(iterator).ended := 1;
ELSE
  contractIDCollection(iterator).ended := 0;
END IF;

If you did want to incur the overheads of context-switching (don't, as you do not need to and it is likely to be slower) then you can use:

SELECT CASE WHEN contractEnd IS NOT NULL THEN 1 ELSE 0 END
INTO   contractIDCollection(iterator).ended
FROM dual;

or do it all on the line before:

SELECT contract_end,
       CASE WHEN contract_end IS NOT NULL THEN 1 ELSE 0 END
INTO   contractEnd,
       contractIDCollection(iterator).ended
FROM   contract
WHERE  contract_id = :new.expense_job;