oracle使用匿名块实现,pl/sql分匿名块和命名块

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

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;