oracle table creation stored procedure

Oracle is a very popular relational database management system. In Oracle, creating database tables is a very important task. At the same time, stored procedures are also an important part of database development. This article will introduce in detail how Oracle creates tables and stored procedures, and give practical examples.

1. Oracle table creation

In Oracle, the syntax for creating a table is: CREATE TABLE table_name (column_name1 datatype1 [NULL | NOT NULL], column_name2 datatype2 [NULL | NOT NULL], ...);

Among them, table_name is the name of the table to be created, column_name and datatype are keywords that define the name and data type of each column in the table.

For example, if we want to create a student table named students and include four columns: id, name, age and gender, we can use the following command:

CREATE TABLE students (id NUMBER( 4) NOT NULL, name VARCHAR2(20) NOT NULL, age NUMBER(2), gender VARCHAR2(8));

Among them, the id column is defined as NUMBER type, the length is 4, and cannot be empty; The name column is defined as VARCHAR2 type, the length is 20, and cannot be empty; the age column is defined as NUMBER type, the length is 2, and can be empty; the gender column is defined as VARCHAR2 type, the length is 8, and can be empty.

2. Oracle Stored Procedure

Stored procedure is a commonly used programmed operation in Oracle. It is a combination of logical statements and can receive parameters and return values. Stored procedures can be called by multiple programs or applications, and can enhance data security and data processing efficiency.

The syntax format of the stored procedure is:

CREATE [OR REPLACE] PROCEDURE procedure_name [(parameter_name [IN | OUT | IN OUT] datatype [, ...])]IS
DECLARE
--Define variables
BEGIN
--Stored procedure body
EXCEPTION
--Exception handling
END [procedure_name];

Among them, procedure_name is the storage The process name, parameter_name is the input or output parameter of the stored procedure, and datatype is the data type of the parameter.

The stored procedure can include variables, control structures, loops, exception handling, etc., and can also call other stored procedures and functions.

An example is given below to demonstrate how to create a simple stored procedure:

CREATE OR REPLACE PROCEDURE get_student_info (p_id IN NUMBER, p_name OUT VARCHAR2, p_age OUT NUMBER, p_gender OUT VARCHAR2)IS
BEGIN
SELECT name, age, gender
INTO p_name, p_age, p_gender
FROM students
WHERE id = p_id;
END get_student_info;

In the above storage In the process, p_id is the input parameter, and p_name, p_age and p_gender are the output parameters. When the stored procedure is called, it will return the student information of the specified id.

Summary

Through the introduction of this article, we have learned the syntax and usage of Oracle's table creation and stored procedures. For database managers and developers, establishing data tables and stored procedures is a very important task. In future database applications, we need to further study and master other functions and applications of Oracle database.

The above is the detailed content of oracle table creation stored procedure. For more information, please follow other related articles on the PHP Chinese website!