plsql基础知识教程大全

练习答案见最下面！！！
PL/SQL
一 简介
PL/SQL表示SQL的过程式语言扩展(Procedural Language Extension to SQL)。 是对SQL的扩充,它吸收了高级语言的许多最高设计特点:如数据封装性、信息隐蔽 性、重载等。它允许SQL的数据操纵语言和查询语句包含在块结构
(block_structured)和代码过程语言中,使PL/SQL成为一个功能强大的事务处理语言。

为什么使用PL/SQL？
传统SQL只负责做什么，不管怎么做。缺少过程与控制语句；无任何算法描述能力。

PL/SQL 拥有变量、控制结构、函数、异常处理等高级语言的要素。

优点:
1.提升系统的性能
在没有PL/SQL时，Oracle每次只处理一个SQL语句，而在具有PL/SQL时，一个
完整的语句块一次发送到Oracle，可明显减少和Oracle之间的通信和调用。
2.良好的可维护性和可移植性
保存于数据库内部，可以运行于Oracle所运行的任何环境。在不同的环境下，
由于PL/SQL不会改变，任何工具可以使用一个PL/SQL脚本。
3.流程控制
条件语句、循环语句和分支可用来控制程序的过程流，以决定是否或何时执
行SQL或其他行动。
4.安全性高
安全性高，减少程序对底层数据库的直接操作。

缺点:
不能移植到异构数据库系统

开发环境:
任何纯文本编辑器 如vi
ORACLE客户端SQLPLUS

二 PL/SQL程序结构
PLSQL可执行块:
DECLARE
声明部分
BEGIN
代码执行部分,这部分是必要的
EXCEPTION
异常处理部分
END;

例如：
DECLARE --声明部分
v_StudentID NUMBER(5) := 1000;
--以分号分隔每一行代码,与Java相同
v_FirstName VARCHAR(20);
BEGIN --执行部分
SELECT first_name
INTO v_FirstName
FROM students
WHERE id = v_StudentID;
EXCEPTION --异常处理
WHEN NO_DATA_FOUND THEN
INSERT INTO log_table
VALUES(‘Student 1000 does not exist!’);
END;


练习：Hello World.

打开输出:set serverout on;

变量定义：以v_开头
常量定义：以c_开头
游标定义：以_cursor结尾
异常定义：以e_开头

PLSQL的注释：
单行注释： --两个减号
多行注释: /* XXXXX
*/

标识符命名规则：
.以字母开头
.后跟任意非空格字符，数字，货币符号，下划线和#
.最大长度为30个字符
.不能是oracle中的关键字

注：PLSQL对大小写不敏感


PLSQL的变量声明
变量名 [CONSTANT] 变量类型 [NOT NULL] [:=初始值]

PL/SQl中变量的作用域和可见性
作用域：指的是可以访问变量的程序部分，
作用域从变量声明开始到该语句块结束。
可见性：外部过程变量在内嵌的过程中可见，
内部过程变量在外部过程不可见

例：v_var varchar2(2

0):='Hello World';

练习：变量定义字符串Hello World.输出
练习：从某表中查处一个值赋给变量，进行输出

PLSQL转义字符: ' (单引号)
练习：输出连续的三个单引号

三 PLSQL数据类型
PLSQL的数据类型分为:
标量型:数字型、字符型、布尔型、日期型
组合型(复合型):RECORD、TABLE
引用型(参考型):CURSOR
LOB型:BLOB、CLOB等

(1)标量类型：
数字型：
1.NUMBER
定点数或浮点数，同SQL中NUMBER
2.BINARY_INTEGER
-2**31至2**31间的整数，占用空间比NUMBER小。
当数值范围超出限制时，自动转型为NUMBER。
3.PLS_INTEGER
-2**31至2**31间的整数，占用空间比NUMBER小。
当数值范围超出限制时抛出异常。
注：每一种类型均有许多的子类型，不做介绍

字符型:
常用：CHAR VARCHAR VARCHAR2(STRING)


%TYPE:具有与某变量或数据库的表中某一字段相同的类型
例如：v_FirstName students.first_name%TYPE;
v_1 varchar(10);
v_2 v_1%TYPE;
declare
v_var mytable.c_column%type;
v_aa v_var%type;
begin
select c_column into v_var from mytable where rownum<2;
dbms_output.put_line(v_var);
end;
/


