1.sql%rowcount在存储过程记录更新或插入的记录数
2.concat(‘’,‘’)追加字符串
3.IF语句(转的)
3.1.
IF THEN
END IF;
SQL> set serveroutput on;
SQL> declare
x number(3):=9;
begin
if x<10 then
dbms_output.put_line('x is less than10');
end if;
end;
3.2
IF - ELSE 语句
IF THEN
ELSE
END IF;
DECLARE
x NUMBER(3) := 10;
BEGIN
IF x < 10 THEN
dbms_output.put_line('X is less than 10');
ELSE
dbms_output.put_line('X is not less than 10');
END IF;
END;
3.3
IF - ELSIF - ELSE 语句
IF THEN
ELSIF THEN
ELSIF THEN
ELSE
END IF;
DECLARE
x NUMBER(3) := 47;
BEGIN
IF x < 10 THEN
dbms_output.put_line('X is less than 10');
ELSIF x = 10 THEN
dbms_output.put_line('X is equal to 10');
ELSIF x < 100 THEN
dbms_output.put_line('X is between 11 and 99');
ELSE
dbms_output.put_line('X is greater than 99');
END IF;
END;
3.4
与NULL值比较处理
declare
v NUMBER;
begin
if v = 1 then
DBMS_OUTPUT.put_line('Equal to 1');
elsif v!= 1 then
DBMS_OUTPUT.put_line('Not equal to 1');
elsif v = v then
DBMS_OUTPUT.put_line('Equal to itself');
else
DBMS_OUTPUT.put_line('Undefined result');
end if;
v:=v+1;
DBMS_OUTPUT.put_line('New value: <'||v||'>');
end;
4.使用DBMS_LOCK手动锁定
赋权限:grant execute on dbms_lock to xxx
使用dbms_lock包提供的request()和release()方法进行锁定
LRESULT NUMBER;
–手动添加一把锁,只允许一个进程调动该存储过程
LRESULT := DBMS_LOCK.REQUEST(ID => 0, RELEASE_ON_COMMIT => FALSE);
–释放锁
LRESULT := DBMS_LOCK.RELEASE(ID => 0);