-
I got 2 pl/sql questions like to check with you all:
1. I'd like to test if a certain value already exist in a table, and if it's not, add this value to the table. What's the best way to do this? In my code, I did
procedure my_proc (p_user_id in integer)
is
v_count integer := 0;
begin
SELECT count(*) INTO v_count FROM Users
WHERE user_id = p_user_id;
IF v_count = 0 THEN
INSERT INTO Users (UserID) VALUES (p_user_id);
END IF;
end my_proc;
This procedure works fine, but when I read Steven Feuerstein's book on PL/SQL, he said 'don't use select count(*) unless you really want to know the total number of hits. ' For me, I am more interested in knowing if this value 'exist' or not, instead of how many are there.
So is that a better (more efficient) way to handle it?
2. In my procedure, I'd like to check the validity of the input parameter. If it's invalid, I'd like to raise an exception and return control back to the calling program. What I did is:
Procedure my_proc2 (p_user_name in varchar) IS
Invalid_param exception;
BEGIN
IF p_user_name is null THEN
RAISE invalid_param;
END IF;
....
EXCEPTION
WHEN invalid_param THEN
return; -- Is this necessary?
WHEN OTHERS THEN
dbms_ouput.put_line(SQLERRM);
END my_proc2;
You might noted that I put a return statement in the user defined exception handling part. Is it necessary? i.e. will the control go back to the calling program anyway even w/o a return statement over there?
Thanks a lot!
-
If you don't like 'select count(*) ...', here is another way you may try.
procedure my_proc (p_user_id in integer)
is
v_id integer := 0;
begin
SELECT user_id INTO v_id FROM Users
WHERE user_id = p_user_id;
Exception
When no_data_found then
INSERT INTO Users (User_ID) VALUES (p_user_id);
When others then
dbms_output.put_line(SQLERRM);
end my_proc;
-
It won't work for me...
Thanks for your reply but this won't work ---
I don't want to jump to no_data_found exception since there are still lots of codes need to execuated in the execution section of the procedure. That's why I tried to avoid using the select statement you used here.
i.e.
--=-=-=-=-=-=-=-=-=-=-=-=
procedure my_test (user_id) is
...
begin
...
select count(*) into v_count from users
where user_id = p_user_id;
if count =0 then
insert into users (user_ID) values (p_user_id);
end if;
-- lot of code here need to be executed
...
exception
...
end
--=-=-=-=-=-=-=-=-=-=-=-=
Other ideas?
-
procedure my_proc (p_user_id in integer)
is
v_exists integer := 0;
begin
--
select 1 into v_exists from dual
where exists (SELECT 1 FROM Users WHERE user_id = p_user_id)
union
select 0 from dual
where not exists (SELECT 1 FROM Users WHERE user_id = p_user_id);
--
IF v_exists = 0 THEN
INSERT INTO Users (UserID) VALUES (p_user_id);
END IF;
end my_proc;
This method works fine :
SQL> select 1 from dual
where exists (SELECT 1 FROM dba_Users WHERE username = 'SYS')
union
select 0 from dual
where not exists (SELECT 1 FROM dba_Users WHERE username = 'SYS');
1
----------
1
SQL> select 1 from dual
where exists (SELECT 1 FROM dba_Users WHERE username = 'SSYS')
union
select 0 from dual
where not exists (SELECT 1 FROM dba_Users WHERE username = 'SSYS');
1
----------
0
Try.
-
Thanks Shestakov, it works great, but
I have one more question to check with you --
I don't want to use select count(*) since I don't want to take the performance hit (it will cause the full table scan and could be very slow if the table is big). But I seem to remember that the 'exist/not exist' clause is kind of slow too (since basically it also needs to go through the whole table and find out if that entry is in or not).
So do you have any idea on that? The whole discussion here is actually how to improve performance, instead of just avoid using select count(*).
Thanks a lot for your help...
-
you can use this statement
select *
from qbsbd_subbrand
where brand_code = 'HSEBRD'
and rownum =1
Note: rownum=1 will cause the execution to stop when a match is found
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
|
Click Here to Expand Forum to Full Width
|