Oracle REST Data Services (ORDS) 发布 RESTful Web Services 教程

前端之家收集整理的这篇文章主要介绍了Oracle REST Data Services (ORDS) 发布 RESTful Web Services 教程前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

Assumptions and Comments

This article assumes the following.

  • You already have a functioninginstallation of ORDS.
  • The paths for the ORDS configuration match those from the ORDS installation article listed above.
  • You have an Oracle database available. In this article I will be using a 12c database,but it works just the same with an 11g database.
  • You have a way to call the web services. Some GET web services can be called from a browser,but the other methods require some coding,or a REST client. I used "curl" and the "Advanced REST client" extension for Chrome.

The examples in this article are based around single operations against a single table. I don't consider these "realistic" tests by any means. From a performance perspective,web services that perform DML should call transactional APIs,which may perform multiple operations in a single call,rather than effectively exposing table APIs,like we are here.

Create a Test Database User

We need a new database user for our testing.

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 TO testuser1;

Create and populate a copy of the EMP table.

CONN testuser1/testuser1@pdb1

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

There are a number of steps that are necessary for ORDS to publish REST web services from a database user.

Installing ORDS configures a default database connection called "apex",available from the base URL "/ords/". We could use this existing database connection,but instead we will create a new database connection. If you already have a connection you are happy to use ignore the following database connection and URL mapping ORDS commands.

$ cd /u01/ords
$ $JAVA_HOME/bin/java -jar ords.war setup --database pdb1
Enter the name of the database server [ol7-121.localdomain]:
Enter the database listen port [1521]:
Enter 1 to specify the database service name,or 2 to specify the database SID [1]:
Enter the database service name [pdb1]:
Enter 1 if you want to verify/install Oracle REST Data Services schema or 2 to skip this step [1]:
Enter the database password for ORDS_PUBLIC_USER:
Confirm password:
Enter 1 if you want to use PL/SQL Gateway or 2 to skip this step.
If using Oracle Application Express or migrating from mod_plsql then you must enter 1 [1]:2
Nov 29,2016 11:04:54 AM
INFO: Updated configurations: pdb1_pu
Nov 29,2016 11:04:54 AM oracle.dbtools.rt.config.setup.SchemaSetup install
INFO: Oracle REST Data Services schema version 3.0.8.277.08.01 is installed.
$

We create a URL mapping to the new database connection.

$ cd /u01/ords
$ $JAVA_HOME/bin/java -jar ords.war map-url --type base-path /pdb1 pdb1
Nov 29,2016 11:06:25 AM
INFO: Creating new mapping from: [base-path,/pdb1] to map to: [pdb1,]
$

Web services from the connection can now be referenced using the following base URL.

http://ol7-121.localdomain:8080/ords/pdb1/

The final step is to enable REST web services for the test schema itself. We could use any unique and legal URL mapping pattern for the schema,so it is not necessary to expose the schema name as we have done here.

CONN testuser1/testuser1@pdb1

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

Web services from the schema can now be referenced using the following base URL.

http://ol7-121.localdomain:8080/ords/pdb1/testuser1/

We are now ready to start.

Create GET Web Services (Read/SELECT)

This section describes the creation of REST web services using the GET method,which are typically used to read data. To stay true to REST,you should not perform any changes to data with a GET operation. Think of this as query-only. It breaks down the process to give you some indication of the pieces that make up an ORDS RESTful web service. The sections for the other methods,will not be broken down in the same way,but the information in this section is applicable to them also.

Quick Build

TheDEFINE_SERVICEprocedure allows you to create a new module,template and handler in a single step. If the module already exists,it's replaced by the new definition.

CONN testuser1/testuser1@pdb1

BEGIN
  ORDS.define_service(
    p_module_name    => 'testmodule1',p_base_path      => 'testmodule1/',p_pattern        => 'emp/',p_method         => 'GET',p_source_type    => ORDS.source_type_query,p_source         => 'SELECT * FROM emp',p_items_per_page => 0);

  COMMIT;
END;
/

