1、PL/SQL简介

1.PL/SQL过程体结构(PL/SQL0)

[Declare 声明部分|可选部分] Begin 主程序部分|必须部分 [Exception 异常处理程序部分|可选部分] End;

2.PL/SQL注释格式

单行注释:--||多行注释:/*可跨行*/

3.PL/SQL字符集

大写小写字母A-Z|a-z||数字0-9||其它ASCII字符的一部分（除字符串外不区分大小写）

4.PL/SQL分隔符

+|-|*|/|=|>|<|(|)|;|<>|!=|~=|^=|<=|>=|:=|=>|..||||%|,|@|/|:|**|<<|>>|--|/*|*/||

5.PL/SQL常用数据类型

字符类型

：：varchar2(Max_Length)//变长字符串，最大32767，数据库中varchar2最大为2000字节，最大长度Max-Length必须给出

：：char(Max_Length)//定长字符串，最大32767，数据库中char最大为2000字节，最大长度Max_Length默认为1,字符串长度不足Max_Length时自动补空格

：：long(Max_Length)//可变字符串，最大32760，数据库中long最大为2GB，所有PL/SQL中的任何字符串都能赋值给long类型变量，Max_Length必须给出

数值类型

：：number(p,s)//p,s都是可选，指定s则必须指定p；p数值中有效数字的个数，s为正时候表示小数位个数，s为负数时候表示整数位个数

：：pls_integer：：binary_integer：：dec：：decimal：：double precision：：integer：：int：：numeric：：real：：smallint//Oracle为了兼容其它数据库定义的同Number等价的类型

布尔类型

：：Boolean//只有三种取值：true||false||null

定义类型

：：Type <数据类型名> is <数据类型（类型定义）>；//允许定义的数据类型只要两种，Record(记录类型)||Table(表类型)

特殊类型

：：Table_name.column_name%TYPE//表示表对应字段的数据类型，能使用它来声明字段变量

Table_name%ROWTYPE//表示表对应记录（元组）的数据类型，能使用它来声明记录变量

6.常量和变量的定义

常量定义

：：<常量名> constant <数据类型>:=<值>;//定义常量必须赋初值，定义完成后值不允许更改

变量定义

：：<变量名> <数据类型(宽度)> [:=初始值]//初始化课可选的||对于未初始化的任何数据类型值被定义为null

7.流程控制语句

：：if.then...end if//判断if表达式为真就执行then后的语句

：：if.then...else...//判断if表达式为真就执行then后的语句,否则执行else后面的语句

：：if.then...elsif.then...else...end if //嵌套判断

：：case//有逻辑的从数值中做出选择,整个case语句的值为最后一个被执行的语句的值（如是一个字符串，整个表达式就是这个字符串）；

：：loop...exit.end//循环控制，判断exit后面的内容是否为真来执行exit退出循环

：：loop...exit when...end//判断when是否为真来执行exit退出循环

：：while.loop...end//判断while后是否为真，为假是退出执行

：：for...in...loop...end//已知循环次数的循环

：：goto//无条件转向流程控制（不推荐使用）

8.字符表达式

：：’sting’||’string1’||’string2’(字符连接表达式)

9.布尔表达式

：：算术表达式0R|AND算术表达式（对两个算术表达式的结果进行并或者交）

：：NOT 算术表达式（对算术表达式值取反）

