--示例 11-2：
DECLARE
message varchar2(20):= 'Hello, World!';
BEGIN
dbms_output.put_line(message);
END;
/
--示例 11-3：
DECLARE
part_number NUMBER(6);
part_name VARCHAR2(20);
in_stock BOOLEAN;
part_price NUMBER(6,2);
part_description VARCHAR2(50);
BEGIN
NULL;
END;
/
--示例 11-4：
DECLARE
--全局变量
num1 number := 95;
num2 number := 85;
BEGIN
dbms_output.put_line('Outer Variable num1: ' || num1);
dbms_output.put_line('Outer Variable num2: ' || num2);
DECLARE
--局部变量
num1 number := 195;
num2 number := 185;
BEGIN
dbms_output.put_line('Inner Variable num1: ' || num1);
dbms_output.put_line('Inner Variable num2: ' || num2);
END;
END;
/
--示例 11-5：
DECLARE
c_id customer.id%type := 1;
c_name customer.name%type;
c_addr customer.address%type;
c_sal customer.salary%type;
BEGIN
SELECT name, address, salary INTO c_name, c_addr, c_sal
FROM customer
WHERE id = c_id;
dbms_output.put_line
('Customer ' ||c_name || ' from ' || c_addr || ' earns ' || c_sal);
END;
/
--示例 11-6：
DECLARE
credit_limit CONSTANT REAL := 5000.00; max_days_in_year CONSTANT
INTEGER := 366;
urban_legend CONSTANT BOOLEAN := FALSE;
BEGIN
NULL;
END;
/
--示例 11-7：
DECLARE
deadlock_detected EXCEPTION;
PRAGMA EXCEPTION_INIT(deadlock_detected, -60);
BEGIN
NULL;
EXCEPTION
WHEN deadlock_detected THEN
NULL;
END;
/
--示例 11-8：
DECLARE
stock_price NUMBER := 9.73;
net_earnings NUMBER := 0;
pe_ratio NUMBER;
BEGIN
pe_ratio := stock_price / net_earnings; -- 触发 ZERO_DIVIDE 异常
DBMS_OUTPUT.PUT_LINE('Price/earnings ratio = ' || pe_ratio);
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('公司收益为 0');
pe_ratio := NULL;
END;
/
--示例 11-9：
DECLARE
invalidDep EXCEPTION;
department VARCHAR2(10);
BEGIN
department := '&Dep';
IF department NOT IN ('CS','BIO','Maths','PHY') THEN
RAISE invalidDep;
ELSE
DBMS_OUTPUT.PUT_LINE('您输入系别的是'||department);
END IF;
EXCEPTION
WHEN invalidDep THEN
DBMS_OUTPUT.PUT_LINE('无法识别该系别');
END;
/
--示例 11-10：
CREATE PROCEDURE account_status (due_date DATE,today DATE )
IS
BEGIN
IF due_date < today THEN -- 显式触发异常
RAISE_APPLICATION_ERROR(-20000, '账户已过期');
END IF;
END;
DECLARE
past_due EXCEPTION; -- 声明异常
PRAGMA EXCEPTION_INIT (past_due, -20000); -- 给异常分配错误码
BEGIN
account_status (to_date('2018-01-01','yyyy-mm-dd'), to_date('2018-01-03','yyyy-
mm-dd')); -- 调用过程
EXCEPTION
WHEN past_due THEN -- 处理异常
DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQLERRM(-20000)));
END;
--示例 11-12：
DECLARE
SUBTYPE Balance IS NUMBER(8,2);
checking_account Balance;
savings_account Balance;
BEGIN
checking_account := 2000.00;
savings_account := 1000000.00;
END;
--示例 11-13：
DECLARE
SUBTYPE name IS char(20);
SUBTYPE message IS varchar2(100);
salutation name;
greetings message;
BEGIN
salutation := 'Reader ';
greetings := 'Welcome to the World of PL/SQL';
dbms_output.put_line('Hello ' || salutation || greetings);
END;
/
--示例 11-14：
DECLARE
dept_rec departments%ROWTYPE;
BEGIN
dept_rec.department_id := 10;
dept_rec.department_name := 'Administration';
dept_rec.manager_id := 200;
dept_rec.location_id := 1700;
-- Print fields:
DBMS_OUTPUT.PUT_LINE('dept_id: ' || dept_rec.department_id);
DBMS_OUTPUT.PUT_LINE('dept_name: ' || dept_rec.department_name);
DBMS_OUTPUT.PUT_LINE('mgr_id: ' || dept_rec.manager_id);
DBMS_OUTPUT.PUT_LINE('loc_id: ' || dept_rec.location_id);
END;
/
--示例 11-15：
DECLARE
surname employees.last_name%TYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('surname=' || surname);
END;
/
--示例 11-16：
DECLARE
howmany NUMBER;
num_tables NUMBER;
BEGIN
-- 开始处理
SELECT COUNT(*) INTO howmany
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'TABLE'; -- 获取表的数目
num_tables := howmany; -- 赋值
END;
/
--示例 11-17：
DECLARE
some_condition BOOLEAN;
pi NUMBER := 3.1415926;
radius NUMBER := 15;
area NUMBER;
BEGIN
/* 执行一些简单的判断和赋值 */
IF 2 + 2 = 4 THEN
some_condition := TRUE;
/* THEN 分支总是会被执行 */
END IF;
/*此行使用 pi 计算圆的面积，
pi 是圆周和直径之间的比率。
计算区域后，将显示结果. */
area := pi * radius**2;
DBMS_OUTPUT.PUT_LINE('The area is: ' || TO_CHAR(area));
END;
/