The basic makeup of an ORDS RESTful web service is as follows.

  • Module : A container for one or more templates,with an associated path (testmodule1/).
  • Template : A container for one or more handlers. The template must be unique within the module and is associated with a specific path (emp/),which may or may not include parameters.
  • Handler : A link to the actual work that is done. Typical handler methods include GET,POST,PUT,DELETE,which are passed in the HTTP header,rather than the URL. Each handler is associated with a specific source (or action),which can be of several types.

We can see this pattern in the URL to access this web service. Remember,the default method for a browser URL is GET,so the final URL listed will work in a browser.

Base ORDS URL : http://ol7-121.localdomain:8080/ords/pdb1/
Schema (alias): http://ol7-121.localdomain:8080/ords/pdb1/testuser1/
Module        : http://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule1/
Template      : http://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule1/emp/
Views

We can see the definition in the database using theUSER_ORDS_%views.

-- 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 testmodule1		/testmodule1/

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 emp/

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/query      GET	  SELECT * FROM emp

SQL>
Manual Build

Rather than using theDEFINE_SERVICEprocedure,we can build the same web service manually using theDEFINE_MODULE,DEFINE_TEMPLATEandDEFINE_HANDLERprocedures. The following code creates a similar web service to that defined previously,but this time defining all the pieces manually.

CONN testuser1/testuser1@pdb1

BEGIN
  ORDS.define_module(
    p_module_name    => 'testmodule2',p_base_path      => 'testmodule2/',p_items_per_page => 0);
  
  ORDS.define_template(
   p_module_name    => 'testmodule2',p_pattern        => 'emp/');

  ORDS.define_handler(
    p_module_name    => 'testmodule2',p_items_per_page => 0);
    
  COMMIT;
END;
/

The web service is available using the following URL.

http://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule2/emp/
Multiple Templates

The following code creates a web service with two templates,one of which uses a parameter to return a single record. Notice the parameter is used in the associated query to limit the results returned.

CONN testuser1/testuser1@pdb1

BEGIN
  ORDS.define_module(
    p_module_name    => 'testmodule3',p_base_path      => 'testmodule3/',p_items_per_page => 0);
  
  ORDS.define_template(
   p_module_name    => 'testmodule3',p_pattern        => 'emp/');

  ORDS.define_handler(
    p_module_name    => 'testmodule3',p_items_per_page => 0);
    
  ORDS.define_template(
   p_module_name    => 'testmodule3',p_pattern        => 'emp/:empno');

  ORDS.define_handler(
    p_module_name    => 'testmodule3',p_pattern        => 'emp/:empno',p_source         => 'SELECT * FROM emp WHERE empno = :empno',sans-serif; font-size: 16px;">The web service is available using the following URLs.

http://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule3/emp/
http://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule3/emp/7499
Multiple Parameters

There are a number of ways to pass multiple parameters to a get services,including posting JSON in the URL,but I prefer the following method. The following code creates a web service to return a result set. Notice the parameters are not specified in the template,but are specified in the handler.

CONN testuser1/testuser1@pdb1

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

  ORDS.define_template(
   p_module_name    => 'testmodule3b',p_pattern        => 'emp/');

  ORDS.define_handler(
    p_module_name    => 'testmodule3b',p_source         => 'SELECT * FROM emp WHERE empno BETWEEN :empno_start AND :empno_end',p_items_per_page => 0);
  COMMIT;
END;
/

http://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule3b/emp/?empno_start=7876&empno_end=7934

Stored Procedure (JSON)

The previous examples use handlers associated with queries,but they can be associated with stored procedures. I would suggest this is the best approach to REST enabling the database,as you can hide relational complexity behind a PL/SQL API. To show this,create the following procedure,which uses theAPEX_JSONpackage to generate the JSON results.

CONN / AS SYSDBA
ALTER SESSION SET CONTAINER=pdb1;

GRANT CREATE PROCEDURE TO testuser1;

CONN testuser1/testuser1@pdb1

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,'DD-MON-YYYY') 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;
/

The following code creates a web service which calls this procedure. Notice the source type ofSOURCE_TYPE_PLSQLand a PL/SQL block as the source.

CONN testuser1/testuser1@pdb1

