Oracle PL/SQL Tutorial/System Packages/HTP
Содержание
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("Hierarchy | Org Long Name |
---|---|
"||idx.product_description||" | "); 18 htp.p(""||idx.rupany_long_name||" | "); 19 htp.p("
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("Hierarchy | Org Long Name |
---|---|
"||idx.product_description||" | "); 19 htp.p(""||idx.rupany_long_name||" | "); 20 htp.p("
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("Hierarchy | Org Long Name |
---|---|
Director | "); 14 htp.p("Office of DirectorInc. | "); 15 htp.p("
Director | "); 18 htp.p("Office | "); 19 htp.p("
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>