SQL是用于访问ORACLE数据库
语言
PL/SQL扩展和加强了SQL功能它同时引入了更强
逻辑
PL/SQL支持DML命令和SQL事务控制语句DDL在PL/SQL中不被支持这就意味作在PL/SQL块中不能创建表或其他任何对象较好PL/SQL设计是在PL/SQL块中使用象DBMS_SQL这样内建包或执行EXECUTE IMMEDIATE命令建立动态SQL来执行DDL命令PL/SQL编译器保证对象引用以及用户权限下面我们将讨论各种用于访问ORACLE数据库
DDL和TCL语句查询
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块声明部分声明在执行部分或异常处理部分打开取数据,关闭下表显示了显式游标和隐式游标差别:表1 隐式游标和显式游标
隐式游标 显式游标
PL/SQL维护
当执行查询时自动打开和关闭 在中显式定义、打开、关闭游标有个名字 游标属性前缀是SQL 游标属性
前缀是游标名 属性%ISOPEN总是为FALSE %ISOPEN根据游标
状态确定值 SELECT语句带有INTO子串
只有行数据被处理 可以处理多行数据在中设置循环取出每行数据 =txt> 使用游标
这里要做
个声明我们所说游标通常是指显式游标因此从现在起没有特别指明情况我们所说游标都是指显式游标要在中使用游标必须首先声明游标声明游标
语法:
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=> 这段代码无疑是非常麻烦
如果有多行返回结果可以使用循环并用游标属性为结束循环条件以这种方式提取数据可读性和简洁性都大为提高下面我们使用循环重新写上面:SET SERVERIUTPUT _disibledevent=> 记录变量
定义
个记录变量使用TYPE命令和%ROWTYPE有关%ROWsTYPE更多信息请参阅相关资料记录变量用于从游标中提取数据行
当游标选择很多列时候那么使用记录比为每列声明个变量要方便得多当在表上使用%ROWTYPE并将从游标中取出
值放入记录中时如果要选择表中所有列那么在SELECT子句中使用*比将所有列名列出来要安全得多例:
SET SERVERIUTPUT _disibledevent=>
%ROWTYPE也可以用游标名来定义
这样话就必须要首先声明游标:SET SERVERIUTPUT _disibledevent=> 带参数
游标和存储过程和
相似可以将参数传递给游标并在查询中使用这对于处理在某种条件下打开游标情况非常有用它语法如下: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;
=txt> 游标FOR循环
在大多数时候我们在设计
时候都遵循下面步骤:1、打开游标
2、开始循环
3、从游标中取值
4、检查那
行被返回5、处理
6、关闭循环
7、关闭游标
可以简单
把这类代码称伪暧糜谘贰５褂幸恢盅酚胝庵掷嘈筒幌嗤饩褪荈OR循环用于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
最新评论