12、PL/SQL

最新推荐文章于 2023-12-27 09:26:50 发布

陈同学专辑

最新推荐文章于 2023-12-27 09:26:50 发布

阅读量666

点赞数

本文链接：https://blog.csdn.net/qq_35234765/article/details/84970515

版权

PL/SQL

1. PL/SQL简介

1、PL/SQL简介：

（1）Procedural Language和SQL的结合体。通过增加变量、控制语句，使我们可以写一些逻辑更加复杂的数据库操作。

（2）PL/SQL是 Procedure Language & Structured Query Language 的缩写。叫存储过程。

2. 匿名块

1、匿名块由以下四部分组成：

(1)declare – 可选

声明各种变量或游标的地方。

(2)begin – 必要

开始执行语句。

--单行注释语句用两个连在一起的‘-’表示。

/*多行注释语句，

可以换行*/

(3)exception – 可选

出错后的处理。

(4)end; – 必要（请注意end后面的分号）

2、declare

v_temp number(6):=123; /* := 是一个赋值符号 */

begin

dbms_output.put_line('middle');

dbms_output.put_line(v_temp);

end;

3. 变量声明

一)变量必须在declare语句块中声明

变量声明的语法规则

Identifier [constant] datatype [not null] [:= | default expr];

如:

--变量名类型 :=初始值

v_str varchar2(10):='abc';

constant :修饰常量

二)变量的命名规则

(1)变量名不能够使用保留字，如from 、 select等

(2)变量名最多包含30个字符

(3)不要与数据库的表或者列同名

(4)每一行只能声明一个变量

(5)建议:

a)普通变量 v_

b)常量 c_

三)赋值语句

a)PL/SQL中的赋值使用 :=

b)"=“被用作比较操作符

c)赋值语句中有一些规则需要遵守：

字符类型必须使用单引号 ,不可以使用group by ,可以使用数学操作符或字符串连接操作符。

命名建议

1、常用变量类型：

–普通变量

–%type（当不知道哪个字段的类型的时候，可以直接使用%type的方式给变量定义类型）

–%rowtype（可以定义一行记录作为一个类型，用来存储一整行的数据，可以给单个字段赋值）

–varray (数组)

–table （表）

–record (类似结构体 (抽象)，可以定义不同的类型。)

2、变量声明规则：

–变量名不能够使用保留字，如from 、select等

–第一个字符必须是字母

–变量名最多包含30个字符

–不要与数据库的表或者列同名

–每一行只能声明一个变量

3、普通变量的类型主要有：

–binary_integer：整数，主要用来计数而不是用来表示字段类型

–number(x,y) ：数字类型，最长x位，y位小数

–varchar2（maxlength）：变长字符串,这个参数的上限是32767字节

4、声明方式如下VARCHAR2（L），L为字符串长度，没有缺省值，作为变量最大32767个字节

–char(max_length) 定长字符串

–date：日期

–boolean：布尔类型，可以取值为true、false和null值

链接此处例子

--创建匿名块

declare

--声明变量

v_name varchar2(10) := 'abc';

v_empno number(4) := 1234;

v_hiredate date := to_date('1984-09-03', 'yyyy-MM-dd'); //输出格式21-12月-12

v_hiredate1 date := sysdate;

--当不知道哪个字段的类型的时候，可以直接使用%type的方式给变量定义类型

v_sal emp.sal%type := 1234.12;

--可以定义一行记录作为一个类型，用来存储一整行的数据，可以给单个字段赋值

v_emp emp%rowtype;

v_dd number := 89+9;//可以直接使用算数运算

v_dd1 varchar2(100):='hello'||'world';

--可以设置变量非空

-- v_d1 varchar2(10) not null ;

--声明常量

c_dd constant number := 123;

begin

v_emp.ename := '张三';

v_name:='李四';

--c_dd:=234;

--输出语句

dbms_output.put_line(v_name);

dbms_output.put_line(v_empno);

dbms_output.put_line(v_hiredate);

dbms_output.put_line(v_hiredate1);

dbms_output.put_line(v_sal);

