Explain Oracle functions and subprograms, and explain oracle function instances

Explain Oracle functions and subprograms, and explain oracle function instances
SubroutineI,Subroutine Overview

The PL/SQL naming block is defined in the Declaration part of the PL/SQL block. The user calls it a subroutine.

Generally speaking: The named PL/SQL block is compiled and stored in the database.

Subroutine composition:

L Declaration

L executable part

L Exception Handling (optional)

Advantages of subprograms:

Adequacy: subprograms can divide programs into easy-to-manage and clearly defined logic modules. It supports top-down design methods.

Maintainability: simplify maintenance operations.

Reusability: Once defined, a subroutine can be used in many applications.

Security: You can set permissions to make data more secure.

II, Sub-course category

Oracle provides four types of subprograms: stored procedures, functions, packages, and triggers.

Process-perform some operations

Function-execute the operation and return the value

III, Subprograms: Functions

A function is a PL/SQL subroutine that can return values.

Functions are divided into built-in functions and custom functions.

1. Built-in functions

A series of functions provided by ORACLE for executing specific operations.

SQL built-in functions contain one or more parameters and return one value.

SQL built-in functions include single-row functions, grouping functions, and analytic functions.

L single row function: returns only one value for each row queried from the table.

Date functions, numeric functions, character functions, conversion functions, and other functions

L grouping function: returns results based on a group of rows. Returns a value for each group of rows.

COUNT (), SUM (), AVG (), MAX (), MIN ()

L analysis function: calculates the aggregate value based on a group of rows. Used to calculate the cumulative ranking and moving average of aggregation completion.

The analysis function returns multiple rows for each group of records.

ROW_NUMBER, RANK, DENSE_RANK

1.1 Numeric Functions

ROUND () Rounding

Select round (10054.345) FROM dual;

-- Return Value: 10054

Select round (10054.345, 2) FROM dual;

-- Return Value: 10054.35 accurate to the second decimal point

Select round (10054.345,-2) FROM dual;

-- Return Value: 10100

TRUNC () round (No rounding)

SELECT trunc (45.67) FROM dual;

-- Return Value: 45

SELECT trunc (-45.22, 0) FROM dual;

-- Return Value:-45

FLOOR () rounded up (minimum value, No rounding)

SELECT floor (45.67) FROM dual;

-- Return value: the minimum integer of 45.

SELECT floor (-45.17) FROM dual;

-- Return:-46 smallest negative integer

MOD () modulo (same as % in JAVA)

SELECT mod (11, 7) FROM dual;

-- Return Value: 4

POWER () Initiator

SELECT power (5, 2) FROM dual;

-- Return Value: 25 to the 2nd power of 5

SQRT () root number

SELECT sqrt (4) FROM dual;

-- Return Value: 2

SIGN () Positive and negative number of the judgment Value

-- Return a positive number, 1, 0, 0, and-1 for a negative number (commonly used in the future)

SELECT sign (10) FROM dual;

-- Return Value: 1

1.2 Character Functions

SUBSTR () string Truncation

SELECT substr ('abcdefg', 2, 4) FROM dual;

-- Return Value: bcde truncates four consecutive bits starting from 2nd bits.

-- Note: in SQL and Oracle, the string badge starts from 1, while in Java, the string badge starts from 0.

LENGTH () Evaluate the LENGTH of a string (the number of characters, irrelevant to the bytes occupied by Chinese and English)

SELECT length ('abc') FROM dual;

-- Return Value: 3

INSTR () searches for character locations (similar to charindex in Java and indexof in SQL Server)

SELECT instr ('abcadefag', 'A') FROM dual;

-- Return Value: 1. Search starts from the string badge 1 by default.

SELECT instr ('abcsadefag', 'A', 4) FROM dual;

-- Return Value: 5. Search from the string Badge 4.

Concat () connection and | same

SELECT concat ('A', 'B') FROM dual;

-- Result: AB

Replace () replace

SELECT replace ('abcdefg', 'A', 9) FROM dual;

-- Result: 9 bcdefg

Select translate ('adbecf', 'abc', '2013') from dual;

Lpad () left Filling

SELECT lpad ('abc', 10, '6') FROM dual; -- 10 is the total length of the string

-- Result: 6666666abc

Rpad () Right Filling

SELECT rpad ('abc', 10, '6') FROM dual;

-- Result: abc6666666

Trim ()

-- Remove spaces on both sides of the string.

-- Remove the specified characters on both sides of the string

-- Syntax: trim ('character 1' FROM 'string 2 ')

SELECT trim ('A' FROM 'aabbbccdda ') FROM dual;

-- Result: bbbccdd

