虚谷文档 v1.0.1

适用虚谷数据库版本

v12.9



适用虚谷数据库版本

v12.9


SQL执行 ​

📄字数 3.6K
👁️阅读量 加载中...

在 PL/SQL 中，SQL 执行模块是指用于执行 SQL 语句的代码部分，位于 BEGIN 和 END 之间，主要包含数据操作（DML）、数据定义（DDL）、事务控制、查询操作、批量操作等。这些操作可以直接嵌入 PL/SQL 代码中，或通过动态 SQL（绑定变量） 执行。

语法格式 ​

(RecyleStmt::=, ProcDef::=, OptExceptionStmt::=, PipeRowStmt::=)

  • sql_stmt：数据库语法支持的 sql 语句，包含数据操作（DML）、数据定义（DDL）、事务控制、查询操作、批量操作等。

  • StmtBlock：块语句，即BEGIN 和 END 之间还可进行嵌套。

  • NULL：空语句，即不做任何操作。

  • 事务控制的语法可参考 TCL 文档。

  • 匿名事务：匿名事务，又称自治事务，是独立的实体，它不与主事务共享锁、资源或依赖等，可用于子程序、对象的方法等。匿名事务可以进行 SQL 操作，并提交或回滚这些操作，同时不影响主事务中的提交和数据。例如，主事务中发生事务回滚，从匿名事务之后的某处回滚至匿名事务之前的保存点，其中主事务中的操作被回滚，但匿名事务中的操作不会被回滚。

    • 匿名事务标识：AUTONOMOUS_TRANSACTION

注意

上述 pl_stmt 并未完全展示其子句，属于 pl_stmt 的其余子句如流程控制、循环、跳转、赋值、输出等有单独页面介绍，可在 plsql 其他文档查看
sql_stmt 的子句并未在此处展开，需要了解子句语法，可以阅读对应子句如 DMl、DDL、查询等模块的文档
事务控制中的匿名事务参考本文档示例章节->事务控制
PipeRowStmt只在存储函数指定关键字PIPELINED关键字时使用，详细介绍请参阅PIPELINED函数。

示例 ​

  • 示例1

    执行 dml

    sql
    SQL> BEGIN
     INSERT INTO test_plsql_tab values(1);
     INSERT INTO test_plsql_tab values(2);
     END;
     / 

SQL> BEGIN
     SELECT * FROM test_plsql_tab;
     END;
     /


SQL> SELECT * FROM test_plsql_tab;

+----+
| ID |
+----+
| 1  |
| 2  |
+----+



SQL> BEGIN
     UPDATE test_plsql_tab SET id = 3 WHERE id = 2;
     END;
     /

SQL> SELECT * FROM test_plsql_tab;

+----+
| ID |
+----+
| 1  |
| 3  |
+----+

SQL> BEGIN
     DELETE FROM test_plsql_tab WHERE id = 3; 
     END;
     /

SQL> SELECT * FROM test_plsql_tab;

+----+
| ID |
+----+
| 1  |
+----+

  • 示例2

    执行 ddl

    sql
    
SQL> BEGIN
   CREATE TABLE test_pqsql_tab2(id int);
   END;
   /


SQL> SELECT table_name FROM sys_tables WHERE table_name = 'test_pqsql_tab2';

+-----------------+
|   TABLE_NAME    |
+-----------------+
| TEST_PQSQL_TAB2 |
+-----------------+


SQL> BEGIN
   DROP TABLE test_pqsql_tab2;
   END;
   /


SQL> SELECT table_name FROM sys_tables WHERE table_name = 'test_pqsql_tab2';

+------------+
| TABLE_NAME |
+------------+
+------------+

  • 示例3

    事务控制

    sql
    
SQL> SET auto_commit OFF;


SQL> CREATE TABLE test_plsql_trans(id int);

-- begin，开启事务,可以写成 BEGIN、START，后面也可跟 TRAN、WORK、TRANSACTION
SQL> BEGIN 
   BEGIN TRANSACTION;
   INSERT INTO test_plsql_trans values(1);
   END;
   /


SQL> SELECT * FROM test_plsql_trans;

+----+
| ID |
+----+
| 1  |
+----+


SQL> show auto_commit

+-------------+
| AUTO_COMMIT |
+-------------+
| F           |
+-------------+

SQL> CREATE TABLE test_plsql_trans2(id int);


SQL> BEGIN 
   START TRANSACTION;
   INSERT INTO test_plsql_trans2 values(1);
   END;
   /


SQL> SELECT * FROM test_plsql_trans2;

+----+
| ID |
+----+
| 1  |
+----+


SQL> show auto_commit

+-------------+
| AUTO_COMMIT |
+-------------+
| F           |
+-------------+


SQL> ROLLBACK TRAN;


SQL> SELECT * FROM test_plsql_trans;

+----+
| ID |
+----+
+----+


-- commit,commit 提交事务之后的操作回滚不受影响，
SQL> BEGIN 
   INSERT INTO test_plsql_trans values(1);
   COMMIT;
   END;
   /