dbms_output.put_line(v_emp.ename);

dbms_output.put_line(c_dd);

-- dbms_output.put_line(v_d1);

dbms_output.put_line(v_dd);

dbms_output.put_line(v_dd1);

--exception

--end后面一定加;

end;

--除了使用sql窗口之外还可以使用commond窗口，写完语句只有使用"/"执行结束，

--后面还跟还需要设置输出打开 set serveroutput on;才会打印结果

4. 数据类型

1、基本数据类型

Number 数字型

Int 整数型

Pls_integer 整数型产生溢出时出现错误 Binary_integer 整数型，表示带符号的整数

Char 定长字符型，最大255个字符

Varchar2 变长字符型，最大2000个字符

Long 变长字符型，最长2GB

Date 日期型

Boolean 布尔型（TRUE、FALSE、NULL三者取一）

2、数据类型

（1）VARCHAR2 我们可以使用VARCHAR2类型来存储变长的字符数据。至于数据在数据库中的内部表现形式要取决于数据库的字符集。VARCHAR2类型需要指明数据长度，这个参数的上限是32767字节。

%type -->表示不需要知道具体的一个字段类型，与指定的字段类型一致即可。

v_empno emp.empno%type;

v_empno2 v_empno%type;

等同于：

v_empno number(4);

--类型与表中某一字段类型一致且长度一致

（2）declare

--声明并初始化

v_cname dept.dname%type:='123';

--等同于

v_cnam2 varchar2(14):='123';

begin

dbms_output.put_line(v_cname);

end;

（3）%rowtype （可以定义一行记录作为一个类型，用来存储一整行的数据，可以给单个字段赋值）

--与表结构完全一致

declare

v_d dept%rowtype;

begin

v_d.dname:='abc';

dbms_output.put_line(v_d.dname);

end;

（4）varry

定义VARRY数据类型语法如下：

type varray_name is varry(size) of element_type [NOT NULL];

varray_name是varray数据类型的名称，size是下整数，表示可容纳的成员的最大数量，每个成员的数据类型是element_type。默认成员可以取空值，否则需要使用NOT NULL加以限制。对于varray数据类型来说，必须经过三个步骤，分别是：定义、声明、初始化。

（5）数组

--定义一个最多保存5个VARCHAR(25)数据类型成员的VARRAY数据类型

TYPE reg_varray_type IS VARRAY(5) OF VARCHAR(25);

--声明一个该VARRAY数据类型的变量

v_reg_varray REG_VARRAY_TYPE;

--TYPE varray_name IS VARRAY(size) OF element_type [NOT NULL];

declare

--相当于定义一个类

-- type v_varray is varray(5) of varchar2(10);

type v_varray is varray(5) of emp.ename%type;

--创建一个数组的对象，v_varray作为类型存在

myArray v_varray;

begin

--给数组类型进行初始化操作

myArray:= v_varray('a','b','c','d','e');

--下标从1开始

dbms_output.put_line(myArray(1));

--下标位置不是数组的个数，而是存储的数据的个数

-- dbms_output.put_line(myArray(4));

--要求，必须在第四个位置插入数据

--扩展只能在数据不大于数组的长度的情况下

--myArray.extend(1);

--替换

myArray(2):='java';

dbms_output.put_line(myArray(2));

end;

4、table记录表（或索引表）数据类型。

一、先声明table类型（声明具体的table类型）

type table类型的名称 is table of data_type [index by binary_integer];

二、再声明该类型的变量

（1）identifier type_name;

如：declare

type tb_mytable is table of dept.deptno%type index by binary_integer;

v_tb_mytable tb_mytable; //变量类型

begin

dbms_output.put_line('hello world');

end;

（2）常用方法：

记录表链接例子

5、record 类似结构体 (抽象)，可以定义不同的类型。

一、先定义 record类型（声明具体的类型）

type type_name is record

(field_name1 data_type [not null {:= | default } expr],

field_name2 data_type [not null {:= | default } expr],

…);

二、再声明该变量

identifier type_name;

