Oracle知识

最新推荐文章于 2021-11-24 17:07:16 发布

原创

最新推荐文章于 2021-11-24 17:07:16 发布 · 1.3k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #数据库 #plsql

Number(38,0)类型:38表示总长度，0表示小数的长度，为0，如果为2，就表示后面有2位小数

存储过程无参的话，不加（），存储过程一般用在数据库的更新上

用sequence实现自增长

-- 创建

create sequence tt

minvalue 1

maxvalue 999999

nocycle --不循环

start with 201

increment by 1

cache 100--缓存 100个

order;

--删除

drop sequence tt;

--修改

alter sequence tt

cycle;

insert into temp(id) values( tt.nextval);

Insert into temp(id) values(tt.currval)

dbms_output.put('the first');//单独用这条语句无法打印，需要结合DBMS_OUTPUT.NEW_LINE;

--获取表中的数据放入变量中

declare

userid NUMBER(38, 0) := 0;

user_name varchar2(50) := '';

pass_word varchar2(50) := '';

begin

select id, username, password into userid, user_name, pass_word

from tuser

where id = 1;

dbms_output.put_line(userid || ' --> ' || user_name || ' --> ' ||

pass_word);

end;

--null运算

select null+5 from dual;

--select null < 5 from dual;

select NULL|| 'ABC' from dual;

-显示游标(用记录类型获取)

declare

--声明游标

cursor c_tuser is

select * from tuser;

--声明变量

v_tuser tuser%rowtype;

begin

open c_tuser;

loop

fetch c_tuser

into v_tuser;

dbms_output.put_line(v_tuser.id || ' ' || v_tuser.username || ' ' || v_tuser.password);

exit when c_tuser%notfound;

end loop;

close c_tuser;

end;

--for循环操作游标

declare

begin

for v_tuser in (select * from tuser) loop

dbms_output.put_line(v_tuser.id || ' ' || v_tuser.username || ' ' || v_tuser.password);

end loop;

end;

PL/SQL语言是的SQL语言扩展，具有为程序开发而设计的特性，如数据封装、异常处理、面向对象等特性。

PL/SQL是嵌入到Oracle服务器和开发工具中的，具有很高的执行效率和同Oracle数据库的完美结合。在PL/SQL模块中可以使用查询语句和数据操纵语句(即进行DML操作)，这样就可以编写具有数据库事务处理功能的模块。

至于数据定义(DDL)和数据控制(DCL)命令的处理，需要通过Oracle提供的特殊的DMBS_SQL包来进行。PL/SQL还可以用来编写过程、函数、包及数据库触发器。过程和函数也称为子程序，在定义时要给出相应的过程名和函数名。它们可以存储在数据库中成为存储过程和存储函数，并可以由程序来调用，它们在结构上同程序模块类似。

PL/SQL过程化结构的特点是：

可将逻辑上相关的语句组织在一个程序块内；

通过嵌入或调用子块，构造功能强大的程序；

可将一个复杂的问题分解成为一组便于管理、定义和实现的小块。

块结构和基本语法

PL/SQL程序的基本单元是块(BLOCK)，块就是实现一定功能的逻辑模块。一个PL/SQL程序由一个或多个块组成。块有固定的结构，也可以嵌套。一个块可以包括三个部分，每个部分由一个关键字标识。块中各部分的作用解释如下：

(1) DECLARE：声明部分标志。

2) BEGIN：可执行部分标志。

(3) EXCEPTION：异常处理部分标志。

(4) END；:程序结束标志。

显示PL/SQL程序模块的输出信息。

DBMS_OUTPUT.PUT(字符串表达式)；

DBMS_OUTPUT.PUT_LINE(字符串表达式)；

DBMS_OUTPUT.NEW_LINE；//回车

注意：是否显}示用DBMS_OUTPUT.PUT_LINE包进行输出的信息。

