PL/SQL基础

1.什么是pl/sql

PL/SQL(Procedure Language/SQL)是oracle在标准的sql语言上的扩展。ql/sql不仅允许嵌入sql语言，还可以定义变量和常量，允许私用条件语句和循环语句，允许使用例外处理各种错误，这使得它的功能变得更加强大。

2.PL/SQL的基本结构

	PL/SQL是一种块结构语言，其块结构如下:
	
	  DECLARE
	       ――声明部分:声明变量、常量、用户定义的数据类型以及游标
	       ――这一部分可选，不需要可不写
  BEGIN
	       ――主程序体，在这可以加入各种合法的语句
  EXCEPTION
	       ――异常处理程序，当程序中出现错误时执行这一部分
	       ――这一部分可选
	  END; ――主程序体结束，分号必不可少；
   从上面的结构可以看出，PL/SQL块由三部分组成：声明部分、执行部分和异常处理部分，其中只有执行部分是必须的：
	  BEGIN
	    /*执行部分*/   
	  END;
和 
	DECLARE
	    /*声明部分*/
	 BEGIN
	    /*执行部分*/  
	 END; 
都是合法的。

3.PL/SQL中的数据类型

(1)标量
NUMBER--用于存储和操纵数字数据 ,格式是NUMBER（p,s）,p是数据宽度，s是小数点后的位数，默认为0；
CHARACTER――
  CHAR :字符型，最长2000b
  NCHAR：依赖所使用的语种的字符集，最长2000b，与CHAR类似；
  VARCHAR2：用来存放变长字符串，但要指定最大长度，宽度范围1-4000；
  NVARCHAR2：类似于VARCHAR2，依赖于所使用语种的字符集
  VARCHAR：在当前的版本中，与VARCHAR2同义，但以后的版本中将实现真正的变长，不受宽度限制。
DATE――
   世纪、年、月、日、时、分、秒，总长14个字节
BOOLEAN―― TRUE、FALSE、NULL

 declare
 v_number1 number;
 v_number2 number(3,2) ;
 v_name varchar2(20) :='kettas';
 v_date date :=sysdate;
 v_b boolean := true;
 
(2)复合
RECORD，VARRAY，NESTED，TABLE

(3)引用
REF CURSOR ，REF操作符 

(4)LOB，BLOB，CLOB，NCLOB，BFILE 

(5)PL/SQL中的游标CURSOR

①声明游标

　　CURSOR<游标名>IS SELECT<语句>；

②打开游标

　　OPEN<游标名>;

③提取游标

　　FETCH<游标名>INTO<变量列表>;

④关闭游标

　　CLOSE<游标名>;

(6)%TYPE和%ROWTYPE两种特殊变量；
游标是用来处理使用select语句从数据库中检索到的多行记录的工具，借助于游标的功能，数据库应用程序可以对一组记录逐个进行处理，每次处理一行。可以将有表理解为指向select查询结果的指针。
游标分为显式游标和隐式游标，隐式游标无需用户过问，我们主要学习显式游标。
显式游标处理的4个步骤：
a)	声明游标
b)	为查询打开游标
c)	将结果提取到PL/SQL变量中
d)	关闭游标

注：

PL/SQL 是过程语言(Procedural Language)与结构化查询语言(SQL)结合而成的编程语言

DECLARE ――声明部分:声明变量、常量、用户定义的数据类型以及游标

――这一部分可选，不需要可不写

DECLARE   
      v_num number(3);    --定义变量v_num1，数据类型是NUMBER(3)，也就是3位大小的整数类型
	v_num number(3):=10;   --定义变量v_num1，数据类型是NUMBER(3)，也就是3位大小的整数类型，:=为赋值语句，在C++中相当于=，初始化赋值为10；
	NUMBER(p,s)   --p代表数值型的位数，s代表精度位数
	
	set serveroutput on   --将数据从控制台上输出，要写输出语句就必须写这条语句
	Dbms_output.put_line(‘total manager num ’ ||to_char(v_num));  --输出语句，||在PL/SQL中表示字符串的链接，而在C++中表示与。to_char：函数功能，就是将数值型或者日期型转化为字符型，参考链接https://baike.baidu.com/item/to_char()/8525008

