Home Php C# Sql C C++ Javascript Python Java Go Android Git Linux Asp.net Django .net Node.js Ios Xcode Cocoa Iphone Mysql Tomcat Mongodb Bash Objective-c Scala Visual-studio Apache Elasticsearch Jar Eclipse Jquery Ruby-on-rails Ruby Rubygems Android-studio Spring Lua Sqlite Emacs Ubuntu Perl Docker Swift Amazon-web-services Svn Html Ajax Xml Java-ee Maven Intellij-idea Rvm Macos Unix Css Ipad Postgresql Css3 Json Windows-server Vue.js Typescript Oracle Hibernate Internet-explorer Github Tensorflow Laravel Symfony Redis Html5 Google-app-engine Nginx Firefox Sqlalchemy Lucene Erlang Flask Vim Solr Webview Facebook Zend-framework Virtualenv Nosql Ide Twitter Safari Flutter Bundle Phonegap Centos Sphinx Actionscript Tornado Register | Login | Edit Tags | New Questions | 繁体 | 简体


10 questions online user: 8

7
votes
answers
26 views
+10

热备份与冷备份有何不同?

冷备份和热备份有什么区别?

沙发
+40

热备份

对于热备份关闭不是强制性的,因此在进行热备份时,用户可以使用数据库。

在热备份数据库中不一致。

冷备份

对于冷备份关闭是必要的,因此在此期间用户无法访问数据库。

在冷备份数据库中是一致的。

板凳
+30
  • 当系统没有用户活动时,将执行冷备份。当数据库未运行且没有用户登录时,也称为脱机备份。数据库的所有文件都被复制,并且在复制过程中没有任何更改。
  • 当进行热备份时数据库需要一直运行。这是一个在线备份。复制数据库的所有文件,并且在复制期间可能会对数据库进行更改。
    • 进行热备份的好处是数据库可用于在备份发生时使用,并且可以将数据库恢复到任何时间点。
    • 进行冷备份的好处是通常更容易管理备份和恢复过程。
23
votes
answers
30 views
+10

如何从Oracle中的日期中提取月份号

我有 ID_BB_SECURITY 列,它是数字数据类型,但日期值存储在此列中,例如'20190801'

我想要从这个字段获取月份数,例如8月日期我想得到8。

我尝试了下面的查询,但它抛出错误'文字不匹配':

 从BT_EXPORT中选择to_number(to_date(ID_BB_SECURITY),'mm') 
    
        
沙发
+60
+50
/ pre>

或者您可以使用子字符串:

 从BT_EXPORT中选择to_number(substr(ID_BB_SECURITY,5,2));   

这些采用固定的一致格式,在使用错误的数据类型时,这始终是一个冒险的假设。Ans,如果它是一个数字,他们正在进行从数字到字符串的隐式转换,为了更清晰,你可以转换为显式转换。

如果它已经是一个日期 - 当然应该是 - 但是您不需要转换:

 从BT_EXPORT选择提取(来自ID_BB_SECURITY的月份)  

这些假定采用固定的一致格式,在使用错误的数据类型时始终是一个冒险的假设。Ans,如果它是一个数字,他们正在进行从数字到字符串的隐式转换,为了更清晰,你可以转换为显式转换。

如果它已经是一个日期 - 当然应该是 - 但是您不需要转换:

 从BT_EXPORT选择提取(来自ID_BB_SECURITY的月份)  

这些假定采用固定的一致格式,在使用错误的数据类型时始终是一个冒险的假设。Ans,如果它是一个数字,他们正在进行从数字到字符串的隐式转换,为了更清晰,你可以转换为显式转换。

如果它已经是一个日期 - 当然应该是 - 但是您不需要转换:

 从BT_EXPORT选择提取(来自ID_BB_SECURITY的月份) 
     
			
        

您无需提供格式模型; 如果不这样做,那么TO_DATE将使用NLS_DATE_FORMAT会话参数作为默认格式模型,如果这不匹配,那么您将得到相应的错误。但是,您应该提供显式格式模型,因为每个用户都可以修改自己的会话参数(并且默认值在不同的国际地区可能不同),因此您不能依赖不同用户之间的默认值或一致性。 - MT0 8月29日10:57

+50

如果你有一个数字,你可以使用算术来提取月份:

  select dual(floor(20190801/100),100)来自dual;  
     
			
        

倾向于成为唯一不依赖丑陋的字符串操作的答案。 - Tim Biegeleisen 8月29日10:43

一般工作但它也适用于错误的数字,如mod(floor(20191301/100),100)或mod(floor(20190230/100),100) - 它取决于是否接受这些值的要求。 - Wernfried Domscheit 8月29日10:52

@TimBiegeleisen:赞成成为唯一使用丑陋数字操作的答案。:-) - Bob Jarvis 8月29日11:22

+40

您可以尝试将数字日期转换为字符串,然后提取第5和第6个字符:

  SELECT SUBSTR(TO_CHAR(ID_BB_SECURITY),5,2)AS mm FROM BT_EXPORT;   

但是,使用正确的日期列会更好。然后,你可以使用一个不那么严苛的方法,例如:

  SELECT TO_CHAR(ID_BB_SECURITY,'mm')AS mm  - 假设日期来自BT_EXPORT;  
     
			
        
+30
 从双 中选择to_number(to_char(to_date('20190801','yyyymmdd'),'mm'))
     
			
        
+20

 从双 中选择提取(来自to_date('20190801','yyyymmdd')的月份)
     
			
        
+20

