oracle 11g PL/SQL Programming学习十七

----------------------------------------------------------------------------
-----------------PL/SQL学习笔记系列 By Cryking-----------------
------------------------转载请注明出处,谢谢!------------------------ 

 

第16章 Web应用开发


OHS结构(oracle HTTP Server)
OHS使用一个HTTP监听来接收和处理URL请求.它是基于apache HTTP服务的.
apache和OHS服务支持CGI(公共网关接口).
oracle 提供一个mod_plsql模块来为PL/SQL程序服务.
OHS定义了一个基于虚拟映射的接入请求和链接之间的关系,叫DAD(数据访问描述符).
DAD包含了管理到Oracle数据库的HTTP管道连接信息.也就是将URL连接信息映射到数据库.
DAD的物理文件位置在:Apache/modplsql/conf/dads.conf(是在$OHS下的)
两种方法实现OHS.
1.包含了Oracle应用服务的9i/10g产品.
2.与Oracle 9i/10g一起的独立组件.


OHS处理流程:
 1.OHS从客户端浏览器接收一个PL/SQL程序或PSP请求.
 2.OHS将请求路由转发到mod_plsql模块.
 3.mod_plsql模块将请求转发到Oracle数据库的存储过程.
   mod_plsql是通过读取DAD的映射信息来转发的.
 4.存储过程通过PL/SQL Web工具包生成一个HTML页面.
   你可以创建基于web的存储过程或者PSP(PL/SQL Server Page).
 5.PL/SQL Web工具包返回HTML格式到调用的存储.
 6.存储过程返回HTML格式化页面到OHS.
 7.OHS将HTML页面返回给请求的客户端浏览器.
--获得PL/SQL WEB工具包版本
23:25:04 SYS@ORCL> SELECT owa_util.get_version AS "PL/SQL Toolkit" FROM dual;


PL/SQL Toolkit
---------------
10.1.2.0.8


1 row selected.


Elapsed: 00:00:00.01



配置OHS
mod_plsql模块描述:
  mod_plsql是一个提供基本服务的框架.它最初是用来作为CORBA(公共对象请求代理结构)的一个暗盒来定义的.
  PL/SQL网关就是由mod_plsql来提供服务的.
mod_plsql主要提供以下服务:
  1.加速你的PL/SQL动态内容
  2.使你的PL/SQL程序成为OHS的一部分.
  3.监视URL到HTML页面整个HTTP请求的访问.
OHS(Oracle HTTP Server)配置
  在配置OHS之前需要你先配置环境.一般的环境配置内容如下:
Unix下:
ORACLE_HOME=/<mnt_point>/<directories>
export ORACLE_HOME
PATH=$ORACLE_HOME/Apache/modplsql/conf:$PATH
export PATH
PATH=$ORACLE_HOME/perl/bin:$PATH;export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib
export LD_LIBRARY_PATH

Windows下:
set ORACLE_HOME=<logical_drive>\<directories>
set PATH=%ORACLE_HOME%\Apache\modplsql\conf;%PATH%
set PATH=%ORACLE_HOME%\perl\bin;%PATH%
set LD_LIBRARY_PATH=%ORACLE_HOME%\lib


在oracle 9i,使用apachectl工具启动和关闭OHS.当你改变了DAD配置文件后,你就需要重启OHS.
在oracle 10g,你需要使用opmnctl工具启动和关闭OHS.
因为10g有一个叫OPMN(oracle进程管理和通知)的工具来支持你的数据库实例的Web视图.


配置mod_plsql的关键在DAD文件.
DAD文件的是在$OHS_HOME/Apache/modplsql/conf/dads.conf 这里.
最小化配置内容如下所示:
<Location /pls>
SetHandler pls_handler
Order deny,allow
Allow from all
AllowOverride None
PlsqlDatabaseUsername <oracle_user_name>
PlsqlDatabasePassword <oracle_password>
PlsqlDatabaseConnectString <hostname>.<domain_name>:<port>:<sid>
PlsqlAuthenticationMode Basic
</Location>
其中:
Location定义了指向特定DAD的URL组件.这个Location标签可以使你定义多个DAD.


