Oracle functions in a detailed "Weber production"

First, what is a function

A function:

1. is a named PL/SQL block and must return a value

2. Can be stored in the database repeatedly executed

3. Can be part of an expression or provide a parameter value

II. syntax for creating functions

You must have at least one return value to create the template:

CREATE [OR REPLACE] FUNCTION function_name [(Parameter1 [Mode1] datatype1, ...)] RETURN datatypeis | as [Local_variable_declarations; ...] BEGIN  - actions;  RETURN expression; END [function_name];

Example of creating a function:

Create or Replace function get_sal (v_employee_id employees.employee_id%type)  returnis    v_salary employees.salary%type;begin  Select  salary into    v_ Salary    from  employees   where employee_id = v_employee_id;   return V_salary;end;

Third, the way to execute the function:

1. Called as part of an expression

1.1 Using host variables to get results

Sql> Conn hr/hrsql> variable sal number (6); SQL> Execute:sal:=get_sal;P l/SQL process completed successfully. SQLfor999999sql> print sal;    SAL-------  24000

1.2 Using local variables to get results

Declare  sal number (6); begin  Sal:= get_sal (+);  Dbms_output.put_line (' employee's salary:' | | Sal); end;

2. Passing to another subroutine as a parameter

Set serveroutput onsql> Execute dbms_output.put_line (get_sal ()); 24000 PL/SQL procedure successfully completedsqlselect get_sal ( from Dual Get_sal (------------)       24000

3. Using in SQL statements

Select get_sal ( from dual; Get_sal (------------)       24000

Iv. advantages of user-defined functions in SQL statements

Function:

1. Oracle's Custom functions

2. User-defined Functions

Advantage:

1. When SQL is not available, you can extend the SQL statement

2. The efficiency of filtering data can be improved when used to filter data in the WHERE clause

3. Can manipulate the data

Using a function in an SQL expression: example

 Create or Replace function tax (value Employees.salary%type) return  number Span style= "color: #0000ff;" >is  begin  return  (Value * 0.08   

Select  from employees; First_Name tax           (SALARY)-------------------------------Donald                       208Douglas                       208Jennifer                     352...             Kevin                        107 rows selected

V. Where to call user-defined functions

User-defined functions, like the built-in single-line functions, can be used to:

1. SELECT list

2. Conditional expressions for where and having clauses

3. Connect by of the query statement, START with, ORDER by, GROUP by, the value clause of the INSERT statement, and the SET clause of the UPDATE statement

Vi. restrictions on calling a function in an SQL expression

Restrictions on calling a function in an SQL expression:

1. Only stored functions (server-side) can be called in SQL statements, and functions that cannot be called by clients

2. Functions that are called in SQL statements can only have input parameters (in) and cannot have output parameters (out) and input/output parameters (INOUT)

3. Functions that are called in SQL statements can only use standard data types supported by SQL, not the unique data types (such as boolean,table and record, etc.)

4. The function called in the SQL statement cannot contain insert,update, and the DELETE statement

Let's verify the fourth article:

 Create or Replace function Dml_call_sal (sal number) return  number is   Begin INSERT into employees (employee_id, last_name, email, hire_date, job_id, Salary) VALUES ( 1 ,  " frost  , "    ", Sysdate, "   Sa_man   "  return   (Sal+100  ); End;  

Call this function in sql:
set salary=dml_call_sal (where employee_id=107Set salary =dml_call_sal (+where employee_id=107ORA-04091: Table HR. EMPLOYEES has changed, the trigger/ function cannot read it ora-06512"HR. Dml_call_sal"3

Vii. Viewing the data dictionary of a function

The following view can view information about a function:

user_source all_sourcedba_sourceselect textfrom   user_sourcewhere  'FUNCTION ' and     ='dml_call_sal'

Use User_objects to view object names of type function

SELECT object_namefrom   user_objectswhere  'FUNCTION';

Sql> Select object_name from user_objects where object_type= ' FUNCTION ';
object_name
--------------------------------------------------------------------------------
Get_sal
Tax
Dml_call_sal

Use User_source to view the contents of a function

Sql>SelectText,line,name, type fromUser_sourcewhereType='FUNCTION'and name='Dml_call_sal';                           TEXT Line NAME TYPE-------------------------------------------------------------------------------- ---------- --------------------- --------- ------------function Dml_call_sal (sal number)returnNumber is                                        1Dml_call_sal Functionbegin 2dml_call_sal FUNCTION INSERT INTO employees (employee_id, last_name, email, hire_date, job_id, Sala Ry3dml_call_sal FUNCTION VALUES 4dml_call_sal FUNCTION (1,'Frost','[email protected]', Sysdate,'Sa_man', Sal);5dml_call_sal FUNCTIONreturn(sal+ -);6dml_call_sal FUNCTION end; 7dml_call_sal FUNCTION7Rows selected

Eight, process and function comparison

Ix. Delete function

To delete a function:

Syntax: Drop function function_name Example: Drop function get_sal;

When the function is deleted, all permissions on the function are deleted

The Create OR REPLACE syntax equals the first delete function, burning re-creation, and permissions are still preserved

X. Permissions

1. Definition rights (default)

Sql> Conn hr/hrconnected.sqlasselect from employees; Table created. SQLSelect from e;  COUNT (*)----------       107SQL> Conn scott/Tiger; Connected.

Sql> CREATE TABLE E as SELECT * from EMP;

Table created.

Sql> Select COUNT (*) from E;

COUNT (*)
----------
14

Sql> Conn hr/hr;
Connected.
sql> Create or replace procedure Insert_e is
2 begin
3 INSERT INTO e select * from E;
4 commit;
5 end;
6/

Procedure created.

Sql> Show User
USER is "HR"

Sql> Grant execute on insert_e to Scott;

Grant succeeded.

Sql> Conn Scott/tiger
Connected.
Sql> Select COUNT (*) from E;

COUNT (*)
----------
14

Sql> Conn hr/hr;
Connected.
Sql> Select COUNT (*) from E;

COUNT (*)
----------
107

Sql> Conn Scott/tiger;
Connected.

Sql> Execute hr.insert_e;

PL/SQL procedure successfully completed.

Sql> Conn Hr/hr
Connected.
Sql> Select COUNT (*) from E;

COUNT (*)
----------
214

2. Caller permissions

By default, the program executes in its owner's identity (the definition of the person). The program that defines the permissions is bound to its schema, and the calling object does not need to add a schema full name. For example, if the pattern HR and OE have deparments tables, the HR-owned program can call departments without hr.departments. And if OE calls the HR program, the program still calls the HR departments.

If you want different schemas (schemas) to invoke the same program but you can manipulate the objects you own, you can add Authid current_user when you define the program.

Conn hr/hrcreate or Replace procedure insert_e Authid Current_User isbegin INSERT INTO eSelect* frome; Commit;end;grant execute on Insert_e to Scott;conn Scott/Tigerexecute insert_e;SelectCOUNT (*) frome; COUNT (*)---------- -Conn HR/HRSelectCOUNT (*) frome; COUNT (*)----------214

Oracle functions in a detailed "Weber production"