第一章 PL/SQL简介
一、
PL/SQL:过程化的SQL语言,也就是在SQL语句的基础上加入了条件判断、循环等过程化的语句。
使SQL能够完成更加复杂的操作和计算。用SQL语句进行编程
二、PL/SQL块
1、块结构
Declare
--声明部分,所有的变量或常量,在此部分声明
--如果程序中不需要变量或常量,此部分可省略
Begin
--可执行部分,功能的实现部分,不能省略
Exception
--异常处理部分
--此部分可以省略
End;--结束部分,标识了程序的边界,不能省略
--end关键字末尾一定要加;结尾
--每一条语句用一行书写,末尾用;结尾
2、编辑和运行PL/SQL块
a.要在SQLplus环境下编辑,运行。 --也可以在PL/SQL developer 中新建Command Window中
b.用/来运行PL/SQL块
----------------------------------------------------------------------------
C:\Documents and Settings\ttc>sqlplus scott/tiger@orcl
SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 2月 21 09:20:20 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> declare
2 ...
3 ...
4 begin
5 ...
6 exception
7 ...
8 end;
9 /
----------------------------------------------------------------------------
3、输出语句
dbms_output.put_line(列|表达式|变量);--包.过程(参数),向屏幕上输出列|表达式|变量的值
serveroutput 环境变量
--在一个会话开始的时候,要设置环境变量serveroutput为打开状态,此会话中的所有的PL/SQL块才能输出值到屏幕上
--PL/SQL developer中,一个会话就是一个新建的Command Window文件
--通常在PL/SQL developer中的Command Window文件,使用Editor编辑(F8执行)
Set serveroutput On
练习1:编辑一个PL/SQL块,输出hello world.
--------------------------------------------------------------------------------------------
SQL> begin
2 dbms_output.put_line('Hello world');
3 end;
4 /
Hello world
PL/SQL procedure successfully completed
--------------------------------------------------------------------------------------------
4、块的类型
a.匿名块:没有名字的PL/SQL块,不能够存储,只能写一次执行一次,也是不能被调用。通常用来做调试。
b.子程序(存储过程、函数):有名字的PL/SQL块,被当做对象存储在数据库中。只有被调用才能执行。
c.触发器:有名字的PL/SQL块,被当做对象存储在数据库中。触发器不能被调用,它是自动运行的
三、变量
1、变量的命名规则
a.首先,要符合Oracle对象的命名规则;
b.不能和数据库中表名相同;
c.不能和数据库中表中的列名相同
d.通常状况下,变量的命名要遵循一定的原则
变量:v_变量名 v_sal--emp表中员工的工资的变量
常量:c_常量名 c_ename--emp表中员工的姓名的常量
2、变量的种类
a.标量型:这种变量只有一个值,内部没有分量的数据类型
i.数字型
Number ,Binary_Integer,Pls_Integer
--Pls_Integer所占用的存储空间更小,效率稍微高
ii.字符型
Varchar2(n):必须要指定n,n<32767,--在Oracle SQL中,n<4000
Char[(n)]:n默认为1,n<32767--在Oracle SQL中,n<2000
--当PL/SQL块使用字符类型变量操作表中相应的列的时候,一定要注意,不能超过列的最大长度
iii.日期型
Date
iv.布尔型:是PL/SQl特有的数据类型,SQL不支持
Boolean :(True,False,Null)Null:代表不可知,不确定
b.复合型:内部有分量的数据类型
i.PL/SQL记录(record):相当于java中的类
ii.PL/SQL表(table):相当于数组。(Map)
c.引用型:相当于指针
i.游标(Cursor)
d.lob型
i.内部lob:Clob,Blob
ii.外部lob:Bfile
3、变量的声明
变量的声明必须在declare,其他部分中不能包含声明语句
a.格式
变量名 [Constant]变量的数据类型[Not Null]{[:=表达式|Default 表达式]};
String str="Hello world";--java中声明,初始化
str Constant Varchar2(10):='Hello world';--PL/SQL中声明,初始化
str Constant Varchar2(10) Default 'Hello world';--PL/SQL中声明,初始化
--如果使用constant或者是not null 关键字来声明变量,那么一定要进行初始化
--变量名不区分大小写
b.%Type
变量名 前缀%Type;
--前缀可以是数据库中表的列名,也可以是先前定义过的变量的变量名
--变量的数据类型与表中的列或先前定义过的变量保持一致
v_sal emp.sal%Type;--v_sal与emp表中的sal列的数据类型一致
str2 str%Type;--str2与str的数据类型保持一致
--声明变量的时候,不用去表中查询,就能够使数据类型和表中的列一致
--用这种方式声明变量,不论表中列的数据类型如何变化,能够做到不修改程序继续运行
4、变量的赋值方法
a.直接赋值
变量:=表达式;
--在PL/SQL中的赋值运算符是:=
--表达式和变量的数据类型要一致
b.使用select语句进行赋值,从数据库中查询出数据给变量赋值
Select 列名
Into 变量名
From 表名
[Where 条件];
--PL/SQL中如果出现了select语句的话,一定要有into子句;
--当且仅当select语句返回的结果集是一条的时候(有结果+只有一条),赋值才会成功
--如果select语句返回0条或多条的时候,都会抛出异常
练习:写一个pl/sql块,输出scott的工资(在pl/Sql developer-command window-editor中运行)
--Select * From emp Where lower(ename)='scott';
----------------------------------------------------------------------------------------
Declare
v_Sal Emp.Sal%Type;
Begin
Select Sal Into v_Sal From Emp Where Lower(Ename) = 'scott';
Dbms_Output.Put_Line('scott''s salary is ' || v_Sal);
End;
/
--运行结果
--scott's salary is 3300
--PL/SQL procedure successfully completed
-----------------------------------------------------------------------------------------
--Select max(deptno) from emp;
练习:写一个pl/sql,输出最大的部门编号
----------------------------------------------------------------------------------------
Declare
v_Deptno Emp.Deptno%Type;
Begin
Select Max(Deptno) Into v_Deptno From Emp;
Dbms_Output.Put_Line('The max(deptno) is ' || v_Deptno);
End;
/
--运行结果
--The max(deptno) is 30
--PL/SQL procedure successfully completed
-----------------------------------------------------------------------------------------
5、非PL/SQL变量
a.外部变量(声明在PL/SQL的外部)
声明: Variable 变量名 数据类型--数据类型不能使用%Type
可以用 print 变量名 查看变量当前的值;
在PL/SQL块中,可以给变量赋值
:外部变量名:=表达式;
--外部变量的名字以g_开头
------------------------------------------------------------------------------
SQL> variable g_num Number
SQL> begin
2 :g_num:=1;
3 end;
4 /
PL/SQL procedure successfully completed
g_num
---------
1
SQL> print g_num
g_num
---------
1
SQL> begin
2 :g_num:=100;
3 end;
4 /
PL/SQL procedure successfully completed
g_num
---------
100
------------------------------------------------------------------------------
练习:要求使用外部变量输出scott的工资
------------------------------------------------------------------------------
Variable g_sal Number
Begin
Select sal Into :g_sal From emp Where ename='SCOTT';
End;
/
--PL/SQL procedure successfully completed
--g_sal
---------
--3300
------------------------------------------------------------------------------
b.替代变量(交互式命令)
变量名 : &变量名--不需要事先声明、直接用.
--替代变量如果代表的是字符或日期类型的值,变量名一定要写在单引号当中
--可以使用在SQl语句当中
------------------------------------------------------------------------------
SQL> begin
2 dbms_output.put_line('&p_str');
3 end;
4 /
--11111111111
--PL/SQL procedure successfully completed
------------------------------------------------------------------------------
练习:写一个PL/SQL块,输出员工的工资,员工的姓名有用户在程序的运行时输入
-------------------------------------------------------------------------------
Declare v_sal emp.sal%Type;
Begin
Select sal Into v_sal From EMP Where ename='&p_name';
dbms_output.put_line(v_sal);
End;
/
--SQL> 3300
--PL/SQL procedure successfully completed
----------------------------------------------------------------------------------