Character corresponding to chr () ASCII code

SELECT chr (97) FROM dual;

-- Result:

ASCII code corresponding to the ASCII () character

SELECT ascii ('A') FROM dual;

-- Result: 97

Decode ()

-- Decode (value, if1, then1, if2, then2, if3, then3,..., else)

If the value is equal to if1, the result of the DECODE function returns then1,.... if the value is not equal to any if value, else is returned. At first glance, decode () can only perform equals tests, but as we have seen just now, we can replace value through some functions or calculations to enable decode () functions must be greater than, less than, or equal.

SELECT decode ('A', 'A', 1, 'B', 2, 'C', 3) FROM dual;

-- 1

-- If the salary is higher than 3500, the system prompts tax payment. If the salary is equal to 3500, it is just right. If the salary is smaller than 3500, efforts should be made.

-- Analysis: Three Cases

SELECT decode (sign (sal-3500), 1, 'tax payby', 0, 'right',-1, 'duration') FROM emp;

1.3 Date Functions

Sysdate: the current system time.

SELECT sysdate FROM dual;

-- Display the time type (date), but convert it to a string?

Systimestamp gets the current system time

SELECT distinct imestamp FROM dual;

-- The result is relatively long. The format is greengeorge time.

Add_months (): Use month to add month.

SELECT add_months (sysdate, 3) FROM dual;

-- Add 3 to the current system time month

SELECT add_months (to_date ('1970-12-12 ', 'yyyy-mm-dd'), 3) FROM dual;

-- Add 3 to the month

Extract () (year, month, day)

SELECT extract (day FROM sysdate) FROM dual;

-- Get the day of the current system

SELECT extract (month FROM sysdate) FROM dual;

-- Take the month of the current system

-- Case Study: Number of employees per month

Monthly group statistics

SELECT extract (month FROM hiredate), count (*) FROM emp

Group by extract (month FROM hiredate)

-- Calculate the number of employees each month.

SELECT extract (year FROM hiredate), extract (month FROM hiredate), count (*) FROM emp

Group by extract (year FROM hiredate), extract (month FROM hiredate)

Months_between () Time Difference

SELECT months_between (sysdate, to_date ('1970-12-12 ', 'yyyy-mm-dd') FROM dual;

-- Subtract the following date from the previous date. The return value is the month, and a negative number or decimal number (the most accurate) appears)

Last_day () is the last day of the month.

SELECT last_day (sysdate) FROM dual;

-- 16:59:13 current time of the last day of the month

Next_day () Next date

SELECT next_day (sysdate, 'thursday') FROM dual;

-- Result: the date of the next "Thursday"

Q: Will the Wednesday and Wednesday of the next week be the same?

Round () takes the last day

-- Rounded up. The second day is counted after 12 o'clock. It has the rounding function.

