Oracle中的存储过程简单应用
一，实例——查询
1.
create or replace procedure print_emp_name
(
v_id in emp_s.EMP_ID%type
)
as
v_emp emp_s%rowtype;
begin
select * into v_emp from emp_s where emp_id = v_id;
if SQL%found then
dbms_output.PUT_LINE('员工姓名：'||v_emp.emp_name);
end if;
end print_emp_name;
begin print_emp_name(1);end;
2.
create or replace procedure get_emp_name
(
v_id in emp_s.EMP_ID%type,
o_name out emp_s.EMP_NAME%type
)
is
begin
select emp_name into o_name from emp_s where emp_id = v_id;
end get_emp_name;
declare
o_name varchar2(10);
v_id number;
begin
v_id := 1;
get_emp_name(v_id,o_name);
dbms_output.PUT_LINE(o_name);
end;
二、实例——插入
create or replace procedure insert_emp
(
i_id in emp_s.EMP_ID%type,
i_name in emp_s.EMP_NAME%type
)
as
str_sql varchar2(500);
begin
str_sql := 'insert into emp_s values(:i_id,:i_name)';
execute immediate str_sql using i_id,i_name;
if sql%found then
dbms_output.PUT_LINE('insert success');
end if;
end insert_emp;
Oracle 存储过程修改列的类型的字符大小
create or replace procedure alter_col_size
(
col_name in varchar2,
str_size in number
)
as
str_sql varchar2(5000);
begin
declare cursor exec_alter_sql is
select 'alter table ' || table_name || ' modify '||col_name||' varchar2('||str_size||')'
from user_tab_columns
where COLUMN_NAME=col_name;
begin
open exec_alter_sql;
loop
fetch exec_alter_sql into str_sql;
execute immediate str_sql;
--dbms_output.PUT_LINE(str_sql);
exit when exec_alter_sql%notfound;
end loop;
end;
end alter_col_size;