共计 33959 个字符，预计需要花费 85 分钟才能阅读完成。
oracle pl/sql 模板代码怎么写，很多新手对此不是很清楚，为了帮助大家解决这个难题，下面丸趣 TV 小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。
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
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 hh34:mi:ss) 北京时间,to_char(new_time
(sysdate, PDT , GMT), yyyy.mm.dd hh34: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 hh34: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
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
看完上述内容是否对您有帮助呢？如果还想对相关知识有进一步的了解或阅读更多相关文章，请关注丸趣 TV 行业资讯频道，感谢您对丸趣 TV 的支持。
