云数据库RDS快速入门教程(PPAS)

云数据库RDS快速入门教程(PostgreSQL)

本文档包含以下内容： RDS for PostgreSQL实例连接 RDS for PostgreSQL数据迁入 RDS for PostgreSQL数据迁出 RDS for PostgreSQL用户 … 云数据库RDS快速入门教程(PostgreSQL)

本文档包含以下内容：

RDS for PPAS实例连接 RDS for PPAS数据迁入 RDS for PPAS数据迁出 RDS for PPAS Oracle兼容性基础 RDS for PPAS本地驱动安装 RDS for PPAS功能限制

1.RDS for PPAS实例连接

创建初始帐号

在完成实例购买之后，首先去登陆RDS管理控制台，创建“初始帐号”。 假设创建的账号为：ppas_user

命令行连接实例

在管理控制台的基本信息页面，可以找到实例的连接地址和端口，例如我们有如下实例： rdsv07z563m7o25cj550public.ppas.rds.aliyuncs.com:3433 有了实例连接串、端口、初始帐号、密码，就可以使用命令管理和操作实例了。

连接数据库

psql -U ppas_user -h rdsv07z563m7o25cj550public.ppas.rds.aliyuncs.com -p 3433 template1 Password for user ppas_user: psql.bin (9.4.4, server 9.3.5.14) Type “help” for help. template1=>\q

注意事项

建议不要使用9.3.5以下的PostgreSQL客户端psql工具连接数据库，因为版本不匹配可能会导致很多功能无法使用，连接时会出现类似以下的提示：

WARNING: psql major version 9.1, server major version 9.3. Some psql features might not work.

2.RDS for PPAS数据迁入

使用阿里云数据传输(Data Transmission)将本地的Oracle迁移至RDS for PPAS实例中，可以进行结构定义迁移及全量数据迁移。迁移过程中，对本地的Oracle数据库没有影响。

迁移类型简介

结构迁移 DTS会将迁移对象的结构定义迁移到目标实例。目前DTS支持结构迁移的对象有：表、视图、同义词、触发器、存储过程、存储函数、包、自定义类型

全量迁移 DTS会将源数据库迁移对象在的数据全部迁移到目标实例。如果迁移过程中，本地Oracle库有数据写入的话，那么这些增量数据不一定能够被迁移到PPAS中。所以，如果要保证数据一致性，那么尽量选择在业务低峰期进行全量迁移

迁移限制

迁移过程中，不支持DDL操作 不支持物化视图的迁移 结构迁移时，会将reverse index迁移成普通索引 结构迁移时，会将位图索引迁移成普通索引 结构迁移时，会将分区索引迁移成，在每个分区上单独创建自己的索引

迁移权限要求

当使用DTS进行Oracle迁移时，在不同迁移类型情况下，源跟目标数据库的迁移帐号权限要求如下：

迁移步骤

下面详细介绍下用户如何使用DTS将本地的Oracle迁移到RDS for PPAS上的流程

RDS实例数据库创建

在正式迁移之前，需要确认目标RDS实例中是否已经创建好了需要迁入的数据库，如果没有创建的话，需要先在目标PPAS实例中创建数据库。

迁移帐号创建

在正式迁移之前，需要先在本地Oracle数据库及云上PPAS实例中创建迁移帐号，并将要迁移的库表的相关权限授权给上面创建的帐号。

正式迁移

当上面的所有前提条件都配置完成后，就可以开始正式的数据迁移了。下面详细介绍下具体的迁移步骤。 1）进入DTS控制台，点击创建迁移任务，正式开始任务创建

2) 填写本地Oracle跟目标RDS实例的连接信息

这里面源实例类型要选择有公网IP的自建数据库

3）选择迁移对象及迁移类型

默认情况下，迁移对象迁移到目标库后，对象名跟源库一致。如果用户想修改对象在目标库上的名字时，可以直接修改已选择的对象名。

当点击编辑后，即进入对象名称修改页面：

修改了对象名后，目标实例的对象名即为修改后的对象名。

4）启动预检查 在数据正式迁移之前，会先进行基础预检查，只有预检查通过后，才能启动迁移

如果预检查失败，那么可以查看具体的失败详情，根据失败原因修复后，重新进行预检查

当点击查看后，可以查看到具体的预检查失败原因：

