1、基本数据类型和语法类型
begin
dbms_output.put_line("hello world");打印
end;
2、声明变量和数据类型
declare
V_age number; V_age:=20
V_name varchar2(25); V_name:='张三';
V_dute date; V_dute:=sysdate;
3、数据类型2
declare
V_emp_ename emp.ename%type;
变量和emp表中ename字段类型一致
V_emp emp%rowtype
变量和emp表中类型相符
4、符合类型
declare
type t_mytype is record(
empno emp.empno%type,dname dept.dname%type
)
v_join_val t_mytype;
定义了复合类型,该复合类型有两个属性,分别对应两张表的两个字段
5、执行sql
declare
V_deptno dept.deptno%tyoe:=60;
V_dname dept.dname%type;
V_loc dept.loc%type:='济南';
begin 增删改一样
insert into dept values(v_dptno,v_dname,v_loc);
commit;
end;
declare 查询略有不同
v_empno emp.empno%type:=7369;
v_emp emp%rowtype;存数据,对象类型,整行变量
begin 一条数据
select * into V_emp from emp where empno=V_empno;
end;
6、动态SQL
declare 预编译的
v_depno dept.deptno%type:=70;
v_dname dept.dname%type:='测试部';
v_loc dept.loc%type:='济南';
v_sql varchar2(100):='insert into dept values(:v_dept:Vv_dname,:v_loc)'; :后是占位符
begin 增删改一样
execute immediate v_sql using V_deptno,V_dname,V_loc;
commit;
end;
declare 查询
v_empno emp.empno%type:=7369;
v_emp emp%rowtype;
v_sql varchar2(100):='select * from emp where empno=:V_empno'
begin
execute immediate v_sql into v_emp using v_empno;
dbms_output.put_;ine(v_emp.ename);
end;
7、逻辑语句
分支,if,case
declare
v_empno emp.empno%type:=7369;
v_sal emp.sal%type;
v_sql varchar2(100):='select sal from emp where empno=:v_empno';
begin
execute immediate v_sql into v_sal using v_empno;
if v_sal>30000 then
dbms_output.put_line('高工资');
elsif/else
dbms_output.put_line('低工资');
end if;
end;
case
when v_sal>3000 then
when v_sal c=3000 and v_sal>2000th
end case;
end;
循环 while,do while,增强for循环
declare
v_number number:=1;
begin
while v_number <=10 loop
dbms_output.put_line(v_number);
v_number:=v_number+1;
end loop;
end;
增强for循环
begin 不需要声明,循环变量
for v_number每次循环的值 in 1..10遍历范围 loop
dbms_output.put_line(v_number);
end loop;
end;
8、异常
declare
c_val number;
begin
v_val=10/0;
exception 代表所有异常
when others then
dbms_output.put_line("有异常!!!");
end;
9、游标:指向sql语句的指针
隐式游标
declare
v_emp emp%rowtype;
begin
select * into v_emp from emp where empno=7369;
dbms_output.put_line(sql);
end;
属性:rowcount执行结果影响几条数据,found/notfound执行的结果,是否存在
if sql%found then
dbms_...('存在');
else
dbms_...('不存在');
end if
显式游标
declare
cursor c is select * from emp;
v_emp emp%rowtype;
begin
open c;打开游标
fetch c into v_emp;移动,并返回数据
dbms_...(v_emp.empno);
close c;
end;
与while一起合用
declare
cursor c is select * from emp;
v_emp emp%rowtype;
begin
open c;
fetch c into v_emp;
while c%found loop
dbms_...(v_emp.empno);
fetch c into v_emp;
end loop;
close c;
end;
与do while通用
declare
cursor c is select * from emp;
v_emp emp%rowtype;
begin
open c;
loop
fetch c into v_emp;
exit when c%notfound;
dbms_...(v_emp.empno);
end loop;
close c;
end;
与for同用
declare
cursor c is select * from emp;
begin
for v_emp in c loop;
dbms_...(v_emp.empno);
end loop;
end;
可更新的游标
declare
cuisor c is select * from emp for update;
begin
for v_emp in c loop
if v_emp.sal<2000 then
update emp set sal-sal+100 where current of c;
else
update emp set sal=sal+50 where current of c;
end if;
end loop;
commit;
end;
带参数的游标
declare
cursor c (v_deptno emp.deptno%type) is select * from emp where deptno=v_deptno;
begin
for v_emp in(10) loop
dbms_...(v_emp.empno||'....'||v_emp.deptno);
end loop;
end;
10、触发器
create or replace trigger t名 after insert on emp;
begin
dbms_...('向emp插入了数据');
end;
create or replace trigger t名 after update on emp for each row;每条数据改变都会执行触发器
伪属性::OLD触发的事件执行之前的数据状态,也就是执行条件
:NEW修改后的状态
触发器处理外键级联操作
create or replace trigger t after update on dept for each row行触发器
begin
update emp set deptno=:new.deptno where deptno=:OLD.deptno;
end;
11、存储过程
create or replace procedure p is cursor c is select * from emp;
begin
for v_emp in c loop
dbms_...(v_emp.empno);
end loop;
end;
带参的存储过程
create or replace procedure p (a number,b number) is
begin
if a<b then
dbms_...(b);
else
dbms_...(a);
end if;
end;
输入参数
declare
a number:=10;
b number:=20;
begin
p(a,b);
end;
输出参数
create or replace procedure p1(a number,c number) is
begin
if a<b then
c:=b;
else
c:=a;
end if;
end;
12、函数
create or replace function f(v_a number,v_b number) return number is
begin
if v_a>v_b then
return v_a;
else
return v_b;
end if;
end;
select f(20,30) from dual;