SELECT round (to_date ('2017-09-08 12:12:12 ', 'yyyy-mm-dd hh: mi: ss') FROM dual;

-- Only the integer part 2009/9/9 has more than 12 points, even if the number is 9.

SELECT round (to_date ('2017-09-08 12:12:12 ', 'yyyy-mm-dd hh24: mi: ss') FROM dual;

-- Only take the integer section 2009/9/9

Trunc () truncation date function

-- Integer, Day

SELECT trunc (to_date ('2017-09-08 12:12:12 ', 'yyyy-mm-dd hh: mi: ss') FROM dual;

-- Only take the integer section 2009/9/8

SELECT trunc (to_date ('2017-09-08 23:12:12 ', 'yyyy-mm-dd hh24: mi: ss') FROM dual;

-- Only take the integer section 2009/9/8

1.4 Conversion functions

The conversion function converts a value from one data type to another.

TO_CHAR

Convert non-char type to char type

SELECT to_char (sysdate, 'yyyy-mm-dd') FROM dual;

SELECT to_char (sysdate, 'yyyymmdd') FROM dual;

SELECT to_char (sysdate, 'yyyy "year" mm "month" dd "day" ') FROM dual;

-- This is the only place in oracle that uses double quotation marks.

TO_DATE

Convert non-date type to date type

SELECT to_date ('2014-09-09 ', 'yyyy-mm-dd') FROM dual;

SELECT to_date ('2017-09-09 18:20:30 ', 'yyyy-mm-dd hh24: mi: ss') FROM dual;

TO_NUMBER

Convert a non-numeric type to a numeric type

SELECT to_number ('123') FROM dual;

SELECT to_number ('34a5 ') FROM dual;

1.5 Set Functions

COUNT ()

SUM ()

AVG ()

MAX ()

MIN ()

1.6 Other functions

Convert null values

Nvl (exp1, exp2)

-- If the first oracle parameter is null, the value of the second parameter is displayed. If the value of the first parameter is not empty, the original value of the first parameter is displayed.

SELECT ename, nvl (comm,-1) FROM emp;

Nvl2 (exp1, exp2, exp3)

-- If the first parameter of the function is null, the value of the third parameter is displayed. If the value of the first parameter is not empty, the value of the second parameter is displayed.

SELECT ename, nvl2 (comm,-1, 1) FROM emp;

Nullif (exp1, exp2)

-- If exp1 and exp2 are equal, NULL is returned. Otherwise, the first value is returned.

SELECT ename, nullif (comm,-1) FROM emp;

1.7 Analysis functions

7. analysis functions

Perform analysis based on the primary query results, such as department-specific summary and department-specific average.

(1) Oracle analysis functions are built on the so-called data window, which can be understood as a data set.

The data of the primary query can be divided into different datasets according to different standards.

For example, partition BY manager_id divides the data in the primary query into N (N indicates the number of different manager_id) Data Windows based on Manager_id.

(2) second, the data window should also be implemented through order by in a certain ORDER.

Row_number () over ()

Rank () over ()

Dense_rank () over ()

Differences between analysis functions and group:

(1) Most functions of analysis functions can be aggregated by group.

(2) the number of rows queried BY the analysis function is determined BY the primary query, and the result of the number of rows in group by is determined BY the unique combination of the sets following group, generally, the number of rows is less than the number of results in the primary query.

SUM (emp. salary) over (partition by emp. manager_id) sum_salary_department, -- total salary of this department

Dense_rank () over (partition by emp. manager_id order by emp. salary DESC) rank_salary_dept -- department salary ranking of the employee

MIN (emp. salary) keep (dense_rank first order by emp. salary) over (partition by emp. manager_id) min_salary_dept_first, -- minimum salary of the Department

First_value (emp. salary) over (partition by emp. manager_id order by emp. salary) min_salary_dept_firstv, -- minimum salary of the Department

LAG (EMP. FULL_NAME, 1, '00') OVER (order by emp. salary desc) LAST_PERSION, -- the person whose SALARY is in front of him

2. Custom Functions 2.1 create functions (in, out, in out)

Syntax for creating a function:

CREATE [or replace] FUNCTION

[(Param1, param2)]

RETURN IS |

[Local declarations]

BEGIN

Executable Statements;

RETURN result;

EXCEPTION

Exception handlers;

END;

-- Case: Give the number, and return the 'paybytax 'or 'right' or 'work'

Create or replace function f_n126 (sid number)

Return varchar2

Is

Ssal number (8, 2 );

Str varchar2 (22); -- note that declare is not required.

Begin

SELECT sal into ssal FROM emp where empno = sid;

If ssal> 3500 then

Str: = 'tax payby ';

Elsif ssal = 3500 then

Str: = 'right ';

Else

Str: = 'duration ';

End if;

Return str;

End;

Create or replace function my_sum (n_a in number)

Return number

Is

N_sum number (5): = 0;

Begin

For int_s in 1 .. n_a loop

N_sum: = n_sum + int_s;

End loop;

Return n_sum;

End;

Restrictions on defining functions:

The function can only accept the IN parameter, but cannot accept the in out or OUT parameter.

The parameter cannot be of the PL/SQL type.

The return type of the function must also be the database type.

There are two methods to access a function:

Use PL/SQL Blocks

Use SQL statements

Create a function

Create or replace function fun_hello

RETURN VARCHAR2

IS

BEGIN

RETURN 'Hello friend ';

END;

Call a function from an SQL statement:

SELECT fun_hello from dual;

Example

CREATE OR REPLACE FUNCTION

Item_price_range (price NUMBER)

RETURN VARCHAR2

Min_price NUMBER;

Max_price NUMBER;

BEGIN

Select max (ITEMRATE), MIN (ITEMRATE)

INTO max_price, min_price

FROM itemfile;

IF price> = min_price AND price <= max_price

THEN

RETURN 'the unit price entered is between the lowest price and the highest price ';

ELSE

RETURN 'out of range ';

End if;

END;

DECLARE

P number: = 300;

MSG VARCHAR2 (200 );

BEGIN

MSG: = item_price_range (300 );

DBMS_OUTPUT.PUT_LINE (MSG );

END;

2.2 call a function

-- Oracle call method:

SELECT f_n126 (7369) FROM dual;

-- Pl/SQL call method:

Declare str varchar2 (22 );

Begin

Str: = f_n126 (7369 );

Dbms_output.put_line (str );

End;

2.3 Delete A Function

Drop function f_name;