Oracle知识

最新推荐文章于 2024-08-28 13:56:44 发布

Jinken博客-编程

最新推荐文章于 2024-08-28 13:56:44 发布

阅读量1k

点赞数

分类专栏： oracle 文章标签： oracle 数据库 plsql

本文链接：https://blog.csdn.net/Jacabe/article/details/32321609

版权

oracle 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

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');

LOOP

FETCH emp_cursor INTO v_empno,v_ename;

EXIT WHEN emp_cursor%NOTFOUND;

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

END LOOP;

END;

异常处理

错误处理部分位于程序的可执行部分之后，是由WHEN语句引导的多个分支构成的。错误处理的语法如下：

EXCEPTION

WHEN 错误1[OR 错误2] THEN

语句序列1；…

WHEN OTHERS THEN

语句序列n；

END;

错误是在标准包中由系统预定义的标准错误，或是由用户在程序的说明部分自定义的错误，参见下一节系统预定义的错误类型。

语句序列就是不同分支的错误处理部分。

说明

凡是出现在WHEN后面的错误都是可以捕捉到的错误，其他未被捕捉到的错误，将在WHEN OTHERS部分进行统一处理，OTHERS必须是EXCEPTION部分的最后一个错误处理分支。如要在该分支中进一步判断错误种类，可以通过使用预定义函数SQLCODE( )和SQLERRM( )来获得系统错误号和错误信息。

如果在程序的子块中发生了错误，但子块没有错误处理部分，则错误会传递到主程序中。

下面是由于查询编号错误而引起系统预定义异常的例子。

SQLCODE(),SQLERRM()

常用exception

Too_many_rows 行过多

No_data_found 数据为空

Case_not_found 建case时无分支语句

Cursor_already_open 重新打开已打开的游标

Dup_val_on_index 主键冲突

Invalid_cursor 要对未打开或已关闭的游标读取数据

Zero_divide 除0异常

系统预定义异常

PL/SQL集合

索引表

嵌套表

变长数组

记录表

当执行DML语句时，Oracle会在作用表上加表锁，以防止用户改变结构；同时会在被作用行上加行锁，防止其他DML在相应行上执行DML语句。

在Oracle数据库中为了确保数据的一致性，不允许其他用户读脏数据（未提交的数据）

读“脏”数据的例子：

事务1修改某一数据，并将其写回磁盘

事务2读取同一数据后

事务1由于某种原因被撤消，这时事务1已修改过的数据恢复原值

事务2读到的数据就与数据库中的数据不一致，

是不正确的数据，又称为“脏”数据。

使用commit可以提交事务，执行commit后会确认事务变化，结束事务，删除保存点，释放锁。其他事务也能看到事务变化后的数据。

--设置串型化事务管理：两个事务同时操作同一数据，同一条记录时，数据库里的数据可能会乱，这时数据库不知道先执行那条，故加串型化事务管理，可以让一方，让先执行的语句提交，执行完后，再让第二方执行

设置串型化事务管理，使先操作的语句，加了行锁，只有等当前操作完，另一个用户的语句才可以执行提交

--会话1（--》SQL界面1）

set transaction isolation level serializable;

update tuser set ausername=ausername||1 where aid=1;

--会话2（--》SQL界面2）

select * from tuser for update nowait;

当用户1在执行，没提交时，用户2加了nowait时，就会提示资源忙，当不加nowait时就会一直等待，等待用户1的语句执行完

在创建表之后，创建表的嵌套表

create type phone_table_type is table of varchar2(100);

declare

phone_tables phone_table_type := phone_table_type('1303243434',

'13234343434');

begin

update tuser set phone = phone_tables where id = 20;

commit;

end;

回退事务

保存点是事务中的一点，用于取消部分事务，当结束事务时会删除所定义的保存点。执行rollback时，可以根据保存点回退部分事务。

Rollback to A 回退到保存点A

Rollback取消全部事务

declare

Begin

