09.txt

Oracle database 10g基础教程（第二版） 源码

set serveroutput on
declare
  example_text varchar2(100);
begin
  example_text:='欢迎您!';
  dbms_output.put_line(example_text);
exception
  when others then
    dbms_output.put_line('出现异常了!');
    raise;
end;
/

 








declare
  example_number_variable number;
begin
  example_number_variable := 60;
end;
/
declare
  example_number_variable number := 60;
begin
  null;
end;
/










declare
  example_number_constant constant number := 60;
begin
  null;
end;
/
declare
  example_number_constant constant number;
begin
  null;
end;
/
declare
  example_number_constant constant number := 60;
begin
  example_number_constant := 50;
end;
/







declare
  example_number_constant constant number default 2;
  example_number_variable number default 3;
begin
  example_number_constant := 5;
  example_number_variable := 120;
end;
/



declare
  example_parent_number number;
begin
  -- example_parent_number变量是可视的，且在作用域
  example_parent_number := 1;

  declare 
    example_child_number number := 2;
  begin
    -- example_child_number变量是可视的，且在作用域
    dbms_output.put_line('parent_child='
                         ||to_char(example_parent_number + example_child_number));
  end;

  -- example_child_number变量是不可视的，且不在作用域
  example_child_number := 2;
end;
/





declare
  example_variable_1 varchar2(100);
  example_variable_2 varchar2(100) := '';
  example_variable_3 varchar2(100) := null;
  example_variable_4 varchar2(100) default null;
begin
  null;
end;
/



declare 
  example_variable_varchar2 varchar2(100);
begin
end;
/



create table departments(
department_id number,
department_name varchar2(20)
);



declare
  example_dept departments%rowtype;
  example_another_dept departments.department_name%type;
begin
  example_dept.department_id := 1000;
  example_dept.department_name := '行政部';

  insert into departments(department_id, department_name)
  values(example_dept.department_id, example_dept.department_name);

  example_dept.department_id := 1001;
  example_another_dept := '销售部';

  insert into departments(department_id, department_name)
  values(example_dept.department_id, example_another_dept);

  dbms_output.put_line(example_dept.department_name||'和'||example_another_dept);
end;
/








declare
  example_boolean boolean;
begin
  example_boolean := 'true';
end;
/


declare
  example_boolean boolean;
begin
  example_boolean := true;
end;
/






set serveroutput on
declare
  var_datetime timestamp (9) := systimestamp;
begin
  dbms_output.put_line(var_datetime );
end;
/


set serveroutput on
declare
  var_datetime timestamp (3) with time zone := systimestamp;
begin
  dbms_output.put_line(var_datetime );
end;
/









set serveroutput on
declare
  type location_record_type is record(
    street_address        varchar2(40),
    postal_code           varchar2(6),
    city                  varchar2(10),
    country               char(3) not null := 'CHN'
  );
  l_my_loc location_record_type;
begin
  l_my_loc.street_address := '海淀区中关村大街18号';
  l_my_loc.postal_code := '100083';
  l_my_loc.city := '北京市';
  dbms_output.put_line('我的地址是：');
  dbms_output.put_line(l_my_loc.street_address);
  dbms_output.put_line(l_my_loc.postal_code);
  dbms_output.put_line(l_my_loc.city);
  dbms_output.put_line(l_my_loc.country);
end;
/






set serveroutput on
declare 
  type my_text_table_type is table of varchar2(20) index by binary_integer;
  l_text_table my_text_table_type;

begin
  l_text_table(1) := '一条高速公路';
  l_text_table(2) := '一大片麦田';
  dbms_output.put_line('我们有'||l_text_table.count||'个varchar2变量');
  dbms_output.put_line('变量(1)='||l_text_table(1));
  dbms_output.put_line('变量(2)='||l_text_table(2));
end;
/



set serveroutput on
declare 
  type my_text_table_type is table of varchar2(200) index by binary_integer;
  l_text_table my_text_table_type;
  l_index number;

begin
  for emp_rec in (select * from emp) loop
    l_text_table(emp_rec.empno) := emp_rec.ename;
  end loop;
  l_index := l_text_table.first;
  loop
    exit when l_index is null;
    dbms_output.put_line(l_index||' : '||l_text_table(l_index));
    l_index := l_text_table.next(l_index);
  end loop;
end;
/




