edit-icon download-icon

Appendix: PPAS compatibility description

Last Updated: Dec 12, 2017

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

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

CREATE DATABASE

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

CREATE ROLE

  1. orcl_ppas=> CREATE ROLE scott Logon PASSWORD 'scott123';
  2. CREATE ROLE

CREATE SCHEMA

  1. orcl_ppas=> CREATE SCHEMA scott;
  2. CREATE SCHEMA
  3. orcl_ppas=> GRANT scott TO myuser;
  4. GRANT ROLE
  5. orcl_ppas=> ALTER SCHEMA scott OWNER TO scott;
  6. ALTER SCHEMA
  7. orcl_ppas=> REVOKE scott FROM myuser;
  8. 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:

    1. 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.

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

CREATE TABLE

  1. CREATE TABLE dept (
  2. deptno NUMBER(2) NOT NULL CONSTRAINT dept_pk PRIMARY KEY,
  3. dname VARCHAR2(14) CONSTRAINT dept_dname_uq UNIQUE,
  4. lock VARCHAR2(13)
  5. );
  6. CREATE TABLE emp (
  7. empno NUMBER(4) NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
  8. ename VARCHAR2(10),
  9. job VARCHAR2(9),
  10. mgr NUMBER(4),
  11. hiredate DATE,
  12. sal NUMBER(7,2) CONSTRAINT emp_sal_ck CHECK (sal > 0),
  13. comm NUMBER(7,2),
  14. deptno NUMBER(2) CONSTRAINT emp_ref_dept_fk
  15. REFERENCES dept(deptno)
  16. );
  17. CREATE TABLE jobhist (
  18. empno NUMBER(4) NOT NULL,
  19. startdate DATE NOT NULL,
  20. enddate DATE,
  21. job VARCHAR2(9),
  22. sal NUMBER(7,2),
  23. comm NUMBER(7,2),
  24. deptno NUMBER(2),
  25. chgdesc VARCHAR2(80),
  26. CONSTRAINT jobhist_pk PRIMARY KEY (empno, startdate),
  27. CONSTRAINT jobhist_ref_emp_fk FOREIGN KEY (empno)
  28. REFERENCES emp(empno) ON DELETE CASCADE,
  29. CONSTRAINT jobhist_ref_dept_fk FOREIGN KEY (deptno)
  30. REFERENCES dept (deptno) ON DELETE SET NULL,
  31. CONSTRAINT jobhist_date_chk CHECK (startdate <= enddate)
  32. );

CREATE OR REPLACE VIEW

  1. CREATE OR REPLACE VIEW salesemp AS
  2. SELECT empno, ename, hiredate, sal, comm FROM emp WHERE job = 'SALESMAN';

CREATE SEQUENCE

  1. CREATE SEQUENCE next_empno START WITH 8000 INCREMENT BY 1;

INSERT INTO

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

ANALYZE

  1. ANALYZE dept;
  2. ANALYZE emp;
  3. ANALYZE jobhist;

