Oracle PL/SQL

1. SQL*Plus命令模式：

在SQL*Plus中可以运行SQL*Plus命令和SQL*Plus语句：

一、SET命令：

使用SET命令来设置SQL*Plus的运行环境，SET语法格式为：

SET system_variable value

下面介绍常用的几个参数：

1. SET TIME ON 在命令提示符SQL>前面显示当前系统时间。

2. PAGESIE设置：设置从顶部标题至页结束之间的行数，  例如 SET PAGESIZE 18

3. NEWPAGE变量：用来设置一页中空行的数量，例如 SET NEWPAGE 5，默认值是1 

4. LINESIZE变量：设置在SQL*Plus环境中一行所显示的最多字符总数，例如 SET LINESIZE 100

5. PAUSE变量：用来设置SQL*Plus输出结果是否滚动显示，类似于more命令格式，例如 SET PAUSE ON

6. NUMFORMAT变量：用来设置显示数值的默认格式，该格式是数值格式，例如 SET NUMFORMAT $999,999,999.00。

二、 HELP命令：

展示某个命令的功能，参数格式等。例如：查看startup命令的用法，HELP也可使用?代替：

HELP STARTUP;
? STARTUP;

如果无法记清要使用的命令，可以使用HELP INDEX命令查看SQL*Plus命令清单：

HELP INDEX;

三、DESCRIBE命令：

使用最为频繁，用来查询指定数据对象的组成结构。同时，#DESCRIBE user_tables可以在命令输入过程中使用，也会立即显示DESCRIBE的结果。在显示完后，可以继续输入未完成的命令。

SQL> DESCRIBE user_tables;

SQL> SELECT *
  2  #DESCRIBE user_tables

四、SPOOL命令：

SPOOL命令可以把查询结果输出到指定文件中，这样可以方便地保存查询结果并打印。

命令参数格式：

SPO[OL] [file_name[.ext] [CRE[ATE] | REP[LACE] | APP[END]] | OFF | OUT]

CREATE，新建一个文件；REPLACE，替换某一个文件；APPEND，追加到某个文件尾部。 OFF|OUT 关闭SPOOL流。

SPOOL C:\Storm\oracleoutput\user_tables.txt
SELECT TABLE_NAME FROM user_tables;
SPOOL OFF

五、DEFINE命令：

用来设置或者显示一个或者多个用户变量。命令参数格式：

 DEF[INE] [variable] | [variable = text]

（1）显示所有用户变量的值：

DEFINE

（2）显示变量_DATE的值：

DEFINE _DATE

（3）将变量vjob的值设置为'SALESMAN'：

DEFINE vjob='SALESMAN'

六、SHOW 命令：

显示SQL*Plus系统变量的值，或者显示当前SQL*Plus环境变量的值。例如：

SHOW PARAMETER db_block_size

七、EDIT命令：

用来编辑SQL缓冲区或指定磁盘文件中的SQL语句或PL/SQL块。如果后边跟参数（文件名称），则是打开文件（用记事本程序打开这个文件，以让用户来进行编辑操作），如果没有跟参数，则是编辑SQL缓冲区中的最近一条SQL语句或PL/SQL块（也是使用记事本打开）。

命令参数格式：

ED[IT] [file_name[.ext]]

EDIT C:\Storm\oracleoutput\emp_select01.sql
EDIT

八、SAVE命令和GET命令：

将SQL缓冲区中的最近一条SQL语句或PL/SQL块保存到一个文件中，参数格式：

SAV[E] [FILE] file_name[.ext] [CRE[ATE] | REP[LACE] | APP[END]]

SAVE C:\Storm\oracleoutput\emp_select02.sql

GET命令把一个SQL脚本文件的内容放进SQL缓冲区。

九、START和@命令：

这2个命令都可以用来执行一个脚本：

START C:\Storm\oracleoutput\emp_select02.sql

2. 查询结果格式化：

1. COLUMN命令：

可以实现格式化查询结果、设置列宽度、重新设置列标题等功能。命令参数如下：

COL[UMN] [{column | expr} [option ...] ]

option的具体内容比较多，介绍几个常用的，其余的可参考HELP命令。