(2)组合类型
1.RECORD类型，包含多个标量类型
使用前，首先进行定义
TYPE record_name IS RECORD (
field1 type1 [NOT NULL] [:=expr1],
field2 type2 [NOT NULL] [:=expr2],
…
fieldn typen [NOT NULL] [:=exprn]
);
声明RECORD变量
v_record record_name;
RECORD属性访问
v_record.field1:=...;
v_record.field2:=...;

两个类型相同的RECORD变量可以相互赋值
当RECORD中属性名与select的类型相同，可以直接进行into赋值

例如：select field1,field2 into v_record from ....

2.%ROWTYPE 返回一个基于数据库表定义的类型

例如：
v_record test%ROWTYPE;

注：%rowtype和record的区别
%rowtype是 自定义与表同类型record的便捷方式

3.PL/SQL表(TABLE类型）
类似数组功能的变量类型，包含一组数据，通过下标的方式访问数据。
定义一个TABLE类型：
TYPE table_type_name IS TABLE OF member_type INDEX BY BINARY_INTEGER;

TABLE中元素的类型(member_type)可以是复合类型
如果元素i还没有创建就被引用，会抛出异常(ORA-1403: No data found)

declare
type my_table is table of %type index by varchar2(20);
v_table my_table;
begin
select name into v_table(1) from student;
dbms_output.put_line(v_table(1));
end;
/

四 控制语句
控制语句：条件语句 循环语句 GOTO语句（不建议使用）

(1)条件语句
IF boolean_expression1 THEN
...
ELSIF boolean_expression2 THEN
...
ELSE
...
END IF;
练习：判断一个表中记录条数是奇数还是偶数。

(2)循环语句
1. LOOP简单循环
格式如下:
LOOP
…
--使用IF与EXIT配合进行退出
IF boolean_expr THEN
EXIT;
END IF;

--使用EXIT与WHEN进行退出
EXIT WHEN boolean_expr2;
END LOOP;
练习：输出1..10之间的数

2. WHILE循环
WHILE boolean_expression LOOP
…
END LOOP;

也可以使用EXIT或EXIT WHEN语句终止循环处理。
3. FOR循环
FOR loop_counter IN [REVERSE] low_bound..high_bound LOOP
…
END LOOP;
注:FOR循环中的loop_counter经试验在10g中可以不声明
练习：反序输出1..10之间的数

利用循环向表中添加若干条记录
--10条数据

循环的选择：
FOR循环：用于明确循环次数的使用
WHILE循环：用于条件判断的循环
LOOP循环：类似JAVA中的do while循环，首先进行一次操作

GOTO语句：
GOTO label_name;
只能由内部的语句块跳往外部块
设置标签
<>

declare
total number := 0;
begin
for i in 1..10 loop
if i > 5 then
goto my_lable;
end if;
total := total + i;
end loop;
<>
dbms_output.put_line('total = '||total);
end;


NULL语句：表示不执行任何操作
declare
total number := 0;
begin
for i in 1..10 loop
if mod(i,2) = 1 then
total := total + i;
else
null;
end if;
end loop;
dbms_output.put_line('total = '||total);
end;
/

五 游标CURSOR
使用游标，用于提取多行数据集。
cursor的使用步骤
1.声明游标(声明游标代表的查询语句)
2.为查询打开游标(执行查询语句)
3.将结果提取出来，存入PL/SQL变量中(遍历查询语句)
4.关闭游标

(1)游标声明
CURSOR CURSOR_NAME IS select_statement
如果使用了PL/SQL变量在select_statement中,
变量的声明必须放在游标前面。
例如：
DELCARE
v_major students.major%TYPE;
CURSOR c_student IS
SELECT first_name, last_name
FROM students
WHERE major = v_major;

(2)打开游标
OPEN Cursor_name
注:游标最多只能打开一次

(3)从游标中取出数据 FETCH
FETCH Cursor_name into v_1,v_2
FETCH Cursor_name into v_rec

游标常用属性:
%ISOPEN：确定游标是否打开
%ROWCOUNT:当前游标的指针位移量,即当前检索的个数
%FOUND：若前面的FETCH语句返回一行数据，
则%FOUND返回TRUE;
如果未fetch就检查%FOUND,则返回NULL
%NOTFOUND：与%FOUND行为相反
注：%ROWCOUNT %FOUND %NOTFOUND均需要游标已打开

(4)关闭
CLOSE CURSOR
游标关闭后不允许FETCH
游标只能关闭一次

(5)遍历CURSOR
1.简单LOOP
LOOP
FETCH cursor INTO…
EXIT WHEN cursor%NOTFOUND;
END LOOP
2.WHILE
FETCH cursor INTO…
WHILE cursor%FOUND LOOP
FETCH cursor INTO…
END LOOP
3.FOR
FOR var IN cursor LOOP
--var既是提取出的RECORD，可以直接使用
END LOOP
注：使用FOR不需要对游标进行open,fetch与close

(6)带参数的CURSOR
声明：
CURSOR cursor_name(v_field field_type)
is select * from mytable where c_column=v_field
使用：
open cursor_name(fiel

d_value);
(7)自定义游标
声明：
type my_cursor is ref cursor;
使用： v_cur my_cursor;
open v_cur for select * from student;

open cursor_name;

练习
1.在test1表中插入多条数据 10
2.遍历test1表数据，如果id为奇数则插入test2表,如果为偶数则插入test3表
3.删除test1表中id能被5整出的记录

六 异常处理
PL/SQL错误
编译时(show error显示编译异常)
运行时(异常EXCEPTION)

DECLARE
…
BEGIN
…
EXCEPTION
WHEN exception_type(others) THEN
--异常处理
END;

自定义异常:
declare
exception_name EXCEPTION;

抛出异常:
raise exception_name;


预定义异常:
CURSOR_ALREADY_OPEN ORA-6511 试图打开一个已打开的游标
DUP_VAL_ON_INDEX ORA-0001 试图破坏一个唯一性限制
INVALID_CURSOR ORA-1001 试图使用一个无效的游标
INVALID_NUMBER ORA-1722 试图对非数字值进行数字操作
LOGIN_DENIED ORA-1017 无效的用户名或者口令
NO_DATA_FOUND ORA-1403 查询未找到数据
NOT_LOGGED_ON ORA-1012 还未连接就试图数据库操作
PROGRAM_ERROR ORA-6501 内部错误
ROWTYPE_MISMATCH ORA-6504 主变量和光标的类型不兼容
STORAGE_ERROR ORA-6500 内部错误
TIMEOUT_ON_RESOURCE ORA-0051 发生超时
TOO_MANY_ROWS ORA-1422 SELECT INTD命令返回的多行
TRANSACTION_BACKED_OUT ORA-006 由于死锁提交被退回
VALUE_ERROR ORA-6502 转换或者裁剪错误
ZERO_DIVIDE ORA-1476 试图被零除

七 存储过程PROCEDURE
匿名语句块：
不能存储于数据库之中
每次执行都需要重新编译
不能被其他模块调用

命名语句块：
编译并存储于数据库中
可以在其他地方调用
可以输入输出参数

命名语句块包括：
(1)存储过程 PROCEDURE
(2)函数 FUNCTION
(3)触发器 TRIGGER
(4)包 PACKAGE

存储过程创建语法:
CREATE OR REPLACE PROCEDURE procedure_name
[(
arg_name1 [{IN,OUT,IN OUT}] TYPE,
...
arg_namen [{IN,OUT,IN OUT}] TYPE
)]
IS/AS
--声明部分
BEGIN
...
EXCEPTION
...
END;

参数类型：
IN:只读
OUT:只写
IN OUT:可读可写
存储过程的使用：

注意：存储过程传参数时不能指定形参的长度，只有类型。
形参类型有三种:in. out. in out.默认为in
注：IN OUT或OUT参数对应的实际参数必须是变量，
不能是常量或表达式。


调用方式:
1.直接执行：exec/execute my_procedure_name(..);
2.在其他地方调用
begin
my_proced

ure(...);
end;
/
3.call my_procedure();
4.利用jdbc调用存储过程：
create or replace procedure pro_jdbc
(v_id in number,v_name out varchar2)
is
v_name1 varchar2(20);
begin
select name into v_name1 from t_emp
where id = v_id;
v_name := v_name1;
end;
/

public static void CallProcedure() throws Exception{
Class.forName
("oracle.jdbc.driver.OracleDriver");
String url =
"jdbc:oracle:thin:@localhost:1521:XE";
Connection conn =
DriverManager
.getConnection(url,"jun","jun");
CallableStatement cs =
conn.prepareCall("{call pro_jdbc(?,?)}");
cs.setInt(1,3);
cs.registerOutParameter
(2, java.sql.Types.VARCHAR);
cs.execute();
String userName = cs.getString(2);
System.err.println(userName);
cs.close();
conn.close();
}

传参方式:
位置标示法
调用时添入所有参数，实参与形参按顺序一一对应
名字标示法
调用时给出形参名字，并给出实参
ModeTest(12,
p_OutParm => v_var1,
p_InOut => 10);
两种方法可以混用
名字标示法对于参数很多时，可提高程序的可读性

删除过程：
drop procedure pro_name;

查看过程、函数内容：
select text from all_source where name=upper('&plsql_name');

八 FUNCTION函数
函数与存储过程相同点：
都有名字
都有统一的形式：声明，执行与异常处理
都可以存储在数据库中

差别：
存储过程调用 本身是一个PL/SQL语句
函数调用 则是PLSQL语句的一部分
v_count:=my_function(table_name);
函数有返回值而存储过程没有
函数可以在SQL语句中调用

CREATE [OR REPLACE] FUNCTION func_name
[(
arg_name1 [{IN,OUT,IN OUT}] TYPE,
...
arg_namen [{IN,OUT,IN OUT}] TYPE
)]
RETURN TYPE --返回类型
IS/AS
--声明部分
BEGIN
...
EXCEPTION
...
END;

RETURN的使用：
与其他语言的RETURN相同
PLSQL的FUNCTION要求必须拥有一个要执行到的RETURN
当有异常处理部分的时候，必须在每种异常处理部分（when .. then ..）有一个return

函数的调用：
declare
v_result varchar2(20);
begin
v_result:=my_function(2);
dbms_output.put_line(v_result);
end;

函数的另一种调用：
select my_function(2) from dual;

利用jdbc调用函数：
conn.prepareCall("{?=call pro_fun(?,?)}");
1.建立一个函数，实现两个整数的相加，
并返回结果
2.利用jdbc调用，打印结果。

函数的删除：
drop function function_name;

九 PACKAGE包
包是可以将逻辑上相关的对象存储在一起的PL/SQL结构
包中可以包括:
变量、游标、RECORD、TABLE、存储过程、函数等，
声明部分出现的任何东西都能出现在包中。

包中的内容可以在其他地方使用或调用，是全局的内容。

PACKAGE内容包括：包头、包体
包头：包含了包的所有信息，但不包括任何过程代码。
包体：包含了包头声

明的procedure、function的过程代码。

使用：
包名.函数名
包名.过程名

例如:
dbms_output.put_line('hello');

注：包头与包体分开创建，只有包头创建成功才可以创建包体。

创建包头：
CREATE [OR REPLACE] PACKAGE pack_name { IS | AS }
procedure_specification |
function_ specification |
variable_declaration |
type_definition |
exception_declaration |
cursor_declaration

procedure package_procedure;
function package_function
return varchar2;
END pack_name;

创建包体：
CREATE OR REPLACE PACKAGE BODY pak_test AS
PROCEDURE AddStudent(p_StuID IN students.id%TYPE,
p_Dep IN classes.department%TYPE,
p_Course IN classes.course%TYPE) IS
BEGIN
…
END AddStudent;

PROCEDURE RemoveStudent(p_StuID IN students.id%TYPE) IS
BEGIN
…
END RemoveStudent;
END pak_test;

举例：
CREATE OR REPLACE PACKAGE pak_test AS
PROCEDURE AddStudent(
p_StuID IN students.id%TYPE,
p_Dep IN classes.department%TYPE,
p_Course IN classes.course%TYPE);
PROCEDURE RemoveStudent(p_StuID IN students.id%TYPE);
e_StudentNotRegistered EXCEPTION;
TYPE t_StuIDTable IS TABLE OF students.id%TYPE
INDEX BY BINARY_INTEGER;
END pak_name;



包中内容的使用：
包中的内容在包内可以直接使用
包中的内容在包外，需使用加上"包名."前缀进行使用
每一个会话在第一次使用包时，
初始化并拥有一个包中所有变量的副本。


十 触发器TRIGGER 又称为特殊的存储过程，当触发事件发生时它们会隐含的执行。

触发事件：
1.处理数据库表的DML语句（insert update delete）
2.用户执行的DDL语句（create alter）
通常用于审计目的（Oracle DBA专用，用于记录何时执行，哪个用户执行）
3.数据库启动或者关闭
4.用户登录或者注销


创建触发器语法：
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE|AFTER} triggering_event ON table_name
[FOR EACH ROW]
declare
...
begin
...
end;