4.pl/sql中的语句

（1）IF语句（||在Oracle中表示字符串的链接，而在C++中表示与）
SET SERVEROUTPUT ON
DECLARE
      v_num number(3);
BEGIN
   Select count(*) into v_num
      From scott.emp
      Where sal>2500;
  If v_num>5 then  
Dbms_output.put_line(‘total manager num ’ ||to_char(v_num));
Elsif v_num<5 then
Dbms_output.put_line(‘total manager num 2’ ||to_char(v_num));
Else
Dbms_output.put_line(‘total manager num 3’ ||to_char(v_num)); 
    End if;
   END;
   /
（2）loop语句
DECLARE
   N number:=1;
Count1 number:=2;
BEGIN
  Loop 
   n:=n*count1;
   count1:=count1+1;
   If count1>10 then
   Exit;
   End if;
End loop;
Dbms_output.put_line(to_char(n));
     END;
/  
（3）for 语句
DECLARE
 v_lower NUMBER :=1;
 v_upper NUMBER :=10;
BEGIN
   FOR i IN v_lower .. v_upper LOOP
     dbms_output.put_line('i is: '|| i);
   END LOOP;
END;
（4）while 语句
declare  
 n number:=0;
 begin
 while n<100 loop
    n:=n+1;
    dbms_output.put_line('n='||n);
 end loop;
 end;

例	查询出SCOTT.EMP表中每个的员工薪水等级，并按照下面的格式输出到屏幕
EMPNO  ENAME  薪水是 SAL 属于 GRADE 等级；


set serveroutput on
DECLARE
	empl scott.emp%rowtype;
	CURSOR emp_cur IS 
	select * from SCOTT.EMP;
	begin
		open emp_cur;
	fetch emp_cur into empl;
	loop
		exit when not emp_cur%found;
		if empl.sal<=1200 then
		Dbms_output.put_line(empl.empno||'  '||empl.ename||'  薪水是 '||empl.sal||' 属于 '||'1 等级');
		elsif empl.sal>1200 and empl.sal<=1400 then
		Dbms_output.put_line(empl.empno||'  '||empl.ename||'  薪水是 '||empl.sal||' 属于 '||'2 等级');
		elsif empl.sal>1400 and empl.sal<=2000 then
		Dbms_output.put_line(empl.empno||'  '||empl.ename||'  薪水是 '||empl.sal||' 属于 '||'3 等级');
		elsif empl.sal>2000 and empl.sal<=3000 then
		Dbms_output.put_line(empl.empno||'  '||empl.ename||'  薪水是 '||empl.sal||' 属于 '||'4 等级');
		else
		Dbms_output.put_line(empl.empno||'  '||empl.ename||'  薪水是 '||empl.sal||' 属于 '||'5 等级');
		END IF;
		fetch emp_cur into empl;
	end loop;
	close emp_cur;
	end;
/

alt

小技巧：

如果在运行代码的时候，可以直接在控制台中输入ed进行修改。。

alt

5.隐式游标和显式游标

隐式游标：

隐式游标 ---由Oracle数据库自动创建，名称是(SQL) ,主要用途是可以返回一个操作是否成功或失败.

用于判断一个操作是否成功.
     SQL%notfound  --返回Boolean值  存在结果集返回 False
     SQL%found    --返回Boolean值   存在结果集返回 True
     SQL%rowcount  --修改涉及到的记录的行数
     SQL%isopen   --在隐式游标里一般这个属性是自动打开和关闭的，且任何时候查询都返回False。

set serveroutput on
DECLARE
	empl scott.emp%rowtype;
	Cursor emp_cur is
 	select * from scott.emp where sal<1000;
	begin
	OPEN  emp_cur;
loop
	FETCH emp_cur INTO empl;
	EXIT WHEN NOT emp_cur%FOUND;
	empl.sal := empl.sal*1.2;
	update scott.emp set sal=empl.sal where ename=empl.ename;
	commit;
END loop;
Dbms_output.put_line('影响到的行数:'||emp_cur%ROWCOUNT);
CLOSE emp_cur;
END;
/

alt

