游标

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
no_result Exception;

begin
delete 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;

 

动态sql 存储过程中不可以直接用ddl语言
create or replace procedure dropt1 is
begin
execute immediate 'drop table t1 purge';
dbms_output.put_line('删除成功');
exception
when others then
dbms_output.put_line('删除失败');
end dropt1;


begin
dropt1;
end;

函数 需要返回值,可以在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;
posted on 2020-01-10 22:09  我有我的信仰  阅读(134)  评论(0)    收藏  举报