trigger_name: 触发器名称
triggering_event：触发事件(insert update delete)
{BEFORE|AFTER}： 触发时间(即触发事件发生前，还是发生后)
[FOR EACH ROW]： 触发执行级别(行级别/语句级别)

（1）理解什么是触发器
（2）触发器的类型：
触发事件：insert,update,delete
触发时间：before，after
触发级别：行级别(for each row)和语句级别

访问数据,使用伪记录：
:NEW 新插入或更新的数据(insert update)
:OLD 删除或更新前的数据(delete update)
仅限于 for each row

注：一个表最多定义十二种触发器
触发器是否会执行事务控制语句（commit,savepoint,rollback）：
1）不会
触发器所有执行的操作（包括触发器调用的函数和过程）会成为
事务的一部分，当该事务提交或回滚，触发

器所执行的操作也会
被提交或回滚。
2）会
触发器中包含有自治事务

定义自治事务：
使用编译指令：AUTONOMOUSE_TRANSACTION,该指令出现在语句块
的声明部分
例如：DECLARE
PRAGMA AUTONOMOUSE_TRANSACTION;
...

查看触发器：select count(*) from user_triggers;
删除触发器：drop trigger my_trigger;
删除一个表，则在该表上定义的触发器
也会被删除

十一 动态SQL与定时调用
动态执行sql语句
execute immediate v_sql

