游标
declare
cursor vrows is select * from emp;
vrow emp%rowtype;
begin
open vrows;
loop
fetch vrows into vrow;
exit when vrows%notfound;
dbms_output.put_line('姓名'||vrow.ename||' '||'工资'||vrow.sal);
end loop;
close vrows;
end;
过程
create or replace procedure proc_one (vnum in number,vno in number)
is
vsal number;
begin
select sal into vsal from emp where empno=vno;
dbms_output.put_line(vsal);
update emp set sal=sal+vnum where empno=vno;
dbms_output.put_line('更新后的工资是'||(vnum+vsal));
commit;
end;
select * from emp
call proc_one(500,7369)
函数
create or replace function fun_one (vno in number)return number
is
vsal number;
begin
select sal*12+nvl(comm,0) into vsal from emp where empno=vno;
return vsal;
end;
declare
vsal number;
begin
vsal:=fun_one(7369);
dbms_output.put_line(vsal);
end;
select t.*,fun_one(7369) totalsal from emp t where empno=7369
触发器
create or replace trigger tri_one
before
insert
on emp
declare
vday varchar(10);
begin
select trim(to_char(sysdate,'day'))into vday from dual;
if vday='星期五' then
dbms_output.put_line('今天星期五要休息了');
raise_application_error(-20001,'haha');
end if;
end;
insert into emp (empno,ename)values(9527,'华安');
create or replace trigger tri_two
before
update
on emp
for each row
declare
begin
if :old.sal>:new.sal then
raise_application_error(-20001,'低工资警告');
dbms_output.put_line('警告一次');
end if;
end;
update emp set sal=sal-10 ;
自动增长id
create table person(id number(10) primary key,name varchar(30));
create or replace trigger tri_three
before
insert
on person
for each row
declare
begin
select seq_test.nextval into :new.id from dual;
end;
insert into person values(null,'lisi');
create sequence USER_ID_SEQ
minvalue 1
maxvalue 99999999
start with 1
increment by 1
nocache
order;
炼数成金补充内容
CREATE OR REPLACE PROCEDURE DelEmp(v_empno IN emp.empno%TYPE) AS
No_result EXCEPTION;
BEGIN
DELETE FROM emp WHERE empno=v_empno;
IF SQL%NOTFOUND THEN
RAISE no_result;
END IF;
DBMS_OUTPUT.PUT_LINE('编码为'||v_empno||'的员工已被除名!');
EXCEPTION
WHEN no_result THEN
DBMS_OUTPUT.PUT_LINE('你需要的数据不存在!');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('发生其它错误!');
END DelEmp;
|
上面是老师例子下面是我做的 create or replace procedure delemp(v_empno in emp.empno%type) as
动态sql 存储过程中不可以直接用ddl语言
|
函数 需要返回值,可以在sql中被调用。sum就是一个函数 |
create or replace function mysum(a in number,b in number)
return number is
v_sum number;
begin
v_sum:=a+b;
return v_sum;
end mysum;
|