BEGIN
  ORDS.define_module(
    p_module_name    => 'testmodule4',p_base_path      => 'testmodule4/',p_items_per_page => 0);
  
  ORDS.define_template(
   p_module_name    => 'testmodule4',p_pattern        => 'emp/');

  ORDS.define_handler(
    p_module_name    => 'testmodule4',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    => 'testmodule4',p_pattern        => 'emp/:empno');

  ORDS.define_handler(
    p_module_name    => 'testmodule4',p_source         => 'BEGIN get_emp_json(:empno); END;',204); border-radius: 4px;">http://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule4/emp/
http://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule4/emp/7499
Stored Procedure (XML)

Using a stored procedure is a simple way to produce XML REST web services. To show this,which uses SQL/XML to generate the XML results.

CONN testuser1/testuser1@pdb1

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.deptno AS "deptno"
               )
             )
           )
         ).getClobVal()
  INTO   l_clob
  FROM   emp e
  WHERE  e.empno = DECODE(p_empno,p_empno);

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

END;
/

The following code creates a web service that calls this procedure.

CONN testuser1/testuser1@pdb1

BEGIN
  ORDS.define_module(
    p_module_name    => 'testmodule5',p_base_path      => 'testmodule5/',p_items_per_page => 0);
  
  ORDS.define_template(
   p_module_name    => 'testmodule5',p_pattern        => 'emp/');

  ORDS.define_handler(
    p_module_name    => 'testmodule5',p_source         => 'BEGIN get_emp_xml; END;',p_items_per_page => 0);
    
  ORDS.define_template(
   p_module_name    => 'testmodule5',p_pattern        => 'emp/:empno');

  ORDS.define_handler(
    p_module_name    => 'testmodule5',p_source         => 'BEGIN get_emp_xml(:empno); END;',204); border-radius: 4px;">http://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule5/emp/
http://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule5/emp/7499
Create POST Web Services (Create/INSERT)

The POST method is typically used for a create operation,like inserting some data. Remember,from a database perspective,a create operation,like creating an employee,may involve multiple operations,not just inserts. The POST method expects the parameter values to be passed as a payload. I used the "Advanced REST client" extension for Chrome to send the requests here.

The source of the POST handler can be a regular PL/SQL block containing an insert statement,but it makes sense to enclose this in a stored procedure or packaged procedure. Create a stored procedure to insert rows.

CONN testuser1/testuser1@pdb1

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,'DD-MON-YYYY'),p_sal,p_comm,p_deptno);
EXCEPTION
  WHEN OTHERS THEN
    HTP.print(SQLERRM);
END;
/

The following code creates a web service with a POST handler that calls the stored procedure,passing the parameters from the payload.

CONN testuser1/testuser1@pdb1

