PL/SQL语言的系统介绍

1.PL/SQL是ORACLE对标准数据库语言的扩展，ORACLE公司已经将PL/SQL整合到ORACLE 服务器和其他工具中了
PL/SQL的优点如下：
　　. PL/SQL是一种高性能的基于事务处理的语言，能运行在任何ORACLE环境中，支持所有数据处理命令。通过使用PL/SQL程序单元处理SQL的数据定义和数据控制元素。
　　. PL/SQL支持所有SQL数据类型和所有SQL函数，同时支持所有ORACLE对象类型
　　. PL/SQL块可以被命名和存储在ORACLE服务器中，同时也能被其他的PL/SQL程序或SQL命令调用，任何客户/服务器工具都能访问PL/SQL程序，具有很好的可重用性。
　　. 可以使用ORACLE数据工具管理存储在服务器中的PL/SQL程序的安全性。可以授权或撤销数据库其他用户访问PL/SQL程序的能力。
　　. PL/SQL代码可以使用任何ASCII文本编辑器编写，所以对任何ORACLE能够运行的操作系统都是非常便利的
　　. 对于SQL，ORACLE必须在同一时间处理每一条SQL语句，在网络环境下这就意味作每一个独立的调用都必须被oracle服务器处理，这就占用大量的服务器时间，同时导致网络拥挤。而PL/SQL是以整个语句块发给服务器，这就降低了网络拥挤。

2.pl/sql 语言结构
PL/SQL是一种块结构的语言，组成PL/SQL程序的单元是逻辑块，一个PL/SQL 程序包含了一个或多个逻辑块，每个块都可以划分为三个部分。与其他语言相同，变量在使用之前必须声明，PL/SQL提供了独立的专门用于处理异常的部分，下面描述了PL/SQL块的不同部分：

• 声明部分(Declaration section)
  　　声明部分包含了变量和常量的数据类型和初始值。这个部分是由关键字DECLARE开始，如果不需要声明变量或常量，那么可以忽略这一部分；需要说明的是游标的声明也在这一部分。

• 执行部分(Executable section)
  　　执行部分是PL/SQL块中的指令部分，由关键字BEGIN开始，所有的可执行语句都放在这一部分，其他的PL/SQL块也可以放在这一部分。

• 异常处理部分(Exception section)
  　　这一部分是可选的，在这一部分中处理异常或错误，对异常处理的详细讨论我们在后面进行。

PL/SQL程序块可以是一个命名的程序块也可以是一个匿名程序块。匿名程序块可以用在服务器端也可以用在客户端。 典型的命名语句块包括函数、过程、触发器等

命名程序块可以出现在其他PL/SQL程序块的声明部分，这方面比较明显的是子程序，子程序可以在执行部分引用，也可以在异常处理部分引用。
PL/SQL程序块可背独立编译并存储在数据库中，任何与数据库相连接的应用程序都可以访问这些存储的PL/SQL程序块；典型的可存储程序块包括函数、过程、包、触发器

3.变量声明与执行

x:=a;//对变量赋值x=a;（只在sql语句中可以使用）

declare 后是对变量的定义（变量名 变量类型 := 值）
begin和end之间是对变量的操作（赋值、输出： dbms_output.put_line （））
“–”表示注释，“||”表示连接符，类似java中的“+”，“=”代表相等符号，相当于java中的“==”

declare  mynumber number(3):=0;//创建数据类型为number的变量mynumber  result varchar(20):='the result is ';//创建数据类型为varchar的变量resultbegin  mynumber :=10+100;  dbms_output.put_line(result||mynumber);//结果输出  dbms_output.put_line('Hello World');end;

执行部分包含了所有的语句和表达式,执行部分以关键字BEGIN开始，以关键字EXCEPTION结束，如果EXCEPTION不存在，那么将以关键字END结束。

在PL/SQL中还可以直接通过数据库里的数据类型，声明与之类型相同的变量

• 变量名 表名.属性名%type:表示定义的变量类型与该表中对应属性名的类

tname people.name%type;//创建与people的name类型一致的tname

• 表名%rowtype ：表示定义的新表与对应标的类型一致

 tpeople people%rowtype;//创建与people表一致的tpeople

• 变量名 变量类型 := &变量名 : 表示键盘输入值传入变量中

age number(3):=&age1;//表示将输入的值age1传给age变量

（注意，PL/SQL中所有的字符串只能用单引号）

4.可直接在其中写入sql语句，对数据表进行操作

select * from student;//不需要有关键词引导，可直接执行

注意：oracle默认事务都不自动提交，因此执行完更新操作后需要执行commit操作，没有将数据表commit，可能遇到查询不到的情况。但是查询和提交都在同一个事务中，则可以查询到最新提交但未commit的数据

5.逻辑判断
if逻辑中，要进行多层判断，需要加then，第二层及第二层以上的判断需要用elsif，结束要用end if

--对年龄的判断（PL/SQL中“--”表示注释）declare  num number(3) :=&num;begin  if num<20 then    dbms_output.put_line('You are still a adolesence!');  elsif num<50 then    dbms_output.put_line('You are already a adult!');  else    dbms_output.put_line('You are a elder!');  end if;end;

6.循环操作

—第一种

LOOP和WHILE循环的循环次数都是不确定的，FOR循环的循环次数是固定的，counter是一个隐式声明的变量，他的初始值是start_range,第二个值是start_range+1,直到end_range,如果start_range等于end _range,那么循环将执行一次。如果使用了REVERSE关键字，那么范围将是一个降序。

