Oracle REST 数据服务 (ORDS):使用pl/sql创建rest服务-CSDN博客

本文链接：https://blog.csdn.net/zzzjjjfff/article/details/120986914

创建测试数据库用户

我们需要一个新的数据库用户来进行测试。

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER=pdb1;

DROP USER testuser1 CASCADE;
CREATE USER testuser1 IDENTIFIED BY testuser1
  DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;
  
GRANT CREATE SESSION, CREATE TABLE, CREATE TYPE TO testuser1;

创建并填充 EMP 表

CONN testuser1/testuser1@pdb1

CREATE TABLE EMP (
  EMPNO NUMBER(4,0), 
  ENAME VARCHAR2(10 BYTE), 
  JOB VARCHAR2(9 BYTE), 
  MGR NUMBER(4,0), 
  HIREDATE DATE, 
  SAL NUMBER(7,2), 
  COMM NUMBER(7,2), 
  DEPTNO NUMBER(2,0), 
  CONSTRAINT PK_EMP PRIMARY KEY (EMPNO)
  );
  
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7369,'SMITH','CLERK',7902,to_date('17-DEC-80','DD-MON-RR'),800,null,20);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7499,'ALLEN','SALESMAN',7698,to_date('20-FEB-81','DD-MON-RR'),1600,300,30);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7521,'WARD','SALESMAN',7698,to_date('22-FEB-81','DD-MON-RR'),1250,500,30);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7566,'JONES','MANAGER',7839,to_date('02-APR-81','DD-MON-RR'),2975,null,20);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7654,'MARTIN','SALESMAN',7698,to_date('28-SEP-81','DD-MON-RR'),1250,1400,30);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7698,'BLAKE','MANAGER',7839,to_date('01-MAY-81','DD-MON-RR'),2850,null,30);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7782,'CLARK','MANAGER',7839,to_date('09-JUN-81','DD-MON-RR'),2450,null,10);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7788,'SCOTT','ANALYST',7566,to_date('19-APR-87','DD-MON-RR'),3000,null,20);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7839,'KING','PRESIDENT',null,to_date('17-NOV-81','DD-MON-RR'),5000,null,10);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7844,'TURNER','SALESMAN',7698,to_date('08-SEP-81','DD-MON-RR'),1500,0,30);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7876,'ADAMS','CLERK',7788,to_date('23-MAY-87','DD-MON-RR'),1100,null,20);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7900,'JAMES','CLERK',7698,to_date('03-DEC-81','DD-MON-RR'),950,null,30);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7902,'FORD','ANALYST',7566,to_date('03-DEC-81','DD-MON-RR'),3000,null,20);
insert into EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7934,'MILLER','CLERK',7782,to_date('23-JAN-82','DD-MON-RR'),1300,null,10);
commit;

为schema启用 Oracle REST 数据服务。可以为使用任何唯一且合法的 URL 映射到有要启用的schema，在这个例子中，我们使用了“hr”。

CONN testuser1/testuser1@pdb1

BEGIN
  ORDS.enable_schema(
    p_enabled             => TRUE,
    p_schema              => 'TESTUSER1',
    p_url_mapping_type    => 'BASE_PATH',
    p_url_mapping_pattern => 'hr',
    p_auto_rest_auth      => FALSE
  );
    
  COMMIT;
END;
/

现在可以使用以下基本 URL 引用模式中的 Web 服务。

http://localhost:8080/ords/hr/

创建 GET Web 服务（读取/查询）

本节介绍使用 GET 方法创建 REST Web 服务，通常用于读取数据。为了遵守REST的约定，您不应使用 GET 操作对数据执行任何更改。将此视为仅查询。这种约定也适用其他的操作方法。

快速构建

该DEFINE_SERVICE过程允许您在一个步骤中创建新模块、模板和处理程序。如果模块已经存在，它将被新定义替换。

CONN testuser1/testuser1@pdb1

BEGIN
  ORDS.define_service(
    p_module_name    => 'rest-v1',
    p_base_path      => 'rest-v1/',
    p_pattern        => 'employees/',
    p_method         => 'GET',
    p_source_type    => ORDS.source_type_collection_feed,
    p_source         => 'SELECT * FROM emp',
    p_items_per_page => 0);

  COMMIT;
