ORACLE DB Learning

匿名块：

简单原型

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;
/