17,086
社区成员
发帖
与我相关
我的任务
分享
CREATE OR REPLACE PROCEDURE
TEST1
(
id in number,
name in varchar2,
selectid in number
)
as
tem manager.testtable.name%type;
begin
select age into tem from manager.testta
where id=selectid;
insert into manager.testtable(id,name,a
values (id,name,tem);
end;
create or replace procedure procedure_name
(
参数1 in DataType,
参数2 in DataType,
参数3 in DataType,
参数4 in DataType
)
as
begin
select comcode from Company where comname like 参数1;
insert into user(id,name,comcode) values(参数2, 参数3, 参数4 );
commit;
end;
-- TRY IT .. 比如你输入的ID=1,NAME='TEST':
INSERT INTO USER(ID,NAME,COMCODE)
SELECT 1 EID, 'TEST' ENAME, COMCODE
FROM COMPANY
WHERE COMNAME LIKE '%SHANGHAI%';
[Quote=引用楼主 leiru 的帖子:]create or replace procedure XXX(p_id data_type,p_name data_type,p_pos datatype) as
--p_pos 上海
v_comcode data_type;
begin
EXECUTE IMMEDIATE 'select comcode from Company where comname like ''%'||p_pos||'%''' into v_comcode; --要保证只返回一行数据
insert into user(id,name,comcode) values(p_id,p_name,v_comcode);
end;
declare
c Company.comname%type;
id int;
name varchar2(20);
cursor r_c is select comcode from Company where comname like '%上海%';
begin
open r_c;
loop
fetch r_c
into c;
exit when r_c%notfound;
id = 1;
name = 'zyf'; --id,name可改为传值方式.
insert into user(id,name,comcode) values(id,name,c);
end loop;
close r_c;
end;