END;
/

ORDS RESTful Web 服务的基本组成如下。

模块：一个或多个模板的容器，具有关联的路径 (rest-v1/)。
模板：一个或多个处理程序的容器。模板在模块内必须是唯一的，并且与特定路径（employees/）相关联，该路径可能包含也可能不包含参数。
处理程序：指向已完成的实际工作的链接。典型的处理程序方法包括 GET、POST、PUT、DELETE，它们在 HTTP 标头中传递，而不是在 URL 中传递。每个处理程序都与一个特定的源（或动作）相关联，它可以是多种类型。

我们可以在 URL 中看到这个模式来访问这个 Web 服务。浏览器访问URL 的默认方法是 GET，因此列出的最终 URL 将在浏览器中工作。

Base ORDS URL : http://localhost:8080/ords/
Schema (alias): http://localhost:8080/ords/hr/
Module        : http://localhost:8080/ords/hr/rest-v1/
Template      : http://localhost:8080/ords/hr/rest-v1/employees/

可以使用USER_ORDS_%视图查看数据库中的关于ords的定义。

-- Modules
COLUMN name FORMAT A20
COLUMN uri_prefix FORMAT A20

SELECT id, name, uri_prefix
FROM   user_ords_modules
ORDER BY name;

	ID NAME 		URI_PREFIX
---------- -------------------- --------------------
     10006 rest-v1		/rest-v1/

SQL>


-- Templates
COLUMN uri_template FORMAT A20

SELECT id, module_id, uri_template
FROM   user_ords_templates
ORDER BY module_id;

	ID  MODULE_ID URI_TEMPLATE
---------- ---------- --------------------
     10007	10006 employees/

SQL>


-- Handlers.
COLUMN source_type FORMAT A15
COLUMN source FORMAT A20

SELECT id, template_id, source_type, method, source
FROM   user_ords_handlers
ORDER BY id;

        ID TEMPLATE_ID SOURCE_TYPE     METHOD     SOURCE
---------- ----------- --------------- ---------- --------------------
     10008       10007 json/collection GET        SELECT * FROM emp

手动构建

DEFINE_SERVICE创建的服务同样可以使用DEFINE_MODULE,DEFINE_TEMPLATE和DEFINE_HANDLER过程手动构建相同的 Web 服务。以下代码创建了一个与之前定义的类似的 Web 服务。

BEGIN
  ORDS.define_module(
    p_module_name    => 'rest-v2',
    p_base_path      => 'rest-v2/',
    p_items_per_page => 0);
  
  ORDS.define_template(
   p_module_name    => 'rest-v2',
   p_pattern        => 'employees/');

  ORDS.define_handler(
    p_module_name    => 'rest-v2',
    p_pattern        => 'employees/',
    p_method         => 'GET',
    p_source_type    => ORDS.source_type_collection_feed,
    p_source         => 'SELECT * FROM emp',
    p_items_per_page => 0);
    
  COMMIT;
END;
/

使用以下 URL 可以使用 Web 服务。

http://localhost:8080/ords/hr/rest-v2/employees/

多个模板

以下代码使用两个模板创建一个 Web 服务，其中一个模板使用参数返回单个记录。请注意，在关联查询中使用该参数来限制返回的结果。

BEGIN
  ORDS.define_module(
    p_module_name    => 'rest-v3',
    p_base_path      => 'rest-v3/',
    p_items_per_page => 0);
  
  ORDS.define_template(
   p_module_name    => 'rest-v3',
   p_pattern        => 'employees/');

  ORDS.define_handler(
    p_module_name    => 'rest-v3',
    p_pattern        => 'employees/',
    p_method         => 'GET',
    p_source_type    => ORDS.source_type_collection_feed,
    p_source         => 'SELECT * FROM emp',
    p_items_per_page => 0);
    
  ORDS.define_template(
   p_module_name    => 'rest-v3',
   p_pattern        => 'employees/:empno');

  ORDS.define_handler(
    p_module_name    => 'rest-v3',
    p_pattern        => 'employees/:empno',
    p_method         => 'GET',
    p_source_type    => ORDS.source_type_collection_feed,
    p_source         => 'SELECT * FROM emp WHERE empno = :empno',
    p_items_per_page => 0);
    
  COMMIT;