：：n between m and k(判断n是否在m-k之间）

：：string in(string1,sting2,sting3..)(判断string是否属于后面字符串的集合

10.游标概念

：：声明游标：：Cursor <游标名称[参数 参数类型..]> Is 语句>

：：Select语句中的where中可以使用已经定义的PL/SQL变量，Select可以是除开有into的一切Select语句

：：Open 游标名称;（打开游标，检查所有的连编变量，执行Select语句，游标指向查询结果的第0行）

：：Close 游标名称;（关闭游标，允许重复打开，不允许重复关闭，重复代开会自动执行close）

：：提取游标：Fetch <游标名称> Into 变量列表||PL/SQL记录（游标后一行，并将当前行赋值给变量列表或者记录）

：：隐式游标：Select column1,column2.. Into l1,l2.. From table_name where (只返回一个元组的条件)

：：显示游标属性：游标名%FOUND:游标是否能够继续读取||%NOTFOUND同前面一个值相反||%ROWCOUNT已经读取过的行数||%ISOPEN游标是否代开||

：：显示游标属性：SQL%FOUND|SQL%NOTFOUND最近操作是否成功||SQL%ROWCOUNT最近执行的隐式SQL语句影响的条数

：：游标参数在使用时候必须传入，否则会报错！

：：游标变量：Type 类型名 Is REF Cursor Return 返回类型;

：：关联游标变量同Select语句：Open 游标变量 For Select语句（Select返回类型必须匹配）

：：游标变量的关闭：Close 游标名；

：：有特殊游标变量可以不使用任何返回类型（此种特殊类型游标变量可以同任何Select绑定，当不建议使用）

11.存储过程（PL/SQL1）

：：Create [Or Replace] Procedure <过程名> (<参数1>,[方式1]<数据类型1>，<参数2>,[方式2]<数据类型2>...) IS|AS PL/SQL0(PL/SQL过程体)

：：Execute 过程名(输入参数表)<执行过程>||Drop procedure 过程名<删除过程>

：：or replace::此参数可以覆盖已经定义过的过程（同名）

：：方式：in:输入参数||out:输出参数，在过程中被赋值，可以传递给过程体外的部分或环境使用||in out需要被传入，同时也可以传出

：：只有输出参数的PL/SQL都不能直接执行

12.存储函数（PL/SQL2)

：：Create [Or Replace] FUNCTION <函数名> (<参数1>,[方式1]<数据类型1>，<参数2>,[方式2]<数据类型2>...) Return <数据类型n> IS|AS PL/SQL0(PL/SQL过程体且含有return)

：：参数类型只要IN,RETURN 定义类型和PL/SQL过程体返回类型不一致会强制转换，如果运行完成没有遇到return，就会报错

：：可以使用Execute 定义的全局变量=函数名（输入参数表）||Drop function 函数名称（删除函数）||Or replace覆盖已经定义的函数

13.存储包（PL/SQL3)

：：包：用来打包PL/SQL块|常量|变量|自定义数据类型|异常|过程|函数|游标《用来组织PL/SQL块|PL/SQL函数|PL/SQL过程》

：：包的构成：说明部分<少修改；过程，函数，游标等首部>||包体部分<截图过程，函数，游标的实现>

：：格式：Create [or replace]package <包名称> Is 变量、常量、数据类型定义|游标定义|函数、过程定义和参数列表以及返回类型 End <包名称>//包的说明部分

：：格式：Create package body <包名称> AS 游标，函数，过程具体定义 END <包名称>//包的包体部分

：：包的调用：：包名称.变量名||包名称.常量名||包名称.游标名||包名称.函数名(参数表)||包名称.过程名(参数表)

：：删除包：Drop package 包名（会删除包的头部和包的主体部分）

：：覆盖定义时候使用or replace就行了：必须重新定义包的主体部分

14.存储触发器（PL/SQL4)

：：触发器类型：DML触发器||Instead of触发器||系统触发器（DDL语句|启动|关闭数据库等事件）

：：触发事件：DML语句（Insert|Update|Delete|Select）||DDL语句（Create|Alter|Drop）||数据库事件（关闭|启动|退出|异常|错误）||用户实际（连接登陆|退出数据库）

：：触发条件：When+逻辑表达式：只要此表达式值为真时候此触发器事件定义的事件才会执行

：：触发对象：数据表|视图|模式|数据库（table|schema|view|database)

：：触发操作：PL/SQL语句块（属于PL/SQL0）

：：触发时机：before||after:指定PL/SQL是否在DML语句执行之前执行还是DDL语句执行之后才执行