当你配置完dads.conf文件后,你将准备配置OHS.为了追踪配置过程,你可以启用日志记录.
修改$OHS_HOME/conf/plsql.conf文件中PlsqlLogEnable的值为on即启用日志.
生成的日志文件在$OHS_HOME/Apache/modplsql/logs/_pls下.


XML DB服务结构
DML DB Server在11g中作为一个嵌入式服务存在.
它是一个嵌入PL/SQL网关的apache HTTP服务.
这个PL/SQL网关也维护了数据库中的DAD(数据访问描述符).
就像标准的apache HTTP服务一样,XML DB服务也支持CGI和apache模块.
不像OHS,XML DB服务需要所有的维护都通过数据库.
为了管理XDB(XML DB)数据库服务,你需要一个XDBA的管理角色权限,而且你需要启用ANONYMOUS用户帐号.
Oracle 11g在使用DBCA安装示范实例的时候会自动安装XML DB服务.


XML DB处理流程:
  1.XML DB监听器接收客户端浏览器的一个PL/SQL存储或PSP请求.
  2.XML DB监听器将请求转发给PL/SQL网关.PL/SQL网关的mod_plsql模块会通过DAD将请求转发到数据库的存储过程.
    在这里PL/SQL网关还能对访问请求进行验证.
  3.PL/SQL网关的mod_plsql模块准备参数并调用数据库的存储过程.
  4.存储过程通过调用PL/SQL web工具包生成HTML页面.
  5.PL/SQL Web工具包返回HTML格式到调用的存储.
  6.存储过程返回格式化的HTML页面到XML DB监听器.
  7.XML DB监听器将返回的HTML页面转给请求的客户端浏览器.
  
你可以配置XML DB来支持静态或动态的认证模型.
使用DBMS_EPG包来配置认证模型.
XML DB服务支持XMLType表和XML库.
用来管理的XMLType的PL/SQL和JAVA API是一种SOA结构(面向服务的结构).
PL/SQL的API定义在DBMS_XDB包中.JAVA是通过JAVA/JNI来支持这种结构.
XML DB主要的配置文件就是xdbconfig.xml,你可以通过下面的方式查询该文件内容:
--查询xdbconfig.xml文件内容
--也可直接在目录$ORACLE_HOME/rdbms/xml下查看到
23:25:14 SYS@ORCL> SELECT dbms_xdb.cfg_get() FROM dual;


DBMS_XDB.CFG_GET()
--------------------------------------------------------------------------------
<xdbconfig xmlns="http://xmlns.oracle.com/xdb/xdbconfig.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xsi:schemaLocation="http://xmlns.oracle.com/xdb/xdbconfig.xsd http://xmlns.oracle.com/xdb/xdbconfig.xsd">
  <sysconfig>
    <acl-max-age>15</acl-max-age>
    <acl-cache-size>32</acl-cache-size>
    <invalid-pathname-chars/>
    <case-sensitive>true</case-sensitive>
    <call-timeout>6000</call-timeout>
    <max-link-queue>65536</max-link-queue>
    <max-session-use>100</max-session-use>
    <persistent-sessions>false</persistent-sessions>
    <default-lock-timeout>3600</default-lock-timeout>
    <xdbcore-logfile-path>/sys/log/xdblog.xml</xdbcore-logfile-path>
    <xdbcore-log-level>0</xdbcore-log-level>
    <resource-view-cache-size>1048576</resource-view-cache-size>
    <protocolconfig>
      <common>
        <extension-mappings>
          <mime-mappings>
            <mime-mapping>
              <extension>au</extension>
              <mime-type>audio/basic</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>avi</extension>
              <mime-type>video/x-msvideo</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>bin</extension>
              <mime-type>application/octet-stream</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>bmp</extension>
              <mime-type>image/bmp</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>css</extension>
              <mime-type>text/css</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>doc</extension>
              <mime-type>application/msword</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>eml</extension>
              <mime-type>message/rfc822</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>gif</extension>
              <mime-type>image/gif</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>htm</extension>
              <mime-type>text/html</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>html</extension>
              <mime-type>text/html</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>jpe</extension>
              <mime-type>image/jpeg</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>jpeg</extension>
              <mime-type>image/jpeg</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>jpg</extension>
              <mime-type>image/jpeg</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>js</extension>
              <mime-type>application/x-javascript</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>jsp</extension>
              <mime-type>text/html</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>mid</extension>
              <mime-type>audio/mid</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>mov</extension>
              <mime-type>video/quicktime</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>movie</extension>
              <mime-type>video/x-sgi-movie</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>mp3</extension>
              <mime-type>audio/mpeg</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>mpe</extension>
              <mime-type>video/mpg</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>mpeg</extension>
              <mime-type>video/mpg</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>mpg</extension>
              <mime-type>video/mpg</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>msa</extension>
              <mime-type>application/x-msaccess</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>msw</extension>
              <mime-type>application/x-msworks-wp</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>pcx</extension>
              <mime-type>application/x-pc-paintbrush</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>pdf</extension>
              <mime-type>application/pdf</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>png</extension>
              <mime-type>image/png</mime-type>
            </mime-mapping>
            <mime-mapping>
              <extension>ppt</extension>
              <mime-type>application/vnd.ms-powerpoint</mime-type>...