　　SQL> SET SERVEROUT[PUT] {ON|OFF

块结构和基本语法

在PL/SQL模块中可以使用查询语句和数据操纵语句(即进行DML操作)，所以PL/SQL程序是同SQL语言紧密结合在一起的。

在PL/SQL程序中，最常见的是使用SELECT语句从数据库中获取信息，同直接执行SELECT语句不同，在程序中的SELECT语句总是和INTO相配合，INTO后跟用于接收查询结果的变量，形式如下：

SELECT 列名1，列名2... INTO 变量1，变量2... FROM 表名 WHERE 条件；

注意：接收查询结果的变量类型、顺序和个数同SELECT语句的字段的类型、顺序和个数应该完全一致。

并且SELECT语句返回的结果集必须是一行，否则将引发系统错误。当接收返回的多行结果时，可以采用后面介绍的游标的方法。

基本语法

使用INSERT、DELETE和UPDATE的语法没有变化，但在程序中要注意判断语句执行的状态，并使用COMMIT或ROLLBACK进行事务处理。

“--”是注释符号，后边是程序的注释部分。该部分不编译执行，所以在输入程序时可以省略。/*......*/中间也是注释部分，同“--”注释方法不同，它可以跨越多行进行注释。

Varchar2:是数据库特有的数据类型，varchar:是数据库都有的数据类型

PL/SQL数据类型

数据类型

定义变量

变量的作用是用来存储数据，可以在过程语句中使用。

变量在声明部分可以进行初始化，即赋予初值。

变量在定义的同时也可以将其说明成常量并赋予固定的值

变量的命名规则是：

以字母开头，后跟其他的字符序列，字符序列中可以包含字母、数值、下划线等符号

最大长度为30个字符，不区分大小写。

不能使用Oracle的保留字作为变量名。

变量名不要和在程序中引用的字段名相重，如果相重，变量名会被当作列名来使用。

定义变量empno=..de mingzi shi

变量的作用范围是在定义此变量的程序范围内，如果程序中包含子块，则变量在子块中也有效。但在子块中定义的变量，仅在定义变量的子块中有效，在主程序中无效。

语法：变量名 [CONSTANT] 类型标识符 [NOT NULL][:=值|DEFAULT 值];

关键字CONSTANT用来说明定义的变量是常量，如果是常量，必须有赋值部分进行赋值。

关键值NOT NULL用来说明变量不能为空。

∶=或DEFAULT用来为变量赋初值。

变量可以在程序中使用赋值语句重新赋值。通过输出语句可以查看变量的值。

变量名:=值或 PL/SQL 表达式;

两种特殊的类型

%TYPE:根据表的字段或变量类型定义变量

变量的声明还可以根据数据库表的字段进行定义或根据已经定义的变量进行定义。方法是在表的字段名或已经定义的变量名后加 %TYPE，将其当作数据类型。定义字段变量的方法如下：

变量名表名.字段名%TYPE;

%ROWTYPE:记录变量的定义

还可以根据表或视图的一个记录中的所有字段定义变量，称为记录变量。记录变量包含若干个字段，在结构上同表的一个记录相同，定义方法是在表名后跟%ROWTYPE。记录变量的字段名就是表的字段名，数据类型也一致。

记录变量名表名%ROWTYPE；获得记录变量的字段的方法是：记录变量名.字段名，如emp_record.ename。

declare

v_record student%rowtype;

begin

select * into v_record from student where name='mly';

Dbms_Output.put_line('id:'||v_record.studentid ||' name:' ||v_record.name || ' age:' ||v_record.age);

end ;

运算符和函数

PL/SQL常见的运算符和函数包括以下方面：

算术运算：加(+)、减(-)、乘(*)、除(/)、指数(**)。

关系运算：小于(<)、小于等于(<=)、大于(>)、大于等于(>=)、等于(=)、不等于(!=或<>)。

字符运算：连接(||)。

逻辑运算：与(AND)、或(OR)、非(NOT)。

运算符说明

IS NULL或IS NOT NULL用来判断运算对象的值是否为空，不能用“=”去判断。另外，对空值的运算也必须注意，对空值的算术和比较运算的结果都是空，但对空值可以进行连接运算，结果是另外一部分的字符串。例如：

NULL+5的结果为NULL。

NULL>5的结果为NULL。

NULL|| 'ABC' 的结果为'ABC'。

在PL/SQL中可以使用绝大部分Oracle函数，但是组函数(如AVG( )、MIN( )、MAX( )等)只能出现在SQL语句中，不能在其他语句中使用。还有GREATEST( )、LEAST( )也不能使用。类型转换在很多情况下是自动的，在不能进行自动类型转换的场合需要使用转换函数。

PL/SQL的基本构成

基本语法

数据类型

变量定义

结构控制语句

分支结构

if语句

case语句

循环

游标、异常

IF语句

分支结构是最基本的程序结构，分支结构由IF语句实现。

使用IF语句，根据条件可以改变程序的逻辑流程。IF语句有如下的形式:

IF 条件1 THEN

语句序列1；

[ELSIF 条件2 THEN

语句序列2；

ELSE

语句序列n；]

END IF;

declare

v_id varchar2(100);

begin

select studentid into v_id from student where name='mly';

if v_id='001' then

Dbms_Output.put_line(v_id);

end if ;

if v_id='001' then

Dbms_Output.put_line('v_id=001');

elsif v_id='002' then

Dbms_Output.put_line('v_id=002');

else

Dbms_Output.put_line('v_id is null');

end if ;

end;

IF语句说明

条件部分是一个逻辑表达式，值只能是真(TRUE)、假(FALSE)或空(NULL)。

语句序列为多条可执行的语句。

Oracle还提供了一种搜索CASE结构，它没有选择变量，直接判断条件表达式的值，根据条件表达式决定转向。

CASE

WHEN 条件表达式1 THEN

语句序列1

WHEN 条件表达式2 THEN

语句序列2

WHEN 条件表达式n THEN

语句序列n

ELSE

语句序列n+1

END CASE;

case

when v_id='001' then

Dbms_Output.put_line('v_id is 001');

when v_id='002' then

Dbms_Output.put_line('v_id is 002 ');

else

Dbms_Output.put_line('v_id is null');

end case ;

CASE语句

CASE语句适用于分情况的多分支处理，可有以下三种用法。

基本CASE结构

语句的语法如下：

CASE 选择变量名

WHEN 值1 THEN

语句序列1

WHEN 值n THEN

语句序列n

ELSE

语句序列n+1

END CASE;

在整个结构中，选择变量的值同表达式的值进行顺序匹配，如果相等，则执行相应的语句序列，如果不等，则执行ELSE部分的语句序列。

表达式结构CASE语句

在Oracle中，CASE结构还能以赋值表达式的形式出现，它根据选择变量的值求得不同的结果。它的基本结构如下：

变量:=CASE 选择变量名

WHEN 表达式1 THEN 值1

WHEN 表达式n THEN 值n

ELSE值n+1

END;

v_result:=CASE v_grade

WHEN 'A' THEN '优‘

WHEN 'D' THEN '差'

ELSE '未知'

END;

基本LOOP

基本循环的结构如下：

LOOP --循环起始标识

语句1;

语句2;

EXIT [WHEN 条件];

END LOOP; --循环结束标识

该循环的作用是反复执行LOOP与END LOOP之间的语句。

EXIT用于在循环过程中退出循环，WHEN用于定义EXIT的退出条件。如果没有WHEN条件，遇到EXIT语句则无条件退出循环。

写循环计算：sum=1+2+3+4+…10

declare

i integer := 1;

v_sum number := 0;

begin

loop

v_sum := v_sum+i;

exit when i = 10;

i:=i+1;

end loop;

Dbms_Output.put_line(v_sum);

end looppro;

While 循环

While 条件 loop

…..

End loop ;

v_step :=1 ;

v_result := 0 ;

while v_step<11 loop

v_result := v_result + v_step ;

v_step := v_step + 1 ;

end loop ;

FOR LOOP

FOR循环是固定次数循环，格式如下：

FOR 控制变量 in [REVERSE] 下限..上限

LOOP

语句1;

语句2;

END LOOP;

循环控制变量是隐含定义的，不需要声明。

下限和上限用于指明循环次数。正常情况下循环控制变量的取值由下限到上限递增，REVERSE关键字表示循环控制变量的取值由上限到下限递减。

写循环计算：sum=1+2+3+4+…10

declare

j integer := 1;

v_sum number := 0;

begin

for j in 1..10 loop

v_sum := v_sum +j;

end loop ;

Dbms_Output.put_line(v_sum);

end loop;

游标是SQL的一个内存工作区，由系统或用户以变量的形式定义。

游标的作用就是用于临时存储从数据库中提取的数据块。在某些情况下，需要把数据从存放在磁盘的表中调到计算机内存中进行处理，最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高，否则频繁的磁盘数据交换会降低效率。

游标有两种类型：显式游标和隐式游标。

在前述程序中用到的SELECT...INTO...查询语句，一次只能从数据库中提取一行数据，对于这种形式的查询和DML操作，系统都会使用一个隐式游标。

但是如果要提取多行数据，就要由程序员定义一个显式游标，并通过与游标有关的语句进行处理。显式游标对应一个返回结果为多行多列的SELECT语句。

游标一旦打开，数据就从数据库中传送到游标变量中，然后应用程序再从游标变量中分解出需要的数据，并进行处理。

隐式游标

如前所述，DML操作和单行SELECT语句会使用隐式游标，它们是：

插入操作：INSERT。

更新操作：UPDATE。

删除操作：DELETE。

单行查询操作：SELECT ... INTO ...。

当系统使用一个隐式游标时，可以通过隐式游标的属性来了解操作的状态和结果，进而控制程序的流程。

隐式游标可以使用名字SQL来访问，但要注意，通过SQL游标名总是只能访问前一个DML操作或单行SELECT操作的游标属性。所以通常在刚刚执行完操作之后，立即使用SQL游标名来访问属性。游标的属性有四种，如表7-1所示。

隐式游标属性

隐式游标－例子

使用隐式游标的属性，判断对雇员工资的修改是否成功。

SET SERVEROUTPUT ON

BEGIN

UPDATE emp SET sal=sal+100 WHERE empno=1234;

IF SQL%FOUND THEN

DBMS_OUTPUT.PUT_LINE('成功修改雇员工资！');

COMMIT;

ELSE

DBMS_OUTPUT.PUT_LINE('修改雇员工资失败！');

END IF;

END;

显式游标

游标的使用分成以下4个步骤。

1．声明游标

在DECLARE部分按以下格式声明游标：

CURSOR 游标名 [(参数1 数据类型[，参数2 数据类型...])]

IS SELECT语句;

参数是可选部分，所定义的参数可以出现在SELECT语句的WHERE子句中。如果定义了参数，则必须在打开游标时传递相应的实际参数。

SELECT语句是对表或视图的查询语句，甚至也可以是联合查询。可以带WHERE条件、ORDER BY或GROUP BY等子句，但不能使用INTO子句。在SELECT语句中可以使用在定义游标之前定义的变量。

显式游标

2．打开游标

在可执行部分，按以下格式打开游标：OPEN 游标名[(实际参数1[，实际参数2...])];

打开游标时，SELECT语句的查询结果就被传送到了游标工作区。

3．提取数据

在可执行部分，按以下格式将游标工作区中的数据取到变量中。提取操作必须在打开游标之后进行。

FETCH 游标名 INTO 变量名1[，变量名2...];或 FETCH 游标名 INTO 记录变量;

游标打开后有一个指针指向数据区，FETCH语句一次返回指针所指的一行数据，要返回多行需重复执行，可以使用循环语句来实现。控制循环可以通过判断游标的属性来进行。

游标

获取数据格式：

第一种格式中的变量名是用来从游标中接收数据的变量，需要事先定义。变量的个数和类型应与SELECT语句中的字段变量的个数和类型一致。

第二种格式一次将一行数据取到记录变量中，需要使用%ROWTYPE事先定义记录变量，这种形式使用起来比较方便，不必分别定义和使用多个变量。

定义记录变量的方法如下：

其中的表必须存在，游标名也必须先定义。

变量名表名|游标名%ROWTYPE；

4．关闭游标 CLOSE 游标名;

显式游标打开后，必须显式地关闭。游标一旦关闭，游标占用的资源就被释放，游标变成无效，必须重新打开才能使用。

例子1

【1】用游标提取emp表中7788雇员的名称和职务。

SET SERVEROUTPUT ON

DECLARE

v_ename VARCHAR2(10);

v_job VARCHAR2(10);

CURSOR emp_cursor IS

SELECT ename,job FROM emp WHERE empno=7788;

BEGIN

OPEN emp_cursor;

FETCH emp_cursor INTO v_ename,v_job;

DBMS_OUTPUT.PUT_LINE(v_ename||','||v_job);

CLOSE emp_cursor;

END;

【训练2】用游标提取emp表中7788雇员的姓名、职务和工资。

SET SERVEROUTPUT ON

DECLARE

CURSOR emp_cursor IS SELECT ename,job,sal FROM emp WHERE empno=7788;

emp_record emp_cursor%ROWTYPE;

BEGIN

OPEN emp_cursor;

FETCH emp_cursor INTO emp_record;

DBMS_OUTPUT.PUT_LINE(emp_record.ename||','||emp_record.job||','|| emp_record.sal);

CLOSE emp_cursor;

END;

例子3

【训练3】显示工资最高的前3名雇员的名称和工资。

SET SERVEROUTPUT ON

DECLARE

V_ename VARCHAR2(10);

V_sal NUMBER(5);

CURSOR emp_cursor IS SELECT ename,sal FROM emp ORDER BY sal DESC;

BEGIN

OPEN emp_cursor;

FOR I IN 1..3 LOOP

FETCH emp_cursor INTO v_ename,v_sal;

DBMS_OUTPUT.PUT_LINE(v_ename||','||v_sal);

END LOOP;

CLOSE emp_cursor;

END;

游标循环

【训练1】使用特殊的FOR循环形式显示全部雇员的编号和名称。

SET SERVEROUTPUT ON

DECLARE

CURSOR emp_cursor IS

SELECT empno, ename FROM emp;

BEGIN

FOR Emp_record IN emp_cursor LOOP

DBMS_OUTPUT.PUT_LINE(Emp_record.empno|Emp_record.ename);

END LOOP;

END;

说明：可以看到该循环形式非常简单，隐含了记录变量的定义、游标的打开、提取和关闭过程。Emp_record为隐含定义的记录变量，循环的执行次数与游标取得的数据的行数相一致。

不用打开游标

游标

【训练2】另一种形式的游标循环。

SET SERVEROUTPUT ON

BEGIN

FOR re IN (SELECT ename FROM EMP) LOOP

DBMS_OUTPUT.PUT_LINE(re.ename)

END LOOP;

END;

说明：该种形式更为简单，省略了游标的定义，游标的SELECT查询语句在循环中直接出现。

显式游标属性

游标名%属性

要判断游标emp_cursor是否处于打开状态，可以使用属性emp_cursor%ISOPEN。如果游标已经打开，则返回值为“真”，否则为“假”。具体可参照以下的训练。

游标参数的传递

SET SERVEROUTPUT ON

DECLARE

V_empno NUMBER(5);

V_ename VARCHAR2(10);

CURSOR emp_cursor(p_deptno NUMBER, p_job VARCHAR2) IS SELECT empno, ename FROM emp WHERE deptno = p_deptno AND job = p_job;

BEGIN

OPEN emp_cursor(10, 'CLERK')

最低0.47元/天解锁文章

200万优质内容无限畅学