创建定时调用
dbms_job.submit(code,'pro_test;
',sysdate,
'sysdate+1/(24*2)');
四个参数:
1.定时调用任务序号(out模式参数,由Oracle自动生成)
2.定时调用要执行的代码
3.首次执行时间
4.下次执行时间代码

create or replace procedure my_job_pro
is
begin
insert into my_job_test values(sysdate);
end;
/

declare
v_code number;
begin
dbms_job.submit(v_code,'my_job_pro;'
,sysdate,'sysdate+1/(24*60*5)');
commit;
end;
/
查询定时调用
user_jobs; job,what
select job,what from user_jobs;
删除定时调用
exec dbms_job.remove(任务号)；

begin
dbms_job.remove(code);
end;
/

练习答案

例一：
declare
v_name varchar2(20) := 'Hello World';
begin
dbms_output.put_line(v_name||' PL/SQL');
end;
/

打开输出:set serverout on;

例二：
declare
v_name ername%type;
v_pwd v_name%type;
begin
select username,password
into v_name,v_pwd
from customer
where username='demo';
dbms_output.put_line(v_name||' '||v_pwd);
end;
/

例三：
begin
dbms_output.put_line('''''''');
end;
/

例四：
declare
TYPE my_record IS RECORD(
v_name varchar2(20),
v_pwd ername%type
);
v_record my_record;
begin
select username,password
into v_record.v_name,v_record.v_pwd
from customer;
dbms_output.put_line(v_record.v_name||' '||v_record.v_pwd);
end;
/

