This topic provides examples to help Oracle users quickly understand the glossary and concepts used in the PPAS database and improve the efficiency of data migration and development.

The examples described in this document enable Oracle users to quickly understand glossary and concepts used in the PPAS database, and to improve the efficiency during 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 PPAS, for example, creating databases and data tables and managing accounts. The basic data model is shown as follows:Basic model

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 a schema user space with the same name as this role.

Prerequisites

EnterpriseDB is installed on the local host or ECS.

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 LOGIN 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 cott is not added to the myuser role before execution of ALTER SCHEMA scott OWNER TO scott, the following permission error is displayed: 
    ERROR:must be member of role "scott"
  • For security purpose, remove cott from the myuser role after authorization of OWNER.

Connect to the orcl_ppas database

Note This step is important. All of the following operations must be performed using 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 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
  Vanderbilt empno: = new_empno ();
  INSERT INTO emp VALUES (v_empno, p_ename, 'SALESMAN', 7698,
      CURRENT_DATE, p_sal, p_comm, 30);
  SELECT INTO
      Vanderbilt empno, le_ename, Vanderbilt job, Vanderbilt Mgr, glassals
      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;