/*set serveroutput on
declare
type emp_record_type is record(
name emp.ename%TYPE,
salary emp.sal%TYPE,
dno emp.deptno%TYPE
);
emp_record emp_record_type;
begin
select ename,sal,deptno into emp_record --放到记录
from emp where empno=&no;
dbms_output.put_line(emp_record.name);
end;*/
--select * from dept;
/* 记录类型
declare
dept_record dept%ROWTYPE;
begin
dept_record.deptno:=50;
dept_record.loc:='BEIJING';
dept_record.dname:='ADMINISTRATOR';
insert into dept values dept_record;
dept_record.deptno:=30;
dept_record.dname:='SALES';
dept_record.loc:='ShangHai';
update dept set row=dept_record where deptno=30;
dept_record.deptno:=50;
delete from dept where deptno=dept_record.deptno;
end; */
--索引表
/*set serveroutput on
declare
type ename_table_type is table of emp.ename%TYPE --建立索引表
index by BINARY_INTEGER;
ename_table ename_table_type;
begin
select ename into ename_table(-1) from emp --下标取行值
where empno=&no;
dbms_output.put_line('雇员名:'||ename_table(-1));
end; */
--使用varchar2
/*set serveroutput on
declare
type area_table_type is table of number
index by varchar2(10);
area_table area_table_type;
begin
area_table('北京'):=1; --建立索引表
area_table('上海'):=2;
area_table('广州'):=3;
dbms_output.put_line('第一个元素:'||area_table.first);--获取index ,以拼音排序
dbms_output.put_line('最后一个元素:'||area_table.last);
end; */
--嵌套表
/*declare
type ename_table_type is table of emp.ename%type;
ename_table ename_table_type;
begin
ename_table:=ename_table_type('MARY','MARY','MARY');
select ename into ename_table(2) from emp
where empno=&no;
dbms_output.put_line('雇员名1:'||ename_table(1));
dbms_output.put_line('雇员名2:'||ename_table(2));
dbms_output.put_line('雇员名3:'||ename_table(3));
end; */
--表列中使用嵌套表
/*create type phone_type is table of varchar2(20);--create type 创建嵌套表,自动生成构造方法
/
create table employee(
id number(4),
name varchar2(10),
sal number(6,2),
phone phone_type
)nested table phone store as phone_table;
--指定专门存储表
begin
insert into employee values(1,'scott',800,phone_type('0471-888888','13400000000'));--使用构造方法
end;*/
--检索嵌套列数据
/*set serveroutput on
declare
phone_table phone_type;
begin
select phone into phone_table from employee where id=1;--设置嵌套列变量
for i in 1..phone_table.count loop
dbms_output.put_line('电话号码:'||phone_table(i));
end loop;
end;*/
--更新
/*declare
phone_table phone_type:=phone_type('010-123456','12322222222');
begin
update employee set phone=phone_table where id=1;
end;*/
--变长数组 varray
/*declare
type ename_table_type is varray(20) of emp.ename%type; --设置容量
ename_table ename_table_type:=ename_table_type('mary');--初始化
begin
for i in 1..ename_table.count loop
select ename into ename_table(i) from emp where empno=&no;--更改
end loop;
for i in 1..ename_table.count loop
dbms_output.put_line('雇员名'||i||':'||ename_table(i));
end loop;
end;*/
--表列中使用 varray
/*create type phone_type is varray(20) of varchar2(20);
/
create table employee(
id number(4),
name varchar2(10),
sal number(6,2),
phone phone_type
);*/
--pl/sql 记录表 处理多行多列数据
/*declare
type emp_table_type is table of emp%rowtype --索引表 多行多列
index by binary_integer;
emp_table emp_table_type;
begin
select * into emp_table(1) from emp
where empno=&no;
dbms_output.put_line('雇员姓名:' ||emp_table(1).ename);
dbms_output.put_line('雇员工资:'||emp_table(1).sal);
end; */
--多级集合 多维数组 嵌套varray
/*declare
type al_varray_type is varray(10) of int;
type nal_varray_type is varray(10) of al_varray_type;
nv nal_varray_type:=nal_varray_type(
al_varray_type(58,100,102),
al_varray_type(55,6,73),
al_varray_type(2,4));
begin
dbms_output.put_line('显示元素:');
for i in 1..nv.count loop
for j in 1.. nv(i).count loop
dbms_output.put('nv{'||i||','||j||'}='||nv(i)(j)||' ');
end loop;
dbms_output.put_line('');
end loop;
end;
/*/
--嵌套表
/*declare
type al_varray_type is table of int;
type nal_varray_type is table of al_varray_type;
nv nal_varray_type:=nal_varray_type(
al_varray_type(58,100,102),
al_varray_type(55,6,73),
al_varray_type(2,4,6));
begin
dbms_output.put_line('显示元素:');
for i in 1..nv.count loop
for j in 1.. nv(i).count loop
dbms_output.put('nv{'||i||','||j||'}='||nv(i)(j)||' ');
end loop;
dbms_output.put_line('');
end loop;
end;
/*/
--多级索引表
/*declare
type al_varray_type is table of int
index by binary_integer;
type nal_varray_type is table of al_varray_type
index by binary_integer;
nv nal_varray_type;
begin
nv(1)(1):=10;
nv(1)(2):=20;
nv(2)(1):=30;
nv(2)(2):=40;
dbms_output.put_line('显示元素:');
for i in 1..nv.count loop
for j in 1.. nv(i).count loop
dbms_output.put('nv{'||i||','||j||'}='||nv(i)(j)||' ');
end loop;
dbms_output.put_line('');
end loop;
end;
/*/
/*declare
type ename_table_type is table of emp.ename%type;
ename_table ename_table_type;--:=ename_table_type('kevin','lee'); --索引表无需初始化
begin
if ename_table.exists(1) then --用于pl/sql
ename_table(1):='scott';
dbms_output.put_line('更改后:'||ename_table(1));
else
dbms_output.put_line('要初始化表元素');
end if;
end;
/*/
--批量绑定测试
/*drop table demo;
commit;
create table demo(
id number(6) primary key,
name varchar2(10)
);
commit;
declare
type id_table_type is table of demo.id%type
index by binary_integer;
type name_table_type is table of demo.name%type
index by binary_integer;
id_table id_table_type;
name_table name_table_type;
start_time number(10);
end_time number(10);
begin
for i in 1..50000 loop
id_table(i):=i;
name_table(i):='Name'||i; --to_char?
end loop;
start_time:=dbms_utility.get_time;
--for i in 1..id_table.count loop
forall i in 1..id_table.count --使用批量绑定特征
insert into demo values(id_table(i),name_table(i));
--end loop;
end_time:=dbms_utility.get_time;
dbms_output.put_line('总计时间(秒):'||(end_time-start_time)/100);--时间??
commit;
end;
/*/
/*
create table new_demo as --空表
select * from demo where 1=0;
commit;
declare
type id_table_type is table of demo.id%type;
type name_table_type is table of demo.name%type;
id_table id_table_type;
name_table name_table_type;
type index_pointer_type is table of pls_integer; --创建指针表
index_pointer index_pointer_type;
begin
select * bulk collect into id_table,name_table --??
from demo;
index_pointer:=index_pointer_type(6,8,10);
forall i in values of index_pointer
insert into new_demo values(id_table(i),name_table(i));
commit;
end;
/*/
--显示游标
/*declare
cursor emp_cursor is
select ename,sal from emp where deptno=10; --create
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
open emp_cursor; --open
loop
fetch emp_cursor into v_ename,v_sal; --fetch(loop)
exit when emp_cursor%NOTFOUND;
dbms_output.put_line(v_ename||':'||v_sal);
end loop;
close emp_cursor; --close
end;
/*/
--显示游标 提取到collect
/*declare
cursor emp_cursor is
select ename from emp where deptno=10; --create
type ename_table_type is table of emp.ename%type;
ename_table ename_table_type;
begin
open emp_cursor; --open
fetch emp_cursor bulk collect into ename_table; --fetch
for i in 1..ename_table.count loop
dbms_output.put_line(ename_table(i));
end loop;
close emp_cursor; --close
end;
/*/
/*
declare
type name_array_type is varray(5) of varchar2(10);
name_array name_array_type;
cursor emp_cursor is select ename from emp;
lim int:=5;
v_count int:=0;
begin
open emp_cursor;
loop
fetch emp_cursor bulk collect into name_array limit lim;
dbms_output.put('雇员名:');
for i in 1..(emp_cursor%rowcount-v_count) loop
dbms_output.put(name_array(i)||' ');
end loop;
dbms_output.put_line(to_char(v_count));
v_count:=emp_cursor%rowcount; --上一次的游标位置
exit when emp_cursor%notfound;
end loop;
close emp_cursor;
end;
/*/
--游标变量 建议 可带参数 当用于 update delete
/*declare
cursor emp_cursor(no number) is select ename,sal from emp where deptno=no --for update 加锁
for update;
emp_record emp_cursor%rowtype;
begin
open emp_cursor(10);
loop
fetch emp_cursor into emp_record;
exit when emp_cursor%notfound;
update emp set sal=sal-200 where current of emp_cursor; --current of 当前行
dbms_output.put_line('雇员姓名:'||emp_record.ename||',雇员工资:'||emp_record.sal);
end loop;
close emp_cursor;
end;
/*/
--使用for语句 简化开发
declare
cursor emp_cursor(no number) is select ename,sal from emp where deptno=no;
begin
for emp_record in emp_cursor(10) loop --不用声明 也可不用游标 替换为子查询 (select * from emp)
dbms_output.put_line('第'||emp_cursor%rowcount||'个雇员:'||emp_record.ename);
end loop;
end;
/
declare
type emp_record_type is record(
name emp.ename%TYPE,
salary emp.sal%TYPE,
dno emp.deptno%TYPE
);
emp_record emp_record_type;
begin
select ename,sal,deptno into emp_record --放到记录
from emp where empno=&no;
dbms_output.put_line(emp_record.name);
end;*/
--select * from dept;
/* 记录类型
declare
dept_record dept%ROWTYPE;
begin
dept_record.deptno:=50;
dept_record.loc:='BEIJING';
dept_record.dname:='ADMINISTRATOR';
insert into dept values dept_record;
dept_record.deptno:=30;
dept_record.dname:='SALES';
dept_record.loc:='ShangHai';
update dept set row=dept_record where deptno=30;
dept_record.deptno:=50;
delete from dept where deptno=dept_record.deptno;
end; */
--索引表
/*set serveroutput on
declare
type ename_table_type is table of emp.ename%TYPE --建立索引表
index by BINARY_INTEGER;
ename_table ename_table_type;
begin
select ename into ename_table(-1) from emp --下标取行值
where empno=&no;
dbms_output.put_line('雇员名:'||ename_table(-1));
end; */
--使用varchar2
/*set serveroutput on
declare
type area_table_type is table of number
index by varchar2(10);
area_table area_table_type;
begin
area_table('北京'):=1; --建立索引表
area_table('上海'):=2;
area_table('广州'):=3;
dbms_output.put_line('第一个元素:'||area_table.first);--获取index ,以拼音排序
dbms_output.put_line('最后一个元素:'||area_table.last);
end; */
--嵌套表
/*declare
type ename_table_type is table of emp.ename%type;
ename_table ename_table_type;
begin
ename_table:=ename_table_type('MARY','MARY','MARY');
select ename into ename_table(2) from emp
where empno=&no;
dbms_output.put_line('雇员名1:'||ename_table(1));
dbms_output.put_line('雇员名2:'||ename_table(2));
dbms_output.put_line('雇员名3:'||ename_table(3));
end; */
--表列中使用嵌套表
/*create type phone_type is table of varchar2(20);--create type 创建嵌套表,自动生成构造方法
/
create table employee(
id number(4),
name varchar2(10),
sal number(6,2),
phone phone_type
)nested table phone store as phone_table;
--指定专门存储表
begin
insert into employee values(1,'scott',800,phone_type('0471-888888','13400000000'));--使用构造方法
end;*/
--检索嵌套列数据
/*set serveroutput on
declare
phone_table phone_type;
begin
select phone into phone_table from employee where id=1;--设置嵌套列变量
for i in 1..phone_table.count loop
dbms_output.put_line('电话号码:'||phone_table(i));
end loop;
end;*/
--更新
/*declare
phone_table phone_type:=phone_type('010-123456','12322222222');
begin
update employee set phone=phone_table where id=1;
end;*/
--变长数组 varray
/*declare
type ename_table_type is varray(20) of emp.ename%type; --设置容量
ename_table ename_table_type:=ename_table_type('mary');--初始化
begin
for i in 1..ename_table.count loop
select ename into ename_table(i) from emp where empno=&no;--更改
end loop;
for i in 1..ename_table.count loop
dbms_output.put_line('雇员名'||i||':'||ename_table(i));
end loop;
end;*/
--表列中使用 varray
/*create type phone_type is varray(20) of varchar2(20);
/
create table employee(
id number(4),
name varchar2(10),
sal number(6,2),
phone phone_type
);*/
--pl/sql 记录表 处理多行多列数据
/*declare
type emp_table_type is table of emp%rowtype --索引表 多行多列
index by binary_integer;
emp_table emp_table_type;
begin
select * into emp_table(1) from emp
where empno=&no;
dbms_output.put_line('雇员姓名:' ||emp_table(1).ename);
dbms_output.put_line('雇员工资:'||emp_table(1).sal);
end; */
--多级集合 多维数组 嵌套varray
/*declare
type al_varray_type is varray(10) of int;
type nal_varray_type is varray(10) of al_varray_type;
nv nal_varray_type:=nal_varray_type(
al_varray_type(58,100,102),
al_varray_type(55,6,73),
al_varray_type(2,4));
begin
dbms_output.put_line('显示元素:');
for i in 1..nv.count loop
for j in 1.. nv(i).count loop
dbms_output.put('nv{'||i||','||j||'}='||nv(i)(j)||' ');
end loop;
dbms_output.put_line('');
end loop;
end;
/*/
--嵌套表
/*declare
type al_varray_type is table of int;
type nal_varray_type is table of al_varray_type;
nv nal_varray_type:=nal_varray_type(
al_varray_type(58,100,102),
al_varray_type(55,6,73),
al_varray_type(2,4,6));
begin
dbms_output.put_line('显示元素:');
for i in 1..nv.count loop
for j in 1.. nv(i).count loop
dbms_output.put('nv{'||i||','||j||'}='||nv(i)(j)||' ');
end loop;
dbms_output.put_line('');
end loop;
end;
/*/
--多级索引表
/*declare
type al_varray_type is table of int
index by binary_integer;
type nal_varray_type is table of al_varray_type
index by binary_integer;
nv nal_varray_type;
begin
nv(1)(1):=10;
nv(1)(2):=20;
nv(2)(1):=30;
nv(2)(2):=40;
dbms_output.put_line('显示元素:');
for i in 1..nv.count loop
for j in 1.. nv(i).count loop
dbms_output.put('nv{'||i||','||j||'}='||nv(i)(j)||' ');
end loop;
dbms_output.put_line('');
end loop;
end;
/*/
/*declare
type ename_table_type is table of emp.ename%type;
ename_table ename_table_type;--:=ename_table_type('kevin','lee'); --索引表无需初始化
begin
if ename_table.exists(1) then --用于pl/sql
ename_table(1):='scott';
dbms_output.put_line('更改后:'||ename_table(1));
else
dbms_output.put_line('要初始化表元素');
end if;
end;
/*/
--批量绑定测试
/*drop table demo;
commit;
create table demo(
id number(6) primary key,
name varchar2(10)
);
commit;
declare
type id_table_type is table of demo.id%type
index by binary_integer;
type name_table_type is table of demo.name%type
index by binary_integer;
id_table id_table_type;
name_table name_table_type;
start_time number(10);
end_time number(10);
begin
for i in 1..50000 loop
id_table(i):=i;
name_table(i):='Name'||i; --to_char?
end loop;
start_time:=dbms_utility.get_time;
--for i in 1..id_table.count loop
forall i in 1..id_table.count --使用批量绑定特征
insert into demo values(id_table(i),name_table(i));
--end loop;
end_time:=dbms_utility.get_time;
dbms_output.put_line('总计时间(秒):'||(end_time-start_time)/100);--时间??
commit;
end;
/*/
/*
create table new_demo as --空表
select * from demo where 1=0;
commit;
declare
type id_table_type is table of demo.id%type;
type name_table_type is table of demo.name%type;
id_table id_table_type;
name_table name_table_type;
type index_pointer_type is table of pls_integer; --创建指针表
index_pointer index_pointer_type;
begin
select * bulk collect into id_table,name_table --??
from demo;
index_pointer:=index_pointer_type(6,8,10);
forall i in values of index_pointer
insert into new_demo values(id_table(i),name_table(i));
commit;
end;
/*/
--显示游标
/*declare
cursor emp_cursor is
select ename,sal from emp where deptno=10; --create
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
open emp_cursor; --open
loop
fetch emp_cursor into v_ename,v_sal; --fetch(loop)
exit when emp_cursor%NOTFOUND;
dbms_output.put_line(v_ename||':'||v_sal);
end loop;
close emp_cursor; --close
end;
/*/
--显示游标 提取到collect
/*declare
cursor emp_cursor is
select ename from emp where deptno=10; --create
type ename_table_type is table of emp.ename%type;
ename_table ename_table_type;
begin
open emp_cursor; --open
fetch emp_cursor bulk collect into ename_table; --fetch
for i in 1..ename_table.count loop
dbms_output.put_line(ename_table(i));
end loop;
close emp_cursor; --close
end;
/*/
/*
declare
type name_array_type is varray(5) of varchar2(10);
name_array name_array_type;
cursor emp_cursor is select ename from emp;
lim int:=5;
v_count int:=0;
begin
open emp_cursor;
loop
fetch emp_cursor bulk collect into name_array limit lim;
dbms_output.put('雇员名:');
for i in 1..(emp_cursor%rowcount-v_count) loop
dbms_output.put(name_array(i)||' ');
end loop;
dbms_output.put_line(to_char(v_count));
v_count:=emp_cursor%rowcount; --上一次的游标位置
exit when emp_cursor%notfound;
end loop;
close emp_cursor;
end;
/*/
--游标变量 建议 可带参数 当用于 update delete
/*declare
cursor emp_cursor(no number) is select ename,sal from emp where deptno=no --for update 加锁
for update;
emp_record emp_cursor%rowtype;
begin
open emp_cursor(10);
loop
fetch emp_cursor into emp_record;
exit when emp_cursor%notfound;
update emp set sal=sal-200 where current of emp_cursor; --current of 当前行
dbms_output.put_line('雇员姓名:'||emp_record.ename||',雇员工资:'||emp_record.sal);
end loop;
close emp_cursor;
end;
/*/
--使用for语句 简化开发
declare
cursor emp_cursor(no number) is select ename,sal from emp where deptno=no;
begin
for emp_record in emp_cursor(10) loop --不用声明 也可不用游标 替换为子查询 (select * from emp)
dbms_output.put_line('第'||emp_cursor%rowcount||'个雇员:'||emp_record.ename);
end loop;
end;
/
发表评论
-
adb命令
2012-08-09 14:28 1292ADB install app.apk ADB shell ... -
sqlite3
2012-08-09 13:02 921.mode column; .header on; al ... -
Oracle 10G:PL/SQL正规表达式(正则表达式)
2012-03-23 18:47 750Oracle 10G:PL/SQL正规表达式(正则表达式) ... -
生产区重新部署web
2012-03-09 22:38 660http://middleware123.com/weblog ... -
procedure 学习
2012-03-09 10:54 757http://hi.baidu.com/test2704/bl ... -
dblink
2012-03-09 10:05 4687三. 创建DBLINK的方法: 1. create p ... -
oracle 子分区
2012-01-30 10:43 3874--1.创建表空间 create tablespace ... -
oracle execute immediate
2012-01-30 10:42 6774解析并马上执行动态语句 ,或非运行时创建的pl/sql ... -
厅表tp_ac01
2011-10-25 20:18 0create table tp_ac01 as select ... -
oracle 添加定时任务
2011-09-16 16:31 922--submit select * from repor ... -
decode和case的参数区别 timestamp date
2011-08-23 14:58 862SELECT DECODE('AA ', 'AA', '= ... -
oracle 用一个表更新另一个表
2011-07-11 10:19 29881.生成临时表 update (select a.aaa,b. ... -
semi join anti-join
2011-07-06 17:24 0使用in exists 用semi-join set aut ... -
终止oracle正在执行的存储过程
2011-06-14 14:13 4837--查看运行的procedure (表提供对象在libra ... -
oracle 并行
2011-05-13 14:52 0多的的地对地导弹的 -
oracle 级联查询 级联求和 汇总
2011-04-29 15:12 2611级联查询 select level||'层',lpad ... -
oracle中文日期 Oracle数据库中的''与NULL的
2011-04-13 10:48 1196转自:http://micki.blogbus.com/log ... -
[转]无法启动oracleDBConsole的解决方案之一
2011-02-21 22:24 928http://www.iteye.com/topic/6472 ... -
oracle触发器与存储过程(第10章)
2011-02-09 17:09 0--包调用方式 biology_degrees integer ... -
pk_imp_benefit(未完成)
2011-02-09 11:26 0n_sql := 'select ffqsny,yhzh00 ...
相关推荐
oracle存储过程学习经典入门 非常好的 初学者必看
oracle存储过程学习资料 oracle存储过程学习资料 oracle存储过程学习资料
oracle存储过程学习
ORACLE存储过程 学习源码,从简单到深奥,一共有30个文件。
存储过程学习、个人随笔、基本语法和流程控制。仅供参考
快速学习函数创建和练习。 创建函数、存储过程、创建过程、调用存储过程、AUTHID、PRAGMA AUTONOMOUS_TRANSACTION、开发存储过程步骤、删除过程和函数、过程与函数的比较
oracle存储过程学习经典[语法+实例+调用].doc
oracle 存储过程学习经典,主要适合初学oracle存储过程的.
oracle存储过程学习经典[语法+实例+调用].pdf
oracle存储过程学习经典(实例),适合刚学习存储过程的广大程序员们
1.基本结构 CREATE OR REPLACE PROCEDURE 存储过程名字 ( 参数1 IN NUMBER, 参数2 IN NUMBER ) IS 变量1 INTEGER :=0; 变量2 DATE; BEGIN END 存储过程名字
不经典的我也不会发上来。我是推荐给大家。oracle存储过程学习经典语法+实例+调用。
该文档比较详细地介绍了Oracle存储过程,有例子
oracle存储过程学习经典oracle存储过程学习经典
oracle存储过程学习经典入门