如：type tp_mytype is record( deptno dept.deptno%type, dname varchar2(20), sal emp.sal%type );

tp_v_mytype tp_mytype;

6、Boolean类型

布尔值只有TRUE, FALSE及 NULL 三个值 ,注意声明变量时，变量名在前，类型在后。

链接例子

7、单行取值select

--将查询到得数据放入变量中

--1、确保必须有唯一一个字段

--不能存在多个值，也不能为空，否则报错

--将查询到得数据放入变量中

写法

–select 字段 into 变量 from 表 where 条件

--数据来源于表

declare

v_name varchar(20);

v_number number(5,2);

begin

select ename,empno into v_name,v_number from emp where emp.deptno=20 and emp.empno=7369;

dbms_output.put_line(v_name);

end;

&表示一个占位符，动态变量,名称任意取。建议见名知意

declare

v_name varchar(20);

begin

select ename into v_name from emp where emp.deptno=20 and emp.empno=&a;

dbms_output.put_line(v_name);

end;

链接例子

5. plsql异常处理

1、pls异常处理

（1） Oracle把java中的error与exception统称为错误。

decare

begin

exception

end

no_data_found

others

–sqlcode :错误编码

–sqlerrm :错误信息

2、用户自定义的异常处理，对于这类异常情况的处理，步骤如下：

（1）在PL/SQL 块的定义部分定义异常情况：

<异常情况> EXCEPTION;

（2） RAISE <异常情况>；

（3）在PL/SQL 块的异常情况处理部分对异常情况做出相应的处理。

链接例子

3、常见异常种类

6. DML语句

1、DML语句 insert update delete

（1）直接使用

（2）DML语句起作用或者回退的话，需要显式的调用commit或者rollback

（3）sql%rowcount属性来记录最后一条SQL语句影响了多少条记录

2、insert

（1）注意 & 使用表示一个占位符，动态变量,名称任意取，含有&的会弹出一个窗口填入自己定义的参数。建议见名知意

（2）注意字符处理必须在 ''之内

（3）注意提交和回滚

create table tb_temp( vname varchar2(20), vage number(3) )

1)拼接字符串：标准的sql语句

2)execute immediate +字符串

3)execute immediate +字符串 into 变量 using 参数

–：参数名

execute immediate 执行ddl语句或dml语句或dcl语句 (标准的sql语句)

链接例子

7. 控制语句

1、单选择语句：

if 条件 then

end if;

2、双重选择

if 条件 then

else

end if;

3、多选择

if 条件 then

elsif 条件 then

else

end if;

5、先执行后判断（do where）

loop

exit when 条件

end loop;

6、whlie循环先判断后执行

whlie 条件 loop

end loop;

7、for循环:能够确定次数

--不用声明变量增长变量

for index in[reverse] min..max loop

end loop;

index 不能手动改变值，内部自动维护 +1 或-1

reverse：反转的意思

8、链接例子

8. 游标概念

1、游标概念

（1）在PL/SQL块中执行SELECT、INSERT、DELETE和UPDATE语句时，ORACLE会在内存中为其分配上下文区（Context Area），即缓冲区。游标是指向该区的一个指针，或是命名一个工作区（Work Area），或是一种结构化数据类型。它为应用等量齐观提供了一种对具有多行数据查询结果集中的每一行数据分别进行单独处理的方法，是设计嵌入式SQL语句的应用程序的常用编程方式。

（2）在每个用户会话中，可以同时打开多个游标，其数量由数据库初始化参数文件中的OPEN_CURSORS参数定义。

（3）对于不同的SQL语句，游标的使用情况不同：

9. 显示游标

一、游标：cursor

游标就是指在某个结果集上的指针，通过这个指针的移动，我们得以遍历整个结果集

二、游标的使用步骤(标准)

1)、声明游标

2)、打开游标

3)、处理游标中的数据

4)、关闭游标

1、打开游标：就是执行游标所对应的SELECT 语句，将其查询结果放入工作区，并且指针指向工作区的首部，标识游标结果集合。如果游标查询语句中带有FOR UPDATE选项，OPEN 语句还将锁定数据库表中游标结果集合对应的数据行。

