PLSQL语法基础
变量
-
普通变量声明方式示例
-- CREATED ON 2019/7/17 BY ZHOU DECLARE v_name varchar(20); v_sal number; v_addr varchar(20); BEGIN v_name:='张三'; v_sal:=10000; select '长沙' into v_addr from dual; dbms_output.put_line(v_name||',在'||v_addr||'薪资为:'|| v_sal); END;
- 引用型变量声明方式
-- Created on 2019/7/17 by ZHOU --使用引用变量 declare v_name emp.ename%TYPE; v_sal emp.sal%TYPE; begin -- Test statements here select emp.ename into v_name from emp where emp.empno=7499; select emp.sal into v_sal from emp where emp.empno=7499; dbms_output.put_line(v_name||'-->'|| v_sal); end;
分支语句:IF…ELSE
语法:
if 条件表达式 then
语句;
elsif 条件表达式 then
语句;
else
语句;
end if
示例:
-- Created on 2019/7/17 by ZHOU
declare
-- Local variables here
record_num integer;
begin
-- Test statements here
select count(*) into record_num from emp;
if record_num<10 then
dbms_output.put_line('行数不满10条,number='||record_num);
elsif record_num>20 then
dbms_output.put_line('行数超过20条,number='||record_num);
else
dbms_output.put_line('行数在10~20条内,number='||record_num);
end if;
end;
循环语句:LOOP
语法:
loop
exit when 退出条件;
语句;
end loop;
示例:
-- Created on 2019/7/17 by ZHOU
declare
-- Local variables here
num number:=1;
begin
loop
exit when num>10;
dbms_output.put_line(num);
num:=num+1;
end loop;
end;
游标
游标是sql结果集合中的指针,可以配合循环语句获取其数据,游标有带参游标和无参游标
语法:
声明游标
cursor 游标名 is sql语句;
打开游标
open 游标名
关闭游标
close 游标名
获取数据
fetch 游标名 into 变量
- 无参游标示例:
-- Created on 2019/7/17 by ZHOU
declare
-- Local variables here
cursor c_emp is select emp.ename,emp.sal from emp;
vname emp.ename%TYPE;
vsal emp.sal%TYPE;
begin
-- Test statements here
open c_emp;
loop
fetch c_emp into vname,vsal;
exit when c_emp%notfound;
dbms_output.put_line(vname||'---'||vsal);
end loop;
close c_emp;
end;
- 带参游标示例:
-- 指定部门的员工姓名和薪资
-- Created on 2019/7/17 by ZHOU
declare
--定义游标
cursor c_emp(v_dept emp.deptno%TYPE) is select emp.ename,emp.sal from emp where emp.deptno=v_dept;
vname emp.ename%TYPE;
vsal emp.sal%TYPE;
begin
-- Test statements here
open c_emp(10);
loop
fetch c_emp into vname,vsal;
exit when c_emp%notfound;
dbms_output.put_line(vname||'--'||vsal);
end loop;
end;
存储过程
语法:
create or replace procedure 存储过程名(参数1 in/out 类型,参数2 in/out 类型,…)
begin
执行代码块;
end 存储过程名
示例:查询指定员工编号的薪资
--存储文件:
create or replace procedure simple_procedure(v_empno in emp.empno%TYPE, v_sal out emp.sal%TYPE) is
begin
select emp.sal into v_sal from emp where emp.empno=v_empno;
end simple_procedure;
调用文件
-- Created on 2019/7/17 by ZHOU
declare
v_sal emp.sal%TYPE;
begin
simple_procedure('7499',v_sal);
dbms_output.put_line(v_sal);
end;