命名块:存储过程,函数,触发器,包等
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;