7.PL_SQL——在PL_SQL程序中內(nèi)嵌查詢語句、DML語句、事物處理語句和游標(biāo)屬性
在PL/SQL中可以使用的SQL語句主要有以下幾類：
專注于為中小企業(yè)提供成都網(wǎng)站設(shè)計(jì)、成都網(wǎng)站建設(shè)服務(wù),電腦端+手機(jī)端+微信端的三站合一,更高效的管理,為中小企業(yè)涿鹿免費(fèi)做網(wǎng)站提供優(yōu)質(zhì)的服務(wù)。我們立足成都，凝聚了一批互聯(lián)網(wǎng)行業(yè)人才，有力地推動(dòng)了上千企業(yè)的穩(wěn)健成長，幫助中小企業(yè)通過網(wǎng)站建設(shè)實(shí)現(xiàn)規(guī)模擴(kuò)充和轉(zhuǎn)變。
SELECT 查詢語句，DML語句，Transaction 事物處理語句以及游標(biāo)的屬性，本文將對(duì)這幾類語句在PL/SQL中的用法逐一介紹。
一、查詢語句—SELECT
SELECT 語句用來查詢一條或多條語句。雖然SELECT 語句也屬于DML語句，但SELECT是只讀的，所以單獨(dú)列出。
在PL/SQL中使用SELECT 語句的格式如下：
SELECT select_list
INTO {variable_name[,variable_name]...
| record_name}
FROM table
[WHERE condition];
}
這里INTO是必選項(xiàng)，是指將查詢出來的結(jié)果導(dǎo)入到變量中，因此INTO 后面跟的變量個(gè)數(shù)、數(shù)據(jù)類型和順序必須和SELECT 后面跟的選項(xiàng)個(gè)數(shù)、數(shù)據(jù)類型以及順序一致。
如果使用record這種變量類型，可以一次性導(dǎo)入而不需要逐一指明（后文將詳解）。
要使用INTO，查詢結(jié)果只能有一條（行）記錄，否則會(huì)發(fā)生混淆。下面看這個(gè)例子：
SQL> edit
DECLARE
-- v_fname VARCHAR2(25);
v_fname employees.first_name%TYPE;
BEGIN
SELECT first_name INTO v_fname
FROM employees WHERE employee_id = 200;
DBMS_OUTPUT.PUT_LINE('First Name is: '|| v_fname);
END;
/
SQL> /
FirstName is: Jennifer PL/SQLprocedure successfully completed.
如果現(xiàn)在要查詢的employee_id 大于等于200，那么會(huì)出現(xiàn)什么結(jié)果呢？
SQL> edit
DECLARE
v_fname employees.first_name%TYPE;
BEGIN
SELECT first_name INTO v_fname
FROM employees WHERE employee_id >=200;
DBMS_OUTPUT.PUT_LINE('First Name is: '|| v_fname);
END;
/
SQL> /
DECLARE * ERRORat line 1: ORA-01422:exact fetch returns more than requested number of rows ORA-06512:at line 5 -- 報(bào)錯(cuò)了，提示查詢結(jié)果多余1條
現(xiàn)在再來看一個(gè)查詢多列的例子（注意，仍然是1行結(jié)果）：
SQL> edit
DECLARE
v_emp_hiredateemployees.hire_date%TYPE;
v_emp_salary employees.salary%TYPE;
BEGIN
SELECT hire_date, salary
INTO v_emp_hiredate, v_emp_salary
-- 查詢了兩列記錄，需要注意的是，數(shù)據(jù)個(gè)數(shù)、數(shù)據(jù)類型和順序必須一一對(duì)應(yīng)
FROM employees
WHERE employee_id = 100;
DBMS_OUTPUT.PUT_LINE('Hire date is :'|| v_emp_hiredate);
DBMS_OUTPUT.PUT_LINE('Salary is : ' ||v_emp_salary);
END;
/
SQL> /
Hiredate is : 17-JUN-03 Salaryis : 24000 PL/SQLprocedure successfully completed.
除了基本的查詢函數(shù)外，一些聚合函數(shù)也可以直接在PL/SQL中使用，只要保證這些聚合函數(shù)的運(yùn)行結(jié)果仍然是一行記錄即可：
SQL> edit
DECLARE
v_sum_sal NUMBER(10,2);
v_deptno NUMBER NOT NULL := 60;
BEGIN
SELECT SUM(salary) -- group function
-- 使用了SQL中的聚合函數(shù)SUM()
--SUM（）的結(jié)果仍然是一行
INTO v_sum_sal
-- 將SUM()函數(shù)的運(yùn)行結(jié)果導(dǎo)入到變量v_sum_sal中
FROM employees
WHERE department_id = v_deptno;
DBMS_OUTPUT.PUT_LINE('The sum of salaryis '|| v_sum_sal);
END;
SQL> /
Thesum of salary is 28800 PL/SQLprocedure successfully completed.
如果要查詢多條記錄并導(dǎo)入變量，則需要涉及游標(biāo)的概念，游標(biāo)將在后文中詳解。
二、在PL/SQL中使用DML語句
PL/SQL中也可以使用DML語句來修改數(shù)據(jù)庫中的表，DML語句主要指的是INSERT, UPDATE,DELETE和MERGE等語句，其中INSERT,UPDATE,DELETE 多半只涉及一張表，而MERGE則涉及兩張表，如果兩張表的數(shù)據(jù)不一致，就使用其中一張表來更新另外一張。
下面來看DML語句中INSERT使用的例子：
SQL> edit
BEGIN INSERT INTO employees (employee_id, first_name,last_name,email, hire_date,job_id,salary) VALUES(employees_seq.NEXTVAL,'Ruth','Cores', 'RCORES',CURRENT_DATE,'AD_ASST',4000); END; /
SQL> /
PL/SQLprocedure successfully completed.
SQL> select * from employees wherefirst_name like 'Ruth';
EMPLOYEE_ID FIRST_NAME LAST_NAME ------------------------------- ------------------------- EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY --------------------------------------------- --------- ---------- ---------- COMMISSION_PCTMANAGER_ID DEPARTMENT_ID ------------------------ ------------- 207 Ruth Cores RCORES 14-AUG-14 AD_ASST 4000
再來看一個(gè)UPDATE的例子：
SQL> edit
DECLARE sal_increase employees.salary%TYPE :=800; BEGIN UPDATE employees SET salary = salary + sal_increase WHERE job_id = 'ST_CLERK'; END; /
SQL> /
PL/SQLprocedure successfully completed.
UPDATE語句中也可以使用INTO關(guān)鍵字，來看下面這個(gè)例子：
SQL> edit
DECLARE
myname emp.last_name%TYPE;
mysal emp.salary%TYPE;
BEGIN
FOR rec IN (SELECT * FROM emp)
LOOP
-- 這里不用理解loop循環(huán)的含義，后文會(huì)詳解loop的用法
UPDATE emp SET salary = salary *1.5
WHERE employee_id = rec.employee_id
RETURNING salary, last_name
INTO mysal, myname;
-- 在UPDATE語句中使用INTO，將更新后的值導(dǎo)入到變量中
DBMS_OUTPUT.PUT_LINE('New salaryfor ' || myname || ' = ' || mysal);
END LOOP;
END;
/SQL> /
Newsalary for OConnell = 3900 Newsalary for Dellinger = 5100 Newsalary for Cabrio = 4500 Newsalary for McCain = 4800 Newsalary for Jones = 4200 Newsalary for Walsh = 4650 Newsalary for Feeney = 4500 PL/SQLprocedure successfully completed.
在PL/SQL中使用DELETE也很簡單：
SQL> select * from tt；
-- 首先使用SQL語句查詢所有的信息 ID ---------- 0 1
SQL> edit
DECLARE v_id tt.id%TYPE := 1; BEGIN DELETE FROM tt WHERE id = v_id; END; /
SQL> /
PL/SQL procedure successfully completed.
SQL> select * from tt;
ID ---------- 0 -- 已經(jīng)刪除了一條記錄
INSERT, UPDATE,DELETE 語句基本上可以直接在PL/SQL中使用，和SQL語言并無差別。MERGE這種操作使用不多，下面舉一個(gè)例子來簡單介紹一下：
1. 先創(chuàng)建表ttt，并插入三條記錄
SQL> create table ttt(t_id int,
2 t_name varchar2(20)); Tablecreated.
SQL> insert into ttt values(0,'a');
1row created.
SQL> insert into ttt values(1,'b');
1row created.
SQL> insert into ttt values(2,'c');
1row created.
SQL> select * from ttt;
T_ID T_NAME ------------------------------ 0 a 1 b 2 c
2. 再創(chuàng)建表t2，插入一條記錄
SQL> create table t2(t2_id int,
2 t2_name varchar2(20));
Table created.
SQL> insert into t2 values(0,'d');
1row created.
SQL> insert into t2 values(3,'e');
1row created.
SQL> select * from t2;
T2_ID T2_NAME ------------------------------ 0 d 3 e
3. 現(xiàn)在在PL/SQL中使用MERGE的方法用t2的數(shù)據(jù)來更新ttt的數(shù)據(jù)
SQL> edit
BEGIN MERGEINTO ttt USING t2 ON (ttt.t_id = t2.t2_id) WHEN MATCHED THEN UPDATE SET ttt.t_name = t2.t2_name WHEN NOT MATCHED THEN INSERT VALUES(t2.t2_id, t2.t2_name); END; /
SQL> select * from ttt;
-- 表ttt已經(jīng)用t2合并更新了 T_ID T_NAME ------------------------------ 0 d 1 b 2 c 3 e
DML語句操作數(shù)據(jù)庫時(shí)，還可以使用Records來同時(shí)插入或更新一組數(shù)據(jù)，如下面這兩個(gè)例子：
DECLARE my_book books%ROWTYPE; BEGIN my_book.isbn := '1-56592-335-9'; my_book.title := 'Oracle PL/SQLProgramming 5th'; my_book.summary := 'General userguide'; my_book.author := 'Feusertein, Steven'; my_book.page_count := 1000; INSERT INTO books VALUES my_book; END; /
DECLARE my_book books%ROWTYPE; BEGIN my_book.isbn := '1-56592-335-9'; my_book.title := 'Oracle PL/SQLProgramming 5th'; my_book.summary := 'General userguide'; my_book.author := 'Feusertein, Steven'; my_book.page_count := 1000; UPDATE books SET ROW = my_book WHERE isbin = my_book.isbn; END; /
這兩個(gè)例子中各條記錄都保存在變量類型為Record的變量my_book中，然后將該變量作為一個(gè)整體插入或更新到表中。
Records這種變量類型后文中將詳解，這里只簡要的介紹一下它的基本用法。
INDEX等DDL語句不能夠直接使用，必須使用動(dòng)態(tài)SQL語句的形式來使用。
三、事物控制語句（Transaction control）
事物控制語句主要指的是commit、rollback和savepoint。
PL/SQL的Block 和事物（Transaction）之間沒有一一對(duì)應(yīng)的關(guān)系，可能一個(gè)PL/SQL的塊已經(jīng)結(jié)束了（如出現(xiàn)異常，退出程序了），但是事物（Tansaction）還沒有被提交或者是回滾，就會(huì)導(dǎo)致數(shù)據(jù)的丟失。（相關(guān)知識(shí)后文會(huì)詳解）另一方面，可能一個(gè)Transaction已經(jīng)結(jié)束了，而PL/SQL 的block還在繼續(xù)。一個(gè)Transaction可以跨越多個(gè)Block，如上文中INSERT 和UPDATE的例子中，雖然對(duì)表做了修改，但并沒有使用commit加以提交，因此Transaction還在繼續(xù)。
現(xiàn)在來看下面這個(gè)例子：
1. 首先以SYS用戶登錄：
SQL> show user
USERis "SYS"
SQL> desc v$transaction
Name Null? Type ------------------------------------------------- ---------------------------- ADDR RAW(4) XIDUSN NUMBER XIDSLOT NUMBER XIDSQN NUMBER XID RAW(8)
SQL> select xid,xidusn from v$transaction;
norows selected --當(dāng)前沒有記錄
2.現(xiàn)在切換成hr用戶
SQL> show user
USERis "HR"
SQL> create table tt (id int);
Tablecreated.
SQL> insert into tt values(0);
1row created.
3.現(xiàn)在再次切換到SYS用戶來查看xid這一行的記錄
SQL> show user
USER is "SYS"
SQL> select xid,xidusn fromv$transaction
XID XIDUSN ------------------------------ ---------- 040001009B020000 4
4.現(xiàn)在切換回hr用戶，然后提交剛才的事物
SQL> show user
USERis "HR"
SQL> commit;
Commitcomplete.
SQL> select * from tt;
ID ---------- 0
5.此時(shí)再以SYS用戶查看動(dòng)態(tài)性能試圖（v$transaction），就會(huì)發(fā)現(xiàn)剛才的記錄消失了：
SQL> show user
USERis "SYS"
SQL> select xid,xidusn fromv$transaction;
no rows selected
五、游標(biāo)的屬性
1.、什么是游標(biāo)
如果要對(duì)游標(biāo)Cursor有深入的了解，就必須對(duì)Oracle的體系結(jié)構(gòu)有深入的了解，本章只做基本概述。
Cursor 是一個(gè)指針，指向的是Oracle服務(wù)器分配的一塊私有內(nèi)存區(qū)，用來處理SQL語句。通常這個(gè)私有的內(nèi)存區(qū)在用戶session的PGA里面，如果PGA是Dedicated（專有模式），則這塊PGA是獨(dú)立于SGA的；如果是共享服務(wù)器模式，那么這個(gè)游標(biāo)是放在SGA里的（以后的文章會(huì)介紹PGA,SGA的相關(guān)概念）。持有游標(biāo)就可以訪問該游標(biāo)所指向的內(nèi)存區(qū)域。更多Cursor相關(guān)知識(shí)可查詢Oracle中關(guān)于OCI的知識(shí)。
前文中介紹過，在PL/SQL中使用SELECT INTO語句，可以將查詢到的一條結(jié)果放到變量中，而使用CURSOR就可以存放多條記錄。
游標(biāo)包括隱式游標(biāo)和顯式游標(biāo)。其中隱式游標(biāo)的創(chuàng)建、管理都是有Oracle其中隱式游標(biāo)的創(chuàng)建、管理都是有Oracle 服務(wù)器完成的；而顯式游標(biāo)則需要程序員通過一系列的系統(tǒng)調(diào)用來聲明、打開、執(zhí)行以及關(guān)閉游標(biāo)，顯式游標(biāo)后文中會(huì)詳解，目前只涉及到隱式游標(biāo)。
2.游標(biāo)屬性的作用
當(dāng)我們使用UPDATE和DELETE（INSERT不需要）數(shù)據(jù)后，想知道影響了多少條記錄
使用游標(biāo)屬性可以查詢相關(guān)信息。這些主要包括以下三種：
A. SQL%FOUND：如果返回布爾型變量 TRUE，表示至少有1條記錄被影響到了。
B. SQL%NOTFOUND：如果返回布爾型變量TRUE，表示一條記錄都沒有被影響到。
C. SQL%ROWCOUNT:返回的數(shù)值是多少，就表示有多少條記錄被影響了。
下面來看一段使用游標(biāo)的例子：
SQL> select * from ttt;
T_ID T_NAME ------------------------------ 0 a 1 b 2 c
SQL> edit
DECLARE test_id ttt.t_id%TYPE :=1; BEGIN DELETE FROM ttt WHERE t_id = test_id; COMMIT; DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || 'rows deleted!'); END; /
SQL> /
1 rows deleted! PL/SQL procedure successfullycompleted.
SQL> select * from ttt;
T_ID T_NAME ------------------------------ 0 a 2 c
分享標(biāo)題：7.PL_SQL——在PL_SQL程序中內(nèi)嵌查詢語句、DML語句、事物處理語句和游標(biāo)屬性
本文URL：http://jinyejixie.com/article48/jjpchp.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站設(shè)計(jì)、標(biāo)簽優(yōu)化、網(wǎng)頁設(shè)計(jì)公司、微信公眾號(hào)、網(wǎng)站排名、動(dòng)態(tài)網(wǎng)站
聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)
- 偽用戶體驗(yàn)站與常規(guī)SEO站之間排名競爭分析 2023-01-21
- 提升營銷型網(wǎng)站的用戶體驗(yàn)須做好哪些 2022-08-08
- 網(wǎng)站建設(shè)提升用戶體驗(yàn)，靠的不僅僅是網(wǎng)站的設(shè)計(jì) 2016-11-10
- 404頁面對(duì)于用戶體驗(yàn)和SEO優(yōu)化的重要性 2020-08-01
- 網(wǎng)站優(yōu)化應(yīng)該避免哪些差的用戶體驗(yàn) 2016-06-12
- 移動(dòng)網(wǎng)站建設(shè)需要注意什么？怎樣設(shè)計(jì)才能提高用戶體驗(yàn)？ 2022-10-22
- 用戶體驗(yàn)永遠(yuǎn)是上海網(wǎng)站建設(shè)的根本 2022-09-17
- 網(wǎng)站建設(shè)中15種差的用戶體驗(yàn) 2022-12-02
- 什么是網(wǎng)站用戶體驗(yàn)設(shè)計(jì)？ 2022-08-08
- SEO的核心是用戶體驗(yàn)OR用戶忠誠度 2020-08-24
- 高級(jí)用戶體驗(yàn)設(shè)計(jì)師需要具備的習(xí)慣有哪些 2022-08-04
- 網(wǎng)站設(shè)計(jì)中如何提高用戶體驗(yàn)度？ 2023-02-04