如何使用PL/SQL来创建RESTful Web Services

57次阅读
没有评论

共计 6511 个字符,预计需要花费 17 分钟才能阅读完成。

这篇文章主要介绍如何使用 PL/SQL 来创建 RESTful Web Services,文中介绍的非常详细,具有一定的参考价值,感兴趣的小伙伴们一定要看完!

Version:

ords.18.1.1.95.1251

jdk1.8.0_171

设置 JDK

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

设置 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

在安装完成以后验证数据库对象

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.

添加一个数据库 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 ,,]

在数据库里建立做一个简单测试

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 =
 

  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 =
   
  COMMIT;
END;

以上是“如何使用 PL/SQL 来创建 RESTful Web Services”这篇文章的所有内容,感谢各位的阅读!希望分享的内容对大家有帮助,更多相关知识,欢迎关注丸趣 TV 行业资讯频道!

正文完
 
丸趣
版权声明:本站原创文章,由 丸趣 2023-07-24发表,共计6511字。
转载说明:除特殊说明外本站除技术相关以外文章皆由网络搜集发布,转载请注明出处。
评论(没有评论)