Introduction to oracle-defined functions

2011-09-08  来源:本站原创  分类:Database  人气:95 

User-defined function is stored in a database block, the value can be returned to the caller. Calls as the system functions, such as max (value) function, which, value is known as parameters. There are three types of function parameters.

IN parameter types: the input to the function.

OUT parameter type: This parameter is assigned the function can be passed to the function caller.

IN OUT parameter types: either pass that parameter value can be assigned.

1, the syntax:

SQL syntax to create the syntax:

CREATE OR REPLACE FUNCTION function_name /**//* function name * /
(
Parameter_name1, mode1 datatype1, /**//* parameter definition section * /
Parameter_name2, mode2 datatype2,
Parameter_name3, mode3 datatype3
...
)
RETURN return_datatype /**//* define the return value type * /
IS / AS
BEGIN
Function_body /**//* function body part * /
RETURN scalar_expression /**//* return statement * /
END function_name;

Description:

function_name:: user-defined function name. Function identifier name must conform to the definition of rules, its owners, the name is unique in the database.

parameter: the user-defined parameters. Users can define one or more parameters.

mode: parameter type.

datatype: data type of user-defined parameters.

return_type:: User return value data types.

Function returns the value of the expression scalar_expression, function_body function body from the pl / sql statement form.

2, sample

Function code:

create or replace function T01001_count
return number
is
count_T01001 number;
begin
select count (*) into count_T01001 from T01001;
return (count_T01001);
end T01001_count; - remember that a semicolon is absolutely necessary

Call:
declare
i number;
begin
i: = T01001_count ();
dbms_output.put_line (to_char (i));
end; - remember that a semicolon is absolutely necessary

Note:

(1) If the function has no parameters, then the function should not be in parentheses after the name;

(2) function when creating the back end must remember to write the function name

- No parameters
create or replace function get_user return varchar2 is
v_user varchar2 (50);
begin
select username into v_user from user_users;
return v_user;
end get_user;

- Test methods a
select get_user from dual;

Method Two
SQL> var v_name varchar2 (50)
SQL> exec: v_name: = get_user;

PL / SQL procedure successfully completed.

SQL> print v_name

V_NAME
------------------------------
TEST

Method Three
SQL> exec dbms_output.put_line ('the current database user is:' | | get_user);
The current database user is: TEST

PL / SQL procedure successfully completed.
- No parameters
create or replace function get_user return varchar2 is
v_user varchar2 (50);
begin
select username into v_user from user_users;
return v_user;
end get_user;
- Test methods a
select get_user from dual;
Method Two
SQL> var v_name varchar2 (50)
SQL> exec: v_name: = get_user;
PL / SQL procedure successfully completed.
SQL> print v_name
V_NAME
------------------------------
TEST
Method Three
SQL> exec dbms_output.put_line ('the current database user is:' | | get_user);
The current database user is: TEST
PL / SQL procedure successfully completed.
Sql Code
- IN parameter of a function with
create or replace function get_empname (v_id in number) return varchar2 as
v_name varchar2 (50);
begin
select name into v_name from employee where id = v_id;
return v_name;
exception
when no_data_found then
raise_application_error (-20001, 'you enter the ID is invalid! ");
end get_empname;
- IN parameter of a function with
create or replace function get_empname (v_id in number) return varchar2 as
v_name varchar2 (50);
begin
select name into v_name from employee where id = v_id;
return v_name;
exception
when no_data_found then
raise_application_error (-20001, 'you enter the ID is invalid! ");
end get_empname;

Attachment:

Function call restrictions
1, SQL statement can only call stored function (server side), but can not call the client function
2, SQL can only be called with the input parameters, not with the output, input and output functions
3, SQL can not use PL / SQL-specific data types (boolean, table, record, etc.)
4, SQL statement calls the function can not contain INSERT, UPDATE and DELETE statements

View the source code for hospital functions
oracle source code of the function name and the information stored in the data dictionary user_source
select text from user_source where name = 'GET_EMPNAME';

Delete function
drop function get_empname

相关文章
  • Layman Oracle analytic functions 2011-07-04

    Oracle development topics of: analysis of function (OVER) 1 Oracle development topics of: analysis of the function 2 (Rank, Dense_rank, row_number) 6 Oracle development topics of: analysis of the function 3 (Top / Bottom N, First / Last, NTile) 10 Or

  • Ext Ext.util.Format introduction to commonly used functions 2010-06-20

    <%@page contentType="text/html" pageEncoding="UTF-8"%> <% request.setAttribute("base", request.getContextPath()); %> <html> <head> <meta http-equiv="Content-Type" content="text/html;

  • Daquan ORACLE internal functions as well as the difference between SQLSERVER 2010-07-30

    Daquan ORACLE internal functions as well as the difference between SQLSERVER Collection Character function Here are the characters Oracle support functions and their Microsoft SQL Server equivalent function. Functions Oracle Microsoft SQL Server The

  • Chapter 1 Introduction to Oracle Oracle 2011-04-29

    Oracle 9i installation and uninstallation Chapter 2 of Oracle query Oracle data types, functions Chapter 1 Introduction to Oracle Oracle Note: This article uses the version for Oracle 9i 1, the technical objectives Understand the various components o

  • [Change] javascript defined functions Methods 2010-03-30

    In JavaScript, the way defined functions can be said is full of tricks, there are many ways, the following definition of the function of several common way to summarize this: 1. The most conventional method defined function name () () <script languag

  • el expression defined functions 2010-06-05

    Expression language can be used in addition to the basic operators, but also can use a custom function. By using a custom function, increased expression language functions. EL expression function, the main function is to complete the modification of

  • ORACLE - Oracle analytic functions detailed in [1] 2010-06-07

    1. Analysis of the function 1 (OVER) Contents: =============================================== About 1.Oracle analysis functions 2. Oracle simple example of analysis functions 3. Of an analytic function OVER, Oracle analysis functions Description: In

  • Collection of Oracle common functions 2010-08-11

    Oracle numeric functions common feature highlights: To obtain the client IP address trim (NVL (sys_context ('userenv', 'ip_address'), '255 .255.255.255')) abs (m) m the absolute value mod (m, n) m is the remainder after n addition to power (m, n) m o

  • oracle analytic functions over (transfer) 2010-08-13

    1, Oracle analysis functions Description: In the daily production environment, we have contacted more than yes OLTP Xitong (ie Online Transaction Process), the system is characterized by Jubei Shishiyaoqiu, Or at least for the length of time Xiangyin

  • Oracle Group Functions Summary 2010-09-19

    Oracle Group Functions Summary 1.OVER (PARTITION BY ..) Cases: select a, b, c, sum (c) OVER (PARTITION BY b) sum_c The value of the b column c the same value of the cumulative row. - Retrieve the type specified number of percentage Lingshou Hu select

  • Oracle Analytic Functions: RANK, DENSE_RANK, FIRST and LAST 2010-12-13

    Oracle / PLSQL: Rank Function: http://techonthenet.com/oracle/functions/rank.php Oracle Rank: http://psoug.org/reference/rank.html RANK, DENSE_RANK, FIRST and LAST Analytic Functions: http://www.oracle-base.com/articles/misc/RankDenseRankFirstLastAna

  • Oracle Analytic Functions: RANK, DENSE_RANK, FIRST and LAST; PARTITION BY 2010-12-13

    Oracle / PLSQL: Rank Function: http://techonthenet.com/oracle/functions/rank.php Oracle Rank: http://psoug.org/reference/rank.html RANK, DENSE_RANK, FIRST and LAST Analytic Functions: http://www.oracle-base.com/articles/misc/RankDenseRankFirstLastAna

  • oracle analytic functions over and the window function 2011-01-11

    oracle analytic functions over and the window function A: Analysis of function over Oracle began offering analysis functions from 8.1.6 to analyze the function used to calculate the aggregate value based on some kind of group, and aggregate functions

  • Oracle WMSYS Functions 2011-07-13

    Oracle WMSYS Functions Version 11.2 GENERAL Data Types CREATE OR REPLACE TYPE wm_period AS OBJECT ( validfrom TIMESTAMP WITH TIME ZONE, validtill TIMESTAMP WITH TIME ZONE); Security conn / as sysdba ALTER USER wmsys ACCOUNT UNLOCK IDENTIFIED BY wmsys

  • Hive User Defined Functions 2011-07-14

    Hive User Defined Functions Hive User Defined Functions (UDFs) fall into the following categories: ( * ) Built-in Operators Relational Operators Arithmetic Operators Logical Operators Complex Type Constructors Operators on Complex Types Built-in Func

  • Oracle Analytic Functions RANK (), ROW_NUMBER (), LAG (), etc. Use (reproduced) 2010-11-15

    Oracle Analytic Functions RANK (), ROW_NUMBER (), LAG () to use such ROW_NUMBER () OVER (PARTITION BY COL1 ORDER BY COL2) Said the group based on COL1, COL2 sort according to the group and this internal value that each number within the ordered seque

  • Oracle SQL functions and mathematical functions mathematical difference 2011-02-21

    Oracle mathematical functions and mathematical functions in SQL Server are not the same. ① absolute S: select abs (-1) value O: select abs (-1) value from dual ② rounded (large) S: select ceiling (-001) value O: select ceil (-001) value from dual ③ r

  • oracle analytic functions dense_rank over partition 2011-03-14

    oracle analysis and statements: http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/analysis.htm # DWHSG0205 http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:122801500346829407 oracle aggregate functions: http://psoug.or

  • Introduction to oracle 9i (primer 1 )----- 2011-03-30

    Introduction to oracle 9i (primer) 1 Introduction: the need to have some basis for the database, do not speak here of basic SQL, focus on Oracle 9i in some of the more special things, many of which are frequently used in practice. One, Oracle 9i Inst

  • Oracle group functions and Case Analysis 2011-04-14

    Oracle's group function, respectively: MAX () MIN () AVG () COUNT () SUM () 1. COUNT (): That function as the look-up table in the data field is not empty the total number of articles; For example: SQL> select count (empno) from emp; COUNT (EMPNO) --