（1）FORMAT

用于格式化指定的列，需要在FORMAT关键字的后边加一个掩码格式。

col sal format $999,999.00
SELECT empno,ename,sal FROM scott.emp;

（2）HEADING

用于定义列标题，重命名列标题。

COLUMN ENAME HEADING '雇员名称'
COLUMN JOB HEADING '职位'
SELECT ENAME,JOB FROM SCOTT.EMP;

（3）NULL

在NULL后指定一个值，如果字段的值为NULL，则显示为NULL选项后指定的值。

COLUMN COMM NULL '空值'
SELECT EMPNO,COMM FROM SCOTT.EMP;

（4）ON|OFF

用于控制定义的显示属性的状态，OFF表示定义的所有显示属性都不起作用，默认为ON。

（5）WRA[PPED] | WOR[D_WRAPPED]

实现折行的功能。

2. TTITLE和BTITLE命令：

这2个命令分别用来设置打印时每页的顶部和底部标题。

参数选项：

 TTI[TLE] [printspec [text|variable] ...] | [OFF|ON]

 BTI[TLE] [printspec [text|variable] ...] | [OFF|ON]

如果不指定，默认是ON 的。

SET PAGESIZE 20
TTITLE CENTER '销售情况表'
BTITLE LEFT '时间：2015-12-14  制表人：PM'
SELECT ENAME,SAL FROM SCOTT.EMP;

可以用字符串指定TITLE，也可以用变量来制定TITILE：

SET PAGESIZE 20 
DEFINE val_top = '雇员信息表'
DEFINE val_bottom = '时间：2015-12-14  制表人：PMPA'
TTITLE LEFT val_top
BTITLE LEFT val_bottom
SELECT EMPNO,ENAME FROM SCOTT.EMP;

3. Oracle SQL特性，常用SQL：

1.用户模式：

为了便于管理用户所创建的数据库对象，引入模式概念。模式是一个数据库对象的集合。例如实例模式SCOTT，模式SYSTEM，模式SYS等。

可以通过user_tables查询某个模式下有哪些表，或者DBA也可以通过dba_tables表来查看某个用户模式都有哪些表。

SELECT * FROM user_tables;
SELECT * FROM dba_tables WHERE OWNER = 'SCOTT';

2. 查询语句：

（1）ROWID:

在Oracle数据库中，有一个标识行唯一特性的行标识符，名称为ROWID，ROWID是Oracle内部使用的隐藏列，该列实际上并不定义在表中，也成为伪列。ROWID记录该行在数据库中的物理地址。使用DESCRIBE命令无法查到该列，但是可以使用SELECT查询。

SELECT ROWID,EMPNO,ENAME,SAL FROM SCOTT.EMP;

（2）比较操作符

A{operator} ANY(B)：表示A与B中的任何一个元素进行operator运算符的比较，只要有一个比较值为true，就返回true。

A{operator} ALL(B)：表示A与B中的所有元素进行operator运算符的比较，只有与每一个元素的比较值都为true，才返回true。

SELECT * FROM SCOTT.EMP WHERE SAL > ANY(800,950,1100);
SELECT * FROM SCOTT.EMP WHERE SAL > ALL(800,950,1100);

（3）自然连接

指在检索多个表时，Oracle会将第一个表中的列与第二个表中具有相同名称的列进行自动连接。自然连接使用NATURAL JOIN关键字。使用情况较少。

SELECT * FROM SCOTT.EMP NATURAL JOIN  SCOTT.DEPT WHERE SAL > 2000

3. 常用函数：

dual表是Oracle系统内部提供的一个用于实现临时数据计算的特殊表，只有一个列DUMMY。可以使用这个表完成计算等临时任务。

（1）ASCII()和CHR()函数：

ASCII()用于返回一个字符的ASC码，CHR()用于返回某个ASCII对应的字符。

SELECT ASCII('A'),ASCII('a'),ASCII('S') FROM DUAL;
SELECT CHR(78),CHR(79),CHR(80),CHR(81) FROM DUAL;

（2）CONCAT(s1,s2)函数：

将字符串s2连接到字符串s1后边。如果s1为null，返回s2；如果s2为null，返回s1,；如果都为null，则返回null。

