如何使用PL/SQL來創(chuàng)建RESTful Web Services

這篇文章主要介紹如何使用PL/SQL來創(chuàng)建RESTful Web Services，文中介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們一定要看完！

Version:

ords.18.1.1.95.1251

jdk1.8.0_171

設(shè)置JDK

export JAVA_HOME=/u/dba/oracle/frank/jdk1.8.0_171
export PATH=$JAVA_HOME/bin:$PATH

設(shè)置ORDS

bash-4.1$ java -jar ords.war install advanced
This Oracle REST Data Services instance has not yet been configured.
Please complete the following prompts

Enter the location to store configuration data:/lv01/apache-tomcat-8.5.31/ords/config
Enter the name of the database server [localhost]:<host name>
Enter the database listen port [1521]:152X
Enter 1 to specify the database service name, or 2 to specify the database SID [1]:2
Enter the database SID [xe]:<sid>
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:
Requires SYS AS SYSDBA to verify Oracle REST Data Services schema.

Enter the database password for SYS AS SYSDBA:
Confirm password:

Retrieving information.
Enter the default tablespace for ORDS_METADATA [SYSAUX]:
Enter the temporary tablespace for ORDS_METADATA [TEMP]:
Enter the default tablespace for ORDS_PUBLIC_USER [USERS]:
Enter the temporary tablespace for ORDS_PUBLIC_USER [TEMP]:
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
Jul 04, 2018 7:07:23 PM  
INFO: Updated configurations: defaults, apex_pu
Installing Oracle REST Data Services version 18.1.1.95.1251
... Log file written to /web01/apache-tomcat-8.5.31/ords/logs/ords_install_core_2018-07-04_190723_00459.log
... Verified database prerequisites
... Created Oracle REST Data Services schema
... Created Oracle REST Data Services proxy user
... Granted privileges to Oracle REST Data Services
... Created Oracle REST Data Services database objects
... Log file written to /web01/apache-tomcat-8.5.31/ords/logs/ords_install_datamodel_2018-07-04_190806_00041.log
... Log file written to /web01/apache-tomcat-8.5.31/ords/logs/ords_install_apex_2018-07-04_190807_00872.log
Completed installation for Oracle REST Data Services version 18.1.1.95.1251. Elapsed time: 00:00:45.790

Enter 1 if you wish to start in standalone mode or 2 to exit [1]:2

在安裝完成以后驗(yàn)證數(shù)據(jù)庫(kù)對(duì)象

bash-4.1$ java -jar ords.war validate <sid>
Requires SYS AS SYSDBA to verify Oracle REST Data Services schema.

Enter the database password for SYS AS SYSDBA:
Confirm password:

Retrieving information.

Oracle REST Data Services will be validated.
Validating Oracle REST Data Services schema version 18.1.1.95.1251
... Log file written to /web01/apache-tomcat-8.5.31/ords/logs/ords_validate_core_2018-07-04_191324_00217.log
Completed validating Oracle REST Data Services version 18.1.1.95.1251.  Elapsed time: 00:00:12.509

view /web01/apache-tomcat-8.5.31/ords/logs/ords_validate_core_2018-07-04_191324_00217.log
[*** script: ords_validate_objects.sql]

Session altered.

INFO: 19:13:27 Validating objects for Oracle REST Data Services.
VALIDATION: 19:13:27 Starting validation for schema: ORDS_METADATA
VALIDATION: 19:13:27 Validating objects
VALIDATION: 19:13:35 Validating ORDS Public Synonyms
VALIDATION: 19:13:36 Total objects: 244, invalid objects: 0
VALIDATION: 19:13:36     72  INDEX
VALIDATION: 19:13:36      1  JOB
VALIDATION: 19:13:36     11  PACKAGE
VALIDATION: 19:13:36     11  PACKAGE BODY
VALIDATION: 19:13:36     43  PUBLIC SYNONYM
VALIDATION: 19:13:36      1  SEQUENCE
VALIDATION: 19:13:36     27  TABLE
VALIDATION: 19:13:36     26  TRIGGER
VALIDATION: 19:13:36     19  TYPE
VALIDATION: 19:13:36      6  TYPE BODY
VALIDATION: 19:13:36     27  VIEW
VALIDATION: 19:13:36 Validation completed.
INFO: 19:13:36 Completed validating objects for Oracle REST Data Services. PL/SQL procedure successfully completed.

添加一個(gè)數(shù)據(jù)庫(kù)map

export JAVA_HOME=/u/dba/oracle/frank/jdk1.8.0_171
export PATH=$JAVA_HOME/bin:$PATH

java -jar ords.war map-url --type base-path /<sid> <sid>
Jul 11, 2018 6:40:33 PM  
INFO: Creating new mapping from: [base-path,/<sid>] to map to: [<sid>,,]

在數(shù)據(jù)庫(kù)里建立做一個(gè)簡(jiǎn)單測(cè)試

sqlplus testuser1/testuser1@<sid> 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;

#enable schema
CONN testuser1/testuser1@<sid>

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;
/ ase ORDS URL : http://<host>:8080/ords/<host>/
Schema (alias): http://<host>.bv.tek.com:8080/ords/<host>/testuser1/
Module        : http://<host>.bv.tek.com:8080/ords/<host>/testuser1/testmodule1/
Template      : http://<host>.bv.tek.com:8080/ords/<host>/testuser1/testmodule1/emp/ 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_pattern        => 'emp/',
    p_method         => 'GET',
    p_source_type    => ORDS.source_type_collection_feed,
    p_source         => 'SELECT * FROM emp',
    p_items_per_page => 0);
    
  COMMIT;
END;

以上是“如何使用PL/SQL來創(chuàng)建RESTful Web Services”這篇文章的所有內(nèi)容，感謝各位的閱讀！希望分享的內(nèi)容對(duì)大家有幫助，更多相關(guān)知識(shí)，歡迎關(guān)注億速云行業(yè)資訊頻道！