文档中心

EXECUTE语句 ​

EXECUTE语句用于调用存储过程或包，存储过程或块语句中若包含DDL语句，需使用特定EXECUTE语句（EXECUTE IMMEDIATE）进行。

说明：

CALL语句同EXECUTE语句语法，只需替换关键字即可。

主要语法结构 ​

语法格式 ​

sql
ExecuteStmt::=
    [schema_name.] name_space [( func_params )]
|   EXECUTE [schema_name.] name_space
|   EXECUTE [schema_name.] name_space ( func_params )
|   EXECUTE IMMEDIATE b_expr [ USING ColId [,ColId]... ] [RETURNING | BULK COLLECT | RETURNING BULK COLLECT] [INTO ColId [,ColId]...] [LIMIT ICONST]

参数说明 ​

  • schema_name：模式名，可省略，跨模式执行存储过程/函数时需获取执行权限。
  • name_space：存储过程名、存储函数名、包中定义的存储过程名或存储函数名，其中如果调用包中存储过程或存储函数则需加包名，仅存储过程或函数可直接使用过程名或函数名进行调用。
  • USING：在执行动态SQL语句时对参数列表进行绑定，该参数后的参数列表与动态SQL语句中的占位符进行绑定。
  • RETURNING：将插入、更新和删除受影响的行返回，通常与INTO子句搭配使用，INTO子句保存和记录插入、更新和删除受影响的行的数据，若无INTO子句则表示仅返回受影响的行但不记录受影响行的数据，此处仅表示单个的变量或集合数据。
  • BULK COLLECT：单个集合变量或多个集合变量的多记录可使用BULK COLLECT。
  • RETURNING BULK COLLECT：可以出现在插入、更新、删除或执行即时语句中。使用该子句可将其结果集存储在一个或多个集合中。

参数信息func_params ​

语法格式 ​

sql
func_params::=
    {b_expr | param_name => b_expr}
|   func_params , {b_expr | param_name => b_expr} 
|   /*EMPTY*/

参数说明 ​

  • func_params：参数值，根据指定的参数类型进行传值，支持两种方式传值，包括直接指定参数值b_expr、参数值与形参名绑定传值param_name=>b_expr，其中param_name为形参名，同一个过程或函数参数两种方式不可混用。
  • /*EMPTY*/：空参数。

说明：

存储过程或函数参数的类型可以指定为in、out、in out三种模式。

示例 ​

动态SQL ​

定义一个test_exec表与一个execute_proc存储过程，存储过程含2个输入参数x与y，过程体定义一个string变量用于初始化记录入库语句，然后将输入参数值赋值给入库语句进行执行。通过执行存储过程，实现将参数值1与TEST插入到test_exec表，最后查询表记录确认存储过程执行正确性。

sql
-- 创建表
CREATE TABLE test_exec(id INT,name VARCHAR(20));
-- 创建存储过程
CREATE OR REPLACE PROCEDURE execute_proc(x IN INTEGER, y IN CHAR(10))
AS
    string VARCHAR2(100);
BEGIN
    string := 'INSERT INTO test_exec VALUES(?, ?);';
    EXECUTE IMMEDIATE string USING x, y;
END;
/
-- 调用存储过程
EXECUTE execute_proc(1,'TEST_EXEC');
-- 查询表
SELECT * FROM test_exec;

ID | NAME | 
------------------------------------------------------------------------------
1 | TEST_EXEC|

RETURNING INTO ​

将ID为100的员工薪水提高10%，保留旧薪水记录，使用RETURNING INTO将更新后的last_name和salary值存入emp_info记录，最后输出。

sql
-- 创建表
CREATE TABLE emp_ret (employee_id INT, last_name VARCHAR(30), salary NUMERIC(15,3));
-- 插入数据
INSERT INTO emp_ret VALUES(100,'King',31944)(101,'Kochhar',18700)(102,'De Haan',18700)(103,'Hunold',9900);

