pgsql 存储过程教程

### PgSQL 和 Oracle 数据库存储过程使用教程对比 #### 1. 创建存储过程 ##### PostgreSQL (PgSQL) 在 PostgreSQL 中，创建存储过程的方式如下： ```sql CREATE OR REPLACE PROCEDURE update_salary(employee_id INT, new_salary NUMERIC) LANGUAGE plpgsql AS $$ BEGIN UPDATE employees SET salary = new_salary WHERE id = employee_id; END; $$; ``` 此代码定义了一个名为 `update_salary` 的存储过程，用于更新员工工资[^1]。 ##### Oracle Database 在 Oracle 数据库中，创建存储过程的语法略有不同： ```sql CREATE OR REPLACE PROCEDURE update_salary ( p_employee_id IN NUMBER, p_new_salary IN NUMBER ) AS BEGIN UPDATE employees SET salary = p_new_salary WHERE id = p_employee_id; END; / ``` 这段代码同样实现了更新员工工资的功能，但在参数声明上有所不同[^2]。 #### 2. 调用存储过程 ##### PostgreSQL (PgSQL) 调用 PostgreSQL 存储过程的方法为： ```sql CALL update_salary(101, 75000); ``` 这会执行之前定义好的 `update_salary` 过程，并传入具体参数值。 ##### Oracle Database 对于 Oracle 来说，则可以这样来调用相同功能的过程： ```sql BEGIN update_salary(101, 75000); END; / ``` 注意这里的语法差异，在 PL/SQL 块内完成操作。 #### 3. 返回结果集 ##### PostgreSQL (PgSQL) 如果想要让存储过程返回查询的结果集合，可以在 PostgreSQL 中这样做： ```sql CREATE OR REPLACE FUNCTION get_employees_by_dept(dept_name TEXT) RETURNS TABLE(id INT, name TEXT, position TEXT) LANGUAGE sql AS $$ SELECT e.id, e.name, e.position FROM employees e JOIN departments d ON e.dept_id = d.id WHERE d.name = dept_name; $$; ``` 这里使用了函数而不是纯粹意义上的 "存储过程",因为 PostgreSQL 支持直接从函数获取表形式的结果。 ##### Oracle Database 而在 Oracle 中实现同样的逻辑则更为直观一些： ```sql CREATE OR REPLACE PROCEDURE get_employees_by_dept( p_dept_name IN VARCHAR2, o_cursor OUT SYS_REFCURSOR ) IS BEGIN OPEN o_cursor FOR SELECT e.id, e.name, e.position FROM employees e JOIN departments d ON e.dept_id = d.id WHERE d.name = p_dept_name; END; / ``` 此处采用了游标的机制传递结果给外部程序处理。 ---

### 如何安装 PostgreSQL #### Windows 平台下的安装过程 在 Windows 上安装 PostgreSQL 可通过官方提供的图形化界面完成。以下是具体的流程： 1. **下载** 需要访问官方网站并下载适合操作系统的版本。可以从官网获取最新稳定版的安装包[^1]。 2. **执行安装程序** 运行下载好的 `.exe` 文件，按照向导逐步设置安装路径、密码以及其他配置选项。默认情况下会安装 `pgAdmin` 工具用于数据库管理。 3. **初始化数据库集群** 安装过程中通常会自动完成此步骤，但如果未启用，则需手动运行以下命令来初始化： ```bash "C:\Program Files\PostgreSQL\version\bin\initdb.exe" --locale=en_US.UTF-8 -E UTF8 -D "C:\Program Files\PostgreSQL\version\data" ``` 4. **启动服务** 使用 Windows 服务管理器或者 PowerShell 启动 PostgreSQL 服务： ```powershell Start-Service postgresql-x64-version ``` 5. **验证安装成功** 打开 pgAdmin 或者使用 psql 命令行工具连接到本地服务器以确认一切正常工作: ```bash psql -U postgres ``` --- #### Linux (RedHat/CentOS) 平台下的安装方法 对于基于 Red Hat 的发行版（如 CentOS），可以采用 YUM 软件仓库的方式快速部署 PostgreSQL。 1. **添加软件源** 下载并导入官方 RPM 包作为新的存储库定义文件： ```bash sudo yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-$(rpm -E '%{rhel}')-x86_64/pgdg-redhat-repo-latest.noarch.rpm ``` 2. **安装客户端和服务端组件** 分别安装客户端和服务器端所需的依赖项以及核心功能模块： ```bash sudo yum install postgresql11 sudo yum install postgresql11-server ``` 3. **初始化数据目录结构** 利用内置脚本完成初始环境搭建任务： ```bash /usr/pgsql-11/bin/postgresql-11-setup initdb ``` 4. **调整开机自启状态并与当前 session 关联起来** 将 PostgreSQL 添加至系统引导序列里，并立即激活它进入运行模式： ```bash sudo systemctl enable postgresql-11 sudo systemctl start postgresql-11 ``` 5. **创建新角色与分配权限范围** 登入超级管理员账户后执行 SQL 查询语句实现新增用户及其关联属性设定目标[^2]: ```sql CREATE USER myusername WITH PASSWORD 'mypassword'; ALTER ROLE myusername CREATEDB; GRANT ALL PRIVILEGES ON DATABASE mydatabase TO myusername; ``` --- #### 测试连接性 无论是哪种操作系统上完成了上述全部环节之后都建议尝试建立远程链接测试其可用性和安全性状况。例如借助 Navicat Professional 图形界面对象浏览器直观查看实例内部组成情况；又或者是单纯依靠 telnet ping portnumber 方法简单判断监听端口是否开放畅通无阻等等方式均可达成目的。 ```python import psycopg2 try: connection = psycopg2.connect( user="myusername", password="mypassword", host="localhost", port="5432", database="mydatabase" ) except Exception as error: print(f"Error while connecting to PostgreSQL {error}") finally: if(connection): cursor.close() connection.close() ```