Home » SQL & PL/SQL » SQL & PL/SQL » Help with cursor and trigger (Oracle 10g)
Help with cursor and trigger [message #395202] Tue, 31 March 2009 11:19 Go to next message
kokokoko
Messages: 5
Registered: March 2009
Junior Member
Hi,

last week I am trying to learn something about cursors in SQL/PL. I got free access to running Oracle 10g from my school and I want to create database of my web herb encyklopedia users here. I studied something from internet and now I am trying to make some trigger working, which will help me to decide to which group they belong according to email. But I still do not get it, how the cursors in SQL/PL really work.

Basically, problem is that I am not familiar with programming - but i plan to change this asap. Can anybody please help me with code bellow?

I have table freetester.MEE_customers with collumns name, e-mail and schoolmate. And I want my trigger to check each row of the table for the mail something@fjj.sk. Because these are students of the same school as me and I want to give them acces to some more features.

It should check every row

Can you please tell me, what I am doing wrong?

/* trigger*/
create or replace trigger MEE_mailcheck
after insert on freetester.MEE_customers

for each row

declare
  cursor c is select e-mail from freetester.MEE_customers;	
  buffer varchar2(30)
    
begin
   fetch c into buffer
   if owa_pattern.match(buffer, "^[a-zA-Z_\.]+@fjj.sk$") then
   update freetester.MEE_customers.e-mail set schoolmate  = "1"
   else
   update freetester.MEE_customers.e-mail set schoolmate= "0"
   end if;
   DBMS_OUTPUT.PUT_LINE("ALL OK");
      
end

[Updated on: Tue, 31 March 2009 11:46]

Report message to a moderator

Re: Help with cursor and trigger [message #395203 is a reply to message #395202] Tue, 31 March 2009 11:34 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
A lot I'm afraid.

Time to read the manual:
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/toc.htm

Couple of obvious things:
cursors need to be opened and closed.
cursors that can return multiple rows really need to be used in a loop.
updating the row you just inserted in an after statement trigger is generally a bad idea.

I strongly recommend you get to grips with basic PL/SQL before getting into triggers.
Re: Help with cursor and trigger [message #397138 is a reply to message #395202] Thu, 09 April 2009 03:29 Go to previous messageGo to next message
kokokoko
Messages: 5
Registered: March 2009
Junior Member
Okay, I did so. And now I believe I understand it much more. So I prepared another script, which I believe is syntactically right -
SQL developer does not return any mistake - but it also doesn´t returns data.

drop table 'KUC_vysledky';
create table 'KUC_vysledky'  
(
   'id'                 INTEGER                         not null,
   'jmeno'              CHAR(20),
   'prijmeni'           CHAR(20),
   'narodnost'          CHAR(30),
   'oddil'              VARCHAR2(30),
   'nejlepsi_pokus'     NUMBER(3),
   'pocet_pokusu'     NUMBER(1),

   constraint PK_KUC_VYSLEDKY primary key ('id')
);

/*DATA*/
insert into KUC_vysledky (id, jmeno, prijmeni, narodnost, oddil, nejlepsi_pokus, pocet_pokusu)
                 values  ('1','Jaroslav', 'Bába', 'CZE', 'AK SSK Vítkovice', '230', '1' ); 
insert into KUC_vysledky (id, jmeno, prijmeni, narodnost, oddil, nejlepsi_pokus, pocet_pokusu)
                 values  ('2','David', 'Sazima', 'CZE', 'AK Most', '198', '1' ); 
insert into KUC_vysledky (id, jmeno, prijmeni, narodnost, oddil, nejlepsi_pokus, pocet_pokusu)
                 values  ('3','Kamil','Kotík', 'CZE', 'TJ Jiskra Humpolec', '200', '2' ); 


declare
   cursor kurzor
      is select v.jmeno || ' ' || v.prijmeni, v.nejlepsi_pokus
           from KUC_vysledky v;

      cele_jmeno varchar2(50);
      vysledek     number(3,2);
   
begin
      for kurzor in (select avg(v.nejlepsi_pokus)  prumerny_vysledek
        from KUC_vysledky v) 
        loop
        dbms_output.put_line('Průměrný výsledek na HM ČR byl '||kurzor.prumerny_vysledek);
      end loop;

      open kurzor;
      fetch kurzor into cele_jmeno, vysledek;

      dbms_output.put_line(chr(13) || chr(13));
      dbms_output.put_line('Výsledková listina (HM ČR 08 - Praha)');
      

      while kurzor%FOUND loop
        dbms_output.put_line(cele_jmeno || ' dosáhl výkonu ' || vysledek);
        fetch kurzor into into cele_jmeno, vysledek;
      end loop;
      close kurzor;

exception
   when no_data_found then
      raise_application_error(-20000,'zadna data');

   end;


It should be texting cele_jmeno + dosahl vykonu + vysledek in rows. What the heck am I doing wrong this time? Sad

Re: Help with cursor and trigger [message #397144 is a reply to message #397138] Thu, 09 April 2009 03:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ your create table statement is invalid
2/ your code does not compile
SQL> declare
  2     cursor kurzor
  3        is select v.jmeno || ' ' || v.prijmeni, v.nejlepsi_pokus
  4             from KUC_vysledky v;
  5  
  6        cele_jmeno varchar2(50);
  7        vysledek     number(3,2);
  8     
  9  begin
 10        for kurzor in (select avg(v.nejlepsi_pokus)  prumerny_vysledek
 11          from KUC_vysledky v) 
 12          loop
 13          dbms_output.put_line('Prumerný výsledek na HM CR byl '||kurzor.prumerny_vysledek);
 14        end loop;
 15  
 16        open kurzor;
 17        fetch kurzor into cele_jmeno, vysledek;
 18  
 19        dbms_output.put_line(chr(13) || chr(13));
 20        dbms_output.put_line('Výsledková listina (HM CR 08 - Praha)');
 21        
 22  
 23        while kurzor%FOUND loop
 24          dbms_output.put_line(cele_jmeno || ' dosáhl výkonu ' || vysledek);
 25          fetch kurzor into into cele_jmeno, vysledek;
 26        end loop;
 27        close kurzor;
 28  
 29  exception
 30     when no_data_found then
 31        raise_application_error(-20000,'zadna data');
 32  
 33     end;
 34  /
        fetch kurzor into into cele_jmeno, vysledek;
                          *
ERROR at line 25:
ORA-06550: line 25, column 27:
PLS-00103: Encountered the symbol "INTO" when expecting one of the following:
mod <an identifier> <a double-quoted delimited-identifier>
<a bind variable> current sql execute forall merge pipe
ORA-06550: line 26, column 7:
PLS-00103: Encountered the symbol "END"

First post syntaxically valid code.

Regards
Michel
Re: Help with cursor and trigger [message #397194 is a reply to message #397138] Thu, 09 April 2009 06:28 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
In Oracle, you don't wrap column/table names in quotes for DDL statements (CREATE TABLE statements in this case)

In fact, you rarely use a column name in quotes in DML (select, update, insert, delete etc).

This leaves the first part of your test case as:
drop table KUC_vysledky;
create table KUC_vysledky  
(
   id                 INTEGER                         not null,
   jmeno              CHAR(20),
   prijmeni           CHAR(20),
   narodnost          CHAR(30),
   oddil              VARCHAR2(30),
   nejlepsi_pokus     NUMBER(3),
   pocet_pokusu     NUMBER(1),

   constraint PK_KUC_VYSLEDKY primary key (id)
);


You've got 2 mistakes in the code.
1) You've got the key work INTO twice in a row
2) youve defined your variable vysledek as a number (3,2), which means a 3 digit number, two digits of which are to the right of the decimal place. As you're fetching values lie 230,200 into it, it needs to be a number(3,0)

Other than that it works.

From a style point of view, I wouldn't recommend using names for cursor loop variables ('kurzor' in the line "for kurzor in (select avg(v.nejlepsi_pokus) prumerny_vysledek" which are used for other things in the code - it leads to confusion.
Re: Help with cursor and trigger [message #397208 is a reply to message #395202] Thu, 09 April 2009 07:18 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Couple of other obvious points.

1) Your for loop only returns one row. Aggregate functions (avg, max, min, count) without a group by can only ever return one row. So there's no point it being a FOR LOOP and should be rewritten as a SELECT INTO.
2) "while kurzor%FOUND loop" is pretty much the definition of a FOR LOOP so you should write that as a FOR LOOP.
3) Your code doesn't contain anything that'll give a no_data_found. Explicit cursors (ones declared in the declare section) and for loops can't give that error.
4) When declaring local variables to store data retrived from a table it's good practice to type them to the corresponding table column.
So:
vysledek     number(3,2);

