1 / 17

ESERCIZIO PL/SQL e PSP

ESERCIZIO PL/SQL e PSP. LO SCHEMA. create table studenti ( nome VARCHAR2(15) not null, cognome VARCHAR2(15) not null, eta NUMBER );. COPIATE I FILES. Copiate i files da \homeserverghellibdl08eserciziesepsp

hamlin
Download Presentation

ESERCIZIO PL/SQL e PSP

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. ESERCIZIO PL/SQL e PSP

  2. LO SCHEMA create table studenti ( nome VARCHAR2(15) not null, cognome VARCHAR2(15) not null, eta NUMBER );

  3. COPIATE I FILES • Copiate i files da \\homeserver\ghelli\bdl08\esercizi\esepsp • oppure da http://www.di.unipi.it/~ghelli/didattica/bdl08/esercizi/esepsp/

  4. CREATE LO SCHEMA • Caricate: • createtable.sql • Risposta: drop table studenti * ERRORE alla riga 1: ORA-00942: table or view does not exist Creata tabella. Creata 1 riga....

  5. Voglio creare la pagina: • <html><head><title>Mostrastudenti</title></head> <body> <h1>Elenco studenti </h1> <table><tr> <td> <i> Cognome </i> </td><td> <i>Nome </i> </td> </tr><tr> <td>Carto</td> <td> Lina</td> </tr><tr> <td>Mando</td> <td> Lino</td> </tr><tr> <td> Aspi</td> <td>Rina</td> </tr><tr> <td>Compi</td> <td>Tino</td> </tr></table></body> </html>

  6. Ecco il codice PL/SQL create or replace PROCEDURE provapl AS beginhtp.prn('<HTML> <HEAD><TITLE>Mostrastudenti</TITLE></HEAD> <BODY><H1>Elenco studenti </H1> <TABLE> <TR> <TD> <I>Cognome</I></TD><TD> <I>Nome </I> </TD> </TR>');for studente in (select nome, cognome from studenti) loop htp.prn('<TR> <TD>' ||studente.cognome);htp.prn('</TD> <TD>' ||studente.nome);htp.prn('</TD> </TR>');end loop;htp.prn('</TABLE> </BODY> </HTML>');end;

  7. Compilate provapl.sql • Compilate provapl.sql • Test: • //oracle1.cli.di.unipi.it/pls/user.provapl

  8. PASSARE UN PARAMETRO create or replace PROCEDURE provaparpl ( ilCognome IN VARCHAR2) AS beginhtp.prn(' <HTML> <HEAD../HEAD> <BODY><H1>Elenco studenti </H1> <TABLE> <TR> <TD> <I>Cognome</I></TD><TD> <I>Nome </I> </TD> </TR> '); for studente in (select nome, cognome from studenti s where s.Cognome = ilCognome) loop htp.prn(' <TR> <TD> ‘ || studente.cognome );htp.prn(' </TD> <TD> ‘ ||studente.nome );htp.prn(' </TD> </TR> ');end loop;htp.prn(' </TABLE> </BODY> </HTML> ');end;

  9. Compilate provaparpl.sql • Compilate provaparpl.sql • Test: //oracle1.cli.di.unipi.it/pls/user.provaparpl Errore //or.../pls/user.provaparpl?ilCognome=Mando Successo //.../pls/user.provaparpl?ilCognome=ghelli Tabella vuota

  10. Un modo più semplice: con un editore html: <HTML><HEAD><TITLE>Mostra studenti</TITLE></HEAD> <BODY><H1>Elenco studenti </H1> <TABLE> <TR><TD> <I><font size=+1> Cognome </font></I> </TD> <TD> <I><font size=+1> Nome </font></I> </TD></TR> <TR> <TD> Mario </TD> <TD> Rossi </TD> </TR> <TR> <TD> Luigi </TD> <TD> Bianchi </TD> </TR> </TABLE></BODY></HTML>

  11. Poi, modificate come segue <%@ page language="PL/SQL" %> <HTML><HEAD><TITLE>Mostra studenti</TITLE></HEAD> <BODY><H1>Elenco studenti </H1> <TABLE> <TR><TD> <I><font size=+1> Cognome </font></I> </TD> <TD> <I><font size=+1> Nome </font></I> </TD></TR> <% for studente in (select nome, cognome from studenti) loop %> <TR> <TD> <%= studente.cognome %> </TD> <TD> <%= studente.nome %> </TD> </TR> <% end loop; %> </TABLE></BODY></HTML>

  12. COMPILATE provapsp.psp • Guardate provapsp.psp • Compilatelo da una command shell (run: cmd, oppure accessoriescommand prompt) • cd C:\...\esepsp • loadpsp –replace –useruser/pwd@oracle1.cli.di.unipi.it/oracle1provapsp.psp • "provapsp.psp": procedure "..." created. • Test: • oracle1.cli.di.unipi.it/pls/user.provapsp • Verifica: • dentro sqlplus worksheet scrivete: • describe provapsp • il sistema risponde: • PROCEDURE provapsp

  13. IL FILE GENERATO create or replace PROCEDURE provapsp AS BEGIN NULL;htp.prn(' <HTML> <HEAD><TITLE>Mostra studenti</TITLE></HEAD> <BODY> <H1>Elenco studenti </H1><TABLE> <TR> <TD> <I><font size=+1> Cognome </font></I> </TD> <TD> <I><font size=+1> Nome </font></I> </TD> </TR> ');for studente in(select nome, cognome from studenti)loophtp.prn(' <TR> <TD> '); htp.prn(studente.cognome); htp.prn(' </TD> <TD> '); htp.prn(studente.nome); htp.prn(' </TD> </TR> ');end loop;htp.prn(' </TABLE> </BODY> </HTML> ');END;

  14. PARAMETRO PSP <%@ page language="PL/SQL" %> <%@ plsql parameter="ilCognome" %> <HTML><HEAD><TITLE>Mostra studenti</TITLE></HEAD> <BODY><H1>Elenco studenti </H1> <TABLE> <TR> <TD> <I> …</I> </TD> </TR> <% for studente in (select nome, cognome from studenti s where (ilCognome is null or s.Cognome like ilCognome)) loop %> <TR> <TD>…</TD> </TR> <% end loop; %> </TABLE></BODY></HTML>

  15. COMPILATE provaparpsp.psp • Guardate provaparpsp.psp • Aprite provaparpsp.psp con Notepad o Wordpad • Compilatelo • loadpsp –replace –useruser/pwd@oracle1.cli.di.unipi.it/oracle1 file.psp • “....psp": procedure "..." created.

  16. TEST • //oracle1.cli.di.unipi.it/pls/user.provaparpsp • Errore • //or.../pls/user.provaparpsp?ilCognome=Mando • Successo • //.../pls/user.provaparpsp?ilCognome= • Mostra il contenuto della tabella

  17. ESERCIZI • Modificate le procedure per mostrare anche l’età • Costruire una pagina parametrica che, ricevuto in input ‘nomeProc’, visualizzi in una tabella il risultato della query: SELECT Line, Text FROM User_source WHERE Name = nomeProc

More Related