How do I check if a particular element exists in a table - how can I return true or false?
I have a table that has
Verbally, I want to do this: If a particular user_id
exists in the user_id
column, then return true -- otherwise return false.
CREATE OR REPLACE FUNCTION fun_tiene_cita (id_paciente number, fecha_cita date) return number is begin if (exists(select id_paciente from citas where id_paciente = 500 and fecha_cita = 03/03/2020)) then return 'true'; else return 'false'; end if; END fun_tiene_cita; Obviously, it isn't working, so, what could I do?
The Oracle EXISTS condition is used in combination with a subquery and is considered "to be met" if the subquery returns at least one row. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.
Type a short Oracle program, using the following code as a guide: DECLARE record_exists INTEGER; BEGIN SELECT COUNT(*) INTO record_exists FROM your_table WHERE search_field = 'search value' AND ROWNUM = 1; IF record_exists = 1 THEN DBMS_OUTPUT. put_line('Record Exists') ELSE DBMS_OUTPUT.
The EXISTS operator returns true if the subquery returns any rows, otherwise, it returns false. In addition, the EXISTS operator terminates the processing of the subquery once the subquery returns the first row.
There is no Boolean type in Oracle SQL. You will need to return a 1 or 0, or some such and act accordingly:
SELECT CASE WHEN MAX(user_id) IS NULL THEN 'NO' ELSE 'YES' END User_exists FROM user_id_table WHERE user_id = 'some_user';
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With