END;
/

使用以下 URL 可以使用 Web 服务。

http://localhost:8080/ords/hr/rest-v3/employees/ 
http://localhost:8080/ords/hr/rest-v3/employees/7499

多个参数

有多种方法可以将多个参数传递给 get 服务。以下代码创建一个 Web 服务以返回结果集。注意参数不是在模板中指定的，而是在处理程序中指定的。

BEGIN
  ORDS.define_module(
    p_module_name    => 'rest-v3b',
    p_base_path      => 'rest-v3b/',
    p_items_per_page => 0);

  ORDS.define_template(
   p_module_name    => 'rest-v3b',
   p_pattern        => 'employees/');

  ORDS.define_handler(
    p_module_name    => 'rest-v3b',
    p_pattern        => 'employees/',
    p_method         => 'GET',
    p_source_type    => ORDS.source_type_collection_feed,
    p_source         => 'SELECT * FROM emp WHERE empno BETWEEN :empno_start AND :empno_end',
    p_items_per_page => 0);
  COMMIT;
END;
/

使用以下 URL 可以使用 Web 服务。

http://localhost:8080/ords/hr/rest-v3b/employees/?empno_start=7876&empno_end=7934

下面的代码重新创建了之前的web服务返回一个结果集，但是参数是在模板中指定的，所以是必填的，位置是固定的。

BEGIN
  ORDS.define_module(
    p_module_name    => 'rest-v3b',
    p_base_path      => 'rest-v3b/',
    p_items_per_page => 0);

  ORDS.define_template(
   p_module_name    => 'rest-v3b',
   p_pattern        => 'employees/:empno_start/:empno_end');

  ORDS.define_handler(
    p_module_name    => 'rest-v3b',
    p_pattern        => 'employees/:empno_start/:empno_end',
    p_method         => 'GET',
    p_source_type    => ORDS.source_type_collection_feed,
    p_source         => 'SELECT * FROM emp WHERE empno BETWEEN :empno_start AND :empno_end',
    p_items_per_page => 0);
  COMMIT;
END;
/

使用以下 URL 可以使用 Web 服务。

http://localhost:8080/ords/hr/rest-v3b/employees/7876/7934

多值参数

在某些情况下需要 Web 服务来处理同一参数的多个值。一种方法是将逗号分隔的值列表作为单个参数值传递。必须对 Web 服务进行编码处理。以下示例创建一个处理员工编号列表的服务。

CREATE OR REPLACE FUNCTION in_list (p_in_list  IN  VARCHAR2)
  RETURN t_in_list_tab PIPELINED
AS
  l_text  VARCHAR2(32767) := p_in_list || ',';
  l_idx   NUMBER;
BEGIN
  LOOP
    l_idx := INSTR(l_text, ',');
    EXIT WHEN NVL(l_idx, 0) = 0;
    PIPE ROW (TRIM(SUBSTR(l_text, 1, l_idx - 1)));
    l_text := SUBSTR(l_text, l_idx + 1);
  END LOOP;

  RETURN;
END;
/

BEGIN
  ORDS.define_module(
    p_module_name    => 'rest-v3c',
    p_base_path      => 'rest-v3c/',
    p_items_per_page => 0);
  
  ORDS.define_template(
   p_module_name    => 'rest-v3c',
   p_pattern        => 'employees/:empno');

  ORDS.define_handler(
    p_module_name    => 'rest-v3c',
    p_pattern        => 'employees/:empno',
    p_method         => 'GET',
    p_source_type    => ORDS.source_type_collection_feed,
    p_source         => 'SELECT *
                         FROM   emp
                         WHERE  empno IN (SELECT * FROM TABLE(in_list(:empno)))
                         ORDER BY ename',
    p_items_per_page => 0);
    
  COMMIT;
END;
/

使用以下 URL 可以使用 Web 服务。