CREATE PROCEDURE

  1. CREATE OR REPLACE PROCEDURE list_emp
  2. IS
  3. v_empno NUMBER(4);
  4. v_ename VARCHAR2(10);
  5. CURSOR emp_cur IS
  6. SELECT empno, ename FROM emp ORDER BY empno;
  7. BEGIN
  8. OPEN emp_cur;
  9. DBMS_OUTPUT.PUT_LINE('EMPNO ENAME');
  10. DBMS_OUTPUT.PUT_LINE('----- -------');
  11. LOOP
  12. FETCH emp_cur INTO v_empno, v_ename;
  13. EXIT WHEN emp_cur%NOTFOUND;
  14. DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_ename);
  15. END LOOP;
  16. CLOSE emp_cur;
  17. END;
  18. --
  19. -- Procedure that selects an employee row given the employee
  20. -- number and displays certain columns.
  21. --
  22. CREATE OR REPLACE PROCEDURE select_emp (
  23. p_empno IN NUMBER
  24. )
  25. IS
  26. v_ename emp.ename%TYPE;
  27. v_hiredate emp.hiredate%TYPE;
  28. v_sal emp.sal%TYPE;
  29. v_comm emp.comm%TYPE;
  30. v_dname dept.dname%TYPE;
  31. v_disp_date VARCHAR2(10);
  32. BEGIN
  33. SELECT ename, hiredate, sal, NVL(comm, 0), dname
  34. INTO v_ename, v_hiredate, v_sal, v_comm, v_dname
  35. FROM emp e, dept d
  36. WHERE empno = p_empno
  37. AND e.deptno = d.deptno;
  38. v_disp_date := TO_CHAR(v_hiredate, 'MM/DD/YYYY');
  39. DBMS_OUTPUT.PUT_LINE('Number : ' || p_empno);
  40. DBMS_OUTPUT.PUT_LINE('Name : ' || v_ename);
  41. DBMS_OUTPUT.PUT_LINE('Hire Date : ' || v_disp_date);
  42. DBMS_OUTPUT.PUT_LINE('Salary : ' || v_sal);
  43. DBMS_OUTPUT.PUT_LINE('Commission: ' || v_comm);
  44. DBMS_OUTPUT.PUT_LINE('Department: ' || v_dname);
  45. EXCEPTION
  46. WHEN NO_DATA_FOUND THEN
  47. DBMS_OUTPUT.PUT_LINE('Employee ' || p_empno || ' not found');
  48. WHEN OTHERS THEN
  49. DBMS_OUTPUT.PUT_LINE('The following is SQLERRM:');
  50. DBMS_OUTPUT.PUT_LINE(SQLERRM);
  51. DBMS_OUTPUT.PUT_LINE('The following is SQLCODE:');
  52. DBMS_OUTPUT.PUT_LINE(SQLCODE);
  53. END;
  54. --
  55. -- Procedure that queries the 'emp' table based on
  56. -- department number and employee number or name. Returns
  57. -- employee number and name as IN OUT parameters and job,
  58. -- hire date, and salary as OUT parameters.
  59. --
  60. CREATE OR REPLACE PROCEDURE emp_query (
  61. p_deptno IN NUMBER,
  62. p_empno IN OUT NUMBER,
  63. p_ename IN OUT VARCHAR2,
  64. p_job OUT VARCHAR2,
  65. p_hiredate OUT DATE
  66. p_sal OUT NUMBER
  67. )
  68. IS
  69. BEGIN
  70. SELECT empno, ename, job, hiredate, sal
  71. INTO p_empno, p_ename, p_job, p_hiredate, p_sal
  72. FROM emp
  73. WHERE deptno = p_deptno
  74. AND (empno = p_empno
  75. OR ename = UPPER(p_ename));
  76. END;
  77. --
  78. -- Procedure to call 'emp_query_caller' with IN and IN OUT
  79. -- parameters. Displays the results received from IN OUT and
  80. -- OUT parameters.
  81. --
  82. CREATE OR REPLACE PROCEDURE emp_query_caller
  83. IS
  84. v_deptno NUMBER(2);
  85. v_empno NUMBER(4);
  86. v_ename VARCHAR2(10);
  87. v_job VARCHAR2(9);
  88. v_hiredate DATE;
  89. v_sal NUMBER;
  90. BEGIN
  91. v_deptno := 30;
  92. v_empno := 0;
  93. v_ename := 'Martin';
  94. emp_query(v_deptno, v_empno, v_ename, v_job, v_hiredate, v_sal);
  95. DBMS_OUTPUT.PUT_LINE('Department : ' || v_deptno);
  96. DBMS_OUTPUT.PUT_LINE('Employee No: ' || v_empno);
  97. DBMS_OUTPUT.PUT_LINE('Name : ' || v_ename);
  98. DBMS_OUTPUT.PUT_LINE('Job : ' || v_job);
  99. DBMS_OUTPUT.PUT_LINE('Hire Date : ' || v_hiredate);
  100. DBMS_OUTPUT.PUT_LINE('Salary : ' || v_sal);
  101. EXCEPTION
  102. WHEN TOO_MANY_ROWS THEN
  103. DBMS_OUTPUT.PUT_LINE('More than one employee was selected');
  104. WHEN NO_DATA_FOUND THEN
  105. DBMS_OUTPUT.PUT_LINE('No employees were selected');
  106. END;

