CREATE FUNCTION autonomous_test() RETURNS integerLANGUAGE plpgsql AS$$ DECLARE -- 定义使用自治事务PRAGMA AUTONOMOUS_TRANSACTION; BEGIN FOR i IN 0..9LOOP -- 启动自治事务 START TRANSACTION; -- 目前仅支持动态SQL EXECUTE 'INSERT INTO test1 VALUES (' || i::text || ')'; IF i % 2 = 0 THE...
postgresql随机字符串,本篇主要内容如下:6.1 引言6.2 创建函数6.3 存储过程6.3.1 创建过程6.3.2 调用存储过程6.3.3 AUTHID6.3.4 PRAGMAAUTONOMOUS_TRANSACTION6.3.5 开发存储过程步骤6.3.6 删除过程和函数6.3.7 过程与
-- create an autonomous trigger that inserts into the -- shadow table before each insert into the main table CREATE TRIGGER parts_trig BEFORE INSERT ON parts FOR EACH ROW DECLARE PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO parts_log VALUES (:NEW .pnum, :NEW .pname); COMMIT ; END ;-...
PRAGMA AUTONOMOUS_TRANSACTION; BEGIN EXECUTE IMMEDIATE:new.cmd; COMMIT; END; 注意1:上面建的命令表是临时表,因为我们只需要执行触发器,而不需要保存执行的SQL 注意2: 如果想在触发器中执行DDL,需要在自治事务中:PRAGMA AUTONOMOUS_TRANSACTION 注意3:因为是在存储过程中执行DDL,需要显式的对用户赋DDL权限,如: ...
1. src/pl/plpgsql/src/expected/plpgsql_autonomous.out CREATE TABLE test1 (a int); CREATE FUNCTION autonomous_test() RETURNS integer LANGUAGE plpgsql AS $$ DECLARE PRAGMA AUTONOMOUS_TRANSACTION; BEGIN FOR i IN 0..9 LOOP START TRANSACTION; EXECUTE 'INSERT INTO test1 VALUES (' || i::text |...
存储过程支持自治事务 自治事务可以在存储过程中定义,标识符为PRAGMA AUTONOMOUS_TRANSACTION,其余语法与创建存储过程语法相同,示例如下。 --建表 create table t2(a int, b int); insert into t2 values(1 来自:帮助中心 查看更多 → GaussDB(DWS)存储过程声明语法 ...
BULK COLLECT/BIND ASSOCIATIVE ARRAYS NESTED TABLES VARRAYS PL/SQL SUPPLIED PACKAGES PRAGMA EXCEPTION_INIT PRAGMA AUTONOMOUS_TRANSACTION USER DEFINED EXCEPTIONS OBJECT TYPES SUB-TYPES 包包内置函数 高级功能AWR使用说明 客户端驱动PolarPlus JDBC .NET ODBC OCI PHP...
BULK COLLECT/BIND ASSOCIATIVE ARRAYS NESTED TABLES VARRAYS PL/SQL SUPPLIED PACKAGES PRAGMA EXCEPTION_INIT PRAGMA AUTONOMOUS_TRANSACTION USER DEFINED EXCEPTIONS OBJECT TYPES SUB-TYPES 包包内置函数 高级功能AWR使用说明 客户端驱动PolarPlus JDBC .NET ODBC OCI PHP...
AUTONOMOUS_TRANSACTION Enable translation of autonomous transactions into a wrapper function using dblink or pg_background extension. If you don't want to use this translation and just want the function to be exported as a normal one without the pragma call, disable this directive. Materialized ...
答:对于大多数入门级的DBA通常给出的答案是否定的。...而对于大师级的数据库专家,可以给出解决方案如下:在触发器中加入自治事务处理,即:pragma autonomous_transaction便可完成此项功能需求。...Step 2.创建触发器,并调用恰才新生成的存储过程proce1即可。...(此处只举例了事后触发器,根据业务需求可自由编写...