http://localhost:8080/ords/hr/rest-v3c/employees/7876,7934,7782

存储过程 (JSON)

前面的示例使用与查询相关联的处理程序，但它们可以与存储过程相关联。我建议这是启用数据库的 REST 的最佳方法，因为您可以将关系复杂性隐藏在 PL/SQL API 后面。要显示这一点，请创建以下过程，该过程使用APEX_JSON包生成 JSON 结果，如果未安装apex可以自己生成json。

CREATE OR REPLACE PROCEDURE get_emp_json (p_empno IN emp.empno%TYPE DEFAULT NULL) AS
  l_cursor SYS_REFCURSOR;
BEGIN
  
  OPEN l_cursor FOR
    SELECT e.empno AS "empno",
           e.ename AS "employee_name",
           e.job AS "job",
           e.mgr AS "mgr",
           TO_CHAR(e.hiredate,'YYYY-MM-DD') AS "hiredate",
           e.sal AS "sal",
           e.comm  AS "comm",
           e.deptno AS "deptno"
    FROM   emp e
    WHERE  e.empno = DECODE(p_empno, NULL, e.empno, p_empno);

  APEX_JSON.open_object;
  APEX_JSON.write('employees', l_cursor);
  APEX_JSON.close_object;
END;
/


BEGIN
  ORDS.define_module(
    p_module_name    => 'rest-v4',
    p_base_path      => 'rest-v4/',
    p_items_per_page => 0);
  
  ORDS.define_template(
   p_module_name    => 'rest-v4',
   p_pattern        => 'employees/');

  ORDS.define_handler(
    p_module_name    => 'rest-v4',
    p_pattern        => 'employees/',
    p_method         => 'GET',
    p_source_type    => ORDS.source_type_plsql,
    p_source         => 'BEGIN get_emp_json; END;',
    p_items_per_page => 0);
    
  ORDS.define_template(
   p_module_name    => 'rest-v4',
   p_pattern        => 'employees/:empno');

  ORDS.define_handler(
    p_module_name    => 'rest-v4',
    p_pattern        => 'employees/:empno',
    p_method         => 'GET',
    p_source_type    => ORDS.source_type_plsql,
    p_source         => 'BEGIN get_emp_json(:empno); END;',
    p_items_per_page => 0);
    
  COMMIT;
END;
/

使用以下 URL 可以使用 Web 服务。

http://localhost:8080/ords/hr/rest-v4/employees/ 
http://localhost:8080/ords/hr/rest-v4/employees/7499

存储过程 (XML)

使用存储过程是一种生成 XML REST Web 服务的简单方法。为了说明这一点，请创建以下过程，该过程使用 SQL/XML 生成 XML 结果。

CREATE OR REPLACE PROCEDURE get_emp_xml (p_empno IN emp.empno%TYPE DEFAULT NULL) AS
  l_clob CLOB;
BEGIN

  SELECT XMLELEMENT("employees",
           XMLAGG(
             XMLELEMENT("emp",
               XMLFOREST(e.empno AS "empno",
                         e.ename AS "employee_name",
                         e.job AS "job",
                         e.mgr AS "mgr",
                         TO_CHAR(e.hiredate,'YYYY-MM-DD') AS "hiredate",
                         e.sal AS "sal",
                         e.comm  AS "comm",
                         e.deptno AS "deptno"
               )
             )
           )
         ).getClobVal()
  INTO   l_clob
  FROM   emp e
  WHERE  e.empno = DECODE(p_empno, NULL, e.empno, p_empno);

  OWA_UTIL.mime_header('text/xml');  
  HTP.print(l_clob);

END;
/


BEGIN
  ORDS.define_module(
    p_module_name    => 'rest-v5',
    p_base_path      => 'rest-v5/',
    p_items_per_page => 0);
  
  ORDS.define_template(
   p_module_name    => 'rest-v5',
   p_pattern        => 'employees/');

  ORDS.define_handler(
    p_module_name    => 'rest-v5',
    p_pattern        => 'employees/',
    p_method         => 'GET',
    p_source_type    => ORDS.source_type_plsql,
    p_source         => 'BEGIN get_emp_xml; END;',
    p_items_per_page => 0);
    
  ORDS.define_template(
   p_module_name    => 'rest-v5',
   p_pattern        => 'employees/:empno');

  ORDS.define_handler(
    p_module_name    => 'rest-v5',
    p_pattern        => 'employees/:empno',
    p_method         => 'GET',
    p_source_type    => ORDS.source_type_plsql,
    p_source         => 'BEGIN get_emp_xml(:empno); END;',
    p_items_per_page => 0);
    
  COMMIT;
