pgsql存储过程if

### PostgreSQL 存储过程的创建与使用 #### 创建存储过程 在 PostgreSQL 中，可以通过 `CREATE OR REPLACE PROCEDURE` 来定义存储过程。以下是其基本语法： ```sql CREATE OR REPLACE PROCEDURE procedure_name ( [IN | OUT | INOUT] parameter_name data_type, ... ) AS $$ BEGIN -- 存储过程逻辑 END; $$ LANGUAGE plpgsql; ``` 上述语法规则涵盖了存储过程的核心部分[^2]。其中： - **参数模式**：可以指定为 `IN`, `OUT`, 或 `INOUT`。 - `IN`: 输入参数，默认值。 - `OUT`: 输出参数，通常用于返回单个或多个结果。 - `INOUT`: 同时作为输入和输出参数。 - **数据类型**：支持多种标准 SQL 数据类型。 #### 示例：带返回值的存储过程 下面是一个简单的例子，展示如何创建一个带有返回值的存储过程，并通过调用来获取结果。 ##### 创建存储过程 ```sql CREATE OR REPLACE PROCEDURE calculate_sum(IN num1 integer, IN num2 integer, OUT result integer) AS $$ BEGIN result := num1 + num2; END; $$ LANGUAGE plpgsql; ``` 此存储过程中，`num1` 和 `num2` 是输入参数，而 `result` 则是输出参数，表示两数之和的结果[^3]。 ##### 调用存储过程 要执行该存储过程，可以使用以下命令： ```sql CALL calculate_sum(5, 7, result); SELECT result; ``` 这会将 `5` 和 `7` 的和赋给变量 `result` 并显示出来。 #### 示例：带条件逻辑的存储过程 另一个更复杂的场景涉及更新数据库中的某些字段。例如，基于员工 ID 修改薪资水平。 ##### 创建存储过程 ```sql CREATE OR REPLACE PROCEDURE update_salary( IN employee_id integer, IN percentage_increase numeric ) AS $$ DECLARE current_salary NUMERIC; BEGIN SELECT salary INTO current_salary FROM employees WHERE id = employee_id; IF NOT FOUND THEN RAISE EXCEPTION 'Employee with ID % not found', employee_id; ELSE UPDATE employees SET salary = current_salary * (1 + percentage_increase / 100) WHERE id = employee_id; END IF; END; $$ LANGUAGE plpgsql; ``` 在此示例中，如果未找到对应的员工记录，则抛出异常；否则按照百分比调整工资。 ##### 调用存储过程 ```sql CALL update_salary(101, 10); ``` 这条指令将会把编号为 `101` 的员工薪水增加 10%。 #### 动态SQL操作 对于一些动态需求（比如批量新增列），也可以利用存储过程完成复杂任务。如下所示的是一个函数实现的例子，它允许向任意表一次性添加多列[^4]: ```sql CREATE OR REPLACE FUNCTION pg_addcolumn(_columns TEXT[], _table_name VARCHAR) RETURNS INT AS $BODY$ DECLARE col_count INTEGER; sql_stmt VARCHAR; BEGIN FOREACH col_text IN ARRAY _columns LOOP sql_stmt := format('ALTER TABLE %I ADD COLUMN %I VARCHAR DEFAULT NULL;', _table_name, col_text); EXECUTE sql_stmt; END LOOP; RETURN array_length(_columns, 1); END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; ``` 调用方式如下： ```sql SELECT public.pg_addcolumn(array['col_a','col_b'], 'my_table'); ``` 以上展示了如何灵活运用存储过程解决实际问题。 --- ###

### 如何在 PostgreSQL (pgSQL) 中调用存储过程 #### 创建存储过程 为了能够在 PostgreSQL 数据库中调用存储过程，首先需要定义这些存储过程。这通常通过 `CREATE OR REPLACE FUNCTION` 或者更具体的 `CREATE PROCEDURE` 来完成。 ```sql -- 定义一个简单的存储过程，它接受两个整数作为输入参数，并返回这两个数字之和。 CREATE OR REPLACE PROCEDURE add_two_numbers(IN num1 integer, IN num2 integer, OUT result integer) LANGUAGE SQL AS $$ SELECT num1 + num2 INTO result; $$; ``` 此段代码创建了一个名为 `add_two_numbers` 的存储过程，用于接收两个整型数值并输出其总和[^3]。 #### 使用 JDBC 进行调用 当涉及到 Java 应用程序时，可以借助于 JDBC API 提供的功能来执行对 PostgreSQL 存储过程的调用： ```java // 加载驱动类 Class.forName("org.postgresql.Driver"); // 获取数据库连接对象 Connection conn = DriverManager.getConnection(url, user, password); // 准备 CallableStatement 对象以调用带有三个参数（两个输入和一个输出）的过程 CallableStatement callableStmt = conn.prepareCall("{ call add_two_numbers(?, ?, ?) }"); // 设置输入参数 callableStmt.setInt(1, 5); // 输入第一个整数 callableStmt.setInt(2, 7); // 输入第二个整数 // 注册第三个参数为 OUTPUT 类型 callableStmt.registerOutParameter(3, Types.INTEGER); // 执行 CALL 操作 callableStmt.execute(); // 取得结果 int sumResult = callableStmt.getInt(3); System.out.println("Sum of numbers is " + sumResult); ``` 上述例子展示了如何使用 JDBC 在应用程序端准备并发送请求去调用 PostgreSQL 上面已经存在的存储过程 `add_two_numbers`，同时处理它的输出参数[^5]。 #### 处理带条件逻辑的存储过程 对于更加复杂的场景，比如基于某些业务规则更新数据表中的记录，则可以在存储过程中加入控制结构如 IF...THEN...ELSE 和循环等。下面是一个简化版的例子，展示如果根据员工ID调整工资的情况下的存储过程设计思路[^4]: ```sql CREATE OR REPLACE PROCEDURE update_salary_based_on_id( employeeId INT, percentageIncrease DECIMAL ) LANGUAGE plpgsql AS $$ BEGIN UPDATE employees SET salary = salary * (1 + percentageIncrease / 100.0) WHERE id = employeeId; -- 如果影响行数大于零则打印成功消息；否则提示未找到对应员工 GET DIAGNOSTICS INTEGER ROW_COUNT = ROW_COUNT; IF ROW_COUNT > 0 THEN RAISE NOTICE 'Salary updated successfully'; ELSE RAISE WARNING 'No such employee found with ID %', employeeId; END IF; END $$; ``` 这段脚本不仅实现了基本的数据修改操作，还包含了错误检测机制以及相应的反馈信息生成能力。