declare
v_record customer%rowtype;
begin
select username,password,name
into v_record
from customer;
dbms_output.put_line(v_ername||' '||v_record.password);
end;
/

例五：
declare
TYPE my_table IS TABLE OF ername%type INDEX BY varchar2(20);
v_table my_table;
begin
select username into v_table(1) from customer where rownum<2;
dbms_output.put_line(v_table(1));
end;
/

例六：
declare
type my_table is table of tbl_%type index by varchar2(20);
v_table my_table;
begin
select name into v_table(4) from tbl_test;
dbms_output.put_line(v_table(4));
end;
/

declare
type my_varray is varray(10) of varchar(20);
v_array my_varray;
begin
v_array:=my_varray('1','2','3');
dbms_output.put_line(v_array(1));
e

nd;
/

declare
type my_varray is varray(10) of varchar(20);
v_array my_varray;
begin
v_array:=my_varray(null,null,null);
v_array(1):='a';
v_array(3):='c';
dbms_output.put_line(v_array(v_array.next(2)));
end;
/

declare
type my_varray is table of varchar(20);
v_array my_varray;
begin
v_array:=my_varray(null,null,null);
v_array(1):='a';
v_array(3):='c';
dbms_output.put_line(v_array(v_array.next(2)));
end;
/

declare
type my_varray is table of varchar(20);
v_array my_varray;
begin
v_array:=my_varray('e','f','g');
v_array(1):='a';
v_array(3):='c';
dbms_output.put_line(v_array(v_array.next(2))||' test');
--dbms_output.put_line(v_array(v_array.next(3)));
v_array.extend;
v_array(4):='z';
dbms_output.put_line(v_array(v_array.next(3)));
end;

declare type my_table is table of s_%type index by binary_integer;
v_table my_table;
v_isNull boolean;
begin
select name into v_table(10) from s_region where id=1;
select name into v_table(100) from s_region where id=2;
dbms_output.put_line(v_table(10));
dbms_output.put_line(v_table(v_table.next(10)));
v_isNull:=v_table is null;
if v_isNull=false then
dbms_output.put_line(' v_isNull is false!');
end if;
end;

