《Oracle PL/SQL开发指南》学习笔记28——源码调试——PL/SQL基础知识（第二部分）

预见未来to50

于 2018-11-26 13:47:53 发布

阅读量196

点赞数

分类专栏：数据库（DB）

本文链接：https://blog.csdn.net/hpdlzu80100/article/details/84536814

版权

数据库（DB）专栏收录该内容

368 篇文章 11 订阅

订阅专栏

1. 绑定变量赋值给变量

SQL> DECLARE
  2  lv_input VARCHAR2(30);
  3  BEGIN
  4  lv_input :=:bind_variable;
  5  dbms_output.put_line('['||lv_input||']');
  6  END;
  7  /
[HelloPL/SQL world!]

PL/SQL procedure successfully completed.

2. 异常块只能捕捉运行时错误，无法捕捉解析错误。

SQL> edit
Wrote file afiedt.buf

  1  BEGIN
  2  dbms_output.put_line('['|| & input ||']');
  3  EXCEPTION
  4  when others then
  5  dbms_output.put_line('发生了异常：'||SQLERRM);
  6* END;
SQL> /
Enter value for input: abc
old   2: dbms_output.put_line('['|| & input ||']');
new   2: dbms_output.put_line('['|| abc ||']');
dbms_output.put_line('['|| abc ||']');
                           *
ERROR at line 2:
ORA-06550: line 2, column 28:
PLS-00201: identifier 'ABC' must be declared
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored

3. 标量变量和复合变量 (Scalar and Composite Variables)

Scalar variables hold only one thing at a time and are frequently labeled as primitives; these include numbers, strings, and timestamps. Oracle timestamps are dates precise to one thousandth of a second. You can also define compound variables, alternatively labeled composite variables. There's not much difference in the words, but Oracle Database 12c documentation uses the term composite variables. So, this book uses "composite variables" to describe arrays, structures, and objects. Composite variables are variables built from primitives in a programming language.

4. 块中变量的行为

1）仅声明变量而不为其赋值，则变量值为null

DECLARE			
lv_sample	NUMBER;		
BEGIN			
dbms_output.put_line('Value	is	['||lv_sample||']');	
END;			
/

2) 以下代码块是否会引发运行时错误并由异常模块捕捉呢？答案是否，声明块中的赋值未被当作运行时错误来处理。

SQL> edit
Wrote file afiedt.buf

  1  DECLARE
  2  lv_input   VARCHAR2(10)    :=      '&input';
  3  BEGIN
  4  dbms_output.put_line('['||lv_input||']');
  5  EXCEPTION
  6  WHEN       OTHERS  THEN
  7  dbms_output.put_line('异常处理模块：'||SQLERRM);
  8* END;
SQL> /
Enter value for input: 此输入超限了
old   2: lv_input       VARCHAR2(10)    :=      '&input';
new   2: lv_input       VARCHAR2(10)    :=      '此输入超限了
';
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 2

3）为变量赋值时，出现运行时错误，可由异常模块捕捉

SQL> edit
Wrote file afiedt.buf

  1  DECLARE
  2  lv_input   VARCHAR2(10);
  3  BEGIN
  4     lv_input := '&input';
  5     dbms_output.put_line('['||lv_input||']');
  6  EXCEPTION
  7     WHEN    OTHERS  THEN
  8             dbms_output.put_line('Exception module:'||SQLERRM);
  9* END;
SQL> /
Enter value for input: 输入超限了！
old   4:        lv_input := '&input';
new   4:        lv_input := '输入超限了！';
Exception module:ORA-06502: PL/SQL: numeric or value error: character string buffer too small

PL/SQL procedure successfully completed.