insert into student values(6,'zhangsan',20);

savepoint a1 ;

insert into student values(7,'lisi',21);

savepoint a2 ;

insert into student values(6,'wangwu',22);

commit ;

exception

when dup_val_on_index then

rollback to a1;

commit;

end;

当rollback to a1;只增加了6,'zhangsan',20一条数据

当rollback to a1;增加了6,'zhangsan',20和7,'lisi',21

只读事务是只允许读操作，不允许其他DML操作。当使用只读事务时可以确保用户取得特定时间点的数据。假定要在16点统计最近24小时的销售记录，当设置了只读事务后，尽管其他回话会提交新事务，但只读事务不会取得新数据。

set transaction read write

select * from tuser;

set transaction read only;

insert into tuser values (23, 'zhangsan', '12345');

select * from tuser;

语法set transaction read {only|write}

事务只读事务中,再进行DML就会出错.

在cmd中运行：

先输入运行

set serveroutput on，

再运行调用的存储过程或函数的语句

select * from student for update ;查询出来然后再修改表里面的数据

静态SQL和动态SQL的区别和比较

静态SQL是在编写PL/SQL块时直接嵌入的SQL语句。动态SQL是在运行PL/SQL块时动态输入的SQL语句。

静态SQL性能要优于动态SQL。如果功能确定应该适用静态SQL。

--动态sql

declare

d_sql varchar2(100) := '';

begin

d_sql := 'drop table tuser1';

execute immediate d_sql;

end;

--动态sql采用变量

declare

d_sql varchar2(100) := '';

begin

d_sql := 'update tuser set username=:y where id=:x';

execute immediate d_sql using 'zhangsan',4;

end;

--动态sql单行查询

declare

d_sql varchar2(100) := '';

--定义记录类型

v_tuser tuser%rowtype;

begin

--编写动态sql

d_sql := 'select * from tuser where id=:x';

--执行动态sql

execute immediate d_sql into v_tuser using 4;

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

end;

处理包含占位符的语句

declare

v_presql varchar2(100) ;

begin

v_presql := 'update student set name=:x where id=100 ' ;

execute immediate v_presql using 'lisi‘;

commit ;

end;

Java调用存储过程

加载ojdbc14.jar

Driver:oracle.jdbc.driver.OracleDriver

Connectionurl:jdbc:oracle:thin:@localhost:1521:orcl

Java调用存储过程和function.

Truncate数据被删除后，高水平线（high-water mark，HWM）并没有复位只是那些空间不再使用而已，再次查询依然会读取HWH以前的块查找是否有可用的数据。而截断表将复位HWH，告诉这些空间没有保存数据。

一是truncate速度快，无法回滚，因为truncate不是dml语句；二是truncate能降低HWM，而delete 无法降低HWM，因此无法表在用delete删除，表的大小没有改变！

merge 语法--把tuser1 里面的数据加到tuser里面，并替换掉

merge into tuser t

using tuser1 t1

on (t.id = t1.id)

when matched then

update set t.username = t1.username, t.password = t1.password

when not matched then

insert values (t1.id, t1.username, t1.password)

复合数据类型

PL/SQL记录　

PL/SQL集合

PL/SQL记录

它由一组相关的成员变量组成

有两种定义方式：

(1) type student_record is record (

stuid number(4),

name varchar2(20),

sex number(1) ,

age number(3)

);

(2) emp_record emp%rowtype;

见例子:record_define.txt

可以通过select into 语句将单行记录写入PL/SQL记录。

例:

v_emp_rec emp%rowtype ;

select * into v_emp_rec from emp where empno=7369;

在insert语句中使用insert into emp values emp_record;

例:

v_emp_rec.empno := 7340;

insert into emp values v_emp_rec ;

--定义记录类型

declare

--定义记录类型

type tuser_record_type is record(

v_id tuser.id%type,

v_username tuser.username%type,

v_password tuser.password%type);

--定义记录类型变量