--i的递增方式由for决定，加一递增，然后从2 递增到无序，只有在20-30之间时会打印出来declare  i number(4):=2;begin  for i in 20 .. 30    loop    dbms_output.put_line(i);  end loop; end;

–第二种

--循环打出2-10declare  i number(4):=2;begin  while i<10  loop    exit when i=10;    i:=i+1;    dbms_output.put_line(i);  end loop;end;

7.查询并将查询结果输出（通过光标方式实现）
第一种（不带参数的方式查询）

declare  cursor pc is select id,name from people;      pid people.id%type;  pname people.name%type;begin  open pc;     --打开游标  loop    fetch pc into pid,pname;    --往下移动游标，将查询到的值赋予pid,pname    exit when pc%notfound;       --当查询不到数据时，则停止游标    dbms_output.put_line(pname||' id is ' ||pid);  end loop;  close pc; --关闭游标end;

第二种（带参数的方式查询）

declare  cursor pc (pid people.id%type) is select name from people where id=pid;   --游标带着参数pid进行查询  pname people.name%type;begin  open pc(&ppid);    --输入的ppid为游标的参数  loop    fetch pc into pname;       exit when pc%notfound;         dbms_output.put_line(pname);  end loop;  close pc; end;

8.处理异常
如果有异常处理，则以异常处理的结束符exception结束

• 除零异常

declare  result number(3);begin  result:=1/0;  dbms_output.put_line(result);exception   --捕获异常  when zero_divide then  dbms_output.put_line('could not divide by zero');end;

• 数据查询错误异常

declare  ppname people.name%type;begin  select name into ppname from people where id=100;  dbms_output.put_line(ppname);exception               --当找不到该数据时，会报出异常  when no_data_found then  dbms_output.put_line('could not find the people100!');end;

9.pl/sql编写存储过程
存储过程是一个PL/SQL程序块，接受零个或多个参数作为输入(INPUT)或输出(OUTPUT)、或既作输入又作输出(INOUT)，与函数不同，存储过程没有返回值，存储过程不能由SQL语句直接使用，只能通过EXECUT命令或PL/SQL程序块内部调用

• 无参数存储过程

create or replace procedure sayhello  asbegin  dbms_output.put_line('hello');end;declarebegin  sayhello();  --像java中函数调用一般end;

• 带参数存储过程(即如果方法中带参数，需要在传参时先将参数定义，其中的in，out表示数据的输入输出，inout则表示数据及可以输入也可以输出)

输入查询

create or replace procedure queryName(pid in people.id%type) aspname people.name%type;begin  select name into pname from people where id=pid;  dbms_output.put_line(pname);end;declarebegin  queryName(&ppid);  --输入id查询姓名end;

• 输入查询，将查询结果输出

create or replace procedure getName (pid in people.id%type,tname out people.name%type)asbegin  select name into tname from people where id = pid;  dbms_output.put_line(tname);end;declare  pname people.name%type;begin  getName(&ppid,tname=>pname);  --将查询到的tname赋予pnameend;

10.存储函数（与存储过程类似，但是存储函数必须带有返回值，这个相当于java方法中带有返回值的方法）
函数是命名了的、存储在数据库中的PL/SQL程序块。函数接受零个或多个输入参数，有一个返回值，返回值的数据类型在创函数时定义，其中返回参数类型如果是varchar之类的，不可以定义长度，在as中定义长度。

create or replace function queryById (pid in people.id%type)return people.name%type   -声明返回变量，并将返回变量赋予pnameaspname people.name%type;   begin  select name into pname from people where id=pid;  return pname;end;declare  pname people.name%type;begin  dbms_output.put_line(queryById(&ppid));   --类似java中带返回值函数的调用方法end;

11.触发器（注意触发器创建的语句格式）
触发器与一个表或数据库事件联系在一起的，当一个触发器事件发生时，定义在表上的触发器被触发。
注意：当触及自身的更新操作时，只能使用before触发器，after不被允许

写一个因为下班时间不能执行插入数据操作的触发器(下面加粗部分是强制转换类型)

create or replace trigger insertCheckbefore insert on peopledeclare  nowDay varchar(24);    nowTime number(23);begin  select to_char(sysdate,'day') into nowDay from dual;  --dual是oracle中特定的表，to_char是表示强制转化  select to_number(to_char(sysdate,'hh24')) into nowTime from dual;  if nowDay in ('saturday','sunday') or nowTime not between 9 and 18 then --当不在工作时间内无法插入数据  raise_application_error('-20000','The staff is offduty');  end if; end;

触发器类型

• 语句级触发器

在语句执行之前或之后执行触发器，不管影响几行

create or replace trigger logafter insert on people  --当people表中插入数据之后，会触发触发器declarebegin  dbms_output.put_line('success to insert data');end;

• 行级触发器（for each）

触发语句作用的每一条记录都被触发。在行级触发器中使用:old和:new伪记录变量, 识别值的状态

涉及到行级别的新旧数据对比，需要用行级触发器，需要在begin前加for each row

写一个涨工资的触发器（如果更新工资低于原先的工资则不能涨）

create or replace trigger updateCheckbefore update on empfor each row --the command has the highest  superioritybegin  if  :new.sal <:old.sal  raise_application_error('-20000','the new salary less than the old one!');  end if;end;