存储过程语法有点不一样,下面是样本,希望对你有用,比较忙,不能帮你转换: ---创建程序包 CREATE OR REPLACE PACKAGE DBOSAM.TYPES AS -- THIS FILE INCLUDES ALL REFERENCES NEEDED FOR THE APP TYPE MYCURSOR IS REF CURSOR; END TYPES; / -存储过程 create or replace procedure dbosam.proc_SearchAllNote ( tempcursor out types.mycursor,--用作返回记录的游标 strKeyword varchar2, strClassID VARCHAR2, BeginDate varchar2, EndDate varchar2, strSenderName varchar2, intOpSort number '1根据标题来查找,2根据内容来找 ) IS TEMPENDDATE DATE; begin IF ENDDATE='TODAY' THEN null; ELSE select sysdate into TEMPENDDATE from dual; --TEMPENDDATE:=TO_DATE(ENDDATE); END IF; if intOpSort=1 then ---根据标题来查找 OPEN TEMPCURSOR FOR SELECT DISTINCT NOTEID,NOTECAPTION,SENDDATE,NOTESCORE,SENDERID,SENDERNAME,ISVALID,NOTECLASSID,NOTECLASS,ISEND,REPLYNUM FROM DBOSAM.VIEWFORUMBASE WHERE NOTECAPTION LIKE STRKEYWORD AND NOTECLASSID LIKE STRCLASSID AND SENDDATE BETWEEN TO_DATE(BEGINDATE) AND TEMPENDDATE AND ROWNUM<301 ORDER BY SENDDATE DESC; ELSIF INTOPSORT=2 THEN---根据内容来查找 OPEN TEMPCURSOR FOR SELECT DISTINCT NOTEID,NOTECAPTION,SENDDATE,NOTESCORE,SENDERID,SENDERNAME,ISVALID,NOTECLASSID, NOTECLASS,ISEND,REPLYNUM FROM DBOSAM.VIEWFORUMALL WHERE NOTECLASSID LIKE STRCLASSID AND (NOTECONTENT LIKE STRKEYWORD OR REPLYCONTENT LIKE STRKEYWORD) AND SENDDATE BETWEEN TO_DATE(BEGINDATE) AND TEMPENDDATE AND ROWNUM<301 ORDER BY SENDDATE DESC; END IF; end proc_SearchAllNote; /
谢谢JohnsonPoon(三木俊子),我的过程有什么语法错误吗?其实就是从一个表中取出sum(),然后插入另外一个表中,很简单,谁帮我写一个例子 Create OR Replace Procedure D_Proc_ADAccount ( Order_ID varchar(20) ) is Item_Account integer; ADVendor_ID varchar(20); Settle_Period varchar(20); ADVendor_Name varchar(50); begin select ADVendorID,SettlePeriod into ADVendor_ID,Settle_Period from adms_ADItemAdd where AdOrderId=Order_ID;
select CompanyName into ADVendor_Name from adms_customerinfo where AccountId=ADVendor_ID; select sum(adms_ADItemAdd.Value) into Item_Account from adms_ADItemAdd004 where AdOrderId=Order_ID; insert into adms_ADVendorAccount values(Settle_Period,AdDVendor_ID,ADVendor_Name,Item_Account,0,sysdate); end D_Proc_ADAccount;
我把每一个语句拆开都正确,但是总是Invalid,谁能帮我看看,急呀,谢谢! 下面的绝对不会有表的字段的问题,也不会有字段null,无法插入的问题,等着你的答案,谢谢!最后insert 的 value 我用具体数据代替了,但是还是Invalid Create OR Replace Procedure D_Proc_ADAccount ( Order_ID number ) as Item_Account integer; ADVendor_ID number; ADVendor_Name varchar(50); begin select ADVendorID into ADVendor_ID from adms_ADItemAdd where AdOrderId=Order_ID;
select CompanyName into ADVendor_Name from adms_customerinfo where AccountId=ADVendor_ID; select sum(adms_ADItemAdd.Value) into Item_Account from adms_ADItemAdd where AdOrderId=Order_ID; insert into adms_ADVendorAccount(ADVENDORID,ADVENDORNAME,ADORDERID,ADBARGAIN,POPCOUNT, CLICKCOUNT,TOTALVALUE,REALVALUE,STATUS,CREATETIME) values(1,'11',1,'1',1,1,1,1,1,sysdate); end D_Proc_ADAccount;
Order_ID number? 是什么意思? 好象应该是Order_ID In number or Order_ID out number. 必须定义是输入还是输出参数。
CREATE OR REPLACE PROCEDURE D_Proc_ADAccount ( i_OrderID IN VARCHAR2 ) AS v_ItemAccount INTEGER; v_ADVendorID VARCHAR2(20); v_SettlePeriod VARCHAR2(20); v_ADVendorName VARCHAR2(20); BEGIN SELECT ADVendorID, SettlePeriod INTO v_ADVendorID, v_SettlePeriod FROM adms_ADItemAdd WHERE AdOrderId = i_OrderID; SELECT CompanyName INTO v_ADVendorName FROM adms_customerinfo WHERE AccountId = v_ADVendorID; SELECT SUM(Value) INTO v_ItemAccount FROM adms_ADItemAdd WHERE AdOrderId = i_OrderID; INSERT INTO adms_ADVendorAccount VALUES(v_SettlePeriod, v_ADVendorID, v_ADVendorName, v_ItemAccount, 0, SYSDATE); COMMIT; END; /
不行呀,还是InValid, ERROR at line 1: ORA-06550: line 1, column 7: PLS-00905: object ADMS01.TEST0 is invalid ORA-06550: line 1, column 7: PL/SQL: Statement ignored
PLS-00905可是编译错误,看看你的ADMS01.TEST0这个冬冬是否有效PLS-00905 object string is invalid Cause: An invalid package specification or stored subprogram was referenced. A package specification or stored subprogram is invalid if its source code or any database object it references has been DROPped, REPLACEd, or ALTERed since it was last compiled. Action: Find out what invalidated the package specification or stored subprogram, then make sure that Oracle can recompile it without errors.
Create Produce D_Proc_ADAccount(p_OrderID in varchar2) as v_ItemAccount int v_ADVendorID varchar(20); v_SettlePeriod varchar(20); v_ADVendorName varchar(20); begin select ADVendorID,SettlePeriod into v_ADVendorID,v_SettlePeriod from adms_ADItemAdd where AdOrderId=p_OrderID; select CompanyName into v_ADVendorName from adms_customerinfo where AccountId=v_ADVendorID; select sum(Value) into v_ItemAccount from adms_ADItemAdd where AdOrderId=p_OrderID; insert into adms_ADVendorAccount values(v_SettlePeriod,v_AdDVendorID,v_ADVendorName,v-ItemAccount,0,sysdate); end; /
Create OR Replace Procedure D_Proc_ADAccount ( Order_ID in varchar2 --传入参数要in ) as -- not is Item_Account intr; ADVendor_ID varchar(20); Settle_Period varchar(20); ADVendor_Name varchar(50); begin select ADVendorID,SettlePeriod into ADVendor_ID,Settle_Period from adms_ADItemAdd where AdOrderId=Order_ID;
select CompanyName into ADVendor_Name from adms_customerinfo where AccountId=ADVendor_ID; select sum(adms_ADItemAdd.Value) into Item_Account from adms_ADItemAdd004 where AdOrderId=Order_ID; insert into adms_ADVendorAccount values(Settle_Period,AdDVendor_ID,ADVendor_Name,Item_Account,0,sysdate); end;
Create OR Replace Procedure D_Proc_ADAccount ( i_OrderID in number ) as v_ItemAccount integer; v_ADVendorID number; v_ADVendorName varchar(50); begin select ADVendorID into v_ADVendorID from adms_ADItemAdd where AdOrderId=i_OrderID;
select CompanyName into v_ADVendorName from adms_customerinfo where AccountId=v_ADVendorID;
select sum(Value) into v_ItemAccount from adms_ADItemAdd where AdOrderId=Order_ID;
insert into adms_ADVendorAccount (ADVENDORID,ADVENDORNAME,ADORDERID,ADBARGAIN,POPCOUNT, CLICKCOUNT,TOTALVALUE,REALVALUE,STATUS,CREATETIME) values(1,'11',1,'1',1,1,1,1,1,sysdate); end D_Proc_ADAccount; 那位看一下,到底有什么错误,因为一直InValid,急呀
---创建程序包
CREATE OR REPLACE PACKAGE DBOSAM.TYPES AS
-- THIS FILE INCLUDES ALL REFERENCES NEEDED FOR THE APP
TYPE MYCURSOR IS REF CURSOR;
END TYPES;
/
-存储过程
create or replace procedure dbosam.proc_SearchAllNote
(
tempcursor out types.mycursor,--用作返回记录的游标
strKeyword varchar2,
strClassID VARCHAR2,
BeginDate varchar2,
EndDate varchar2,
strSenderName varchar2,
intOpSort number '1根据标题来查找,2根据内容来找 )
IS
TEMPENDDATE DATE;
begin
IF ENDDATE='TODAY' THEN
null;
ELSE
select sysdate into TEMPENDDATE from dual;
--TEMPENDDATE:=TO_DATE(ENDDATE);
END IF;
if intOpSort=1 then ---根据标题来查找
OPEN TEMPCURSOR FOR
SELECT DISTINCT NOTEID,NOTECAPTION,SENDDATE,NOTESCORE,SENDERID,SENDERNAME,ISVALID,NOTECLASSID,NOTECLASS,ISEND,REPLYNUM
FROM DBOSAM.VIEWFORUMBASE
WHERE NOTECAPTION LIKE STRKEYWORD AND NOTECLASSID LIKE STRCLASSID AND SENDDATE BETWEEN TO_DATE(BEGINDATE) AND TEMPENDDATE AND ROWNUM<301 ORDER BY SENDDATE DESC;
ELSIF INTOPSORT=2 THEN---根据内容来查找
OPEN TEMPCURSOR FOR
SELECT DISTINCT NOTEID,NOTECAPTION,SENDDATE,NOTESCORE,SENDERID,SENDERNAME,ISVALID,NOTECLASSID, NOTECLASS,ISEND,REPLYNUM FROM DBOSAM.VIEWFORUMALL
WHERE NOTECLASSID LIKE STRCLASSID AND (NOTECONTENT LIKE STRKEYWORD OR REPLYCONTENT LIKE STRKEYWORD) AND SENDDATE BETWEEN TO_DATE(BEGINDATE) AND TEMPENDDATE AND ROWNUM<301 ORDER BY SENDDATE DESC;
END IF;
end proc_SearchAllNote;
/
Create OR Replace Procedure D_Proc_ADAccount
(
Order_ID varchar(20)
)
is
Item_Account integer;
ADVendor_ID varchar(20);
Settle_Period varchar(20);
ADVendor_Name varchar(50);
begin
select ADVendorID,SettlePeriod
into ADVendor_ID,Settle_Period
from adms_ADItemAdd where AdOrderId=Order_ID;
select CompanyName into ADVendor_Name from adms_customerinfo where AccountId=ADVendor_ID;
select sum(adms_ADItemAdd.Value) into Item_Account from adms_ADItemAdd004 where AdOrderId=Order_ID;
insert into adms_ADVendorAccount values(Settle_Period,AdDVendor_ID,ADVendor_Name,Item_Account,0,sysdate);
end D_Proc_ADAccount;
下面的绝对不会有表的字段的问题,也不会有字段null,无法插入的问题,等着你的答案,谢谢!最后insert 的 value 我用具体数据代替了,但是还是Invalid
Create OR Replace Procedure D_Proc_ADAccount
(
Order_ID number
)
as
Item_Account integer;
ADVendor_ID number;
ADVendor_Name varchar(50);
begin
select ADVendorID into ADVendor_ID
from adms_ADItemAdd where AdOrderId=Order_ID;
select CompanyName into ADVendor_Name from adms_customerinfo where AccountId=ADVendor_ID;
select sum(adms_ADItemAdd.Value) into Item_Account from adms_ADItemAdd where AdOrderId=Order_ID;
insert into adms_ADVendorAccount(ADVENDORID,ADVENDORNAME,ADORDERID,ADBARGAIN,POPCOUNT,
CLICKCOUNT,TOTALVALUE,REALVALUE,STATUS,CREATETIME) values(1,'11',1,'1',1,1,1,1,1,sysdate);
end D_Proc_ADAccount;
是什么意思?
好象应该是Order_ID In number or Order_ID out number.
必须定义是输入还是输出参数。
(
i_OrderID IN VARCHAR2
)
AS
v_ItemAccount INTEGER;
v_ADVendorID VARCHAR2(20);
v_SettlePeriod VARCHAR2(20);
v_ADVendorName VARCHAR2(20);
BEGIN
SELECT ADVendorID, SettlePeriod
INTO v_ADVendorID, v_SettlePeriod
FROM adms_ADItemAdd
WHERE AdOrderId = i_OrderID; SELECT CompanyName
INTO v_ADVendorName
FROM adms_customerinfo
WHERE AccountId = v_ADVendorID; SELECT SUM(Value)
INTO v_ItemAccount
FROM adms_ADItemAdd
WHERE AdOrderId = i_OrderID; INSERT INTO adms_ADVendorAccount
VALUES(v_SettlePeriod, v_ADVendorID, v_ADVendorName, v_ItemAccount, 0, SYSDATE); COMMIT;
END;
/
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00905: object ADMS01.TEST0 is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Cause: An invalid package specification or stored subprogram was referenced.
A package specification or stored subprogram is invalid if its source code or any
database object it references has been DROPped, REPLACEd, or ALTERed since
it was last compiled.
Action: Find out what invalidated the package specification or stored
subprogram, then make sure that Oracle can recompile it without errors.
as
v_ItemAccount int
v_ADVendorID varchar(20);
v_SettlePeriod varchar(20);
v_ADVendorName varchar(20);
begin
select ADVendorID,SettlePeriod into v_ADVendorID,v_SettlePeriod from adms_ADItemAdd where AdOrderId=p_OrderID;
select CompanyName into v_ADVendorName from adms_customerinfo where AccountId=v_ADVendorID;
select sum(Value) into v_ItemAccount from adms_ADItemAdd where AdOrderId=p_OrderID;
insert into adms_ADVendorAccount
values(v_SettlePeriod,v_AdDVendorID,v_ADVendorName,v-ItemAccount,0,sysdate);
end;
/
(
Order_ID in varchar2 --传入参数要in
)
as -- not is
Item_Account intr;
ADVendor_ID varchar(20);
Settle_Period varchar(20);
ADVendor_Name varchar(50);
begin
select ADVendorID,SettlePeriod
into ADVendor_ID,Settle_Period
from adms_ADItemAdd where AdOrderId=Order_ID;
select CompanyName into ADVendor_Name from adms_customerinfo where AccountId=ADVendor_ID;
select sum(adms_ADItemAdd.Value) into Item_Account from adms_ADItemAdd004 where AdOrderId=Order_ID;
insert into adms_ADVendorAccount values(Settle_Period,AdDVendor_ID,ADVendor_Name,Item_Account,0,sysdate);
end;
(
i_OrderID in number
)
as
v_ItemAccount integer;
v_ADVendorID number;
v_ADVendorName varchar(50);
begin
select ADVendorID
into v_ADVendorID
from adms_ADItemAdd
where AdOrderId=i_OrderID;
select CompanyName
into v_ADVendorName
from adms_customerinfo
where AccountId=v_ADVendorID;
select sum(Value)
into v_ItemAccount
from adms_ADItemAdd
where AdOrderId=Order_ID;
insert into adms_ADVendorAccount
(ADVENDORID,ADVENDORNAME,ADORDERID,ADBARGAIN,POPCOUNT,
CLICKCOUNT,TOTALVALUE,REALVALUE,STATUS,CREATETIME)
values(1,'11',1,'1',1,1,1,1,1,sysdate);
end D_Proc_ADAccount;
那位看一下,到底有什么错误,因为一直InValid,急呀