SELECT CONCAT('New ','York') FROM DUAL;

（3）INITCAP(s)函数：

将字符串s的每一个单词的第一个字母大写，其他小写。单词间用空格、控制字符、标点符号区分。

LENGTH(s)：返回字符串s的长度。

LOWER(s) 和 UPPER(s) ：返回字符串s的小写形式、和大写形式。

SELECT INITCAP('united,states,japan,china') FROM dual;

（4）INSTR(s1,s2[,i][,j])函数：

用于返回字符s2在字符串s1中第j次出现时的位置，搜索从字符串s1的第i个字符开始。

SELECT INSTR('oracle 11g','1',3,2) FROM dual;
SELECT INSTR('oracle 11g','1',9,1) FROM dual;
SELECT INSTR('oracle 11g','1',9,2) FROM dual;

（5）REPLACE(s1,s2[,s3])函数：

该函数使用s3字符串替换出现在s1字符串中的所有s2字符串，并返回替换后的新字符串，其中s3的默认值是空串。

SELECT REPLACE('oracle 11g','oracle') FROM dual;
SELECT REPLACE('oracle 11g','oracle','IBM') FROM dual;

（6）LTRIM(s1,s2)函数、RTRIM(s1,s2)函数和TRIM(s1,s2)函数：

分别用来删除字符串s1左边的字符串s2、删除字符串s1右边的字符串s2，删除字符串s1两边的字符串s2。如果不指定s2，则是去掉空格。

SELECT  LTRIM(' ASD ') FROM dual;
SELECT  TRIM(' ASD ') FROM dual;

SELECT  LTRIM('ggHelloWorld!gg','gg') FROM dual;
SELECT  LTRIM('aggHelloWorld!gg','gg') FROM dual;

4. TRUNCATE语句

如果用户确定要删除表中的所有记录，则除了可以使用DELETE 语句外，还可以使用TRUNCATE语句，建议使用TRUNCATE语句，比DELETE语句快很多。

使用TRUNCATE语句时，不会产生回滚记录。当然，使用了TRUNCATE后也就不能再使用ROLLBACK语句撤销。

TRUNCATE TABLE SCOTT.emp_temp;
SELECT * FROM SCOTT.emp_temp;

5. 事务操作：

首先，解锁HR模式的登陆：

以DBA权限登陆数据库，解锁HR模式：

ALTER USER hr ACCOUNT UNLOCK; -- 解锁用户 HR  
ALTER USER hr IDENTIFIED BY hr; -- 修改密码  
disconnect  
exit   

以hr用户登陆数据库： 

connect hr/hr@orcl

create table jobs_temp(
job_id varchar2(10) primary key,
job_title varchar2(35) not null,
min_salary number(6),
max_salary number(6));

truncate table jobs_temp;

insert into jobs_temp values('OFFICE','办公文员',3000,5000);

savepoint sp;

insert into jobs_temp values('FINANCE','财务人员',4000,8000);

select * from jobs_temp;

rollback to savepoint sp;

commit;

select * from jobs_temp;

4. PL/SQL块编程：

4.1 PL/SQL概述：

1.PL/SQL块结构：

PL/SQL程序以块(Block)为基本单位，通常包含3个部分：声明部分(以DECLARE开头)、执行部分(以BEGIN开头)、异常处理部分(以EXCEPTION开头)，其中只有执行部分是必须要有的。如下为PL/SQL的一般格式：

[DECLARE]
	--声明部分，可选
BEGIN
	--执行部分，必须
[EXCEPTION]
	--异常处理部分，可选
END

下面举一个列子来使用PL/SQL块，取2个整数的和与差的商：

SET serveroutput ON
DECLARE
  v_int_1 INT:=98; 
  v_int_2 INT:=23;
  c NUMBER;
BEGIN
  c:=(v_int_1+v_int_2)/(v_int_1-v_int_2);
  dbms_output.put_line('The output is '||c);
EXCEPTION 
  WHEN ZERO_DIVIDE THEN
  dbms_output.put_line('You can not divide by zero');
END;
/

其中结尾的/表示程序结束，在命令行中如果不输入/的话，程序不会结束不会打印执行结果。