格式： OPEN cursor_name[([parameter =>] value[, [parameter =>] value]…)];

2、提取游标数据：就是检索结果集合中的数据行，放入指定的输出变量中。

格式： FETCH cursor_name INTO {variable_list | record_variable };

3、执行FETCH语句时，每次返回一个数据行，然后自动将游标移动指向下一个数据行。当检索到最后一行数据时，如果再次执行FETCH语句，将操作失败，并将游标属性%NOTFOUND置为TRUE。所以每次执行完FETCH语句后，检查游标属性%NOTFOUND就可以判断FETCH语句是否执行成功并返回一个数据行，以便确定是否给对应的变量赋了值。

（1）对该记录进行处理；

（2）继续处理，直到活动集合中没有记录；

4、关闭游标：当提取和处理完游标结果集合数据后，应及时关闭游标，以释放该游标所占用的系统资源，并使该游标的工作区变成无效，不能再使用FETCH 语句取其中数据。关闭后的游标可以使用OPEN 语句重新打开。

格式： CLOSE cursor_name;

注：定义的游标不能有INTO 子句。

5、例子

如:抓取一条数据：

declare

--声明游标

cursor cur_dept is select * from dept;

v_temp dept%rowtype;

begin

--打开游标

open cur_dept;

--使用游标

fetch cur_dept into v_temp;

dbms_output.put_line(v_temp.deptno||'---'||v_temp.dname||'---'||v_temp.loc);

--关闭游标

close cur_dept;

end;

显示游标

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

定义:CURSOR <游标名> IS <SELECT 语句> [FOR UPDATE | FOR UPDATE OF 字段];

[FOR UPDATE | FOR UPDATE OF 字段] --给游标加锁,既是在程序中有"UPDATE","INSERT","DELETE"语句对数据库操作时。

游标自动给指定的表或者字段加锁，防止同时有别的程序对指定的表或字段进行"UPDATE","INSERT","DELETE"操作.

在使用"DELETE","UPDATE"后还可以在程序中使用CURRENT OF <游标名> 子句引用当前行.

操作:OPEN <游标名> --打开游标

FETCH <游标名> INTO 变量1,变量2,变量3,....变量n,;

或者

FETCH <游标名> INTO 行对象; --取出游标当前位置的值

CLOSE <游标名> --关闭游标

属性: %NOTFOUND --如果FETCH语句失败，则该属性为"TRUE"，否则为"FALSE";

%FOUND --如果FETCH语句成果，则该属性为"TRUE"，否则为"FALSE";

%ROWCOUNT --返回游标当前行的行数;

%ISOPEN --如果游标是开的则返回"TRUE"，否则为"FALSE";

使用:

LOOP循环

示例:

DECLARE

cursor c_1 is select * from emp; --定义游标

r c_1%rowtype; --定义一个行对象，用于获得游标的值

BEGIN

if c_1%isopen then

CLOSE c_1;

end if;

OPEN c_1; --判断游标是否打开.如果开了将其关闭，然后在打开

dbms_output.put_line('行号姓名薪水');

LOOP

FETCH c_1 INTO r; --取值

EXIT WHEN c_1%NOTFOUND; --如果游标没有取到值，退出循环.

dbms_output.put_line(c_1%rowcount||' '||r.ename||' '||r.sal); --输出结果,需要 set serverout on 才能显示.

END LOOP;

END;

FOR循环

示例:

DECLARE

cursor c_1 is select ename,sal from emp; --定义游标

BEGIN

dbms_output.put_line('行号姓名薪水');

FOR i IN c_1 --for循环中的循环变量i为c_1%rowtype类型;

LOOP

dbms_output.put_line(c_1%rowcount||' '||i.ename||' '||i.sal); --输出结果,需要 set serverout on 才能显示.

END LOOP;

END;

for循环使用游标是在循环开始前自动打开游标，并且自动取值到循环结束后，自动关闭游标.

游标加锁示例:

DECLARE

