www.techonthenet.com Open in urlscan Pro
64.40.117.204  Public Scan

Form analysis
0 forms found in the DOM

Text Content

Advertisements


 1. Home
 2. Oracle / PLSQL

JavaScript is required for this website to work properly. Please re-enable
JavaScript in your browser settings.
 * Databases
 * SQL
 * Oracle / PLSQL
 * SQL Server
 * MySQL
 * MariaDB
 * PostgreSQL
 * SQLite

 * MS Office
 * Excel
 * Access
 * Word

 * Web Development
 * HTML
 * CSS
 * JavaScript
 * Color Picker

 * Programming
 * C Language

 * More
 * ASCII
 * Unicode
 * Linux
 * UNIX
 * Techie Humor

Advertisements




ORACLE BASICS

 * ALIASES
 * AND
 * AND & OR
 * BETWEEN
 * COMPARISON OPERATORS
 * DELETE
 * DISTINCT
 * EXISTS
 * FROM
 * GROUP BY
 * HAVING
 * IN
 * INSERT
 * INSERT ALL
 * INTERSECT
 * IS NOT NULL
 * IS NULL
 * JOIN
 * LIKE
 * MINUS
 * NOT
 * OR
 * ORDER BY
 * PIVOT
 * REGEXP_LIKE
 * SELECT
 * SUBQUERY
 * TRUNCATE
 * UNION
 * UNION ALL
 * UPDATE
 * WHERE


ORACLE ADVANCED

 * Alter Table
 * Alter Tablespace
 * Change Password
 * Check Constraints
 * Comments in SQL
 * Create Schema
 * Create Schema Statement
 * Create Table
 * Create Table As
 * Create Tablespace
 * Create User
 * Data Types
 * Declare Variables
 * Drop Table
 * Drop Tablespace
 * Drop User
 * Error Messages
 * Find Default Tablespace
 * Find Users
 * Find Users Logged In
 * Find Version Information
 * Functions
 * Global Temporary
 * Grant/Revoke Privileges
 * Indexes
 * Literals
 * Local Temporary
 * Primary Keys
 * Procedures
 * Roles
 * Sequences
 * Set Default Tablespace
 * Synonyms
 * System Tables
 * Unique Constraints
 * Views


ORACLE CURSORS

 * Close Cursor
 * Cursor Attributes
 * Declare Cursor
 * Fetch Cursor
 * Open Cursor
 * Select For Update
 * Where Current Of


ORACLE EXCEPTION HANDLING

 * Named Programmer-Defined Exception
 * Named System Exception
 * WHEN OTHERS Clause
 * SQLCODE
 * SQLERRM


ORACLE FOREIGN KEYS

 * Disable Foreign Key
 * Drop Foreign Key
 * Enable Foreign Key
 * Foreign Key
 * Foreign Key (cascade delete)
 * Foreign Key (set null delete)


ORACLE LOOPS/CONDITIONALS

 * CURSOR FOR LOOP
 * EXIT
 * FOR LOOP
 * GOTO
 * IF-THEN-ELSE
 * LOOP
 * REPEAT UNTIL LOOP
 * WHILE LOOP


ORACLE TRANSACTIONS

 * Commit Transaction
 * Lock Table
 * Rollback Transaction
 * Set Transaction


ORACLE TRIGGERS

 * After Delete Trigger
 * After Insert Trigger
 * After Update Trigger
 * Before Delete Trigger
 * Before Insert Trigger
 * Before Update Trigger
 * Disable All Triggers
 * Disable Trigger
 * Drop Trigger
 * Enable All Triggers
 * Enable Trigger


STRING/CHAR FUNCTIONS

 * ASCII
 * ASCIISTR
 * CHR
 * COMPOSE
 * CONCAT
 * Concat with ||
 * CONVERT
 * DECOMPOSE
 * DUMP
 * INITCAP
 * INSTR
 * INSTR2
 * INSTR4
 * INSTRB
 * INSTRC
 * LENGTH
 * LENGTH2
 * LENGTH4
 * LENGTHB
 * LENGTHC
 * LOWER
 * LPAD
 * LTRIM
 * NCHR
 * REGEXP_INSTR
 * REGEXP_REPLACE
 * REGEXP_SUBSTR
 * REPLACE
 * RPAD
 * RTRIM
 * SOUNDEX
 * SUBSTR
 * TRANSLATE
 * TRIM
 * UPPER
 * VSIZE