1 row selected.


Elapsed: 00:00:00.86


配置XML DB服务
  在oracle 11g中,XML DB服务是一个完全独立的服务.
它有两个主要元素:XML DB监听器和嵌入式PL/SQL网关.
你通过设置init.ora文件中的dispatchers参数来启用XML DB监听器.
之后你可通过DBMS_XDB包来配置监听器.
嵌入式PL/SQL网关管理DAD(数据访问描述符)和mod_plsql组件.
DAD属性在mod_plsql和嵌入式PL/SQL网关中都有使用.下表显示了这两个产品的属性映射关系.

   

    图:mod_plsql_plsqlGateWay



图:mod_plsql_plsqlGateWay2(不全)



mod_plsql的用户不需要设置PlsqlDatabasePassword和PlsqlDatabaseConnectString属性.
缺省值通常已经能满足嵌入式PL/SQL网关的大多数用户.
这里有3种方法为PL/SQL网关创建DADs.
 第一种方法:使用静态认证.
   静态认证是为了迁移提供了证书的mod_plsql应用,提供的证书在DAD配置文件中.它仅依赖于模式用户名和密码.
 第二种方法:使用动态认证
   动态认证是需要用户通过浏览器来认证.也就是我们所熟知的基于HTTP的认证,它以明文方式发送用户证书.
   XML DB服务缺省以该方式进行认证.
 第三种方法:使用匿名认证
   oracle Application Express就是使用这种方式连接数据库.


oracle 11g给你提供了一个诊断脚本epgstat.sql,用来帮助你理解XML DB服务的当前配置.
该脚本在$ORACLE_HOME/rdbms/admin目录下.
该脚本检查以下项目:
  1.XML DB服务的HTTP和FTP端口配置(缺省为0)
  2.DAD虚拟映射
  3.DAD属性
  4.DAD认证
  5.ANONYMOUS用户状态(缺省ANONYMOUS用户为expired和locked状态)
  6.ANONYMOUS用户能否访问XML DB库(缺省情况下禁止访问)
需要使用SYS用户来运行此脚本.如果其他用户来执行,需要XDBADMIN角色权限.


在你开始认证之前,你应该先设置HTTP监听端口.(使用DBMS_XDB.SETHTTPPORT来设置)
17:47:02 SYS@cry> exec DBMS_XDB.SETHTTPPORT(8080);


PL/SQL procedure successfully completed.


Elapsed: 00:00:13.59

使用dbms_xdb.getlistenerendpoint来查看当前监听器端口配置
21:59:14 SYS@ORCL> DECLARE
21:59:31   2    endpoint NUMBER := 1;
21:59:31   3    host     VARCHAR2(40);
21:59:31   4    port     NUMBER := -1;
21:59:31   5    protocol NUMBER := -1;
21:59:31   6  BEGIN
21:59:31   7    dbms_xdb.getlistenerendpoint(endpoint, host, port, protocol);
21:59:31   8    dbms_output.put_line('port [' || port || ']');
21:59:31   9  END;
21:59:33  10  /
port [8080]


PL/SQL procedure successfully completed.


Elapsed: 00:00:00.05


有三个步骤来创建DAD.
  1.创建它;2.映射到一个数据库用户;3.授权数据库用户使用它.
 你通过映射一个虚拟目录到XML DB服务来创建DAD.(使用DBMS_EPG.CREATE_DAD创建)