set serverout on
declare
begin
  for my_dept_rec in(select deptno, dname, loc
                     from dept order by deptno)
  loop
    dbms_output.put(my_dept_rec.deptno);
    dbms_output.put_line('部门名称是'||my_dept_rec.dname||'，位于'||my_dept_rec.loc||'市。');
  end loop;
end;
/







set echo off
set define '&'
set verify off
set serveroutput on size 10000

accept num prompt '请输入一个数字:'
declare
  l_num number := #
begin
  if l_num = 1 then
    dbms_output.put_line('你输入的是数字1');
elsif l_num = 2 then
  dbms_output.put_line('你输入的是数字2');
elsif l_num = 3 then
  dbms_output.put_line('你输入的是数字3');
elsif l_num = 4 then
  dbms_output.put_line('你输入的是数字4');
elsif l_num = 5 then
  dbms_output.put_line('你输入的是数字5');
elsif l_num = 6 then
  dbms_output.put_line('你输入的是数字6');
elsif l_num = 7 then
  dbms_output.put_line('你输入的是数字7');
elsif l_num = 8 then
  dbms_output.put_line('你输入的是数字8');
elsif l_num = 9 then
  dbms_output.put_line('你输入的是数字9');
elsif l_num = 0 then
  dbms_output.put_line('你输入的是数字0');
else
  dbms_output.put_line('你输入的数字太多了…');
  end if;
end;
/

























set echo off
set define '&'
set verify off
set serveroutput on size 10000

accept num prompt '请输入一个数字:'
declare
  l_num number := #
begin
  case l_num
    when 1 then dbms_output.put_line('你输入的是数字1');
    when 2 then dbms_output.put_line('你输入的是数字2');
    when 3 then dbms_output.put_line('你输入的是数字3');
    when 4 then dbms_output.put_line('你输入的是数字4');
    when 5 then dbms_output.put_line('你输入的是数字5');
    when 6 then dbms_output.put_line('你输入的是数字6');
    when 7 then dbms_output.put_line('你输入的是数字7');
    when 8 then dbms_output.put_line('你输入的是数字8');
    when 9 then dbms_output.put_line('你输入的是数字9');
    when 0 then dbms_output.put_line('你输入的是数字0');
    else dbms_output.put_line('你输入的数字太多了…');
  end case;
end;
/



set serverout on
declare l_loops number := 0;
begin
  dbms_output.put_line('循环开始');

  loop
    if l_loops > 4 then
      exit;
    end if;
    dbms_output.put_line('第'||l_loops||'次循环');
    l_loops := l_loops +1;
  end loop;

  dbms_output.put_line('循环结束');
end;
/







set serverout on
declare l_loops number := 0;
begin
  dbms_output.put_line('循环开始');

  loop
    exit when l_loops > 4;
    dbms_output.put_line('第'||l_loops||'次循环');
    l_loops := l_loops +1;
  end loop;

  dbms_output.put_line('循环结束');
end;
/








set serverout on
declare l_loops number := 0;
begin
  dbms_output.put_line('循环开始');

  while l_loops < 5 loop
    dbms_output.put_line('第'||l_loops||'次循环');
    l_loops := l_loops +1;
  end loop;

  dbms_output.put_line('循环结束');
end;
/




create table departments(
department_id number(4) primary key,
department_name varchar2(30));

insert into departments values(1000, 'Admin');

declare
  deptID number(4);
  deptName varchar2(30);
begin
  deptID := 1000;
  deptName := 'Admin';
  insert into departments(department_id, department_name)
  values(deptID, deptName);
end;
/










set serverout on
declare
  deptID number(4);
  deptName varchar2(30);
begin
  deptID := 1000;
  deptName := 'Admin';
  insert into departments(department_id, department_name)
  values(deptID, deptName);

  exception
    when dup_val_on_index then
      dbms_output.put_line('代码执行过程中遇到了dup_val_on_index异常！');
end;
/


















set echo off
set verify off
set define '&'

prompt '汤碗的温度是多少？：'
accept temp default '37'

declare
  porridge_too_hot exception;
  porridge_too_cold exception;
begin
  case
    when '&temp' < 30.00 then raise porridge_too_cold;
    when '&temp' > 60.00 then raise porridge_too_hot;
    else null;
  end case;
  dbms_output.put_line('汤碗的温度正合适！');

  exception
    when value_error then
      dbms_output.put_line('请输入一个数字化的温度，例如37。');
    when porridge_too_hot then
      dbms_output.put_line('汤碗的温度太高了！');
    when porridge_too_cold then
      dbms_output.put_line('汤碗的温度太低了！');
end;
/