云数据库 GaussDB-动态调用匿名块:示例

时间：2025-02-12 15:08:13

云数据库 GaussDB

示例

gaussdb=# DROP SCHEMA IF EXISTS hr CASCADE;gaussdb=# CREATE SCHEMA hr;CREATE SCHEMAgaussdb=# SET CURRENT_SCHEMA = hr;SETgaussdb=# CREATE TABLE staffs (  staff_id NUMBER,   first_name VARCHAR2,  salary NUMBER);CREATE TABLEgaussdb=# INSERT INTO staffs VALUES (200, 'mike', 5800);INSERT 0 1gaussdb=# INSERT INTO staffs VALUES (201, 'lily', 3000);INSERT 0 1gaussdb=# INSERT INTO staffs VALUES (202, 'john', 4400);INSERT 0 1--创建重载函数gaussdb=# CREATE OR REPLACE PACKAGE pkg1 IS   PROCEDURE plus(var1 in int, var2 int, var3 out int);   PROCEDURE plus(var1 in out int);END pkg1;/CREATE PACKAGEgaussdb=# CREATE OR REPLACE PACKAGE BODY pkg1 IS   PROCEDURE plus(var1 in int, var2 int, var3 out int)    AS   BEGIN       var3 = var1 + var2 + 1;   END;   PROCEDURE plus(var1 in out int)    AS    BEGIN      var1 = var1 + 1;   END;END pkg1;/CREATE PACKAGE BODY--创建存储过程dynamic_procgaussdb=# CREATE OR REPLACE PROCEDURE dynamic_procAS   staff_id     NUMBER(6) := 200;   first_name   VARCHAR2(20);   salary       NUMBER(8,2);BEGIN    --执行匿名块     EXECUTE IMMEDIATE 'begin select first_name, salary into :first_name, :salary from hr.staffs where staff_id= :dno; end;'       USING OUT first_name, OUT salary, IN staff_id;   dbe_output.print_line(first_name|| ' ' || salary);END;/CREATE PROCEDURE--创建存储过程调用重载函数gaussdb=# CREATE OR REPLACE PROCEDURE dynamic_proc1 AS     v_sql  VARCHAR2(200);     var1   NUMBER(6) := 1;     var2   NUMBER(6) := 2;     var3   NUMBER(6);BEGIN     v_sql := 'begin pkg1.plus(:1, :2, :3); end;';     EXECUTE IMMEDIATE v_sql USING var1, var2, out var3;     dbe_output.print_line('var3: ' || var3);END;/CREATE PROCEDURE--调用存储过程gaussdb=# CALL dynamic_proc();mike 5800.00 dynamic_proc --------------(1 row)gaussdb=# CALL dynamic_proc1();var3: 4 dynamic_proc1 ---------------(1 row)--删除存储过程gaussdb=# DROP PROCEDURE dynamic_proc;DROP PROCEDUREgaussdb=# DROP PROCEDURE dynamic_proc1;DROP PROCEDURE--开启dynamic_sql_check时报错示例gaussdb=# SET behavior_compat_options = 'dynamic_sql_check';SETgaussdb=# CREATE OR REPLACE PROCEDURE test_proc_exception001(a out integer, b inout integer, c integer) as BEGIN a := 1;  begin b := 1/0; end; EXCEPTION WHEN others THEN  b := 2; END;/CREATE PROCEDUREgaussdb=# DECLARE  a integer := 1;  c integer; BEGIN  execute immediate 'begin test_proc_exception001(:1,:2,:1); end;' using in out a, out c, a; END;/ERROR:  argnum not match in Dynamic SQL, using args num : 3 , actual sql args num : 2CONTEXT:  PL/pgSQL function inline_code_block line 4 at EXECUTE statement--修改同名占位符gaussdb=# DECLARE  a integer := 1;  c integer; BEGIN  execute immediate 'begin test_proc_exception001(:1,:2,:3); end;' using in out a, out c, a; END;/ANONYMOUS BLOCK EXECUTEgaussdb=# DROP PROCEDURE test_proc_exception001;DROP PROCEDURE