cursor c_1 is select ename,sal from emp for update of sal; --定义游标对emp表的sal字段加锁.

BEGIN

dbms_output.put_line('行号姓名薪水');

FOR i IN c_1 --for循环中的循环变量i为c_1%rowtype类型;

LOOP

UPDATE EMP set sal=sal+100 WHERE CURRENT OF c_1; --表示对当前行的sal进行跟新.

END LOOP;

FOR i IN c_1

LOOP

dbms_output.put_line(c_1%rowcount||' '||i.ename||' '||i.sal); --输出结果,需要 set serverout on 才能显示.

END LOOP;

END;

代参数的游标

定义:CURSOR <游标名>(参数列表) IS <SELECT 语句> [FOR UPDATE | FOR UPDATE OF 字段];

示例:

DECLARE

cursor c_1(name emp.ename%type) is select ename,sal from emp where ename=name; --定义游标

BEGIN

dbms_output.put_line('行号姓名薪水');

FOR i IN c_1('&name') --for循环中的循环变量i为c_1%rowtype类型;

LOOP

dbms_output.put_line(c_1%rowcount||' '||i.ename||' '||i.sal); --输出结果,需要 set serverout on 才能显示.

END LOOP;

END;

10. 隐式游标

隐试游标游标是系统自动生成的。每执行一个DML语句就会产生一个隐试游标，起名字为SQL;

隐试游标不能进行"OPEN" ,"CLOSE","FETCH"这些操作;

属性:

%NOTFOUND --如果DML语句没有影响到任何一行时，则该属性为"TRUE"，否则为"FALSE";

%FOUND --如果DML语句影响到一行或一行以上时，则该属性为"TRUE"，否则为"FALSE";

%ROWCOUNT --返回游标当最后一行的行数;

个人认为隐试游标的作用是判断一个DML语句；

示例：

BEGIN

DELETE FROM EMP WHERE empno=&a;

IF SQL%NOTFOUND THEN

dbms_output.put_line('empno不存在');

END IF;

IF SQL%ROWCOUNT>0 THEN

dbms_output.put_line('删除成功');

END IF;

END;

11. 游标属性和遍历

1、游标属性

(1)%isopen，boolean类型变量，用来代表游标是否打开。

(2)%notfound，boolean类型变量，如果最近的fetch语句没有返回一条记录，取true。

(3)%found，boolean类型变量，如果最近的fetch语句取到了记录，取true。

(4)%rowcount，number类型变量，用来代表目前fetch到的记录的总行数。

2、Loop循环遍历游标 利用loop循环和%notfound属性实现游标的遍历

declare

--声明游标 cursor cur_dept is select * from dept;

--声明变量用来存结果 v_temp dept%rowtype;

begin

--打开游标 open cur_dept;

loop

--使用游标

fetch cur_dept into v_temp;

exit when cur_dept%notfound;

dbms_output.put_line(v_temp.deptno||'---'||v_temp.dname||'---'||v_temp.loc);

end loop;

--关闭游标

close cur_dept;

end;

3、while循环遍历游标 利用while循环配合%found属性实现游标的遍历

declare

--声明游标

cursor cur_dept is select * from dept;

--声明变量

v_temp cur_dept%rowtype;

begin

--打开游标

open cur_dept;

--先抓取一次

fetch cur_dept into v_temp;

while cur_dept%found loop

dbms_output.put_line(v_temp.deptno||'---'||v_temp.dname||'---'||v_temp.loc);

--使用游标

fetch cur_dept into v_temp;

end loop;

--关闭游标

close cur_dept;

end;

4、for循环遍历游标 利用for循环遍历游标不需要打开游标，也不需要关闭，甚至不用声明循环变量、不用抓取，最简单。推荐使用

declare

--声明游标

cursor cur_dept is select * from dept;

begin

for cur_dept_temp in cur_dept loop

dbms_output.put_line('第'||cur_dept%rowcount||'行,数据为'||cur_dept_temp.deptno||'---'||cur_dept_temp.dname||'---'||cur_dept_temp.loc);

end loop;

end;

