匿名块:
简单原型
BEGIN
NULL;
END;
/
简单输入输出
SET SERVEROUTPUT ON SIZE 1000000
DECLARE
lv_hello CONSTANT VARCHAR2(5) := 'hello';
lv_whom VARCHAR2(30);
BEGIN
lv_whom := '&input';
dbms_output.put_line(lv_hello || ' world, ' || lv_whom);
dbms_output.put_line(q'(Tom's cat is missing.)');
dbms_output.put_line('Tom''s cat is missing.');
END;
/
会话级绑定变量
VARIABLE bv VARCHAR2(30);
BEGIN
:bv := 'Tom';
END;
/
DECLARE
lv_whom VARCHAR2(30);
BEGIN
lv_whom := :bv;
dbms_output.put_line(lv_whom);
END;
/
@F:/test.sql
命名块:
过程块
CREATE OR REPLACE PROCEDURE hello_procedure
( pv_whom VARCHAR2 ) IS
BEGIN
dbms_output.put_line('hello ' || pv_whom);
END;
/
EXECUTE hello_procedure('Tom');
BEGIN
hello_procedure('Tom');
END;
/
函数块
CREATE OR REPLACE FUNCTION hello_function
( pv_whom VARCHAR2 ) RETURN VARCHAR2 IS
BEGIN
RETURN 'hello ' || pv_whom;
END;
/
VARIABLE result VARCHAR2(20);
CALL hello_function('Tom') INTO :result;
PRINT :result
SELECT :result from dual;
区别:过程以实现某种操作为目的,而函数是为了获得一个计算结果值。
DECLARE
PROCEDURE a;
FUNCTION b RETURN VARCHAR2;
PROCEDURE a IS
BEGIN
dbms_output.put_line(b || ' world');
END a;
FUNCTION b RETURN VARCHAR2 IS
BEGIN
RETURN 'hello';
END b;
BEGIN
a;
END;
/
日期和时间戳
DECLARE
lv_date1 DATE := '11-5月-2012';
lv_date2 DATE := SYSDATE;
lv_interval INTERVAL DAY TO SECOND;
BEGIN
lv_date1 := lv_date1 + 1;
dbms_output.put_line(TO_CHAR(lv_date2, 'YY-MON-DD HH24:MI:SS'));
dbms_output.put_line(TO_CHAR(TRUNC(lv_date2), 'YY-MON-DD HH24:MI:SS'));
lv_interval := TO_TIMESTAMP(lv_date2) - TO_TIMESTAMP(lv_date1);
dbms_output.put_line(lv_interval);
END;
/
DECLARE
lv_date1 TIMESTAMP := '11-5月-12';
lv_date2 TIMESTAMP := SYSTIMESTAMP;
lv_interval INTERVAL DAY(9) TO SECOND;
BEGIN
lv_interval := lv_date2 - lv_date1;
dbms_output.put_line(lv_interval);
END;
/