全部产品
存储与CDN 数据库 安全 应用服务 数加·人工智能 数加·大数据基础服务 互联网中间件 视频服务 开发者工具 解决方案 物联网
云数据库 RDS 版

附录:PPAS 兼容性说明

更新时间:2017-06-07 13:26:11

附录:PPAS 兼容性说明

通过本文档中的示例,Oracle 用户可以快速了解 PPAS 数据库中的术语及概念,以便在迁移及开发过程中提高效率。

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

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

连接数据库 psql

  1. psql -h ppasaddress.ppas.rds.aliyuncs.com -p 3433 -U myuser -d template1
  2. 用户 myuser 的口令:
  3. psql.bin (9.4.1.3, 服务器 9.3.5.14)
  4. 输入 "help" 来获取帮助信息.
  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, 服务器 9.3.5.14)

创建普通用户 CREATE ROLE

  1. orcl_ppas=> CREATE ROLE scott LOGIN 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

说明:

  • 如果在进行 `ALTER SCHEMA scott OWNER TO scott 之前没有将 scott 加入到 myuser 角色,将会出现如下权限问题。
    1. ERROR: must be member of role "scott"
  • 从安全角度出发,在处理完 OWNER 的授权后,请将 scott 用户移出 myuser 角色以提高安全性。

    连接到 orcl_ppas 数据库

    注意:此步骤十分重要,以下所有操作都是在 scott 账号下进行的,否则所建立的数据表及各种数据库对象将不属于 scott 用户,导致权限问题。

    1. [root@localhost bin]# ./psql -h ppasaddress.ppas.rds.aliyuncs.com -p 3433 -U scott -d orcl_ppas
    2. 用户 scott 的口令:
    3. psql.bin (9.4.1.3, 服务器 9.3.5.14)
    4. 输入 "help" 来获取帮助信息.
    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 ensures 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;
本文导读目录