从ORACLE范例整理 RESTful 数据服务

从ORACLE范例整理 RESTful 数据服务

1. 按员工ID查询员工信息。HTTP实体标记类型=“Secure Hash”,
   1.1、按员工ID查询员工信息。源类型=“Collection Query Item”,
   完整URL: https://oracleapex.com/ords/hr_zao/hr/employees/:id ,

例如： https://oracleapex.com/ords/hr_zao/hr/employees/7839

1.2、更新员工信息，

参数列表：

2、查询所有员工信息，HTTP实体标记类型=“Secure Hash”,
完整URL: https://oracleapex.com/ords/hr_zao/hr/employees/

效果：

3、查询所有员工信息，源类型=“Collection Query”,

完整URL：https://oracleapex.com/ords/hr_zao/hr/empinfo/

效果：

4、按员工姓名查询

完整URL: https://oracleapex.com/ords/hr_zao/hr/empsec/:empname

例如： https://oracleapex.com/ords/hr_zao/hr/empsec/KING

效果：

5、按员工姓名查询，

完整URL: https://oracleapex.com/ords/hr_zao/hr/empsecformat/:empname

如： https://oracleapex.com/ords/hr_zao/hr/empsecformat/KING

PL/SQL 程序段：

DECLARE

prevdeptno number;

total_rows number;

deptloc varchar2(20);

deptname varchar2(20);

CURSOR getemps is select * from emp

start with ename = :empname

connect by prior empno = mgr

order siblings by deptno, ename;

BEGIN

sys.htp.htmlopen;

sys.htp.headopen;

sys.htp.title('Hierarchical Department Report for Employee '||wwv_flow_escape.html(:empname));

sys.htp.headclose;

sys.htp.bodyopen;

for l_employee in getemps

loop

if l_employee.deptno != prevdeptno or prevdeptno is null then

select dname, loc

into deptname, deptloc

from dept

where deptno = l_employee.deptno;

if prevdeptno is not null then

sys.htp.print('</ul>');

end if;

sys.htp.print('Department ' || wwv_flow_escape.html(deptname) || ' located in ' || wwv_flow_escape.html(deptloc) || '<p/>');

sys.htp.print('<ul>');

end if;

sys.htp.print('<li>' || wwv_flow_escape.html(l_employee.ename) || ', ' || wwv_flow_escape.html(l_employee.empno) || ', ' ||

wwv_flow_escape.html(l_employee.job) || ', ' || wwv_flow_escape.html(l_employee.sal) || '</li>');

prevdeptno := l_employee.deptno;

total_rows := getemps%ROWCOUNT;

end loop;

if total_rows > 0 then

sys.htp.print('</ul>');

end if;

sys.htp.bodyclose;

sys.htp.htmlclose;

END;

效果：

6、新增员工信息。

完整URL: https://oracleapex.com/ords/hr_zao/hr/emp_post_example/

PL/SQL 代码：

DECLARE

new_id integer;

current_date date;

BEGIN

select sysdate into current_date from dual;

insert into emp

(empno, ename, job, mgr, hiredate, sal, comm, deptno)

VALUES

(null, :ename, :ejob, :mgr, current_date, :sal, :comm, :deptno)

returning empno into new_id;

:status_code := 201;

:forward_location := '../employees/'||new_id;

exception

WHEN VALUE_ERROR

THEN

:errmsg := 'SALARY must be a number.';

:status_code := 400;

when others then

:status_code := 400;

:errmsg := sqlerrm;

end;

参数列表：

1. 查询 APEX版本

完整URL: https://oracleapex.com/ords/hr_zao/hr/version/

效果：