(oracle数据库基础)第五章 PL/SQL基础

来源：互联网发布：android 启动优化编辑：程序博客网时间：2024/05/29 05:02

1.PL/SQL块

declare

begin

exception

end;

2.PL/SQL基本数据类型：预定义数据类型和用户自定义子类型

--预定义数据类型

a.数字数据类型number、binary_integer(存储符号整型)、pls_integer(存储符号整型)

b.字符数据类型char(size)、varchar2(size)

c.日期数据类型date、timestamp...

d.布尔类型true、false

e.大对象数据类型blob、clob、nclob、bfile

binary_integer和pls_integer的区别：

(1)当两者运算的结果赋值给一个number变量时，如果pls_integer溢出，系统会有异常，而binary_integer不会。

(2)pls_integer效率高于binary_integer。oracle建议为了与原有应用兼容，可以继续使用binary_integer。

新开发中建议用pls_integer提高效率。

3.定义变量

语法格式：变量名[constant]变量数据类型 [notnull][{:=|default}default_value]

--变量例子：根据emp表中各列数据类型，定义各列变量

declare

v_ename varchar2(10);

v_job varchar2(9);

begin

v_ename:='smith';

v_job:='clerk';

dbms_output.put_line(v_ename ||' works as ' || v_job ||' in buaa-sem-108.');--dbms_output.put_line相当于java的system.out.println();

end;

--常量例子：定义常量DAY_OF_MONTH为30，DAY_OF_YEAR为365。

declare

DAY_OF_MONTH constantnumber(3) :=30;

DAY_OF_YEAR constantnumber(3)default365;

begin

dbms_output.put_line(DAY_OF_MONTH);--dbms_output.put_line相当于java的system.out.println();

dbms_output.put_line(DAY_OF_YEAR);--dbms_output.put_line相当于java的system.out.println();

end;

--&的使用

declare

v_empno number(4) :=&v_empno;

begin

dbms_output.put_line(v_empno);--dbms_output.put_line相当于java的system.out.println();

end;

4.PL/SQL复合数据类型：记录和集合

4.1记录：由域组成，域可以看成表中的列，记录可以看成表中的行。访问记录中的域使用：记录名.域名;

a.显示定义记录

语法：

type record_nameisrecord(field_definition_list);

<变量名> <记录名>;

域的定义和变量定义相同。

--例子：定义记录

declare

type t_tempisrecord--记录

(

v_empno number(4),--域

v_ename varchar(20),

v_job varchar2(9)

);

--如果要定义跟表中相同的类型，可以使用以下方式直接引用表中的类型（建议使用）：

declare

type t_tempisrecord--记录

(

v_empno emp.empno%type,--域

v_ename emp.ename%type,

v_job emp.job%type

);

b.隐式定义记录(强烈推荐使用)

--用%rowtype定义表中对应的记录

--例子：用%rowtype属性定义表emp中对应的列的记录。

declare

v_emp emp%rowtype;--记录

begin

v_emp.ename:='SMITH';--访问记录中的域用"."符号。

v_emp.job:='CLERK';

dbms_output.put_line(v_emp.ename||' works as '||v_emp.job);

end;

--总结：%type只是引用了表中某一列，而%rowtype引用了整张表。

4.2集合:与其他语言的数组类似。

用type定义，包括三种类型：index_by表、嵌套表、可变数组。

三者不同点：

前者不能存储在数据库中，但是后两者可以。

共同点：

都是一位数组结构

都有内建方法

访问由点分割

a. index_by表

定义语法：type type_nameistableof element_type[notnull]indexbybinary_integer;--element_type是任意合法的PL/SQL数据类型

--例子：定义index_by表，存储表emp中员工号为7369的员工号。

declare

type table_emp_typeistableof emp.empno%typeindexbybinary_integer;

table_empno table_emp_type;--定义一个index_by类型的变量

i binary_integer:=1;--定义一个变量当下标

begin

select empnointo table_empno(i)from empwhere empno=7369;

dbms_output.put_line(table_empno(i));

end;

--例子：定义index_by表，存储表emp中员工号为7369的员工信息。

declare

type table_emp_typeistableof emp%rowtypeindexbybinary_integer;

table_emp table_emp_type;--定义一个index_by类型的变量

i binary_integer:=1;--定义一个变量当下标

begin

select *into table_emp(i)from empwhere empno=7369;

dbms_output.put_line(table_emp(i).ename);

end;

b. 嵌套表

类似index_by表，语法也相似，但是没有indexby binary_integer子串。

语法：

type type_nameistableof element_type[notnull];--element_type可以一个记录，但是记录只能是标量数据类型字段级用于数据库的数据类型。

注意：嵌套表用构造函数初始化，构造函数和集合的名字相同，同时有一组参数，具体看下面的例子。每个参数对应一个元素，如果没有初始化，则不能引用该元素。

--例子：定义嵌套表，存储表emp表中五个员工号

declare

type nested_table_empno_typeistableof emp.empno%type;--定义嵌套表

nest_tabe_empno nested_table_empno_type;--定义嵌套表类型的变量

begin