当修复完成后，可以在任务列表中，选择这个任务，然后重新启动预检查

5）启动迁移任务 当预检查通过后，我们可以启动迁移任务，任务启动后，可以到任务列表中查看具体的迁移状态及进度

至此，完成将本地Oracle数据库迁移到RDS for PPAS实例的数据迁移工作。

3.RDS for PPAS数据迁出

通过逻辑备份进行数据迁出

通过pg_dump导出逻辑备份

通过以下命令操作 pg_dump -U {user-name} -h {host-name} -p {port} {database-name} -f {file-name} 如：

pg_dump -U ppas_user -h rdsv07z563m7o25cj550public.ppas.rds.aliyuncs.com -p 3433 edb -f ppas.sql

将数据恢复到目标服务器

将pg001.sql放到目标服务器中，并进行以下命令操作 psql -U {user-name} -h {host-name} -d {desintation_db} -p {port} -f {dumpfilename.sql} 如：

psql -U ppas_user -h localhost -d edb -p 5444 -f ppas.sql

注意事项

由于云上的权限配置不一至，在数据导入过程当中可能会出现一些与权限相关的WARNING或ERROR，可以忽略，如：

WARNING: no privileges could be revoked for “xxxxx” ERROR: role “xxxxx” does not exist

4.RDS for PPAS Oracle兼容性基础

通过本文档中的DEMO，Oracle用户可以快速对应PPAS数据库中的术语及概念，以便在迁移及开发过程中提高效率。如有任何文档上的问题欢迎通过工单给我们进行反馈。

以下所有操作基于一个基础模型，通过此模型用户可以看到RDS for PPAS中最基本的建库、建表、用户管理等操作，基础数据模型如下：

同时，为了模拟Oracle上类似的环境，我们会建立一名字为orcl_ppas的数据库(database)，在此数据库中建立名为scott的用户，并建立与这个用户同名的schema用户空间。

连接数据库

psql -h rdsv07z563m7o25cj550public.ppas.rds.aliyuncs.com -p 3433 -U ppas_user -d template1 用户 ppas_user 的口令： psql.bin (9.4.1.3, 服务器 9.3.5.14) 输入 “help” 来获取帮助信息. template1=>

注意：以上的“template1=>”为操作提示符，意思是当前正在使用的是名为template1的数据库

建立数据库 CREATE DATABASE

template1=> CREATE DATABASE orcl_ppas; CREATE DATABASE template1=> \c orcl_ppas psql.bin (9.4.1.3, 服务器 9.3.5.14)

您现在已经连线到数据库 “orcl_ppas”,用户 “ppas_user”.

建立普通用户 CREATE USER

orcl_ppas=> CREATE ROLE scott LOGIN PASSWORD ‘scott12345’; CREATE ROLE

建议用户的私有空间 CREATE SCHEMA

orcl_ppas=> CREATE SCHEMA scott; CREATE SCHEMA orcl_ppas=> GRANT scott TO ppas_user; GRANT ROLE orcl_ppas=> ALTER SCHEMA scott OWNER TO scott; ALTER SCHEMA orcl_ppas=> REVOKE scott FROM ppas_user; REVOKE ROLE

注意1：如果在进行ALTER SCHEMA scott OWNER TO scott之前没有将scott加入到ppas_user角色，将会出现ERROR: must be member of role “scott”的权限问题。 注意2：从安全角度出发，scott这个用户在本DEMO定义为是一个普通的应用程序帐号，因此在处理完OWNER的授权后，将它移出ppas_user角色以提高安全性。

使用scott用户连接到orcl_ppas数据库

[[email protected] bin]# ./psql -h rdsv07z563m7o25cj550public.ppas.rds.aliyuncs.com -p 3433 -U scott -d orcl_ppas 用户 scott 的口令： psql.bin (9.4.1.3, 服务器 9.3.5.14) 输入 “help” 来获取帮助信息. orcl_ppas=>

注意：这一步骤十分重要，以下所有操作都是以scott帐号进行登陆进行的，否则所建立的数据表及各种数据库对象将不属于scott用户，导致权限问题

建立数据表 CREATE TABLE

