--ifdeclare
old number:=&num ;
begin
if old<18then
dbms_output.put_line('未成年');
elsif old>=18and old <40then
dbms_output.put_line('成年人');else
dbms_output.put_line('老年了');endif;
end;
--循环
-----输出1到10
-- whileloopendloop;
declarestep number:=1;
begin
whilestep<=10loop
dbms_output.put_line(step);
step:=step+1;
endloop;
end;
--loopexitwhenendloop;
declarestep number:=1;
begin
loop
dbms_output.put_line(step);
step:=step+1;
exitwhenstep>10;
endloop;
end;
---- forinloopendloopdeclare
begin
forstepin1..10loop
dbms_output.put_line(step);
endloop;
dbms_output.put_line(step); --这样相当于只在loop内定义了该变量;这里打印不了;
end;
---游标
declare
cursor pc isselect * from emp; --这里只能是* ,因为pemp的类型是emp的一个行
pemp emp%rowtype;
begin
open pc;
loop
fetch pc into pemp;
exitwhen pc%notfound;
dbms_output.put_line(pemp.empno);
endloop;
close pc;
end;
--存储过程
--给指定的员工涨100,并打印涨前与涨后的工资
create or replace procedure addSall(eno in number) --记得不能使用no,add做过程名,是它里面的关键字;
is
pemp myemp%rowtype;
begin
select * into pemp from myemp where empno=eno;
update myemp set sal=sal+100where empno=eno;
dbms_output.put_line(pemp.sal||'----'||(pemp.sal+100));end addSall;
begin
addsall(eno=>7902); --也可以直接这样的addsall(7902);
commit;
end;
--- 存储函数
create or replace function queryempincome(eno in number)
return number
as --这里可以使用is 也可以使用as
--月薪和奖金
psal emp.sal%type;
pcomm emp.comm%type;
begin
select sal,comm into psal,pcomm from emp where empno=eno;
--返回年收入
return psal*12+nvl(pcomm,0);
end;
--调用
begin
dbms_output.put_line( queryempincome(7499));
end;
--带参数参数的存储过程
create or replace procedure p_queryincome(eno in myemp.empno%type ,income out number)
is psal myemp.sal%type;
pcomm myemp.comm%type;
begin
select sal,comm into psal,pcomm from myemp where empno=eno;
income:=psal*12+nvl(pcomm,0);
end;
declare
income number;
begin
p_queryincome(7499,income);
dbms_output.put_line(income);
end;
--返回参数为游标
----查出某个部门的所有员工的工资
create or replace procedure proc_cur (dno in number, mycur out sys_refcursor) ---这里只能使用sys_recursor,不能用cursor
---函数过程不能对游标进行操作只能返回,用了一直报错----不确定
is
begin
open mycur forselect * from myemp where myemp.deptno=dno;
end;
declare
mycur sys_refcursor; --系统定义的cursor不需要再开启了
emplist myemp%rowtype;
begin
proc_cur(20,mycur);
loopexitwhen mycur%notfound;
fetch mycur into emplist;
dbms_output.put_line(emplist.empno||' '||emplist.ename||' '||emplist.sal);endloop;
close mycur;
end;
--触发器
--插入员工后面打印一句话"一个员工插入成功"
create or replace trigger testTrigger
after insert on myemp --这里不能有分号
declare
begin
dbms_output.put_line('一个员工插入成功');end testTrigger;
insert into myemp (empno,ename)values(95128,'huaan');
--不能星期四 插入员工
create or replace trigger validInsertEmp
before insert on myemp
declare
weekend varchar2(20);
begin
select to_char(sysdate,'day') into weekend from dual;if weekend in('thursday') then
raise_application_error(-20003,'不能在非法的时间内查入员工');----raise_application_error这个是连在一起的endif;
end validInsertEmp; ---这个不知道为什么会生效不了
insert into myemp (empno,ename) values(9530,'huaan');
--判断员工涨工资之后的工资应该高于涨工资之后 --行级触发器
create or replace trigger tri_cheSal
before update of sal on myemp
foreach row
begin
if :old.sal>=:new.sal then
raise_application_error(-2005,'员工涨工资之后的工资应该高于涨工资之后');endif;
end tri_cheSal;
---触发器模拟自增
create table myuser (id number(6) not null,name varchar2(8) not null,
constraint pk_user_id primary key(id)
)
create or replace trigger user_trigger
before insert on myuser
foreach row
begin
select myuser_seq.nextval into :new.id from sys.dual;
end;
create or replace trigger tri_ttuser
before insert on tt_user
foreach row
begin
select sys_guid() into :new.id from dual; ----这里 sys_guid()获得了uuid,他的位数为32位; end;
--自定义类型
declare
type mytype is record(eno myemp.empno%type,ename myemp.ename%type);
arr mytype;
begin
select empno,ename into arr from myemp where empno=7369;
dbms_output.put_line(arr.eno||' '||arr.ename);end;
-自定义数组
declare
type myarray is table ofinteger index by binary_integer;
varr myarray;
begin
for i in ‐5..5loop
‐‐括号代表下标
varr(i) :=i;
endloop;
for i in ‐5..5loop
dbms_output.put_line(varr(i));
endloop;
end;