select * from scott.emp;
==============for 循环=======================
declare
empno NUMBER(4,0);
ename VARCHAR2(10 BYTE);
sal NUMBER(7,2);
begin
for REC in
(select empno, ename, sal from scott.emp)
loop
begin
empno := REC.empno;
ename := REC.ename;
sal := REC.sal;
/*---------------------------------写入一条明细开始------------------------------*/
if NVL(empno,0) <> 0 then
begin
dbms_output.put_line('记录： ' || empno || ' ' || ename || ' ' || sal );
end;
end if;
/*---------------------------------写入一条明细开始------------------------------*/
end;
end loop;
end;
/
--===============带参数的游标==================--
DECLARE
dept_code emp.deptno%TYPE; --声明列类型变量三个
emp_code emp.empno%TYPE;
emp_name emp.ename%TYPE;
CURSOR emp_cur(deptparam NUMBER) IS
SELECT empno, ename FROM EMP WHERE deptno = deptparam; --声明显示游标
BEGIN
dept_code := &部门编号; --请用户输入想查看的部门编号
OPEN emp_cur(dept_code); --打开游标
LOOP
--死循环
FETCH emp_cur
INTO emp_code, emp_name; --提取游标值赋给上面声明的变量
EXIT WHEN emp_cur%NOTFOUND; --如果游标里没有数据则退出循环
DBMS_OUTPUT.PUT_LINE(emp_code || '' || emp_name); --输出查询
END LOOP;
CLOSE emp_cur; --关闭游标
END;
--=================REF游标==================--
ACCEPT tab FROMPT '你想查看什么信息?员工(E)或部门信息(D):'; --使用ACCEPT命令弹出对话框让用户输入数据
DECLARE
TYPE refcur_t IS REF CURSOR; --声明REF游标类型
refcur refcur_t; --声明REF游标类型的变量
pid NUMBER;
p_name VARCHAR2(100);
selection VARCHAR2(1) := UPPER(SUBSTR('&tab', 1, 1)); --截取用户输入的字符串并转换为大写
BEGIN
IF selection = 'E' THEN
--如果输入的是'E',则打开refcurr游标,并将员工表查询出来赋值给此游标
OPEN refcur FOR
SELECT EMPNO ID, ENAME NAME FROM EMP;
DBMS_OUTPUT.PUT_LINE('=====员工信息=====');
ELSIF selection = 'D' THEN
--如果输入是'D',则打开部门表
OPEN refcur FOR
SELECT deptno id, dname name FROM DEPT;
DBMS_OUTPUT.PUT_LINE('=====部门信息======');
ELSE
--否则返回结束
DBMS_OUTPUT.PUT_LINE('请输入员工信息(E)或部门信息(D)');
RETURN;
END IF;
FETCH refcur
INTO pid, p_name; --提取行
WHILE refcur%FOUND LOOP
DBMS_OUTPUT.PUT_LINE('#' || pid || ':' || p_name);
FETCH refcur
INTO pid, p_name;
END LOOP;
CLOSE refcur; --关闭游标
END;
--===================动态SQL=================--
VARIABLE maxsal NUMBER; --声明变量
EXECUTE :maxsal := 2500; --执行引用并给变量赋值
DECLARE
r_emp EMP%ROWTYPE; --声明一个行类型变量
TYPE c_type IS REF CURSOR; --声明REF游标类型
cur c_type; --声明REF游标类型的变量
p_salary NUMBER; --声明一个标量变量
BEGIN
p_salary := :maxsal; --引用变量
--使用USING语句将引用到的值传给动态SQL语句'SAL >: 1'中的'1'
OPEN cur FOR 'SELECT * FROM EMP WHERE SAL >: 1 ORDER BY SAL DESC'
USING p_salary;
DBMS_OUTPUT.PUT_LINE('薪水大于' || p_salary || '的员工有:');
LOOP
FETCH cur
INTO r_emp;
EXIT WHEN cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('编号:' || r_emp.empno || '姓名:' || r_emp.ename ||
'薪水:' || r_emp.sal);
END LOOP;
CLOSE cur; --关闭游标
END;
-- 例子：
CREATE OR REPLACE PROCEDURE x_ne_change
AS
CURSOR cur_new
IS
SELECT int_id, omc_id || ':' || msc_id || ':' || bsc_id AS related_id,
omc_id, msc_id, bsc_id, ne_name, cell_id, lac, freq_band,
trx_count, tch, sdcch, gprs_enabled, gprs_trx, dedicated_pdch,
max_pdch, device_type, software_version, dumpfre_type, site_no,
cell_no, rac, ncc, bcc, sms_cb_used, bsc_omc_int_id, omc_int_id,
TIMESTAMP
FROM appuser.K_C_CELL
WHERE TIMESTAMP = '2004-04-23 8' AND cell_id < 2000;
CURSOR cur_old (c_no NUMBER)
IS
SELECT int_id, omc_id || ':' || msc_id || ':' || bsc_id AS related_id,
omc_id, msc_id, bsc_id, ne_name, cell_id, lac, freq_band,
trx_count, tch, sdcch, gprs_enabled, gprs_trx, dedicated_pdch,
max_pdch, device_type, software_version, dumpfre_type, site_no,
cell_no, rac, ncc, bcc, sms_cb_used, bsc_omc_int_id, omc_int_id,
TIMESTAMP
FROM appuser.K_C_CELL
WHERE TIMESTAMP = '2004-04-21 6' AND cell_id = c_no;
BEGIN
FOR v_new IN cur_new
LOOP
FOR v_old IN cur_old (v_new.cell_id)
LOOP
BEGIN
IF v_new.related_id <>; v_old.related_id
THEN
INSERT INTO TEST_NE_CHANGE
(omc_id, omc_int_id, ne_id,
old_value, now_value,
modify_item, modify_time
)
VALUES (v_new.omc_id, v_new.omc_int_id, v_new.cell_id,
v_old.related_id, v_new.related_id,
'related_id', v_new.TIMESTAMP
);
COMMIT;
END IF;
IF v_new.tch <>; v_old.tch
THEN
INSERT INTO TEST_NE_CHANGE
(omc_id, omc_int_id, ne_id,
old_value, now_value, modify_item, modify_time
)
VALUES (v_new.omc_id, v_new.omc_int_id, v_new.cell_id,
v_old.tch, v_new.tch, 'TCH', v_new.TIMESTAMP
);
COMMIT;
END IF;
END;
END LOOP;
END LOOP;
END;
--- 多行数据提取
declare
cursor cur_tsalary is
select employeeid,positionid from tsalary whererownum < 10;
type rec_tsalary isrecord(
employeeid tsalary.employeeid%type,
positionid tsalary.positionid%type);
type all_rec_tsalary_type istableof rec_tsalary;
all_rec_tsalary all_rec_tsalary_type;
begin
--一次处理所有
fetch cur_tsalary bulkcollectinto all_rec_tsalary limit5;
for i in1..all_rec_tsalary.countloop dbms_output.put_line(all_rec_tsalary(i).employeeid||all_rec_tsalary(i).positionid);
endloop;
--使用limit分批提出大量数据
open cur_tsalary;
loop
fetch cur_tsalary bulkcollectinto all_rec_tsalary limit5;
for i in1..all_rec_tsalary.countloop dbms_output.put_line(all_rec_tsalary(i).employeeid||all_rec_tsalary(i).positionid);
endloop;
exitwhen cur_tsalary%notfound;
endloop;
close cur_tsalary;
end;
-- 例六：%NotFound
BEGIN
DELETE FROM Rs_Employees
Where HeTongId = ' WL-090001 ' ;
if sql % Notfound then
Dbms_Output.put_line( ' 没有找到要删除的记录 ' );
else
Dbms_Output.put_line( ' 已删除记录 ' );
end if ;
END ;
-- 例七：%RowCount，查询记录行数
Declare
v_name Rs_Employees.Name % type;
BEGIN
SELECT Name Into v_Name
FROM Rs_Employees
Where HeTongId = ' WL-090010 ' ;
if sql % RowCount > 0 Then
Dbms_Output.put_line( ' 已从表中选择行,Name为： ' || v_Name);
else
Dbms_Output.put_line( ' 从表中未选择行 ' );
end if ;
END ;
-- 再演示以下代码
BEGIN
DELETE FROM Rs_Employees
Where HeTongId <= ' WL-090010 ' ;
Dbms_Output.put_line( ' 已从表中删除 ' || To_Char(sql % RowCount ) || ' 条记录 ' );
END ;
-- 例八：显式游标
-- 以下示例在所有游标的记录中的Name字段中加一字串
-- 同时在此示范了如何使用%NotFound属性
-- set serveroutput on;
Declare
v_Id Rs_Employees.Hetongid % type;
v_name Rs_Employees.Name % type;
v_Count Number : = 0 ;
Cursor MyCur Is
SELECT HetongId,Name FROM Rs_Employees
Where HeTongId <= ' WL-090010 ' ;
BEGIN
-- 打开游标
Open MyCur;
-- 进入循环
Loop
Fetch MyCur Into v_id,v_name;
Exit When MyCur % NotFound;
Update Rs_Employees
Set Name = Name || ' X '
Where HeTongId = v_Id;
v_Count : = v_Count + 1 ;
End Loop;
Dbms_Output.put_line( ' 已更新 ' || v_Count || ' 行 ' );
END ;
-- 例10：以下示范%RowCount和%IsOpen，同时示范了%RowType的使用
-- 例10：以下示例%RowCount
Declare
v_Row Rs_Employees % RowType;
Cursor MyCur Is
SELECT * FROM Rs_Employees
Where HeTongId <= ' WL-090010 ' ;
BEGIN
if Not MyCur % IsOpen then
Dbms_Output.put_line( ' 游标未打开 ' );
-- 打开游标
Open MyCur;
end if ;
-- 进入循环
Loop
Fetch MyCur Into v_row;
Exit When MyCur % NotFound;
Dbms_Output.put_line( ' 当前已取得 ' || MyCur % RowCount || ' 行 ' );
Dbms_Output.put_line( ' 姓名： ' || v_row.Name || ' ' || ' 合同号： ' || v_row.HeTongId);
Dbms_Output.put_line( '' );
End Loop;
Dbms_Output.put_line( ' 总共已取得 ' || MyCur % RowCount || ' 行 ' );
if MyCur % IsOpen then
Dbms_Output.put_line( ' 游标已打开 ' );
Close MyCur;
end if ;
if Not MyCur % IsOpen then
Dbms_Output.put_line( ' 游标已关闭 ' );
end if ;
END ;
-- 例12：查询嵌套表中数据的游标
-- 1 创建类型
CREATE OR REPLACE TYPE emp_type As Object
(eno number ,
ename varchar2 ( 20 ),
esal number );
-- 2 使用Table of 子句创建Table类型
CREATE TYPE emp_nt AS Table Of emp_type;
-- 3 使用emp_nt数据类型创建myemp表
CREATE TABLE myemp
(deptno number ,
edet emp_nt)
NESTED TABLE edet Store As myemployee;
-- 4 初始化myemp的数据
Insert Into myemp values
( 10 ,emp_nt(emp_type( 1000 , ' James ' , 10000 ),
emp_type( 1001 , ' Daniel ' , 20000 )));
Commit ;
-- 执行下列代码
Declare
sal number ;
ena varchar2 ( 20 );
Cursor MyCur Is
SELECT a.esal,a.ename
FROM the
( Select edet From myemp
Where deptno = 10 ) a;
BEGIN
-- 打开游标
Open MyCur;
-- 进入循环
Loop
Fetch MyCur Into sal,ena;
Exit When MyCur % NotFound;
Dbms_Output.put_line(ena || ' ' || sal);
End Loop;
Close MyCur;
END ;
-- 例13：此例改自例10，示范循环游标的用法
Declare
Cursor MyCur Is
SELECT * FROM Rs_Employees
Where HeTongId <= ' WL-090010 ' ;
BEGIN
For tmp_cur In MyCur
Loop
Dbms_Output.put_line( ' 当前已取得 ' || MyCur % RowCount || ' 行 ' );
Dbms_Output.put_line( ' 姓名： ' || tmp_cur.Name || ' ' || ' 合同号： ' || tmp_cur.HeTongId);
End Loop;
-- 在这种情况下，下面这条语句不能执行
-- Dbms_Output.put_line('当前已取得'||MyCur%RowCount||'行');
END ;
-- 再示范带参数的游标
Declare
Cursor MyCur(m_HeTongId Rs_Employees.Hetongid % type) Is
SELECT * FROM Rs_Employees
Where HeTongId <= m_HeTongId;
BEGIN
For tmp_cur In MyCur( ' WL-090020 ' )
Loop
Dbms_Output.put_line( ' 当前已取得 ' || MyCur % RowCount || ' 行 ' );
Dbms_Output.put_line( ' 姓名： ' || tmp_cur.Name || ' ' || ' 合同号： ' || tmp_cur.HeTongId);
End Loop;
-- 在这种情况下，下面这条语句不能执行
-- Dbms_Output.put_line('当前已取得'||MyCur%RowCount||'行');
END ;
-- 再示范以下写法
-- 在循环游标中使用查询
Declare
v_HeTongId Rs_Employees.Hetongid % type;
BEGIN
v_HeTongId : = ' WL-090020 ' ;
For tmp_cur In ( SELECT * FROM Rs_Employees
Where HeTongId <= v_HeTongId)
Loop
Dbms_Output.put_line( ' 姓名： ' || tmp_cur.Name || ' ' || ' 合同号： ' || tmp_cur.HeTongId);
End Loop;
END ;
-- 例15：示范游标变量
-- 此例要在Command window或Sql Plus中示范，结果会有点区别
-- SET SERVEROUTPUT ON;
Declare
TYPE r1_cur IS REF CURSOR ;
var1 r1_cur;
no varchar2 ( 20 );
v_czy sc_chukudan.czy % type;
v_pid sc_chukudanDetail.Productid % type;
BEGIN
no : = ' &你选择的 ' ;
IF UPPER (no) = ' MASTER ' then
OPEN var1 For
Select Czy FROM sc_chukudan
Where ChuKuDanId = ' SCKD04020001 ' ;
FETCH var1 into v_Czy;
Dbms_Output.put_line( ' 操作员是： ' || v_czy);
CLOSE var1;
ELSE
OPEN var1 For
Select Productid FROM sc_chukudanDetail
Where ChuKuDanId = ' SCKD04020001 ' ;
LOOP
FETCH var1 into v_pid;
EXIT WHEN var1 % NotFound;
Dbms_Output.put_line( ' 生产通知单ID是： ' || v_pid);
END LOOP;
CLOSE var1;
END IF ;
END ;
-- 例16: 游标中的更新和删除
-- 此例改自例8
Declare
-- 当打开此游标，将锁住了相关记录
Cursor MyCur Is
SELECT Name FROM Rs_Employees
Where HeTongId <= ' WL-090010 '
For Update OF Name;
BEGIN
For tmp_cur in MyCur
Loop
Update Rs_Employees
Set Name = Name || ' X '
Where Current of MyCur;
End Loop;
END ;
-- 示例一：Create Table 命令，区别较小
Create Table vendor_master
(
vencode varchar2 ( 5 ),
venname varchar2 ( 20 ),
venadd1 varchar2 ( 20 ),
venadd2 varchar2 ( 20 ),
venadd3 varchar2 ( 20 )
)
-- 示例二：Alter Table Modify 命令，区别较大
Alter Table vendor_master Modify (venname varchar2 ( 25 ))
-- 示例三：Alter Table Add 命令，区别较小，主要是数据类型
Alter Table vendor_master
add (tel_no number ( 12 ),
tngst_no number ( 12 ))
-- 示例四：Drop Column 命令：完全一样
Alter Table vendor_master Drop Column tngst_no
-- 示例五：Oracle 独有
alter Table vendor_master set unused(tel_no)
-- 示例六：Truncate Table命令 ：完全一样
truncate table vendor_master
-- 示例八：Desc命令：完全不一样
Desc vendor_master
-- 示例九：Drop Table 命令：完全一样
drop table vendor_master
-- 示例10：Insert命令
Insert into vendor_master values ( ' v001 ' , ' John smith ' , ' 11 E main st ' , ' West Avenue ' , ' alabama ' , 1234567 )
-- 以下这种方法只在Orace中有效，l但不推荐使用此方法
Insert into vendor_master values ( ' &vencode ' , ' &venname ' , ' &venadd1 ' , ' &venadd2 ' , ' &venadd3 ' , & telno)
-- 示例15：Select命令
select * from vendor_master
-- 示例20：Update命令 ：注意大小写
update vendor_master set tel_no = 987654 where vencode = ' V001 ' -- 'v001'
-- 示例24：Grant 和 Revoke命令
grant all on vendor_master to sys
revoke all on vendor_master from sys
**********************************************************************************************
// 用户
connect system / manager @ydgl ;
-- 删除已有的用户和表空间
-- drop tablespace freemandatabase;
-- drop tablespace tempfreemandatabase;
-- 创建表空间
create tablespace FreeManDataBase
datafile ' c:/FreeManDataBase.ora '
size 25M;
-- 创建临时表空间
create temporary tablespace tempFreeManDataBase
tempfile ' c:/tempFreeManDataBase.ora '
size 25M;
-- 创建用户
create user zong identified by " 123456 "
default tablespace FreeManDataBase
temporary tablespace tempFreeManDataBase;
-- 赋权限
grant connect to zong;
grant resource to zong;
grant dba to aaa;
-- 登录
connect zong / 123456 @ydgl ;
create table zong.ccc(bh varchar2 ( 10 ), xm varchar2 ( 10 ), age number , salary number , birthday date)
-- 事务处理
insert into zong.ccc(bh,xm,age,salary,birthday) values ( ' 000 ' , ' aaa ' , 56 , 12345 ,to_date( ' 1978-1-1 ' , ' yyyy-mm-dd ' ));
insert into zong.ccc(bh,xm,age,salary,birthday) values ( ' 004 ' , ' peng ' , 24 , 3456.3 ,to_date( ' 1976-1-1 ' , ' yyyy-mm-dd ' ));
savepoint ppp;
insert into zong.ccc(bh,xm,age,salary,birthday) values ( ' 005 ' , ' cao ' , 21 , 345.3 ,to_date( ' 1996-1-1 ' , ' yyyy-mm-dd ' ));
rollback to ppp;
insert into zong.ccc(bh,xm,age,salary,birthday) values ( ' 006 ' , ' li ' , 28 , 31456.3 ,to_date( ' 1966-1-1 ' , ' yyyy-mm-dd ' ));
update zong.ccc set bh = ' 008 ' ,salary = 5000 ,age = 33 where bh = ' 004 ' ;
commit ;
-- 清除数据
truncate table zong.cc
delete from zong.ccc where bh = ' 006 '
create table zong.firsttable (xm varchar2 ( 10 ),age number ( 4 ),salary number ( 7 , 2 ))
-- 添加列
alter table zong.firsttable add (kk number ( 10 ), birthday date)
-- 更新列类型
alter table zong.firsttable modify (xm number ( 2 ), birthday varchar2 ( 10 ))
-- 收回权限
revoke dba from zong
-- 授予管理员角色
grant dba to zong
-- 授予对象权限
grant select , update on firsttable to system
-- 删除表
drop table zong.ccc
-- 集合操作
Create Table zong.YYY(xm Varchar2 ( 10 ),age Number ( 8 ));
Insert Into zong.yyy Values ( ' aaa ' , 10 )
Insert Into zong.yyy Values ( ' bbb ' , 20 )
Create Table zong.xxx(xm Varchar2 ( 10 ),age Number ( 8 ));
Insert Into zong.xxx Values ( ' aaa ' , 10 )
Insert Into zong.xxx Values ( ' ccc ' , 30 )
Select * From zong.yyy Union Select * From fei.xxx
Select * From zong.yyy Union All Select * From fei.xxx
Select * From zong.yyy Intersect Select * From fei.xxx
Select * From zong.yyy Minus Select * From fei.xxx
*********************************************
**********************************************
-- 字符串函数
select ascii ( ' A ' ) A, ascii ( ' a ' ) a, ascii ( ' 0 ' ) zero, ascii ( ' ' ) space from dual
select chr( 54740 ) zhao,chr( 65 ) chr65 from dual
select concat( ' 010- ' , ' 88888888 ' ) || ' 连接 ' 实例 from dual
select initcap( ' smith ' ) upp from dual;
select instr( ' oracle traning ' , ' ra ' , 1 , 2 ) instring from dual
select lpad(rpad( ' gao ' , 10 , ' * ' ), 17 , ' * ' ) from dual;
select ltrim ( rtrim ( ' gao qian jing ' , ' ' ), ' ' ) from dual;
select substr( ' 13088888888 ' , 3 , 8 ) from dual;
select replace ( ' he love you ' , ' he ' , ' i ' ) from dual;
-- 数学函数
select floor ( 2345.67 ) from dual;
select mod( 10 , 3 ),mod( 3 , 3 ),mod( 2 , 3 ) from dual;
select round ( 55.5 ), round ( - 55.4 ),trunc( 55.5 ),trunc( - 55.5 ) from dual;
select sign ( 123 ), sign ( - 100 ), sign ( 0 ) from dual;
-- 日期函数
select to_char(add_months(to_date( ' 199912 ' , ' yyyymm ' ), 2 ), ' yyyymm ' ) from dual;
select to_char(sysdate, ' yyyy.mm.dd ' ),to_char((sysdate) + 1 , ' yyyy.mm.dd ' ) from dual;
select last_day(sysdate) from dual;
select months_between( ' 19-12月-1999 ' , ' 19-3月-1999 ' ) mon_between from dual;
select months_between(to_date( ' 2000.05.20 ' , ' yyyy.mm.dd ' ),to_date( ' 2005.05.20 ' , ' yyyy.mm.dd ' )) mon_betw from dual;
select to_char(sysdate, ' yyyy.mm.dd hh24:mi:ss ' ) 北京时间,to_char(new_time
(sysdate, ' PDT ' , ' GMT ' ), ' yyyy.mm.dd hh24:mi:ss ' ) 埃及时间 from dual;
select next_day( ' 18-5月-2001 ' , ' 星期五 ' ) next_day from dual;
//
select round (sysdate, ' year ' ) from ccc;
select to_char(sysdate, ' dd-mm-yyyy day ' ) from dual;
select * from ccc where birthday - to_date( ' 1977-11-11 ' , ' yyyy-mm-dd ' ) > 120 ;
select * from ccc where birthday - to_date( ' 1977-11-11 ' , ' yyyy-mm-dd ' ) > 30 ;
select * from ccc where birthday > to_date( ' 1977-11-11 ' , ' yyyy-mm-dd ' );
select to_char(t.d, ' YY-MM-DD ' ) from (
select trunc(sysdate, ' MM ' ) + rownum - 1 as d
from dba_objects
where rownum < 32 ) t
where to_char(t.d, ' MM ' ) = to_char(sysdate, ' MM ' ) -- 找出当前月份的周五的日期
and trim(to_char(t.d, ' Day ' )) = ' 星期五 '
-- 类型转换函数
select to_char(sysdate, ' yyyy/mm/dd hh24:mi:ss ' ) from dual;
select to_number( ' 1999 ' ) year from dual;
-- 系统函数
select username, user_id from dba_users where user_id = uid;
select user from dual;
-- 集合函数
create table table3(xm varchar ( 8 ),sal number ( 7 , 2 ));
insert into table3 values ( ' gao ' , 1111.11 );
insert into table3 values ( ' gao ' , 1111.11 );
insert into table3 values ( ' zhu ' , 5555.55 );
-- select avg(distinct sal) from gao.table3;
-- select max(distinct sal) from scott.emp;
-- 分组函数和统计函数
select deptno, count ( * ), sum (sal) from scott.emp group by deptno;
select deptno, count ( * ), sum (sal) from scott.emp group by deptno having count ( * ) >= 5 ;
select deptno, count ( * ), sum (sal) from scott.emp having count ( * ) >= 5 group by deptno ;
select deptno,ename,sal from scott.emp order by deptno,sal desc ;
**********************************************************************************************
CREATE TABLE "ZONG"."CCC"("BH" VARCHAR2 ( 10 ), "XM" VARCHAR2 ( 10 ), "AGE" NUMBER , "SALARY" NUMBER , "BIRTHDAY" DATE)
insert into zong.ccc(bh,xm,age,salary,birthday) values ( ' 000 ' , ' aaa ' , 56 , 12345 ,to_date( ' 1978-1-1 ' , ' yyyy-mm-dd ' ));
insert into zong.ccc(bh,xm,age,salary,birthday) values ( ' 001 ' , null , 33 , 4444 ,to_date( ' 1979-1-1 ' , ' yyyy-mm-dd ' ));
insert into zong.ccc(bh,xm,age,salary,birthday) values ( ' 004 ' , ' peng ' , 24 , 3456.3 ,to_date( ' 1976-1-1 ' , ' yyyy-mm-dd ' ));
insert into zong.ccc(bh,xm,age,salary,birthday) values ( ' 005 ' , ' cao ' , 21 , 345.3 ,to_date( ' 1996-1-1 ' , ' yyyy-mm-dd ' ));
insert into zong.ccc(bh,xm,age,salary,birthday) values ( ' 006 ' , ' li ' , 28 , 31456.3 ,to_date( ' 1966-1-1 ' , ' yyyy-mm-dd ' ));
select bh 编号, nvl(xm, ' ggg ' ) as 姓名 from ccc where bh = ' 001 '
select bh 编号, nvl2(xm, ' yes ' , ' no ' ) as 姓名 from ccc
-- select bh 编号,NULLIF('bbb','aaa') from ccc
-- is null 的用法
select * from ccc where xm is null
select * from ccc where xm is NOT null
-- not in的用法
select * from ccc where birthday between to_date( ' 1978-1-1 ' , ' yyyy-mm-dd ' ) and to_date( ' 1996-1-1 ' , ' yyyy-mm-dd ' )
select * from ccc where birthday not between to_date( ' 1978-1-1 ' , ' yyyy-mm-dd ' ) and to_date( ' 1996-1-1 ' , ' yyyy-mm-dd ' )
-- in的用法
select * from ccc where xm in ( ' aaa ' , ' peng ' , ' cao ' )
select * from ccc where xm not in ( ' aaa ' , ' peng ' , ' cao ' )
-- like的用法和=、!=、<、>、<=、>=的用法
select * from ccc where age > 24 and age <= 56 and xm like ' %a% '
--
create table sales (xm varchar2 ( 10 ), dTime date, count number , totalmoney number ,city varchar2 ( 10 ))
insert into sales values ( ' 张三 ' ,to_date( ' 2003-01-01 ' , ' yyyy-mm-dd ' ), 1200 , 30000 , ' 南昌 ' );
insert into sales values ( ' 张三 ' ,to_date( ' 2004-01-01 ' , ' yyyy-mm-dd ' ), 1200 , 30000 , ' 南昌 ' );
insert into sales values ( ' 张三 ' ,to_date( ' 2003-01-01 ' , ' yyyy-mm-dd ' ), 1000 , 30000 , ' 北京 ' );
insert into sales values ( ' 张三 ' ,to_date( ' 2004-01-01 ' , ' yyyy-mm-dd ' ), 2333 , 40000 , ' 北京 ' );
insert into sales values ( ' 李四 ' ,to_date( ' 2003-01-01 ' , ' yyyy-mm-dd ' ), 800 , 24567 , ' 南昌 ' );
insert into sales values ( ' 李四 ' ,to_date( ' 2004-01-01 ' , ' yyyy-mm-dd ' ), 600 , 15000 , ' 南昌 ' );
insert into sales values ( ' 李四 ' ,to_date( ' 2003-01-01 ' , ' yyyy-mm-dd ' ), 400 , 20000 , ' 北京 ' );
insert into sales values ( ' 李四 ' ,to_date( ' 2004-01-01 ' , ' yyyy-mm-dd ' ), 1000 , 18000 , ' 北京 ' );
select xm, sum ( count ) 数量, sum (totalmoney) 金额 ,city from sales group by xm , count ,totalmoney,rollup(city) order by xm , count ,totalmoney,city
-- group分组语句
select xm, sum ( count ) 数量, sum (totalmoney) 金额 ,city from sales group by xm , count ,totalmoney,rollup(city) having count > 2000 order by xm , count ,totalmoney,city
-- rollup函数
select xm, sum ( count ) 数量,city from sales group by xm , count ,rollup(city) order by xm , count ,city
-- 事务级临时表是指临时表中的数据只在事务生命周期中存在。当一个事务结束（commit or rollback），Oracle自动清除临时表中数据
CREATE GLOBAL TEMPORARY TABLE admin_work_area
(startdate DATE,
enddate DATE,
class CHAR ( 20 ))
ON COMMIT DELETE ROWS;
create table permernate( a number );
insert into admin_work_area values (sysdate,sysdate, ' temperary table ' );
insert into permernate values ( 1 );
commit ;
select * from admin_work_area;
select * from permernate;
-- 会话级临时表是指临时表中的数据只在会话生命周期之中存在，当用户退出会话结束的时候，Oracle自动清除临时表中数据
drop table admin_work_area;
CREATE GLOBAL TEMPORARY TABLE admin_work_area
(startdate DATE,
enddate DATE,
class CHAR ( 20 ))
ON COMMIT PRESERVE ROWS;
create table permernate( a number );
insert into admin_work_area values (sysdate,sysdate, ' temperary table ' );
insert into permernate values ( 2 );
commit ;
select * from admin_work_area;
select * from permernate;
**********************************************************************************************
// 锁
create table sales (xm varchar2 ( 10 ), dTime date, count number , totalmoney number ,city varchar2 ( 10 ))
insert into sales values ( ' 张三 ' ,to_date( ' 2003-01-01 ' , ' yyyy-mm-dd ' ), 1200 , 30000 , ' 南昌 ' );
insert into sales values ( ' 张三 ' ,to_date( ' 2004-01-01 ' , ' yyyy-mm-dd ' ), 1200 , 30000 , ' 南昌 ' );
insert into sales values ( ' 张三 ' ,to_date( ' 2003-01-01 ' , ' yyyy-mm-dd ' ), 1000 , 30000 , ' 北京 ' );
insert into sales values ( ' 张三 ' ,to_date( ' 2004-01-01 ' , ' yyyy-mm-dd ' ), 2333 , 40000 , ' 北京 ' );
insert into sales values ( ' 李四 ' ,to_date( ' 2003-01-01 ' , ' yyyy-mm-dd ' ), 800 , 24567 , ' 南昌 ' );
insert into sales values ( ' 李四 ' ,to_date( ' 2004-01-01 ' , ' yyyy-mm-dd ' ), 600 , 15000 , ' 南昌 ' );
insert into sales values ( ' 李四 ' ,to_date( ' 2003-01-01 ' , ' yyyy-mm-dd ' ), 400 , 20000 , ' 北京 ' );
insert into sales values ( ' 李四 ' ,to_date( ' 2004-01-01 ' , ' yyyy-mm-dd ' ), 1000 , 18000 , ' 北京 ' );
select * from sales where xm = ' 张三 ' for update of count
update sales set count = 30000 where xm = ' 张三 '
-- 另一用户登录
update ydgl.sales set count = 30000 where xm = ' 张三 '
-- 在多个用户在同一张表中放置锁时,其他用户等待上一用户的时间.
select * from sales where xm = ' 张三 ' for update of count wait 10
-- 只作查询
lock table sales in share mode
-- 能删除,更新,插入除锁定外的其他行
lock table sales in share update mode
-- 排他锁 ,在同一时间点上,只有一个用户在表中放置排他锁.
lock table sales in exclusive mode
-- 避免延迟时间,锁在用户之间的传递时间,不等待,立即提示错误信息
lock table sales in exclusive mode nowait
**********************************************************************************************
-- --创建临时表
/**/ /* create temporary tablespace mydb
tempfile 'f:/mydb.ora'
size 10m; */
-- --创建抽象数据类型
create or replace type address_ty as object
(street_no number ( 3 ),
street_name varchar2 ( 20 ),
city varchar2 ( 20 ),
state varchar2 ( 20 ));
-- --查看抽象数据类型实际值
select attr_name,length,attr_type_name
from user_type_attrs
where type_name = ' ADDRESS_TY ' ;
-- --创建应用了抽象数据类型的表
create table vend_mast
(vencode varchar2 ( 5 ),
venname varchar2 ( 15 ),
venadd address_ty,
tel_no number ( 10 ));
-- --查看表结构
desc vend_mast;
select column_name,data_type from user_tab_columns where
table_name = ' VEND_MAST ' ;
-- --插入记录
insert into vend_mast values
( ' v100 ' , ' john ' ,address_ty( 110 , ' Clinton Rd ' ,
' Rosewood ' , ' Columbia ' , 234465987 );
-- --查看记录
select a.venadd.city from vend mast a;
-- -修改记录,一定要用别名
update vend_mast a
set a.venadd.street_no = 10
where venname = ' john ' ;
-- --删除记录
delete from vend_mast a
where a.venadd.city = ' Rosewood ' ;
-- --强行删除抽象数据类型
drop type address_ty force;
-- --创建应用了抽象数据类型的表的索引
create index streetnum on vend_mast(venadd.street_no);
-- --查看索引
select owner,index_name,index_type,table_owner,table_name,table_type
from all_indexes
where owner = ' SCOTT ' ;
-- --创建不能继承的对象
create or replace type Student_typ as object
(Ssn number ,
Name varchar2 ( 30 ),
Address varchar2 ( 100 )) not final;
-- --修改是否能继承
alter type Student_typ not final;
create type t as object
(x number ,)
not instantiable member function func1 return number )
not instantiable not final;
-- --创建可变数组
create type itemcode as varray( 5 ) of varchar2 ( 5 );
create type qty_ord as varray( 5 ) of number ( 5 );
create type qty_deld as varray( 5 ) of number ( 5 );
-- --基于可变数组创建表
create table order_detail(
orderno varchar2 ( 5 ),
item_va itemcode,
qty_va qty_ord,
qtyd_va qty_deld);
-- --插入记录
insert into order_detail
values ( ' o100 ' ,itemcode( ' i100 ' , ' i101 ' , ' i102 ' , ' i103 ' , ' i104 ' ),
qty_ord( 100 , 98 , 47 , 29 , 20 ),
qty_deld( 100 , 900 , 800 , 700 , 600 ));
-- --查看整体
select * from order_detail
-- --单个
select item_va from order_detail
-- --查看可变数组内容
select * from table (
-- --select * from order_detail a where a.orderno='o100')
select a.item_va from order_detail a where a.orderno = ' o100 ' )
-- --嵌套表
-- --创建抽象数据类型即对象
create or replace type ord_ty as object (
itemcode varchar2 ( 5 ),
qty_ord number ( 5 ),
qty_deld number ( 5 ));
-- --表中包含嵌套表一定要基于一个对象创建一个新的对象作为嵌套表
create or replace type ord_nt as table of ord_ty;
-- --创建包含嵌套表的表
create table order_master(
orderno varchar2 ( 5 ),
odate date,
vencode varchar2 ( 5 ),
dets ord_nt)
nested table dets store as ord_nt_tab;
-- 嵌套表放入某个任意任名的存储空间,嵌套表的存储空间与普通表不同,分别存储在不同的空间
insert into order_master values (
' o100 ' ,to_date( ' 18-07-99 ' , ' DD-MM-YY ' ), ' v001 ' ,
ord_nt(ord_ty( ' i100 ' , 10 , 5 ),
ord_ty( ' i101 ' , 50 , 25 ),
ord_ty( ' i102 ' , 5 , 5 )));
-- --把记录插入到嵌套表中
insert into table ( select p.dets from order_master p
where p.orderno = ' o100 ' )
values ( ' i103 ' , 30 , 25 );
select t.dets from order_master t where t.orderno = ' o100 ' ;
-- --查看嵌套表中的信息
select * from table ( select t.dets from order_master t
where t.orderno = ' o100 ' );
-- --修改
update table ( select t.dets from order_master t
where t.orderno = ' o100 ' ) t
set value(t) = ord_ty( ' i103 ' , 50 , 45 )
where t.itemcode = ' i103 ' ;
-- --删除嵌套表的值
delete from table ( select t.dets from order_master t
where t.orderno = ' o100 ' ) t
where t.itemcode = ' i102 ' ;
-- --把嵌套表中已存在的记录添加到创建的表中
insert into order_master values ( ' o202 ' ,to_date( ' 2003-3-5 ' , ' YY-MM-DD ' ),
' v101 ' , cast (multiset( select * from table ( select dets from order_master
where orderno = ' o201 ' )) as ord_nt));
-- --创建对象
create type vend_ty as object(
vencode varchar2 ( 5 ),
venname varchar2 ( 20 ),
venadd1 varchar2 ( 20 ),
venadd2 varchar2 ( 20 ),
venadd3 varchar2 ( 20 ),
tel_no number ( 6 ));
drop table vend_master;
-- --创建对象表,对象中不能定义约束，在对象表中可通过关键字constraint定义
create table vend_master of vend_ty(vencode constraint vc_pk primary key );
insert into vend_master values (
vend_ty( ' v201 ' , ' John ' , ' 10 ' , ' Fezinnith ' , ' Mexico ' , 948456 ));
-- --查看地址（表中所分配的OID）
select ref(a) from vend_master a;
-- --创建一个指向抽象数据类型的表
create table ord_master(
orderno varchar2 ( 5 ),
vendet ref vend_ty); -- --数据类型为指向抽象数据类型的类型
-- --类似将查询记录插入一个表的语法插入记录
insert into ord_master( select ' o301 ' ,ref(a) from vend_master a
where vencode = ' v201 ' );
-- --查看所有记录
select * from ord_master;
select deref(a.vendet) from ord_master a;
delete from vend_master where vencode = ' v201 ' ;
-- --对象视图
create table item(
itemcode varchar2 ( 10 ),
item_on_hand number ( 10 ),
item_sold number ( 10 ));
create or replace type item_type as object
(itemcode varchar2 ( 10 ),
item_on_hand number ( 10 ),
item_sold number ( 10 ));
create view item_view of item_type with object oid -- --表名of类型名with object oid
(itemcode) as
select * from item where item_on_hand < 20 ;
insert into item values ( ' i201 ' , 10 , 5 );
-- --插入值调用函数
insert into item_view values (item_type( ' i102 ' , 15 , 50 ));
create view nt_view of ord_ty with object oid(itemcode)
as select * from table ( select d.dets
from order_master d where d.orderno = ' o201 ' );
create table itemfile(
itemcode varchar2 ( 5 ) primary key ,
itemdesc varchar2 ( 20 ),
p_category varchar2 ( 20 ),
qty_hand number ( 5 ),
re_level number ( 5 ),
max_level number ( 5 ),
itemrate number ( 9 , 2 ));
create table order_detail (
orderno varchar2 ( 5 ),
itemcode varchar2 ( 5 ),
qty_ord number ( 5 ),
qty_deld number ( 5 ), primary key (orderno,qty_ord,qty_deld),
foreign key (itemcode) references itemfile(itemcode));
create or replace type itemfile_ty as object
(itemcode varchar2 ( 5 ),
itemdesc varchar2 ( 20 ),
p_category varchar2 ( 20 ),
qty_hand number ( 5 ),
re_level number ( 5 ),max_level number ( 5 ),
itemrate number ( 9 , 12 ));
create view itemfile_ov of itemfile_ty
with object oid(itemcode)
as select * from itemfile;
select make_ref(itemfile_ov,itemcode) from itemfile;
create view order_detail_ov
as
select make_ref(itemfile_ov,itemcode) items,orderno,qty_ord,qty_deld
from order_detail;
-- --不能正确运行
select deref(a.items) from order_detail_ov a;
**********************************************************************************************
-- --创建抽象数据类型
create or replace type add_ty as object(
Street varchar2 ( 25 ),
City varchar2 ( 15 ),
State varchar2 ( 10 ),
Zip number );
-- --基于抽象数据类型创建表
create table customer(
Customer_id number ( 4 ),
person add_ty);
-- --插入记录
insert into customer values (
1001 ,add_ty( ' No.2 downhill st. ' , ' Los Angles ' , ' California ' , 700023 ));
insert into customer values (
1002 ,add_ty( ' No.120 stepahead rd. ' , ' houston ' , ' texas ' , 701024 ));
-- --查询记录
select customer_id,c.person.city from customer c
where c.person.state = ' texas ' ;
-- --删除记录
delete from customer a
where a.person.zip = 701024 ;
-- --创建可变数组
create type Phone as varray( 2 ) of Number ( 8 );
-- --使用可变数组创建表
create table Employee(
Eno number ( 4 ),
name varchar2 ( 15 ),
phone phone);
-- --插入数据
insert into Employee values (
1000 , ' George ' ,Phone( 67343344 , 3432342 ));
delete from Employee where name = ' gxj ' ;
select * from employee;
select phone from employee;
-- --创建对象
create type person_details as object(
name varchar2 ( 15 ),
age number ( 2 ),
desg varchar2 ( 15 ));
create type person_detail_table_ty as table of person_details;
create table other_info_person(
dept_name varchar2 ( 10 ),
dept_no number ( 3 ),
person_info person_detail_table_ty)
nested table person_info store as person_store_table;
-- --创建抽象数据类型
create or replace type Dept_type as object(
Deptno number ( 2 ),
Dname varchar2 ( 14 ),
Loc varchar2 ( 13 ));
-- --创建表
create table Student(
Name varchar2 ( 15 ),
Dept_detail Dept_type);
-- --插入数据
insert into Student values (
' Jessica ' ,Dept_type( 20 , ' Computer ' , ' Chicago ' ));
insert into Student values (
' Peter ' ,Dept_type( 40 , ' Electronics ' , ' California ' ));
-- --查询数据
select * from Student;
select name, a.dept_detail.Deptno from Student a
where a.Dept_detail.Loc = ' Chicago ' ;
insert into Employee values (
1002 , ' Dick ' ,Phone( 33444876 , 87876565 ));
insert into Employee values (
1003 , ' Jones ' ,Phone( 54576545 , 52457779 ));
plsql
--例二：创建具有LOB数据类型的表
CREATE TABLE vendor_master
(vencode varchar2(5),
venname varchar2(15),
venadd1 varchar2(20),
venadd2 varchar2(20),
venadd3 varchar2(20),
tel_no number(6),
msg CLOB);
--例三：初始化LOB值
INSERT INTO vendor_master VALUES
('v201','aryay','10','first st','mds',475859,
'这是我们的初始化LOB值');
select * from vendor_master;
--例9：条件控制
select * from rs_employees
where hetongid='WL-090001';
DECLARE
v_department rs_employees.department%type;
BEGIN
SELECT department INTO v_department
FROM rs_employees
WHERE HeTongId='WL-090001';
IF v_department = '车间工人' THEN
UPDATE rs_employees
SET department='不是工人'
WHERE HeTongId='WL-090001';
ELSE
UPDATE rs_employees
SET department='车间工人'
WHERE HeTongId='WL-090001';
END IF;
END;
/
--CASE语句示例（下面的写法有错）
SET SERVEROUT ON;
DECLARE
I number:=2;
BEGIN
CASE
WHEN (I=1) THEN dbms_output.put_line('Result is 1');
WHEN (I=2) THEN dbms_output.put_line('Result is 2');
END CASE;
END;
--和上面的区别是什么
SET SERVEROUT ON;
DECLARE
I number:=2;
BEGIN
CASE I(用于选择器)
WHEN 1 THEN dbms_output.put_line('Result is 1');
WHEN 2 THEN dbms_output.put_line('Result is 2');
END CASE;
END;
/
--例11：简单循环：在Test Window中执行
--SET SERVEROUT ON;
DECLARE
a NUMBER := 100;
BEGIN
LOOP
a := a+25;
EXIT WHEN A=250;
END LOOP;
dbms_output.put_line(TO_CHAR(a));
END;
--例12：While循环，此值书上有错
DECLARE
i NUMBER :=0;
J NUMBER :=0;
BEGIN
while i<=100 Loop
J := J+1;
i := i+2;
end loop;
dbms_output.put_line('j的值是'||j);
END;
--例13：FOR循环，结果是5050
DECLARE
i number :=0;
j number :=0;
BEGIN
for i in 1..100
loop
j := j + 1;
end loop;
dbms_output.put_line('j的值是'||j);
END;
PLSQL表
CREATE OR REPLACE PROCEDURE MY_PLSQL_TABLE AS
--定义一个PL/SQL表
TYPE MyType IS Table OF Rs_Employees.Name%Type
Index By Binary_Integer;
--定义二个变量
MyTable MyType;
i binary_integer:=0;
--通过循环取出PL/SQL表中的第一条记录的序号及内容
Procedure MyOutPut Is
Begin
I := MyTable.First;
Dbms_Output.put_line('第'||To_char(I)||'行为：'||MyTable(I));
Loop
I := MyTable.Next(I);
Dbms_Output.put_line('第'||To_char(I)||'行为：'||MyTable(I));
Exit When I>= MyTable.Last;
End Loop;
End;
BEGIN
--通过游标往PL/SQL表中写入数据
FOR tmp_cur in (SELECT HeTongId,Name
From Rs_Employees
Where HeTongId<='WL-090010')
LOOP
i := i + 1;
MyTable(i) := tmp_cur.Name;
Dbms_Output.put_line('原表中合同号为：'||tmp_cur.hetongid||' 姓名为：'||tmp_cur.Name);
Dbms_Output.put_line('PL/SQL表中姓名为：'||Mytable(i));
END LOOP;
--跳过前面的顺序，有意增加一条记录
MyTable(80) := 'XW';
-- 显示PL/SQL表相关信息
Dbms_Output.put_line('');
Dbms_Output.put_line('PL/SQL表的总行数为： '||MyTable.count||'行');
Dbms_Output.put_line('PL/SQL表的第一行为： '||MyTable.First||' '||MyTable(MyTable.First));
Dbms_Output.put_line('PL/SQL表的最后一行为：'||MyTable.Last||' '||MyTable(MyTable.Last));
--显示最后一条相关信息
Dbms_Output.put_line('');
Dbms_Output.put_line('PL/SQL表的第80行为： '||MyTable(80));
Dbms_Output.put_line('PL/SQL表的第80行之后为：'||MyTable.Next(80));
--示范通过循环取出PL/SQL表中的每一条记录的序号及内容
Dbms_Output.put_line('');
MyOutPut;
--示范从PL/SQL表中删除行
Dbms_Output.put_line('');
MyTable.Delete(3);
Dbms_Output.put_line('已从PL/SQL表删除第3行');
Dbms_Output.put_line('现在PL/SQL表的总行数为： '||MyTable.count||'行');
--通过循环取出PL/SQL表中的每一条记录的序号及内容
MyOutPut;
--示范从PL/SQL表中删除行
Dbms_Output.put_line('');
MyTable.Delete;
Dbms_Output.put_line('已从PL/SQL表删除全部行');
Dbms_Output.put_line('现在PL/SQL表的总行数为： '||MyTable.count||'行');
--取消以下注释将会引发异常
Dbms_Output.put_line('');
--MyOutPut;
Exception
--修改上面的代码，有意触发此异常
--此示例说明了，只有对PL/SQL表中进行了赋值的记录才可以引用；但不需要按顺序对每条
--记录进行赋值；如果试图访问没有赋值的记录，将会引发错误；通过集合函数对PL/SQL表
--进行操作时，如果超出了记录范围，则返回空值
When Others then
Dbms_Output.put_line('发生了错误！'||I);
END;
记录
DECLARE
--声明一个记录类型
TYPE TYPE_RSRECORD IS RECORD
(HETONGID RS_EMPLOYEES.HeTongId%Type,
NAME RS_EMPLOYEES.Name%type,
SEX RS_EMPLOYEES.Sex%type,
DEPARTMENT RS_EMPLOYEES.Department%type,
HIREDATE RS_EMPLOYEES.Hiredate%type);
--定义一个记录变量
Rs_REcord TYPE_RSRECORD;
--定义一个游标
Cursor MyCursor Is
SELECT * From Rs_Employees
Where HeTongId<='WL-090010';
BEGIN
--通过游标往记录中写入数据
Open MyCursor;
Loop
Fetch MyCursor Into Rs_Record;
Exit When MyCursor%Notfound;
Dbms_Output.put_line('合同号为：'||Rs_Record.hetongid||' 姓名为：'||Rs_Record.Name);
End Loop;
Close MyCursor;
END;
/
可变数组
DECLARE
TYPE itemcode1 IS varray(5) of varchar2(5);
TYPE qty_ord1 IS varray(5) of Number(5);
TYPE qty_deld1 IS varray(5) of Number(5);
v_itemcode itemcode1;
v_qty_ord qty_ord1 := qty_ord1(1,2);
BEGIN
IF v_itemcode is NULL Then
DBMS_OUTPUT.put_line('v_itemcode包含空值');
END IF;
IF v_qty_ord is NULL Then
DBMS_OUTPUT.put_line('v_qty_ord包含空值');
ELSE
DBMS_OUTPUT.put_line('v_qty_ord非空');
END IF;
END;
/
批量绑定
--先建立一张表，用于测试
CREATE TABLE VENDOR
(VENCODE VARCHAR2(5),
VENNAME VARCHAR2(15));
--测试批量绑定
DECLARE
--定义二张PL/SQL表
TYPE NumTab Is Table Of VarCHAR2(5) INDEX BY BINARY_INTEGER;
TYPE NameTab Is Table Of VarCHAR2(15) INDEX BY BINARY_INTEGER;
vnums NumTab;
vNames NameTab;
--三个时间变量
t1 varchar2(5);
t2 varchar2(5);
t3 varchar2(5);
--捕获当前时间的过程
Procedure get_time(t Out Number) Is
BEGIN
SELECT TO_CHAR(SYSDATE,'SSSSS') INTO t FROM DUAL;
END;
BEGIN
FOR j IN 1..20000
LOOP
vnums(j) :=j;
vNames(j) :='vendor' || To_char(j);
End loop;
get_time(t1);
--用FOR循环插入
For i In 1..20000
LOOP
Insert Into vendor (vencode,venname)
Values(vnums(i),vnames(i));
END LOOP;
get_time(t2);
--用FORALL插入
FORALL i In 1..20000
Insert Into vendor (vencode,venname)
Values(vnums(i),vnames(i));
get_time(t3);
DBMS_OUTPUT.put_line('执行时间（秒）');
DBMS_OUTPUT.put_line('--------------------------');
DBMS_OUTPUT.put_line('For循环：'||To_char(t2-t1));
DBMS_OUTPUT.put_line('ForAll： '||To_char(t3-t2));
End;
/
--抽象数据类型
CREATE OR REPLACE TYPE address_ty AS OBJECT
(street_no number(3),
street_name varchar2(20),
city varchar2(20),
state varchar2(20));
CREATE TABLE vend_mast
(vencode varchar2(5),
venname varchar2(15),
venadd address_ty,
tel_no number(10));
INSERT INTO vend_mast VALUES
('v100','john',address_ty(110,'Clinton Rd ','Rosewood','Columbia'),
234465987);
SELECT * FROM vend_mast;
select a.venadd.city from vend_mast a;
UPDATE vend_mast a
set a.venadd.street_no = 10
WHERE venname='john';
DELETE FROM vend_mast a
WHERE a.venadd.city='Rosewood';
DROP TYPE address_ty;
CREATE INDEX streetnum ON vend_mast (venadd.street_no);
CREATE OR REPLACE TYPE Student_typ AS OBJECT
(ssn number,
Name varchar2(30),
Address varchar2(100)) NOT FINAL
--对象表.
CREATE TYPE vend_ty AS Object
(vencode varchar2(5),
venname varchar2(20),
venadd1 varchar2(20),
venadd2 varchar2(20),
venadd3 varchar2(20),
tel_no number(6));
CREATE TABLE vend_master OF vend_ty
(vencode CONSTRAINT VC_PK PRIMARY KEY);
INSERT INTO vend_master values
(vend_ty('v201','John','10','Fezinnith','Mexico',948456));
SELECT vencode FROM vend_master;
--对象视图
CREATE TABLE item
(itemcode varchar2(10),
item_on_hand number(10),
item_sold number(10));
CREATE OR REPLACE TYPE ITEM_TYPE AS OBJECT
(itemcode varchar2(10),
item_on_hand number(10),
item_sold number(10));
CREATE VIEW ITEM_VIEW OF ITEM_TYPE
with object oid(itemcode) As
SELECT * FROM ITEM WHERE ITEM_ON_HAND < 20;
INSERT INTO ITEM VALUES ('i201',10,5);
INSERT INTO item_view Values (item_type('i102',15,50));
select * from item_view;
DELETE FROM ITEM_VIEW WHERE ItEMCODE='i102';
--可变数组
CREATE TYPE itemcode AS varray(5) of varchar2(5);
CREATE TYPE qty_ord AS varray(5) of number(5);
CREATE TYPE qty_deld AS varray(5) of number(5);
CREATE TABLE ORDER_DETAIL
(ORDERNO VARCHAR2(5),
ITEM_VA ITEMCODE,
QTY_VA QTY_ORD,
QTYD_VA QTY_DELD);
INSERT INTO order_detail VALUES
('o100',itemcode('i100','i101','i102','i103','i104'),
qty_ord(100,98,49,39,20),
qty_deld(100,900,800,700,600));
INSERT INTO order_detail VALUES
('o101',itemcode('i102','i103','i104'),
qty_ord(100,98,20),
qty_deld(100,900));
SELECT * FROM ORDER_DETAIL;
--嵌套表
CREATE TYPE ord_ty As Object
(itemcode varchar2(5),
qty_ord number(5),
qty_deld number(5));
CREATE TYPE ord_nt AS Table OF ord_ty;
CREATE TABLE order_master
(orderno varchar2(5),
odate date,
vencode varchar2(5),
dets ord_nt)
NESTED TABLE dets STORE AS ord_nt_tab;
INSERT INTO order_master VALUES
('o100',To_date('18-07-99','dd-mm-yy'),'v001',
ord_nt(
ord_ty('i100',10,5),
ord_ty('i101',50,25),
ord_ty('i102',5,5)
)
);
INSERT INTO TABLE (SELECT p.dets
FROM order_master p
WHERE p.orderno='o100')
Values ('i103',30,25);
SELECT * FROM TABLE (SELECT t.dets FROM order_master t
Where t.orderno = 'o100');
UPDATE TABLE (SELECT e.dets from order_master e
WHERE e.orderno = 'o100') p
SET VALUE(p) = ord_ty('i103',50,45)
Where p.itemcode = 'i103';
DELETE FROM TABLE (SELECT e.dets from order_master e
WHERE e.orderno = 'o100') p
Where p.itemcode = 'i103';