DECLARE
iv_user_id NUMBER(16):= TO_NUMBER(:USER_ID);
BEGIN
:CODE := -1;
:INFO := 'TRADE OK!';
FOR i IN (SELECT trade_id FROM tf_b_trade WHERE exec_time>SYSDATE AND user_id=iv_user_id AND trade_type_code IN (110,111)) LOOP
UPDATE tf_b_trade_svc
SET modify_tag='4'
WHERE trade_id=i.trade_id
AND service_id BETWEEN 13 AND 19;
END LOOP;
:CODE := 0;
END;
iv_user_id NUMBER(16):= TO_NUMBER(:USER_ID);
BEGIN
:CODE := -1;
:INFO := 'TRADE OK!';
FOR i IN (SELECT trade_id FROM tf_b_trade WHERE exec_time>SYSDATE AND user_id=iv_user_id AND trade_type_code IN (110,111)) LOOP
UPDATE tf_b_trade_svc
SET modify_tag='4'
WHERE trade_id=i.trade_id
AND service_id BETWEEN 13 AND 19;
END LOOP;
:CODE := 0;
END;
,若要执行该块,在sqlplus里直接执行即可....
DECLARE
iv_user_id NUMBER(16):= TO_NUMBER(&USER_ID); --&USER_ID是绑定变量
BEGIN
null;
END;
在sqlplus中执行:
SQL> DECLARE
2 iv_user_id NUMBER(16):= TO_NUMBER(&USER_ID);
3
4 BEGIN
5
6 null;
7 END;
8 /
输入 user_id 的值: 5
原值 2: iv_user_id NUMBER(16):= TO_NUMBER(&USER_ID);
新值 2: iv_user_id NUMBER(16):= TO_NUMBER(5);PL/SQL 过程已成功完成。
create or replace procedure v_p(v_num in number)
as
iv_user_id number;
begin
iv_user_id := v_num;
dbms_output.put_line('iv_user_id:'||iv_user_id);
end;
在sqlplus中执行:
SQL> create or replace procedure v_p(v_num in number)
2 as
3 iv_user_id number;
4 begin
5 iv_user_id := v_num;
6 dbms_output.put_line('iv_user_id:'||iv_user_id);
7 end;
8 /过程已创建。
--调用
begin
v_p(5);
end;
SQL> set serveroutput on
SQL> begin
2 v_p(5);
3 end;
4 /
iv_user_id:5PL/SQL 过程已成功完成。