Oracle data types, functions and stored procedures, and oracle Data Types

Oracle data types, functions and stored procedures, and oracle Data Types
String type
Fixed Length: char nchar n indicates Unicode encoding
Variable Length: varchar2 nvarchar2

Number Type: number (P, S) P: integer digits, S decimal places
Integer
BINARY_FLOAT Single Floating Point
BINARY_DOUBLE Double Floating Point
FLOAT (N) floating point number N indicates precision
DATE type: DATE
TIMESTAMP


A user-defined function is a code block stored in a database. It can return the value to the calling program. The call time is as follows:


Like system functions, such as max (value) functions, value is called a parameter. There are three types of function parameters.
IN parameter type: indicates the parameter input to the function.
OUT parameter type: indicates that the parameter is assigned a value in the function and can be passed to the function calling program.
In out parameter type: indicates that a parameter can be either passed or assigned a value.
1. Syntax format:
The syntax format created by SQL syntax is:

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/
Variable variableType;/* declare variable */
BEGIN
Function_body/* function body part */
RETURN scalar_expression/* RETURN statement */
END function_name;
Note:

Function_name: User-Defined Function Name. The function name must comply with the rules defined by the identifier. For its owner, the name is unique in the database.
Parameter: User-defined parameter. You can define one or more parameters.
Mode: parameter type.
Datatype: Data Type of user-defined parameters.
Return_type: indicates the data type returned by the user.
The function returns the value of the scalar_expression expression. The function_body function 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;
Note:
(1) If the function does not have a parameter, brackets should not be placed after the function name; no parameter name is required during the call.

(2) Remember to write the function name after the end when creating the function.

-- Functions without 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;



Stored Procedure
Oracle stored procedure basic syntax Stored Procedure
1 create or replace procedure stored PROCEDURE name (parameter name type parameter data type ,...)
2 IS
3 BEGIN
4 NULL;
5 END;
Row 1:
Create or replace procedure is an SQL statement that notifies the Oracle database to CREATE a stored PROCEDURE called skeleton. If it exists, it will be overwritten;
Row 2:
The IS keyword indicates that a PL/SQL body will be followed.
Row 3:
The BEGIN keyword indicates the start of the PL/SQL body.
Row 4:
The null pl/SQL statement indicates that nothing is done. This statement cannot be deleted because at least one sentence is required in the PL/SQL body;
Row 5:
The END keyword indicates the END of the PL/SQL body.

For example:
Create or replace procedure pCountUsers (id integer, name varchar2)
As
Begin
Insert into users (id, name) values (id, name );
End;

Call: call pCountUsers (2, 'kk ');



Differences between functions and stored procedures:
1. The difference between return values. A function has one return value, while a stored procedure returns a value through a parameter. There may be multiple or none
2. Differences in calling. A function can be called directly in a query statement, and a stored procedure must be called independently.
A function is generally used to calculate and return a computing result, while a stored procedure is generally used to perform specific data operations (such as modifying, inserting a database table, or executing some DDL statements)

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.