例七：
declare
v_num number(10);
begin
select count(username) into v_num from customer;
v_num:=mod(v_num,2);
if v_num=0 then
dbms_output.put_line('偶数');
elsif v_num<>0 then
dbms_output.put_line('奇数');
end if;
end;
/

例一：
loop,while loop循环练习：
declare
v_c number(3):=1;
begin
loop
dbms_output.put_line(v_c);
--if v_c=10 then
-- exit;
--end if;
exit when v_c=10;
v_c:=v_c+1;
end loop;
end;
/

declare
v_num number(3):=1;
begin
while v_num<=10 loop
dbms_output.put_line('This is '||v_num);
v_num:=v_num+1;
end loop;
end;
/

例二：
for 循环练习：
declare
--v_count number(3):=1;
begin
for v_count in 1..10 loop
dbms_output.put_line(v_count);
end loop;
end;
/

例三：
for反向输出：
declare
v_count number(3):=1;
begin
for v_count in reverse 1..10 loop
dbms_output.put_line(v_count);
end loop;
end;
/

例四：
循环插入数据；
begin
for i in 1..10 loop
insert into tbl_test values(i,'briup');
end loop;
commit；
end;
/

例五：
游标的使用：

loop遍历游标：
declare
--1.游标的声明
cursor my_cur is
select * from student;

--声明变量存放数据
v_rec student%rowtype;
begin
--2.打开游标
open my_cur;
--3.取数据
loop
fetch my_cur into v_rec;
exit when my_cur%NOTFOUND;
dbms_output.put_line(v_);
end loop;
if(my_cur%NOTFOUND) then
dbms_output.put_line('TRUE '||my_cur%ROWCOUNT);
end if;
--

4.关闭游标
close my_cur;
end;
/

例六：
for循环不用开始和关闭游标：
declare
cursor my_cur is select * from tbl_test;
begin
for v_rec in my_cur loop
dbms_output.put_line(v_);
end loop;
end;
/

例七：
while .. loop 循环遍历游标：
declare
cursor my_cursor is select * from student;
my_record student%rowtype;
begin
open my_cursor;
fetch my_cursor into my_record;

while my_cursor%FOUND loop
dbms_output.put_line(my_record.id || ' ' ||my_);
fetch my_cursor into my_record;
end loop;

if(my_cursor%NOTFOUND) then
dbms_output.put_line('CURSOR ROWCOUNT '||my_cursor%ROWCOUNT);
end if;

close my_cursor;
end;
/

2011-04-14下午
例八：
传参数的游标：
declare
cursor v_cur(v_canshu %type) is
select * from student where name like v_canshu;
v_rec student%rowtype;
begin
open v_cur('%i%');
fetch v_cur into v_rec;
while v_cur%FOUND loop
dbms_output.put_line(v_);
fetch v_cur into v_rec;
end loop;
end;
/

例九：
游标的另一种使用方式（可供jdbc调用的游标）：
declare
type my_cursor is ref cursor; --自定义游标类型
v_mycur my_cursor; --声明自定义游标类型的变量
v_rec tbl_test%rowtype;
begin
open v_mycur for select * from tbl_test; --打开游标
fetch v_mycur into v_rec;
while(v_mycur%FOUND) loop
dbms_output.put_line(v_rec.id);
fetch v_mycur into v_rec;
end loop;
close v_mycur;
end;
/

练习
1.在test1表中插入多条数据
2. 遍历test1表数据，如果id为奇数则插入test2表
如果为偶数则插入test3表
3.删除test1表中id能被5整出的记录

create table test1(id number(2));
create table test2(id number(2));
create table test3(id number(3));

1)begin
for v_num in 1..10 loop
insert into test1 values(v_num);
end loop;
end;

2)declare
v_rec test1%rowtype;
v_flag test1.id%type;
cursor v_cur is select id from test1;
begin
open v_cur;
fetch v_cur into v_rec;
while v_cur%FOUND loop
v_flag:=mod(v_rec.id,2);
if v_flag=0 then
insert into test3 values(v_rec.id);
commit;
else
insert into test2 values(v_rec.id);
commit;
end if;

fetch v_cur into v_rec;
end loop;
close v_cur;
end;
/
3)declare
v_rec test1%rowtype;
cursor v_cur is select id from test1;
begin
open v_cur;
fetch v_cur into v_rec;
while v_cur%found loop
if (mod(v_rec.id,5)=0) then
delete from test1 where id=v_rec.id;
end if;