5、链接例子

12. 更新或者删除

1、更新或者删除

有时候我们取出数据并不只是为了查阅，也可以进行更新或删除，游标用作这种用途的时候必须进行锁定，在下面这个例子中，我们根据每位雇员的薪水信息来进行更新或删除操作，为了不影响emp表中旧有的数据，我们把emp这张表复制一下：

create table emp2 as select * from emp;

然后就可以更改表中的数据了

2、说明for update

在这个例子中，我们通过在select语句后面添加for update来提示oracle锁定记录以便进行更新，然后用where current of 来指明操作是添加在当前游标所指向的记录上。

链接例子

13. ref游标

强型REF游标

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

定义:TYPE <游标名> IS REF CURSOR RETURN<返回类型>;

操作:OPEN <游标名> For <select 语句> --打开游标

FETCH <游标名> INTO 变量1,变量2,变量3,....变量n,;

或者

FETCH <游标名> INTO 行对象; --取出游标当前位置的值

CLOSE <游标名> --关闭游标

属性: %NOTFOUND --如果FETCH语句失败，则该属性为"TRUE"，否则为"FALSE";

%FOUND --如果FETCH语句成果，则该属性为"TRUE"，否则为"FALSE";

%ROWCOUNT --返回游标当前行的行数;

%ISOPEN --如果游标是开的则返回"TRUE"，否则为"FALSE";

使用:

示例:

DECLARE

type c_type is ref cursor return emp%rowtype; --定义游标

c_1 c_type; --实例化这个游标类型

r emp%rowtype;

BEGIN

dbms_output.put_line('行号姓名薪水');

open c_1 for select * from emp;

loop

fetch c_1 into r;

exit when c_1%notfound;

dbms_output.put_line(c_1%rowcount||' '||r.ename||' '||r.sal); --输出结果,需要 set serverout on 才能显示.

END LOOP;

close c_1;

END;

弱型REF游标

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

定义:TYPE <游标名> IS REF CURSOR;

操作:OPEN <游标名> For <select 语句> --打开游标

FETCH <游标名> INTO 变量1,变量2,变量3,....变量n,;

或者

FETCH <游标名> INTO 行对象; --取出游标当前位置的值

CLOSE <游标名> --关闭游标

属性: %NOTFOUND --如果FETCH语句失败，则该属性为"TRUE"，否则为"FALSE";

%FOUND --如果FETCH语句成果，则该属性为"TRUE"，否则为"FALSE";

%ROWCOUNT --返回游标当前行的行数;

%ISOPEN --如果游标是开的则返回"TRUE"，否则为"FALSE";

示例：

set autoprint on;

var c_1 refcursor;

DECLARE

n number;

BEGIN

n:=&请输入;

if n=1 then

open :c_1 for select * from emp;

else

open :c_1 for select * from dept;

end if;

END;

4、链接例子

14. 过程

过程

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

定义:CREATE [OR REPLACE] PROCEDURE <过程名>[(参数列表)] IS

[局部变量声明]

BEGIN

可执行语句

EXCEPTION

异常处理语句

END [<过程名>];

变量的类型:in 为默认类型,表示输入; out 表示只输出;in out 表示即输入又输出;

操作以有的过程:在PL/SQL块中直接使用过程名;在程序外使用execute <过程名>[(参数列表)]

使用:

示例:

创建过程:

create or replace procedure p_1(n in out number) is

r emp%rowtype;

BEGIN

dbms_output.put_line('姓名薪水');

select * into r from emp where empno=n;

dbms_output.put_line(r.ename||' '||r.sal); --输出结果,需要 set serverout on 才能显示.

n:=r.sal;

END;

使用过程:

declare

n number;

begin

n:=&请输入员工号;

p_1(n);

dbms_output.put_line('n的值为 '||n);

end;

删除过程:

DROP PROCEDURE <过程名>;

15. 函数

函数

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

定义:CREATE [OR REPLACE] FUNCTION <过程名>[(参数列表)] RETURN 数据类型 IS

[局部变量声明]

BEGIN

可执行语句

