查询
SELECT语句用于从数据库中查询数据
当在PL/SQL中使用SELECT语句时要和INTO子句
起使用查询
返回值被赋予INTO子句中变量变量声明是在DELCARE中
SELECT INTO语法如下: SELECT [DISTICT|ALL]{*|column[,column,...]}
INTO (variable[,variable,...] |record)
FROM {table|(sub-query)}[alias]
WHERE............
PL/SQL中SELECT语句只返回
行数据如果超过行数据那么就要使用显式游标(对游标讨论我们将在后面进行)INTO子句中要有和SELECT子句中相同列数量变量INTO子句中也可以是记录变量
%TYPE属性
在PL/SQL中可以将变量和常量声明为内建或用户定义
数据类型以引用个列名同时继承他数据类型和大小这种动态赋值思路方法是非常有用比如变量引用列数据类型和大小改变了如果使用了%TYPE,那么用户就不必修改代码否则就必须修改代码 例:
v_empno SCOTT.EMP.EMPNO%TYPE;
v_salary EMP.SALARY%TYPE;
不但列名可以使用%TYPE,而且变量、游标、记录
或声明常量都可以使用%TYPE这对于定义相同数据类型变量非常有用 DELCARE
V_A NUMBER(5):=10;
V_B V_A%TYPE:=15;
V_C V_A%TYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE
(\'V_A=\'||V_A||\'V_B=\'||V_B||\'V_C=\'||V_C);
END
SQL>/
V_A=10 V_B=15 V_C=
PL/SQL procedure successfully completed.
SQL>
其他DML语句
其它操作数据
DML语句是:INSERT、UPDATE、DELETE和LOCK TABLE,这些语句在PL/SQL中语法和在SQL中语法相同我们在前面已经讨论过DML语句使用这里就不再重复了在
DML语句中可以使用任何在DECLARE部分声明
变量如果是嵌套块那么要注意变量作用范围 例:
CREATE OR REPLACE PROCEDURE FIRE_EMPLOYEE (pempno in number)
AS
v_ename EMP.ENAME%TYPE;
BEGIN
SELECT ename INTO v_ename
FROM emp
WHERE empno=p_empno;
INSERT INTO FORMER_EMP(EMPNO,ENAME)
VALUES (p_empno,v_ename);
DELETE FROM emp
WHERE empno=p_empno;
UPDATE former_emp
SET date_deleted=SYSDATE
WHERE empno=p_empno;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(\'Employee Number Not Found!\');
END
DML语句
结果 当执行
条DML语句后DML语句结果保存在 4个游标属性中这些属性用于控制
流程或者了解状态当运行DML语句时PL/SQL打开个内建游标并处理结果游标是维护查询结果内存中个区域游标在运行DML语句时打开完成后关闭隐式游标只使用SQL%FOUND,SQL%NOTFOUND,SQL%ROWCOUNT 3个属性.SQL%FOUND,SQL%NOTFOUND是布尔值SQL%ROWCOUNT是整数值 SQL%FOUND
和SQL%NOTFOUND
在执行任何DML语句前SQL%FOUND和SQL%NOTFOUND
值都是NULL,在执行DML语句后SQL%FOUND属性值将是: . TRUE :INSERT
. TRUE :DELETE和UPDATE
至少有行被DELETE或UPDATE. . TRUE :SELECT INTO至少返回
行 当SQL%FOUND为TRUE时,SQL%NOTFOUND为FALSE
SQL%ROWCOUNT
在执行任何DML语句的前
SQL%ROWCOUNT值都是NULL,对于SELECT INTO语句如果执行成功SQL%ROWCOUNT值为1,如果没有成功SQL%ROWCOUNT值为0同时产生个异常NO_DATA_FOUND. SQL%ISOPEN
SQL%ISOPEN是
个布尔值如果游标打开则为TRUE, 如果游标关闭则为FALSE.对于隐式游标而言SQL%ISOPEN总是FALSE这是
隐式游标在DML语句执行时打开结束时就立即关闭 事务控制语句
事务是
个工作逻辑单元可以包括个或多个DML语句事物控制帮助用户保证数据致性如果事务控制逻辑单元中任何个DML语句失败那么整个事务都将回滚在PL/SQL中用户可以明确地使用COMMIT、ROLLBACK、SAVEPOINT以及SET TRANSACTION语句 COMMIT语句终止事务
永久保存数据库变化同时释放所有LOCK,ROLLBACK终止现行事务释放所有LOCK但不保存数据库任何变化,SAVEPOINT用于设置中间点当事务
过多数据库操作时中间点是非常有用SET TRANSACTION用于设置事务属性比如read-write和隔离级等 显式游标
当查询返回结果超过
行时就需要个显式游标此时用户不能使用select 
o语句
PL/SQL管理隐式游标
当查询开始时隐式游标打开查询结束时隐式游标自动关闭显式游标在PL/SQL块声明部分声明在执行部分或异常处理部分打开取数据,关闭 使用游标
这里要做
个声明我们所说游标通常是指显式游标因此从现在起没有特别指明情况我们所说游标都是指显式游标要在中使用游标必须首先声明游标 声明游标
语法:
CURSOR cursor_name IS select_statement;
在PL/SQL中游标名是
个未声明变量不能给游标名赋值或用于表达式中 例:
DELCARE
CURSOR C_EMP IS SELECT empno,ename,salary
FROM emp
WHERE salary>2000
ORDER BY ename;
........
BEGIN
在游标定义中SELECT语句中不
定非要表可以是视图也可以从多个表或视图中选择列甚至可以使用*来选择所有列 打开游标
使用游标中
值的前应该首先打开游标打开游标
化查询处理打开游标语法是: OPEN cursor_name
cursor_name是在声明部分定义
游标名 例:
OPEN C_EMP;
关闭游标
语法:
CLOSE cursor_name
例:
CLOSE C_EMP;
从游标提取数据
从游标得到
行数据使用FETCH命令每次提取数据后游标都指向结果集下行语法如下: FETCH cursor_name INTO variable[,variable,...]
对于SELECT定义
游标每列FETCH变量列表都应该有个变量和的相对应变量类型也要相同 例:
SET SERVERIUTPUT _disibledevent=>
DECLARE
v_ename EMP.ENAME%TYPE;
v_salary EMP.SALARY%TYPE;
CURSOR c_emp IS SELECT ename,salary FROM emp;
BEGIN
OPEN c_emp;
FETCH c_emp INTO v_ename,v_salary;
DBMS_OUTPUT.PUT_LINE(\'Salary of Employee\'|| v_ename ||\'is\'|| v_salary);
FETCH c_emp INTO v_ename,v_salary;
DBMS_OUTPUT.PUT_LINE(\'Salary of Employee\'|| v_ename ||\'is\'|| v_salary);
FETCH c_emp INTO v_ename,v_salary;
DBMS_OUTPUT.PUT_LINE(\'Salary of Employee\'|| v_ename ||\'is\'|| v_salary);
CLOSE c_emp;
END
这段代码无疑是非常麻烦
如果有多行返回结果可以使用循环并用游标属性为结束循环条件以这种方式提取数据可读性和简洁性都大为提高下面我们使用循环重新写上面:
SET SERVERIUTPUT _disibledevent=>
DECLARE
v_ename EMP.ENAME%TYPE;
v_salary EMP.SALARY%TYPE;
CURSOR c_emp IS SELECT ename,salary FROM emp;
BEGIN
OPEN c_emp;
LOOP
FETCH c_emp INTO v_ename,v_salary;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(\'Salary of Employee\'|| v_ename ||\'is\'|| v_salary);
END
记录变量
定义
个记录变量使用TYPE命令和%ROWTYPE有关%ROWsTYPE更多信息请参阅相关资料 记录变量用于从游标中提取数据行
当游标选择很多列时候那么使用记录比为每列声明个变量要方便得多
当在表上使用%ROWTYPE并将从游标中取出
值放入记录中时如果要选择表中所有列那么在SELECT子句中使用*比将所有列名列出来要安全得多 例:
SET SERVERIUTPUT _disibledevent=>
DECLARE
R_emp EMP%ROWTYPE;
CURSOR c_emp IS SELECT * FROM emp;
BEGIN
OPEN c_emp;
LOOP
FETCH c_emp INTO r_emp;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUT.PUT.PUT_LINE(\'Salary of Employee\'||r_emp.ename||\'is\'|| r_emp.salary);
END LOOP;
CLOSE c_emp;
END;
%ROWTYPE也可以用游标名来定义
这样话就必须要首先声明游标: SET SERVERIUTPUT _disibledevent=>
DECLARE
CURSOR c_emp IS SELECT ename,salary FROM emp;
R_emp c_emp%ROWTYPE;
BEGIN
OPEN c_emp;
LOOP
FETCH c_emp INTO r_emp;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUT.PUT.PUT_LINE(\'Salary of Employee\'||r_emp.ename||\'is\'|| r_emp.salary);
END LOOP;
CLOSE c_emp;
END;
带参数
游标 和存储过程和
相似可以将参数传递给游标并在查询中使用这对于处理在某种条件下打开游标情况非常有用它语法如下: CURSOR cursor_name[(parameter[,parameter],...)] IS select_statement;
定义参数
语法如下: Parameter_name [IN] data_type[{:=|DEFAULT} value]
和存储过程区别
是游标只能接受传递值而不能返回值参数只定义数据类型没有大小 另外可以给参数设定
个缺省值当没有参数值传递给游标时就使用缺省值游标中定义参数只是个占位符在别处引用该参数不定可靠 在打开游标时给参数赋值
语法如下: OPEN cursor_name[value[,value]....];
参数值可以是文字或变量
例:
DECALRE
CURSOR c_dept IS SELECT * FROM dept ORDER BY deptno;
CURSOR c_emp (p_dept VARACHAR2) IS
SELECT ename,salary
FROM emp
WHERE deptno=p_dept
ORDER BY ename
r_dept DEPT%ROWTYPE;
v_ename EMP.ENAME%TYPE;
v_salary EMP.SALARY%TYPE;
v_tot_salary EMP.SALARY%TYPE;
BEGIN
OPEN c_dept;
LOOP
FETCH c_dept INTO r_dept;
EXIT WHEN c_dept%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(\'Department:\'|| r_dept.deptno||\'-\'||r_dept.dname);
v_tot_salary:=0;
OPEN c_emp(r_dept.deptno);
LOOP
FETCH c_emp INTO v_ename,v_salary;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(\'Name:\'|| v_ename||\' salary:\'||v_salary);
v_tot_salary:=v_tot_salary+v_salary;
END LOOP;
CLOSE c_emp;
DBMS_OUTPUT.PUT_LINE(\'Toltal Salary for dept:\'|| v_tot_salary);
END LOOP;
CLOSE c_dept;
END;
游标FOR循环
在大多数时候我们在设计
时候都遵循下面步骤: 1、打开游标
2、开始循环
3、从游标中取值
4、检查那
行被返回 5、处理
6、关闭循环
7、关闭游标
可以简单
把这类代码称为游标用于循环但还有种循环和这种类型不相同这就是FOR循环用于FOR循环游标按照正常声明方式声明它优点在于不需要显式打开、关闭、取数据测试数据存在、定义存放数据变量等等游标FOR循环语法如下: FOR record_name IN
(corsor_name[(parameter[,parameter]...)]
| (query_d
inition) LOOP
statements
END LOOP;
下面我们用for循环重写上面
例子: DECALRE
CURSOR c_dept IS SELECT deptno,dname FROM dept ORDER BY deptno;
CURSOR c_emp (p_dept VARACHAR2) IS
SELECT ename,salary
FROM emp
WHERE deptno=p_dept
ORDER BY ename
v_tot_salary EMP.SALARY%TYPE;
BEGIN
FOR r_dept IN c_dept LOOP
DBMS_OUTPUT.PUT_LINE(\'Department:\'|| r_dept.deptno||\'-\'||r_dept.dname);
v_tot_salary:=0;
FOR r_emp IN c_emp(r_dept.deptno) LOOP
DBMS_OUTPUT.PUT_LINE(\'Name:\' || v_ename || \'salary:\' || v_salary);
v_tot_salary:=v_tot_salary+v_salary;
END LOOP;
DBMS_OUTPUT.PUT_LINE(\'Toltal Salary for dept:\'|| v_tot_salary);
END LOOP;
END;
在游标
FOR循环中使用查询
在游标FOR循环中可以定义查询
由于没有显式声明所以游标没有名字记录名通过游标查询来定义 DECALRE
v_tot_salary EMP.SALARY%TYPE;
BEGIN
FOR r_dept IN (SELECT deptno,dname FROM dept ORDER BY deptno) LOOP
DBMS_OUTPUT.PUT_LINE(\'Department:\'|| r_dept.deptno||\'-\'||r_dept.dname);
v_tot_salary:=0;
FOR r_emp IN (SELECT ename,salary
FROM emp
WHERE deptno=p_dept
ORDER BY ename) LOOP
DBMS_OUTPUT.PUT_LINE(\'Name:\'|| v_ename||\' salary:\'||v_salary);
v_tot_salary:=v_tot_salary+v_salary;
END LOOP;
DBMS_OUTPUT.PUT_LINE(\'Toltal Salary for dept:\'|| v_tot_salary);
END LOOP;
END;
游标中
子查询 语法如下:
CURSOR C1 IS SELECT * FROM emp
WHERE deptno NOT IN (SELECT deptno
FROM dept
WHERE dname!=\'ACCOUNTING\');
可以看出和SQL中
子查询没有什么区别 游标中
更新和删除 在PL/SQL中依然可以使用UPDATE
和DELETE语句更新或删除数据行
显式游标只有在需要获得多行数据情况下使用PL/SQL提供了仅仅使用游标就可以执行删除或更新记录思路方法 UPDATE或DELETE语句中
WHERE CURRENT OF子串专门处理要执行UPDATE或DELETE操作表中取出最近数据要使用这个思路方法在声明游标时必须使用FOR UPDATE子串当对话使用FOR UPDATE子串打开个游标时所有返回集中数据行都将处于行级(ROW-LEVEL)独占式锁定其他对象只能查询这些数据行不能进行UPDATE、DELETE或SELECT...FOR UPDATE操作 语法:
FOR UPDATE [OF [schema.]table.column[,[schema.]table.column]..
[nowait]
在多表查询中
使用OF子句来锁定特定表,如果忽略了OF子句那么所有表中选择数据行都将被锁定如果这些数据行已经被其他会话锁定那么正常情况下ORACLE将等待直到数据行解锁 在UPDATE和DELETE中使用WHERE CURRENT OF子串
语法如下: WHERE{CURRENT OF cursor_name|search_condition}
例:
DELCARE
CURSOR c1 IS SELECT empno,salary
FROM emp
WHERE comm IS NULL
FOR UPDATE OF comm;
v_comm NUMBER(10,2);
BEGIN
FOR r1 IN c1 LOOP
IF r1.salary<500 THEN
v_comm:=r1.salary*0.25;
ELSEIF r1.salary<1000 THEN
v_comm:=r1.salary*0.20;
ELSEIF r1.salary<3000 THEN
v_comm:=r1.salary*0.15;
ELSE
v_comm:=r1.salary*0.12;
END IF;
UPDATE emp;
SET comm=v_comm
WHERE CURRENT OF c1l;
END LOOP;
END
最新评论