在PL/SQL Developer 中开发测试PL/SQL块，并测试的方法：打开“测试窗口”，输入PL/SQL块代码，F8执行即可，可以在“DBMS输出”看到最后结果。

2. PL/SQL注释：

单行注释使用--  ；

多行注释使用/**/  ；

4.2 数据类型和变量定义：

1. 基本数据类型：

（1）数值：NUMBER(9,2)：表示一共9位，其中小数共2位。

还包括类型：DEC,DECIMAL,DOUBLE,INTEGER,INT,NUMERIC,SMALLINT,BINARY_INTEGER,PLS_INTEGER等。

（2）字符类型：

VARCHAR2,CHAR,LONG,NCHAR,NVARCHAR2等。

（3）日期类型（日期+时间）：

只有一种DATE

（4）布尔类型：

BOOLEAN。

2.特殊数据类型：

（1）%TYPE类型：使用%TYPE关键字可以声明一个与指定列名称相同的数据类型，紧跟在指定列名的后边。例如：  

var_job JOBS.JOB_TITLE%TYPE;

（2）RECORD类型：也成为记录类型，使用该类型可以存储由多个列值组成的一行数据。

type emp_type is record
(
	var_ename varchar2(20),
	var_job varchar2(20),
	var_sal number
);

（3）%ROWTYPE类型：结合了%TYPE和RECORD类型的优点，可以根据数据表中行的结构定义一种特殊的数据类型，用来存储从数据表中检索到的一行数据。

rowVar_emp emp%rowtype;

3.定义变量和常量：

定义变量：<变量名> <数据类型>[(长度):=<初始值>];   例如：

var_countryname varchar2(50):='United Staes'

定义常量：<常量名> constant <数据类型>:=<常量值>;  例如：

con_day constant integer:=365

4.3 流程控制：

1. 选择语句：

if语句：

if <condition_expression1> then
  plsql_sentence_1;
elsif <condition_expression2> then
  plsql_sentence_2;
  ...
else
  plsql_sentence_n;
end if;

case语句：

case <selector>
  when <expression_1> then plsql_sentence_1;
  when <expression_2> then plsql_sentence_2;
  ...
  when <expression_n> then plsql_sentence_n;
  [else plsql_sentence;]
end case;

case关键字的后边有一个选择器，它通常是一个变量。下面是一个使用CASE语句的一个实例，根据输入的一个数字，来显示相应的内容：

DECLARE
  v_int INT:=2;
BEGIN
  CASE v_int
    WHEN 1 THEN dbms_output.put_line('Spring');
    WHEN 2 THEN dbms_output.put_line('Summer');
    WHEN 3 THEN dbms_output.put_line('Autumn');
    WHEN 4 THEN dbms_output.put_line('Winter');
    else dbms_output.put_line('Unknown');
  END CASE;
END;
/

2. 循环语句：

loop语句，语法结构：

loop
  plsql_sentence;
  exit when end_condition_exp
end loop;

while语句，语法结构：

while condition_expression loop
  plsql_sentence;
end loop;

for语句，语法结构：

for variable_counter_name in [reverse] lower_limit..upper_limit loop
  plsql_sentence;
end loop;

下面使用循环举例实现1到100的和：

DECLARE
  v_i INT;
  v_sum INT:=0;
BEGIN
  v_i:=1;
  LOOP
    v_sum:=v_sum+v_i;
    v_i:=v_i+1;
    EXIT WHEN v_i=101;
  END LOOP;
  DBMS_OUTPUT.put_line('The sum of 1 to 100 is '||v_sum);
END;

DECLARE
	v_sum INT:=0;
	v_i INT;
BEGIN
	FOR v_i IN 1..100 LOOP
		v_sum:=v_sum+v_i;
	END LOOP;
	DBMS_OUTPUT.put_line('The sum of 1 to 100 is '||v_sum);
END;

4.4 PL/SQL游标：

游标的作用就相当于指针，通过游标PL/SQL程序可以一次处理查询结果集中的一行，并进行相应的操作。在Oracle中，通过游标操作数据主要使用显示游标和隐式游标。

1.显式游标：