CREATE FUNCTION

  1. CREATE OR REPLACE FUNCTION emp_comp (
  2. p_sal NUMBER,
  3. p_comm NUMBER
  4. ) RETURN NUMBER
  5. IS
  6. BEGIN
  7. RETURN (p_sal + NVL(p_comm, 0)) * 24;
  8. END;
  9. --
  10. -- Function that gets the next number from sequence, 'next_empno',
  11. -- and make sure that it is not already in use as an employee number.
  12. --
  13. CREATE OR REPLACE FUNCTION new_empno RETURN NUMBER
  14. IS
  15. v_cnt INTEGER := 1;
  16. v_new_empno NUMBER;
  17. BEGIN
  18. WHILE v_cnt > 0 LOOP
  19. SELECT next_empno.nextval INTO v_new_empno FROM dual;
  20. SELECT COUNT(*) INTO v_cnt FROM emp WHERE empno = v_new_empno;
  21. END LOOP;
  22. RETURN v_new_empno;
  23. END;
  24. --
  25. -- EDB-SPL function that adds a new clerk to table 'emp'. This function
  26. -- uses package 'emp_admin'.
  27. --
  28. CREATE OR REPLACE FUNCTION hire_clerk (
  29. p_ename VARCHAR2,
  30. p_deptno NUMBER
  31. ) RETURN NUMBER
  32. IS
  33. v_empno NUMBER(4);
  34. v_ename VARCHAR2(10);
  35. v_job VARCHAR2(9);
  36. v_mgr NUMBER(4);
  37. v_hiredate DATE;
  38. v_sal NUMBER(7,2);
  39. v_comm NUMBER(7,2);
  40. v_deptno NUMBER(2);
  41. BEGIN
  42. v_empno := new_empno;
  43. INSERT INTO emp VALUES (v_empno, p_ename, 'CLERK', 7782,
  44. TRUNC(SYSDATE), 950.00, NULL, p_deptno);
  45. SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno INTO
  46. v_empno, v_ename, v_job, v_mgr, v_hiredate, v_sal, v_comm, v_deptno
  47. FROM emp WHERE empno = v_empno;
  48. DBMS_OUTPUT.PUT_LINE('Department : ' || v_deptno);
  49. DBMS_OUTPUT.PUT_LINE('Employee No: ' || v_empno);
  50. DBMS_OUTPUT.PUT_LINE('Name : ' || v_ename);
  51. DBMS_OUTPUT.PUT_LINE('Job : ' || v_job);
  52. DBMS_OUTPUT.PUT_LINE('Manager : ' || v_mgr);
  53. DBMS_OUTPUT.PUT_LINE('Hire Date : ' || v_hiredate);
  54. DBMS_OUTPUT.PUT_LINE('Salary : ' || v_sal);
  55. DBMS_OUTPUT.PUT_LINE('Commission : ' || v_comm);
  56. RETURN v_empno;
  57. EXCEPTION
  58. WHEN OTHERS THEN
  59. DBMS_OUTPUT.PUT_LINE('The following is SQLERRM:');
  60. DBMS_OUTPUT.PUT_LINE(SQLERRM);
  61. DBMS_OUTPUT.PUT_LINE('The following is SQLCODE:');
  62. DBMS_OUTPUT.PUT_LINE(SQLCODE);
  63. RETURN -1;
  64. END;
  65. --
  66. -- PostgreSQL PL/pgSQL function that adds a new salesman
  67. -- to table 'emp'.
  68. --
  69. CREATE OR REPLACE FUNCTION hire_salesman (
  70. p_ename VARCHAR,
  71. p_sal NUMERIC,
  72. p_comm NUMERIC
  73. ) RETURNS NUMERIC
  74. AS $$
  75. DECLARE
  76. v_empno NUMERIC(4);
  77. v_ename VARCHAR(10);
  78. v_job VARCHAR(9);
  79. v_mgr NUMERIC(4);
  80. v_hiredate DATE;
  81. v_sal NUMERIC(7,2);
  82. v_comm NUMERIC(7,2);
  83. v_deptno NUMERIC(2);
  84. BEGIN
  85. v_empno := new_empno();
  86. INSERT INTO emp VALUES (v_empno, p_ename, 'SALESMAN', 7698,
  87. CURRENT_DATE, p_sal, p_comm, 30);
  88. SELECT INTO
  89. v_empno, v_ename, v_job, v_mgr, v_hiredate, v_sal, v_comm, v_deptno
  90. empno, ename, job, mgr, hiredate, sal, comm, deptno
  91. FROM emp WHERE empno = v_empno;
  92. RAISE INFO 'Department : %', v_deptno;
  93. RAISE INFO 'Employee No: %', v_empno;
  94. RAISE INFO 'Name : %', v_ename;
  95. RAISE INFO 'Job : %', v_job;
  96. RAISE INFO 'Manager : %', v_mgr;
  97. RAISE INFO 'Hire Date : %', v_hiredate;
  98. RAISE INFO 'Salary : %', v_sal;
  99. RAISE INFO 'Commission : %', v_comm;
  100. RETURN v_empno;
  101. EXCEPTION
  102. WHEN OTHERS THEN
  103. RAISE INFO 'The following is SQLERRM:';
  104. RAISE INFO '%', SQLERRM;
  105. RAISE INFO 'The following is SQLSTATE:';
  106. RAISE INFO '%', SQLSTATE;
  107. RETURN -1;
  108. END;

