学习存储过程

存储过程定义信息查看

column text format a30;column name format a20;select * from  user_source where name ='select_emp';

修改
修改和创建一样就是加上or replace;

删除存储过程：
drop procedure '过程名';

创建过程：

create or replace procedure update_emp isbegin  update emp set ename = 'hhdhjf' where empno = 7934;end update_emp;

调用过程：call update_emp ();execute update_emp;

创建过程（实现两个数交换）create or replace procedure exchange_value(value1 in out number,                                           value2 in out number) as  temp1 number;  temp2 number;begin  temp1  := value1;  temp2  := value2;  value1 := temp2;  value2 := temp1;end exchange_value;调用该过程variable val1 number;variable val2 number;exec :val1 := 2009;exec :val2 := 9002;exec exchange_value(:val1, :val2);输出值：select :value1, :value2 from dual;select :val1, :val2 from dual;

存储过程使用默认值

set serverout on;create or replace procedure creat_value(value1 varchar2 default '123456',                                        value2 varchar2 default '789') asbegin  dbms_output.put_line('value1:' || value1 || '  ' || 'value2:' || value2);end creat_value;调用过程begin  creat_value();  creat_value('kkkkk');  creat_value('sssss', 'eeeeee');end;

带参数out

create or replace procedure select_emp(emp_num  in number,                                       emp_name out varchar2) asbegin  select ename into emp_name from emp where empno = emp_num;end select_emp;variable v_name varchar2(10);call select_emp(7934,:v_name);或execute select_emp(7934,:v_name);输出print v_name;

 带参数in out

create or replace procedure app_disp(v_id      in out departments.department_id%type,                                     v_name    in out departments.department_name%type,                                     v_address in out departments.address%type) asbegin  insert into departments values(v_id, v_name, v_address);  v_id := v_id - 1;  select department_id, department_name, address    into v_id, v_name, v_address    from departments   where department_id = v_id;exception  when dup_val_on_index then    dbms_output.put_line('插入系部信息时，系部号不能重复。');  when no_data_found then    dbms_output.put_line('查询系部信息时，该系不存在');end app_disp;调用过程：variable v_id number;variable v_name varchar2(8);variable v_address varchar2(40);execute :v_name :='地理物理';execute :v_address := 'x号教学楼';execute :v_id :='111';call app_disp(:v_id, :v_name, :v_address);输出参数：print v_id ;print v_name;print v_address;

带参数存储过程in

create or replace procedure update_emp2(emp_num  in number,                                        emp_name in varchar2) asbegin  update emp set ename = emp_name where empno = emp_num;end update_emp2;

不带参数名调用：call update_emp2(7934, 'ddddd');

使用参数名调用：execute update_emp2(emp_num => 7934, emp_name => 'ddddd');