配置静态认证
 静态认证就是直接使用在DAD中存储了用户名和密码的mod_plsql用户.
意思就是浏览器用户查看web页面不需要输入任何认证,这在需要显示动态非安全的web页面是很好的.
22:00:33 SYS@ORCL> EXEC DBMS_EPG.CREATE_DAD('SCOTT','/scottweb/*')


PL/SQL procedure successfully completed.


Elapsed: 00:00:00.19


在创建DAD之后,你需要设置database-username属性.
22:15:15 SYS@ORCL> EXEC DBMS_EPG.SET_DAD_ATTRIBUTE('SCOTT','database-username','ANONYMOUS');


PL/SQL procedure successfully completed.


Elapsed: 00:00:00.19

注:DAD属性是大小写敏感的,并且必须小写.


22:16:17 SYS@ORCL> EXEC DBMS_EPG.authorize_dad('SCOTT','SCOTT');


PL/SQL procedure successfully completed.


Elapsed: 00:00:00.11


你可以将执行dbms_epg包的权限赋予用户,然后由用户自己来验证自己.
现在你已经有了授权的DAD,接下来你需要通过赋予权限和创建同义词来配置数据库.



配置动态认证
  动态认证基于HTTP认证授权.
  意思就是在任何时候,用户访问Web页面必须有数据库用户名和密码.
  大多数安全用户不喜欢这种方式,因为它是以明文方式传输这些信息的.
配置方法:
22:19:13 SYS@ORCL> EXEC DBMS_EPG.CREATE_DAD('DYNAMIC_SCOTT','/pls/*');


PL/SQL procedure successfully completed.


Elapsed: 00:00:00.15
22:27:08 SYS@ORCL> EXEC DBMS_EPG.SET_DAD_ATTRIBUTE('DYNAMIC_SCOTT','database-username','SCOTT');


PL/SQL procedure successfully completed.


Elapsed: 00:00:00.15



配置匿名认证
匿名认证是连接到一个没有任何数据的中立模式.
ANONYMOUS用户就是设计用来支持这种模型的.

--解锁ANONYMOUS用户并设置密码、赋予创建同义词权限
22:27:32 SYS@ORCL> ALTER USER anonymous ACCOUNT UNLOCK;


User altered.


Elapsed: 00:00:00.11
22:30:50 SYS@ORCL> ALTER USER anonymous IDENTIFIED BY anonymous;


User altered.


Elapsed: 00:00:00.05
22:31:24 SYS@ORCL> GRANT CREATE ANY SYNONYM TO anonymous;


Grant succeeded.


Elapsed: 00:00:00.06
--创建DAD
22:32:00 SYS@ORCL> EXEC DBMS_EPG.CREATE_DAD('SCOTT','/scottweb/*');


PL/SQL procedure successfully completed.


Elapsed: 00:00:00.16
--配置认证方式
22:33:27 SYS@ORCL> EXEC DBMS_EPG.SET_DAD_ATTRIBUTE('SCOTT','database-username','ANONYMOUS');


PL/SQL procedure successfully completed.


Elapsed: 00:00:00.08
--授权用户SCOTT
22:34:17 SYS@ORCL> EXEC DBMS_EPG.authorize_dad('SCOTT','SCOTT');


PL/SQL procedure successfully completed.


Elapsed: 00:00:00.11

我们下面演示的web页面都是以匿名认证来进行的.



创建启用web的PL/SQL存储过程
当存储过程支持web应用时,它的工作方式有些不同.它们生成的HTML输出流格式在SQL*Plus环境下无法查看.
只能通过mod_plsql在web网页中查看.web应用的存储过程需要PL/SQL web工具包来支持,PL/SQL web工具包包含了一系列的包.
如:HTP包、HTF包、OWA_CACHE包、OWA包、OWA_CUSTOM包、OWA_IMAGE包、OWA_OPT_LOCK包、OWA_PATTERN包等.