CREATE TABLE dept ( deptno NUMBER(2) NOT NULL CONSTRAINT dept_pk PRIMARY KEY, dname VARCHAR2(14) CONSTRAINT dept_dname_uq UNIQUE, lock VARCHAR2(13) ); CREATE TABLE emp ( empno NUMBER(4) NOT NULL CONSTRAINT emp_pk PRIMARY KEY, ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4), hiredate DATE, sal NUMBER(7,2) CONSTRAINT emp_sal_ck CHECK (sal > 0), comm NUMBER(7,2), deptno NUMBER(2) CONSTRAINT emp_ref_dept_fk REFERENCES dept(deptno) ); CREATE TABLE jobhist ( empno NUMBER(4) NOT NULL, startdate DATE NOT NULL, enddate DATE, job VARCHAR2(9), sal NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(2), chgdesc VARCHAR2(80), CONSTRAINT jobhist_pk PRIMARY KEY (empno, startdate), CONSTRAINT jobhist_ref_emp_fk FOREIGN KEY (empno) REFERENCES emp(empno) ON DELETE CASCADE, CONSTRAINT jobhist_ref_dept_fk FOREIGN KEY (deptno) REFERENCES dept (deptno) ON DELETE SET NULL, CONSTRAINT jobhist_date_chk CHECK (startdate <= enddate) );

建立视图 CREATE VIEW

CREATE OR REPLACE VIEW salesemp AS SELECT empno, ename, hiredate, sal, comm FROM emp WHERE job = ‘SALESMAN’;

建立序列 CREATE SEQUENCE

CREATE SEQUENCE next_empno START WITH 8000 INCREMENT BY 1;

数据插入 INSERT

INSERT INTO dept VALUES (10,’ACCOUNTING’,’NEW YORK’); INSERT INTO dept VALUES (20,’RESEARCH’,’DALLAS’); INSERT INTO dept VALUES (30,’SALES’,’CHICAGO’); INSERT INTO dept VALUES (40,’OPERATIONS’,’BOSTON’); INSERT INTO emp VALUES (7369,’SMITH’,’CLERK’,7902,’17-DEC-80′,800,NULL,20); INSERT INTO emp VALUES (7499,’ALLEN’,’SALESMAN’,7698,’20-FEB-81′,1600,300,30); INSERT INTO emp VALUES (7521,’WARD’,’SALESMAN’,7698,’22-FEB-81′,1250,500,30); INSERT INTO emp VALUES (7566,’JONES’,’MANAGER’,7839,’02-APR-81′,2975,NULL,20); INSERT INTO emp VALUES (7654,’MARTIN’,’SALESMAN’,7698,’28-SEP-81′,1250,1400,30); INSERT INTO emp VALUES (7698,’BLAKE’,’MANAGER’,7839,’01-MAY-81′,2850,NULL,30); INSERT INTO emp VALUES (7782,’CLARK’,’MANAGER’,7839,’09-JUN-81′,2450,NULL,10); INSERT INTO emp VALUES (7788,’SCOTT’,’ANALYST’,7566,’19-APR-87′,3000,NULL,20); INSERT INTO emp VALUES (7839,’KING’,’PRESIDENT’,NULL,’17-NOV-81′,5000,NULL,10); INSERT INTO emp VALUES (7844,’TURNER’,’SALESMAN’,7698,’08-SEP-81′,1500,0,30); INSERT INTO emp VALUES (7876,’ADAMS’,’CLERK’,7788,’23-MAY-87′,1100,NULL,20); INSERT INTO emp VALUES (7900,’JAMES’,’CLERK’,7698,’03-DEC-81′,950,NULL,30); INSERT INTO emp VALUES (7902,’FORD’,’ANALYST’,7566,’03-DEC-81′,3000,NULL,20); INSERT INTO emp VALUES (7934,’MILLER’,’CLERK’,7782,’23-JAN-82′,1300,NULL,10); INSERT INTO jobhist VALUES (7369,’17-DEC-80′,NULL,’CLERK’,800,NULL,20,’New Hire’); INSERT INTO jobhist VALUES (7499,’20-FEB-81′,NULL,’SALESMAN’,1600,300,30,’New Hire’); INSERT INTO jobhist VALUES (7521,’22-FEB-81′,NULL,’SALESMAN’,1250,500,30,’New Hire’); INSERT INTO jobhist VALUES (7566,’02-APR-81′,NULL,’MANAGER’,2975,NULL,20,’New Hire’); INSERT INTO jobhist VALUES (7654,’28-SEP-81′,NULL,’SALESMAN’,1250,1400,30,’New Hire’); INSERT INTO jobhist VALUES (7698,’01-MAY-81′,NULL,’MANAGER’,2850,NULL,30,’New Hire’); INSERT INTO jobhist VALUES (7782,’09-JUN-81′,NULL,’MANAGER’,2450,NULL,10,’New Hire’); INSERT INTO jobhist VALUES (7788,’19-APR-87′,’12-APR-88′,’CLERK’,1000,NULL,20,’New Hire’); INSERT INTO jobhist VALUES (7788,’13-APR-88′,’04-MAY-89′,’CLERK’,1040,NULL,20,’Raise’); INSERT INTO jobhist VALUES (7788,’05-MAY-90′,NULL,’ANALYST’,3000,NULL,20,’Promoted to Analyst’); INSERT INTO jobhist VALUES (7839,’17-NOV-81′,NULL,’PRESIDENT’,5000,NULL,10,’New Hire’); INSERT INTO jobhist VALUES (7844,’08-SEP-81′,NULL,’SALESMAN’,1500,0,30,’New Hire’); INSERT INTO jobhist VALUES (7876,’23-MAY-87′,NULL,’CLERK’,1100,NULL,20,’New Hire’); INSERT INTO jobhist VALUES (7900,’03-DEC-81′,’14-JAN-83′,’CLERK’,950,NULL,10,’New Hire’); INSERT INTO jobhist VALUES (7900,’15-JAN-83′,NULL,’CLERK’,950,NULL,30,’Changed to Dept 30′); INSERT INTO jobhist VALUES (7902,’03-DEC-81′,NULL,’ANALYST’,3000,NULL,20,’New Hire’); INSERT INTO jobhist VALUES (7934,’23-JAN-82′,NULL,’CLERK’,1300,NULL,10,’New Hire’);