尝试这个

 从BT_EXPORT选择提取(月份来自to_date(ID_BB_SECURITY,'YYYYMMDD))  

这个转换号码到目前为止提取月份。

怎么样“避免异步void。返回一个像公共异步TaskDownloadAsync(字符串路径,字符串[]名称)的任务,这样你就可以等待那个方法” - Uzay 2天前

我没有异步无效。它是异步main中的异步方法。 - Murdock 2天前

可能不同的加密或zip方法 - ralf.w. 2天前

我可以证实这种行为。通过MS Azure Storage Explorer下载我的文件在01:09.87下载,但是通过DownloadToFileAsync方法,它需要03:04:01。也许他们将下载分成Azure存储资源管理器中的块,然后进行并行下载? - 贾里德2天前

+10

您的日期列的值存储格式为“yyyymmdd”,其中

  • yyyy是年份
  • mm月份
  • < p> dd当天为了返回月份的数值(mm),我们可以按如下方式进行:

    1:首先使用to_date将值从数字转换为日期(20190801,' yyyymmdd')

    2:使用to_date运算符to_char获取月份(to_date(20190801,'yyyymmdd'),'mm')

我还在使用旧版本的Storage Explorer(1.4.1)。所以它不能成为制造差异的azcopy,但我会给你的建议一个去。 - 昨天默多克

@Murdock,请在你的帖子中指定存储资源管理器的版本:) - Ivan Yang昨天

0
votes
answers
73 views
+10

如何預先填充APEX中的表格文件

0

我正在使用apex創建具有數據庫後端的應用程序。 該應用程序允許用戶根據特定標準搜索機構,一旦指定標準,向用戶顯示報告,包括機構名稱和機構標識。名稱列鏈接到顯示所選機構詳細信息的頁面。如何預先填充APEX中的表格文件

我想從詳細信息頁面創建一個鏈接,將用戶帶到'聯繫我們'頁面,該頁面應該包含一些預填充的表單字段,例如機構名稱和電子郵件等。但是,我不確定如何通過機構編號,以便可以預填字段。

+0

通過鏈接到一個頁面項目上通過鏈接與我們聯繫頁面傳遞機構ID。 – RLOG

+0

可以你給我舉例說明如何做到這一點? –

沙发
0
0

在您的報告中,請將機構標識作爲名稱和徽標旁邊的link類型的列。使target page您與我們聯繫頁面,並與#INSTITUTION_ID#填充機構ID頁面項目(或任何你institution_id列稱爲。

0
votes
answers
53 views
+10

ORA-02291:完整性約束(H00300581.FK_CLASSES)違反 - 父鍵沒有找到

0

當我做插入給我這個錯誤:ORA-02291:完整性約束(H00300581.FK_CLASSES)違反 - 父鍵沒有找到

ORA-02291: integrity constraint (H00300581.FK_CLASSES) violated - parent key not found

這種錯誤發生的所有鑲入登記表。我需要幫助,請嘗試爲我解決。

它來自表中的外鍵嗎?這可能是因爲我嘗試了一切,但它沒有奏效。

創建表

drop table g3_trainers CASCADE CONSTRAINTS; 
drop table g3_classes CASCADE CONSTRAINTS; 
drop table g3_members CASCADE CONSTRAINTS; 
drop table g3_registration CASCADE CONSTRAINTS; 

CREATE TABLE G3_TRAINERS 
(
TRAINER_ID VARCHAR(6) NOT NULL, 
TRAINER_NAME VARCHAR(50) NOT NULL, 
COUNTRY VARCHAR(25) NOT NULL, 
DOB DATE NOT NULL, 
SPECIALITY VARCHAR(30) NOT NULL, 
DESCRIPTION VARCHAR(500) NOT NULL, 
PRIMARY KEY (TRAINER_ID) 
); 

CREATE TABLE G3_CLASSES 
(
CLASS_NUM VARCHAR(6) NOT NULL, 
TRAINER_ID VARCHAR(6) NOT NULL, 
CLASS_TITLE VARCHAR(50) NOT NULL, 
START_DATE DATE NOT NULL, 
END_DATE DATE NOT NULL, 
COST NUMBER(6) NOT NULL, 
PRIMARY KEY (CLASS_NUM), 
CONSTRAINT fk_TRAINERS FOREIGN KEY(TRAINER_ID) REFERENCES G3_TRAINERS(TRAINER_ID) 
); 

CREATE TABLE G3_MEMBERS 
(
MEMBER_ID VARCHAR(6) NOT NULL, 
MEMBER_NAME VARCHAR(50) NOT NULL, 
MEMBER_AGE NUMBER(2) NOT NULL, 
GENDER VARCHAR(10) NOT NULL, 
PHONE_NUMBER VARCHAR(10) NOT NULL, 
CHECK (regexp_like (PHONE_NUMBER,'^(d{3}-d{3}-?d{4}|d{10})$')), 
CHECK (MEMBER_AGE>=17), 
CHECK(MEMBER_AGE NOT LIKE '%[^a-zA-Z]%'), 
PRIMARY KEY (MEMBER_ID) 
); 

CREATE TABLE G3_REGISTRATION 
(
REGISTRATION_ID VARCHAR(6) NOT NULL, 
MEMBER_ID VARCHAR(6) NOT NULL, 
AMOUNT_TO_PAY NUMBER(6) NOT NULL, 
CLASS_NUM VARCHAR(6) NOT NULL, 
PRIMARY KEY (REGISTRATION_ID), 
CONSTRAINT fk_MEMBERS FOREIGN KEY(MEMBER_ID) REFERENCES G3_MEMBERS(MEMBER_ID), 
CONSTRAINT fk_CLASSES FOREIGN KEY(CLASS_NUM) REFERENCES G3_CLASSES(CLASS_NUM) 
); 

創建序列

CREATE SEQUENCE G3_TRAINER_Seq 
START WITH  10001 
INCREMENT BY 1 
NOCACHE 
NOCYCLE; 

CREATE SEQUENCE G3_MEMBER_Seq 
START WITH  20001 
INCREMENT BY 1 
NOCACHE 
NOCYCLE; 

CREATE SEQUENCE G3_CLASSE_Seq 
START WITH  30001 
INCREMENT BY 1 
NOCACHE 
NOCYCLE; 

CREATE SEQUENCE G3_REGISTRATIONS_Seq 
START WITH  40001 
INCREMENT BY 1 
NOCACHE 
NOCYCLE; 

訓練數據

INSERT INTO G3_TRAINERS (TRAINER_ID, TRAINER_NAME, COUNTRY, DOB, SPECIALITY, DESCRIPTION) 
VALUES (G3_TRAINER_Seq.nextVal, 'Ahmed', 'Eygpt', '2/3/1988', 'Body Building', 'I describe my self as an active guy i workout everyday and thats why i desided to train people to be good as me'); 

INSERT INTO G3_TRAINERS (TRAINER_ID, TRAINER_NAME, COUNTRY, DOB, SPECIALITY, DESCRIPTION) 
VALUES (G3_TRAINER_Seq.nextVal, 'John', 'Canada', '4/6/1987', 'Crossfit', 'I like being healthy and work as much as i can to be fit and to have a great life'); 

INSERT INTO G3_TRAINERS (TRAINER_ID, TRAINER_NAME, COUNTRY, DOB, SPECIALITY, DESCRIPTION) 
VALUES (G3_TRAINER_Seq.nextVal, 'Sara', 'Canada', '9/1/1990', 'Yoga', 'I like interacting with poeple, and to teach them how yoga will be a very good source of flexing and relaxing your body'); 

INSERT INTO G3_TRAINERS (TRAINER_ID, TRAINER_NAME, COUNTRY, DOB, SPECIALITY, DESCRIPTION) 
VALUES (G3_TRAINER_Seq.nextVal, 'Jun', 'China', '12/4/1981', 'Crossfit', 'I describe my self as an daily working guy i like my job because i get to know more about people and i teach many people how to be fit'); 

    INSERT INTO G3_TRAINERS (TRAINER_ID, TRAINER_NAME, COUNTRY, DOB, SPECIALITY, DESCRIPTION) 
VALUES (G3_TRAINER_Seq.nextVal, 'Mike', 'Ohio', '2/22/1981', 'Body Building', 'Being strong and big is what i am aiming for, i like keeping my diet healthy and to train poeple to be healthy and big like me'); 

INSERT INTO G3_TRAINERS (TRAINER_ID, TRAINER_NAME, COUNTRY, DOB, SPECIALITY, DESCRIPTION) 
VALUES (G3_TRAINER_Seq.nextVal, 'Mia', 'Germany', '12/12/1987', 'Yoga', 'I describe myself as a hard working i love my job and i love my members'); 

INSERT INTO G3_TRAINERS (TRAINER_ID, TRAINER_NAME, COUNTRY, DOB, SPECIALITY, DESCRIPTION) 
VALUES (G3_TRAINER_Seq.nextVal, 'Essam', 'UAE', '1/5/1980', 'Body Building', 'I am a hard worker and i like to train my members and i want them to be healthy and strong'); 

類的數據

INSERT INTO G3_CLASSES (CLASS_NUM, TRAINER_ID, CLASS_TITLE, START_DATE, END_DATE, COST) 
VALUES (G3_CLASSE_Seq.nextVal, 10001, 'Weight Lifting Class', '1/12/2017', '1/1/2018', 1150); 

INSERT INTO G3_CLASSES (CLASS_NUM, TRAINER_ID, CLASS_TITLE, START_DATE, END_DATE, COST) 
VALUES (G3_CLASSE_Seq.nextVal, 10001, 'Weight Lifting Class', '1/2/2018', '1/3/2018', 1150); 

INSERT INTO G3_CLASSES (CLASS_NUM, TRAINER_ID, CLASS_TITLE, START_DATE, END_DATE, COST) 
VALUES (G3_CLASSE_Seq.nextVal, 10002, 'Body Weight Class', '2/1/2018', '2/2/2018', 950); 

INSERT INTO G3_CLASSES (CLASS_NUM, TRAINER_ID, CLASS_TITLE, START_DATE, END_DATE, COST) 
VALUES (G3_CLASSE_Seq.nextVal, 10003, 'Yoga Class', '3/2/2018', '3/3/2018', 750); 

INSERT INTO G3_CLASSES (CLASS_NUM, TRAINER_ID, CLASS_TITLE, START_DATE, END_DATE, COST) 
VALUES (G3_CLASSE_Seq.nextVal, 10003, 'Yoga Class', '5/5/2018', '5/6/2018', 750); 

INSERT INTO G3_CLASSES (CLASS_NUM, TRAINER_ID, CLASS_TITLE, START_DATE, END_DATE, COST) 
VALUES (G3_CLASSE_Seq.nextVal, 10004, 'Body Weight Class', '10/2/2018', '10/3/2018', 950); 

INSERT INTO G3_CLASSES (CLASS_NUM, TRAINER_ID, CLASS_TITLE, START_DATE, END_DATE, COST) 
VALUES (G3_CLASSE_Seq.nextVal, 10004, 'Body Weight Class', '10/4/2018', '10/5/2018', 950); 

INSERT INTO G3_CLASSES (CLASS_NUM, TRAINER_ID, CLASS_TITLE, START_DATE, END_DATE, COST) 
VALUES (G3_CLASSE_Seq.nextVal, 10005, 'Weight Lifting Class', '12/20/2017', '1/20/2018', 1150); 

INSERT INTO G3_CLASSES (CLASS_NUM, TRAINER_ID, CLASS_TITLE, START_DATE, END_DATE, COST) 
VALUES (G3_CLASSE_Seq.nextVal, 10005, 'Weight Lifting Class', '2/20/2018', '3/20/2018', 1150); 

INSERT INTO G3_CLASSES (CLASS_NUM, TRAINER_ID, CLASS_TITLE, START_DATE, END_DATE, COST) 
VALUES (G3_CLASSE_Seq.nextVal, 10005, 'Weight Lifting Class', '4/20/2018', '5/20/2018', 1150); 

INSERT INTO G3_CLASSES (CLASS_NUM, TRAINER_ID, CLASS_TITLE, START_DATE, END_DATE, COST) 
VALUES (G3_CLASSE_Seq.nextVal, 10006, 'Yoga Class', '12/28/2017', '1/28/2018', 750); 

INSERT INTO G3_CLASSES (CLASS_NUM, TRAINER_ID, CLASS_TITLE, START_DATE, END_DATE, COST) 
VALUES (G3_CLASSE_Seq.nextVal, 10007, 'Weight Lifting Class', '10/3/2018', '10/4/2018', 1150); 

INSERT INTO G3_CLASSES (CLASS_NUM, TRAINER_ID, CLASS_TITLE, START_DATE, END_DATE, COST) 
VALUES (G3_CLASSE_Seq.nextVal, 10007, 'Weight Lifting Class', '10/5/2018', '10/6/2018', 1150); 

成員數據

INSERT INTO G3_MEMBERS (MEMBER_ID, MEMBER_NAME, MEMBER_AGE, GENDER, PHONE_NUMBER) 
VALUES (G3_MEMBER_Seq.nextVal, 'Mohammed', '21', 'Male', '0502202212'); 

INSERT INTO G3_MEMBERS (MEMBER_ID, MEMBER_NAME, MEMBER_AGE, GENDER, PHONE_NUMBER) 
VALUES (G3_MEMBER_Seq.nextVal, 'Afra', '28', 'Female', '0555544558'); 

INSERT INTO G3_MEMBERS (MEMBER_ID, MEMBER_NAME, MEMBER_AGE, GENDER, PHONE_NUMBER) 
VALUES (G3_MEMBER_Seq.nextVal, 'Essa', '30', 'Male', '0555544558'); 

INSERT INTO G3_MEMBERS (MEMBER_ID, MEMBER_NAME, MEMBER_AGE, GENDER, PHONE_NUMBER) 
VALUES (G3_MEMBER_Seq.nextVal, 'Ahmed', '19', 'Male', '0563020300'); 

INSERT INTO G3_MEMBERS (MEMBER_ID, MEMBER_NAME, MEMBER_AGE, GENDER, PHONE_NUMBER) 
VALUES (G3_MEMBER_Seq.nextVal, 'Alex', '25', 'Female', '0521022932'); 

INSERT INTO G3_MEMBERS (MEMBER_ID, MEMBER_NAME, MEMBER_AGE, GENDER, PHONE_NUMBER) 
VALUES (G3_MEMBER_Seq.nextVal, 'Abdulla', '24', 'Male', '0504493393'); 

INSERT INTO G3_MEMBERS (MEMBER_ID, MEMBER_NAME, MEMBER_AGE, GENDER, PHONE_NUMBER) 
VALUES (G3_MEMBER_Seq.nextVal, 'Abdulrahman', '20', 'Male', '0502292290'); 

INSERT INTO G3_MEMBERS (MEMBER_ID, MEMBER_NAME, MEMBER_AGE, GENDER, PHONE_NUMBER) 
VALUES (G3_MEMBER_Seq.nextVal, 'Olivia', '21', 'Female', '0561020200'); 

INSERT INTO G3_MEMBERS (MEMBER_ID, MEMBER_NAME, MEMBER_AGE, GENDER, PHONE_NUMBER) 
VALUES (G3_MEMBER_Seq.nextVal, 'Omar', '30', 'Male', '0502503323'); 

INSERT INTO G3_MEMBERS (MEMBER_ID, MEMBER_NAME, MEMBER_AGE, GENDER, PHONE_NUMBER) 
VALUES (G3_MEMBER_Seq.nextVal, 'Nasser', '29', 'Male', '0504334439'); 

INSERT INTO G3_MEMBERS (MEMBER_ID, MEMBER_NAME, MEMBER_AGE, GENDER, PHONE_NUMBER) 
VALUES (G3_MEMBER_Seq.nextVal, 'Sophia', '22', 'Female', '0561616316'); 

INSERT INTO G3_MEMBERS (MEMBER_ID, MEMBER_NAME, MEMBER_AGE, GENDER, PHONE_NUMBER) 
VALUES (G3_MEMBER_Seq.nextVal, 'Nadia', '21', 'Female', '0507797786'); 

註冊數據

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20001, 1150, 30001); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20001, 750, 30004); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20002, 750, 30004); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20002, 950, 30006); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20003, 1150, 30001); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20003, 1150, 300010); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20004, 950, 30006); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20004, 1150, 30010); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20005, 1150, 30007); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20005, 750, 30011); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20006, 1150, 30013); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20006, 1150, 30012); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20007, 1150, 30009); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20007, 750, 30011); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20008, 1150, 30008); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20008, 750, 30011); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20009, 1150, 30013); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20009, 750, 30005); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20010, 1150, 30013); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20010, 1150, 30012); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20010, 950, 30007); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20011, 1150, 30013); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20011, 750, 30005); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20012, 750, 30004); 

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) 
VALUES (G3_REGISTRATIONS_Seq.nextVal, 20012, 1150, 30009); 
+0

