plsql集合

A. plsql 什麼情況下使用集合

一般情況下是對比數據的時候，會用的比較多吧！
具體的也要看業務要求。。。

B. oracle plsql 怎麼在一個集合里做時間差值運算

也就是上一行，當然如果本身沒有排序，那麼你的前面應該有id欄位按照id欄位計算就可以了。
上一行有一個函數lead，那麼我們就可以操作了
首次任務開始時間- lead(首次任務完成時間, 1, null) over(order by 首次開始完成時間）
如果不能直接操作，那麼就先 lead(首次任務完成時間, 1, null) over(order by 首次開始完成時間）查出來，然後取個別名再操作就好了。
如果是以前的不管，也是這么操作，後面加上where條件就行。
當然如果你的開始時間和結束時間是分開寫的，那麼也可以直接用max來寫，當然前提是你的開始時間和結束時間不是同事寫入表中的，不然你可能需要在過程中才能計算（我說的過程就是記錄開始時間的那個地方）。

C. sql plsql sqlplus oracle之間的關系

sql是資料庫編程語言，plsql是sql按照一定目的集合起來的數據塊。sqlplus則是連接資料庫的工具，oracle是資料庫本身，也就是他們的載體。

打個比方來說：oracle是一輛車，sql則是坐在這輛車上的人（既屬於這輛車但又不限於這輛車，同時可以在一定的范圍內操作這輛車），plsql則是一群人打算開著這輛車去某個地方（有目的的把sql按照一定的規則組合在一起，比如怎麼坐，誰坐前面誰坐後面，要帶什麼東西等等），sqlplus則是方向盤，主要作用就是操作這輛車（具體怎麼操作要由sql（人）決定，sqlplus僅僅是最古老的方向盤（注意方向盤是可以換的，比如現在的常用的一些資料庫連接工作，你可以看做是漂亮好用的方向盤）而已）

D. plsql怎麼往表裡插入多條數據

1、採用insert into values 語句插入一條，寫很多條語句即可多條數據，這種主要針對於離散值以及一些基礎信息的錄入，如：insert into test（xh,mc) values('123','測試');
如果插入的數據有規律，可利用for、loop循環插入，主要用於批量生成測試數據
begin
for i in 1 .. 100 loop
insert into test(xh,mc) values(i||'','測試');
end loop;
end ;。
2、採用insert into selct from 語句來一次性插入一個集合,這種主要依據於要插入的數據源已經存儲於資料庫對象中,或者利用al虛表來構造數據，經過加工後寫入一個集合。
insert into test (xh,mx) select '123','測試' from al;
3、採用plsql等工具、或者oracle的imp、impdp命令來導入,這種主要用資料庫與資料庫之間的大批量數據導入，導入的數據格式為plsql的pde、oracle的dmp等。dmp文件可使用
table_exists_action參數控制導入動作:replace替換原表,truncate清除原表數據再導入，append增量導入數據，當然impdp數據泵的導入要依賴於directory路徑。
impdp 用戶名/密碼 mpfile=123.dmp logfile=123.log directory=imp_dir tables=test table_exists_action=append
4、使用excel文件直接拷貝。這種主要用於要寫入的數據已是excel文件或者行列分明的其它格式文件，每一列的值和表結構相對應，可直接打開表的行級鎖，把數據拷貝進入。
打開行級鎖方法：
select t.*,rowid from 表名 t where 1=2;
select * from 表名 where 1=2 for update;
直接把excel數據拷貝到表裡

E. PLSQL中有兩個表,怎麼樣將兩個表的數據隨機排列組合成第三張表

SQL 的連接(JOIN)語句將資料庫中的兩個或多個表組合起來.[1] 由"連接"生成的集合, 可以被保存為表, 或者當成表來使用. JOIN 語句的含義是把兩張表的屬性通過它們的值組合在一起. 基於 ANSI 標準的 SQL 列出了五種 JOIN 方式: 內連接(INNER), 全外連接(FULL OUTER), 左外連接(LEFT OUTER), 右外連接(RIGHT OUTER)和交叉連接(CROSS). 在特定的情況下, 一張表(基本表, 視圖, 或連接表)可以和自身進行連接, 成為自連接(self-join).
程序員用 JOIN 謂詞表示要得到"連接"後的集合. 如果evaluated predicate為真, 組合後的記錄就會按照預期的方式生成, 如一個記錄集, 或者一張臨時表.

F. PLSQL集合表類型與對象表區別

PLSQL表變數類型

TYPE typ_id_record is RECORD(
gid NUMBER(10);
gno NUMBER(5);
co NUMBER(5));
TYPE typ_record_table is table of typ_id_record;
注意：PLSQL表變數類型必須在包頭定義，否則會提示：Error:PLS-00642:不允許使用本地收集類型
集合在使用前需清空，typ_record_table.delete
對象表變數類型
CREATE OR REPLACE TYPE typ_id_object as OBJECT(
gid NUMBER(10);
gno NUMBER(5);
co NUMBER(5));
CREATE OR REPLACE TYPE typ_table_object as table of typ_id_object;
使用bulk collect批量獲取查詢結果集
bulk collect的使用區別

PLSQL表變數typ_record_table可以直接接受bulk collect，但對象表變數typ_object_table必須先進行轉換
如下所示：
PLSQL表變數typ_record_table
CREATE OR REPLACE PROCEDURE p_f4 IS
tab_ids type_record_table;
BEGIN
SELECT gp.gid, gp.gno, gp.co BULK COLLECT
INTO tab_ids
FROM p_table_test gp;
END p_f4;
對象表變數typ_table_object不能直接接受bulk collect，必須進行轉換
如下所示：
報錯：沒有足夠的值
CREATE OR REPLACE PROCEDURE p_f4 is
tab_ids type_table_object;
begin
select gp.gid,gp.gno,gp.co bulk collect
into tab_ids
from p_table_test gp;
end;
解決辦法： 將object變數進行轉換，如下所示：
CREATE OR REPLACE PROCEDURE p_f4 is
tab_ids type_table_object;
begin
select type_table_object(gp.gid,gp.gno,gp.co) bulk collect
into tab_ids
from p_table_test gp;
end;

G. PLSQL 幾種游標的用法

游標作用：通常情況下，關系資料庫中的操作總是對整個記錄集產生影響，例如使用SELECT語句檢索數據表時，將得到所有滿足該語句where子句中條件的記錄，而在實際應用過程中，經常需要每次處理一條或者一部分記錄。在這種情況下，需要使用游標在伺服器內部處理結果集合，他可以有助於識別一個數據集合內部指定的記錄，從而可以有選擇的按記錄執行操作。

H. plsql 什麼時候用游標 和 bulk collect

[sql] view plain
<span style="color:black;"></span>
當運行一個pl/sql程序時， pl/sql語句引擎會執行pl/sql語句。但如果在這個過程中引擎遇到sql語句，它會把這個語句傳給sql引擎（後台發生上下文切換）。
在PL/SQL和SQL引擎(engines)中，太多的上下文切換(context switches)會影響性能。這個會發生在當一個循環為集合中的每個元素執行一個單個SQL語句時。而使用批挷定能顯著提高性能。
在SQL語句中，為PL/SQL變數指定值稱為挷定(binding)，
DML語句能傳遞所有集合元素到一個單個操作中，這過程稱為批挷定（bulk binding）。
如果集合有20個元素，批挷定讓你用單個操作等效於執行與20個SELECT，INSERT， UPDATE或DELETE語句。這個技術通過減少在PL/SQL和SQL引擎(engines)間的上下文切換來提高性能。批挷定包括：
1．帶INSERT, UPDATE, and DELETE語句的批挷定：在FORALL語句中嵌入SQL語句
2．帶SELECT語句的批挷定：在SELECT語句中用BULK COLLECT 語句代替INTO 。

作用：BULK COLLECT提供對數據的高速檢索。
優點：可以將多個行引入一個或多個集合中，而不是單獨變數或記錄中,減少了上下文切換，性能高。
缺點：1.消耗更多的內存(PGA)；由於該數集合據存儲在每個會話中,假設一個會話多消耗5M，內存，那麼1000個就消耗約5G內存。
2.BULK COLLECT INTO的目標對象必須是集合類型。
3.不能對使用字元串類型作鍵的關聯數組使用BULK COLLECT子句。
4.如果有一個隱式的數據類型轉換，復合目標的集合(如對象類型集合)就不能用於 BULK COLLECTINTO 子句中。
bulk collect語句可以使用三種方式：
1在select into語句中使用bulk collect

2在fetch into中使用bulk collect

3在returning into中使用bulk collect
[sql] view plain
create table empl_tbl(last_name varchar2(20),
first_name varchar2(10),
salary number(10));

--創建表
create table demo_t(
id number(5),
name varchar2(50),
sales number
);

[sql] view plain
--在select into語句中使用bulk collect
DECLARE
TYPE contractList IS TABLE OF sad.cm_contracts_t.contract_number%TYPE;
contracts contractList;
BEGIN
-- Limit the number of rows to 100.
SELECT contract_number BULK COLLECT INTO contracts FROM sad.cm_contracts_t
WHERE ROWNUM <= 100;

--在fetch into中使用bulk collect
DECLARE
TYPE contracts_list IS TABLE OF sad.cm_contracts_t%ROWTYPE;
contracts_l contracts_list;
CURSOR c1 IS
SELECT contract_number,contract_id FROM contracts_l WHERE deptno > 10;
BEGIN
OPEN c1;
FETCH c1 BULK COLLECT INTO dept_recs;
END;

--在returning into中使用bulk collect
--CREATE TABLE item AS SELECT * FROM sad.sad_prm_item_ti;
declare
-- Local variables here
TYPE lineid IS TABLE OF item.lineid%TYPE;
line_id lineid;
begin
DELETE FROM item t WHERE t.lineid='1'
RETURNING lineid BULK COLLECT INTO line_id;
dbms_output.put_line('Deleted ' || SQL%ROWCOUNT || ' rows:');
FOR i IN line_id.FIRST .. line_id.LAST
LOOP
dbms_output.put_line('item : '||line_id(i));
END LOOP;
end;

[sql] view plain
-- Created on 2013/12/4 by ZWX190516
DECLARE
-- Local variables here
all_row NUMBER(10);
all_rows_bluk NUMBER(10);
temp_last_name empl_tbl.last_name%TYPE;
--首先，定義一個Index-by表數據類型
TYPE last_name_tab IS TABLE OF empl_tbl.last_name%TYPE INDEX BY BINARY_INTEGER;
last_name_arr last_name_tab;
--定義一個Index-by表集合變數
dis_count NUMBER;

--數據量
data_count NUMBER :=100000;
--記錄時間
t1 NUMBER;
t2 NUMBER;
t3 NUMBER;
t4 NUMBER;
BEGIN
all_row := 0;
all_rows_bluk := 0;
temp_last_name := ' ';

--寫入1W筆數據
FOR i IN 1 .. data_count LOOP
INSERT INTO empl_tbl
(last_name, first_name, salary)
VALUES
('carl' || (i), 'wu' || (data_count-1), i);
END LOOP;

COMMIT;

--記錄時間
SELECT DBMS_UTILITY.get_time INTO t1 FROM DUAL;

--查詢不相同數據使用時間
SELECT COUNT(DISTINCT last_name) "Distinct Last Name"
INTO dis_count
FROM empl_tbl;

--記錄時間
SELECT DBMS_UTILITY.get_time INTO t2 FROM DUAL;

--使用簡單游標實現
BEGIN
FOR cur IN (SELECT last_name FROM empl_tbl ORDER BY last_name) LOOP
IF cur.last_name != temp_last_name THEN
all_row := all_row + 1;
END IF;
temp_last_name := cur.last_name;
END LOOP;
dbms_output.put_line('all_rows are ' || all_row);
END;

--記錄時間
SELECT DBMS_UTILITY.get_time INTO t3 FROM DUAL;
--使用Bulk Collect來實現
BEGIN
SELECT last_name BULK COLLECT INTO last_name_arr FROM empl_tbl;
FOR i IN 1 .. last_name_arr.count LOOP
IF temp_last_name != last_name_arr(i) THEN
all_rows_bluk := all_rows_bluk + 1;
END IF;
temp_last_name := last_name_arr(i);
END LOOP;
dbms_output.put_line(' BULK COLLECT all_rows are ' || all_rows_bluk);
END;
--記錄時間
SELECT DBMS_UTILITY.get_time INTO t4 FROM DUAL;

DBMS_OUTPUT.put_line('Execution Time (hsecs)');
DBMS_OUTPUT.put_line('---------------------');
DBMS_OUTPUT.put_line('distince: ' || TO_CHAR(t2 - t1));
DBMS_OUTPUT.put_line('cursor one by one : ' || TO_CHAR(t3 - t2));
DBMS_OUTPUT.put_line('cursor BULK COLLECTe : ' || TO_CHAR(t4 - t3));

END;

I. 關於oracle PLSQL

PL/SQL是ORACLE對標准資料庫語言的擴展，ORACLE公司已經將PL/SQL整合到ORACLE 伺服器和其他工具中了，近幾年中更多的開發人員和DBA開始使用PL/SQL，本文將講述PL/SQL基礎語法，結構和組件、以及如何設計並執行一個PL/SQL程序。

PL/SQL的優點

從版本6開始PL/SQL就被可靠的整合到ORACLE中了，一旦掌握PL/SQL的優點以及其獨有的數據管理的便利性，那麼你很難想像ORACLE缺了PL/SQL的情形。PL/SQL 不是一個獨立的產品，他是一個整合到ORACLE伺服器和ORACLE工具中的技術，可以把PL/SQL看作ORACLE伺服器內的一個引擎，sql語句執行者處理單個的sql語句，PL/SQL引擎處理PL/SQL程序塊。當PL/SQL程序塊在PL/SQL引擎處理時，ORACLE伺服器中的SQL語句執行器處理pl/sql程序塊中的SQL語句。

PL/SQL的優點如下：

. PL/SQL是一種高性能的基於事務處理的語言，能運行在任何ORACLE環境中，支持所有數據處理命令。通過使用PL/SQL程序單元處理SQL的數據定義和數據控制元素。

. PL/SQL支持所有SQL數據類型和所有SQL函數，同時支持所有ORACLE對象類型

. PL/SQL塊可以被命名和存儲在ORACLE伺服器中，同時也能被其他的PL/SQL程序或SQL命令調用，任何客戶/伺服器工具都能訪問PL/SQL程序，具有很好的可重用性。

. 可以使用ORACLE數據工具管理存儲在伺服器中的PL/SQL程序的安全性。可以授權或撤銷資料庫其他用戶訪問PL/SQL程序的能力。

. PL/SQL代碼可以使用任何ASCII文本編輯器編寫，所以對任何ORACLE能夠運行的操作系統都是非常便利的

. 對於SQL，ORACLE必須在同一時間處理每一條SQL語句，在網路環境下這就意味作每一個獨立的調用都必須被oracle伺服器處理，這就佔用大量的伺服器時間，同時導致網路擁擠。而PL/SQL是以整個語句塊發給伺服器，這就降低了網路擁擠。

PL/SQL塊結構

PL/SQL是一種塊結構的語言，組成PL/SQL程序的單元是邏輯塊，一個PL/SQL 程序包含了一個或多個邏輯塊，每個塊都可以劃分為三個部分。與其他語言相同，變數在使用之前必須聲明，PL/SQL提供了獨立的專門用於處理異常的部分，下面描述了PL/SQL塊的不同部分：

聲明部分(Declaration section)

聲明部分包含了變數和常量的數據類型和初始值。這個部分是由關鍵字DECLARE開始，如果不需要聲明變數或常量，那麼可以忽略這一部分；需要說明的是游標的聲明也在這一部分。

執行部分(Executable section)

執行部分是PL/SQL塊中的指令部分，由關鍵字BEGIN開始，所有的可執行語句都放在這一部分，其他的PL/SQL塊也可以放在這一部分。

異常處理部分(Exception section)

這一部分是可選的，在這一部分中處理異常或錯誤，對異常處理的詳細討論我們在後面進行。

PL/SQL塊語法

[DECLARE]
---declaration statements
BEGIN
---executable statements
[EXCEPTION]
---exception statements
END

PL/SQL塊中的每一條語句都必須以分號結束，SQL語句可以使多行的，但分號表示該語句的結束。一行中可以有多條SQL語句，他們之間以分號分隔。每一個PL/SQL塊由BEGIN或DECLARE開始，以END結束。注釋由--標示。

PL/SQL塊的命名和匿名

PL/SQL程序塊可以是一個命名的程序塊也可以是一個匿名程序塊。匿名程序塊可以用在伺服器端也可以用在客戶端。

命名程序塊可以出現在其他PL/SQL程序塊的聲明部分，這方面比較明顯的是子程序，子程序可以在執行部分引用，也可以在異常處理部分引用。

PL/SQL程序塊可背獨立編譯並存儲在資料庫中，任何與資料庫相連接的應用程序都可以訪問這些存儲的PL/SQL程序塊。ORACLE提供了四種類型的可存儲的程序：

. 函數

. 過程

. 包

. 觸發器

函數

函數是命名了的、存儲在資料庫中的PL/SQL程序塊。函數接受零個或多個輸入參數，有一個返回值，返回值的數據類型在創建函數時定義。定義函數的語法如下：

FUNCTION name [{parameter[,parameter,...])] RETURN datatypes IS
[local declarations]
BEGIN
execute statements
[EXCEPTION
exception handlers]
END [name]

過程

存儲過程是一個PL/SQL程序塊，接受零個或多個參數作為輸入(INPUT)或輸出(OUTPUT)、或既作輸入又作輸出(INOUT)，與函數不同，存儲過程沒有返回值，存儲過程不能由SQL語句直接使用，只能通過EXECUT命令或PL/SQL程序塊內部調用，定義存儲過程的語法如下：

PROCEDURE name [(parameter[,parameter,...])] IS
[local declarations]
BEGIN
execute statements
[EXCEPTION
exception handlers ]
END [name]

包(package)

包其實就是被組合在一起的相關對象的集合，當包中任何函數或存儲過程被調用，包就被載入入內存中，包中的任何函數或存儲過程的子程序訪問速度將大大加快。
包由兩個部分組成：規范和包主體(body),規范描述變數、常量、游標、和子程序，包體完全定義子程序和游標。

觸發器(trigger)

觸發器與一個表或資料庫事件聯系在一起的，當一個觸發器事件發生時，定義在表上的觸發器被觸發。

變數和常量

變數存放在內存中以獲得值，能被PL/SQL塊引用。你可以把變數想像成一個可儲藏東西的容器，容器內的東西是可以改變的。

聲明變數

變數一般都在PL/SQL塊的聲明部分聲明，PL/SQL是一種強壯的類型語言，這就是說在引用變數前必須首先聲明，要在執行或異常處理部分使用變數，那麼變數必須首先在聲明部分進行聲明。

聲明變數的語法如下：

Variable_name [CONSTANT] databyte [NOT NULL][:=|DEFAULT expression]

注意:可以在聲明變數的同時給變數強制性的加上NOT NULL約束條件，此時變數在初始化時必須賦值。

給變數賦值

給變數賦值有兩種方式：

. 直接給變數賦值

X:=200;
Y=Y+(X*20);

. 通過SQL SELECT INTO 或FETCH INTO給變數賦值

SELECT SUM(SALARY),SUM(SALARY*0.1)
INTO TOTAL_SALARY,TATAL_COMMISSION
FROM EMPLOYEE
WHERE DEPT=10;

常量

常量與變數相似，但常量的值在程序內部不能改變，常量的值在定義時賦予，，他的聲明方式與變數相似，但必須包括關鍵字CONSTANT。常量和變數都可被定義為SQL和用戶定義的數據類型。

ZERO_VALUE CONSTANT NUMBER:=0;

這個語句定了一個名叫ZERO_VALUE、數據類型是NUMBER、值為0的常量。

標量(scalar)數據類型

標量(scalar)數據類型沒有內部組件，他們大致可分為以下四類：

. number
. character
. date/time
. boolean

表1顯示了數字數據類型；表2顯示了字元數據類型；表3顯示了日期和布爾數據類型。

表1 Scalar Types:Numeric

Datatype
Range
Subtypes
description

BINARY_INTEGER
-214748-2147483647
NATURAL
NATURAL
NPOSITIVE
POSITIVEN
SIGNTYPE
用於存儲單位元組整數。
要求存儲長度低於NUMBER值。
用於限制范圍的子類型(SUBTYPE):
NATURAL:用於非負數
POSITIVE:只用於正數
NATURALN:只用於非負數和非NULL值
POSITIVEN:只用於正數，不能用於NULL值
SIGNTYPE:只有值:-1、0或1.

NUMBER
1.0E-130-9.99E125
DEC
DECIMAL
DOUBLE
PRECISION
FLOAT
INTEGERIC
INT
NUMERIC
REAL
SMALLINT
存儲數字值，包括整數和浮點數。可以選擇精度和刻度方式，語法：
number[（[,]）]。
預設的精度是38,scale是0.

PLS_INTEGER
-2147483647-2147483647

與BINARY_INTEGER基本相同，但採用機器運算時，PLS_INTEGER提供更好的性能 。

表2 字元數據類型

datatype
rang
subtype
description

CHAR
最大長度32767位元組
CHARACTER
存儲定長字元串，如果長度沒有確定，預設是1

LONG
最大長度2147483647位元組

存儲可變長度字元串

RAW
最大長度32767位元組

用於存儲二進制數據和位元組字元串，當在兩個資料庫之間進行傳遞時，RAW數據不在字元集之間進行轉換。

LONGRAW
最大長度2147483647

與LONG數據類型相似，同樣他也不能在字元集之間進行轉換。

ROWID
18個位元組

與資料庫ROWID偽列類型相同，能夠存儲一個行標示符，可以將行標示符看作資料庫中每一行的唯一鍵值。

VARCHAR2
最大長度32767位元組
STRINGVARCHAR
與VARCHAR數據類型相似，存儲可變長度的字元串。聲明方法與VARCHAR相同

表3 DATE和BOOLEAN

datatype
range
description

BOOLEAN
TRUE/FALSE
存儲邏輯值TRUE或FALSE,無參數

DATE
01/01/4712 BC
存儲固定長的日期和時間值，日期值中包含時間

J. plsql裡面兩個集合的合集，差集，交集怎麼寫

交集是兩個集合的公共元素，即兩個方程的公共解；
並集是兩個集合的元素的總個數（相同的元素只寫一次）；
差集：如果兩個集合有交集，則大集元素中所有不屬於小集合的元素的集合是差集，如果沒有交集（空集），則A-B=A, B-A=B