EXCEPTION

异常处理语句

END [<过程名>];

变量的类型:in 为默认类型,表示输入; out 表示只输出;in out 表示即输入又输出;

创建函数:

create or replace function f_1(n number) return number is

r emp%rowtype;

BEGIN

dbms_output.put_line('姓名薪水');

select * into r from emp where empno=n;

dbms_output.put_line(r.ename||' '||r.sal); --输出结果,需要 set serverout on 才能显示.

return r.sal;

END;

使用函数:

declare

n number;

m number;

begin

n:=&请输入员工号;

m:=f_1(n);

dbms_output.put_line('m的值为 '||m);

end;

删除函数:

DROP FUNCTION <函数名>;

16. 数据包

数据包

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

定义:

定义包的规范

CREATE [OR REPLACE] PACKAGE <数据包名> AS

--公共类型和对象声明

--子程序说明

END;

定义包的主体

CREATE [OR REPLACE] PACKAGE BODY <数据包名> AS

--公共类型和对象声明

--子程序主体

BEGIN

-初始化语句

END;

创建数据包规范:

create or replace package pack_1 as

n number;

procedure p_1;

FUNCTION f_1 RETURN number;

end;

创建数据包主体:

create or replace package body pack_1 as

procedure p_1 is

r emp%rowtype;

begin

select * into r from emp where empno=7788;

dbms_output.put_line(r.empno||' '||r.ename||' '||r.sal);

end;

FUNCTION f_1 RETURN number is

r emp%rowtype;

begin

select * into r from emp where empno=7788;

return r.sal;

end;

使用包:

declare

n number;

begin

n:=&请输入员工号;

pack_1.n:=n;

pack_1.p_1;

n:=pack_1.f_1;

dbms_output.put_line('薪水为 '||n);

end;

在包中使用REF游标

创建数据包规范:

create or replace package pack_2 as

TYPE c_type is REF CURSOR; --建立一个ref游标类型

PROCEDURE p_1(c1 in out c_type); --过程的参数为ref游标类型;

end;

创建数据包主体:

create or replace package body pack_2 as

PROCEDURE p_1(c1 in out c_type) is

begin

open c1 for select * from emp;

end;

使用包:

var c_1 refcursor;

set autoprint on;

execute pack_2.p_1(:c_1);

删除包:

DROP PACKAGE <包名>;

17. 触发器

触发器

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

创建触发器:

CREATE [OR REPLACE] TRIGGER <触发器名>

BEFORE|AFTER

INSERT|DELETE|UPDATE [OF <列名>] ON <表名>

[FOR EACH ROW]

WHEN (<条件>)

关键字"BEFORE"在操作完成前触发;"AFTER"则是在操作完成后触发;

关键字"FOR EACH ROW"指定触发器每行触发一次.

关键字"OF <列名>" 不写表示对整个表的所有列.

WHEN (<条件>)表达式的值必须为"TRUE".

特殊变量:

:new --为一个引用最新的列值;

:old --为一个引用以前的列值;

这些变量只有在使用了关键字 "FOR EACH ROW"时才存在.且update语句两个都有,而insert只有:new ,delect 只有:old;

使用RAISE_APPLICATION_ERROR

语法:RAISE_APPLICATION_ERROR(错误号(-20000到-20999),消息[,{true|false}]);

抛出用户自定义错误.

如果参数为'TRUE',则错误放在先前的堆栈上.

INSTEAD OF 触发器

INSTEAD OF 触发器主要针对视图（VIEW)将触发的dml语句替换成为触发器中的执行语句，而不执行dml语句.

禁用某个触发器

ALTER TRIGGER <触发器名> DISABLE

重新启用触发器

ALTER TRIGGER <触发器名> ENABLE

禁用所有触发器

ALTER TRIGGER <触发器名> DISABLE ALL TRIGGERS

启用所有触发器

ALTER TRIGGER <触发器名> ENABLE ALL TRIGGERS

删除触发器

DROP TRIGGER <触发器名>

18. 自定义对象

创建对象:

CREATE [OR REPLACE] TYPE <对象名> AS OBJECT(

属性1 类型

属性2 类型

方法1的规范(MEMBER PROCEDURE <过程名>

方法2的规范 (MEMBER FUNCTION <函数名> RETURN 类型)

PRAGMA RESTRIC_REFERENCES(<方法名>,WNDS/RNDS/WNPS/RNPS);

关键字"PRAGMA RESTRIC_REFERENCES"通知ORACLE函数按以下模式之一操作;

WNDS-不能写入数据库状态;

RNDS-不能读出数据库状态;

WNPS-不能写入包状态;

RNDS-不能读出包状态;

创建对象主体:

CREATE [OR REPLACE] TYPE body <对象名> AS

方法1的规范(MEMBER PROCEDURE <过程名> is <PL/SQL块>

方法2的规范 (MEMBER FUNCTION <函数名> RETURN 类型 is <PL/SQL块>

END;

使用MAP方法和ORDER方法

用于对自定义类型排序。每个类型只有一个MAP或ORDER方法。

格式:MAP MEMBER FUNCTION <函数名> RETURN 类型

ORDER MEMBER FUNCTION <函数名> RETURN NUMBER

创建对象表

CREATE TABLE <表名> OF <对象类型>

示例:

1. 创建name 类型

create or replace type name_type as object(

f_name varchar2(20),

l_name varchar2(20),

map member function name_map return varchar2);

create or replace type body name_type as

map member function name_map return varchar2 is --对f_name和l_name排序

begin

return f_name||l_name;

end;

2 创建address 类型

create or replace type address_type as object

( city varchar2(20),

street varchar2(20),

zip number,

order member function address_order(other address_type) return number);

create or replace type body address_type as

order member function address_order(other address_type) return number is --对zip排序

begin

return self.zip-other.zip;

end;

3 创建stu对象

create or replace type stu_type as object (

stu_id number(5),

stu_name name_type,

stu_addr address_type,

age number(3),

birth date,

map member function stu_map return number,

member procedure update_age);

create or replace type body stu_type as

map member function stu_map return number is --对stu_id排序

begin

return stu_id;

end;

member procedure update_age is --求年龄用现在时间-birth

begin

update student set age=to_char(sysdate,'yyyy')-to_char(birth,'yyyy') where stu_id=self.stu_id;

end;

4. 创建对象表

create table student of stu_type(primary key(stu_id));

5.向对象表插值

insert into student values(1,name_type('关','羽'),address_type('武汉','成都路',43000), null,sysdate-365*20);

6.使用对象的方法

delcare

aa stu_type;

begin

select value(s) into aa from student s where stu_id=1; --value()将对象表的每一行转成行对象括号中必须为表的别名

aa.update_age();

end;

7.select stu_id,s.stu_name.f_name,s.stu_name.l_name from student s; --查看类型的值

8.select ref(s) from student s ; --ref()求出行对象的OID,括号中必须为表的别名;deref()将oid变成行队像；

19. 其他

1.在PL/SQL中使用DDL

将sql语句赋给一个varchar2变量,在用execute immediate 这个varchar2变量即可;

示例：

declare

str varchar2(200);

begin

str:='create table test(id number,name varchar2(20))'; --创建表

execute immediate str;

str:='insert into test values(3,''c'')'; --向表里插数据

execute immediate str;

end;

但是要队这个表插入数据也必须使用execute immediate 字符变量

2.判断表是否存在;

示例：

declare

n tab.tname%type;

begin

select tname into n from tab where tname='&请输入表名';

dbms_output.put_line('此表以存在');

exception

when no_data_found then

dbms_output.put_line('还没有此表');

end;

3.查看以有的过程;

示例：

select object_name,object_type,status from user_objects where object_type='PROCEDURE';

20. jdbc调用存储过程

存储过程

{call 存储名（？，？，？，？）}固定写法

--赋与调试权限

GRANT debug any procedure, debug connect session TO scott

小知识：函数加()使用就是调用这个函数，不加就代表这个函数

conn.close;

conn.close();

陈同学专辑

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