您的腳本有太多錯誤。有幾個地方丟失了逗號。您在插入語句中使用的序列名稱中存在拼寫錯誤。首先解決這些問題。 –

+0

@Kaushik Nayak我修復了一些小問題,我如何添加代碼? –

+0

@Kaushik Nayak我編輯它看看它 –

沙发
0
0

commas缺少的大多數語句,如:

between 'Eygpt' & '12/3/1988' in :

INSERT INTO G3_TRAINERS (TRAINER_ID, TRAINER_NAME, COUNTRY, DOB, 
SPECIALITY, DESCRIPTION) VALUES (G3_TRAINERS_Seq.nextVal, 'Ahmed', 
'Eygpt' '12/3/1988', 'Body Building', 'I describe my self as an active 
guy i workout everyday and thats why i desided to train people to be 
good as me'); 

OR

between 'Weight Lifting Class' & '1/12/2017'

INSERT INTO G3_CLASSES (CLASS_NUM, TRAINER_ID, CLASS_TITLE, START_DATE, END_DATE, COST) 
VALUES (G3_CLASSES_Seq.nextVal, 10001, 'Weight Lifting Class' '1/12/2017', '1/1/2018', 1150); 

你有CONSTRAINT fk_CLASSES FOREIGN KEY(CLASS_NUM) REFERENCES G3_CLASSES(CLASS_NUM)爲約束

G3_CLASSES_Seq序列填充g3_classes表的class_num列,應該是方便,fk_CLASSES FOREIGN KEY,但你有另一個序列G3_CLASSE_Seq開始30000。順便說一句,現在看來,這應該與30001

i.e. Pay attention that G3_CLASSE_Seq and G3_CLASSES_Seq are different.

的方式啓動,刪除空格在column names之間create sentences類似以下內容:

爲例,其轉換

CREATE TABLE G3_TRAINERS 
(

TRAINER_ID VARCHAR(6) NOT NULL, 
TRAINER_NAME VARCHAR(50) NOT NULL, 
COUNTRY VARCHAR(25) NOT NULL, 
DOB DATE NOT NULL, 
SPECIALITY VARCHAR(30) NOT NULL, 
DESCRIPTION VARCHAR(500) NOT NULL, 

PRIMARY KEY (TRAINER_ID) 

); 

對此:

CREATE TABLE G3_TRAINERS 
(
TRAINER_ID VARCHAR(6) NOT NULL, 
TRAINER_NAME VARCHAR(50) NOT NULL, 
COUNTRY VARCHAR(25) NOT NULL, 
DOB DATE NOT NULL, 
SPECIALITY VARCHAR(30) NOT NULL, 
DESCRIPTION VARCHAR(500) NOT NULL, 
PRIMARY KEY (TRAINER_ID) 
); 

An d在以下語句中存在另一個問題(300010

INSERT INTO G3_REGISTRATION (REGISTRATION_ID, MEMBER_ID, AMOUNT_TO_PAY, CLASS_NUM) VALUES (G3_REGISTRATIONS_Seq.nextVal, 20003, 1150, 300010);

+0

非常感謝,解決了我的其他問題之一,但我發佈了主要問題 –

+0

@abdulrahmanarab請看我最後編輯的外鍵和序列。 –

+0

@abdulrahmanarab我認爲,問題不是使用'G3_CLASSE_Seq'(你應該使用這個,從30001開始)。您正在使用'G3_CLASSES_Seq'。 –

0
votes
answers
54 views
+10

顯示作者姓名,書名爲誰寫的多本書籍

-4
CREATE TABLE Book_Master(
Book_code NUMBER(10) PRIMARY KEY, 
Book_name VARCHAR2(50) NOT NULL, 
Book_pub_year NUMBER, 
Book_pub_author VARCHAR2 (50) NOT NULL); 

INSERT INTO book_master VALUES(10000001,'Let Us C++',2000,'Yashavant Kanetkar'); 

INSERT INTO book_master VALUES(10000002,'Mastersing VC++',2005,'P.J Allen'); 

INSERT INTO book_master VALUES(10000003,'JAVA Complete Reference',2004,'H.Schild'); 

INSERT INTO book_master VALUES(10000004,'J2EE Complete Reference',2000,'H. Schild'); 

INSERT INTO book_master VALUES(10000005,'Relational DBMS',2000,'B.C. Desai'); 

INSERT INTO book_master VALUES(10000006,'Let Us C',2000, 'Yashavant Kanetkar'); 

INSERT INTO book_master VALUES(10000007,'Intoduction To Algorithams',2001,'Cormen'); 

INSERT INTO book_master VALUES(10000008,'Computer Networks',2000,'Tanenbaum'); 

INSERT INTO book_master VALUES(10000009,'Introduction to O/S',2001,'Millan'); 
+3

認真對待作業。顯示一些努力。 –

沙发
0
0
select Book_pub_author, Book_name 
    from book_master 
where Book_pub_author in (select Book_pub_author 
           from book_master 
          group by Book_pub_author 
          having count(1)>1) 
order by Book_pub_author, Book_name 
+0

Thx爲您提供幫助 –

+0

@AbhiramVarma歡迎您。 –

板凳
0
0

這些作者還可以使用內部聯接,而不是IN子句

Select Book_pub_author 
    , Book_name 
from book_master b 
INNER JOIN (
    select Book_pub_author 
    from book_master 
    group by Book_pub_author 
    having count(1)>1 
) t on t.Book_pub_author = b.Book_pub_author 

anfd您可以獲取所有的書在同一行中使用group_concat

Select Book_pub_author 
    , group_concat(Book_name) 
from book_master b 
INNER JOIN (
    select Book_pub_author 
    from book_master 
    group by Book_pub_author 
    having count(1)>1 
) t on t.Book_pub_author = b.Book_pub_author 
group by Book_pub_author 
+0

Thx爲您的幫助 –

+0

@AbhiramVarma如果其中一個答案是正確的,請選擇一個你喜歡的,並將其標記爲已接受...看看這裏如何 http://meta.stackexchange.com/questions/5234/how-不接受-的回答工作 – scaisEdge

0
votes
answers
74 views
+10

PL/SQL - XML CLOB錯誤

0

我想測試一個PL/SQL過程,它需要一個XML CLOB作爲一個參數PL/SQL - XML CLOB錯誤

PROCEDURE example_proc(ex_xml CLOB) IS 
BEGIN 

/* Find the second < (skip over declaration) */ 
ln_position := instr(pc_shipment_xml, '<', 1, 2); 
/* Find the space after the first tag */ 
ln_position := instr(pc_shipment_xml, ' ', ln_position, 1); 
/* Set the first part of the XML clob */ 
lc_shipment_xml := substr(pc_shipment_xml, 1, ln_position - 1); 
/* Skip the namespace */ 
ln_position  := instr(pc_shipment_xml, '>', ln_position, 1); 
lc_shipment_xml := lc_shipment_xml || substr(pc_shipment_xml, ln_position); 

/* Set the XML type */ 
lx_shipment_xml := xmltype(lc_shipment_xml); --RIGHT HERE IS WHERE IT ERRORS OUT 

這裏是我在想過去(因爲ex_xml CLOB的XML

ORA-19202: Error occurred in XML processing 
LPX-002255: end-element tag "ShipmentLines" does not match start-element tag "Shipment" 

請原諒我的無知,但這是我第一次寬:參數):在與最後一行

xml := 
'<Shipment> 
    <OrderNumber>00000</OrderNumber> 
    <ShipmentLines> 
    <ShipmentLine> 
     <OrderDetailId>000000</OrderDetailId> 
     <LineNumber>0</LineNumber> 
     <ItemId>00000</ItemId> 
     <UnitId>0000000</UnitId> 
     <BaseUom>X</BaseUom> 
     <Quantity1>000</Quantity1> 
    </ShipmentLine> 
    </ShipmentLines> 
</Shipment>'; 

,並出現了錯誤用PL/SQ處理XML,我是否缺少格式化的東西?據我所見,我正確打開和關閉我的標籤。任何輸入都是值得讚賞的。

+1

你真的嘗試用'instr','substr'等修改XML文檔嗎?看看[XML函數](https://docs.oracle.com/database/121/SQLRF/functions002.htm#SQLRF51185) –

沙发
0
0

運行你的代碼

declare 
    pc_shipment_xml CLOB := '<Shipment> 
    <OrderNumber>00000</OrderNumber> 
    <ShipmentLines> 
    <ShipmentLine> 
     <OrderDetailId>000000</OrderDetailId> 
     <LineNumber>0</LineNumber> 
     <ItemId>00000</ItemId> 
     <UnitId>0000000</UnitId> 
     <BaseUom>X</BaseUom> 
     <Quantity1>000</Quantity1> 
    </ShipmentLine> 
    </ShipmentLines> 
</Shipment>'; 
    lc_shipment_xml varchar2(2000); 
    ln_position integer; 
BEGIN 
    /* Find the second < (skip over declaration) */ 
    ln_position := instr(pc_shipment_xml, '<', 1, 2); 
    /* Find the space after the first tag */ 
    ln_position := instr(pc_shipment_xml, ' ', ln_position, 1); 
    /* Set the first part of the XML clob */ 
    lc_shipment_xml := substr(pc_shipment_xml, 1, ln_position - 1); 
    /* Skip the namespace */ 
    ln_position  := instr(pc_shipment_xml, '>', ln_position, 1) + 1; 
    lc_shipment_xml := lc_shipment_xml || substr(pc_shipment_xml, ln_position); 
    dbms_output.put_line(lc_shipment_xml); 
end; 
/

給出了下面的輸出:

<Shipment> 
    <OrderNumber>00000</OrderNumber> 

    <ShipmentLine> 
     <OrderDetailId>000000</OrderDetailId> 
     <LineNumber>0</LineNumber> 
     <ItemId>00000</ItemId> 
     <UnitId>0000000</UnitId> 
     <BaseUom>X</BaseUom> 
     <Quantity1>000</Quantity1> 
    </ShipmentLine> 
    </ShipmentLines> 
</Shipment> 

,所以它看起來像你剝出開始ShipmentLines標記,但沒有結束標記

如果你知道要刪除的標籤的名稱可以更簡單地刪除與

lc_shipment_xml := regexp_replace(pc_shipment_xml, '</?ShipmentLines>'); 

或獲取由位置標籤:

l_tag := regexp_substr(pc_shipment_xml, '<(w+)>',1,3,'i',1); 
lc_shipment_xml := regexp_replace(pc_shipment_xml, '</?' || l_tag || '>'); 

一個更好的辦法是使用xml funtions,這樣的事情:

declare 
    pc_shipment_xml CLOB := '<Shipment> 
    <OrderNumber>00000</OrderNumber> 
    <ShipmentLines> 
    <ShipmentLine> 
     <OrderDetailId>000000</OrderDetailId> 
     <LineNumber>0</LineNumber> 
     <ItemId>00000</ItemId> 
     <UnitId>0000000</UnitId> 
     <BaseUom>X</BaseUom> 
     <Quantity1>000</Quantity1> 
    </ShipmentLine> 
    </ShipmentLines> 
</Shipment>'; 
    lx_shipment_xml xmltype := xmltype(pc_shipment_xml); 
    lx_shipline_xml xmltype := xmltype(pc_shipment_xml); 
    lc_shipment_xml varchar2(2000); 
BEGIN 
    /* Set the XML type */ 
    select extract(lx_shipment_xml, '/Shipment/ShipmentLines/ShipmentLine') into lx_shipline_xml from dual; 
    select deletexml(lx_shipment_xml, '/Shipment/ShipmentLines') into lx_shipment_xml from dual; 
    select insertxmlafter(lx_shipment_xml, '/Shipment/OrderNumber', lx_shipline_xml) into lx_shipment_xml from dual; 
    select xmlserialize(content lx_shipment_xml) into lc_shipment_xml from dual; 
    dbms_output.put_line(lc_shipment_xml); 
end; 
/
板凳
0
0

,我是在解析XML的方式需要投入clob開頭的<?xml version="1.0"?>。解析方法不是我自己的代碼,所以改變我這樣做的方式不是一種選擇。謝謝

0
votes
answers
35 views
+10

Oracle 10g安裝程序消失

1

我一直試圖按照本教程安裝Oracle 10g http://faq.programmerworld.net/database/installing-oracle-10g.html 但是,安裝程序只是在選擇安裝路徑並點擊next後纔會消失。Oracle 10g安裝程序消失

歡迎頁面

Welcome page

卸裝產品

Deinstall Products

選擇安裝類型 - >企業版

Select Installation Type -> Enterprise Edition

指定主頁詳情

Specify home details: Name -> OraDb10g_home1 Path -> c:oracleproduct10.2.0db_1

產品特定的先決條件檢查

Product-Specific Prerequisite Checks (all good)

最後一步後,窗口就會消失,並沒有任何反應 我看着在互聯網上,嘗試了各種東西,我一直試圖做的最後一件事是這個解決方案http://www.dbforums.com/showthread.php?1641469-Oracle-Installer-Disappears,但我無法弄清楚如何去做。

+0

我有一天有類似的問題。原來我們收到了一個不完整的安裝盤,安裝過程只是坐在後臺。發放磁盤的同事只複製了?700 MB的文件夾,但應該已經達到?1.3 GB(他製作了一張CD而不是DVD)。 – yacc

+1

嗯,好點,我必須驗證,因爲我從另一臺電腦複製它,謝謝啓蒙! – Lynn94

+0

我們使用FileMon(來自Sysinternals)來監控進程,並且在複製了一堆文件後,它只是坐在那裏,空轉。所以我們得出的結論是沒有輸入。安裝程序沒有明顯的程序來檢查其清單的完整性。 – yacc

沙发
0
0

閱讀本故障排除安裝指南:https://docs.oracle.com/cd/B19306_01/install.102/b14317/ts.htm#NTDBX404

我會做的是去看看,如果你能找到安裝日誌,看看你至少可以找到一個錯誤代碼的第一件事。

+0

謝謝,我會通過它來嘗試做你告訴我的 – Lynn94

0
votes
answers
30 views
+10

Oracle左外連接返回0行,其中包含左數據集中的數據

0

我試圖獲取硬編碼事件的列表,並將其與實際事件加入表中,以便實際查看它們是否已執行。Oracle左外連接返回0行,其中包含左數據集中的數據

這裏是我的查詢:

SELECT a.Verification FROM (SELECT 'Verify ID changed' as Verification FROM DUAL 
UNION ALL 
SELECT 'Verify manual Valve closed' as Verification FROM DUAL 
UNION ALL 
SELECT 'Verify manual Valve open' as Verification FROM DUAL 
UNION ALL 
SELECT 'Visually verify Things' as Verification FROM DUAL 
) a 
LEFT OUTER JOIN JOBEVENTS c ON a.Verification = c.EVENTDESC 
WHERE c.EVT_TIME BETWEEN '22-Jan-2017' AND '22-Jan-2017' 

當我閱讀文檔的左外連接,那麼隊列應始終從返回左表中的數據,那麼,爲什麼我的查詢返回0行(沒有什麼在JOBEVENTS事件表中,但是硬編碼值始終存在)。

我誤解了LEFT OUTER JOIN的工作原理嗎?

+0

您的Where子句依賴於來自左連接的數據。如果左連接不匹配,那麼您的where子句也將無法匹配。 –

沙发
0
1

在連接的左側連接表部分創建過濾器,而不是where子句。

SELECT a.Verification, c.* 
FROM (
     SELECT 'Verify ID changed' as Verification FROM DUAL UNION ALL 
     SELECT 'Verify manual Valve closed' as Verification FROM DUAL UNION ALL 
     SELECT 'Verify manual Valve open' as Verification FROM DUAL UNION ALL 
     SELECT 'Visually verify Things' as Verification FROM DUAL 
    ) a 
LEFT OUTER JOIN JOBEVENTS c ON a.Verification = c.EVENTDESC 
     AND c.EVT_TIME BETWEEN '22-Jan-2017' AND '22-Jan-2017' 
; 

或者,您可以添加到您的where子句,以便可以從左連接表返回NULLS。例如:

SELECT a.Verification, c.* 
FROM (
     SELECT 'Verify ID changed' as Verification FROM DUAL UNION ALL 
     SELECT 'Verify manual Valve closed' as Verification FROM DUAL UNION ALL 
     SELECT 'Verify manual Valve open' as Verification FROM DUAL UNION ALL 
     SELECT 'Visually verify Things' as Verification FROM DUAL 
    ) a 
LEFT OUTER JOIN JOBEVENTS c ON a.Verification = c.EVENTDESC 
WHERE (c.EVT_TIME BETWEEN '22-Jan-2017' AND '22-Jan-2017' 
     OR c.EVENTDESC IS NULL 
    ) 
; 

您從該表允許空值在where子句中引用時務必小心與外連接表,否則你創建一個內部的等價條件加入(&有時被稱爲「隱含的內部聯接」) 。

0
votes
answers
41 views
+10

使用Oracle SQL

1

總結子節點在XML我有以下格式使用Oracle SQL

<Receive 
    xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> 
    <Date>20171106</Date> 
    <ReceiptDetails> 
     <ReceiptDetail> 
      <Quantity>3</Quantity> 
     </ReceiptDetail> 
     <ReceiptDetail> 
      <Quantity>2</Quantity> 
     </ReceiptDetail> 
     <ReceiptDetail> 
      <Quantity>1</Quantity> 
     </ReceiptDetail> 
     <ReceiptDetail> 
      <Quantity>4</Quantity> 
     </ReceiptDetail> 
    </ReceiptDetails> 
</Receive> 

XML現在我想查詢的Oracle SQL查詢返回我的節點數量的總和。

我寫了一個連接值的查詢。

SELECT 
XMLTYPE(t.REQ_MSG).EXTRACT('//Receive/ReceiptDetails/ReceiptDetail/Quantity/text()').getStringVal() 
FROM 
    TABLE_NAME t; 

這給了我3214這是連接值,但我想要10這是總和。

沙发
0
1

考慮使用XMLTABLE將XML節點解析到列中,然後在外部查詢中進行聚合。

SELECT SUM(e.Quantity) AS SumQuantity 

FROM XMLTABLE('/Receive/ReceiptDetails/ReceiptDetail' 
     PASSING XMLTYPE('<Receive xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
           xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> 
          <Date>20171106</Date> 
          <ReceiptDetails> 
           <ReceiptDetail> 
            <Quantity>3</Quantity> 
           </ReceiptDetail> 
           <ReceiptDetail> 
            <Quantity>2</Quantity> 
           </ReceiptDetail> 
           <ReceiptDetail> 
            <Quantity>1</Quantity> 
           </ReceiptDetail> 
           <ReceiptDetail> 
            <Quantity>4</Quantity> 
           </ReceiptDetail> 
          </ReceiptDetails> 
         </Receive>') 
     COLUMNS 
     Quantity NUMBER PATH 'Quantity') as e 

以及XML列在一個表中

SELECT SUM(e.Quantity) AS SumQuantity 

FROM MY_TABLE, 
     XMLTABLE('/Receive/ReceiptDetails/ReceiptDetail' 
     PASSING XMLTYPE(MY_TABLE.MY_XML_COLUMN) 

     COLUMNS 
     Quantity NUMBER PATH 'Quantity') as e 

Rextester Demo

+0

感謝您的。我搜索了一個解決方案根本找不到它!你從哪裏學到的?如果你能給我一篇文章或什麼是好的! – vik123

+0

很好聽!很高興我能幫上忙。 * Oracle xmltable *上的簡單Google/Bing搜索可以幫助您查找教程,文檔,甚至是SO帖子。如果您不知道該術語,只需搜索如何在Oracle中查詢XML。那裏有很多例子。 – Parfait

0
votes
answers
42 views
+10

如何取一個字符串,將小數內的小數轉換爲分數,提取部分字符串,並將其與表格進行比較?

1

這是我需要分析的數據:如何取一個字符串,將小數內的小數轉換爲分數,提取部分字符串,並將其與表格進行比較?

--TABLE: PRICE_LIST 


ITEM_DESCRIPTION VARCHAR2(60) VENDOR_PARTNO VARCHAR2(15) 
---------------------------- -------------------------- 
.374 x 3 w/ph KLT-6   5506125 
.4375 x 3-1/2 w/ph KLT-3345 5506124 
.125 x 2-1/2 w/ph KLT-3211  5506123 
.3125 x 4-1/2 w/ph KUR-44  5506127 

這是我需要PRICE_LIST.ITEM_DESCRIPTION比較表:

--Table: MATERIALS 
--COLUMN: VARCHAR2(20) 

ITEM_ID 
-------------- 
1/2 X 3-1/2 
5/16 X 4-1/2 
1/8 X 2-1/2 

我試過這種方法,試圖將它們分開,但是這樣會需要大量的工作:

SELECT SUBSTR(VALUE, 1, INSTR(VALUE, 'x')-1) DIAMETER, 
     SUBSTR(VALUE, INSTR(VALUE, 'x')+1) DIRTY_LENGTH 
     FROM (SELECT DESCRIPTION VALUE FROM PRICE_LIST); 

DIAMETER DIRTY_LENGTH 
-------- ------------ 
.374  3 w/ph KLT-6 
.4375  3-1/2 w/ph KLT-3345 
.125  2-1/2 w/ph KLT-3211 
.3125  4-1/2 w/ph KUR-44 

但現在我有與我不知道該怎麼做小數列,並有我的第二個分數的另一列,但其他數據我不需要。

因爲只有在PRICE_LIST表中的最後2個值將匹配,我要的是隻此返回:

ITEM_DESCRIPTION VENDOR_PARTNO 
---------------- ------------- 
1/8 X 2-1/2  5506123 
5/16 X 4-1/2  5506127 

感謝您的幫助!

+0

你爲什麼要使用這樣的設計很差的數據?你應該有一個主鍵,你可以用來匹配,而不是彌補格式錯誤的數據和草率的字符串解析和比較。您可能想要購買一本書或找到關於基本數據庫設計原則的Web教程。 –

+0

似乎第一部分的描述可能對應於直徑(.125 == 1/4等)。而且,是的,數據應該是結構化的。目前很難理解和使用。 – igr

+1

@KenWhite - 我不知道OP的具體情況。但是,您是否可以想象這種情況,即現有數據,在一個認識到他們以前的IT人員是多麼愚蠢的組織中(只是看看數據模型),他們解僱了所有不熟練的人員,而是*僱傭OP和其他人處理混亂?當然,修復數據模型等等,你會怎麼做?你不需要做**正確** OP的要求是什麼?如果你需要幫助,你不會在這裏問嗎? – mathguy

沙发
0
1

數據格式很糟糕。看來你需要創建一個自定義用戶definied PL/SQL函數來計算這些表達式:

CREATE OR REPLACE FUNCTION evaluate_me(p_x VARCHAR2) 
RETURN NUMBER 
DETERMINISTIC 
IS 
    x VARCHAR2(200); 
    expr VARCHAR2(100); 
    y NUMBER; 
BEGIN 
    x := lower(substr(p_x, 1, regexp_instr(p_x ||'q', '[^.0-9 xX/-]+')-1)); 
    expr := replace('BEGIN :p:='|| x ||'; END;', 'x', '*'); 
    execute immediate expr USING OUT y; 
    RETURN y; 
END; 
/

,然後做就像這個例子:http://sqlfiddle.com/#!4/c7681/2

select p.*, evaluate_me(item_description) x 
from PRICE_LIST p; 

|   ITEM_DESCRIPTION | VENDOR_PARTNO |  X | 
|-----------------------------|---------------|--------| 
|   .374 x 3 w/ph KLT-6 |  5506125 | 1.122 | 
| .4375 x 3-1/2 w/ph KLT-3345 |  5506124 | 0.8125 | 
| .125 x 2-1/2 w/ph KLT-3211 |  5506123 | -0.25 | 
| .3125 x 4-1/2 w/ph KUR-44 |  5506127 | 0.75 | 

select m.*, evaluate_me(item_id) x 
from MATERIALS m 
; 
|  ITEM_ID |  X | 
|--------------|-------| 
| 1/2 X 3-1/2 |  1 | 
| 5/16 X 4-1/2 | 0.75 | 
| 1/8 X 2-1/2 | -0.25 | 

SELECT * 
FROM PRICE_LIST p 
JOIN MATERIALS m 
ON evaluate_me(p.item_description) = evaluate_me(m.item_id) 
; 

|   ITEM_DESCRIPTION | VENDOR_PARTNO |  ITEM_ID | 
|----------------------------|---------------|--------------| 
| .125 x 2-1/2 w/ph KLT-3211 |  5506123 | 1/8 X 2-1/2 | 
| .3125 x 4-1/2 w/ph KUR-44 |  5506127 | 5/16 X 4-1/2 | 

這預計會極其緩慢,th將爲左表的每一行調用e函數,然後調用右表的每一行。因此,如果左邊的表格有10,000行,右邊的表格有20000行(對RDBMS系統來說沒有那麼多),那麼該函數將被稱爲10,000 + 10,000 * 20,000 = 200,010,000次。
這是一個糟糕的設計成本 - 數據不遵循First Normal Form的規則,必須在每次訪問時解析。

爲了使查詢速度更快,你必須創建兩個功能指標,否則當你啓動這個查詢,你可以參加每月休假:

CREATE INDEX MATERIALS_eval ON MATERIALS(evaluate_me(ITEM_ID)); 

CREATE INDEX PRICE_LIST_eval ON PRICE_LIST(evaluate_me(ITEM_DESCRIPTION)); 
+0

感謝您花時間發佈此信息。我明天會在辦公室回來測試一下,然後回來。我同意這些數據是一團糟。它設計得很差,並且這種方式維持了15年。 – Delbudge