nest_tabe_empno := nested_table_empno_type(7369,7499,7521,7566,7654);--初始化嵌套表，nested_table_empno_type(7369,7499,7521,7566,7654)为构造函数

dbms_output.put_line(nest_tabe_empno(3));--输出第三个员工号

end;

c. 可变数组

定义语法：

type type_nameis[varray |varyingarray](max_size)of element_type[notnull];

--max_size为整数，用于标识varray集合拥有的最多元素数目。

--例子：定义可变数组，存储表emp中的两个员工号

declare

type varray_empno_typeisvarray(5)of emp.empno%type;

varray_empno varray_empno_type;

begin

varray_empno := varray_empno_type(7369,7499);

dbms_output.put_line(varray_empno(2));

end;

--集合还有很多内建函数(方法)。使用的语法：集合名.方法名

--例子：对于已定义的集合变量，可以使用count、delete、first、last、next、exists和prior等方法进行操作，返回的是数字。

declare

type element_typeistableofvarchar2(9)indexbybinary_integer;

v_e_p element_type;

begin

v_e_p(1) :='buaa';

v_e_p(2) :='sem';

v_e_p(3) :='108';

dbms_output.put_line('总记录数：'||to_char(v_e_p.count));

dbms_output.put_line('第一条记录：'||v_e_p.first);

dbms_output.put_line('最后一条记录：'||v_e_p.last);

dbms_output.put_line('第二条的前一条记录：'||v_e_p.prior(2));

dbms_output.put_line('第二条的后一条记录：'||v_e_p.next(2));

end;

5.执行SQL

5.1select

语法：

select {column[,column,...]}

into (variable[,variable,...]|record)

from {table|(sub-query)}[alias]

where condition;

--例子：用PL/SQL查询员工编号为7369的员工姓名

declare

v_ename emp.ename%type;

v_job emp.job%type;

begin

select ename,jobinto v_ename,v_jobfrom empwhere empno=7369;

dbms_output.put_line(v_ename||' works as '||v_job);

end;

5.2DML(insert、update、delete)语句

--例子：用PL/SQL在表emp1中，插入员工号为7369的员工信息

--先复制表emp

createtable emp1asselect *from empwhere1=2;

--正事开始

declare

v_empno emp.empno%type:=7369;

v_emp emp%rowtype;

begin

select *

into v_emp

from emp

where empno=v_empno;

insertinto emp1values

(v_emp.empno,v_emp.ename,v_emp.job,v_emp.mgr,v_emp.hiredate,v_emp.sal,v_emp.comm,v_emp.deptno);

end;

--测试

select *from emp1;

5.3动态SQL：指DDL和不确定的DML(即带参数的DML)。

语法：

executeimmediate动态SQL语句using绑定参数列表returninginto输出参数列表;

--例子：使用动态SQL建表

declare

table_name varchar2(20);

field1 varchar2(20);

datatype1 varchar2(20);

field2 varchar2(20);

datatype2 varchar2(20);

str_sql varchar2(500);

begin

table_name:='sem_108';

field1:='id';

datatype1:='number(2)';

field2:='name';

datatype2:='varchar2(3)';

str_sql:='create table'||' '||table_name||'('||field1||' '||datatype1||','||field2||'

'||datatype2||')';

dbms_output.put_line(str_sql);

executeimmediate str_sql;

end;

--例子：使用动态SQL删除上个例子创建的表

declare

str_sql varchar2(100);

begin

str_sql:='drop table sem_108';

executeimmediate str_sql;

end;

5.4条件语句

包括：if-then语句、if-then-else语句、if-then-elsif语句和case语句。

--a. if-then语句

语法：

if conditionthen

sequence_of_statements

endif;

--例子：根据输入的员工号，如果职务是'CLERK'提高工资1%(对表emp1进行操作)。

declare

v_empno emp1.empno%type:=&v_empno;

v_job emp1.job%type;

begin

select jobinto v_jobfrom emp1where empno=v_empno;

if v_job='CLERK'then

update emp1set sal=sal*1.01where empno=v_empno;

dbms_output.put_line('OK!');

endif;

end;

select *from emp1;

--b. if-then-else语句

语法：

if conditionthen

sequence_of_statements1

else

sequence_of_statements2

endif;

--例子：根据输入的员工号，如果职务是CLERK提高工资1%，如果不是提高2%。

declare

v_empno emp1.empno%type:=&v_empno;

v_job emp1.job%type;

begin

select jobinto v_jobfrom emp1where empno=v_empno;

if v_job='CLERK'then

update emp1set sal=sal*1.01where empno=v_empno;

dbms_output.put_line('CLERK OK!');

else

update emp1set sal=sal*1.02where empno=v_empno;

dbms_output.put_line('Others OK!');

endif;

end;

select *from emp1;

--c. if-then-elsif语句

if condition1then

sequence_of_statements1

elsif condition2then

sequence_of_statements2

else

sequence_of_statements3

endif;

--例子：根据输入的员工号，如果职务是CLERK提高工资1%，如果职务是SALESMAN提高工资25%，如果是其他提高3%。

declare

