Oracle data types, functions and stored procedures

String type
Fixed length: char nchar n denotes Unicode encoding
Variable length: varchar2 nvarchar2

Numeric type: Number (p,s) P: integer digits, S decimal place
Integer integers
Binary_float Single floating point
Binary_double Double floating point
Float (n) floating-point number n indicates precision
Day Type: Date
TIMESTAMP


A user-defined function is a block of code that is stored in a database and is capable of returning values to the calling program. Called as if


System functions, such as the max (value) function, in which value is referred to as a number of parameters. There are 3 types of function references.
In Parameter type: represents the input to the function's parameters.
Out parameter type: Indicates that the parameter is assigned in the function and can be passed to the function calling program.

In out parameter type: Indicates that the parameter can be either passed or assigned.
1. Syntax format:
The syntax format created by SQL syntax is:

CREATE OR REPLACE function function_name/* Functions name */
(
Parameter_name1,mode1 datatype1,/* Parameter definition section */
Parameter_name2,mode2 Datatype2,
Parameter_name3,mode3 Datatype3
...
)
Return return_datatype/* Define return value type */
Is/as
Variable Variabletype; /* DECLARE variable */
BEGIN
Function_body/* Function body part */
Return scalar_expression/* Returns statement */
END function_name;
Description

Function_name:: User-defined function name.

Function names must conform to the definition rules for identifiers. For all of them, the name is unique in the database.
Parameter: User-defined number of parameters.

The user can define one or more of the parameters.
Mode: The type of the parameters.

DataType: The data type of the user-defined parameter.
Return_type:: The data type of the user return value.

The Scalar_expression function returns the value of an expression. The Function_body function body is composed of PL/SQL statements.
Instance:
Create or Replace function GetName (uid in integer) return varchar2
As
Username VARCHAR2 (10)
Begin
Select name into username from users where id=uid;
return (username);
End GetName;
Attention:
(1) Assuming that the function has no parameters, the function name should not be followed by parentheses;

(2) When you create a function, you must remember to write the name of the letter after the end.

--a function without a number of parameters
Create or Replace function Get_user return VARCHAR2 is
V_user VARCHAR2 (50);
Begin
Select username to V_user from User_users;
return v_user;
End Get_user;



Stored Procedures
Basic syntax stored procedures for Oracle stored procedures
1 CREATE OR REPLACE PROCEDURE stored procedure name (parameter type parameter data type, ...) ）
2 is
3 BEGIN
4 NULL;
5 END;
Line 1:
Create OR REPLACE PROCEDURE is an SQL statement that notifies the Oracle database to create a stored procedure called skeleton, overwriting it if it exists;
Line 2:
The IS keyword indicates that a PL/SQL body will be trailing behind.

Line 3:
The BEGIN keyword indicates the start of the PL/SQL body.
Line 4:
The NULL PL/SQL statement indicates that nothing is done, and this sentence cannot be deleted because there is at least one sentence in the PL/SQL body;
Line 5:
End keyword indicates the termination of the PL/SQL body

Like what:
Create or Replace procedure Pcountusers (ID integer,name varchar2)
As
Begin
Insert into users (Id,name) values (id,name);
End;

Invoke: Call Pcountusers (2, ' KK ');



The difference between a function and a stored procedure:
1. The difference in the return value, the function has 1 return values, and the stored procedure is returned by the parameters, can have more or no
2. The difference in invocation, the function can be called directly in the query statement, and the stored procedure must be called separately.
A function is normally used to calculate and return a result of a calculation and the stored procedure is typically used to complete a specific data operation (such as altering, inserting a database table, or running some DDL statements, etc.)

Oracle data types, functions and stored procedures