对于stored procedure .sybase 和oracle的语法当然是有所不同.
但是,差别想来不会太大.
小例子,你先看看吧:
静态:
CREATE OR REPLACE FUNCTION GET_EXC_RATE_US
( a_v_from_curr_code VARCHAR2,
a_d_date DATE)
RETURN NUMBER IS
l_n_rate NUMBER;
l_n_from_rate NUMBER;
l_n_to_rate NUMBER;
BEGIN
IF a_v_from_curr_code IS NULL THEN
RETURN 0;
END IF;
IF a_v_from_curr_code = 'USD' THEN
RETURN 1;
END IF;
SELECT n_exch_rate
INTO l_n_from_rate
FROM tb_exchange_rate
WHERE d_date = a_d_date
AND v_currency_code = a_v_from_curr_code;
SELECT n_exch_rate
INTO l_n_to_rate
FROM tb_exchange_rate
WHERE d_date = a_d_date
AND v_currency_code = 'USD';
l_n_rate := l_n_from_rate/l_n_to_rate;
RETURN l_n_rate;
EXCEPTION
WHEN OTHERS
THEN
RETURN -1;
END;
/动态
CREATE OR REPLACE PROCEDURE update_history (
tab1_name IN VARCHAR2,
tab2_name IN VARCHAR2,
date_in IN DATE
)
AS
cur INTEGER := DBMS_SQL.OPEN_CURSOR;
rows_inserted INTEGER;
BEGIN
DBMS_SQL.PARSE (
cur,
'INSERT INTO ' || tab1_name ||
' (SELECT * FROM ' || tab2_name ||
' WHERE created_on < :datelimit)',
DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE (cur, 'datelimit', date_in);
rows_inserted := DBMS_SQL.EXECUTE (cur); DBMS_SQL.CLOSE_CURSOR (cur);
END;
但是,差别想来不会太大.
小例子,你先看看吧:
静态:
CREATE OR REPLACE FUNCTION GET_EXC_RATE_US
( a_v_from_curr_code VARCHAR2,
a_d_date DATE)
RETURN NUMBER IS
l_n_rate NUMBER;
l_n_from_rate NUMBER;
l_n_to_rate NUMBER;
BEGIN
IF a_v_from_curr_code IS NULL THEN
RETURN 0;
END IF;
IF a_v_from_curr_code = 'USD' THEN
RETURN 1;
END IF;
SELECT n_exch_rate
INTO l_n_from_rate
FROM tb_exchange_rate
WHERE d_date = a_d_date
AND v_currency_code = a_v_from_curr_code;
SELECT n_exch_rate
INTO l_n_to_rate
FROM tb_exchange_rate
WHERE d_date = a_d_date
AND v_currency_code = 'USD';
l_n_rate := l_n_from_rate/l_n_to_rate;
RETURN l_n_rate;
EXCEPTION
WHEN OTHERS
THEN
RETURN -1;
END;
/动态
CREATE OR REPLACE PROCEDURE update_history (
tab1_name IN VARCHAR2,
tab2_name IN VARCHAR2,
date_in IN DATE
)
AS
cur INTEGER := DBMS_SQL.OPEN_CURSOR;
rows_inserted INTEGER;
BEGIN
DBMS_SQL.PARSE (
cur,
'INSERT INTO ' || tab1_name ||
' (SELECT * FROM ' || tab2_name ||
' WHERE created_on < :datelimit)',
DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE (cur, 'datelimit', date_in);
rows_inserted := DBMS_SQL.EXECUTE (cur); DBMS_SQL.CLOSE_CURSOR (cur);
END;
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货