Oracle PL/SQL Tutorial/System Packages/HTP

Материал из SQL эксперт
Перейти к: навигация, поиск

Build query form with HTP

   <source lang="sql">

SQL> SQL> SQL> CREATE OR REPLACE PROCEDURE query_form 

 2     (p_table_txt IN VARCHAR2) IS
 3     CURSOR cur_cols IS
 4     SELECT column_name
 5     FROM   user_tab_columns
 6     WHERE  table_name = UPPER(p_table_txt);
 7  BEGIN
 8     HTP.HTMLOPEN;
 9     HTP.HEADOPEN;
10     HTP.HTITLE("Query the " || p_table_txt || " table!");
11     HTP.HEADCLOSE;
12     HTP.BODYOPEN;
13     HTP.FORMOPEN(OWA_UTIL.GET_OWA_SERVICE_PATH||"do_query");
14     HTP.FORMHIDDEN("p_table_txt", p_table_txt);
15     HTP.FORMHIDDEN("COLS", "dummy");
16     FOR cur_cols_rec IN cur_cols LOOP
17        HTP.FORMCHECKBOX("COLS", cur_cols_rec.column_name);
18        HTP.PRINT(cur_cols_rec.column_name);
19        HTP.NL;
20     END LOOP;
21     HTP.FORMSUBMIT(NULL, "Execute Query");
22     HTP.FORMCLOSE;
23     HTP.BODYCLOSE;
24     HTP.HTMLCLOSE;
25  END query_form;
26  /

Procedure created.</source>


Create HTML List tags

   <source lang="sql">

SQL> SQL> SQL> CREATE OR REPLACE PROCEDURE list_tables IS 

 2     CURSOR cur_tables IS
 3        SELECT table_name
 4        FROM   user_tables
 5        ORDER BY table_name;
 6  BEGIN
 7     HTP.HTMLOPEN;
 8     HTP.HEADOPEN;
 9     HTP.TITLE("Display a List");
10     HTP.HEADCLOSE;
11     HTP.BODYOPEN;
12     HTP.ULISTOPEN;
13        FOR cur_tables_rec IN cur_tables LOOP
14           HTP.LISTITEM(cur_tables_rec.table_name);
15        END LOOP;
16     HTP.ULISTCLOSE;
17     HTP.BODYCLOSE;
18     HTP.HTMLCLOSE;
19  END list_tables;
20  /

Procedure created. SQL></source>


Display employee in HTML format using HTP package

   <source lang="sql">

SQL> SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL, 

 2                    ENAME VARCHAR2(10),
 3                    JOB VARCHAR2(9),
 4                    MGR NUMBER(4),
 5                    HIREDATE DATE,
 6                    SAL NUMBER(7, 2),
 7                    COMM NUMBER(7, 2),
 8                    DEPTNO NUMBER(2));

Table created. SQL> SQL> SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); 1 row created. SQL> SQL> CREATE OR REPLACE PROCEDURE show_emps (i_job IN VARCHAR2 DEFAULT "%") 

 2  AS
 3     CURSOR emp_cur
 4     IS
 5        SELECT * FROM emp WHERE job LIKE i_job ORDER BY ename;
 6     emp_rec emp_cur%ROWTYPE;
 7  BEGIN
 8     HTP.print ("<title>EMP table<title>");