END;
/

使用以下 URL 可以使用 Web 服务。

http://localhost:8080/ords/hr/rest-v5/employees/ 
http://localhost:8080/ords/hr/rest-v5/employees/7499

创建 POST Web 服务（创建/插入）

POST 方法通常用于创建操作，例如插入一些数据。请记住，从数据库的角度来看，创建操作与创建员工一样，可能涉及多个操作，而不仅仅是插入。POST 方法期望参数值作为有效负载传递。我使用 Chrome 的“yet another REST client ”扩展程序和curl工具在此处发送请求。

POST 处理程序的源可以是包含插入语句的常规 PL/SQL 块，但最好将其包含在存储过程或包过程中。创建一个存储过程来插入行。

CREATE OR REPLACE PROCEDURE create_employee (
  p_empno     IN  emp.empno%TYPE,
  p_ename     IN  emp.ename%TYPE,
  p_job       IN  emp.job%TYPE,
  p_mgr       IN  emp.mgr%TYPE,
  p_hiredate  IN  VARCHAR2,
  p_sal       IN  emp.sal%TYPE,
  p_comm      IN  emp.comm%TYPE,
  p_deptno    IN  emp.deptno%TYPE
)
AS
BEGIN
  INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
  VALUES (p_empno, p_ename, p_job, p_mgr, TO_DATE(p_hiredate, 'YYYY-MM-DD'), p_sal, p_comm, p_deptno);
EXCEPTION
  WHEN OTHERS THEN
    HTP.print(SQLERRM);
END;
/

BEGIN
  ORDS.define_module(
    p_module_name    => 'rest-v6',
    p_base_path      => 'rest-v6/',
    p_items_per_page => 0);
  
  ORDS.define_template(
   p_module_name    => 'rest-v6',
   p_pattern        => 'employees/');

  ORDS.define_handler(
    p_module_name    => 'rest-v6',
    p_pattern        => 'employees/',
    p_method         => 'POST',
    p_source_type    => ORDS.source_type_plsql,
    p_source         => 'BEGIN
                           create_employee(p_empno    => :empno,
                                           p_ename    => :ename,
                                           p_job      => :job,
                                           p_mgr      => :mgr,
                                           p_hiredate => :hiredate,
                                           p_sal      => :sal,
                                           p_comm     => :comm,
                                           p_deptno   => :deptno);
                         END;',
    p_items_per_page => 0);

  COMMIT;
END;

可以使用以下 URL、方法、头和有效负载调用 Web 服务。

URL        : http://localhost:8080/ords/hr/rest-v6/employees/
Method     : POST
Header     : Content-Type: application/json
Raw Payload: { "empno": 9999, "ename": "HALL", "job": "ANALYST", "mgr": 7782, "hiredate": "2016-01-01", "sal": 1000, "comm": null, "deptno": 10 }

$ curl -i -X POST --data-binary @/tmp/insert-payload.json -H "Content-Type: application/json" http://localhost:8080/ords/hr/rest-v6/employees/

创建 PUT Web 服务（update）

PUT 方法通常用于修改数据并期望在有效负载中传递参数值。如果数据不存在，PUT 操作通常会执行创建

PUT 处理程序的源可以像包含更新语句的常规 PL/SQL 块一样简单，但最好将其包含在存储过程或包过程中。创建一个存储过程来插入行。