tuser_record tuser_record_type;

begin

select * into tuser_record from tuser where id = 20;

dbms_output.put_line(tuser_record.v_id || ' ' ||

tuser_record.v_username || ' ' ||

tuser_record.v_password);

end;

--2

declare

--定义记录类型变量

tuser_record tuser%rowtype;

begin

select * into tuser_record from tuser where id = 20;

dbms_output.put_line(tuser_record.id || ' ' || tuser_record.username ||

' ' || tuser_record.password);

end;

select * from tuser;

PL/SQL集合：

索引表(PL/SQL表)

它是Oracle早期版本中用于处理PL/SQL数组的数据类型.

索引表的下标可以为负值,而且其元素个数没有限制.

declare

--数组下标索引

i binary_integer := -1 ;

--定义索引表数组类型

type ename_table_type is table of emp.ename%type index by binary_integer ;

--创建数组变量

v_ename_table ename_table_type ;

cursor v_emp_cur is select * from emp where deptno=20 ;

begin

--给数组变量赋值

for v_record in v_emp_cur loop

v_ename_table(i) := v_record.ename ;

i := i+1 ;

end loop;

--判断索引表里是否有元素

if(v_ename_table.count>0) then

--输出数组里的值

for j in v_ename_table.first..v_ename_table.last loop

dbms_output.put_line(v_ename_table(j));

end loop ;

end if ;

end;

--索引表

declare

--定义索引表类型

type username_sy_type is table of varchar2(100) index by binary_integer;

--定义索引表变量

username_sy username_sy_type;

--建立索引表下标--下标可以是负整数，0，正整数

i binary_integer := -1;

--定义游标获取tuser表数据

cursor c_tuser is

select username from tuser;

begin

--循环游标将username放入索引表

open c_tuser;

loop

--将值放入索引表

fetch c_tuser

into username_sy(i);

i := i + 1;

--退出循环

exit when c_tuser%notfound;

end loop;

--将数据从索引表中取出并打印

for j in username_sy.first .. username_sy.last loop

dbms_output.put_line(username_sy(j));

end loop;

end;

--嵌套表

数组下标从1开始，长度不限.

嵌套表需要先进行初始化才能使用.

可以做为表列的数据类型使用.

Type ename_table_type is table of varchar(100);

Ename_table ename_table_type ;

--构造方法,初始情况下有两个元素.

Ename_table :＝ ename_table_type （’A’,’A’）;

--构造方法,初始情况下没有元素.

ename_table_type（）;

--分配一个空间

Ename_table.extend ;

Select name into ename_table(2) from emp where ….

declare

--定义嵌套表类型

type password_qt_type is table of varchar2(100);

--定义嵌套表变量并调用构造函数初始化

password_qt password_qt_type := password_qt_type();

i binary_integer := 1;--嵌套表类型的下标从1开始

begin

--循环遍历游标

for u in (select password from tuser) loop

--给嵌套表开辟新的长度

password_qt.extend;

--给嵌套表赋值

password_qt(i) := u.password;

i := i + 1;

end loop;

--变量嵌套表并打印数据

for j in 1 .. password_qt.count loop

dbms_output.put_line(password_qt(j));

end loop;

end;

在pl/sql块中使用嵌套表：

declare

--定义嵌套表数据类型

type ename_table_type is table of emp.ename%type;

--定义嵌套表类型的变量,并调用构造方法进行初始化.

ename_table ename_table_type := ename_table_type();

cursor v_emp_cur is select ename from emp where deptno =20 ;

--定义嵌套表的开始下标

i binary_integer :=1 ;

begin

for v_record in v_emp_cur loop

--给ename_table变量分配空间

ename_table.extend;

--给ename_table变量赋值

ename_table(i) :=v_record.ename ;

i := i+1 ;

end loop ;

--输出嵌套表变量ename_table里的所有值

for j in 1..ename_table.count loop

dbms_output.put_line( ename_table(j) );