BEGIN
  ORDS.define_module(
    p_module_name    => 'testmodule6',p_base_path      => 'testmodule6/',p_items_per_page => 0);
  
  ORDS.define_template(
   p_module_name    => 'testmodule6',p_pattern        => 'emp/');

  ORDS.define_handler(
    p_module_name    => 'testmodule6',p_method         => 'POST',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;',sans-serif; font-size: 16px;">The web service can be called using the following URL,header and payload.

URL        : http://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule6/emp/
Method     : POST
Header     : Content-Type: application/json
Raw Payload: { "empno": 9999,"ename": "HALL","job": "ANALYST","mgr": 7782,"hiredate": "01-JAN-2016","sal": 1000,"comm": null,"deptno": 10 }

$ curl -i -X POST --data-binary @/tmp/insert-payload.json -H "Content-Type: application/json" http://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule6/emp/

You can see a more complex example here.

Create PUT Web Services (Amend/UPDATE)

The PUT method is typically used to amend data and expects the parameter values to be passed in the payload. The PUT operation is often expected to perform a create if the data doesn't already exist,but I personally don't like this approach.

The source of the PUT handler can be as simple as a regular PL/sql block containing an update statement,but it makes sense to enclose this in a stored procedure or packaged procedure. Create a stored procedure to update rows.

CONN testuser1/testuser1@pdb1

CREATE OR REPLACE PROCEDURE amend_employee (
  p_empno     IN  emp.empno%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,sal      = p_sal,comm     = p_comm,deptno   = p_deptno
  WHERE empno  = p_empno;
EXCEPTION
  WHEN OTHERS THEN
    HTP.print(sqlERRM);
END;
/

The following code creates a web service with a PUT handler that calls the stored procedure,204); border-radius: 4px;">CONN testuser1/testuser1@pdb1 BEGIN ORDS.define_module( p_module_name => 'testmodule7',p_base_path => 'testmodule7/',p_items_per_page => 0); ORDS.define_template( p_module_name => 'testmodule7',p_pattern => 'emp/'); ORDS.define_handler( p_module_name => 'testmodule7',p_method => 'PUT',p_source => 'BEGIN amend_employee(p_empno => :empno,204); border-radius: 4px;">URL : http://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule7/emp/ Method : PUT Header : Content-Type: application/json Raw Payload: { "empno": 9999,"ename": "WOOD","deptno": 20 } $ curl -i -X PUT --data-binary @/tmp/update-payload.json -H "Content-Type: application/json" http://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule7/emp/

Create DELETE Web Services (Remove/DELETE)

Like the POST and PUT methods,the source of the DELETE handler can be a as simple as a PL/sql block containing a delete statement,but it makes sense to enclose this in a stored procedure or packaged procedure. Create a stored procedure to delete rows.

CONN testuser1/testuser1@pdb1

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;
/

The following code creates a web service with a DELETE handler that calls the stored procedure,passing the parameter from the payload.

CONN testuser1/testuser1@pdb1

BEGIN
  ORDS.define_module(
    p_module_name    => 'testmodule8',p_base_path      => 'testmodule8/',p_items_per_page => 0);
  
  ORDS.define_template(
   p_module_name    => 'testmodule8',p_pattern        => 'emp/');

  ORDS.define_handler(
    p_module_name    => 'testmodule8',p_method         => 'DELETE',p_source         => 'BEGIN
                           remove_employee(p_empno => :empno);
                         END;',204); border-radius: 4px;">URL        : http://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule8/emp/
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://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule8/emp/
Complete Example

Each of the examples above is defined as a separate module,most of which have a single template with a single handler. This is not a true reflection of how many web services will be presented. The example below combines the templates and handlers above into a single web service,allowing records to be inserted,updated,deleted and queried. It uses some of the stored procedures defined in the prevIoUs sections.

CONN testuser1/testuser1@pdb1

BEGIN
  ORDS.define_module(
    p_module_name    => 'testmodule9',p_base_path      => 'testmodule9/',p_items_per_page => 0);
  
  ORDS.define_template(
   p_module_name    => 'testmodule9',p_pattern        => 'emp/');

  -- READ : All records.
  ORDS.define_handler(
    p_module_name    => 'testmodule9',p_items_per_page => 0);

  -- INSERT
  ORDS.define_handler(
    p_module_name    => 'testmodule9',p_source         => 'BEGIN
                           create_employee (p_empno    => :empno,p_items_per_page => 0);

  -- UPDATE
  ORDS.define_handler(
    p_module_name    => 'testmodule9',p_items_per_page => 0);

  -- DELETE
  ORDS.define_handler(
    p_module_name    => 'testmodule9',p_items_per_page => 0);

  -- READ : One Record
  ORDS.define_template(
   p_module_name    => 'testmodule9',p_pattern        => 'emp/:empno');

  ORDS.define_handler(
    p_module_name    => 'testmodule9',sans-serif; font-size: 16px;">The web service supports the following URLs,methods,headers and payloads.

READ
====
http://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule9/emp/
http://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule9/emp/7499


INSERT
======
URL        : http://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule9/emp/
Method     : POST
Header     : Content-Type: application/json
Raw Payload: { "empno": 9999,"deptno": 10 }

$ curl -i -X POST --data-binary @/tmp/insert-payload.json -H "Content-Type: application/json" http://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule9/emp/


UPDATE
======
URL        : http://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule9/emp/
Method     : PUT
Header     : Content-Type: application/json
Raw Payload: { "empno": 9999,"deptno": 20 }

$ curl -i -X PUT --data-binary @/tmp/update-payload.json -H "Content-Type: application/json" http://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule9/emp/


DELETE
======
URL        : http://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule9/emp/
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://ol7-121.localdomain:8080/ords/pdb1/testuser1/testmodule9/emp/
原文链接:https://www.f2er.com/oracle/208601.html

猜你在找的Oracle相关文章