Oracle PL/SQL Tutorial/System Packages/HTP
Содержание
- 1 Build query form with HTP
- 2 Create HTML List tags
- 3 Display employee in HTML format using HTP package
- 4 Display records in a HTML table
- 5 Display records in a HTML table with parameters
- 6 Display table names in user_tables in HTML format with HTP.PRINT
- 7 HTP.PRINT with to_char method
- 8 output a form with action
- 9 Output a html table
- 10 Use HTP package to create HTML page structure
- 11 Using HTP.PRINT and HTF.BR to output a HTML
Build query form with HTP
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.
Create HTML List tags
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>
Display employee in HTML format using HTP package
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 ("<UL>");
10 OPEN emp_cur;
11 LOOP
12 FETCH emp_cur INTO emp_rec;
13 EXIT WHEN emp_cur%NOTFOUND;
14 HTP.print ("<LI>" || emp_rec.ename || "</LI>");
15 HTP.print ("<LI>" || emp_rec.job || "</LI>");
16 HTP.print ("<LI>" || emp_rec.hiredate || "</LI>");
17 HTP.print ("<LI>" || emp_rec.sal || "</LI>");
18 END LOOP;
19 CLOSE emp_cur;
20 END;
21 /
Procedure created.
SQL>
SQL> drop table emp;
Table dropped.
SQL>
Display records in a HTML table
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("<H1>Organization Records</H1>");
10 htp.p("<TABLE BORDER="1 ">");
11 htp.p("<TR><TH>Hierarchy</TH><TH>Org Long Name</TH></TR>");
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("<TR>");
17 htp.p("<TD>"||idx.product_description||"</TD>");
18 htp.p("<TD>"||idx.rupany_long_name||"</TD>");
19 htp.p("</TR>");
20 end loop;
21 htp.p("</TABLE>");
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>
Display records in a HTML table with parameters
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("<H1>Organization Records</H1>");
10 htp.p("<TABLE BORDER="1 ">");
11 htp.p("<TR><TH>Hierarchy</TH><TH>Org Long Name</TH></TR>");
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("<TR>");
18 htp.p("<TD>"||idx.product_description||"</TD>");
19 htp.p("<TD>"||idx.rupany_long_name||"</TD>");
20 htp.p("</TR>");
21 end loop;
22 htp.p("</TABLE>");
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.
Display table names in user_tables in HTML format with HTP.PRINT
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("<UL>");
14 FOR cur_tables_rec IN cur_tables LOOP
15 HTP.PRINT("<LI>" || cur_tables_rec.table_name);
16 END LOOP;
17 HTP.PRINT("</UL>");
18 HTP.PRINT("</BODY>");
19 HTP.PRINT("</HTML>");
20 END list_tables;
21 /
Procedure created.
SQL>
HTP.PRINT with to_char method
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>
output a form with action
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("<H1>Organization Records</H1>");
10 htp.p("<P>Press the <b>Run </b>button.</P>");
11 htp.p("<FORM method="get" action="">");
12 htp.p("<P>Hrc Code
13 <INPUT type="text" name="ip_product_id" maxlength="4" size="4">
14 </P>");
15 htp.p("<P>
16 <INPUT type="submit" value="Run">
17 </P>");
18 htp.p("</FORM>");
19 htp.p("</BODY>");
20 htp.p("</HTML>");
21 end;
22 /
Procedure created.
SQL>
Output a html table
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("<H1>Records</H1>");
10 htp.p("<TABLE BORDER="1 ">");
11 htp.p("<TR><TH>Hierarchy</TH><TH>Org Long Name</TH></TR>");
12 htp.p("<TR>");
13 htp.p("<TD>Director</TD>");
14 htp.p("<TD>Office of DirectorInc.</TD>");
15 htp.p("</TR>");
16 htp.p("<TR>");
17 htp.p("<TD>Director</TD>");
18 htp.p("<TD>Office</TD>");
19 htp.p("</TR>");
20 htp.p("</TABLE>");
21 htp.p("</BODY>");
22 htp.p("</HTML>");
23 end;
24 /
Procedure created.
Use HTP package to create HTML page structure
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>
Using HTP.PRINT and HTF.BR to output a HTML
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>