fetch v_cur into v_rec;
end loop;
end;
/

例十：
异常处理：
declare
v_name %type;
begin
select name into v_name from student where rownum<3;
excep

tion --异常处理
when too_many_rows then
dbms_output.put_line('too many rows');
end;
/


例十一：
自定义异常：
declare
my_exception EXCEPTION;
v_name varchar2(20);
begin
RAISE my_exception;
select name into v_name from test where rownum<3;
exception
when my_exception then
dbms_output.put_line('my exception');
when others then
dbms_output.put_line('other exception');
end;
/

例十二：
存储过程(命名语句块)的使用： 注意：存储过程传参数时不能指定形参的长度，只有类型。
形参类型有三种:in. out. in out.默认为in

create or replace procedure my_procedure(v_char in varchar2)
is
--此处声明变量
begin
dbms_output.put_line(v_char);
end;

执行:1) exec my_procedure
2) execute my_procedure
3) call my_procedure()

查看错误：show error

例十三：
create or replace procedure my_procedure(v_id number,v_name out varchar2)
as
begin
select name into v_name from tbl_test
where id = v_id;
end;

调用方式如下：
一种传参方式（常用）：
declare
v_canshu varchar2(20);
begin
my_procedure(2,v_canshu);
dbms_output.put_line(v_canshu);
end;
/

另一种传参方式：
declare
v_name varchar2(20);
begin
my_procedure(v_name=>v_name,v_id=>2);
dbms_output.put_line(v_name);
end;
/


删除存储过程：
drop procedure pro_name;

查看存储过程数量：select count(1) from user_procedures;
查看表的数量： select count(1) from user_tables;
select text from all_source where name=upper('&plsql_name');

函数的使用：

注：函数和存储过程的区别：
函数有返回值，存储过程没有返回值。
存储过程调用本身是一个PL/SQL语句
函数调用则是表达式的一部分
函数可以在SQL语句中调用
函数与存储过程相同点：
都有名字
都有统一的形式：声明，执行与异常处理
都可以存储在数据库中
例十四：
函数的声明：
create or replace function my_function(v_id number)
return varchar2 --指定返回类型
as
v_result varchar2(10); --声明变量
begin
select name into v_result from tbl_test where id = v_id;
return v_result; --返回结果,至少写一个return
end;


函数的调用：
begin
dbms_output.put_line(my_function(2));
end;

函数的另一种调用：
select my_function(2) from dual;

函数的删除：
drop function function_name;


PACKAGE 包：

包名.方法名
包名.过程名
例如:
dbms_output.put_line('hello');

包有包头和包体组成；

例十五：
创建包头：
create or replace package my_package is
procedure package_procedure(v_id number);
end my_package;
/


根据包头创建包体：
create or replace package body my_package is
procedure package_procedure(v_id number)
is
v_name varchar2(

20);
begin
select name into v_name from tbl_test where id=v_id;
dbms_output.put_line('name '||v_name);
end package_procedure;
end my_package;
/

调用包中的存储过程：exec my_package.package_procedure

第二个例子：
create or replace package my_package is
procedure package_procedure(v_id number);
procedure package_pro(v_id number,v_name out %type);
end my_package;

create or replace package body my_package is
procedure package_procedure(v_id number) is
v_name %type;
begin
select name into v_name from test where id=v_id;
dbms_output.put_line(v_name);
end package_procedure;
procedure package_pro(v_id number,v_name out %type) is
begin
select name into v_name from test where id=v_id;
end package_pro;
end my_package;

declare
v_name %type;
begin
my_package.package_procedure(1);
my_package.package_pro(2,v_name);
dbms_output.put_line(v_name);
end;

2010-12-08上午

触发器：
例十六：
create or replace trigger my_trigger
before insert on tbl_test --在insert语句执行前触发触发器
declare
begin
dbms_output.put_line('Trigger runing ');
end;
/


访问数据：仅限于 for each row
:NEW 新插入或更新的数据(insert update)
:OLD 删除或更新前的数据(delete update)
查看触发器：select count(*) from user_triggers;
删除触发器：drop trigger my_trigger;

例十七：
--初始触发器
create or replace trigger my_trigger
before insert on tbl_test for each row --在insert语句执行前触发触发器
--declare
begin
dbms_output.put_line('ID:'||:NEW.id||' Name:'||:);
end;
/
例十八：
--认识after触发器
create table tbl_count(
num number);
当插入tbl_test数据成功后，更新下tbl_test1表;

