PLSQL基础

oracle219

---

---

PLSQL基础

PLSQL就是在SQL的基础之上加上了流程控制语言

oracle中的数据类型：

1.sql的数据类型也就是表中列的数据类型

2.plsql中定义的变量的数据类型这类型是内存当中的数据类型和上面完全是两个概念

T/SQL赋值：select @a = 3/set @a = 3;select @a = count(*) from tab;

PL/SQL赋值：a := 3;select count(*) into a from tab;

SQL> desc test_index;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID CHAR(10)

SQL> set serverout on;
SQL> declare
2 totalcount number;
3 begin
4 select count(*) into totalcount from test_index;
5 dbms_output.put_line('一共有'||totalcount||'条记录数');
6 end;
7 /
一共有800001条记录数

PL/SQL procedure successfully completed.

SQL>

11G提出一个新类型：simple_integer类型，定义不能为空、可以提高性能因为这个数据类型直接操作与硬件

下面是一个简单的对clob的操作

SQL> create table test_clob(
2 id number(10),
3 text_info clob);

Table created.

SQL> insert into test_clob values(1,'说干啥

SQL> set serverout on;//作为输出界面

SQL>

SQL> declare

2 total_count clob;

3 select_count varchar2(2000);

4 select_cou integer ;

5 offset integer;

6 begin

7 offset :=1;

8 select_cou := 1000;

9 select text_info into total_count from test_clob where id = 1;

10 dbms_lob.read(total_count,select_cou,offset,select_count);

11 dbms_output.put_line(select_count);

12 end;

13 /

身份说法萨芬水电费水电费水电费水电费等收费....略...

PL/SQL procedure successfully completed

SQL>

下面的是对blob数据类型进行操作

把'/u01/app/oracle/pl_sql_pacakge/test_blob/psue.jpg'的文件保存到数据库中去

--创建表

createtableinsert_blob(idnumber,blob_fileblob);

--创建目录

createorreplacedirectoryPHONEas'/u01/app/oracle/pl_sql_pacakge/test_blob';

--赋予读写目录权限给用户

grantwrite,readondirectoryPHONEtohr;

--创建存储过程

createorreplaceprocedureinsert_blob_pro(pidnumber,file_namevarchar2)

is

file_bfilebfile;

file_blobblob;

file_sizenumber;

begin

--先插入一个空值

insertintoinsert_blobvalues(pid,empty_blob());

--取出空值行的那一列

selectblob_fileintofile_blobfrominsert_blobwhereid=pid;

file_bfile:=bfilename('PHONE',file_name);

--打开文件

dbms_lob.open(file_bfile);

--读取文件大小

file_size:=dbms_lob.getlength(file_bfile);

--从某个打开的文件读取到内存中

dbms_lob.loadfromfile(file_blob,file_bfile,file_size);

--关闭文件

dbms_lob.close(file_bfile);

commit;

end;

SQL> execute insert_blob_pro(pid => 1,file_name => 'psue.jpg');

PL/SQL procedure successfully completed

SQL> select * from insert_blob;

ID BLOB_FILE

---------- ---------

1<BLOB>

SQL>

pl/sql 11g 对序列的改进

create sequence seq1 start with 1 increment by 3;

set serverout on;

declare

aa int;

begin

aa := seq1.nextval;

dbms_output.put_line(aa);

end;

注意属性类型：

q用于引用数据库列的数据类型，以及表示表中一行的记录类型

q属性类型有两种：

q%TYPE - 引用变量和数据库列的数据类型

q%ROWTYPE - 提供表示表中一行的记录类型

q使用属性类型的优点：

q不需要知道被引用的表列的具体类型

q如果被引用对象的数据类型发生改变，PL/SQL 变量的数据类型也随之改变，健壮性

如何使用流程控制if语句？

SQL> declare

2 sname varchar2(20);

3sage student.sage%TYPE;

4 begin

5 select s.sname,s.sage into sname,sage from student s where s.sno = 1;

6 if sage >= 25 then

7 dbms_output.put_line('该'||sname||'同学已经成年了');

8 else dbms_output.put_line('该'||sname||'同学还未成年');

9 end if;

10 end;

11 /

该AA同学还未成年

PL/SQL procedure successfully completed

SQL>

如何使用控制流程case语句？

SQL> declare

2 out_grade varchar2(20);

3 begin

4 out_grade := case&grade//表示需要用户输入的数据信息

5 when 'A' then '优秀'

6 when 'B' then '良好'

7 when 'C' then '及格'

8 when 'D' then '不及格'

9 else '没有成绩'

10 end ;

11 dbms_output.put_line(out_grade);

12 end;

13 /

优秀

PL/SQL procedure successfully completed

SQL>

如何使用循环控制loop、while loop、for loop等

由于loop是无条件的循环所以需要添加退出语句exit 、exit when。

// while循环

declare

jnumber:=1;

begin

whilej<8

loop

dbms_output.put_line('---'||to_char(j));

j:=j+1;

endloop;

dbms_output.put_line('结束循环');

end;

//for循环

declare

jnumber:=1;

begin

forjin1..10

loop

dbms_output.put_line('---'||to_char(j));

endloop;

dbms_output.put_line('结束循环');

end;

//continue是11G新增的，结束本次循环的目的和高级语言一样，exit相当于高级语言中break一样

declare

jnumber:=0;

begin

loop

j:=j+1;

dbms_output.put_line('---'||to_char(j));

exitwhenj>10;

continuewhenj>5;

endloop;

dbms_output.put_line('循环结束');

end;

如何使用跳转语句goto

declare

jnumber:=1;

begin

<<aa>>

j:=j+1;

dbms_output.put_line('--'||to_char(j));

ifj<10then

goto aa;

endif;

ifj>10then

gotobb;

endif;

<<bb>>null;--什么都不做

dbms_output.put_line('结束');

end;

什么事动态SQL?

动态 SQL 是指在PL/SQL程序执行时生成的 SQL 语句

编译程序对动态 SQL 不做处理，而是在程序运行时动态构造语句、对语句进行语法分析并执行

DDL 语句命令和会话控制语句不能在 PL/SQL 中直接使用，但是可以通过动态 SQL 来执行

执行动态 SQL 的语法：

EXECUTE IMMEDIATEdynamic_sql_string

[INTO

define_variable_list]

[USING

bind_argument_list];

使用动态SQL

SQL> begin

2 create table test(id number);

3 end;

4 /

begin

create table test(id number);

end;

ORA-06550: 第 2 行, 第 1 列:

PLS-00103: 出现符号 "CREATE"在需要下列之一时：

( begin case declare

exit for goto if loop mod null pragma raise return select

update while with <an identifier>

<a double-quoted delimited-identifier> <a bind variable> <<

continue close current delete fetch lock insert open rollback

savepoint set sql execute commit forall merge pipe purge

SQL> begin

2execute immediate 'create table test(id number)';

3 end;

4 /

PL/SQL procedure successfully completed

SQL> desc test;

Name Type Nullable Default Comments

---- ------ -------- ------- --------

ID NUMBER Y

SQL>

异常的分类

1. 预定义异常，oracle已经定义好的异常

SQL> set serverout on;

SQL>

SQL> declare

2 sname student.sname%TYPE;

3 begin

4 select sname into sname from student where sno = 4;

5 dbms_output.put_line('可以查找到'||sname||'同学');

6 exception

7 when NO_DATA_FOUND then

8 dbms_output.put_line('没有找到该学号的学生');

9 end;

10 /

没有找到该学号的学生

PL/SQL procedure successfully completed

SQL>

2. 用户自定义异常，由用户自己定义的异常

declare

sno student.sno%type;

sage student.sage%type;

sname student.sname%type;

student_no_grownexception;--声明异常

begin

selectsname,sage,snointosname,sage,snofromstudent swheres.sno=1;

ifsage<25then

raisestudent_no_grown;--抛出异常

else

dbms_output.put_line('找到sno = '||to_char(sno)||'学号的学生了,'||'sname = '||sname);

endif;

exception

whenno_data_foundthen--捕获oracle预定异常

dbms_output.put_line('找不到sno = '||to_char(sno)||'学号的学生！');

whenstudent_no_grownthen--捕获用户自定义异常

dbms_output.put_line('找到sno = '||to_char(sno)||'学号的学生了,'||'sname = '||sname||'但是还未成年');

raise_application_error(-20000,'学生没成年');--错误编号和错误信息

end;

ORA-20000: 学生没成年

ORA-06512: 在 line 18

2.5.1关系运算符

运算符	意义
=	等于
<> , != , ~= , ^=	不等于
<	小于
>	大于
<=	小于或等于
>=	大于或等于

2.5.2一般运算符

运算符	意义
+	加号
-	减号
*	乘号
/	除号
:=	赋值号
=>	关系号
..	范围运算符
||	字符连接符

2.5.3逻辑运算符

运算符	意义
IS NULL	是空值
BETWEEN AND	介于两者之间
IN	在一列值中间
AND	逻辑与
OR	逻辑或
NOT	取返,如IS NOT NULL, NOT IN