Oracle中的存储过程简单应用

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;