v_empno emp1.empno%type:=&v_empno;

v_job emp1.job%type;

begin

select jobinto v_jobfrom emp1where empno=v_empno;

if v_job='CLERK'then

update emp1set sal=sal*1.01where empno=v_empno;

dbms_output.put_line('CLERK OK!');

elsif v_job='SALESMAN'then

update emp1set sal=sal*1.02where empno=v_empno;

dbms_output.put_line('SALESMAN OK!');

else

update emp1set sal=sal*1.03where empno=v_empno;

dbms_output.put_line('Others OK!');

endif;

end;

--测试

select *from emp1;

--d. case语句

case selector

when expression1then sequence_of_statements1;

when expression2then sequence_of_statements2;

......

when expressionNthen sequence_of_statementsN;

[else sequence_of_statementsN+1;]

endcase;

--例子：根据输入员工号，如果职务是CLERK提高工资1%，如果职务是SALESMAN提高工资2%，如果职务是MANAGER提高工资4%，

--如果职务是ANALYST提高工资3%。

declare

v_empno emp1.empno%type:=&v_empno;

v_job emp1.job%type;

begin

select jobinto v_jobfrom emp1where empno=v_empno;

case v_job

when'CLERK'then

update emp1set sal=sal*1.01where empno=v_empno;

dbms_output.put_line('CLERK OK!');

when'SALESMAN'then

update emp1set sal=sal*1.02where empno=v_empno;

dbms_output.put_line('SALESMAN OK!');

when'MANAGER'then

update emp1set sal=sal*1.03where empno=v_empno;

dbms_output.put_line('MANAGER OK!');

when'ANALYST'then

update emp1set sal=sal*1.04where empno=v_empno;

dbms_output.put_line('ANALYST OK!');

else

update emp1set sal=sal*1.05where empno=v_empno;

dbms_output.put_line('PRESIDENT OK!');

endcase;

end;

--测试

select *from emp1;

5.5循环语句

包括：loop、for-loop和while-loop。

a. loop语句

--语法1：

loop

sequence_of_statements

exit;--如果没有此结束语句，循环将永远执行下去。

endloop;

--语法2：

loop

sequence_of_statements

exitwhen condition;--如果没有此结束语句，循环将永远执行下去。

endloop;

--例子：输出1,2,...12。

declare

n number:=1;

begin

loop

dbms_output.put_line(n);

n:=n+1;

if n>12then

exit;

endif;

endloop;

end;

--例子：(更简洁的)输出1,2,...12。

declare

n number:=1;

begin

loop

dbms_output.put_line(n);

n:=n+1;

exitwhen n>12;

endloop;

end;

b. for loop语句

语法：

for counterin [reverse] lower_bound..higher_boundloop

sequence_of_statements

endloop;

--例子：输出1,2,...5的平方。

declare

i number:=1;

type list_typeistableofbinary_integerindexbybinary_integer;--定义一个binary_integer类型的集合，index_by表

list list_type;

begin

for iin1..5loop

list(i):=i*i;

dbms_output.put_line(list(i));

endloop;

end;

c. while loop语句

语法：

while conditionloop

sequence_of_statements

endloop;

--例子：根据输入字符串反向输出

declare

str1 varchar2(10):='&str1';

lennumber;

str2 varchar2(10);

begin

len:=length(str1);--将输入的字符串长度值赋给变量len

whilelen<>0loop

str2:=str2||substr(str1,len,1);--substr( string, start_position, [ length ] )

len:=len-1;

endloop;

dbms_output.put_line('输入的字符串为：'||str1);

dbms_output.put_line('反向输出字符串：'||str2);

end;

课后习题：

1.编写程序，程序的功能是输入员工号，如果该员工工龄在25年之上，工资提高10%。

declare

v_empno emp1.empno%type:= &empno;--输入的员工号

v_hirdate binary_integer;--工龄

begin

select (sysdate-e.hiredate)/365into v_hirdate from emp1 ewhere e.empno=v_empno;

if v_hirdate>25then

update emp1 eset e.sal=e.sal*(1+0.1)where e.empno=v_empno;

endif;

end;

2.编写程序，程序的功能是输入员工号，输出该员工经理的姓名。

--方式1

declare

v_empno emp1.empno%type:=&empno;

v_mgr emp1.mgr%type;

v_manager emp1.ename%type;

begin

select e.mgrinto v_mgrfrom emp1 ewhere e.empno=v_empno;

if v_mgrisnotnullthen--不能用 !=null

select e.enameinto v_managerfrom emp1 ewhere e.empno=v_mgr;

dbms_output.put_line('员工号为'||v_empno||'的所属经理是'||v_manager);

else

dbms_output.put_line('没有此员工或此员工没有经理');

endif;

end;

--方式2

declare

v_empno emp1.empno%type:=&empno;

v_mgr emp1.mgr%type;

v_manager emp1.ename%type;

begin

select e2.enameinto v_managerfrom emp1 e1, emp1 e2where e1.empno=v_empnoand e2.empno=e1.mgr;

dbms_output.put_line('员工号为'||v_empno||'的所属经理是'||v_manager);

end;