/*==========调用存储过程========*/
create or replace procedure helo_pro
as counts number(4);
begin
select count(*) into counts from test1;
dbms_output.put_line('总记录数=='||counts);
end;
--调用存储过程
call helo_pro();
/*======带参数的存储过程=========*/
create or replace procedure get_name
--声明参数
(t_id number) is
t_name test1.name%type;
begin
select name into t_name from test1 where id=t_id;
dbms_output.put_line('test-name===='||t_name);
end;
call get_name(101);
/*带输入输出参数的存储过程*/
create or replace procedure get_param
(a in number,b in number ,c in out number, d out number)
is
begin
c:=a+b;
d:=a+b+c;
end ;
declare
su number(7);
mu number(5);
begin
--sql中调用存储过程,不加call
get_param(3,65,mu,su);
dbms_output.put_line(su);
end;
/*动态创建表和添加记录*/
create sequence test2_seq;
begin
execute immediate 'create table test2 (id number(10) primary key,name varchar2(100))';
for i in reverse 1..10
loop
/*execute immediate中不能出现序列,在此上下文中不允许表, 视图或序列引用'TEST2_SEQ.NEXTVAL'*/
execute immediate 'insert into test2 values ('||test2_seq.nextval||','||i||')';
end loop;
end;
create or replace procedure helo_pro
as counts number(4);
begin
select count(*) into counts from test1;
dbms_output.put_line('总记录数=='||counts);
end;
--调用存储过程
call helo_pro();
/*======带参数的存储过程=========*/
create or replace procedure get_name
--声明参数
(t_id number) is
t_name test1.name%type;
begin
select name into t_name from test1 where id=t_id;
dbms_output.put_line('test-name===='||t_name);
end;
call get_name(101);
/*带输入输出参数的存储过程*/
create or replace procedure get_param
(a in number,b in number ,c in out number, d out number)
is
begin
c:=a+b;
d:=a+b+c;
end ;
declare
su number(7);
mu number(5);
begin
--sql中调用存储过程,不加call
get_param(3,65,mu,su);
dbms_output.put_line(su);
end;
/*动态创建表和添加记录*/
create sequence test2_seq;
begin
execute immediate 'create table test2 (id number(10) primary key,name varchar2(100))';
for i in reverse 1..10
loop
/*execute immediate中不能出现序列,在此上下文中不允许表, 视图或序列引用'TEST2_SEQ.NEXTVAL'*/
execute immediate 'insert into test2 values ('||test2_seq.nextval||','||i||')';
end loop;
end;
---删除存储过程
drop procedure xxxx;