查询优化器数据分析 ANALYZE

ANALYZE dept; ANALYZE emp; ANALYZE jobhist;

建立存储过程 CREATE PROCEDURE

CREATE OR REPLACE PROCEDURE list_emp IS v_empno NUMBER(4); v_ename VARCHAR2(10); CURSOR emp_cur IS SELECT empno, ename FROM emp ORDER BY empno; BEGIN OPEN emp_cur; DBMS_OUTPUT.PUT_LINE(‘EMPNO ENAME’); DBMS_OUTPUT.PUT_LINE(‘—– ——-‘); LOOP FETCH emp_cur INTO v_empno, v_ename; EXIT WHEN emp_cur%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_empno || ‘ ‘ || v_ename); END LOOP; CLOSE emp_cur; END; — — Procedure that selects an employee row given the employee — number and displays certain columns. — CREATE OR REPLACE PROCEDURE select_emp ( p_empno IN NUMBER ) IS v_ename emp.ename%TYPE; v_hiredate emp.hiredate%TYPE; v_sal emp.sal%TYPE; v_comm emp.comm%TYPE; v_dname dept.dname%TYPE; v_disp_date VARCHAR2(10); BEGIN SELECT ename, hiredate, sal, NVL(comm, 0), dname INTO v_ename, v_hiredate, v_sal, v_comm, v_dname FROM emp e, dept d WHERE empno = p_empno AND e.deptno = d.deptno; v_disp_date := TO_CHAR(v_hiredate, ‘MM/DD/YYYY’); DBMS_OUTPUT.PUT_LINE(‘Number : ‘ || p_empno); DBMS_OUTPUT.PUT_LINE(‘Name : ‘ || v_ename); DBMS_OUTPUT.PUT_LINE(‘Hire Date : ‘ || v_disp_date); DBMS_OUTPUT.PUT_LINE(‘Salary : ‘ || v_sal); DBMS_OUTPUT.PUT_LINE(‘Commission: ‘ || v_comm); DBMS_OUTPUT.PUT_LINE(‘Department: ‘ || v_dname); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(‘Employee ‘ || p_empno || ‘ not found’); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(‘The following is SQLERRM:’); DBMS_OUTPUT.PUT_LINE(SQLERRM); DBMS_OUTPUT.PUT_LINE(‘The following is SQLCODE:’); DBMS_OUTPUT.PUT_LINE(SQLCODE); END; — — Procedure that queries the ’emp’ table based on — department number and employee number or name. Returns — employee number and name as IN OUT parameters and job, — hire date, and salary as OUT parameters. — CREATE OR REPLACE PROCEDURE emp_query ( p_deptno IN NUMBER, p_empno IN OUT NUMBER, p_ename IN OUT VARCHAR2, p_job OUT VARCHAR2, p_hiredate OUT DATE p_sal OUT NUMBER ) IS BEGIN SELECT empno, ename, job, hiredate, sal INTO p_empno, p_ename, p_job, p_hiredate, p_sal FROM emp WHERE deptno = p_deptno AND (empno = p_empno OR ename = UPPER(p_ename)); END; — — Procedure to call ’emp_query_caller’ with IN and IN OUT — parameters. Displays the results received from IN OUT and — OUT parameters. — CREATE OR REPLACE PROCEDURE emp_query_caller IS v_deptno NUMBER(2); v_empno NUMBER(4); v_ename VARCHAR2(10); v_job VARCHAR2(9); v_hiredate DATE; v_sal NUMBER; BEGIN v_deptno := 30; v_empno := 0; v_ename := ‘Martin’; emp_query(v_deptno, v_empno, v_ename, v_job, v_hiredate, v_sal); DBMS_OUTPUT.PUT_LINE(‘Department : ‘ || v_deptno); DBMS_OUTPUT.PUT_LINE(‘Employee No: ‘ || v_empno); DBMS_OUTPUT.PUT_LINE(‘Name : ‘ || v_ename); DBMS_OUTPUT.PUT_LINE(‘Job : ‘ || v_job); DBMS_OUTPUT.PUT_LINE(‘Hire Date : ‘ || v_hiredate); DBMS_OUTPUT.PUT_LINE(‘Salary : ‘ || v_sal); EXCEPTION WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE(‘More than one employee was selected’); WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(‘No employees were selected’); END;