CREATE OR REPLACE PROCEDURE amend_employee (
  p_empno     IN  emp.empno%TYPE,
  p_ename     IN  emp.ename%TYPE,
  p_job       IN  emp.job%TYPE,
  p_mgr       IN  emp.mgr%TYPE,
  p_hiredate  IN  VARCHAR2,
  p_sal       IN  emp.sal%TYPE,
  p_comm      IN  emp.comm%TYPE,
  p_deptno    IN  emp.deptno%TYPE
)
AS
BEGIN
  UPDATE emp
  SET ename    = p_ename,
      job      = p_job,
      mgr      = p_mgr,
      hiredate = TO_DATE(p_hiredate, 'YYYY-MM-DD'),
      sal      = p_sal,
      comm     = p_comm,
      deptno   = p_deptno
  WHERE empno  = p_empno;
EXCEPTION
  WHEN OTHERS THEN
    HTP.print(SQLERRM);
END;
/


BEGIN
  ORDS.define_module(
    p_module_name    => 'rest-v7',
    p_base_path      => 'rest-v7/',
    p_items_per_page => 0);
  
  ORDS.define_template(
   p_module_name    => 'rest-v7',
   p_pattern        => 'employees/');

  ORDS.define_handler(
    p_module_name    => 'rest-v7',
    p_pattern        => 'employees/',
    p_method         => 'PUT',
    p_source_type    => ORDS.source_type_plsql,
    p_source         => 'BEGIN
                           amend_employee(p_empno    => :empno,
                                          p_ename    => :ename,
                                          p_job      => :job,
                                          p_mgr      => :mgr,
                                          p_hiredate => :hiredate,
                                          p_sal      => :sal,
                                          p_comm     => :comm,
                                          p_deptno   => :deptno);
                         END;',
    p_items_per_page => 0);

  COMMIT;
END;
/

可以使用以下 URL、方法、标头和有效负载调用 Web 服务。

URL        : http://localhost:8080/ords/hr/rest-v7/employees/
Method     : PUT
Header     : Content-Type: application/json
Raw Payload: { "empno": 9999, "ename": "WOOD", "job": "ANALYST", "mgr": 7782, "hiredate": "2016-01-01", "sal": 1000, "comm": null, "deptno": 20 }

$ curl -i -X PUT --data-binary @/tmp/update-payload.json -H "Content-Type: application/json" http://localhost:8080/ords/hr/rest-v7/employees/

创建 DELETE Web 服务

与 POST 和 PUT 方法一样，DELETE 处理程序的源可以像包含删除语句的 PL/SQL 块一样简单，创建一个存储过程来删除行。

CREATE OR REPLACE PROCEDURE remove_employee (
  p_empno  IN  emp.empno%TYPE
)
AS
BEGIN
  DELETE FROM emp WHERE empno = p_empno;
EXCEPTION
  WHEN OTHERS THEN
    HTP.print(SQLERRM);
END;
/

BEGIN
  ORDS.define_module(
    p_module_name    => 'rest-v8',
    p_base_path      => 'rest-v8/',
    p_items_per_page => 0);
  
  ORDS.define_template(
   p_module_name    => 'rest-v8',
   p_pattern        => 'employees/');

  ORDS.define_handler(
    p_module_name    => 'rest-v8',
    p_pattern        => 'employees/',
    p_method         => 'DELETE',
    p_source_type    => ORDS.source_type_plsql,
    p_source         => 'BEGIN
                           remove_employee(p_empno => :empno);
                         END;',
    p_items_per_page => 0);

  COMMIT;
END;
/

可以使用以下 URL、方法、标头和有效负载调用 Web 服务

URL        : http://localhost:8080/ords/hr/rest-v8/employees/
Method     : DELETE
Header     : Content-Type: application/json
Raw Payload: { "empno": 9999 }

$ curl -i -X DELETE --data-binary @/tmp/delete-payload.json -H "Content-Type: application/json" http://localhost:8080/ords/hr/rest-v8/employees/

完整示例

上面的每个示例都被定义为一个单独的模块，其中大多数都有一个带有单个处理程序的模板。这并不是将提供多少 Web 服务的真实反映。下面的示例将上述模板和处理程序组合到一个 Web 服务中，允许插入、更新、删除和查询记录。它使用了前面几节中定义的一些存储过程。