SQL> SELECT * FROM test_plsql_trans;

+----+
| ID |
+----+
| 1  |
+----+



SQL> rollback;


SQL> SELECT * FROM test_plsql_trans;

+----+
| ID |
+----+
| 1  |
+----+


-- rollback，回滚操作，可以写成 ROLLBACK、ABORT、后面也可跟 TRAN、WORK、TRANSACTION
SQL> BEGIN 
   INSERT INTO test_plsql_trans values(2);
   COMMIT;
   INSERT INTO test_plsql_trans values(3);
   ROLLBACK;
   END;
   /

SQL> SELECT * FROM test_plsql_trans;

+----+
| ID |
+----+
| 1  |
| 2  |
+----+

-- savepoint，设置保存点,可以写成 SAVEPOINT
SQL> BEGIN 
   INSERT INTO test_plsql_trans values(4);
   SAVEPOINT sp1;
   INSERT INTO test_plsql_trans values(5);
   SAVEPOINT sp2;
   END;
   /


SQL> SELECT * FROM test_plsql_trans;

+----+
| ID |
+----+
| 1  |
| 2  |
| 4  |
| 5  |
+----+

SQL> ROLLBACK TO sp2;


SQL> SELECT * FROM test_plsql_trans;

+----+
| ID |
+----+
| 1  |
| 2  |
| 4  |
| 5  |
+----+


SQL> ROLLBACK TO sp1;

SQL> SELECT * FROM test_plsql_trans;

+----+
| ID |
+----+
| 1  |
| 2  |
| 4  |
+----+

-- release savepoint 删除保存点
SQL> BEGIN
   RELEASE SAVEPOINT sp2;
   RELEASE SAVEPOINT sp1;
   COMMIT;
   END;
   /

-- 匿名事务
SQL> CREATE TABLE tab_person(name VARCHAR(10), age INT);


SQL> CREATE PROCEDURE proc_anonymous_insert() as
       PRAGMA AUTONOMOUS_TRANSACTION;
   BEGIN
       INSERT INTO tab_person VALUES('Bob', 20);
       COMMIT;
   END;
   /

-- 主事务
SQL> BEGIN
       -- 主事务中的此插入将会被回滚
       INSERT INTO tab_person VALUES('Alice', 10);
       -- 匿名事务中的语句不会被回滚，其中提交也不会影响主事务中插入
       proc_anonymous_insert();
       ROLLBACK;
   END;
   /

SQL> SELECT * FROM tab_person;

+------+-----+
| NAME | AGE |
+------+-----+
| Bob  | 20  |
+------+-----+

  • 示例4

    查询操作

    sql
    
SQL> BEGIN
   SELECT * FROM test_plsql_trans;
   END;
   /

  • 示例5

    批量操作

    sql
    -- forall
SQL> declare
   var_1 varchar2(20);
   t1 bigint;
   t2 bigint;
   t3 bigint;
   type type_table is table of varchar2(20);
   v_tab type_table;
   begin
   v_tab := type_table();
   v_tab.extend(1000000);
   for i in 1..1000000 loop
   v_tab(i) := 'item_' || i;
   end loop;
   
   t1 := DBMS_UTILITY.GET_TIME();
   var_1 := 'loop';
   --循环操作
   FOR i IN 1..500000 LOOP
   insert into t_msg values(var_1, v_tab(i));
   END LOOP;
   
   t2 := DBMS_UTILITY.GET_TIME();
   
   var_1 := 'forall';
   --批量操作
   forall i in indices of v_tab between 500001 and 1000000
   insert into t_msg values(var_1, v_tab(i));
   
   t3 := DBMS_UTILITY.GET_TIME();
   
   end;
   /


SQL> select count(*) from t_msg;

+---------+
|  EXPR1  |
+---------+
| 1100000 |
+---------+

  • 示例6

    备份恢复操作

    sql
    --备份
SQL> BEGIN
   BACKUP TABLE test_plsql_trans TO '/backup/tab.exp';
   END;
   /


SQL> DROP TABLE test_plsql_trans;

--恢复
SQL> BEGIN
   restore TABLE test_plsql_trans from '/backup/tab.exp';
   END;
   /



SQL> SELECT * FROM test_plsql_trans;

+----+
| ID |
+----+
| 1  |
| 2  |
+----+

  • 示例7

    存储过程/函数

    sql
    
SQL> DECLARE
   a int;
   b varchar;
   c DATETIME;
   BEGIN
   PROCEDURE pro2 IS
   BEGIN
   a:=123;
   b:='abc';
   c:='2020-08-09 20:20:20';
   
   END pro2;
   EXEC pro2;
   send_msg(a||','||b||','||c);
   END;
   /
123,abc,2020-08-09 20:20:20

  • 示例8

    块语句

    sql
    
SQL> BEGIN
   CREATE TABLE test_block_tab(id int);
   BEGIN
   INSERT INTO test_block_tab values(1);
   END;
   
   END;
   /


SQL> SELECT * FROM test_block_tab;

+----+
| ID |
+----+
| 1  |
+----+