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  |
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 #397138 is a reply to message #395202] |
Thu, 09 April 2009 03:29   |
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? 
|
|
|
Re: Help with cursor and trigger [message #397144 is a reply to message #397138] |
Thu, 09 April 2009 03:44   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
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   |
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   |
cookiemonster
Messages: 13965 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:
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 #397355 is a reply to message #395202] |
Fri, 10 April 2009 04:47   |
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
|
|
|
|
Goto Forum:
Current Time: Fri Feb 14 16:34:32 CST 2025
|