BEGIN
  ORDS.define_module(
    p_module_name    => 'rest-v9',
    p_base_path      => 'rest-v9/',
    p_items_per_page => 0);
  
  ORDS.define_template(
   p_module_name    => 'rest-v9',
   p_pattern        => 'employees/');

  -- READ : All records.
  ORDS.define_handler(
    p_module_name    => 'rest-v9',
    p_pattern        => 'employees/',
    p_method         => 'GET',
    p_source_type    => ORDS.source_type_collection_feed,
    p_source         => 'SELECT * FROM emp',
    p_items_per_page => 0);

  -- INSERT
  ORDS.define_handler(
    p_module_name    => 'rest-v9',
    p_pattern        => 'employees/',
    p_method         => 'POST',
    p_source_type    => ORDS.source_type_plsql,
    p_source         => 'BEGIN
                           create_employee (p_empno    => :empno,
                                            p_ename    => :ename,
                                            p_job      => :job,
                                            p_mgr      => :mgr,
                                            p_hiredate => :hiredate,
                                            p_sal      => :sal,
                                            p_comm     => :comm,
                                            p_deptno   => :deptno);
                         END;',
    p_items_per_page => 0);

  -- UPDATE
  ORDS.define_handler(
    p_module_name    => 'rest-v9',
    p_pattern        => 'employees/',
    p_method         => 'PUT',
    p_source_type    => ORDS.source_type_plsql,
    p_source         => 'BEGIN
                           amend_employee(p_empno    => :empno,
                                          p_ename    => :ename,
                                          p_job      => :job,
                                          p_mgr      => :mgr,
                                          p_hiredate => :hiredate,
                                          p_sal      => :sal,
                                          p_comm     => :comm,
                                          p_deptno   => :deptno);
                         END;',
    p_items_per_page => 0);

  -- DELETE
  ORDS.define_handler(
    p_module_name    => 'rest-v9',
    p_pattern        => 'employees/',
    p_method         => 'DELETE',
    p_source_type    => ORDS.source_type_plsql,
    p_source         => 'BEGIN
                           remove_employee(p_empno => :empno);
                         END;',
    p_items_per_page => 0);

  -- READ : One Record
  ORDS.define_template(
   p_module_name    => 'rest-v9',
   p_pattern        => 'employees/:empno');

  ORDS.define_handler(
    p_module_name    => 'rest-v9',
    p_pattern        => 'employees/:empno',
    p_method         => 'GET',
    p_source_type    => ORDS.source_type_collection_feed,
    p_source         => 'SELECT * FROM emp WHERE empno = :empno',
    p_items_per_page => 0);

  COMMIT;
END;
/

Web 服务支持以下 URL、方法、头和有效负载。

READ
====
http://localhost:8080/ords/hr/rest-v9/employees/
http://localhost:8080/ords/hr/rest-v9/employees/7499


INSERT
======
URL        : http://localhost:8080/ords/hr/rest-v9/employees/
Method     : POST
Header     : Content-Type: application/json
Raw Payload: { "empno": 9999, "ename": "HALL", "job": "ANALYST", "mgr": 7782, "hiredate": "2016-01-01", "sal": 1000, "comm": null, "deptno": 10 }

$ curl -i -X POST --data-binary @/tmp/insert-payload.json -H "Content-Type: application/json" http://localhost:8080/ords/hr/rest-v9/employees/


UPDATE
======
URL        : http://localhost:8080/ords/hr/rest-v9/employees/
Method     : PUT
Header     : Content-Type: application/json
Raw Payload: { "empno": 9999, "ename": "WOOD", "job": "ANALYST", "mgr": 7782, "hiredate": "2016-01-01", "sal": 1000, "comm": null, "deptno": 20 }

$ curl -i -X PUT --data-binary @/tmp/update-payload.json -H "Content-Type: application/json" http://localhost:8080/ords/hr/rest-v9/employees/


DELETE
======
URL        : http://localhost:8080/ords/hr/rest-v9/employees/
Method     : DELETE
Header     : Content-Type: application/json
Raw Payload: { "empno": 9999 }

$ curl -i -X DELETE --data-binary @/tmp/delete-payload.json -H "Content-Type: application/json" http://localhost:8080/ords/hr/rest-v9/employees/