PL(SQL)块
PL/SQL有三种类型的块，即匿名块(anonymous block)、过程(procedure)、 和函数(function)。过程和函数又称为字程序(subprogram)。
匿名块可以包含三个部分：申明部分、执行部分(必须的)和异常处理部分
--执行代码块：
start <PL/SQL 代码块文件名>
--在屏幕上显示数据行/*Display the result on the screen*/
set serveroutput on
DBMS_OUTPUT.PUT_LINE ('Display the result on the screen');
set serveroutput off
--异常处理(不包含有名异常，如VALUE_ERROR,TOO_MANY_ROWS等)
exception
when OTHERS then
dbms_output.putline('错误号：' || SQLERRM);
dbms_output.putline('错误消息：' || SQLERRM);
end;
循环
--简单循环
loop
statements
exit when <condition>
end loop;
--while 循环
while <condition> loop
statements
end loop;
--for 循环(reverse指定为降序循环)
for <loop_variable> in [reverse] <lower_bound>..<upper_bound> loop
statements
end loop;
匿名块anonymous block
--隐式游标(implicit cursor)匿名块
[declare
<variables/constant>]
begin
<execution_statement>
[exception
<exception_statement>]
end;
/
--显式游标(explicit curror)匿名块
--定义游标的语法(游标前缀cv_)
cursor <cursor_name> [<parameter_list>]
[return <return_type>] is
<query>
[for update [of (<column_list>)] [nowait] ];
--使用简单循环处理显式游标
/*1.声明变量来存储从SELECT语句中返回的值*/
DECLARE
v_AvgSal AgentsHR.BaseSalary%TYPE;
v_TeamNo AgentHR.Team%TYPE;
v_AvgSalChar varchar2(15);
/*2.定义游标*/
cursor cv_TeamCursor is
select team,avg(basesalary) from AgentsHR
group by team order by team;
BEGIN
/*3.打开游标*/
open cv_TeamCursor;
--显示列标题
DBMS_OUTPUT.PUT_LINE('Team' || 'Average Salary');
/*4.循环并从游标中提取行*/
loop
fetch cv_TeamCursor into TeamNo,v_AvgSal;
exit when cv_TeamCursor%notfound;
v_AvgSalChar := to_char(v_AvgSal, '$99,999.99');
DBMS_OUTPUT.PUT_LINE(v_TeamNo || ' ' || v_AvgSalChar);
end loop;
/*5.关闭游标，并编写异常处理程序*/
close cv_TeamCursor;
EXCEPTION
when others then
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/
--使用FOR循环处理显式游标，简化了PL/SQL块，不必打开关闭游标，不必
--发布FETCH指令，它将自动进行这三项指令。
/*游标FOR循环语法：(记录record是一个组合结构的变量，用法：变量.列名)
for <record_name> in <cursor_name> loop
<statement1>;
<statement2>;...
end loop;
*/
clear screen
set serveroutput on
DECLARE
cursor cv_Sellers is
select a.firstname,a.lastname,c.askingprice
from Customers a
join Properties b on a.customerid=b.ownerid
join Listings c using(propertyid)
join CustAgentList d using(listingid)
where upper(d.contactreason)='SELL' and a.city = 'Loleta'
order by lastname,firstname;
BEGIN
DBMS_OUTPUT.PUT_LINE(rpad('Seller'''s Name',24,' ') || 'Asking Price');
--不用声明变量v_Counter，会自动申请
for v_Counter in cv_Sellers loop
DBMS_OUTPUT.PUT_LINE(rpad(v_Counter.firstname || ' ' ||
v_Counter.lastname,30,' ') || v_Counter.askingprice);
end loop;
EXCEPTION
when NO_DATA_FOUND then
DBMS_OUTPUT.PUT_LINE('No sellers in that city');
when OTHERS then
DBMS_OUTPUT.PUT_LINE('Error of unknown type occurred');
END;
/
函数function
--函数创建语法
create [or replace] function <function_name>
[(parameter_name [in | out | in out] <datatype> [,...])]
return <datatype> {is | as}
begin
<function_body>
[exception
<exception_handling_statements>;]
end [<function_name>];
/
--显示函数的定义
--1.在USER_OBJECTS中显示函数名
column object_name format a20
column object_type format a12
select object_name,object_type from user_objects
where object_type in ('FUNCTION','PROCEDURE');
--2.显示函数原代码信息
set pagesize 40
clear screen
select text from user_source
where name='AGE'
order by line;
--删除函数
drop function <function_name>;
过程procecdure
--过程创建语法
create [or replace] procecdure <procecdure_name>
[(parameter_name [in | out | in out] <datatype> [,...])]
{is | as}
[<local_variable_declarations>] --局部变量声明，包含游标和标量变量
begin
<procecdure_body>
[exception
<exception_handling_statements>;]
end [<procecdure_name>];
/
执行块，可以直接运行
declare
varQQ VARCHAR2(20);
begin
varQQ := '112';
update 用户 set QQ=varQQ where 账号='一级用户';
update 用户 set QQ=varQQ where 账号='二级用户';
end;
/
with创建表
with不能添加括号，否则报不支持的错误
create table A_TABLE as
with AA as
(select a,b from AB)
select a,b from AA;
