record 相当于类
declare
type type_record_dept is record
(
deptno dept.deptno%type,
dname dept.dname%type,
loc dept.loc%typ
);
v_test type_record_dept;
begin
v_test.deptno := 50;
v_test.dname := '11';
v_test.loc := 'cd';
dbms_output.put_line(v_test.deptno || v_test.dname || v_test.loc);
end;

4-PL_SQL语句的不同
----执行SQL语句必须且只返回一条记录.有且只有
select ename,sal into v_name,v_sal from emp where empno = 7369;--必须有into,赋值
dbms_output.put_line....

---excute immediate 关键字

begin
execute immediate 'create table T(te/
st varchar2(20) default ''aa'')';
end;
执行DDL语句的时候要用execute immediate'';单引号内加语句

----IF关键字
declare
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno = 7369;
if (v_sal < 1200) then
dbms_output.put_line('low');
elsif(v_sal < 2000) then---eslif不是else if
dbms_output.put_line('high');
else
dbms_output.put_line('high very');
end if;
end;

----循环
loop

declare
i binary_integer := 1;
begin
loop
dbms_output.put_line(i);
i := i ++;
exit when(i > = 10);
end loop;
end;

for

begin
for k in 1..10 loop
dbms_output.put_line(k);
end loop;
for k in reverse 1..10 loop--逆序
dbms_output.put_line(k);
end loop;
end;

5---错误处理
declare
v_temp number(4);
begin
select empno into v_temp from emp where deptno = 10;
exception
when too_many_rows then
dbms_output.putline('aaa');
when others then
dbms_output.putline('error');
end;

6---游标cursor,重点 相当于迭代器
declare
cursor c is
select * from emp;
v_emp c%rowtype;--rowtype相当与C这是一表的类型
begin
open c;
fetch c into v_emp;
dbms_output.put_line(v_emp.ename);
close c;
end;
拿出第一条记录

declare
cursor c is
select * from emp;
v_emp c%rowtype;--rowtype相当与C这是一表的类型
begin
open c;
loop
fetch c into v_emp;
exit when(c%notfound)
dbms_output.put_line(v_emp.ename);
endloop;
close c;
end;
循环拿出每一条记录

游标的4个属性
c%isopen是不是打开了;
c%notfound最近的一次fetch如果没有找到就返回true;
c%found 找到就返回true;
c%rouwcount 当前已经fetch 到了多少条记录

for循环遍历,有些不一样.不用open fetch什么的
declare
cursor c is
select * from emp;
begin
for v_emp in c loop
dbms_output.put_line(v_emp.ename);
end loop;
end;

待参数的游标
declare
cursor c(v_deptno emp.deptno%type, v_job emp.job%type)--和形参差不多
is
select ename, sal from emp where deptno = v_deptno and job = v_job;
begin
for v_temp in c(30, 'CLERK') loop
dbms_output.put_line(v_temp.ename);
end loop;
end;

可更新的游标
declare
cursor c
is
select * from emp for update;
begin
for v_temp in c loop
if(v_temp.sal < 2000) then
update emp set sal = sal * 2 where current of c;---where current of c就是更新当前这个游标
elsif (v_temp.sal = 5000) then
delete from emp where current of c;
end if;
end loop;
--commit;
end;

创建存储过程
create or replace procedure p
is
begin
dbms_ouput.put_line('222');
end;

执行后会提示过程已经完成,但是他没有执行
执行方法
1)exec p;
2)
begin
p;
end;
这样的话可以方便的多次执行

带参数的存储过程
create or replace procedure p
(v_a in number, v_b number, v_ret out number, v_temp in out number)-- 传入传出,默认是in
is
---变量声明 无
begin
if(v_a > v_b) then
v_ret := v_a;
else
v_ret := v_b;
end if;
v_temp := v_temp + 1;
end;

执行
declare
v_a number := 3;
v_b number := 4;
v_ret number;---因为他是out number
v_temp number := 5;
begin
p(v_a, v_b, v_ret, v_temp);
dbms_output.put_line(v_ret);
dbms_output.put_line(v_temp);
end;

show error;

函数function
create or replace function sal_tex
(v_sal number)--形参
return number
is
begin
if(v_sal < 2000) then
return 0.10;
else
return 0.20;
end if;
end;

这个不是很重要

触发器
create table emp2_log
(
uname varchar2(20),
ation varchar2(10),
atime date
);