我想做一个存储过程,实现的功能是:我用的数据库是oracle
select comcode from Company where comname like '%上海%' //上海是参数
insert into user(id,name,comcode) values('id','name','comcode') //id,name,comcode都是参数这样的存储过程怎么写啊?今天刚接触如果不建立存储过程,上面的参数可以手动填写,但是要求使用一条sql语句。这样的sql怎么写啊??
知道的话就帮帮忙把?谢谢~!
select comcode from Company where comname like '%上海%' //上海是参数
insert into user(id,name,comcode) values('id','name','comcode') //id,name,comcode都是参数这样的存储过程怎么写啊?今天刚接触如果不建立存储过程,上面的参数可以手动填写,但是要求使用一条sql语句。这样的sql怎么写啊??
知道的话就帮帮忙把?谢谢~!
解决方案 »
- EXECUTE IMMEDIATE中调用函数
- ORA-00600: internal error code, arguments: [kcbz_check_objd_typ_1], [0], [0], [1
- 求一个把值转化成列的过程
- 请大家指点:有效的Oralce数据库间同步软件或模块,请大家给推荐推荐!
- 小问题 关于oracle的随机查询问题
- 关于在LINUX下安装ORAQCEL10G的问题
- 重开的:ORA-00942和ORA-02063错误。请高手指点
- 一个不知道怎样解决的问题,急,在线等待!
- ~~~~~~问题紧急,请大家务必帮小弟一把!~~~~~~~~~~~在线等!
- lob定位器?
- Oracle 问题
- 求救 Connection refused(DESCRIPTION=(TMP=)(VSNNUM=169870080)(ERR=12518)(ERROR_STACK=(ERROR=(
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;
--p_pos 上海
v_comcode data_type;
beginEXECUTE 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;差不多就这样了
INSERT INTO USER(ID,NAME,COMCODE)
SELECT 1 EID, 'TEST' ENAME, COMCODE
FROM COMPANY
WHERE COMNAME LIKE '%SHANGHAI%';
(iPos in varchar2,
iId in varchar2,
iName in varchar2,
iComcode in varchar2)
as
v_sqlSel varchar2(100);
v_sqlIns varchar2(100);
begin
v_sqlSel := 'select comcode from Company where comname like ''%' ||iPos||'%''';
--iPos 可以传入"上海"
v_sqlIns := 'insert into user(id,name,comcode) values(''';
v_sqlIns := v_sqlIns || iId || ''',''';
v_sqlIns := v_sqlIns || iName || ''',''';
v_sqlIns := v_sqlIns || iComcode || ''')';
....
....
....
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;
楼上的,参数4是通过
select comcode from Company where comname like 参数1;
参数1查出来的啊??
PROCEDURE CCICDB.TESTB 编译错误错误:PLS-00215: String length constraints must be in range (1 .. 32767)
行:2
文本:v_comcode varchar2;错误:PL/SQL: ORA-00913: too many values
行:7
文本:INSERT INTO Cc_User (UserCode,UserName,UserEName,Password,错误:PL/SQL: SQL Statement ignored
行:7
文本:INSERT INTO Cc_User (UserCode,UserName,UserEName,Password,
SQL code
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;
那么要把comcode 赋给参数4要怎么写呢??
insert into user(id,name,comcode) values(参数2, 参数3, comcode);
是这么写吗??
(
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;
你可以建立个这样的表尝试下,我这没问题的。