This article is written about how to create and drop features in Oracle/PLSQL with syntax and examples.
Just as you can in different languages, you can create your very own functions in Oracle.
The syntax to create a function in Oracle is:
CREATE [OR REPLACE] FUNCTION function_name [ (parameter [,parameter]) ] RETURN return_datatype IS | AS [declaration_section] BEGIN executable_section [EXCEPTION exception_section] END [function_name];
When you create a procedure or function, you may outline parameters. There are three sorts of parameters that can be declared:
IN – The parameter can be referenced through the manner or function. The cost of the parameter can not be overwritten by using the manner or function. OUT – The parameter can now not be referenced with the aid of the technique or function, but the cost of the parameter can be overwritten by using the method or function. IN OUT – The parameter can be referenced with the aid of the method or feature and the price of the parameter can be overwritten by way of the manner or function.
Let’s seem to be at an example of how to create a function in Oracle.
The following is a easy example of an Oracle function:
CREATE OR REPLACE Function FindCourse ( name_in IN varchar2 ) RETURN number IS cnumber number; cursor c1 is SELECT course_number FROM courses_tbl WHERE course_name = name_in; BEGIN open c1; fetch c1 into cnumber; if c1%notfound then cnumber := 9999; end if; close c1; RETURN cnumber; EXCEPTION WHEN OTHERS THEN raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM); END;
This characteristic is known as FindCourse. It has one parameter known as name_in and it returns a number. The feature will return the course range if it finds a fit primarily based on route name. Otherwise, it returns a 99999.
You ought to then reference your new feature in a SQL announcement as follows:
SELECT course_name, FindCourse(course_name) AS course_id FROM courses WHERE subject = 'Mathematics';
Once you have created your characteristic in Oracle, you might locate that you need to remove it from the database.
The syntax to a drop a function in Oracle is:
DROP FUNCTION function_name;
function_name The title of the characteristic that you desire to drop.
Let’s seem to be at an instance of how to drop a feature in Oracle.
DROP FUNCTION FindCourse;
This instance would drop the feature referred to as FindCourse.