建立函数 CREATE FUNCTION

CREATE OR REPLACE FUNCTION emp_comp ( p_sal NUMBER, p_comm NUMBER ) RETURN NUMBER IS BEGIN RETURN (p_sal + NVL(p_comm, 0)) * 24; END; — — Function that gets the next number from sequence, ‘next_empno’, — and ensures it is not already in use as an employee number. — CREATE OR REPLACE FUNCTION new_empno RETURN NUMBER IS v_cnt INTEGER := 1; v_new_empno NUMBER; BEGIN WHILE v_cnt > 0 LOOP SELECT next_empno.nextval INTO v_new_empno FROM dual; SELECT COUNT(*) INTO v_cnt FROM emp WHERE empno = v_new_empno; END LOOP; RETURN v_new_empno; END; — — EDB-SPL function that adds a new clerk to table ’emp’. This function — uses package ’emp_admin’. — CREATE OR REPLACE FUNCTION hire_clerk ( p_ename VARCHAR2, p_deptno NUMBER ) RETURN NUMBER IS v_empno NUMBER(4); v_ename VARCHAR2(10); v_job VARCHAR2(9); v_mgr NUMBER(4); v_hiredate DATE; v_sal NUMBER(7,2); v_comm NUMBER(7,2); v_deptno NUMBER(2); BEGIN v_empno := new_empno; INSERT INTO emp VALUES (v_empno, p_ename, ‘CLERK’, 7782, TRUNC(SYSDATE), 950.00, NULL, p_deptno); SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno INTO v_empno, v_ename, v_job, v_mgr, v_hiredate, v_sal, v_comm, v_deptno FROM emp WHERE empno = v_empno; DBMS_OUTPUT.PUT_LINE(‘Department : ‘ || v_deptno); DBMS_OUTPUT.PUT_LINE(‘Employee No: ‘ || v_empno); DBMS_OUTPUT.PUT_LINE(‘Name : ‘ || v_ename); DBMS_OUTPUT.PUT_LINE(‘Job : ‘ || v_job); DBMS_OUTPUT.PUT_LINE(‘Manager : ‘ || v_mgr); DBMS_OUTPUT.PUT_LINE(‘Hire Date : ‘ || v_hiredate); DBMS_OUTPUT.PUT_LINE(‘Salary : ‘ || v_sal); DBMS_OUTPUT.PUT_LINE(‘Commission : ‘ || v_comm); RETURN v_empno; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(‘The following is SQLERRM:’); DBMS_OUTPUT.PUT_LINE(SQLERRM); DBMS_OUTPUT.PUT_LINE(‘The following is SQLCODE:’); DBMS_OUTPUT.PUT_LINE(SQLCODE); RETURN -1; END; — — PostgreSQL PL/pgSQL function that adds a new salesman — to table ’emp’. — CREATE OR REPLACE FUNCTION hire_salesman ( p_ename VARCHAR, p_sal NUMERIC, p_comm NUMERIC ) RETURNS NUMERIC AS $$ DECLARE v_empno NUMERIC(4); v_ename VARCHAR(10); v_job VARCHAR(9); v_mgr NUMERIC(4); v_hiredate DATE; v_sal NUMERIC(7,2); v_comm NUMERIC(7,2); v_deptno NUMERIC(2); BEGIN v_empno := new_empno(); INSERT INTO emp VALUES (v_empno, p_ename, ‘SALESMAN’, 7698, CURRENT_DATE, p_sal, p_comm, 30); SELECT INTO v_empno, v_ename, v_job, v_mgr, v_hiredate, v_sal, v_comm, v_deptno empno, ename, job, mgr, hiredate, sal, comm, deptno FROM emp WHERE empno = v_empno; RAISE INFO ‘Department : %’, v_deptno; RAISE INFO ‘Employee No: %’, v_empno; RAISE INFO ‘Name : %’, v_ename; RAISE INFO ‘Job : %’, v_job; RAISE INFO ‘Manager : %’, v_mgr; RAISE INFO ‘Hire Date : %’, v_hiredate; RAISE INFO ‘Salary : %’, v_sal; RAISE INFO ‘Commission : %’, v_comm; RETURN v_empno; EXCEPTION WHEN OTHERS THEN RAISE INFO ‘The following is SQLERRM:’; RAISE INFO ‘%’, SQLERRM; RAISE INFO ‘The following is SQLSTATE:’; RAISE INFO ‘%’, SQLSTATE; RETURN -1; END;

