What are the usages of oracle declare

oracle declare usage includes variable declaration, constant declaration, cursor declaration and subroutine declaration. Detailed introduction: 1. Variable declaration, in PL/SQL block, you can use the DECLARE statement to declare variables; 2. Constants are unchangeable values ​​declared in PL/SQL block; 3. Cursor declaration, used in PL/SQL The query result set is processed in the block; 4. Subroutine declaration. A subroutine is a reusable code block defined in a PL/SQL block.

Oracle The DECLARE statement is a keyword used to declare variables, constants, cursors and subroutines in PL/SQL blocks. The DECLARE statement is usually used at the beginning of a PL/SQL block to define the variables and objects that need to be used. In this article, I will introduce Oracle The usage of DECLARE statement includes variable declaration, constant declaration, cursor declaration and subroutine declaration.

1. Variable declaration:

In the PL/SQL block, you can use the DECLARE statement to declare variables. Variables can be of any valid data type, such as integers, characters, dates, etc. The following is an example:

DECLARE
emp_name VARCHAR2(100);
emp_id NUMBER(10);
BEGIN
-- 在这里使用变量
emp_name := 'John Doe';
emp_id := 1001;
END;

In the above example, we declared two variables emp_name and emp_id, which are VARCHAR2 and NUMBER types respectively. We can then use these variables between BEGIN and END.

2. Constant declaration:

Constant is an unchangeable value declared in a PL/SQL block. Constants are declared using the CONSTANT keyword and must be initialized at the time of declaration. Here is an example:

DECLARE
pi CONSTANT NUMBER := 3.14159;
emp_name CONSTANT VARCHAR2(100) := 'John Doe';
BEGIN
-- 在这里使用常量
DBMS_OUTPUT.PUT_LINE('Pi value is: ' || pi);
DBMS_OUTPUT.PUT_LINE('Employee name is: ' || emp_name);
END;

In the above example, we declared two constants pi and emp_name and initialized them to 3.14159 and 'John respectively Doe'. We can then use these constants between BEGIN and END.

3. Cursor declaration:

Cursors are used to process query result sets in PL/SQL blocks. The cursor declaration uses the CURSOR keyword and the query statement must be specified. Here is an example:

DECLARE
CURSOR emp_cursor IS
SELECT emp_name, emp_id FROM employees;
emp_rec emp_cursor%ROWTYPE;
BEGIN
-- 在这里使用游标
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO emp_rec;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee name is: ' || emp_rec.emp_name);
DBMS_OUTPUT.PUT_LINE('Employee ID is: ' || emp_rec.emp_id);
END LOOP;
CLOSE emp_cursor;
END;

In the above example, we have declared a cursor emp_cursor which selects the emp_name and emp_id columns from the employees table. Then we use the OPEN statement to open the cursor, the LOOP statement to traverse the result set, and the FETCH statement to assign the rows in the result set to the emp_rec variable. Finally, we close the cursor using the CLOSE statement.

4. Subprogram declaration:

A subprogram is a reusable code block defined in a PL/SQL block. Subroutines can be functions or procedures. Functions return a value, whereas procedures return no value. Here is an example:

DECLARE
FUNCTION calculate_salary(emp_id NUMBER) RETURN NUMBER IS
salary NUMBER;
BEGIN
-- 在这里计算薪水
SELECT emp_salary INTO salary FROM employees WHERE emp_id = emp_id;
RETURN salary;
END;
PROCEDURE display_salary(emp_id NUMBER) IS
salary NUMBER;
BEGIN
-- 在这里显示薪水
salary := calculate_salary(emp_id);
DBMS_OUTPUT.PUT_LINE('Employee salary is: ' || salary);
END;
BEGIN
-- 在这里调用子程序
display_salary(1001);
END;

In the above example, we declared a function calculate_salary and a procedure display_salary. The function calculate_salary accepts an emp_id parameter and returns a salary value. The procedure display_salary accepts an emp_id parameter and calls the calculate_salary function to calculate the salary and display the results.

Summary:

Oracle The DECLARE statement is used to declare variables, constants, cursors and subroutines in PL/SQL blocks. Variables and constants can be of any valid data type, cursors are used to process query result sets, and subroutines are reusable blocks of code. By using the DECLARE statement, we can define and use these objects in PL/SQL blocks to achieve more flexible and maintainable code .

The above is the detailed content of What are the usages of oracle declare. For more information, please follow other related articles on the PHP Chinese website!