6.存储过程的创建及使用

所谓的存储过程就是存储在数据库中的过程，该过程与数据库中的表、视图一样属于数据库对象。

（1）语法
Create [or replace] procedure <过程名>
(<参数1> [方式1]<数据类型1>,
<参数2>[方式2]<数据类型2>…
)
Is|as
Pl/sql 

（2）
说明：
In 参数类型：表示这个参数值输入给过程，供过程使用；
Out参数类型：表示该参数是输出类型的参数，表示这个参数在过程中被赋值，可以传给过程体外的环境；
In out参数类型：这种类型的参数实际上就是综合了上述两种参数类型，及向过程体传值，也向过程体外的环境传值；

创建存储过程：
set serveroutput on format wrapped
Create or replace procedure dept_member_num
( in_deptno in scott.emp.deptno%type,Out_total_num out number)
As
Begin
  Select count(*) into Out_total_num
  From scott.emp where deptno=in_deptno;
End dept_member_num;
/

调用存储过程：
Declare
recive_outpara number;
Begin
dept_member_num(10,recive_outpara);
Dbms_output.put_line('the number of dept 10 is   '||to_char(recive_outpara)) ;
End;
/

alt

存储过程的删除：

Drop procedure dept_member_num;

7.函数的创建及使用

 函数创建的语法格式：
Create [or replace] function <function_name>
(<参数1> [方式1]<数据类型1>,<参数2> [方式2]<数据类型2>…)
Return<数据类型> is|as
Pl/sql程序体 ――其中必须有一个return语句
注：通常函数只有in类型的参数

函数的创建：
Set serveroutput on format wrapped
Create or replace function dept_num
(in_dept in scott.emp.deptno%type)
Return number
As
 Out_num number;
Begin
Select count(*) into Out_num
from scott.emp 
where scott.emp.deptno=in_dept;
Return(out_num);
End dept_num;
/

函数的调用：
Declare
Deptnum number;
Begin
Deptnum:=dept_num(20);
Dbms_output.put_line('the number of dept 20 is   '||to_char(deptnum)) ;
End;
/

alt

函数的删除：

Drop function dept_num;

8.包的创建及使用

包类似于面向对象中的类，是数据库中的一个实体，其中包含一系列公共常量、变量、数据类型、游标、过程以及函数的定义。

（1）创建包包由包的描述部分和包体两部分组成，包描述部分相当于一个包头，他对包的所有部件进行一个简单的声明，这些部件可以被外界应用程序访问，包描述部分格式如下：

#包头部分

Create package<包名>
Is
变量、常量及数据类型的定义；
游标定义头部；
函数、过程的说明；
End<包名>；

#包体部分

包体部分是包的描述部分中游标、函数及过程的定义，格式如下：
Create package body<包名>
As
游标、函数、过程的具体意义；
End<包名>；

#调用过程

包调用的方式为：
包名.变量名
包名.游标名
包名.函数名（过程名）
一旦包创建之后，便可以随时调用其中的内容。

样例：

包头部分：
Create package my_package
Is
Dept_num number;
--Cursor manager_cur;
function f_dept_num(in_deptno in scott.emp.deptno%type)
Return number;
procedure p_dept_num(in_deptno in scott.emp.deptno%type,out_num out number);
End my_package;
/

包体过程：
Create or replace package body my_package
As
Function f_dept_num(in_deptno in scott.emp.deptno%type)
Return number
As
Out_num number;
Begin
   Select count(*) into out_num
   From scott.emp
   Where deptno=in_deptno;
  Return out_num;
End f_dept_num;
Procedure p_dept_num(in_deptno in scott.emp.deptno%type,out_num out number)
As
 Begin
 Select count(*) into out_num
   From scott.emp
   Where deptno=in_deptno;
End p_dept_num;
End my_package;
/

调用过程：
Set serveroutput on format wrapped
Declare 
  Num number;
Begin
  Num:=my_package.f_dept_num(20);
Dbms_output.put_line('the num of dept 20 is '|| to_char(num));
my_package.p_dept_num(10,num);
Dbms_output.put_line('the num of dept 10 is '||to_char(num));
End;
/

alt