建立规则，CREATE RULE

CREATE OR REPLACE RULE salesemp_i AS ON INSERT TO salesemp DO INSTEAD INSERT INTO emp VALUES (NEW.empno, NEW.ename, ‘SALESMAN’, 7698, NEW.hiredate, NEW.sal, NEW.comm, 30); CREATE OR REPLACE RULE salesemp_u AS ON UPDATE TO salesemp DO INSTEAD UPDATE emp SET empno = NEW.empno, ename = NEW.ename, hiredate = NEW.hiredate, sal = NEW.sal, comm = NEW.comm WHERE empno = OLD.empno; CREATE OR REPLACE RULE salesemp_d AS ON DELETE TO salesemp DO INSTEAD DELETE FROM emp WHERE empno = OLD.empno;

建立触发器 CREATE TRIGGER

CREATE OR REPLACE TRIGGER user_audit_trig AFTER INSERT OR UPDATE OR DELETE ON emp DECLARE v_action VARCHAR2(24); BEGIN IF INSERTING THEN v_action := ‘ added employee(s) on ‘; ELSIF UPDATING THEN v_action := ‘ updated employee(s) on ‘; ELSIF DELETING THEN v_action := ‘ deleted employee(s) on ‘; END IF; DBMS_OUTPUT.PUT_LINE(‘User ‘ || USER || v_action || TO_CHAR(SYSDATE,’YYYY-MM-DD’)); END; CREATE OR REPLACE TRIGGER emp_sal_trig BEFORE DELETE OR INSERT OR UPDATE ON emp FOR EACH ROW DECLARE sal_diff NUMBER; BEGIN IF INSERTING THEN DBMS_OUTPUT.PUT_LINE(‘Inserting employee ‘ || :NEW.empno); DBMS_OUTPUT.PUT_LINE(‘..New salary: ‘ || :NEW.sal); END IF; IF UPDATING THEN sal_diff := :NEW.sal – :OLD.sal; DBMS_OUTPUT.PUT_LINE(‘Updating employee ‘ || :OLD.empno); DBMS_OUTPUT.PUT_LINE(‘..Old salary: ‘ || :OLD.sal); DBMS_OUTPUT.PUT_LINE(‘..New salary: ‘ || :NEW.sal); DBMS_OUTPUT.PUT_LINE(‘..Raise : ‘ || sal_diff); END IF; IF DELETING THEN DBMS_OUTPUT.PUT_LINE(‘Deleting employee ‘ || :OLD.empno); DBMS_OUTPUT.PUT_LINE(‘..Old salary: ‘ || :OLD.sal); END IF; END;

