oracle pl/sql模板代碼怎么寫

157次閱讀

共計 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 的支持。

正文完