end loop ;

end;

nested table PHONE store as my_phone:设置表的列为嵌套表类型

nested table p store as <nested_table_name>：

<nested_table_name>：设置嵌套表的名字，可以任意取

在创建表时，创建表的嵌套表

注:在表列中使用嵌套表,需要先使用create type 命令先创建嵌套表类型.

create type phone_table_type is table of varchar2(20);

--新建

create table student

(

stuid number(5) ,

name varchar2(20),

phone phone_table_type

) nested table phone store as phone_table ;

public class Function {

public static void main(String[] args) throws SQLException {

Connection connection = Conn.getconn();

// 存储过程调用语句

String sql = "{?=call decfun(?,?)}";

// 获取存储过程调用对象

CallableStatement call = connection.prepareCall(sql);

// 设置输入参数

call.setInt(2, 10);

call.setInt(3, 2);

// 设置输出参数

call.registerOutParameter(1, OracleTypes.INTEGER);

// 执行函数

call.executeUpdate();

// 获取返回值

int rs = call.getInt(1);

System.out.println(rs);

connection.close();

}

public static void main(String[] args) throws SQLException {

Connection connection = Conn.getconn();

// 存储过程调用语句

String sql = "{call adderpro(?,?,?)}";

// 获取存储过程调用对象

CallableStatement call = connection.prepareCall(sql);

// 设置输入参数

call.setInt(1, 10);

call.setInt(2, 20);

// 设置输出参数

call.registerOutParameter(3, OracleTypes.INTEGER);

// 执行存储过程

call.executeUpdate();

// 获取存储过程返回值

int rs = call.getInt(3);

System.out.println(rs);

connection.commit();

connection.close();

}

declare

v_phone_table phone_table_type ;

begin

--在PL/SQL块中为nested table 插入数据

insert into student values(1,'lisi',phone_table_type('138111111','139111111'));

--在PL/SQL块中检索nested table 列的数据

select phone into v_phone_table from student where stuid=1 ;

for i in 1..v_phone_table.count loop

dbms_output.put_line('phone:'||v_phone_table(i) );

end loop;

变长数组(Varray)

变长数组需要先进行初始化才能使用.

可以做为表列的数据类型使用.

数组下标从1开始,长度有限.

declare

--定义变长数组的类型名称,及最大长度

type ename_varray_type is varray(10) of varchar2(20);

--定义变长数组变量,并进行初始化

v_ename_varray ename_varray_type := ename_varray_type() ;

cursor v_emp_cur is select * from emp where deptno=20 ;

i binary_integer := 1 ;

begin

for v_record in v_emp_cur loop

--给变长数组分配空间

v_ename_varray.extend ;

--给变长数组赋值

v_ename_varray(i) :=v_record.ename ;

i := i + 1 ;

end loop;

--输出变长数组里的元素

for i in 1..v_ename_varray.count loop

dbms_output.put_line(v_ename_varray(i));

end loop;

end;

在表列中使用变长数组：

create type phone_arr_type is varray(5) of varchar2(20) ;

create table student

(

stuid number(4),

name varchar2(20),

phone phone_arr_type

);

//其它用法和嵌套表一样

--记录表

declare

--定义记录表类型

type tuser_jl_type is table of tuser%rowtype index by binary_integer;

--定义记录表变量

tuser_jl tuser_jl_type;

--自增变量

i binary_integer := 1;

begin

--循环遍历游标

for u in (select * from tuser) loop

--给记录表赋值

tuser_jl(i) := u;

i := i + 1;

end loop;

--变量嵌套表并打印数据

for j in tuser_jl.first .. tuser_jl.last loop

dbms_output.put_line(tuser_jl(j).id || ' ' || tuser_jl(j).username);

end loop;

end;

为了处理多行多列数据，可以使用记录表.

declare

--定义记录表类型

type emp_table_type is table of emp%rowtype index by binary_integer ;

--定义记录表变量

v_emp_table emp_table_type ;

cursor v_emp_cur is select * from emp where deptno=20 ;

--定义记录表下标索引

i binary_integer := -1 ;

begin

--给记录表变量赋值

for v_record in v_emp_cur loop

v_emp_table(i) := v_record ;

i := i + 1 ;

end loop;

--输出记录表变量的值

for j in v_emp_table.first..v_emp_table.last loop

dbms_output.put_line(v_emp_table(j).ename);

end loop;

end;

批量绑定

是指执行单次SQL操作能传递所有集合元素的数据.

当在select,insert,update,delete 语句上处理批量数据时,通过批量绑定,可以极大加快数据处理速度.

Forall语句和bulk collect可以用来做批量绑定

Forall一般用于insert ,update ,delete.

Insert into student1 select * from student2;执行的是批量操作。

批量修改：

declare

type id_table_type is table of number(6) index by binary_integer ;

type name_table_type is table of varchar(20) index by binary_integer ;

v_id_table id_table_type;

v_name_table name_table_type;

begin

for i in 1..5000 loop

v_id_table(i) := i ;

v_name_table(i) := 'name2' || to_char(i) ;

end loop ;

forall i in 1..5000

update student set name = v_name_table(i) where id = v_id_table(i) ;

commit ;

dbms_output.put_line('end');

end;

Bulk collect 一般用于select into .

Bulk collect子句用与取得批量数据，适用于select into语句,fetch into 语句,DML返回子句，通过使用该子句，可以将批量数据存放到pl/sql集合变量中。

在select into语句中用bulk collect 子句：

declare

type student_table_type is table of student.name%type index by binary_integer;

v_student_table student_table_type ;

begin

select name bulk collect into v_student_table from student ;

for i in 1..50 loop

dbms_output.put_line(v_student_table(i));

end loop ;

end;

在fetch into语句中用bulk collect 子句

declare

type student_table_type is table of student%rowtype index by binary_integer;

v_student_table student_table_type ;

cursor v_studentcursor is select * from student ;

begin

open v_studentcursor ;

fetch v_studentcursor bulk collect into v_student_table ;

close v_studentcursor ;

dbms_output.put_line(v_student_table.count);

end;

在DML返回子句中用bulk collect 子句：

declare

type student_table_type is table of student%rowtype index by binary_integer;

v_student_table student_table_type ;

begin

delete from student where age<11 returning id,name ,age bulk collect into v_student_table ;

for i in 1..v_student_table.count loop

dbms_output.put_line(v_student_table(i).name);

end loop ;

commit;

end;

Oracle-SQL-group by-报错：不是单组分组函数

分类：数据库技术 2010-03-16 19:469338人阅读评论(0)收藏举报

报错：不是单组分组函数

实例：select deptno,count(empno) from emp;

报错：不是单组分组函数

原因：

1，如果程序中使用了分组函数，则有两种情况可以使用：

程序中存在group by，并指定了分组条件，这样可以将分组条件一起查询出来

改为：select deptno,count(empno) from emp group by deptno;

如果不使用分组的活，则只能单独使用分组函数

改为：select count(empno) from emp;

2，在使用分组函数的时候，不能出现分组条件之外的字段

总结：在select需要查询的语句中选中的字段，必须出现在group by子句中，除了分组函数的参数。

Group by

如果在查询的过程中需要按某一列的值进行分组,以统计该组内数据的信息时,就要使用group by子句。不管select是否使用了where子句都可以使用group by子句。

注意：group by子句一定要与分组函数结合使用,否则没有意义。

//求出每个部门的员工人数

SQL> select deptno,count(*) as "人数" from emp group by deptno;

//求出每个部门的员工的平均工资

SQL> select deptno,avg(nvl(sal,0)) from emp group by deptno;

//注意：group by 子句中的列不必包含在SELECT 列表中

SQL> select avg(nvl(sal,0)) from emp group by deptno;

//求出某个部门中相同职位的员工人数 group by 后可以跟多个分组的字段

SQL> select deptno,job,count(*) from emp group by deptno,job order by deptno;

Having 子句

HAVING 子句对 GROUP BY 子句设置条件的方式与 WHERE 子句和 SELECT 语句交互的方式类似。WHERE 子句搜索条件在进行分组操作之前应用；而 HAVING 搜索条件在进行分组操作之后应用。HAVING 语法与 WHERE 语法类似，但 HAVING 可以包含聚合函数。HAVING 子句可以引用选择列表中出现的任意项。

备注：having子句通常与group by子句结合使用

语法：

SELECT column, group_function

FROM table

[WHERE condition]

[GROUP BY group_by_expression]

[HAVING group_condition]

[ORDER BY column];

//查询部门的员工人数大于五部门编号

SQL> select deptno,count(*) from emp group by deptno having count(*)>5;

select pid,

sum(case when type=0 then 1 else 0 end) type_0,

sum(case when type=1 then 1 else 0 end) type_1,

sum(case when type=2 then 1 else 0 end) type_2

from A

group by pid

//求出某个部门中相同职位的员工人数 group by 后可以跟多个分组的字段
SQL> select deptno,job,count(*) from emp group by deptno,job order by deptno;

非法使用组函数
1、所用包含于SELECT 列表中，而未包含于组函数中的列都必须包含于 GROUP BY 子句中。
举例：
SQL> select empno,count(job) from emp;
正确写法如下：
SQL> select empno,count(job) from emp group by empno;
2、不能在 WHERE 子句中使用组函数（注意）。
SQL> select deptno from emp where count(job)>0 group by deptno;
备注：ORA-00934: 此处不允许使用分组函数
Having 子句
HAVING 子句对 GROUP BY 子句设置条件的方式与 WHERE 子句和 SELECT 语句交互的方式类似。WHERE 子句搜索条件在进行分组操作之前应用；而 HAVING 搜索条件在进行分组操作之后应用。HAVING 语法与 WHERE 语法类似，但 HAVING 可以包含聚合函数。HAVING 子句可以引用选择列表中出现的任意项。
备注：having子句通常与group by子句结合使用

select m.mat_collegeid from mat m where mat_flag = 1 group by m.mat_collegeid:可以过掉重复的id,

如果想删掉相同的id值，可以select t.*,rowid from student t

根据rowid的不同来删除，可以拿到max(rowid) 来删掉其他的

比较运算符
>,<:大于，小于
>=.<=:大于等于，小于等于
=:等于
!=,<>,^=:不等于

逻辑运算符运算的优先顺序是NOT，AND，OR。如果要改变优先顺序，可以使用括号

逻辑运算符
AND:逻辑与，表示两个条件必须同时满足
OR:逻辑或，表示两个条件中有一个条件满足即可
NOT:逻辑非，返回与某条件相反的结果

特殊运算符
[NOT] BETWEEN…AND…:用于测试是否在范围内
[NOT] IN (…):用于测试是否在列表中
[NOT] LIKE:用于进行模式匹配
IS [NOT] NULL:用于测试是否为空值
ANY SOME:同列表或查询中的每一个值进行比较，测试是否有一个满足，前面必须使用的运算符包括=、!=、>=、<=、>、<等
ALL:同列表或查询中的每一个值进行比较，测试是否所有的值都满足，前面必须使用的运算符包括=、!=、>=、<=、>、<等
[NOT] EXISTS:测试是否子查询至少返回一行

IN的用法
使用以下运算形式，可以显示值满足特定集合的结果：
[NOT] IN (...)
显示职务为“SALESMAN'，“CLERK”和“MANAGER”的雇员信息。
输入并执行查询： Sql代码

1 SELECT * FROM emp WHERE job IN ('SALESMAN','CLERK','MANAGER');

执行结果从略。
注意：如果在IN前面增加NOT，将显示职务不在集合列表中的雇员。以上用法同样适用于数值型集合，

多行子查询
如果子查询返回多行的结果，则我们称它为多行子查询。多行子查询要使用不同的比较运算符号，它们是IN、ANY和ALL。
查询工资低于任何一个“CLERK”的工资的雇员信息。
执行以下查询： Sql代码

2 SELECT empno, ename, job,sal FROM emp

3 WHERE sal < ANY (SELECT sal FROM emp WHERE job = 'CLERK')

4 AND job <> 'CLERK';

执行结果为：

Sql代码

5 EMPNO ENAME JOB SAL

6 ------------------- ------------ ----------------- ---------------------

7 7521 WARD SALESMAN 1250

8 7654 MARTIN SALESMAN 1250

说明：在emp表的雇员中有4个职务为“CLERK”，他们的工资分别是800、1100、950、1300。满足工资小于任何一个“CLERK”的工资的记录有2个，在这里使用了ANY运算符表示小于子查询中的任何一个工资。
注意：条件job <> 'CLERK'排除了职务是CLERK的雇员本身。
查询工资比所有的“SALESMAN”都高的雇员的编号、名字和工资。
执行以下查询： Sql代码

9 SELECT empno, ename,sal FROM emp

10 WHERE sal > ALL(SELECT sal FROM emp WHERE job= 'SALESMAN');

执行结果为：

Sql代码

11 EMPNO ENAME SAL

12 ---------------- ------------- -----------------------

13 7566 JONES 2975

14 7698 BLAKE 2850

15 7782 CLARK 2450

16 7788 SCOTT 3000

17 7839 KING 5000

18 7902 FORD 3000

说明：在emp表的雇员中有4个职务为“SALESMAN”，他们的工资分别是1600、1250、1250、1500。在这里使用了ALL运算符，表示大于查询中所有的工资。
查询部门20中职务同部门10的雇员一样的雇员信息。
执行以下查询：

Sql代码

19 SELECT empno, ename, job FROM emp

20 WHERE job IN (SELECT job FROM emp WHERE deptno=10)

21 AND deptno =20;

执行结果为：

Sql代码

22 EMPNO ENAME JOB

23 ------------------ -------------- ----------------------

24 7369 SMITH CLERK

25 7876 ADAMS CLERK

26 7566 JONES MANAGER

说明：在该训练中，使用IN运算符表示职务是子查询结果中的任何一个。部门10中有3种职务：MANAGER、PRESIDENT和CLERK，以上查询得到的是部门20中是这3种职务的雇员。

多列子查询
如果子查询返回多列，则对应的比较条件中也应该出现多列，这种查询称为多列子查询。以下是多列子查询的训练实例。
查询职务和部门与SCOTT相同的雇员的信息。
执行以下查询： Sql代码

27 SELECT empno, ename, sal FROM emp

28 WHERE (job,deptno) =(SELECT job,deptno FROM emp WHERE empno=7788);

执行结果为：

Sql代码

29 EMPNO ENAME JOB

30 ------------------ --------------- ----------------

31 7902 FORD ANALYST

说明：在该例的子查询中返回两列，查询条件中也要出现两列，表示雇员的职务和部门应该和SCOTT的职务和部门相同。

LIKE的用法
使用LIKE操作符可完成按通配符查找字符串的查询操作，该操作符适合于对数据进行模糊查询。其语句法为：
[NOT] LIKE 匹配模式
匹配模式中除了可以包含固定的字符之外，还可以包含以下的通配符：
%：代表0个或多个任意字符。
_ ：代表一个任意字符。
显示姓名以“S”开头的雇员信息。
输入并执行查询： Sql代码

32 SELECT * FROM emp WHERE ename LIKE 'S%';

执行结果为： Sql代码

33 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

34 ------------- -------------- ------------------------- ---------- ------------------ ----------- ------------ ------------------

35 7369 SMITH CLERK 7902 17-12月-80 800 20

36 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20

说明：SMITH和SCOTT名字均以S开头，名字后边的字符和长度任意。
显示姓名第二个字符为“A”的雇员信息。
执行查询： Sql代码

37 SELECT * FROM emp WHERE ename LIKE '_A%';

说明：“_”代表第一个字符任意，第二个字符必须为“A”，“%”代表第二个字符后面的字符为任意字符，个数任意。

判断空值NULL
在表中，字段值可以是空，表示该字段没有内容。如果不填写，或设置为空则我们说该字段的内容为NULL。NULL没有数据类型，也没有具体的值，但是使用特定运算可以判断出来。这个运算就是：
IS [NOT] NULL
显示经理编号没有填写的雇员。
输入并执行查询： Sql代码

38 SELECT ename, mgr FROM emp WHERE mgr IS NULL;

执行结果为： Sql代码

39 ENAME MGR

40 SELECT * FROM emp WHERE job IN ('SALESMAN','CLERK','MANAGER');

41 SELECT empno, ename, job,sal FROM emp

42 WHERE sal < ANY (SELECT sal FROM emp WHERE job = 'CLERK')

43 AND job <> 'CLERK';

执行结果为：

Sql代码

44 EMPNO ENAME JOB SAL

45 ------------------- ------------ ----------------- ---------------------

46 7521 WARD SALESMAN 1250

47 7654 MARTIN SALESMAN 1250

48 SELECT empno, ename,sal FROM emp

49 WHERE sal > ALL(SELECT sal FROM emp WHERE job= 'SALESMAN');

执行结果为：

Sql代码

50 EMPNO ENAME SAL

51 ---------------- ------------- -----------------------

52 7566 JONES 2975

53 7698 BLAKE 2850

54 7782 CLARK 2450

55 7788 SCOTT 3000

56 7839 KING 5000

57 7902 FORD 3000

Sql代码

58 SELECT empno, ename, job FROM emp

59 WHERE job IN (SELECT job FROM emp WHERE deptno=10)

60 AND deptno =20;

执行结果为：

Sql代码

61 EMPNO ENAME JOB

62 ------------------ -------------- ----------------------

63 7369 SMITH CLERK

64 7876 ADAMS CLERK

65 7566 JONES MANAGER

66 SELECT empno, ename, sal FROM emp

67 WHERE (job,deptno) =(SELECT job,deptno FROM emp WHERE empno=7788);

执行结果为：

Sql代码

68 EMPNO ENAME JOB

69 ------------------ --------------- ----------------

70 7902 FORD ANALYST

71 SELECT * FROM emp WHERE ename LIKE 'S%';

执行结果为： Sql代码

72 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

73 ------------- -------------- ------------------------- ---------- ------------------ ----------- ------------ ------------------

74 7369 SMITH CLERK 7902 17-12月-80 800 20

75 7788 SCOTT ANALYST 7566 19-4月 -87 3000 20

说明：SMITH和SCOTT名字均以S开头，名字后边的字符和长度任意。
显示姓名第二个字符为“A”的雇员信息。
执行查询： Sql代码

76 SELECT * FROM emp WHERE ename LIKE '_A%';

77 SELECT ename, mgr FROM emp WHERE mgr IS NULL;

执行结果为： Sql代码

78 ENAME MGR

79 ------------ ---------------

80 KING

81
注意：以下用法是错误的。
SELECT ename, mgr FROM emp WHERE mgr=NULL; ------------ ---------------

82 KING

注意：以下用法是错误的。
SELECT ename, mgr FROM emp WHERE mgr=NULL;

Jinken博客-编程

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Oracle知识

Number(38,0)类型:38表示总长度，0表示小数的长度，为0，如果为2，就表示后面有2位小数存储过程无参的话，不加（），存储过程一般用在数据库的更新上用sequence实现自增长-- 创建create sequence tt minvalue 1 maxvalue 999999 nocycle --不循环
复制链接

扫一扫

专栏目录