麻烦帮忙看一下这个存储过程有什么错!!万分感谢~~~
编译在红色处报错,错误为错误(26,25): PLS-00103: 出现符号 ">"在需要下列之一时: := . ( @ % ; not null range default character CREATE OR REPLACE PROCEDURE procedure_MarketWatch(
acct_id IN NUMBER,
cust_id IN NUMBER,
ending_co_id IN NUMBER,
industry_name IN VARCHAR,
start_date IN DATE,
starting_co_id IN NUMBER,
pct_change OUT NUMBER)
IS
old_mkt_cap NUMBER(17,2) := 0.0;
new_mkt_cap NUMBER(17,2) := 0.0;
symbol varchar(15);
new_price number(12,2);
s_num_out number(12);
old_price number(12,2);
--TYPE curType IS REF CURSOR;
-- stock_list curType;
BEGIN
IF cust_id <> 0 THEN
--OPEN stock_list_cur FOR
--SELECT WI_S_SYMB FROM WATCH_ITEM, WATCH_LIST
--WHERE WI_WL_ID=WL_ID AND WL_C_ID=cust_id;
DECLARE CURSOR stock_list IS
SELECT WI_S_SYMB FROM WATCH_ITEM, WATCH_LIST
WHERE WI_WL_ID=WL_ID AND WL_C_ID=cust_id;
ELSIF industry_name <> '' THEN
DECLARE CURSOR stock_list_cur IS
SELECT S_SYMB FROM INDUSTRY, COMPANY, SECURITY
WHERE IN_NAME=industry_name
AND CO_IN_ID=IN_ID
AND CO_ID BETWEEN starting_co_id AND ending_co_id
AND S_CO_ID=CO_ID;
ELSIF acct_id <> 0 THEN
DECLARE CURSOR stock_list IS
SELECT HS_S_SYMB FROM HOLDING_SUMMARY WHERE HS_CA_ID=acct_id;
END IF;
OPEN stock_list
FETCH FROM stock_list INTO symbol;
WHILE stock_list%FOUND LOOP
SELECT LT_PRICE INTO new_price FROM LAST_TRADE WHERE LT_S_SYMB=symbol;
SELECT S_NUM_OUT INTO s_num_out FROM SECURITY WHERE S_SYMB=symbol;
SELECT DM_CLOSE INTO old_price FROM DAILY_MARKET WHERE DM_S_SYMB=symbol AND DM_DATE=start_date;
old_mkt_cap := old_mkt_cap + (s_num_out * old_price);
new_mkt_cap := new_mkt_cap + (s_num_out * new_price);
FETCH FROM stock_list INTO symbol;
END LOOP;
IF old_mkt_cap <> 0 THEN pct_change := 100 * ((new_mkt_cap / old_mkt_cap) - 1);
ELSE pct_change := 0;
END IF;
CLOSE stock_list;
COMMIT;
END procedure_MarketWatch;
编译在红色处报错,错误为错误(26,25): PLS-00103: 出现符号 ">"在需要下列之一时: := . ( @ % ; not null range default character CREATE OR REPLACE PROCEDURE procedure_MarketWatch(
acct_id IN NUMBER,
cust_id IN NUMBER,
ending_co_id IN NUMBER,
industry_name IN VARCHAR,
start_date IN DATE,
starting_co_id IN NUMBER,
pct_change OUT NUMBER)
IS
old_mkt_cap NUMBER(17,2) := 0.0;
new_mkt_cap NUMBER(17,2) := 0.0;
symbol varchar(15);
new_price number(12,2);
s_num_out number(12);
old_price number(12,2);
--TYPE curType IS REF CURSOR;
-- stock_list curType;
BEGIN
IF cust_id <> 0 THEN
--OPEN stock_list_cur FOR
--SELECT WI_S_SYMB FROM WATCH_ITEM, WATCH_LIST
--WHERE WI_WL_ID=WL_ID AND WL_C_ID=cust_id;
DECLARE CURSOR stock_list IS
SELECT WI_S_SYMB FROM WATCH_ITEM, WATCH_LIST
WHERE WI_WL_ID=WL_ID AND WL_C_ID=cust_id;
ELSIF industry_name <> '' THEN
DECLARE CURSOR stock_list_cur IS
SELECT S_SYMB FROM INDUSTRY, COMPANY, SECURITY
WHERE IN_NAME=industry_name
AND CO_IN_ID=IN_ID
AND CO_ID BETWEEN starting_co_id AND ending_co_id
AND S_CO_ID=CO_ID;
ELSIF acct_id <> 0 THEN
DECLARE CURSOR stock_list IS
SELECT HS_S_SYMB FROM HOLDING_SUMMARY WHERE HS_CA_ID=acct_id;
END IF;
OPEN stock_list
FETCH FROM stock_list INTO symbol;
WHILE stock_list%FOUND LOOP
SELECT LT_PRICE INTO new_price FROM LAST_TRADE WHERE LT_S_SYMB=symbol;
SELECT S_NUM_OUT INTO s_num_out FROM SECURITY WHERE S_SYMB=symbol;
SELECT DM_CLOSE INTO old_price FROM DAILY_MARKET WHERE DM_S_SYMB=symbol AND DM_DATE=start_date;
old_mkt_cap := old_mkt_cap + (s_num_out * old_price);
new_mkt_cap := new_mkt_cap + (s_num_out * new_price);
FETCH FROM stock_list INTO symbol;
END LOOP;
IF old_mkt_cap <> 0 THEN pct_change := 100 * ((new_mkt_cap / old_mkt_cap) - 1);
ELSE pct_change := 0;
END IF;
CLOSE stock_list;
COMMIT;
END procedure_MarketWatch;
CURSOR stock_list IS
SELECT WI_S_SYMB FROM WATCH_ITEM, WATCH_LIST
WHERE WI_WL_ID=WL_ID AND WL_C_ID=cust_id;
建议建立三个游标,
IF cust_id <> 0 THEN
ELSIF industry_name <> '' THEN
ELSIF acct_id <> 0 THEN END IF;相应open即可
是不会被执行的。
BEGIN后,即执行部分OPEN相应的游标!
还有你这句“ELSIF industry_name <> '' THEN”是判断industry_name 不为空吧,应该写成:ELSIF industry_name is not null THEN
不能再begin后面用DECLARE对游标进行声明吗?我在网上搜好像有这种情况的··初次接触这些东东··望指教·
另外在程序中游标只有在IF语句成立后才有必要存在,所以我不想声明三个游标···想只有在IF语句成立后才生成这个游标。这样有没有其他的方法呢?
改成你建议的方法后又会出现这种错误··
错误(27,25): PLS-00103: 出现符号 "IS"在需要下列之一时: := . ( @ % ; not null range default character
定义成varchar2看看!很少看到游标在begin后面声明接着执行的!
不知是不是成立!!
看样子游标只能在begin前声明了再执行··
trade_id trade_id_array) ISi NUMBER;
num_found NUMBER:=0;TYPE bid_price_array is table of TRADE.T_BID_PRICE%TYPE;
TYPE exec_name_array is table of TRADE.T_EXEC_NAME%TYPE;
TYPE is_cash_array is table of TRADE.T_IS_CASH%TYPE;
TYPE is_et_array is table of TRADE_TYPE.TT_IS_MARKET%TYPE;
TYPE trade_price_array is table of TRADE.T_TRADE_PRICE%TYPE;
TYPE settlement_amount_array is table of SETTLEMENT.SE_AMT%TYPE;
TYPE settlement_cash_due_date_array is table of SETTLEMENT.SE_CASH_DUE_DATE%TYPE;
TYPE settlement_cash_type_array is table of SETTLEMENT.SE_CASH_TYPE%TYPE;
bid_price bid_price_array;
exec_name exec_name_array;
is_cash is_cash_array;
is_et is_et_array;
trade_price trade_price_array;
settlement_amount settlement_amount_array;
settlement_cash_due_date settlement_cash_due_date_array;
settlement_cash_type settlement_cash_type_array;BEGIN
FOR i IN 0 .. max_trades LOOPSELECT SE_AMT, SE_CASH_DUE_DATE,SE_CASH_TYPE
INTO settlement_amount(i),settlement_cash_due_date(i),settlement_cash_type(i)
FROM SETTLEMENT
WHERE SE_T_ID=trade_id(i);
END LOOP;
END Procedure_TRADELOOKUP_F1;在红色处报错错误(3,1): PLS-00103: 出现符号 "CREATE" 不明白为什么啊···望解答··
1、游标的定义位置,游标需要定义在DECLARE部分,在这个地方同时可以定义变量、常量等。
2、FETCH FROM STOCK_LIST INTO SYMBOL; 可以把其中的FROM去掉。
3、OPEN CURSOR 的后面最好是加上一个 ‘;'。其实PL/SQL很简单的哈~ 只要你更规范一些,呵呵。。