create or replace trigger count_trigger
after insert on test for each row
declare
begin
update tbl_count set num=num+1;
insert into tbl_test1 values(:NEW.id,:);
dbms_output.put_line('Done');
end;
/

--认识before触发器
create or replace trigger my_count
before delete on test for each row
declare

begin
--删除数据之前备份数据到test1中
insert into test1 values(:OLD.id,:);
dbms_output.put_line('done');
end;

例十九：动态建表/删除表：
begin
for i in 1..10 loop
execute immediate
'create table test'||i||'(id number)';
end loop;
end;
/

begin
for i in 1..10 loop
execute immediate
'drop table test'||i;
end loop;
end;
/


创建定时调用
declare
v_code number;
begin
dbms_job.submit(v_code,'my_job_pro;',sysdate,'sysdate+1/(24*60*2)'); --半分钟
--v_code oracle自动生成
--my_job_pro 要执行的代码(必须加

上';'号)
--sysdate 第一次执行的时间
--第四个参数 上次和下次执行的时间

commit;
end;
/

例二十：
create or replace procedure my_procedure
as
begin
insert into test values(2,'aa');
end;
/

declare
v_code number;
begin
dbms_job.submit(v_code,'my_procedure;',sysdate,'sysdate+1/(24*60*5)');
commit;
dbms_output.put_line('Code:'||v_code);
end;
/

删除定时调用：
begin
dbms_job.remove(41);
end;
查看定时调用：
select job,what from user_jobs;

下午
例二十一： 分页:
select id,name from(
select id,name,rownum as c_row from tbl_test)
where c_row>5 and c_row<=10;

select * from table where rownum <30
minus
select * from table where rownum <20;

select * from table where rownum
between 1 and 30;

select id,name
from (select id,name,rownum num from table )
where num between 20 and 30;


例二十二：
创建包：
create or replace package my_pack is
type my_cursor is ref cursor;
end my_pack;

创建存储过程实现分页：
create or replace procedure my_procedure(
v_start number,
v_length number,
v_result out my_pack.my_cursor)
is
begin
open v_result for
select id,name from(
select id,name,rownum as c_row from tbl_test)
where c_row>v_start and c_row<=(v_start+v_length);
end;
/

执行查看分页效果:
declare
v_cursor my_pack.my_cursor;
v_rec tbl_test%rowtype;
begin
my_procedure(0,10,v_cursor);
loop
fetch v_cursor into v_rec;
exit when v_cursor%NOTFOUND;
dbms_output.put_line(v_rec.id);
end loop;
close v_cursor;
end;

ProcedureTest.java --JDBC使用存储过程实现分页




2010-12-09下午

2010-09-25 14:30执行
t_detail_25
13:00--14:00之间执行
v_date
to_number(to_char(v_date,'DD'))
trunc(sysdate) 取整到00：00
trunc(sysdate,'HH') 清零分钟 13:00整点
trunc(sysdate,'mm') 清零秒

所有出现在select后的没有使用组函数的列名必须出现在group by 后面;

PROCEDURE insertBlizzardDay(v_date date)
IS
v_day number :=TO_NUMBER(TO_CHAR(v_date-1/24,'DD'));
v_to_date varchar2(100):='to_date('''||
to_char(v_date,'yyyy_MM_dd HH24:mi:ss')
||''',''yyyy_MM_dd HH24:mi:ss'')';
v_sql varchar2(500);
BEGIN
v_sql :=
'insert into t_day_smile_'||v_day
||' select max(id),login_name,nas_ip,max(logout_date),sum(time_duration)
from t_detail_smile_'||v_day
||' where logout_date between trunc('||v_to_date||'-1/24,''HH'')'
||' and trunc('||v_to_date||',''HH'')'
||' group by login_name,nas_ip';

--dbms_output.put_line(v_sql);
EXECUTE IMMEDIATE v_sql;
END insertBlizzardDay;




v_date 传过来的日期

v_hour varchar2(200):=to_date(to_date());

v_day number:=to_nu

mber(to_char(v_date,'DD'));
v_hour varchar2(20):='to_date('''||to_char(v_date,''yyyy-mm-dd hh:mi:ss'')||''',''yyyy-mm-dd hh:mi:ss''))';

'insert into woss_day_'||v_day||
'select aaa_login_name,nas_ip,SUM(time_duration)
from woss_detail_'||v_day||
'where logout_date between trunc('||v_hour||'-1/24,''hh'')' ||'and trunc('|| v_hour||',''hh'')'||
'group by aaa_login_name,nas_ip'