PL/SQL Programming (vi)--storage functions & Stored Procedures

(a) Introduction

　　Oracle provides the ability to store a PL/SQL program in a database and run it anywhere, so it's called a stored procedure or function. procedures and functions are collectively known as PL/SQL subroutines, which are named PL/SQL blocks that are stored in the database and exchanged with their callers through input, output parameters, or input/output parameters. the only difference between a procedure and a function is that the function always returns data to the caller, and the procedure does not return data.

(ii) Create a function
1. Create an inline function

Grammar:

2. Functions without parameters

--HelloWorld of function: Returns a "HelloWorld" string

Call this function

Method One:

Method Two:

3. Functions with Parameters

Run:

Method One:

Method Two:

--Create a storage function that returns the system time

--Define a function: Gets the sum of the wages for a given department, requires that the department number is defined as a parameter, and the payroll is defined as the return value.

4, out-type parameters:

Because a function can have only one return value, a PL/SQL program can have multiple return values through an out-type parameter implementation

--Requirements: Define a function: Gets the sum of the payroll for a given department and the total number of employees in that department (defined as an out type parameter).

Requirement: The department number is defined as a parameter and the payroll is defined as the return value.

Run:

You can use the default keyword to set the defaults for input parameters when declaring a function parameter in the Create OR replacefunction statement

(iii) Stored procedures

Establish stored procedures on Oracle server that can be called by multiple applications, pass parameters to stored procedures, or pass back parameters to small stored procedures

1. Create a stored procedure

Grammar:

--Define a stored procedure: Gets the sum of the wages for a given department (through out parameters), requires: Department number and payroll are defined as parameters

-- Customize a stored procedure to do the following:

A pay raise for an employee of a given department (as an input parameter), if it is to the company at the time of (?, 95).
[95, 98)%3
[98,?] %
The following results are returned: How much extra cost is required per month for this pay raise (define an out-of-type output parameter).

PL/SQL Programming (vi)--storage functions & Stored Procedures