CODEWITHSUNDEEP
sqloracle-databaseplsql
Nate Ho
Nate Ho

Reputation: 51

How to create a function to add new record into a table? ORACLE

I am not sure if using the create or replace function is the right way to do this but i am trying to figure out how to ONLY add record to the table when the count of employee ID where month of read date = month of sysdate is not more than 10.

I have a employee_read table using primary key, reading_ID, foreign key emp_id and an attribute of read_date.

The information i found online shows return value. but how do i add into table instead of return? is it do-able?

Thank you for your help!

CREATE OR REPLACE FUNCTION AddNewReadRecord

(varEmpID IN NUMBER. varReadDate IN DATE, varWaterMeterID IN CHAR, varCuReading IN NUMBER, varPrevReading IN Number)

RETURN

BEGIN

END

Upvotes: 0

Views: 238

Answers (1)

Popeye
Popeye

Reputation: 35900

You can obviously add DML in the function but using PRAGMA AUTONOMOUS_TRANSACTION as follows:

CREATE OR REPLACE FUNCTION AddNewReadRecord
(varEmpID IN NUMBER. varReadDate IN DATE, varWaterMeterID IN CHAR, varCuReading IN NUMBER, varPrevReading IN Number)
RETURN number IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
Insert into your_table (...) -- column list
Select ... -- variable list
From your_table 
Where emp_id = varEmpID
And read_date = varReadDate
Group by emp_id
Having count(1) < 10;
Commit;
Return 1; -- add logic for return using exception
END;
/

Upvotes: 2

Related Questions