9 HTP.print ("
    "); 10 OPEN emp_cur; 11 LOOP 12 FETCH emp_cur INTO emp_rec; 13 EXIT WHEN emp_cur%NOTFOUND; 14 HTP.print ("
  • " || emp_rec.ename || "
    • "); 15 HTP.print ("
  • " || emp_rec.job || "
    • "); 16 HTP.print ("
  • " || emp_rec.hiredate || "
    • "); 17 HTP.print ("
  • " || emp_rec.sal || "
    • "); 
    18     END LOOP;
19     CLOSE emp_cur;
20  END;
21  /

    Procedure created. SQL> SQL> drop table emp; Table dropped. SQL></source>


    Display records in a HTML table

       <source lang="sql">

    SQL> SQL> SQL> create table product( 

     2     product_id number(4)     not null,
 3     product_description varchar2(20) not null
 4  );

    Table created. SQL> SQL> insert into product values (1,"Java"); 1 row created. SQL> insert into product values (2,"Oracle"); 1 row created. SQL> insert into product values (3,"C#"); 1 row created. SQL> insert into product values (4,"Javascript"); 1 row created. SQL> insert into product values (5,"Python"); 1 row created. SQL> SQL> SQL> create table company( 

     2     product_id        number(4)    not null,
 3     company_id          NUMBER(8)    not null,
 4     company_short_name  varchar2(30) not null,
 5     company_long_name   varchar2(60)
 6  );

    Table created. SQL> insert into company values(1,1001,"A Inc.","Long Name A Inc."); 1 row created. SQL> insert into company values(1,1002,"B Inc.","Long Name B Inc."); 1 row created. SQL> insert into company values(1,1003,"C Inc.","Long Name C Inc."); 1 row created. SQL> insert into company values(2,1004,"D Inc.","Long Name D Inc."); 1 row created. SQL> insert into company values(2,1005,"E Inc.","Long Name E Inc."); 1 row created. SQL> insert into company values(2,1006,"F Inc.","Long Name F Inc."); 1 row created. SQL> SQL> SQL> SQL> CREATE OR REPLACE PROCEDURE webProc 

     2  IS
 3  BEGIN
 4    htp.p("<HTML>");
 5    htp.p("<HEAD>");
 6    htp.p("<TITLE>Organization Records</TITLE>");
 7    htp.p("</HEAD>");
 8    htp.p("<BODY>");
    9 htp.p("

    Organization Records

    "); 10 htp.p(""); 11 htp.p(""); 
    12    for idx in (select h.product_description,o.rupany_long_name
13                from company o,product h
14                where o.product_id =h.product_id
15                order by h.product_id )loop
    16 htp.p(""); 17 htp.p(""); 18 htp.p(""); 19 htp.p(""); 20 end loop; 21 htp.p("
    HierarchyOrg Long Name
    "||idx.product_description||""||idx.rupany_long_name||"
    "); 
    22    htp.p("</BODY>");
23    htp.p("</HTML>");
24  end;
25  /

    Procedure created. SQL> SQL> drop table company; Table dropped. SQL> SQL> drop table product; Table dropped. SQL></source>


    Display records in a HTML table with parameters

       <source lang="sql">

    SQL> SQL> SQL> create table product( 

     2     product_id number(4)     not null,
 3     product_description varchar2(20) not null
 4  );

    Table created. SQL> SQL> insert into product values (1,"Java"); 1 row created. SQL> insert into product values (2,"Oracle"); 1 row created. SQL> insert into product values (3,"C#"); 1 row created. SQL> insert into product values (4,"Javascript"); 1 row created. SQL> insert into product values (5,"Python"); 1 row created. SQL> SQL> SQL> create table company( 

     2     product_id        number(4)    not null,
 3     company_id          NUMBER(8)    not null,
 4     company_short_name  varchar2(30) not null,
 5     company_long_name   varchar2(60)
 6  );

    Table created. SQL> insert into company values(1,1001,"A Inc.","Long Name A Inc."); 1 row created. SQL> insert into company values(1,1002,"B Inc.","Long Name B Inc."); 1 row created. SQL> insert into company values(1,1003,"C Inc.","Long Name C Inc."); 1 row created. SQL> insert into company values(2,1004,"D Inc.","Long Name D Inc."); 1 row created. SQL> insert into company values(2,1005,"E Inc.","Long Name E Inc."); 1 row created. SQL> insert into company values(2,1006,"F Inc.","Long Name F Inc."); 1 row created. SQL> SQL> CREATE OR REPLACE PROCEDURE webProc_with_param(ip_product_id NUMBER) 

     2  IS
 3  BEGIN
 4    htp.p("<HTML>");
 5    htp.p("<HEAD>");
 6    htp.p("<TITLE>Organization Records</TITLE>");
 7    htp.p("</HEAD>");
 8    htp.p("<BODY>");
    9 htp.p("

    Organization Records

    "); 10 htp.p(""); 11 htp.p(""); 
    12    for idx in (select h.product_description,o.rupany_long_name
13                from company o,product h
14                where o.product_id =h.product_id
15                and h.product_id =ip_product_id
16                order by h.product_id )loop
    17 htp.p(""); 18 htp.p(""); 19 htp.p(""); 20 htp.p(""); 21 end loop; 22 htp.p("
    HierarchyOrg Long Name
    "||idx.product_description||""||idx.rupany_long_name||"
    "); 
    23    htp.p("</BODY>");
24    htp.p("</HTML>");
25  end;
26  /

    Procedure created. SQL> SQL> SQL> drop table company; Table dropped. SQL> SQL> drop table product; Table dropped.</source>


    Display table names in user_tables in HTML format with HTP.PRINT

       <source lang="sql">

    SQL> SQL> SQL> CREATE OR REPLACE PROCEDURE list_tables IS 

     2     CURSOR cur_tables IS
 3        SELECT table_name
 4        FROM   user_tables
 5        ORDER BY table_name;
 6  BEGIN
 7     HTP.PRINT("<HTML>");
 8     HTP.PRINT("<HEAD>");
 9     HTP.PRINT("<TITLE>Display a List Tables</TITLE>");
11     HTP.PRINT("</HEAD>");
12     HTP.PRINT("<BODY>");
    13 HTP.PRINT("
      "); 14 FOR cur_tables_rec IN cur_tables LOOP 15 HTP.PRINT("
    • " || cur_tables_rec.table_name); 16 END LOOP; 17 HTP.PRINT("
    "); 
    18     HTP.PRINT("</BODY>");
19     HTP.PRINT("</HTML>");
20  END list_tables;
21  /

    Procedure created. SQL></source>


    HTP.PRINT with to_char method

       <source lang="sql">

    SQL> SQL> CREATE OR REPLACE PROCEDURE hockey_pass (p_person_txt IN VARCHAR2) IS lv_assists_num PLS_INTEGER; 

     2  BEGIN
 3     SELECT 1 INTO   lv_assists_num FROM dual;
 4     HTP.PRINT(p_person_txt || " has " || to_char(lv_assists_num) || " assists this season");
 5  END hockey_pass;
 6  /

    Procedure created. SQL> SQL></source>


    output a form with action

       <source lang="sql">

    SQL> SQL> CREATE OR REPLACE PROCEDURE webProc_with_param_form 

     2  IS
 3  BEGIN
 4    htp.p("<HTML>");
 5    htp.p("<HEAD>");
 6    htp.p("<TITLE>Organization Records</TITLE>");
 7    htp.p("</HEAD>");
 8    htp.p("<BODY>");
    9 htp.p("

    Organization Records

    "); 10 htp.p("

    Press the Run button.

    "); 
    11    htp.p("<FORM method="get" action="">");
    12 htp.p("

    Hrc Code 13 <INPUT type="text" name="ip_product_id" maxlength="4" size="4"> 14

    "); 15 htp.p("

    16 <INPUT type="submit" value="Run"> 17

    "); 
    18    htp.p("</FORM>");
19    htp.p("</BODY>");
20    htp.p("</HTML>");
21  end;
22  /

    Procedure created. SQL></source>


    Output a html table

       <source lang="sql">

    SQL> SQL> SQL> CREATE OR REPLACE PROCEDURE p_test 

     2  IS
 3  BEGIN
 4    htp.p("<HTML>");
 5    htp.p("<HEAD>");
 6    htp.p("<TITLE>Records</TITLE>");
 7    htp.p("</HEAD>");
 8    htp.p("<BODY>");
    9 htp.p("

    Records

    "); 10 htp.p(""); 11 htp.p(""); 12 htp.p(""); 13 htp.p(""); 14 htp.p(""); 15 htp.p(""); 16 htp.p(""); 17 htp.p(""); 18 htp.p(""); 19 htp.p(""); 20 htp.p("
    HierarchyOrg Long Name
    DirectorOffice of DirectorInc.
    DirectorOffice
    "); 
    21    htp.p("</BODY>");
22    htp.p("</HTML>");
23  end;
24  /

    Procedure created.</source>


    Use HTP package to create HTML page structure

       <source lang="sql">

    SQL> SQL> SQL> CREATE OR REPLACE PROCEDURE pageopen (p_in_title_txt IN VARCHAR2) IS 

     2  BEGIN
 3     HTP.HTMLOPEN;
 4     HTP.HEADOPEN;
 5     HTP.TITLE (p_in_title_txt);
 6     HTP.HEADCLOSE;
 7     HTP.BODYOPEN;
 8     HTP.HEADER (1, p_in_title_txt, "CENTER");
 9  END pageopen;
10  /

    Procedure created. SQL></source>


    Using HTP.PRINT and HTF.BR to output a HTML

       <source lang="sql">

    SQL> SQL> SQL> CREATE OR REPLACE PROCEDURE showvals 

     2     (p_a_txt IN VARCHAR2 DEFAULT NULL,
 3      p_b_txt IN VARCHAR2 DEFAULT NULL) IS
 4  BEGIN
 5     HTP.PRINT("a = "|| p_a_txt || HTF.BR);
 6     HTP.PRINT("b = "|| p_b_txt || HTF.BR);
 7  END showvals;
 8  /

    Procedure created.

    SQL></source>
Источник — «http://sqle.ru/index.php?title=Oracle_PL/SQL_Tutorial/System_Packages/HTP&oldid=3291»