：：条件谓词：Inserting||Updating[(column1,column2..)]||Delete(针对不同的事件执行不同的操作的逻辑判断，这些默认参数在执行这些事件时候值为True否则值为False)

：：触发子类型：Row|Statement默认（ROW没执行一行就执行一次PL/SQL，Statement语句触发器，整个语句值执行一次PL/SQL语句（只在安全性检查是才使用）

：：新旧值：new||old(当前被处理行的新值和旧值：一般更新时候使用)

：：触发器格式：Create [or replace] trigger <触发器名称> <触发条件> （可调用PL/SQL1,PL/SQL2,PL/SQL3）>

：：触发器执行：自动执行

：：触发器删除：Drop trigger <触发器名称>||or replace重新定义触发器

15.同义词

：：同义词：为数据中表|索引|视图|模式|序列|存储过程|存储函数|存储包|存储触发器|对象|等其他对象（添加对象的多重引用）

：：创建私有同义词：Create [or replace] Synonym <同义词名称> for <对象名称>（用户必须在此模式上有Create Synonym权限）（创建同义词用户和创建同义词指定的用户可以使用此同义词）

：：创建公有同义词：Create [or replace] Public Synonym <同义词名称> for <对象名称>（用户必须在此模式上有Create Public Synonym权限）（所有用户均能使用此同义词引用此对象）

：：删除同义词：Drop [public] synonym <同义词名称>