创建无参数的web应用存储过程
22:39:43 SCOTT@orcl> CREATE OR REPLACE PROCEDURE HelloWorldProcedure1 AS
22:46:32   2  BEGIN
22:46:32   3    -- Set an HTML meta tag and render page.
22:46:32   4    owa_util.mime_header('text/html'); -- <META Content-type:text/html>
22:46:32   5    htp.htmlopen; -- <HTML>
22:46:32   6    htp.headopen; -- <HEAD>
22:46:32   7    htp.htitle('HelloWorldProcedure1'); -- <TITLE>HelloWorldProcedure</TITLE>
22:46:32   8    htp.headclose; -- </HEAD>
22:46:32   9    htp.bodyopen; -- <BODY>
22:46:32  10    htp.line; -- <HR>
22:46:32  11    htp.print('Hello world.'); -- Hello world.
22:46:32  12    htp.line; -- <HR>
22:46:32  13    htp.bodyclose; -- </BODY>
22:46:32  14    htp.htmlclose; -- </HTML>
22:46:32  15  END HelloWorldProcedure1;
22:46:34  16  /


Procedure created.


Elapsed: 00:00:00.33

给anonymous用户赋予执行存储过程权限
22:46:36 SCOTT@orcl> GRANT EXECUTE ON helloworldprocedure1 TO anonymous;


Grant succeeded.


Elapsed: 00:00:00.04
22:49:57 SCOTT@orcl> conn anonymous/anonymous@orcl
Connected.
22:50:41 ANONYMOUS@orcl> CREATE SYNONYM helloworldprocedure1 FOR scott.helloworldprocedure1;


Synonym created.


Elapsed: 00:00:00.17


完成上述操作之后,我们在浏览器里就可以看到helloworldprocedure1存储过程执行的结果了.
打开浏览器输入:http://192.168.123.203:8080/scottweb/HelloWorldProcedure1 
注:IP输入自己数据库服务器的IP
出现如下界面

    图web_plsql


现在我们创建有参数的web应用存储过程看看
有两种方法来传递参数到web页面,一个是使用HTML表格标记来收集输入或提交的数据;另一个是在URL语句中硬编码.
支持三种类型的参数:NUMBER类型、VARCHAR2类型、PL/SQL集合类型(仅限于NUMMBER或VARCHAR2元素的集合)
示例:(使用URL中硬编码方式)
22:59:38 SCOTT@orcl> CREATE OR REPLACE PROCEDURE HelloWorldProcedure2(who VARCHAR2) AS
22:59:45   2  BEGIN
22:59:45   3    -- Set a HTML MIME content-type before rendering a web page.
22:59:45   4    owa_util.mime_header('text/html'); -- <META Content-type:text/html>
22:59:45   5    htp.htmlopen; -- <HTML>
22:59:45   6    htp.headopen; -- <HEAD>
22:59:45   7    htp.htitle('你好'); -- <TITLE>HelloWorld...</TITLE>
22:59:45   8    htp.headclose; -- </HEAD>
22:59:45   9    htp.bodyopen; -- <BODY>
22:59:45  10    htp.line; -- <HR>
22:59:45  11    htp.print('你好, 欢迎来到' || who || '的世界.'); -- Hello world.
22:59:45  12    htp.line; -- <HR>
22:59:45  13    htp.bodyclose; -- </BODY>
22:59:45  14    htp.htmlclose; -- </HTML>
22:59:45  15  END HelloWorldProcedure2;
22:59:46  16  /


Procedure created.


Elapsed: 00:00:00.12
22:59:48 SCOTT@orcl> GRANT EXECUTE ON helloworldprocedure2 TO anonymous;


Grant succeeded.


Elapsed: 00:00:00.00
23:00:36 SCOTT@orcl> conn anonymous/anonymous@orcl
Connected.
23:01:04 ANONYMOUS@orcl> CREATE SYNONYM helloworldprocedure2 FOR scott.helloworldprocedure2;


Synonym created.


Elapsed: 00:00:00.02

现在输入网址:http://192.168.123.203:8080/scottweb/HelloWorldProcedure2?who=Cryking
后如图:(注意网址中的?who=Cryking为硬编码,Cryking即为存储的传入参数)

                    图:web_plsql2


