《Oracle PL/SQL实例精讲》学习笔记3——PL/SQL语言基础

本章学习了以下新知识：

挂靠数据类型（Anchor）的语法：<变量名> <类型属性>%Type

 

源代码：

-- *** Chapter Exercises *** --
-- For Example ch01_1b.sql


SET SERVEROUTPUT ON
DECLARE
   v_name VARCHAR2(30); 
   v_dob DATE; 
   v_us_citizen BOOLEAN; 
BEGIN
   DBMS_OUTPUT.PUT_LINE(v_name||'born on'||v_dob);
END; 


-- For Example ch01_2a.sql

SET SERVEROUTPUT ON
DECLARE
   v_name student.first_name%TYPE; 
   v_grade grade.numeric_grade%TYPE; 
BEGIN
   DBMS_OUTPUT.PUT_LINE(NVL(v_name, 'No Name ')||
      ' has grade of '||NVL(v_grade, 0));
END; 


-- For Example ch01_3a.sql
SET SERVEROUTPUT ON
DECLARE
   v_lname VARCHAR2(30); 
   v_regdate DATE; 
   v_pctincr CONSTANT NUMBER(4,2) := 1.50; 
   v_counter NUMBER := 0; 
   v_new_cost course.cost%TYPE; 
   v_YorN BOOLEAN := TRUE; 
BEGIN
v_counter := ((v_counter + 5)*2) / 2; 
v_new_cost := (v_new_cost * v_counter)/4; 
--
   v_counter := COALESCE(v_counter, 0) + 1; 
   v_new_cost := 800 * v_pctincr; 
--
       DBMS_OUTPUT.PUT_LINE(V_COUNTER);
       DBMS_OUTPUT.PUT_LINE(V_NEW_COST);
END; 



-- For Example ch01_4a.sql

set serveroutput on
   << find_stu_num >>

   BEGIN
      DBMS_OUTPUT.PUT_LINE('The procedure 
                  find_stu_num has been executed.');
   END find_stu_num; 


-- For Example ch01_4b.sql

SET SERVEROUTPUT ON
<< outer_block >>
DECLARE
   v_test NUMBER := 123;
BEGIN
   DBMS_OUTPUT.PUT_LINE
      ('Outer Block, v_test: '||v_test);
   << inner_block >>
   DECLARE
      v_test NUMBER := 456;   
   BEGIN   
      DBMS_OUTPUT.PUT_LINE
         ('Inner Block, v_test: '||v_test);
      DBMS_OUTPUT.PUT_LINE
         ('Inner Block, outer_block.v_test: '||
           Outer_block.v_test);
   END inner_block;
END outer_block; 


-- For Example ch01_5a.sql


SET SERVEROUTPUT ON
DECLARE
   e_show_exception_scope EXCEPTION;
   v_student_id           NUMBER := 123;
BEGIN
  DBMS_OUTPUT.PUT_LINE('outer student id is '
     ||v_student_id);
   DECLARE
     v_student_id    VARCHAR2(8) := 125;
   BEGIN
      DBMS_OUTPUT.PUT_LINE('inner student id is '
         ||v_student_id);
      RAISE e_show_exception_scope;
   END;
EXCEPTION
   WHEN e_show_exception_scope
   THEN
      DBMS_OUTPUT.PUT_LINE('When am I displayed?');
      DBMS_OUTPUT.PUT_LINE('outer student id is '
         ||v_student_id);
END;