becomes
vysledek     kuc_vysledky.nejlepsi_pokus%TYPE;

That avoids any possibility of the problem JRowbottom pointed out with the size mismatch.
Re: Help with cursor and trigger [message #397349 is a reply to message #395202] Fri, 10 April 2009 03:48 Go to previous messageGo to next message
kokokoko
Messages: 5
Registered: March 2009
Junior Member
thanks all for kind answers. I hope that one time i will be able to help you too

Re: Help with cursor and trigger [message #397355 is a reply to message #395202] Fri, 10 April 2009 04:47 Go to previous messageGo to next message
kokokoko
Messages: 5
Registered: March 2009
Junior Member
Okay, now it compiles, but I still can´t get desidered text ouput. It says only "anonymous block completed". So it seems that I must set dbms_output.put_line to work correctly, somehow...

drop table KUC_vysledky;
create table KUC_vysledky  
(
   id                 INTEGER                         not null,
   jmeno              CHAR(20),
   prijmeni           CHAR(20),
   narodnost          CHAR(30),
   oddil              VARCHAR2(30),
   nejlepsi_pokus     NUMBER(3),
   pocet_pokusu     NUMBER(1),

   constraint PK_KUC_VYSLEDKY primary key (id)
);

insert into KUC_vysledky (id, jmeno, prijmeni, narodnost, oddil, nejlepsi_pokus, pocet_pokusu)
                 values  ('1','Jaroslav', 'Bába', 'CZE', 'AK SSK Vítkovice', '230', '1' ); 
insert into KUC_vysledky (id, jmeno, prijmeni, narodnost, oddil, nejlepsi_pokus, pocet_pokusu)
                 values  ('2','David', 'Sazima', 'CZE', 'AK Most', '198', '1' ); 
       
                 
declare
   cursor kurzor
      is select v.jmeno || ' ' || v.prijmeni, v.nejlepsi_pokus
           from KUC_vysledky v;

      cele_jmeno varchar2(50);
      vysledek     kuc_vysledky.nejlepsi_pokus%TYPE;
   
begin
      for kurzor in (select avg(v.nejlepsi_pokus)  prumerny_vysledek
        from KUC_vysledky v) 
        loop
        dbms_output.put_line('Průměrný výsledek na HM ČR byl '||kurzor.prumerny_vysledek);
      end loop;

      open kurzor;
      fetch kurzor into cele_jmeno, vysledek;

      dbms_output.put_line(chr(13) || chr(13));
      dbms_output.put_line('Výsledková listina (HM ČR 08 - Praha)');
      

      while kurzor%FOUND loop
        dbms_output.put_line(cele_jmeno || ' dosáhl výkonu ' || vysledek);
        fetch kurzor into cele_jmeno, vysledek;
      end loop;
      close kurzor;

exception
   when no_data_found then
      raise_application_error(-20000,'zadna data');

   end;   



Ill surelly sort out that exception and for loop things, as you have told me. but that will be on my list after i make it working...

[Updated on: Fri, 10 April 2009 04:52]

Report message to a moderator

Re: Help with cursor and trigger [message #397357 is a reply to message #395202] Fri, 10 April 2009 05:25 Go to previous message
kokokoko
Messages: 5
Registered: March 2009
Junior Member
Oh I finally figured it out, I had to turn DMBS output on in SQL Developer.

So now it is OK. Once again, thanks all for your answer. I really appreciate your help.

Nice weekend to all.
Previous Topic: CASE shorthand?
Next Topic: delete duplicate
Goto Forum:
  


Current Time: Sat Dec 10 16:40:52 CST 2016

Total time taken to generate the page: 0.08544 seconds