Appendix: PPAS compatibility description - Quick Start for PPAS| Alibaba Cloud Documentation Center

The examples described in this document enable the Oracle users to quickly understand glossary and concepts used in the PPAS database, and to improve the efficiency in data migration and development.

All of the following operations are based on a basic model. In this model, users can see the most basic operations used in RDS for PPAS, for example, creating the databases and data tables, and managing the accounts. The basic data model is shown as follows:

DEMO ER diagram

In addition, we create a database called orcl_ppas to simulate an environment similar to Oracle. In this database, we create a role named scott, and create a schema user space with the same name as this role.

Connect to database psql

   psql -h ppasaddress.ppas.rds.aliyuncs.com -p 3433 -U myuser -d template1
   myuser password:
   psql.bin (9.4.1.3, server 9.3.5.14)
   Input "help" to obtain help information.
   template1=>

CREATE DATABASE

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

CREATE ROLE

   orcl_ppas=> CREATE ROLE scott Logon PASSWORD 'scott123';
   CREATE ROLE

CREATE SCHEMA

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

Note:

If scott is not added to the myuser role before `ALTER SCHEMA scott OWNER TO scott, the following permission error is displayed:
```
ERROR:  must be member of role "scott"
```
For security reasons, remove scott from the myuser role to improve security after the authorization of OWNER is handled.

Connect to orcl_ppas database

Note: This step is important. All of the following operations must be performed under the scott account. Otherwise, all the created data tables and various databases do not belong to the scott role and a permission error occurs.

   [root@localhost bin]# ./psql -h ppasaddress.ppas.rds.aliyuncs.com -p 3433 -U scott -d orcl_ppas
   Password of the scott role:
   psql.bin (9.4.1.3, server 9.3.5.14)
   Input "help" to obtain help information.
   orcl_ppas=>

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 OR REPLACE 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 INTO

   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 make sure that 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 PACKAGE

   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 PACKAGE 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;