创建测试数据库用户
我们需要一个新的数据库用户来进行测试。
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/