DECLARE
    -- 定义一个记录类型
    TYPE emp_rec IS RECORD (
        last_name emp_ret.last_name%TYPE,
        salary emp_ret.salary%TYPE
    );
    -- 声明一个记录类型的变量
    emp_info emp_rec;
    -- 声明一个变量来存储旧的薪水
    old_salary emp_ret.salary%TYPE;
BEGIN
    -- 查询员工ID为100的旧薪水
    SELECT salary INTO old_salary FROM emp_ret WHERE employee_id = 100;

    -- 更新员工ID为100的薪水，并返回更新后的姓名和薪水
    UPDATE emp_ret 
    SET salary = salary * 1.1 
    WHERE employee_id = 100 
    RETURNING last_name, salary INTO emp_info;

    -- 输出更新后的信息
    DBMS_OUTPUT.PUT_LINE('Salary of ' || emp_info.last_name || ' raised from ' || old_salary || ' to ' || emp_info.salary);
END;
/

-- 输出结果
Salary of King raised from 31944 to 35138.4

BULK COLLECT INTO ​

创建一个表emp_ret1，向其中插入一些数据，并查询特定条件下的所有员工信息。将这些信息通过BULK COLLECT INTO收集到一个集合类型中，并通过循环输出每个符合条件的员工的姓名和薪水。

sql
-- 创建表
CREATE TABLE emp_ret1(employee_id INT, last_name VARCHAR(30), salary NUMERIC(15,3));
-- 插入数据
INSERT INTO emp_ret1 VALUES(100, 'King', 31944)(101, 'Kochhar', 18700)(102, 'De Haan', 18700)(103, 'Hunold', 9900);

DECLARE
    -- 定义一个集合类型，元素类型为emp_ret1表的行类型
    TYPE em_tp IS TABLE OF emp_ret1%ROWTYPE;    
    -- 声明一个集合变量
    emp_tb em_tp;
    -- 定义一个字符串变量，用于存储动态SQL查询语句
    str VARCHAR(100);
BEGIN
    -- 构建动态SQL查询语句
    str := 'SELECT * FROM emp_ret1 WHERE employee_id < :id';
    -- 执行动态SQL查询，并将结果批量收集到集合中
    EXECUTE IMMEDIATE str BULK COLLECT INTO emp_tb USING 103;
    -- 遍历集合并输出每个员工的姓名和薪水
    FOR i IN 1..emp_tb.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE('Name of ' || emp_tb(i).last_name || ' salary is ' || emp_tb(i).salary);
    END LOOP;
END;
/

-- 输出结果
Name of King salary is 31944
Name of Kochhar salary is 18700
Name of De Haan salary is 18700

RETURNING BULK COLLECT INTO ​

创建一个表emp_ret2，向其中插入一些数据，并删除特定条件下的员工记录。同时使用RETURNING BULK COLLECT INTO将删除的记录信息收集到集合中，并输出删除的记录信息。

sql
-- 创建表
CREATE TABLE emp_ret2(employee_id INT, last_name VARCHAR(30), salary NUMERIC(15,3));
-- 插入数据
INSERT INTO emp_ret2 VALUES(100, 'King', 31944)(101, 'Kochhar', 18700)(102, 'De Haan', 18700)(103, 'Hunold', 9900);

DECLARE
    -- 定义集合类型
    TYPE num_list IS TABLE OF emp_ret2.employee_id%TYPE;
    TYPE num_list1 IS TABLE OF emp_ret2.last_name%TYPE;
    -- 声明集合变量
    enums num_list;
    names num_list1;
BEGIN
    -- 删除记录并收集返回值
    DELETE FROM emp_ret2 
    WHERE employee_id < 102 
    RETURNING employee_id, last_name BULK COLLECT INTO enums, names;
    -- 输出删除记录的数量
    DBMS_OUTPUT.PUT_LINE('Deleted ' || SQL%ROWCOUNT || ' rows:');
    -- 遍历集合并输出每个删除记录的信息
    FOR i IN enums.FIRST .. enums.LAST LOOP
        DBMS_OUTPUT.PUT_LINE('Employee #' || enums(i) || ': ' || names(i));
    END LOOP;
END;
/

-- 输出结果:
Deleted 2 rows:
Employee #100: King
Employee #101: Kochhar