NUMERIC/MATH FUNCTIONS

 * ABS
 * ACOS
 * ASIN
 * ATAN
 * ATAN2
 * AVG
 * BITAND
 * CEIL
 * COS
 * COSH
 * COUNT
 * EXP
 * FLOOR
 * GREATEST
 * LEAST
 * LN
 * LOG
 * MAX
 * MEDIAN
 * MIN
 * MOD
 * POWER
 * REGEXP_COUNT
 * REMAINDER
 * ROUND (numbers)
 * ROWNUM
 * SIGN
 * SIN
 * SINH
 * SQRT
 * SUM
 * TAN
 * TANH
 * TRUNC (numbers)


DATE/TIME FUNCTIONS

 * ADD_MONTHS
 * CURRENT_DATE
 * CURRENT_TIMESTAMP
 * DBTIMEZONE
 * EXTRACT
 * LAST_DAY
 * LOCALTIMESTAMP
 * MONTHS_BETWEEN
 * NEW_TIME
 * NEXT_DAY
 * ROUND (dates)
 * SESSIONTIMEZONE
 * SYSDATE
 * SYSTIMESTAMP
 * TRUNC (dates)
 * TZ_OFFSET


CONVERSION FUNCTIONS

 * BIN_TO_NUM
 * CAST
 * CHARTOROWID
 * FROM_TZ
 * HEXTORAW
 * NUMTODSINTERVAL
 * NUMTOYMINTERVAL
 * RAWTOHEX
 * TO_CHAR
 * TO_CLOB
 * TO_DATE
 * TO_DSINTERVAL
 * TO_LOB
 * TO_MULTI_BYTE
 * TO_NCLOB
 * TO_NUMBER
 * TO_SINGLE_BYTE
 * TO_TIMESTAMP
 * TO_TIMESTAMP_TZ
 * TO_YMINTERVAL


ANALYTIC FUNCTIONS

 * CORR
 * COVAR_POP
 * COVAR_SAMP
 * CUME_DIST
 * DENSE_RANK
 * FIRST_VALUE
 * LAG
 * LAST_VALUE
 * LEAD
 * LISTAGG
 * NTH_VALUE
 * RANK
 * STDDEV
 * VAR_POP
 * VAR_SAMP
 * VARIANCE


ADVANCED FUNCTIONS

 * BFILENAME
 * CARDINALITY
 * CASE
 * COALESCE
 * DECODE
 * EMPTY_BLOB
 * EMPTY_CLOB
 * GROUP_ID
 * LNNVL
 * NANVL
 * NULLIF
 * NVL
 * NVL2
 * SYS_CONTEXT
 * UID
 * USER
 * USERENV

--------------------------------------------------------------------------------


ORACLE / PLSQL: FUNCTIONS

This Oracle tutorial explains how to create and drop functions in Oracle/PLSQL
with syntax and examples.


CREATE FUNCTION

Just as you can in other languages, you can create your own functions in Oracle.


SYNTAX

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 define parameters. There are
three types of parameters that can be declared:

 1. IN - The parameter can be referenced by the procedure or function. The value
    of the parameter can not be overwritten by the procedure or function.
 2. OUT - The parameter can not be referenced by the procedure or function, but
    the value of the parameter can be overwritten by the procedure or function.
 3. IN OUT - The parameter can be referenced by the procedure or function and
    the value of the parameter can be overwritten by the procedure or function.


EXAMPLE

Let's look at an example of how to create a function in Oracle.

The following is a simple 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 function is called FindCourse. It has one parameter called name_in and it
returns a number. The function will return the course number if it finds a match
based on course name. Otherwise, it returns a 99999.

You could then reference your new function in a SQL statement as follows:

SELECT course_name, FindCourse(course_name) AS course_id
FROM courses
WHERE subject = 'Mathematics';


DROP FUNCTION

Once you have created your function in Oracle, you might find that you need to
remove it from the database.


SYNTAX

The syntax to a drop a function in Oracle is:

DROP FUNCTION function_name;

function_name The name of the function that you wish to drop.


EXAMPLE

Let's look at an example of how to drop a function in Oracle.

For example:

DROP FUNCTION FindCourse;

This example would drop the function called FindCourse.

NEXT: Procedures
Share on:


Advertisements



Home | About Us | Contact Us | Testimonials | Donate



While using this site, you agree to have read and accepted our Terms of Service
and Privacy Policy.

Copyright © 2003-2022 TechOnTheNet.com. All rights reserved.