PL/SQL模块学习之一、基本概念学习

1.登陆

D:\app\Administrator\product\11.2.0\dbhome_1\BIN>sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on 星期二 3月 3 12:19:01 2020

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

SQL> conn as sys/oracle #格式错误，根据下面提示修改登陆参数
SP2-0306: 选项无效。
用法: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM}] [edition=value]]
其中 <logon> ::= <username>[/<password>][@<connect_identifier>]
      <proxy> ::= <proxyuser>[<username>][/<password>][@<connect_identifier>]
SQL> conn sys/oracle   #后应该为设置好的密码
ERROR:
ORA-01017: invalid username/password; logon denied


SQL> conn sys/Password
ERROR:
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER


SQL> conn sys/Password as SYSDBA
已连接。

2.PL\SQL语句块

SQL> set serveroutput on; # 默认输出显示是关闭，需要手动打开
SQL> 
SQL> DECLARE
  2  var_name varchar2(30);
  3  BEGIN
  4  SELECT ENAME INTO var_name
  5  from SCOTT.EMP
  6  WHERE EMPNO=7902;
  7  DBMS_OUTPUT.put_line('var_name is : '||var_name);
  8  END;
  9  / # /表示执行语句
var_name is : FORD
PL/SQL procedure successfully completed

3.DBMS_OUPUT包
其中存储过程PUT_LINE将数据库服务器返回的数据存放到缓存，可设置缓存大小

SQL>set seroutput on size 4000; 

具体使用可参考《Oracle官方文档——DBMS_OUTPUT》

3.替代变量
&符号表示替代变量

SQL> DECLARE
  2  var_name varchar2(30);
  3  var_empno number :=&b_EMPNO;
  4  BEGIN
  5  SELECT ENAME INTO var_name
  6  from SCOTT.EMP
  7  WHERE EMPNO=var_empno;
  8  DBMS_OUTPUT.put_line('var_name is : '||var_name);
  9  END;
 10  /
var_name is : CLARK
PL/SQL procedure successfully completed

SQL> set verify on; #设置是否显示替代变量被替代前后的语句
SQL> 
SQL> DECLARE
  2  var_name varchar2(30);
  3  var_empno number :=&b_EMPNO;
  4  BEGIN
  5  SELECT ENAME INTO var_name
  6  from SCOTT.EMP
  7  WHERE EMPNO=var_empno;
  8  DBMS_OUTPUT.put_line('var_name is : '||var_name);
  9  END;
 10  /
&b_EMPNO: 7782
var_name is : CLARK
PL/SQL procedure successfully completed

&&和&不同的是，只要当前PL/SQL定义相同的替代变量，在执行PL\SQL块时，只需要输入一次，而&需要多次。

SQL> BEGIN
  2    DBMS_OUTPUT.PUT_LINE('TOM is from : '||'&country');
  3    DBMS_OUTPUT.PUT_LINE('larry is from : '||'&country');
  4  END;
  5  /
&country: American 
&country: England #需要多次输入
TOM is from : American
larry is from : England
PL/SQL procedure successfully completed

SQL> BEGIN
  2    DBMS_OUTPUT.PUT_LINE('TOM is from : '||'&&country');
  3    DBMS_OUTPUT.PUT_LINE('larry is from : '||'&country');
  4  END;
  5  /
&country: England 
&country: England  #只输入了一次
TOM is from : England
larry is from : England
PL/SQL procedure successfully completed