This article will help you to understand how to create a user defined function. It’s also known as stored function or user function. User defined functions are similar to procedures. The only difference is that function always returns a value. User defined functions can be used as a part of an SQL expression. Note Oracle …
The BITAND function treats its inputs and its output as vectors of bits, the output is the bitwise AND of the inputs. Basically it performs below steps. Converts the inputs into binary. Performs a standard bitwise AND operation on these two strings. Converts the binary result back into decimal and returns the value. 1. BITAND …
The ALTER FUNCTION statement explicitly recompile a standalone function. Sometimes, ALTER TABLE on the table being used in function will cause the function becomes INVALID, we need to recompile (alter function) it to make it VALID again. 1. ALTER function example First, we will create table test_alter, function get_max_amount. In this function, we are using …
This article show you how to use ATAN() and ATAN2() function in Oracle PL/SQL. 1. ATAN function examples The ATAN function returns the arc tangent of n, the n can be in an unbounded range. The function will returns a value in the range of -pi/2 to pi/2, expressed in radians. SELECT ATAN(1.5) FROM DUAL; …
The CEIL function round the specified number up, and return the smallest number that is greater than or equal to the specified number. CEIL function examples SELECT CEIL(1.2) FROM DUAL; — output : 2 SELECT CEIL(1.5) FROM DUAL; — output : 2 SELECT CEIL(1.7) FROM DUAL; — output : 2 SELECT CEIL(1.99) FROM DUAL; — …
The FLOOR() function round the specified number down, and return the largest number that is less than or equal to the specified number. FLOOR function examples SELECT FLOOR(1.2) FROM DUAL; — output : 1 SELECT FLOOR(1.5) FROM DUAL; — output : 1 SELECT FLOOR(1.7) FROM DUAL; — output : 1 SELECT FLOOR(1.99) FROM DUAL; — …
The ACOS() function returns the arc cosine of input n, the input n must be in the range of -1 to 1. The function will return a value in the range of 0 to pi, expressed in radians. ACOS function examples SELECT ACOS(.2) FROM DUAL; — output 1.36943840600456582777619613942212803186 SELECT ACOS(-.4) FROM DUAL; — output 1.98231317286238463861605958925708704694 …
The ASIN() function returns arc sine of input n, the input n must be in the range of -1 to 1. The function will return a value in the range of -pi/2 to pi/2, expressed in radians. ASIN function examples SELECT ASIN(.25) FROM DUAL; — output 0.25268025514207865348565743699370756609 SELECT ASIN(-.5) FROM DUAL; — output -0.52359877559829887307710723054658381405 SELECT …
This ABS() function returns absolute value of a number, for example, the absolute value of 5 is 5, and the absolute value of -5 is also 5. In Mathematics, absolute value can be think of as a distance from zero. ABS function examples SELECT ABS(-15) FROM DUAL; — 15 SELECT ABS(-51.321) FROM DUAL; –51.321 SELECT …
This article shows you how to use INSTEAD OF TRIGGER to update A noneditioning view A nested table column of a noneditioning view This INSTEAD OF triggers provide a way to modify views that cannot be modified directly through DML statements like INSERT, UPDATE and DELETE An INSTEAD OF trigger is Always a row-level trigger. …
This article shows you how to use BEFORE DELETE TRIGGER, it will fire before the delete operation is executed. In real life scenarios, it is mostly used for purposes like: Restrict invalid DELETE operation. Delete data from another table. 1. Restrict invalid DELETE operation In this example, We have two tables item_details and order_details. The …
This article shows you how to use DROP FUNCTION to delete a function from Oracle database. 1. DROP function example 1.1 Create a function get_current_month. Then we will delete the function using DROP FUNCTION statement. –Creating function CREATE OR REPLACE FUNCTION get_current_month RETURN VARCHAR2 IS curr_month VARCHAR2(10); BEGIN SELECT to_char(sysdate, ‘MONTH’) INTO curr_month FROM dual; …
Check the USER_TRIGGERS table, you can get the Trigger status easily : — display all triggers for users SELECT TRIGGER_NAME,STATUS FROM USER_TRIGGERS; — display status for a specified trigger SELECT TRIGGER_NAME,STATUS FROM USER_TRIGGERS WHERE TRIGGER_NAME = ‘TRIGGER_NAME’; SELECT TRIGGER_NAME,STATUS FROM USER_TRIGGERS WHERE TRIGGER_NAME IN(‘TRIGGER_NAME_A’, ‘TRIGGER_NAME_B’); Sample data. TRIGGER_NAME STATUS TRIGGER_NAME_A ENABLED TRIGGER_NAME_B DISABLED TRG_BEFORE_EMP_UPDATE ENABLED …
This article shows you how to use BEFORE UPDATE TRIGGER, it’s fire before the update operation is executed. In real life scenarios, it is mostly used for purposes like: Data validation Update values automatically Data logging, or auditing 1. Data Validation Suppose some companies have job openings and already having application data and the criteria …
This article shows you how to use DROP TRIGGER to delete a trigger. — delete a trigger DROP TRIGGER trigger_name; Note In DROP TRIGGER statement, recommend to specify a schema name before trigger name. If you don’t specify a schema name, then database will assume that the trigger is in your own schema. 1. Table …
This article shows you how to use ALTER TRIGGER and ALTER TABLE to enable and disable triggers. — enable / disable a trigger ALTER TRIGGER trigger_name ENABLE; ALTER TRIGGER trigger_name DISABLE; — enable / disable all triggers for a specific table ALTER TABLE table_name ENABLE ALL TRIGGERS; ALTER TABLE table_name DISABLE ALL TRIGGERS; 1. Table …
This article shows you how to use BEFORE INSERT TRIGGER, it’s fire BEFORE an INSERT operation is executed. In real life scenarios, it is mostly used for purposes like Data validation Update values automatically (e.g CREATED_BY, CREATION_DATE etc) 1. Table Create a employee_details, we will try to insert different values into this table and observe …
This article shows you how to use AFTER INSERT TRIGGER, it will fire after the insert operation is executed. 1. After INSERT Trigger In this example, if a new user is created in user_details, but fields like passport_no or driving_license_no is missing, a new record will be inserted into user_reminders via ‘after insert’ trigger on …
The following SQL shows you how to get the source code for a specified trigger : select text from user_source where name =’&trigger_name’ and type =’TRIGGER’; References Oracle database – USER_SOURCE table
This article shows you how to use AFTER UPDATE TRIGGER, it will fire after the update operation is executed. 1. Logging example In this example, after each update on ‘SALARY’ column of employee_salary, it will fire a ‘after update’ trigger and insert the new updated data into a employee_salary_log table, for audit purpose. 1.1 Create …
This article shows you how to use ALTER TRIGGER to rename a trigger. — rename a trigger ALTER TRIGGER original_name RENAME TO new_name; 1. Table + Trigger electricity_bill create table electricity_bill ( bill_id number(5) primary key, amount number(5) ); — Table ELECTRICITY_BILL created. trg_rename_example CREATE OR REPLACE TRIGGER trg_rename_example BEFORE UPDATE OR DELETE OR INSERT …
This article shows you how to use AFTER DELETE TRIGGER, it will fire after the delete operation is executed. In real life scenarios, it is mostly used for purposes like: Auditing or logging 1. After DELETE Trigger In this example, if the user deleted a row of medical_bills, the deleted row will be inserted into …