建立包 CREATE PACKATE

CREATE OR REPLACE PACKAGE emp_admin IS FUNCTION get_dept_name ( p_deptno NUMBER ) RETURN VARCHAR2; FUNCTION update_emp_sal ( p_empno NUMBER, p_raise NUMBER ) RETURN NUMBER; PROCEDURE hire_emp ( p_empno NUMBER, p_ename VARCHAR2, p_job VARCHAR2, p_sal NUMBER, p_hiredate DATE, p_comm NUMBER, p_mgr NUMBER, p_deptno NUMBER ); PROCEDURE fire_emp ( p_empno NUMBER ); END emp_admin;

建立包体 CREATE PACKATE BODY

— — Package body for the ’emp_admin’ package. — CREATE OR REPLACE PACKAGE BODY emp_admin IS — — Function that queries the ‘dept’ table based on the department — number and returns the corresponding department name. — FUNCTION get_dept_name ( p_deptno IN NUMBER ) RETURN VARCHAR2 IS v_dname VARCHAR2(14); BEGIN SELECT dname INTO v_dname FROM dept WHERE deptno = p_deptno; RETURN v_dname; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(‘Invalid department number ‘ || p_deptno); RETURN ”; END; — — Function that updates an employee’s salary based on the — employee number and salary increment/decrement passed — as IN parameters. Upon successful completion the function — returns the new updated salary. — FUNCTION update_emp_sal ( p_empno IN NUMBER, p_raise IN NUMBER ) RETURN NUMBER IS v_sal NUMBER := 0; BEGIN SELECT sal INTO v_sal FROM emp WHERE empno = p_empno; v_sal := v_sal + p_raise; UPDATE emp SET sal = v_sal WHERE empno = p_empno; RETURN v_sal; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(‘Employee ‘ || p_empno || ‘ not found’); RETURN -1; WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(‘The following is SQLERRM:’); DBMS_OUTPUT.PUT_LINE(SQLERRM); DBMS_OUTPUT.PUT_LINE(‘The following is SQLCODE:’); DBMS_OUTPUT.PUT_LINE(SQLCODE); RETURN -1; END; — — Procedure that inserts a new employee record into the ’emp’ table. — PROCEDURE hire_emp ( p_empno NUMBER, p_ename VARCHAR2, p_job VARCHAR2, p_sal NUMBER, p_hiredate DATE, p_comm NUMBER, p_mgr NUMBER, p_deptno NUMBER ) AS BEGIN INSERT INTO emp(empno, ename, job, sal, hiredate, comm, mgr, deptno) VALUES(p_empno, p_ename, p_job, p_sal, p_hiredate, p_comm, p_mgr, p_deptno); END; — — Procedure that deletes an employee record from the ’emp’ table based — on the employee number. — PROCEDURE fire_emp ( p_empno NUMBER ) AS BEGIN DELETE FROM emp WHERE empno = p_empno; END; END;

5.RDS for PPAS本地区动安装

RDS for PPAS提供丰富的应用开发驱动程序接口

Linux版本包括：JAVA / OCI / ODBC Windows版本包括：.Net / JAVA / OCI / ODBC

下载 PPAS开发驱动 ，解压后将可以得到

edb_connectors-9.3.5.14-3-linux-x64.run edb_connectors-9.3.5.14-3-linux.run edb_connectors-9.3.5.14-3-windows-x64.exe edb_connectors-9.3.5.14-3-windows.exe

安装完成后

Linux默认驱动包安装路径为：/opt/PostgresPlus/9.3AS/connectors Windows默认驱动包安装路径为：C:/Program Files/PostgresPlus/9.3AS/connectors

6.RDS for PPAS功能限制

为保障实例的稳定及安全，RDS for PPAS有部分使用上的约束，详情如下：

转载请注明：小猪云服务器租用推荐 » 云数据库RDS快速入门教程(PPAS)