-
Procedure not working
Hey guys,
I was trying to do a procedure so if the rate is greater than 20000 certain values will be inserted in a table but the following code doesn't work and I was just wondering where I went wrong?
Thanks in advance.
CREATE OR REPLACE PROCEDURE sch_1 IS
BEGIN
IF T.RATE > 20000
INSERT INTO SCH
(SCHID, NAME)
vALUES(98, JO);
END SCH_1;
/
-
What is T.Rate and from where are you getting it?
-
T is a different table with a rate column.
but i haven't used procedures in pl/sql so im not sure how this would work.
-
You have to get the RATE in a variable and do the compare. Look for SELECT INTO in Oracle doc.
-
Thanks for your advice but I am still having problems with this, have you got any advice on how I could fix my 2nd attempt?
Thanks.
DECLARE
RATE_NO NUMBER;
CREATE OR REPLACE PROCEDURE sch_1 IS
BEGIN
SELECT T.RATE
INTO RATE_NO
FROM T
IF RATE_NO < 20000
INSERT INTO SCH
(SCHID, NAME)
vALUES(98, JO);
END;
/
Last edited by 182; 11-19-2005 at 08:04 PM.
-
SQL> create table t
2 (rate number);
Table created.
SQL>
SQL> create table sch
2 (schid number,
3 name varchar2(40));
Table created.
SQL>
SQL> insert into t values (19999);
1 row created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE sch_1
2 AS
3 RATE_NO NUMBER;
4 v_stmt VARCHAR2(100);
5 BEGIN
6 SELECT RATE INTO RATE_NO
7 FROM T;
8 IF RATE_NO < 20000 THEN
9 v_stmt := 'insert into sch values (98,'||chr(39)||'JO'||chr(39)||')';
10 execute immediate(v_stmt);
11 END IF;
12 END;
13 /
Procedure created.
SQL> show errors
No errors.
SQL> exec sch_1;
PL/SQL procedure successfully completed.
SQL> select * from sch;
SCHID NAME
---------- ----------------------------------------
98 JO
-
You need to declare the variable inside fo the procedure, not before creating it. If it doesn't work, explain the problem to us. Is it giving you an error or simply not doing what you expect it to.
Try this:
CREATE OR REPLACE PROCEDURE sch_1 AS
DECLARE
RATE_NO NUMBER;
BEGIN
SELECT T.RATE
INTO RATE_NO
FROM T
IF RATE_NO < 20000
INSERT INTO SCH
(SCHID, NAME)
vALUES(98, JO);
END;
/
-
There is no "declare" inside a procedure or function.
-
Thanks for the advice guys I have got it working now. The problem before was just an error message. Thanks again.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|