CREATE RULE

  1. CREATE OR REPLACE RULE salesemp_i AS ON INSERT TO salesemp
  2. DO INSTEAD
  3. INSERT INTO emp VALUES (NEW.empno, NEW.ename, 'SALESMAN', 7698,
  4. NEW.hiredate, NEW.sal, NEW.comm, 30);
  5. CREATE OR REPLACE RULE salesemp_u AS ON UPDATE TO salesemp
  6. DO INSTEAD
  7. UPDATE emp SET empno = NEW.empno,
  8. ename = NEW.ename,
  9. hiredate = NEW.hiredate,
  10. sal = NEW.sal,
  11. comm = NEW.comm
  12. WHERE empno = OLD.empno;
  13. CREATE OR REPLACE RULE salesemp_d AS ON DELETE TO salesemp
  14. DO INSTEAD
  15. DELETE FROM emp WHERE empno = OLD.empno;

CREATE TRIGGER

  1. CREATE OR REPLACE TRIGGER user_audit_trig
  2. AFTER INSERT OR UPDATE OR DELETE ON emp
  3. DECLARE
  4. v_action VARCHAR2(24);
  5. BEGIN
  6. IF INSERTING THEN
  7. v_action := ' added employee(s) on ';
  8. ELSIF UPDATING THEN
  9. v_action := ' updated employee(s) on ';
  10. ELSIF DELETING THEN
  11. v_action := ' deleted employee(s) on ';
  12. END IF;
  13. DBMS_OUTPUT.PUT_LINE('User ' || USER || v_action || TO_CHAR(SYSDATE,'YYYY-MM-DD'));
  14. END;
  15. CREATE OR REPLACE TRIGGER emp_sal_trig
  16. BEFORE DELETE OR INSERT OR UPDATE ON emp
  17. FOR EACH ROW
  18. DECLARE
  19. sal_diff NUMBER;
  20. BEGIN
  21. IF INSERTING THEN
  22. DBMS_OUTPUT.PUT_LINE('Inserting employee ' || :NEW.empno);
  23. DBMS_OUTPUT.PUT_LINE('..New salary: ' || :NEW.sal);
  24. END IF;
  25. IF UPDATING THEN
  26. sal_diff := :NEW.sal - :OLD.sal;
  27. DBMS_OUTPUT.PUT_LINE('Updating employee ' || :OLD.empno);
  28. DBMS_OUTPUT.PUT_LINE('..Old salary: ' || :OLD.sal);
  29. DBMS_OUTPUT.PUT_LINE('..New salary: ' || :NEW.sal);
  30. DBMS_OUTPUT.PUT_LINE('..Raise : ' || sal_diff);
  31. END IF;
  32. IF DELETING THEN
  33. DBMS_OUTPUT.PUT_LINE('Deleting employee ' || :OLD.empno);
  34. DBMS_OUTPUT.PUT_LINE('..Old salary: ' || :OLD.sal);
  35. END IF;
  36. END;

