pl/sql分匿名块和命名块

2019独角兽企业重金招聘Python工程师标准>>>

命名块:存储过程，函数，触发器，包等



pl/sql语句块分3部分：
(1)声明部分
(2)可执行部分
(3)异常处理部分
其中可执行部分是语句块中唯一要求必须存在的部分，声明部分和异常处理部分是可选的。


1.匿名快语法:
declare
   declatation statements
begin
    executable statements
exception
    exception-handing statements
end
2.生成变量
语法：
变量名 [constant] datatype [not null] [:=value|default expr]
声明变量方法1：

declare
v_first_name varchar2(50);
c_count   constant number :=0;  ---常量
v_hiredate  date;
v_valid   BOOLEAN NOT NULL DEFAULT TRUE;


声明变量方法2：
declare
v_ename emp.ename%type;
v_sal emp.sal%type;
每一个分句后用分号

conn scott/tiger
查询 某个员工的姓名 薪水 税后工资
set serveroutput on;
declare
v_ename varchar2(20);
v_sal   emp.sal%type;
c_tax_rate constant number(3,2):=0.06;
v_tax_sal number(6,2);
begin
select ename,sal into v_ename,v_sal from emp where empno=&no;
v_tax_sal:=c_tax_rate*v_sal;
dbms_output.put_line('v_ename: ' || v_ename);
dbms_output.put_line('v_sal: ' || v_sal);
dbms_output.put_line('v_tax_sal :'||v_tax_sal);
end;

3.标识符的命名规则

(1)定义变量，建议用v_,如v_ename
(2)定义常量，建议用c_,如c_tax_rate
(3)定义游标，建议用_cursor作为后缀,如emp_cursor
(4)定义表类型，建议用_table_type作为后缀，如sal_table_type;
(5)定义表变量，建议用_table作为后缀，例如sal_table;


4.PL/SQL编译过程步骤

编译过程包括语法检查，绑定以及为代码生成。语法检查涉及检查PL/SQL代码中的编译错误。在纠正语法错误之后，会给每个变量分配内存地址，以保存ORACLE数据，这个过程成为绑定。接下来，会产生PL/SQL语句块的伪代码，伪代码是PL/SQL引擎的指令列表，对于命名语句块，伪代码会存储在数据库中，并在程序下一次执行时使用。

5.替代变量
在匿名PL/SQL块中接受输入参数使用&或者&&作为替代变量

6.初始化变量用select into语法
求emp表的平均工资
set serveroutput on;
declare
v_avg_sal emp.sal%type;
begin
select avg(sal) into v_avg_sal from emp;
dbms_output.put_line('v_avg_sal:'||v_avg_sal);
end;
#############################
7.if语法
if condition then
    statements;

[elsif condition then
    statements;]
[else
    statements;]

end if;

如果工资小于2000，把雇员的薪水加50
set serveroutput on;
DECLARE
v_sal emp.sal%TYPE;
begin
select sal into v_sal from emp where upper(ename)=upper('&ename');
if v_sal<2000 then
update emp set sal=sal+50 where upper(ename)=upper('&ename');
end if;
end;


//如果job=PRESIDENT，sal=sal+200
job=MANAGER ,sal=sal+1000
other ,sal=sal+100
&empno

declare
v_job emp.job%type;
v_empno emp.empno%TYPE;
begin
v_empno:=&empno;
select job into v_job from emp where empno=v_empno;
if v_job='PRESIDENT' then
update emp set sal=sal+200 where empno=v_empno;
elsif v_job='MANAGER'then
update emp set sal=sal+1000 where empno=v_empno;
else
update emp set sal=sal+100 where empno=v_empno;
end if;
end;
或者：
declare
v_job emp.job%type;
v_empno emp.empno%TYPE;
begin
select job,empno into v_job, v_empno from emp where empno=&empno;
if v_job='PRESIDENT' then
update emp set sal=sal+200 where empno=v_empno;
elsif v_job='MANAGER'then
update emp set sal=sal+1000 where empno=v_empno;
else
update emp set sal=sal+100 where empno=v_empno;
end if;
end;
############################
8.循环
#loop循环:
create table tmp01(id int);
declare
i int :=1;
begin
loop
insert into tmp01 values (i);
commit;
exit when i >100;
i := i+1;
end loop;
end;

#while循环
drop table tmp01 purge;
create table tmp01(id int);
declare
i int:=1;
begin
while i<100
 loop
insert into tmp01 values(i);
i:=i+1;
end loop;
commit;
end;

#for循环
drop table tmp01 purge;
create table tmp01(id int);
begin
for i in 1..100
 loop
insert into tmp01 values(i);
end loop;
commit;
end;


#############case语句
//用替代变量输入部门号，使用case语句判断条件更新雇员工资
部门号为10,雇员加薪10%
部门号为20,雇员加薪8%
部门号为30,雇员加薪15%
如果输入其他数字，则显示“该部门不存在”

DECLARE
v_deptno EMP.DEPTNO%TYPE:=&deptno;
begin
case v_deptno
when 10 then
update emp set sal=sal*1.1 where deptno=v_deptno;
when 20 then
update emp set sal=sal*1.08 where deptno=v_deptno;
when 30 then
update emp set sal=sal*1.15 where deptno=v_deptno;
else
dbms_output.put_line(v_deptno||' department is not exists!');
end case;
end;

9.SQL游标
当执行select,insert,update,delete时，oracle会为SQL语句分配相应的上下文区(context area).oracle使用上下区解析并执行相应的SQL语句，而游标就是指向上下文区的指针。
游标包括隐式游标和显示游标。其中隐式游标也称为SQL游标，专门用于处理select into,insert,update,delete语句。显示游标用于处理多行select语句。


SQL游标属性：sql%found,sql%notfound,sql%rowcount,sql%isopen等

(1)sql%isopen:用于确定SQL游标是否打开。
(2)sql%found:用于确定SQL语句是否执行成功。
(3)sql%notfound:用于确定SQL语句是否执行成功。
(4)sql%rowcount：sql语句所做用的总计行数


set serveroutput on;
declare
v_deptno emp.deptno%type := &no;

begin
update emp set sal = sal*1.05 where deptno=v_deptno;
if sql%notfound then
dbms_output.put_line('deptno is not exist.');
else
dbms_output.put_line('sql execute successful.');
end if;
dbms_output.put_line('have '|| sql%rowcount || ' row  change.');
end;

---习题
用替代变量输入客户名(不区分大小写)和所在城市，并修改客户所在城市，如果客户不在，则显示“该客户不在”

create table customer(customer_id number not null,customer_name varchar2(50),city_name varchar2(50),constraint pk_customer primary key(customer_id));

insert into CUSTOMER VALUES(1,'guoyf','luoy'),(2,'wangxq','daok'),(3,'songsl','yic');
declare
v_cusname customer.customer_name%type:='&name';
v_city varchar2(50):='&city';
begin
update customer set city_name=v_city where upper(customer_name)=upper(v_cusname);
if sql%notfound then
dbms_output.put_line(v_cusname||' is not exist');
end if;
commit; ...提交以后游标关闭
end;

转载于:https://my.oschina.net/u/1458120/blog/225681