（oracle）如何创建和使用procedure

 1:定义procedure(在高级程序设计语言中的模块的概念)

create or replace procedure procedure_name (param_1 in type,param_2 in out type)

as

param_inner type

begin

statement;

end;

 

a:不含参数的procedure

create or replace procedure temp 
as
temp_salary employee.salary%type;
begin
select salary into temp_salary from employee where employee.empid=13;
dbms_output.put_line('temp_salary='||temp_salary);
end;

----调用procedure--

begin
temp;
end;

  output:

tempemployee=2440

 

d:含参数的procedure

create or replace procedure temp2 (
temp_empid in employee.empid%type,
temp_empname in out employee.empname%type
)as
name1 employee.empname%type;
begin
select empname into name1 from employee where employee.empid=temp_empid;
temp_empname:='姓名'||name1;
end;


declare
iid employee.empid%type;
iname employee.empid%type;
begin
iid:=10;
iname:='daniel';
temp2(iid,iname);
dbms_output.put_line('iid='||iid);
dbms_output.put_line('iname='||iname);
end;

 

output:

iid=10
iname=姓名dwj

分析：

在调用procedure temp2()的时候iname:='daniel'被存储过程的结果覆盖，变成iname=姓名dwj;

实际上不用再begin里面声明也可以；

带param的procedure 中in out的意思 in typein要输入的param out output要打出的param;

 

c:How to use procedure to create table

create or replace procedure create_table
as sq varchar2(2000);
begin
sq:='create table t2t(
t_id varchar2(20) primary key,
t_name varchar2(255) not null,
t_gender varchar2(255)
)';
execute immediate sq;
end;

begin
create_table;
end;

select * from t2t;

分析：

在create or replace procedure，play attention to limits of authority,有时候创建的procedure不能被调用

即：systemdba中创建的不能在systemnormal中使用；否则会报错