One of the subroutines in Oracle stored procedures

Subroutines are named Pl/sql blocks that can accept parameters and are invoked by other programs. There are two types of pl/sql subroutines, procedures, and functions. In layman's terms, a procedure is used to perform an action, and a function is used to return a result value. Similar to the anonymous Pl/sql block, subroutines have declarations, the execution section, and an optional exception-handling section that contains declarations of types, cursors, constants, variables, exceptions, and nested subroutines. These are valid for the current subroutine, meaning that the content declared when the subroutine exits is automatically invalidated. The execution section contains assignment statements, Process Control statements, and Oracle data manipulation statements. The Exception handling section contains exception handlers.

First, process (stored procedure)

A procedure is a subroutine that can perform a specific action. Create or replace a subroutine saved in the database by using Create or replace.

Example 1: Declaring a stored procedure that returns the number of Dept table rows

DECLARE
PROCEDURE getDeptCount
AS
deptCount INT;
BEGIN
SELECT COUNT(*) INTO deptCount FROM DEPT;
DBMS_OUTPUT.PUT_LINE('DEPT 表的共有记录数：'||deptCount);
END getDeptCount;
BEGIN
getDeptCount [()];
END;

Note: This stored procedure getdeptcount only valid when the block is running.

Example 2: Create a stored procedure with no parameters, which returns the number of Dept table rows

CREATE OR REPLACE PROCEDURE getDeptCount
AS | IS
deptCount int;
BEGIN
SELECT COUNT(*) INTO deptCount FROM dept;
DBMS_OUTPUT.PUT_LINE('dept表共有'||deptCount||'行记录 ');
END [getDeptCount];

When we create a stored procedure that has no parameters, there is no parentheses behind the stored procedure name. The declarations in the stored procedure do not use the DECLARE keyword before the As or is post to begin is the declaration part. As with anonymous pl/sql blocks, the exception and declaration portions are optional.

When we create a process with errors, we can view it through the SELECT * from user_errors or use show ERRORS [PROCEDURE Proc_name].

Use the following code to execute a stored procedure:

BEGIN
getDeptCount;
END;

The above stored procedure can also simplify the call with the following code:

EXEC getDeptCount[;]　　　
CALL　 getDeptCount();

Attention:

Not all stored procedures can be invoked in this way

When defining a parameterless stored procedure, the stored procedure name cannot be added ()

Can be omitted in a block or by calling a stored procedure through exec ()

Calling a parameterless stored procedure via call must be added ()

Example 3: Create a stored procedure with an input parameter that prints the amount of the payroll through the employee number

CREATE OR REPLACE PROCEDURE getSalaryByEmpNo(eNo NUMBER)　 --参数的数据类型不能指定长度
AS
salary emp.sal%TYPE;
BEGIN
SELECT SAL INTO salary　 FROM EMP WHERE EMPNO=eNo;
DBMS_OUTPUT.PUT_LINE(eNo||'号员工的工资为'||salary);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('没有找到该编号的员工');
END;