CREATE PACKAGE

  1. CREATE OR REPLACE PACKAGE emp_admin
  2. IS
  3. FUNCTION get_dept_name (
  4. p_deptno NUMBER
  5. ) RETURN VARCHAR2;
  6. FUNCTION update_emp_sal (
  7. p_empno NUMBER,
  8. p_raise NUMBER
  9. ) RETURN NUMBER;
  10. PROCEDURE hire_emp (
  11. p_empno NUMBER,
  12. p_ename VARCHAR2,
  13. p_job VARCHAR2,
  14. p_sal NUMBER,
  15. p_hiredate DATE,
  16. p_comm NUMBER,
  17. p_mgr NUMBER,
  18. p_deptno NUMBER
  19. );
  20. PROCEDURE fire_emp (
  21. p_empno NUMBER
  22. );
  23. END emp_admin;

CREATE PACKAGE BODY

  1. --
  2. -- Package body for the 'emp_admin' package.
  3. --
  4. CREATE OR REPLACE PACKAGE BODY emp_admin
  5. IS
  6. --
  7. -- Function that queries the 'dept' table based on the department
  8. -- number and returns the corresponding department name.
  9. --
  10. FUNCTION get_dept_name (
  11. p_deptno IN NUMBER
  12. ) RETURN VARCHAR2
  13. IS
  14. v_dname VARCHAR2(14);
  15. BEGIN
  16. SELECT dname INTO v_dname FROM dept WHERE deptno = p_deptno;
  17. RETURN v_dname;
  18. EXCEPTION
  19. WHEN NO_DATA_FOUND THEN
  20. DBMS_OUTPUT.PUT_LINE('Invalid department number ' || p_deptno);
  21. RETURN '';
  22. END;
  23. --
  24. -- Function that updates an employee's salary based on the
  25. -- employee number and salary increment/decrement passed
  26. -- as IN parameters. Upon successful completion the function
  27. -- returns the new updated salary.
  28. --
  29. FUNCTION update_emp_sal (
  30. p_empno IN NUMBER,
  31. p_raise IN NUMBER
  32. ) RETURN NUMBER
  33. IS
  34. v_sal NUMBER := 0;
  35. BEGIN
  36. SELECT sal INTO v_sal FROM emp WHERE empno = p_empno;
  37. v_sal := v_sal + p_raise;
  38. UPDATE emp SET sal = v_sal WHERE empno = p_empno;
  39. RETURN v_sal;
  40. EXCEPTION
  41. WHEN NO_DATA_FOUND THEN
  42. DBMS_OUTPUT.PUT_LINE('Employee ' || p_empno || ' not found');
  43. RETURN -1;
  44. WHEN OTHERS THEN
  45. DBMS_OUTPUT.PUT_LINE('The following is SQLERRM:');
  46. DBMS_OUTPUT.PUT_LINE(SQLERRM);
  47. DBMS_OUTPUT.PUT_LINE('The following is SQLCODE:');
  48. DBMS_OUTPUT.PUT_LINE(SQLCODE);
  49. RETURN -1;
  50. END;
  51. --
  52. -- Procedure that inserts a new employee record into the 'emp' table.
  53. --
  54. PROCEDURE hire_emp (
  55. p_empno NUMBER,
  56. p_ename VARCHAR2,
  57. p_job VARCHAR2,
  58. p_sal NUMBER,
  59. p_hiredate DATE,
  60. p_comm NUMBER,
  61. p_mgr NUMBER,
  62. p_deptno NUMBER
  63. )
  64. AS
  65. BEGIN
  66. INSERT INTO emp(empno, ename, job, sal, hiredate, comm, mgr, deptno)
  67. VALUES(p_empno, p_ename, p_job, p_sal,
  68. p_hiredate, p_comm, p_mgr, p_deptno);
  69. END;
  70. --
  71. -- Procedure that deletes an employee record from the 'emp' table based
  72. -- on the employee number.
  73. --
  74. PROCEDURE fire_emp (
  75. p_empno NUMBER
  76. )
  77. AS
  78. BEGIN
  79. DELETE FROM emp WHERE empno = p_empno;
  80. END;
  81. END;
Thank you! We've received your feedback.