处理使用时的步骤：声明游标、打开游标、读取游标和关闭游标4个步骤。

声明游标主要包括游标名称和为游标提供结果集的SELECT语句。

cursor cur_name[(input_parameter1[,input_parameter2]...)]
	[return ret_type]
is select_sentence;

打开游标：

open cur_name[(para_value1[,para_value2]...)];

读取游标：

fetch cur_name into {variable};

关闭游标：

close cur_name;

2.游标的属性：

无论是显式游标还是隐式游标，都具有%found、%notfound、%isopen、%rowcount 四个属性。

3.隐式游标：

在执行一个SQL语句时，Oracle会自动创建一个隐式游标。这个游标是内存中处理该语句的工作区域。标识符“sql”就是update语句在更新数据过程中所使用的隐式游标，它通常处于隐藏状态，由Oracle自动创建。
下边的例子是使用隐式游标属性的例子：

BEGIN
  UPDATE EMPLOYEES_TEMP 
  SET SALARY = 8000
  WHERE EMPLOYEE_ID = 300;
  IF SQL%NOTFOUND THEN
    dbms_output.put_line('No employee salary updated!');
  ELSE
    dbms_output.put_line(SQL%ROWCOUNT||' employees salary updated!!');
  END IF;
  COMMIT;
END;

4.显式游标使用实例：

（1）声明一个游标，用于检索指定编号的雇员信息，然后使用游标的%found属性检查是否检索到。

DECLARE 
  type emp_instance is record
  (
    ENAME VARCHAR2(10),
    JOB VARCHAR2(9)
   );
  emp_row emp_instance;   --<span style="font-family: Arial, Helvetica, sans-serif;">emp_instance</span>类型的一个实例
  cursor cur_emp(emp_no number:=7369)
  is 
  SELECT ENAME,JOB FROM EMP WHERE EMPNO = emp_no;
BEGIN
  OPEN cur_emp;     --没加参数表示使用默认值
  FETCH cur_emp INTO emp_row;
  IF cur_emp%found THEN 
    dbms_output.put_line(emp_row.ENAME ||' '||emp_row.JOB);
  ELSE
    dbms_output.put_line('The employee can not be found!!');
  END IF;
  CLOSE cur_emp;
END;

（2）使用WHILE循环来依次处理游标中每行记录：

DECLARE 
  type emp_instance is record
  (
    ENAME VARCHAR2(10),
    JOB VARCHAR2(9),
    SAL NUMBER(7,2)
   );
  emp_row emp_instance;                --emp_instance类型的一个实例
  cursor cur_emp
  is 
  SELECT ENAME,JOB,SAL FROM EMP WHERE SAL > 800;
BEGIN
  OPEN cur_emp;
  FETCH cur_emp INTO emp_row;          --每调用一次Fetch表示游标的指针往后推一个
  IF cur_emp%notfound THEN 
    dbms_output.put_line('The employee can not be found!!');
  END IF;
  WHILE cur_emp%found LOOP
    dbms_output.put_line(emp_row.ENAME||' '||emp_row.JOB||' '||emp_row.SAL);
    FETCH cur_emp INTO emp_row;       <span style="font-family: Arial, Helvetica, sans-serif;">--每调用一次Fetch表示游标的指针往后推一个</span>
  END LOOP;
  CLOSE cur_emp;
END;

（3）使用for语句循环游标：

在for语句中遍历隐式游标中的数据时，在“in”关键字的后面提供由SELECT语句检索的结果集。在for语句中遍历显式游标中的数据时，在“in”关键字的后面提供游标名称。

DECLARE 
  type emp_instance is record
  (
    ENAME VARCHAR2(10),
    JOB VARCHAR2(9),
    SAL NUMBER(7,2)
   );
  emp_row emp_instance;                --emp_instance类型的一个实例
  cursor cur_emp
  is 
  SELECT ENAME,JOB,SAL FROM EMP WHERE SAL > 800;
BEGIN
	FOR emp_row IN cur_emp LOOP
		dbms_output.put_line(emp_row.ENAME||' '||emp_row.JOB||' '||emp_row.SAL);
	END LOOP;
END;

4.5 Oracle自定义异常：