在PL/SQL中可以使用的SQL语句主要有以下几类:
专注于为中小企业提供成都网站设计、成都网站建设服务,电脑端+手机端+微信端的三站合一,更高效的管理,为中小企业涿鹿免费做网站提供优质的服务。我们立足成都,凝聚了一批互联网行业人才,有力地推动了上千企业的稳健成长,帮助中小企业通过网站建设实现规模扩充和转变。
SELECT 查询语句,DML语句,Transaction 事物处理语句以及游标的属性,本文将对这几类语句在PL/SQL中的用法逐一介绍。
一、查询语句—SELECT
SELECT 语句用来查询一条或多条语句。虽然SELECT 语句也属于DML语句,但SELECT是只读的,所以单独列出。
在PL/SQL中使用SELECT 语句的格式如下:
SELECT select_list
INTO {variable_name[,variable_name]...
| record_name}
FROM table
[WHERE condition];
}
这里INTO是必选项,是指将查询出来的结果导入到变量中,因此INTO 后面跟的变量个数、数据类型和顺序必须和SELECT 后面跟的选项个数、数据类型以及顺序一致。
如果使用record这种变量类型,可以一次性导入而不需要逐一指明(后文将详解)。
要使用INTO,查询结果只能有一条(行)记录,否则会发生混淆。下面看这个例子:
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.
如果现在要查询的employee_id 大于等于200,那么会出现什么结果呢?
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 -- 报错了,提示查询结果多余1条
现在再来看一个查询多列的例子(注意,仍然是1行结果):
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 -- 查询了两列记录,需要注意的是,数据个数、数据类型和顺序必须一一对应 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.
除了基本的查询函数外,一些聚合函数也可以直接在PL/SQL中使用,只要保证这些聚合函数的运行结果仍然是一行记录即可:
SQL> edit
DECLARE v_sum_sal NUMBER(10,2); v_deptno NUMBER NOT NULL := 60; BEGIN SELECT SUM(salary) -- group function -- 使用了SQL中的聚合函数SUM() --SUM()的结果仍然是一行 INTO v_sum_sal -- 将SUM()函数的运行结果导入到变量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.
如果要查询多条记录并导入变量,则需要涉及游标的概念,游标将在后文中详解。
二、在PL/SQL中使用DML语句
PL/SQL中也可以使用DML语句来修改数据库中的表,DML语句主要指的是INSERT, UPDATE,DELETE和MERGE等语句,其中INSERT,UPDATE,DELETE 多半只涉及一张表,而MERGE则涉及两张表,如果两张表的数据不一致,就使用其中一张表来更新另外一张。
下面来看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
再来看一个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关键字,来看下面这个例子:
SQL> edit
DECLARE myname emp.last_name%TYPE; mysal emp.salary%TYPE; BEGIN FOR rec IN (SELECT * FROM emp) LOOP -- 这里不用理解loop循环的含义,后文会详解loop的用法 UPDATE emp SET salary = salary *1.5 WHERE employee_id = rec.employee_id RETURNING salary, last_name INTO mysal, myname; -- 在UPDATE语句中使用INTO,将更新后的值导入到变量中 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 -- 已经删除了一条记录
INSERT, UPDATE,DELETE 语句基本上可以直接在PL/SQL中使用,和SQL语言并无差别。MERGE这种操作使用不多,下面举一个例子来简单介绍一下:
1. 先创建表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. 再创建表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. 现在在PL/SQL中使用MERGE的方法用t2的数据来更新ttt的数据
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已经用t2合并更新了 T_ID T_NAME ------------------------------ 0 d 1 b 2 c 3 e
DML语句操作数据库时,还可以使用Records来同时插入或更新一组数据,如下面这两个例子:
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; /
这两个例子中各条记录都保存在变量类型为Record的变量my_book中,然后将该变量作为一个整体插入或更新到表中。
Records这种变量类型后文中将详解,这里只简要的介绍一下它的基本用法。
INDEX等DDL语句不能够直接使用,必须使用动态SQL语句的形式来使用。
三、事物控制语句(Transaction control)
事物控制语句主要指的是commit、rollback和savepoint。
PL/SQL的Block 和事物(Transaction)之间没有一一对应的关系,可能一个PL/SQL的块已经结束了(如出现异常,退出程序了),但是事物(Tansaction)还没有被提交或者是回滚,就会导致数据的丢失。(相关知识后文会详解)另一方面,可能一个Transaction已经结束了,而PL/SQL 的block还在继续。一个Transaction可以跨越多个Block,如上文中INSERT 和UPDATE的例子中,虽然对表做了修改,但并没有使用commit加以提交,因此Transaction还在继续。
现在来看下面这个例子:
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 --当前没有记录
2.现在切换成hr用户
SQL> show user
USERis "HR"
SQL> create table tt (id int);
Tablecreated.
SQL> insert into tt values(0);
1row created.
3.现在再次切换到SYS用户来查看xid这一行的记录
SQL> show user
USER is "SYS"
SQL> select xid,xidusn fromv$transaction
XID XIDUSN ------------------------------ ---------- 040001009B020000 4
4.现在切换回hr用户,然后提交刚才的事物
SQL> show user
USERis "HR"
SQL> commit;
Commitcomplete.
SQL> select * from tt;
ID ---------- 0
5.此时再以SYS用户查看动态性能试图(v$transaction),就会发现刚才的记录消失了:
SQL> show user
USERis "SYS"
SQL> select xid,xidusn fromv$transaction;
no rows selected
五、游标的属性
1.、什么是游标
如果要对游标Cursor有深入的了解,就必须对Oracle的体系结构有深入的了解,本章只做基本概述。
Cursor 是一个指针,指向的是Oracle服务器分配的一块私有内存区,用来处理SQL语句。通常这个私有的内存区在用户session的PGA里面,如果PGA是Dedicated(专有模式),则这块PGA是独立于SGA的;如果是共享服务器模式,那么这个游标是放在SGA里的(以后的文章会介绍PGA,SGA的相关概念)。持有游标就可以访问该游标所指向的内存区域。更多Cursor相关知识可查询Oracle中关于OCI的知识。
前文中介绍过,在PL/SQL中使用SELECT INTO语句,可以将查询到的一条结果放到变量中,而使用CURSOR就可以存放多条记录。
游标包括隐式游标和显式游标。其中隐式游标的创建、管理都是有Oracle其中隐式游标的创建、管理都是有Oracle 服务器完成的;而显式游标则需要程序员通过一系列的系统调用来声明、打开、执行以及关闭游标,显式游标后文中会详解,目前只涉及到隐式游标。
2.游标属性的作用
当我们使用UPDATE和DELETE(INSERT不需要)数据后,想知道影响了多少条记录
使用游标属性可以查询相关信息。这些主要包括以下三种:
A. SQL%FOUND:如果返回布尔型变量 TRUE,表示至少有1条记录被影响到了。
B. SQL%NOTFOUND:如果返回布尔型变量TRUE,表示一条记录都没有被影响到。
C. SQL%ROWCOUNT:返回的数值是多少,就表示有多少条记录被影响了。
下面来看一段使用游标的例子:
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
当前题目:7.PL_SQL——在PL_SQL程序中内嵌查询语句、DML语句、事物处理语句和游标属性
标题路径:http://cqwzjz.cn/article/jjpchp.html