我想插入一条数据 并返回它的CompanyID 我是这样写的 可是提示出错
请大家指一下 应怎么写 ,谢谢create or replace procedure SP_RONYEE_COMPANY_ADD
(
UserNAME in varchar2,
COMPANYNAME in varchar2,
COMPANYTYPE in number,
MANAGEMENTADDR in varchar2,
CALLING in varchar2,
MANUFACTURE in varchar2,
FLAG in Number,
COMPANYID out Number
)
isbegin
INSERT INTO ronyee_company (username, companyname,companytype,managementaddr,calling,manufacture, flag)
values
(''||username||'',''||companyname||'',''||companytype||'',''||managementaddr||'',''||calling||'',''||manufacture||'',''||flag||'');
set CompanyID := select Companyid from ronyee_company where Companyname=''||companyname||'' and UserNAME =''||username||'';
end SP_RONYEE_COMPANY_ADD;
请大家指一下 应怎么写 ,谢谢create or replace procedure SP_RONYEE_COMPANY_ADD
(
UserNAME in varchar2,
COMPANYNAME in varchar2,
COMPANYTYPE in number,
MANAGEMENTADDR in varchar2,
CALLING in varchar2,
MANUFACTURE in varchar2,
FLAG in Number,
COMPANYID out Number
)
isbegin
INSERT INTO ronyee_company (username, companyname,companytype,managementaddr,calling,manufacture, flag)
values
(''||username||'',''||companyname||'',''||companytype||'',''||managementaddr||'',''||calling||'',''||manufacture||'',''||flag||'');
set CompanyID := select Companyid from ronyee_company where Companyname=''||companyname||'' and UserNAME =''||username||'';
end SP_RONYEE_COMPANY_ADD;
解决方案 »
- 为什么oracle定时任务启动不了呢? 如有满意答案---速结贴给分~<>~
- Oracle分区表,经常删除会造成磁盘碎片过多吗?
- 面试归来!ORACLE题目
- oracle两个表叠加和成一个视图,请各位大虾指点迷津
- 怎样在SQL SERVER的存储过程中调用ORACLE的过程,急!!
- 急!请教大侠,怎么将sql server里的表和数据导到oracle中去?
- oracle73的数据库恢复问题
- 关于DBMS_SQL包,动态SQL语句的问题
- 初学者:ORACLE Developer报表编辑器中字段前的图标是什么意思?
- exp query中 to_date(a,'yyyy-mm-dd')='ssss-11-22'此类的日期约束条件如何在命令窗口写?
- oracle 根据其它表更新数据
- 如何快速判断数据表中是否有符合某些条件的记录??
into companyid
from ronyee_company
where Companyname='' ¦ ¦companyname ¦ ¦'' and UserNAME ='' ¦ ¦username ¦ ¦'';
values
(:username,:companyname ,:companytype,:managementaddr,:calling,:manufacture,:flag); select Companyid
into companyid
from ronyee_company
where Companyname=:companyname and UserNAME =:username;
错误如下
Compilation errors for PROCEDURE RONYEE_ORACLE_DATABASE.SP_RONYEE_COMPANY_ADDError: PLS-00049: bad bind variable 'USERNAME'
Line: 17
Text: (:username,:companyname ,:companytype,:managementaddr,:calling,:manufacture,:flag);Error: PLS-00049: bad bind variable 'COMPANYNAME'
Line: 17
Text: (:username,:companyname ,:companytype,:managementaddr,:calling,:manufacture,:flag);Error: PLS-00049: bad bind variable 'COMPANYTYPE'
Line: 17
Text: (:username,:companyname ,:companytype,:managementaddr,:calling,:manufacture,:flag);Error: PLS-00049: bad bind variable 'MANAGEMENTADDR'
Line: 17
Text: (:username,:companyname ,:companytype,:managementaddr,:calling,:manufacture,:flag);Error: PLS-00049: bad bind variable 'CALLING'
Line: 17
Text: (:username,:companyname ,:companytype,:managementaddr,:calling,:manufacture,:flag);Error: PLS-00049: bad bind variable 'MANUFACTURE'
Line: 17
Text: (:username,:companyname ,:companytype,:managementaddr,:calling,:manufacture,:flag);Error: PLS-00049: bad bind variable 'FLAG'
Line: 17
Text: (:username,:companyname ,:companytype,:managementaddr,:calling,:manufacture,:flag);Error: PLS-00049: bad bind variable 'COMPANYNAME'
Line: 22
Text: where Companyname=:companyname and UserNAME =:username;Error: PLS-00049: bad bind variable 'USERNAME'
Line: 22
Text: where Companyname=:companyname and UserNAME =:username;
(
UserNAME in varchar2,
COMPANYNAME in varchar2,
COMPANYTYPE in number,
MANAGEMENTADDR in varchar2,
CALLING in varchar2,
MANUFACTURE in varchar2,
FLAG in Number,
COMPANYID out Number
)
is
begin
INSERT INTO ronyee_company (username, companyname,companytype,managementaddr,calling,manufacture, flag)
values
(''||username||'',''||companyname||'',''||companytype||'',''||managementaddr||'',''||calling||'',''||manufacture||'',''||flag||'');
select Companyid
into companyid
from ronyee_company
where Companyname=''||companyname||'' and UserNAME =''||username||''; end SP_RONYEE_COMPANY_ADD;
companyParams[1].Value = userInfo.companyInfo.COMPANYNAME;
companyParams[2].Value = userInfo.companyInfo.COMPANYTYPE;
companyParams[3].Value = userInfo.companyInfo.MANAGEMENTADDR;
companyParams[4].Value = userInfo.companyInfo.CALLING;
companyParams[5].Value = userInfo.companyInfo.MANUFACTURE;
companyParams[6].Value = userInfo.companyInfo.FLAG;
companyParams[7].Direction = ParameterDirection.Output;
给调用的时候还是出 错,说输入类型不对是不是上面的过程写错了请大家帮忙~谢谢
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'SP_RONYEE_COMPANY_ADD'
conn.Open();
// Start an ADO.NET transactions
using (OracleTransaction trans = conn.BeginTransaction())
{
try
{
//Execute the 3 queries
rowsAffected = OraHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure, "SP_RONYEE_USERBASEINFO_ADD", userParams);
rowsAffected += OraHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure, "SP_RONYEE_COMPANY_ADD", companyParams); //就在这出错 contacterParams[0].Value = int.Parse(companyParams[7].Value.ToString());
rowsAffected += OraHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure, "SP_RONYEE_COMPANYCONTACT_ADD", contacterParams); rowsAffected += OraHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure, "sp_ronyee_emailValidate_add", validateEmailParams); //if we reach this point then commit the database work
trans.Commit();
}
catch(Exception ex)
{
//if an expection occurs, rollback all the work
trans.Rollback();
rowsAffected = 0;
}
create or replace procedure SP_RONYEE_COMPANY_ADD
(
v_UserNAME in varchar2,
v_COMPANYNAME in varchar2,
v_COMPANYTYPE in number,
v_MANAGEMENTADDR in varchar2,
v_CALLING in varchar2,
v_MANUFACTURE in varchar2,
v_FLAG in Number,
v_COMPANYID out Number
)
is
begin
INSERT INTO ronyee_company
(username,companyname,companytype,managementaddr,calling,manufacture,flag)
values (v_username ,v_companyname ,v_companytype ,v_managementaddr ,v_calling ,v_manufacture ,v_flag );
select Companyid into v_COMPANYID
from ronyee_company
where Companyname = v_companyname
and UserNAME =v_username ;
end SP_RONYEE_COMPANY_ADD;
要先创建SEQ,比如:
create sequence SEQ_COMPANYID_ID
minvalue 1
maxvalue 9999999999
start with 1
increment by 1
nocache;
INSERT INTO ronyee_company
(Companyid, username,companyname,companytype,managementaddr,calling,manufacture,flag)
values (SEQ_COMPANYID_ID.NEXTVAL, v_username ,v_companyname ,v_companytype ,v_managementaddr ,v_calling ,v_manufacture ,v_flag );
COMMIT;
(
v_UserNAME in varchar2,
v_COMPANYNAME in varchar2,
v_COMPANYTYPE in number,
v_MANAGEMENTADDR in varchar2,
v_CALLING in varchar2,
v_MANUFACTURE in varchar2,
v_FLAG in Number,
v_COMPANYID out Number
)
isbegin
declare Counts in Number;
select companyID into Counts from ronyee_company where rownum=1 order by companyID desc;
INSERT INTO ronyee_company
(username,companyname,companytype,managementaddr,calling,manufacture,flag,conpanyID)
values
(v_username ,v_companyname ,v_companytype ,v_managementaddr ,v_calling ,v_manufacture ,
v_flag ,@Counts+1);
select Companyid into v_COMPANYID
from ronyee_company
where Companyname = v_companyname
and UserNAME =v_username ;
end SP_RONYEE_COMPANY_ADD;
minvalue 6000
maxvalue 99999999
start with 6000
increment by 1
nocache
order; create or replace trigger tri_test_id
before insert on ronyee_company
for each row
declare
nextid number;
begin
IF :new.CompanyID IS NULLor :new.CompanyID=0 THEN
select seq_test_id.nextval
into nextid
from sys.dual;
:new.id:=nextid;
end if;
end tri_test_id;
临时变量位置要放好。另外,这个“@”用得很搞笑。create or replace procedure SP_RONYEE_COMPANY_ADD
(
v_UserNAME in varchar2,
v_COMPANYNAME in varchar2,
v_COMPANYTYPE in number,
v_MANAGEMENTADDR in varchar2,
v_CALLING in varchar2,
v_MANUFACTURE in varchar2,
v_FLAG in Number,
v_COMPANYID out Number
)
is
Counts in Number(10);
begin
select Nvl(Max(companyID), 0) into Counts from ronyee_company; INSERT INTO ronyee_company
(username,companyname,companytype,managementaddr,calling,manufacture,flag,conpanyID)
values
(v_username ,v_companyname ,v_companytype ,v_managementaddr ,v_calling ,v_manufacture ,
v_flag ,Counts+1);
Commit; v_COMPANYID := Counts+1;
end SP_RONYEE_COMPANY_ADD;
还错的话,拜托把异常信息贴出来。
create sequence seq_test_ids
minvalue 6000
maxvalue 99999999
start with 6000
increment by 1
nocache
order; create or replace trigger tri_test_id
before insert on ronyee_company
for each row
declare
nextid number;
begin
IF :new.CompanyID IS NULL or :new.CompanyID=0 THEN
select seq_test_id.nextval
into nextid
from sys.dual;
:new.id:=nextid;
end if;
end tri_test_id;
constant exception <an identifier>
<a double-quoted delimited-identifier> table LONG_ double ref
char time timestamp interval date binary national character
nchar
Line: 13
Text: Counts in Number(10);Error: PLS-00103: Encountered the symbol ";" when expecting one of the following:
. ( * % & - + / at loop mod remainder range rem ..
<an exponent (**)> || multiset
Line: 13
Text: Counts in Number(10);Error: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
pragma
Line: 26
改成:
Counts Number(10);
基本上你把他写的全复制过去就行了