PL/SQL的标准语法及使用示例

pl/sql(procedural language/sql)

是一种过程化语言，pl/sql是oracle的专用语言，它是对标准sql语言的扩展，它允许在其内部嵌套普通的sql语句，也可以定义变量和控制程序的执行流程，这样就将sql语句的数据操纵和查询能力与pl/sql的过程处理能力结合在一起了

标准pl/sql块的语法格式：

[declare] --声明部分，可选

begin --执行部分，必须

[exception] --异常处理部分，可选

end

定义变量和常量

1.定义变量<变量名> <数据类型> [(长度):=<初始值>];//pl/sql的赋值符号为：=

2.定义常量<常量名> constant <数据类型>:=<常量值>;

基本数据类型

1.数值类型：常用的是number(m,n)m为总的位数，n为小数位数

2.字符类型：主要包括varchar2、char、long、nchar和nvarchar2等。这些类型的变量用来存储字符串或字符数据。

3.日期类型：date类型，共七个字节包括世纪、年、月、天、小时、分钟和秒

4.布尔类型：boolean，值有true、false或null

特殊类型：

1.%type

使用这个类型关键字可以声明一个与指定列名称相同的数据类型，它通常紧跟在指定列名后

例如：

在scott模式下，使用%type类型的变量输出emp表中编号为7369的员工姓名和职务信息

set serveroutput on

declare

var_ename emp.ename%type;

var_job emp.job%type;

begin

select ename,job into var_ename,var_job from emp where empno=7369;

dbms_output.put_line('编号为7369的员工姓名为：'||var_ename||',职位为'||var_job);

end;

/

2. record

record类型也称作“记录类型”，使用该类型的变量可以存储由多个列值组成的一行数据。在声明记录类型变量之前，首先需要定义记录类型，然后才可以声明记录类型的变量。记录类型是一种结构化的数据类型，它使用type语句进行定义，在记录类型的定义结构中包含成员变量及其数据类型

例如：

声明一个记录类型emp_type,然后使用该类型的变量存储emp表中编号为7369

员工的姓名、职务、工资信息，并输出这条记录信息。

declare

type emp_type is record(

var_ename emp.ename%type,

var_job emp.job%type,

var_sal emp.sal%type

);

emp_info emp_type;

begin

select ename,job,sal into emp_info from emp where empno=7369;

dbms_output.put_line('编号为7369员工姓名：'

||emp_info.var_ename||'，职务：'||emp_info.var_job||

'工资是：'||emp_info.var_sal);

end;

3.%rowtype

%rowtype类型的变量结合了“%type类型”和record类型变量的优点，它可以根据数据表中行的结构定义一种特殊的数据类型，用来存储从数据表中检索到的一行数据

例如：

声明一个%rowtype类型的变量rowvar_emp,然后使用该变量存储emp表中的一行数据。

declare

rowvar_emp emp%rowtype;

begin

select * into rowvar_emp from emp where empno=7369;

dbms_output.put_line('编号为7369的员工姓名为：'||rowvar_emp.ename||

',雇佣日期为：'||rowvar_emp.hiredate||'工资为：'||rowvar_emp.sal);

end;

/

选择语句：

1.使用if...then语句比较两个字符串变量的长度，并输出比较结果。

declare

var_str1 varchar2(20);

var_str2 varchar2(20);

begin

var_str1:='hello';

var_str2:='oracle is good';

if length(var_str1) dbms_output.put_line('字符串"'||var_str1||'"的长度小于"'||var_str2||'"');

end if;

end;

/

2.通过if..else语句实现只有年龄大于等于65岁，才可以申请退休，否则程序会提示不可以

申请退休。

declare

age number:=15;

begin

if age>=65 then

dbms_output.put_line('您可以申请退休');

else

dbms_output.put_line('年龄不到，不能申请退休!');

end if;

end;

/

3.指定一个月份数值，然后使用if...then..elsif语句判断它所属的季节，并

输出季节信息，代码如下。

declare

month int:=4;

begin

if month>=1 and month<=3 then

dbms_output.put_line('春天');

elsif month>=4 and month<=6 then

dbms_output.put_line('夏日炎炎');

elsif month>=7 and month<=9 then

dbms_output.put_line('秋天');

else

dbms_output.put_line('冬天很冷');

end if;

end;

/

4.使用loop语句求前100个自然数的和，并输出到屏幕。

declare

i int:=0;

sum_i int:=0;

begin

loop

i:=i+1;

sum_i:=sum_i+i;

exit when i=100;//结束条件

end loop;

dbms_output.put_line('前100个自然数的和是：'||sum_i);

end;

/

5.使用while语句求前100个自然数的和，并输出到屏幕。

declare

i int:=0;

sum_i int:=0;

begin

while i<=99 loop//这个i<99是进入循环的条件

i:=i+1;

sum_i:=sum_i+i;

end loop;

dbms_output.put_line('前100个自然数的和是：'||sum_i);

end;

/

6.使用for语句求前100个自然数中偶数之和，并输出到屏幕。

declare

sum_i int:=0;

begin

for i in 1..100 loop

if mod(i,2)=0 then//这是求余函数

sum_i:=i+sum_i;

end if;

end loop;

dbms_output.put_line('前100个自然数的和是：'||sum_i);

end;

/

7.指定一个季度数值，然后使用case语句判断它所包含的月份信息并输出。

declare

season varchar2(20):='夏天';

begin

case season

when '春天' then

dbms_output.put_line('包含1、2、3月份');

when '夏天' then

dbms_output.put_line('包含4、5、6月份');

when '秋天' then

dbms_output.put_line('包含7、8、9月份');

when '冬天' then

dbms_output.put_line('包含10、11、12月份');

else

dbms_output.put_line('未知季节!');

end case;

end;

/

内置函数：

测试系统函数可以使用

select ***(函数) from dual;语句其中dual是体统临时表

ascii(c)函数 ：a---97

chr(i)函数 : 97---a

concat(s1,s2)：函数连接字符串,返回新字符串

initcap(s)： 函数 将每个单词的首字母大写

abs(n)：返回n的绝对值

add_months(d,i)：返回日期d加上i个月之后的结果。其中，i为任意整数

last_day(d)：返回包含日期d月份的最后一天

sysdate：系统当前时间

to_char(x[,format])：该函数实现将表达式转换为字符串，format表示字符串格式

to_date(s[,format[lan]])：该函数将字符串s转换成date类型，format表示字符串格式，lan表示所使用的语言

avg:平均值

count():行数

power(a,b)a的b次方

round(a,b):给a保留b为小数并四舍五入

pl/sql存储过程

存储过程是一种命名的pl/sql程序块

存储过程保存在中，因为是已经编译好的代码，所以在被调用时，其执行效率非常高

存储过程的参数种类：

1. in 输入参数(默认)

2. out 输出参数

3. in out 输入输出参数