现在我们来个复杂点的,创建使用集合变量做参数的web应用存储过程
23:07:20 SCOTT@orcl> CREATE OR REPLACE PROCEDURE item1(items OWA_UTIL.ident_arr) AS
23:07:23   2    CURSOR get_items(begin_item_id NUMBER, end_item_id NUMBER) IS
23:07:23   3      SELECT EMPNO AS item_number,
23:07:23   4             ENAME || ': ' || JOB AS item_title,
23:07:23   5             HIREDATE AS release_date
23:07:23   6        FROM EMP
23:07:23   7       WHERE EMPNO BETWEEN begin_item_id AND end_item_id;
23:07:23   8  BEGIN
23:07:23   9    -- Set HTML page rendering tags.
23:07:23  10    htp.htmlopen;
23:07:23  11    htp.headopen;
23:07:23  12    htp.htitle('Item List'); -- Sets the browser window and frame title.
23:07:23  13    htp.headclose;
23:07:23  14    htp.bodyopen;
23:07:23  15    htp.line;
23:07:23  16    -- Use PL/SQL Toolkit to format the page.
23:07:23  17    htp.tableopen(cborder     => 2,
23:07:23  18                  cattributes => 'style=background-color:feedb8');
23:07:23  19    htp.tablerowopen;
23:07:23  20    htp.tabledata(cvalue      => '#',
23:07:23  21                  calign      => 'center',
23:07:23  22                  cattributes => 'style=color:#336699
23:07:23  23  background-color:#cccc99
23:07:23  24  font-weight:bold
23:07:23  25  width=50');
23:07:23  26    htp.tabledata(cvalue      => 'Title',
23:07:23  27                  calign      => 'center',
23:07:23  28                  cattributes => 'style=color:#336699
23:07:23  29  background-color:#cccc99
23:07:23  30  font-weight:bold
23:07:23  31  width=200');
23:07:23  32    htp.tabledata(cvalue      => 'Release Date',
23:07:23  33                  calign      => 'center',
23:07:23  34                  cattributes => 'style=color:#336699
23:07:23  35  background-color:#cccc99
23:07:23  36  font-weight:bold
23:07:23  37  width=100');
23:07:23  38    htp.tablerowclose;
23:07:23  39    -- Use a loop to collect the data.
23:07:23  40    FOR i IN get_items(items(1), items(2)) LOOP
23:07:23  41      htp.tablerowopen;
23:07:23  42      htp.tabledata(cvalue      => i.item_number,
23:07:23  43                    calign      => 'center',
23:07:23  44                    cattributes => 'style=background-color:#f7f7e7');
23:07:23  45      htp.tabledata(cvalue      => i.item_title,
23:07:23  46                    calign      => 'left',
23:07:23  47                    cattributes => 'style=background-color:#f7f7e7');
23:07:23  48      htp.tabledata(cvalue      => i.release_date,
23:07:23  49                    calign      => 'center',
23:07:23  50                    cattributes => 'style=background-color:#f7f7e7');
23:07:23  51      htp.tablerowclose;
23:07:23  52    END LOOP;
23:07:23  53    -- Close the table.
23:07:23  54    htp.tableclose;
23:07:23  55    -- Print a line and close body and page.
23:07:23  56    htp.line;
23:07:23  57    htp.bodyclose;
23:07:23  58    htp.htmlclose;
23:07:23  59  END item1;
23:07:23  60  /


Procedure created.


Elapsed: 00:00:00.18
23:07:26 SCOTT@orcl> GRANT EXECUTE ON item1 TO anonymous;


Grant succeeded.


Elapsed: 00:00:00.01
23:08:03 SCOTT@orcl> conn anonymous/anonymous@orcl
Connected.
23:08:21 ANONYMOUS@orcl> create SYNONYM item1 for scott.item1;


Synonym created.


Elapsed: 00:00:00.02

输入网址:http://192.168.123.203:8080/scottweb/item1?items=7800&items=7900
出现如下图:(注意硬编码传入参数方式)

                                 图:web_plsql3


构建和访问PSPs(PL/SQL Server Pages)
从oracle 8i开始,构建PSP的程序是一个非常有用的工具.
使用PSPs可以创建完全动态的web页面.PSPs的优点是它包含和JSPs非常相似的程序结构.
PSPs可以在程序里包含javaScript或者其他客户端脚本代码.
使用loadpsp工具将PSP程序加载到数据库.但是没有对应的droppsp工具来删除数据库的PSP程序..
删除数据库的PSP程序应使用标准的DDL命令(DROP)来删除.


下面介绍开发和运行带参和不带参数的PSP程序.
首先是不带参数的PSP程序
创建文件HelloWorld1.psp,内容如下:
<%@ plsql language="PL/SQL" %>
<%@ plsql procedure="HelloWorld1" %>
<html>
<title>Expert PL/SQL - HelloWorld1</title>
<head>
</head>
<body>
<%-- Print a plain string. --%>
Hello World!<br /><br />
<%-- Print using the PL/SQL Toolkit --%>
<% htp.print('Hello World!'); %>
</td></tr></table>
</body>
</html>

使用工具loadpsp将该PSP程序加载到数据库中
C:\Users\Administrator>loadpsp -replace -user scott/tiger@orcl HelloWorld1.psp
"C:\Users\Administrator\HelloWorld1.psp": procedure "HelloWorld1" created.


在数据库中查询该PSP程序的具体内容
23:53:35 SCOTT@orcl> SELECT text FROM user_source WHERE name = 'HELLOWORLD1';


TEXT
-------------------------------------------------------------------------------
PROCEDURE HelloWorld1  AS
 BEGIN NULL;
htp.prn('
');
htp.prn('
<html>
<title>Expert PL/SQL - HelloWorld1</title>
<head>
</head>
<body>
');
htp.prn('
Hello World!<br /><br />
');
htp.prn('
');
 htp.print('Hello World!');
htp.prn('
</td></tr></table>
</body>
</html>
');
 END;


23 rows selected.


Elapsed: 00:00:00.09

--使用匿名认证来查看
23:56:05 SCOTT@orcl> grant execute on HELLOWORLD1 to anonymous;


Grant succeeded.


Elapsed: 00:00:00.01
23:56:10 SCOTT@orcl> conn anonymous/anonymous@orcl
Connected.
23:56:33 ANONYMOUS@orcl> create SYNONYM HELLOWORLD1 for scott.HELLOWORLD1;


Synonym created.


Elapsed: 00:00:00.02

查看网址:
http://192.168.123.203:8080/scottweb/HelloWorld1
内容如下:


                                              图psp1




创建带参的PSP程序
创建文件HelloWorld2.psp,内容如下:
<%@ plsql language="PL/SQL" type="PL/SQL type" %>
<%@ plsql procedure="HelloWorld2" %>
<%-- Defines a parameter in a PARAMETER block. --%>
<%@ plsql parameter="who" type="VARCHAR2" default="NULL" %>
<head>
<title>Expert PL/SQL - HelloWorld2</title>
<%!
CURSOR get_user
( requestor VARCHAR2) IS
SELECT 'Hello '|| USER ||' schema, this is a '||requestor||'!' line
FROM dual;
%>
</head>
<body>
<% FOR i IN get_user(who) LOOP %>
<%= i.line %>
<% END LOOP; %>
</body>
</html>

使用工具loadpsp将该PSP程序加载到数据库中
C:\Users\Administrator>loadpsp -replace -user scott/tiger@orcl HelloWorld2.psp
"C:\Users\Administrator\HelloWorld2.psp": procedure "HelloWorld2" created.

同样也使用匿名认证查看:
grant execute on HELLOWORLD2 to anonymous;
create SYNONYM HELLOWORLD2 for scott.HELLOWORLD2;

打开网址:http://192.168.123.203:8080/scottweb/HelloWorld2

查看内容如下:


                    图PSP2


现在我们来个复杂点的PSP程序
创建文件item3.psp,内容如下:
<%@ plsql language="PL/SQL" type="PL/SQL type" %>
<%@ plsql procedure="item3" %>
<%-- Defines a parameter in a PARAMETER block. --%>
<%@ plsql parameter="begin_id" type="NUMBER" default="NULL" %>
<%@ plsql parameter="end_id" type="NUMBER" default="NULL" %>
<head>
<title>Item List PSP</title>
<%!
CURSOR get_items
( begin_item_id NUMBER
, end_item_id NUMBER ) IS
SELECT empno AS item_number
, empno||': '||job AS item_title
, hiredate AS release_date
FROM emp
WHERE empno BETWEEN begin_item_id AND end_item_id; %>
</title>
<body>
<hr>
<table cborder=2 style=background-color:feedb8>
<tr>
<td align="center"
style="color:#336699;background-color:#cccc99;font-eight:bold;width=50">
#
</td>
<td align="center"
style="color:#336699;background-color:#cccc99;font-weight:bold;width=200">
NAME
</td>
<td align="center"
style="color:#336699;background-color:#cccc99;font-weight:bold;width=100">
TENURE
</td>
</tr>
<% FOR i IN get_items(begin_id,end_id) LOOP %>
<tr>
<td align="center"
style="color:#336699;background-color:#f7f7e7">
<%= i.item_number %>
</td>
<td align="center"
style="color:#336699;background-color:#f7f7e7">
<%= i.item_title %>
</td>
<td align="center"
style="color:#336699;background-color:#f7f7e7">
<%= i.release_date %>
</td>
</tr>
<% END LOOP; %>
</table>
<hr />
</body>
</html>

使用工具loadpsp将该PSP程序加载到数据库中
C:\Users\Administrator>loadpsp -replace -user scott/tiger@orcl item3.psp
"C:\Users\Administrator\item3.psp": procedure "item3" created.


同样使用匿名认证查看:
SCOTT下:
grant execute on ITEM3 to anonymous;
anonymous下:
create SYNONYM ITEM3 for scott.ITEM3;


查看网址:http://192.168.123.203:8080/scottweb/item3?begin_id=7800&end_id=7900

结果如图:


            图PSP3


  • 1
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
作者:Steven Feuerstein, Bill Pribyl 出版日期:October 1, 2009 出版社:O'Reilly 页数:1226 ISBN:ISBN-10: 0596514468 ISBN-13: 978-0596514464 文件格式:PDF 文件大小:15.06 MB Review If you’re doing database application development in the Oracle environment, you’re going to have to know PL/SQL, the company’s extended query and update language. If you want your programs to exploit the special capabilities of Oracle software, you’ll need to know the language well. That’s where the third edition of Oracle PL/SQL Programming comes into play. It’s an absolutely comprehensive reference (as well as a rather extensive tutorial) on PL/SQL, ideally suited to answering your questions about how to perform some programming tasks and reminding you of the characteristics of functions, triggers, and other elements of the database programmer’s toolkit. The new edition covers calls to Java methods from within PL/SQL programs, autonomous transactions, object type inheritance, and the new Timestamp and XMLType data types. There’s also more information about server internals–the way PL/SQL programs are run–than before, better enabling readers to optimize their code for fast and safe execution. Steven Feuerstein takes care to explain, with prose and example code, the characteristics of PL/SQL elements. In explaining number conversions, for example, he explores Oracle’s different ways of formatting numbers, then details the behavior of the to_number function under different conditions (with and without a specified format model, and with National Language Support information attached). It’s a helpful approach that will have readers using the index to locate places in which Feuerstein mentions language elements of interest. –David Wall Topics covered: How to use Oracle PL/SQL in all its manifestations through Oracle9i. Fundamentals of program structure (loops, cases, exceptions, etc.) and execution get attention, as do data types, transaction management, triggers, and the object-oriented aspects of the language. There’s also coverage of calls to external Java and C programs. –This text refers to the Paperback edition. Product Description This book is the definitive reference on PL/SQL, considered throughout the database community to be the best Oracle programming book available. Like its predecessors, this fifth edition of Oracle PL/SQL Programming covers language fundamentals, advanced coding techniques, and best practices for using Oracle’s powerful procedural language. Thoroughly updated for Oracle Database 11g Release 2, this edition reveals new PL/SQL features and provides extensive code samples, ranging from simple examples to complex and complete applications, in the book and on the companion website. This indispensable reference for both novices and experienced Oracle programmers will help you: Get PL/SQL programs up and running quickly, with clear instructions for executing, tracing, testing, debugging, and managing PL/SQL code Optimize PL/SQL performance with the aid of a brand-new chapter in the fifth edition Explore datatypes, conditional and sequential control statements, loops, exception handling, security features, globalization and localization issues, and the PL/SQL architecture Understand and use new Oracle Database 11g features, including the edition-based redefinition capability, the function result cache, the new CONTINUE statement, fine-grained dependency tracking, sequences in PL/SQL expressions, supertype invocation from subtypes, and enhancements to native compilation, triggers, and dynamic SQL Use new Oracle Database 11g tools and techniques such as PL/Scope, the PL/SQL hierarchical profiler, and the SecureFiles technology for large objects Build modular PL/SQL applications using procedures, functions, triggers, and packages
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值