How Oracle uses PL/SQL

PL/SQL is the programming language of Oracle database and can be used to create stored procedures, triggers, functions and packages, etc. This article will introduce how Oracle uses PL/SQL.

1. Create a stored procedure

A stored procedure is a collection of SQL statements. A stored procedure can be regarded as a custom function. Using stored procedures can simplify complex SQL queries and improve database performance.

In Oracle, the syntax for creating a stored procedure is as follows:

CREATE OR REPLACE PROCEDURE procedure_name
(parameter1 datatype, parameter2 datatype,....)
IS
BEGIN
   statement1;
   statement2;
   ...
END;
/

where CREATE OR REPLACE PROCEDURE represents creating or replacing a stored procedure, procedure_name represents the name of the stored procedure, parameter1 and parameter2 are input parameters, datatype indicates the data type of the parameter. Between IS and BEGIN is the main part of the stored procedure, which contains a series of SQL statements. END indicates the end of the stored procedure.

The following is a simple stored procedure example for calculating the sum of two numbers:

CREATE OR REPLACE PROCEDURE addition
(num1 IN NUMBER, num2 IN NUMBER, sum OUT NUMBER)
IS
BEGIN
  sum := num1 + num2;
END;
/

In the above example, addition is the name of the stored procedure, and num1 and num2 are the two inputs Parameter, sum is an output parameter. In the main part of the stored procedure, use the sum := num1 num2; statement to calculate the sum of two numbers and assign the result to the output parameter sum.

2. Create a trigger

A trigger is a program that is automatically executed in the database and can be used when various operations in the database (such as inserting, updating, or deleting data) occur. trigger event. Triggers are often used for data auditing and logging.

In Oracle, the syntax for creating a trigger is as follows:

CREATE OR REPLACE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE
ON table_name
FOR EACH ROW
BEGIN
   statement1;
   statement2;
   ...
END;
/

Among them, CREATE OR REPLACE TRIGGER represents creating or replacing a trigger, trigger_name represents the name of the trigger, and BEFORE/AFTER represents the triggering time. , INSERT/UPDATE/DELETE indicates the operation of triggering the character, table_name indicates the table corresponding to the trigger, and FOR EACH ROW indicates that the statements in the trigger are executed for each row of data.

The following is a simple trigger example for recording the insertion time when data is inserted:

CREATE OR REPLACE TRIGGER insert_time
BEFORE INSERT
ON customer
FOR EACH ROW
BEGIN
  :new.created_at := sysdate;
END;
/

In the above example, insert_time is the name of the trigger, and BEFORE INSERT means that when data is inserted Before executing the trigger, customer is the table name corresponding to the trigger. In the main part of the trigger, use: new.created_at := sysdate; statement to assign the current time to the created_at field of the inserted data.

3. Create a function

A function is a PL/SQL program unit with a return value that can accept zero or more parameters as input and return a value as output. Using functions can encapsulate a piece of logic, making the code more readable and maintainable.

In Oracle, the syntax for creating a function is as follows:

CREATE OR REPLACE FUNCTION function_name
(return_type IN OUT datatype,
parameter1 datatype, parameter2 datatype,....)
RETURN return_datatype
IS
BEGIN
  statement1;
  statement2;
  ...
  RETURN return_value;
END;
/

Among them, CREATE OR REPLACE FUNCTION means creating or replacing a function, function_name means the name of the function, return_type means the type of the return value, parameter1, parameter2 is the input parameter, datatype indicates the data type of the parameter, and RETURN return_datatype indicates the data type of the return value. In the body of the function, use the RETURN return_value statement to return the calculation result to the caller.

The following is a simple function example for calculating the product of two numbers:

CREATE OR REPLACE FUNCTION multiplication(x IN NUMBER, y IN NUMBER)
RETURN NUMBER
IS
BEGIN
  RETURN x * y;
END;
/

In the above example, multiplication is the name of the function, x and y are the two input parameters, RETURN NUMBER means that the data type of the return value is NUMBER, and the return value is the result of x*y.

4. Create a package

A package is a program module that stores a set of PL/SQL program units. It can encapsulate related program units such as functions, stored procedures, variables and constants into In one package. Using packages can make your program more readable and maintainable.

In Oracle, the syntax for creating a package is as follows:

CREATE OR REPLACE PACKAGE package_name
IS
  /* 声明变量、常量和异常 */
  ...
  /* 声明存储过程和函数 */
  PROCEDURE procedure_name;
  FUNCTION function_name RETURN NUMBER;
END;
/

CREATE OR REPLACE PACKAGE BODY package_name
IS
  /* 实现存储过程和函数 */
  PROCEDURE procedure_name
  IS
  BEGIN
    /* 存储过程的逻辑处理 */
  END;
  FUNCTION function_name
  RETURN NUMBER
  IS
  BEGIN
    /* 函数的逻辑处理 */
    RETURN 0;
  END;
END;
/

Among them, CREATE OR REPLACE PACKAGE means creating or replacing a package, package_name means the name of the package, IS means the declaration part of the package, and END means The end of the package, CREATE OR REPLACE PACKAGE BODY means creating or replacing the package body, package_name means the name of the package.

The above is a brief introduction to how Oracle uses PL/SQL. Through PL/SQL, you can create program units such as stored procedures, triggers, functions, and packages to implement database operations and logical processing.

The above is the detailed content of How Oracle uses PL/SQL. For more information, please follow other related articles on the PHP Chinese website!