：：同义词定义信息记录：dba_synonyms|all_synonyms|user_synonyms(必须有对应权限才能查看）

：：数据库连接信息记录：dba_db_links|all_db_links|user_db_links(必须有对应权限才能查看）

16.序列

：：序列：命名的顺序编号生成器，用于生成唯一编号的数据库对象，是连续的整数，往往用户生成唯一的主键编号

：：序列定义：Create Sequence <序列名称> [Increment by 整数] [Start with 整数] [Maxvalue 整数|Nomaxvalue] [Minvalue 整数|Nominvalue] [Cycle|Nocycle] [Cache n|Nocache]

：：定义说明：递增值，负数递减默认1|开始值，默认1|最大值，递增默认1027，递减-1|最小值，无默认，递增最小1,递减最小-1026|值溢出是否循环生成|缓存中预先分配数据值，默认20

：：使用序列：：

：：修改：Alter Sequence <触发器名称> [各种参数]（没有覆盖定义的方法）

：：删除：Drop Sequence <触发器名称>

：：序列数据字典存储视图：dba_sequence||all_sequence||user_sequence（用户必须要有视图的查看权限

/**************注释部分的pl/sql**************/

注释1：

Declare

v_sname char(20):='xurui';--定义了变量存储姓名

v_age number:=18;

Begin

Insert into student(sname,vage) values(v_sname,v_age);

End;

/

注释2:

Declare

v_sname char(20):='xurui';/*定义了变量存储姓名*/

v_age number:=18;

Begin

Insert into student(sname,vage) values(v_sname,v_age);

End;

/

/**************定义部分**************/

定义记录

type stu_record is Record(sno number(5) null:=0,sname varchar2(2)),sage number,ssex char(2));

stu1 stu_record;

使用表定义

type stu_record is Record(sno student.sno%type not null:=0,sname student.sname%type,sage student.vage%type,ssex student.vage%type);

stu_record student%rowtype;

/**************流程控制**************/

if..then语句

If grade>=60 Then dbms_output.put_line('成绩合格：'||grade);

End if;

if..then else语句

If grade>=60 Then dbms_output.put_line('成绩合格：'||grade);

Else dbms_output.put_line('成绩不合格'|grade);

End if;

if..then ..elsif语句

If grade>=60 then dbms_output.put_line('成绩合格：'||grade);

Elsif grade<60 then dbms_output.put_line('成绩不合格：'|grade);

End if;

case ...when 语句

Set serveroutput on

Declare

v_grade varchar2(20):='及格';

v_score varchar2(50);

Begin

v_score:= Case v_grade

when '不及格' then '成绩<60'

when '及格' then '60<=成绩<70'

when '中等' then '70<=成绩<80'

when '良好' then '80<=成绩<90'

when '优秀' then '90<=成绩<100'

else '输入成绩有错误'

End;

dbms_output.put_line(v_score);

End;

/

loop..exit ..end loop循环语句

Declare

ctr_var number:=0;

Begin

Loop

IF ctr_var>50 Then exit;

End if;

ctr_var:=ctr_var+1;

dbms_output.put_line(ctr_var);

End loop;

End;

/

loop... exit when end loop循环语句

Declare

ctr_var number:=0;

Begin

Loop

ctr_var:=ctr_var+1;

dbms_output.put_line(ctr_var);

Exit when ctr_var>=50;

End loop;

End;

/

while...loop...end loop语句

Declare

ctr_var number:=0;

Begin

While ctr_var<50

Loop

ctr_var:=ctr_var+1;

dbms_output.put_line(ctr_var);

End Loop;

End;

/

for..in..loop..end loop语句

Declare

ctr_var number:=0;

Begin

For ctr_var in 0..50

Loop dbms_output.put_line(ctr_var);

End loop;

End;

/

goto无条件转向语句（不推荐使用）

Declare

ctr_var number:=0;

Begin

<> --申明一个语句标号提供给GOTO语句使用

ctr_var:=ctr_var+1;

dbms_output.put_line(ctr_var);

If ctr_var<50 then goto lable;

End if;

End;

/

/**************游标使用**************/

游标定义

Declare

s_no number;

s_name varchar2(2);

s_age number;

s_sex char(2);

Cursor stu_cur Is Select sno,sname,sage,ssex from student;

Begin

.....

End;

/

使用游标

Declare

s_no number;

s_name varchar2(2);

s_age number;

s_sex char(2);

Cursor stu_cur Is Select sno,sname,sage,ssex from student;

Begin

Open stu_cur;

Close stu_cur;

End;

/

游标完整演示

Declare

s_no number(5);

s_name varchar2(20);

s_age number;

s_sex char(2);

Cursor stu_cur IS select sno,sname,sage,ssex from student;

Begin

Open stu_cur;

Fetch stu_cur Into s_no,s_name,s_age,s_sex;

Loop

Exit when NOT stu_cur%FOUND;

IF s_sex='男' then Insert into student_man values(s_no,s_name,s_age);

Else Insert into student_woman values(s_no,s_name,s_age);

End if;

Fetch stu_cur Into s_no,s_name,s_age,s_sex;

End loop;

Close stu_cur;

End;

/

使用游标修改

Declare

stu_record student%ROWTYPE;

Cursor stu_cur Is Select * from student Where sno=1;

Begin

For stu_record In stu_cur

Loop

update stu_cur set sage=10;

End loop;

End;

/

使用隐式游标

Begin

Select sno,sname,sage,ssex Into s_no,s_name,s_age,s_sex from student where sno=1;/*隐式游标必须使用Into字句,而且不允许返回多条记录*/

End;

参数游标

Accept my prompt '请输入一个学号'

set serveroutput on;

Declare

my number:=1;

s_name varchar2(20);

s_age number;

s_sex char(2);

Cursor stu_cur(id number) IS Select sname,sage,ssex from student where sno=id;

Begin

Open stu_cur(my);

Loop

Fetch stu_cur Into s_name,s_age,s_sex;

Exit when stu_cur%NOTFOUND;

dbms_output.put_line(s_name||s_age||s_sex);

End loop;

Close stu_cur;

End;

/

游标变量

Declare

Type stu_ref Is ref cursor Return student%rowtype;/*定义stu_ref类为cursor应用类*/

Type stu_record Is record(sname student.sname%type,sex student.ssex%type);/*定义记录类型*/

Type stu_ref1 Is ref cursor Return stu_record;

stu_rec1 stu_record;/*申明一个记录类型变量*/

Type stu_ref2 Is ref cursor Return stu_rec1%type;/*使用一个已有变量的类型作为游标变量的返回类型*/

ref_cur stu_ref;

ref_cur1 stu_ref1;

ref_cur2 stu_ref2;/*使用游标变量类型声明游标变量*/

Begin

End;

/**************过程使用**************/

带有输入参数的过程

Set serveroutput on;

Create or replace Procedure count_num(in_sex in student.ssex%type)

As

out_num number;

Begin

If in_sex='男' Then Select count(ssex) Into out_num from student where ssex='男';

Else Select count(ssex) Into out_num from student where ssex='女';

End if;

dbms_output.put_line(out_num);

End count_num;

/

带有输出参数的过程

Create or replace Procedure triple(in_num in number,out_num out number)

As

Begin

out_num:=in_num*in_num;

End triple;

/

带有in out参数的过程

Create or replace Procedure square(in_out_num in out number)

As

Begin

in_out_num:=in_out_num*in_out_num;

End square;

/

/**************函数使用**************/

定义一个普通函数

Create or replace function count_num1(in_sex in student.ssex%type)

Return number

As

out_num number;

Begin

If in_sex='男' Then Select count(ssex) into out_num from student where ssex='男';

Else Select count(ssex) into out_num from student where ssex='女';

End if;

return(out_num);

End count_num1;

调用函数

Set serveroutput on;

Declare

woman number;

man number;

Begin

woman:=count_num1('男');

man:=count_num1('女');

dbms_output.put_line(man||woman);

End;

/

/**************包的使用**************/

定义包头

Create or replace package my_package

IS

man number;

woman number;

function f_count(in_sex in student.ssex%type) return number;

procedure p_count(in_sex in student.ssex%type,out_num out number);--此处无法声明游标；

End my_package;

定义包体

Create or replace package body my_package

As

cursor student_cur Is Select * from student;

function f_count(in_sex in student.ssex%type)

Return number

As

out_num number;

Begin

If in_sex='男' Then Select count(ssex) into out_num from student where ssex='男';

Else Select count(ssex) into out_num from student where ssex='女';

End if;

return(out_num);

End f_count;

Procedure p_count(in_sex in student.ssex%type,out_num out number)

As

Begin

If in_sex='男' Then Select count(ssex) Into out_num from student where ssex='男';

Else Select count(ssex) Into out_num from student where ssex='女';

End if;

End p_count;

End my_package;

/

/**************触发器使用**************/

建立触发器

Create trigger my_trigger

Before insert or update of sno,sname on student

For each row

When (new.sname='xurui')

Declare

student_no student.sno%type;

err1 EXCEPTION;/*错误类型数据*/

Begin

Select sno into student_no from student where sname=new.sname;/*在表中查找名为xurui对于的学号，如果插入没有错误，次语句无意义*/

RAISE err1;/*读取系统是否有执行错误*/

EXCEPTION

When err1 Then Insert into Error(Sno,ERR) values(student_no,'这个学生的已经存在');

End my_trigger;

多处理触发器

Create trigger my_trigger1

After insert or update or delete on student

For each row

Declare

info char(10);

Begin

If inserting Then ...info:='插入操作';

Elsif updating ... info:='更新操作';

Elsif ... info:='删除操作';

End if;

Insert into sql_info values(info);/*记录对表的操作记录*/

End my_trigger1;

/

/**************同义词**************/

定义同义词(需要create synonym 或者create public synonym权限)

Create or replace synonym scott_emp1 scott.emp;

Create or repalce public synonym scott_pub_emp1 scott.emp;

/**************序列使用**************/

建立序列（需要Create sequence权限，删除需要Drop sequence权限）

Create sequence bbs_seq

Minvalue 1

Maxvalue 9999

Start with 1

Increment by 1

Cache 30;

使用序列

Crete or replace trigger bbs_trigger

Before insert on bbs_users

For each row

Begin

Select bbs_seq.Nextval Into new.ID from dual;/*DUAL表示系统负责维护的索引表鹏，nextval返回下一个值，currval返回当前值*/

End bbs_trigger;

3PLS