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

本文档包含以下内容：

• 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有部分使用上的约束，详情如下：