[Oracle] ํ๋ก์์ | ์ฌ์ฉ์ ์ ์ํจ์ | ํจ์ ๋ง๋ค๊ธฐ
*๋ฌด๊ฒฐ์ฑ์
๋ฌด๊ฒฐ์ฑ์ customizing
client < ------------ > dbms < ------------ > server
1. DDL๋ฌธ : ์ ์ธ์ ์ธ ๋ฐฉ๋ฒ (table, view...) ===> server
2. DML๋ฌธ : transaction + ํ๋ก๊ทธ๋๋ฐ๋ฐฉ๋ฒ
3. SP(Stored Procedure) : ํ๋ก๊ทธ๋๋ฐ๋ฐฉ๋ฒ, ์ฑ๋ฅ + ์๋ X ===> server
4. trigger(๋ฐฉ์์ ) : ํ๋ก๊ทธ๋๋ฐ ๋ฐฉ๋ฒ, ์ฑ๋ฅ -, ์๋ O ===> server
5. index : ๊ฒ์์ฑ๋ฅ +, ๊ฐฑ์ ์ฑ๋ฅ - ===> server
6. DCL๋ฌธ
ํ๋ก์์ ๋ญ๊ธฐ์ง(pl/sql)
์์ ์ฝ๋
show user
--ํ๊ฒฝ๋ณ์ ํ๋ฉด์ถ๋ ฅ์ ํ๋ ค๋ฉด on์ด ๋์ด์ผํจ
show serveroutput
set serveroutput on
--1. 1๋ถํฐ 10๊น์ง ์์ฐ์ ํฉ๊ณ๋ฅผ ๊ตฌํด๋ผ
-- ์ ์ธ๋ฌธ
declare
v_num int;
-- ํ ๋น๋ฌธ ':=' ํ์ค์นผ ๋ญ๊ธฐ์ง์์ ์ ๋๋ ๊ฒ์ผ๋ก ์ด๊ธฐ๊ฐ์ ์๋ฏธํ๋ค.
v_sum int:=0;
begin
v_num:= 1;
while v_num <=10
loop
v_sum:=v_sum+v_num;
v_num:=v_num+1;
end loop;
-- ์๋๋ ์ค๋ผํด์์ ์ ๊ณตํ๋ ํจํค์ง ์ด๋ฆ - ๋ช๊ฐ์ง ํจ์๊ฐ ์์
dbms_output.put_line(v_sum); --๋ฌธ์์ด์ ์ธ ์ ์๋์?
--์ ์ฒด ์ค ์๋ฌ๊ฐ ์๊ธด๋ค๋ฉด (exception)
exception
when others then
dbms_output.put_line(sqlcode||sqlerrm); --error code name
-- sqlerrm : ์ ์ ์ข
๋ฃ
-- others : ๋น์ ์ ์ข
๋ฃ ์ํด
end;
/
์๋์ ๊ฐ์ด if else ๋ฌธ์ผ๋ก ์ ์ดํด์ loop๋ฅผ ๊ฑธ์ด์ค ์๋ ์๋ค.
declare
v_num int;
-- ํ ๋น๋ฌธ ':=' ํ์ค์นผ ๋ญ๊ธฐ์ง์์ ์ ๋๋ ๊ฒ์ผ๋ก ์ด๊ธฐ๊ฐ์ ์๋ฏธํ๋ค.
v_sum int:=0;
begin
v_num:= 1;
loop
v_sum:=v_sum+v_num;
if v_num >= 10 then
exit;
-- elseif then
else
v_num:=v_num+1;
end if;
end loop;
-- ์๋๋ ์ค๋ผํด์์ ์ ๊ณตํ๋ ํจํค์ง ์ด๋ฆ - ๋ช๊ฐ์ง ํจ์๊ฐ ์์
dbms_output.put_line(v_sum); --๋ฌธ์์ด์ ์ธ ์ ์๋์?
--์ ์ฒด ์ค ์๋ฌ๊ฐ ์๊ธด๋ค๋ฉด (exception)
exception
when others then
dbms_output.put_line(sqlcode||sqlerrm); --error code name
-- sqlerrm : ์ ์ ์ข
๋ฃ
-- others : ๋น์ ์ ์ข
๋ฃ ์ํด
end;
/
-- sqlerrm : ์ ์ ์ข
๋ฃ ์ํด (๋กค๋ฐฑ์ ๋ด๊ฐ ์ ํ ์ ์์)
-- others : ๋น์ ์ ์ข
๋ฃ ์ํด -> ํ๋ ํ์๋ฅผ ๊ทธ๋ฅ ์ข
๋ฃ ์ํด
์๋๋ 3์ ๋ฐฐ์๋ง ๋ํ๋ ์์ ์ด๋ค.
declare
v_num int;
-- ํ ๋น๋ฌธ ':=' ํ์ค์นผ ๋ญ๊ธฐ์ง์์ ์ ๋๋ ๊ฒ์ผ๋ก ์ด๊ธฐ๊ฐ์ ์๋ฏธํ๋ค.
v_sum int:=0;
begin
v_num:= 3;
loop
if v_num >= 20 then
exit;
-- elseif then
else
dbms_output.put_line(to_char(v_sum)||'+'||to_char(v_num));
v_sum:=v_sum+v_num;
v_num:=v_num+3;
end if;
end loop;
dbms_output.put_line('total : '||to_char(v_sum));
dbms_output.put_line('last number : '||to_char(v_num));
exception
when others then
dbms_output.put_line(sqlcode||sqlerrm);
end;
/
์กฐ๊ธ ๋ค๋ฅธ ๋ฐฉ๋ฒ์ผ๋ก ์ ๊ทผ
declare
v_num int;
-- ํ ๋น๋ฌธ ':=' ํ์ค์นผ ๋ญ๊ธฐ์ง์์ ์ ๋๋ ๊ฒ์ผ๋ก ์ด๊ธฐ๊ฐ์ ์๋ฏธํ๋ค.
v_sum int:=0;
begin
v_num:= 1;
loop
if mod(v_num,3) = 0 then
v_sum:= v_sum+v_num;
end if;
if v_num >= 20 then
exit;
-- elseif then
else
v_num:=v_num+1;
end if;
end loop;
dbms_output.put_line('total : '||to_char(v_sum)||'last number :'
|| to_char(v_num));
exception
when others then
dbms_output.put_line(sqlcode||sqlerrm);
end;
/
์๋ฌ์ฝ๋๋ฅผ ํ์ธํด๋ณด๊ณ ์ถ๋ค๋ฉด?
์๋ฌ ์ ๋ฐ -> 0์ผ๋ก ๋๋ ๋ด : ๋ถ์ (?)
exception์ฒ๋ฆฌ๋ ๋ค์ํ๊ฒ ์ปค์คํฐ๋ง์ด์ง ํ ์ ์๋ค.
๊ฒฐ๊ณผ
์ฌ์ฉ์ ์ ์ํจ์ ์์ 1)
create or replace function get_dept_name(v_deptno dept.deptno%type)
return dept.dname%type
is
v_dname dept.dname%type;
begin
select dname
into v_dname
from dept
where deptno = v_deptno;
return v_dname;
exception
--when zero_divide then
--dbms_output.put_line('Try again! divider is 0!');
when others then
dbms_output.put_line(sqlcode||' '||sqlerrm);
return 0;
end;
/
--์์์ ํ๋ ๋ฐฉ๋ฒ์ select๋ฌธ์์ ํ๊ฒ ์.
--ํจ์ ํธ์ถ ๋ฐฉ๋ฒ
--1. select ๋ฌธ์์!
select get_dept_name(10)
from dual;
select deptno,dname,get_dept_name(deptno)
from dept;
desc user_objects
select object_name, object_type
from user_objects
order by 2;
desc user_source
select line, text
from user_source
where name='GET_DEPT_NAME';
--2. ๋ค๋ฅธ ๋ชจ๋์์!
declare
v_dname varchar2(14);
begin
--๊ฐ์ ๋ฆฌํด ๋ฐ์ ๋ณ์ ํ๋๊ฐ ํ์ํจ.
v_dname:=get_dept_name(30);
dbms_output.put_line(v_dname);
end;
/
--3. sql*plus๋ช
๋ น! (pl/sql ์๋!)
var v_dname varchar2(14)
var
exec :v_dname:= get_dept_name(30);
print v_dname
์์ 2) e
--empํ
์ด๋ธ์์ ename์ผ๋ก sal์ ์ถ๋ ฅํ๋ function๋ช
f_Sal์ ์์ฑํ๊ณ ํธ์ถํ๋ผ.
create or replace function f_sal(v_ename emp.ename%type)
return emp.sal%type
is
v_sal emp.sal%type;
begin
select sal
into v_sal
from emp
where ename like '%'||v_ename||'%'; --์ ํ์น ์์ ๊ฒฝ์ฐ = ๋ง๊ณ like๋ฅผ ์ค๋ค.๊ทธ๋์ ์์ผ๋ ์นด๋ ๊ธฐํธ๋ ์ค์ผํจ
return v_sal;
exception
when others then
dbms_output.put_line(sqlcode||' '||sqlerrm);
return 0;
end;
/
์ด์ธ ๋ฌธ์ ํ์ด๋ค..
create or replace function get_dept_name(v_deptno dept.deptno%type)
return dept.dname%type
is
v_dname dept.dname%type;
begin
select dname
into v_dname
from dept
where deptno = v_deptno;
return v_dname;
exception
--when zero_divide then
--dbms_output.put_line('Try again! divider is 0!');
when others then
dbms_output.put_line(sqlcode||' '||sqlerrm);
return 0;
end;
/
--์์์ ํ๋ ๋ฐฉ๋ฒ์ select๋ฌธ์์ ํ๊ฒ ์.
--ํจ์ ํธ์ถ ๋ฐฉ๋ฒ
--1. select ๋ฌธ์์!
select get_dept_name(10)
from dual;
select deptno,dname,get_dept_name(deptno)
from dept;
desc user_objects
select object_name, object_type
from user_objects
order by 2;
desc user_source
select line, text
from user_source
where name='GET_DEPT_NAME';
--2. ๋ค๋ฅธ ๋ชจ๋์์!
declare
v_dname varchar2(14);
begin
--๊ฐ์ ๋ฆฌํด ๋ฐ์ ๋ณ์ ํ๋๊ฐ ํ์ํจ.
v_dname:=get_dept_name(30);
dbms_output.put_line(v_dname);
end;
/
--3. sql*plus๋ช
๋ น! (pl/sql ์๋!)
var v_dname varchar2(14)
var
exec :v_dname:= get_dept_name(30);
print v_dname
--empํ
์ด๋ธ์์ ename์ผ๋ก sal์ ์ถ๋ ฅํ๋ function๋ช
f_Sal์ ์์ฑํ๊ณ ํธ์ถํ๋ผ.
create or replace function f_sal(v_ename emp.ename%type)
return emp.sal%type
is
v_sal emp.sal%type;
begin
select sal
into v_sal
from emp
where ename like '%'||v_ename||'%'; --์ ํ์น ์์ ๊ฒฝ์ฐ = ๋ง๊ณ like๋ฅผ ์ค๋ค.๊ทธ๋์ ์์ผ๋ ์นด๋ ๊ธฐํธ๋ ์ค์ผํจ
return v_sal;
exception
when TOO_MANY_ROWS then --์ ํด์ง ์๋ฌ๋ช
์ :ํด๋น ์๋ฌ๊ฐ ์๊ธฐ๋ฉด ์๋์ ๊ฐ์ด ์ถ๋ ฅํ๋ผ๋ ๊ฒ์.
dbms_output.put_line('Select only one row!');
return 0;
when others then
dbms_output.put_line(sqlcode||' '||sqlerrm);
return 0;
end;
/
select f_sal('SMITH')
from emp;
select ename,sal,f_sal(ename)
from emp;
select f_sal('S')
from dual;
declare
v_sal number(10);
begin
v_sal:= f_sal('SMI');
dbms_output.put_line(v_sal);
end;
/
--select๋ฌธ์ผ๋ก ์ถ๋ ฅ
select f_sal('SMI')
from dual;
--๋๋ค๋ฅธ ๋ฐฉ๋ฒ์ผ๋ก ์ถ๋ ฅ
var x_sal number
var
exec :x_sal:=f_sal('SMI');
print x_sal
--4๋ฒ ๋ฌธ์ ์
create or replace function f_sal(v_ename emp.ename%type)
return emp.sal%type
is
v_sal emp.sal%type;
v_job emp.job%type;
begin
select sal,job
into v_sal,v_job
from emp
where ename like '%' ||v_ename||'%';
if v_job='MANAGER' then
v_sal:=v_sal+v_sal*0.05;
elsif v_job ='SALESMAN' then
v_sal:= v_sal+v_sal*0.1;
end if;
return v_sal;
exception
when TOO_MANY_ROWS then --์ ํด์ง ์๋ฌ๋ช
์ :ํด๋น ์๋ฌ๊ฐ ์๊ธฐ๋ฉด ์๋์ ๊ฐ์ด ์ถ๋ ฅํ๋ผ๋ ๊ฒ์.
dbms_output.put_line('Try again!');
return 0;
when others then
dbms_output.put_line(sqlcode||' '||sqlerrm);
return 0;
end;
/
--select ๊ฒฐ๊ณผ
select ename,job,sal,f_sal(ename)
from emp;
select * from salgrade;
--5๋ฒ ๋ฌธ์ : salgradeํ
์ด๋ธ์์ grade๋ฅผ ์
๋ ฅํ๋ฉด ํด๋น grade์ ์ค๊ฐ ๊ฐ sal์ด ๋ฆฌํด ๋๋๋ก
--f_midsalํจ์ ์์ฑํ๊ณ ํธ์ถํ๋ผ
create or replace function f_midsal(v_grade salgrade.grade%type)
return salgrade.losal%type
is
v_midsal salgrade.losal%type; --losal๊ณผ hisal์ ํ์
์ด ๊ฐ์ผ๋ฏ๋ก ๋ฌด์์ ์ฌ์ฉํ๋์ง, ๋ฌด๊ดํ๋ค.
begin
select round((losal+hisal)/2)
into v_midsal
from salgrade
where grade = v_grade;
return v_midsal;
exception
when others then
dbms_output.put_line(sqlcode||sqlerrm);
return 0;
end;
/
select grade,losal,hisal,f_midsal(grade) as midsal
from salgrade;
--ํ๋ก์์ ๋ก ์ ํจ์๋ฅผ ๋ฐ๊ฟ๋ณด๊ธฐ object ๋ช
๊ณผ, type์ ๋ฐ๊ฟ์ค
--c์ธ์ด์ ๋ง์น void์ฒ๋ผ ! return type์ด ์๋ค.
create or replace procedure p_midsal(v_grade salgrade.grade%type)
is
v_midsal salgrade.losal%type;
begin
select round((losal+hisal)/2)
into v_midsal
from salgrade
where grade = v_grade;
dbms_output.put_line(v_midsal);
exception
when others then
dbms_output.put_line(sqlcode||sqlerrm);
end;
/
-- select๋ก ํ๋ก์์ ํธ์ถ ํ ์ ์์.
-- exec๋ก ์ถ๋ ฅ ๊ฐ๋ฅ(3๋ฒ์งธ ๋ฐฉ๋ฒ) sql+์์ ๋ฐฉ๋ฒ์ค ์์ํํธ(exec)๋ก ์ถ๋ ฅ!
exec p_midsal(2);
begin
p_midsal(2);
end;
/
๋ง์ฝ ํ๋ฉด ์ถ๋ ฅ &ํ๋ฆฐํธ๊ฐ ์๋๋ ๊ฒฝ์ฐ ->
show serveroutput
set serveroutput on
๊ผญ ํด์ฃผ์ด์ผ ํด๊ฒฐ์ด ๋จ
์ต์ข (์ ๋ฆฌํด๋๊ธฐ)
create or replace function get_dept_name(v_deptno dept.deptno%type)
return dept.dname%type
is
v_dname dept.dname%type;
begin
select dname
into v_dname
from dept
where deptno = v_deptno;
return v_dname;
exception
--when zero_divide then
--dbms_output.put_line('Try again! divider is 0!');
when others then
dbms_output.put_line(sqlcode||' '||sqlerrm);
return 0;
end;
/
--์์์ ํ๋ ๋ฐฉ๋ฒ์ select๋ฌธ์์ ํ๊ฒ ์.
--ํจ์ ํธ์ถ ๋ฐฉ๋ฒ
--1. select ๋ฌธ์์!
select get_dept_name(10)
from dual;
select deptno,dname,get_dept_name(deptno)
from dept;
desc user_objects
select object_name, object_type
from user_objects
order by 2;
desc user_source
select line, text
from user_source
where name='GET_DEPT_NAME';
--2. ๋ค๋ฅธ ๋ชจ๋์์!
declare
v_dname varchar2(14);
begin
--๊ฐ์ ๋ฆฌํด ๋ฐ์ ๋ณ์ ํ๋๊ฐ ํ์ํจ.
v_dname:=get_dept_name(30);
dbms_output.put_line(v_dname);
end;
/
--3. sql*plus๋ช
๋ น! (pl/sql ์๋!)
var v_dname varchar2(14)
var
exec :v_dname:= get_dept_name(30);
print v_dname
--empํ
์ด๋ธ์์ ename์ผ๋ก sal์ ์ถ๋ ฅํ๋ function๋ช
f_Sal์ ์์ฑํ๊ณ ํธ์ถํ๋ผ.
create or replace function f_sal(v_ename emp.ename%type)
return emp.sal%type
is
v_sal emp.sal%type;
begin
select sal
into v_sal
from emp
where ename like '%'||v_ename||'%'; --์ ํ์น ์์ ๊ฒฝ์ฐ = ๋ง๊ณ like๋ฅผ ์ค๋ค.๊ทธ๋์ ์์ผ๋ ์นด๋ ๊ธฐํธ๋ ์ค์ผํจ
return v_sal;
exception
when TOO_MANY_ROWS then --์ ํด์ง ์๋ฌ๋ช
์ :ํด๋น ์๋ฌ๊ฐ ์๊ธฐ๋ฉด ์๋์ ๊ฐ์ด ์ถ๋ ฅํ๋ผ๋ ๊ฒ์.
dbms_output.put_line('Select only one row!');
return 0;
when others then
dbms_output.put_line(sqlcode||' '||sqlerrm);
return 0;
end;
/
select f_sal('SMITH')
from emp;
select ename,sal,f_sal(ename)
from emp;
select f_sal('S')
from dual;
declare
v_sal number(10);
begin
v_sal:= f_sal('SMI');
dbms_output.put_line(v_sal);
end;
/
--select๋ฌธ์ผ๋ก ์ถ๋ ฅ
select f_sal('SMI')
from dual;
--๋๋ค๋ฅธ ๋ฐฉ๋ฒ์ผ๋ก ์ถ๋ ฅ
var x_sal number
var
exec :x_sal:=f_sal('SMI');
print x_sal
--4๋ฒ ๋ฌธ์ ์
create or replace function f_sal(v_ename emp.ename%type)
return emp.sal%type
is
v_sal emp.sal%type;
v_job emp.job%type;
begin
select sal,job
into v_sal,v_job
from emp
where ename like '%' ||v_ename||'%';
if v_job='MANAGER' then
v_sal:=v_sal+v_sal*0.05;
elsif v_job ='SALESMAN' then
v_sal:= v_sal+v_sal*0.1;
end if;
return v_sal;
exception
when TOO_MANY_ROWS then --์ ํด์ง ์๋ฌ๋ช
์ :ํด๋น ์๋ฌ๊ฐ ์๊ธฐ๋ฉด ์๋์ ๊ฐ์ด ์ถ๋ ฅํ๋ผ๋ ๊ฒ์.
dbms_output.put_line('Try again!');
return 0;
when others then
dbms_output.put_line(sqlcode||' '||sqlerrm);
return 0;
end;
/
--select ๊ฒฐ๊ณผ
select ename,job,sal,f_sal(ename)
from emp;
select * from salgrade;
--5๋ฒ ๋ฌธ์ : salgradeํ
์ด๋ธ์์ grade๋ฅผ ์
๋ ฅํ๋ฉด ํด๋น grade์ ์ค๊ฐ ๊ฐ sal์ด ๋ฆฌํด ๋๋๋ก
--f_midsalํจ์ ์์ฑํ๊ณ ํธ์ถํ๋ผ
create or replace function f_midsal(v_grade salgrade.grade%type)
return salgrade.losal%type
is
v_midsal salgrade.losal%type; --losal๊ณผ hisal์ ํ์
์ด ๊ฐ์ผ๋ฏ๋ก ๋ฌด์์ ์ฌ์ฉํ๋์ง, ๋ฌด๊ดํ๋ค.
begin
select round((losal+hisal)/2)
into v_midsal
from salgrade
where grade = v_grade;
return v_midsal;
exception
when others then
dbms_output.put_line(sqlcode||sqlerrm);
return 0;
end;
/
select grade,losal,hisal,f_midsal(grade) as midsal
from salgrade;
--ํ๋ก์์ ๋ก ์ ํจ์๋ฅผ ๋ฐ๊ฟ๋ณด๊ธฐ object ๋ช
๊ณผ, type์ ๋ฐ๊ฟ์ค
--c์ธ์ด์ ๋ง์น void์ฒ๋ผ ! return type์ด ์๋ค.
create or replace procedure p_midsal(v_grade salgrade.grade%type)
is
v_midsal salgrade.losal%type;
begin
select round((losal+hisal)/2)
into v_midsal
from salgrade
where grade = v_grade;
dbms_output.put_line(v_midsal);
exception
when others then
dbms_output.put_line(sqlcode||sqlerrm);
end;
/
-- select๋ก ํ๋ก์์ ํธ์ถ ํ ์ ์์.
-- exec๋ก ์ถ๋ ฅ ๊ฐ๋ฅ(3๋ฒ์งธ ๋ฐฉ๋ฒ) sql+์์ ๋ฐฉ๋ฒ์ค ์์ํํธ(exec)๋ก ์ถ๋ ฅ!
exec p_midsal(2);
begin
p_midsal(2);
end;
/
--test table์์ฑ
drop table testemp;
create table testemp
as select empno,ename,sal,deptno from emp;
select * from testemp;
alter table testemp
add grade number(1);
alter table testemp
add sumsal number(10);
update testemp t;
/*์์
set sumsal = (select sum(sal)
from testemp
where t.deptno=deptno );
*/
/
create or replace procedure p_sum01(v_deptno testemp.deptno%type)
is
v_sumsal testemp.sumsal%type;
begin
select sum(sal)
into v_sumsal
from testemp
where deptno=v_deptno;
if v_deptno=10 then
v_sumsal:=0;
end if;
if v_sumsal >= 10000 then
dbms_output.put_line('10000 over!');
end if;
update testemp
set sumsal = v_sumsal
where deptno = v_deptno;
--commit์ db์ ๋ฐ์ํ๊ฒ ๋ค๋ ๋ป์ด๋ค.
commit;
exception
when others then
dbms_output.put_line(sqlcode);
rollback;
end;
/
exec p_sum01(30);
select * from testemp;
commit;-- ๋ณ๊ฒฝ์ ์ ์ฅํ๋ ค๋ฉด ์ปค๋ฐํด์ค์ผํจ. ์ํ๊ณ ๋ฐ๋ก ์ปดํ์ผ ์ ๋กค๋ฐฑ ๋นํจ.
--insert๋ฌธ์ update๊ฐ ๊ผญ ๋ฐ๋ผ๋ค๋
์ผ ํ๋ค.
create or replace procedure p_insert01(v_empno testemp.empno%type,
v_ename testemp.ename%type,
v_sal testemp.sal%type,
v_deptno testemp.deptno%type)
is
begin
insert into testemp
values(v_empno,v_ename,v_sal,v_deptno,null,null);
update testemp
set sumsal=(select sum(sal)
from testemp
where deptno=v_deptno)
where deptno=v_deptno;
commit;
exception
when others then
dbms_output.put_line(sqlcode||sqlerrm);
rollback;
end;
/
select * from testemp;
exec p_insert01(9998,'kim',2000,20);
-- 7-1๋ฒ ๋ฌธ์ ์ ์๋ 7๋ฒ์ด๊ณ , ์ด๋ฅผ ํฉํด์ ๋ง๋ ๊ฒ!
create or replace procedure p_insert02(v_empno testemp.empno%type,
v_ename testemp.ename%type,
v_sal testemp.sal%type,
v_deptno testemp.deptno%type)
is
v_sumsal testemp.sumsal%type;
begin
insert into testemp
values(v_empno,v_ename,v_sal,v_deptno,null,null);
update testemp
set sumsal=(select sum(sal)
from testemp
where deptno=v_deptno)
where deptno=v_deptno;
select sum(sal)
into v_sumsal
from testemp
where deptno=v_deptno;
if v_deptno=10 then
v_sumsal:=0;
end if;
if v_sumsal >= 10000 then
dbms_output.put_line('10000 over!');
end if;
commit;
exception
when others then
dbms_output.put_line(sqlcode||sqlerrm);
rollback;
end;
/
select * from testemp where ;
exec p_insert02(9999,'kim',2000,20);