trigger、function、procedure、package示例
set serverout on;
declare cursor cu_emp is select empno,ename,sal from emp;
e_no number;
e_name varchar2(10);
e_sal number;
begin
open cu_emp;
fetch cu_emp into e_no,e_name,e_sal;
while cu_emp%found loop
dbms_output.put_line('编号：'||e_no||'，姓名：'||e_name||'，基本薪资：'||e_sal);
fetch cu_emp into e_no,e_name,e_sal;
end loop;
close cu_emp;
end;
set serverout on;
declare cursor cu_emp is select empno,ename,sal from emp;
e_no emp.empno%type;
e_name emp.ename%type;
e_sal emp.sal%type;
begin
open cu_emp;
fetch cu_emp into e_no,e_name,e_sal;
while cu_emp%found loop
dbms_output.put_line('编号：'||e_no||'，姓名：'||e_name||'，基本薪资：'||e_sal);
fetch cu_emp into e_no,e_name,e_sal;
end loop;
close cu_emp;
end;
set serverout on;
declare cursor cu_emp is select * from emp;
e emp%rowtype;
begin
open cu_emp;
fetch cu_emp into e;
while cu_emp%found loop
dbms_output.put_line('编号：'||e.empno||'，姓名：'||e.ename||'，基本薪资：'||e.sal);
fetch cu_emp into e;
end loop;
close cu_emp;
end;
set serverout on;
declare cursor cu_emp is select * from emp where sal>2000 and sal<3000;
e emp%rowtype;
begin
open cu_emp;
fetch cu_emp into e;
while cu_emp%found loop
dbms_output.put_line('编号：'||e.empno||'，姓名：'||e.ename||'，基本薪资：'||e.sal);
fetch cu_emp into e;
end loop;
close cu_emp;
end;
begin
if sql%isopen then
dbms_output.put_line('sql游标已打开');
else
dbms_output.put_line('sql游标未打开');
end if;
end;
declare e_count number;
begin
select count(*) into e_count from emp;
dbms_output.put_line('游标捕获的记录数：'||sql%rowcount);
end;
declare e_count number;
begin
select count(*) into e_count from emp;
dbms_output.put_line('游标捕获的记录数：'||sql%rowcount);
end;
begin
update emp set ename='sb3' where empno=111;
if sql%rowcount=1 then
dbms_output.put_line('已更新');
else
dbms_output.put_line('未更新');
end if;
end;
begin
update emp set ename='sb3' where empno=111;
if sql%found then
dbms_output.put_line('已更新');
else
dbms_output.put_line('未更新');
end if;
end;
declare type emptype is ref cursor return emp%rowtype;
cu_emp emptype;
e_count number;
e emp%rowtype;
begin
select count(*) into e_count from emp where job='PRESIDENT1';
if e_count=0 then
open cu_emp for select * from emp;
else
open cu_emp for select * from emp where job='PRESIDENT';
end if;
fetch cu_emp into e;
while cu_emp%found loop
dbms_output.put_line('编号：'||e.empno||'，姓名：'||e.ename||'，基本薪资：'||e.sal);
fetch cu_emp into e;
end loop;
close cu_emp;
end;
declare type customType is ref cursor;
e_count number;
e emp%rowtype;
s salgrade%rowType;
cType customType;
begin
select count(*) into e_count from emp where job='PRESIDENT1';
if e_count=0 then
open cType for select * from salgrade;
fetch cType into s;
while cType%found loop
dbms_output.put_line('等级：'||s.grade||'，最低薪资：'||s.losal||'，最高薪资：'||s.hisal);
fetch cType into s;
end loop;
close cType;
else
open cType for select * from emp where job='PRESIDENT';
fetch cType into e;
while cType%found loop
dbms_output.put_line('编号：'||e.empno||'，姓名：'||e.ename||'，基本薪资：'||e.sal);
fetch cType into e;
end loop;
close cType;
end if;
end;
select t.*, t.rowid from T_BOOK t
insert into t_book values(6,'xx7',2);
delete from t_book where id=6;
update t_book set bookname='xx4' where id=4;
create trigger tr_book
before insert
on t_book
begin
if user!='cc' then
raise_application_error(-20001,'权限不足');
end if;
end;
create trigger tr_book2
before update or delete
on t_book
begin
if user!='CC' then
raise_application_error(-20001,'权限不足');
end if;
end;
create trigger tr_book_log
after insert or update or delete
on t_book
begin
if updating then
insert into t_book_log values(user,'update',sysdate);
else if inserting then
insert into t_book_log values(user,'insert',sysdate);
else if deleting then
insert into t_book_log values(user,'delete',sysdate);
end if;
end if;
end if;
end;
create trigger tr_book_add
after insert
on t_book
for each row
begin
update t_booktype set num=num+1 where id=:new.typeId;
end;
create trigger tr_book_delete
after delete
on t_book
for each row
begin
update t_booktype set num=num-1 where id=:old.typeId;
end;
create function getBookCount return number as
begin
declare book_count number;
begin
select count(*) into book_count from t_book;
return book_count;
end;
end getBookCount;
set serveroutput on;
begin
dbms_output.put_line('表t_book有'|| getBookCount() ||'条数据');
end;
create function getTableCount(table_name varchar2) return number as
begin
declare recore_count number;
query_sql varchar2(300);
begin
query_sql:='select count(*) from ' || table_name;
execute immediate query_sql into recore_count;
return recore_count;
end;
end getTableCount;
begin
dbms_output.put_line('表有'|| getTableCount('t_bookType') ||'条数据');
end;
create procedure addBook(bookName in varchar2,typeId in number) as
begin
declare maxId number;
begin
select max(id) into maxId from t_book;
insert into t_book values(maxId+1,bookName,typeId);
commit;
end;
end addBook;
execute addBook('java好东西',1);
create procedure addBook2(bN in varchar2,typeId in number) as
begin
declare maxId number;
n number;
begin
select count(*) into n from t_book where bookName=bN;
if(n>0) then
return;
end if;
select max(id) into maxId from t_book;
insert into t_book values(maxId+1,bN,typeId);
commit;
end;
end addBook2;
execute addBook2('java好东西33',1);
create procedure addBook3(bN in varchar2,typeId in number,n1 out number,n2 out number) as
begin
declare maxId number;
n number;
begin
select count(*) into n1 from t_book;
select count(*) into n from t_book where bookName=bN;
if(n>0) then
return;
end if;
select max(id) into maxId from t_book;
insert into t_book values(maxId+1,bN,typeId);
select count(*) into n2 from t_book;
commit;
end;
end addBook3;
declare n1 number;
n2 number;
begin
addBook3('喝喝33223',2,n1,n2);
dbms_output.put_line('n1='||n1);
dbms_output.put_line('n2='||n2);
end;
create package pkg_book as
function getbookcount return number;
function getTableCount(table_name varchar2) return number;
procedure addBook(bookName in varchar2,typeId in number);
end pkg_book;
create package body pkg_book as
function getBookCount return number as
begin
declare book_count number;
begin
select count(*) into book_count from t_book;
return book_count;
end;
end getBookCount;
function getTableCount(table_name varchar2) return number as
begin
declare recore_count number;
query_sql varchar2(300);
begin
query_sql:='select count(*) from ' || table_name;
execute immediate query_sql into recore_count;
return recore_count;
end;
end getTableCount;
procedure addBook(bookName in varchar2,typeId in number) as
begin
declare maxId number;
begin
select max(id) into maxId from t_book;
insert into t_book values(maxId+1,bookName,typeId);
commit;
end;
end addBook;
end pkg_book;
set serveroutput on;
begin
dbms_output.put_line('表t_book有'|| pkg_book.getBookCount() ||'条数据');
end;
create user TEST identified by 123456 default tablespace users;
grant create session to TEST;
alter user TEST account lock;
alter user TEST account unlock;
alter user TEST identified by 123;
drop user TEST cascade;
create user TEST identified by 123456 default tablespace users;
create user TEST identified by 123456 default tablespace users;
grant create session to TEST;
grant create table to TEST;
select * from dba_sys_privs;
create user TEST2 identified by 123456 default tablespace users;
grant create session,create table to TEST with admin option;
revoke create session,create table from TEST;
create user TEST2 identified by 123456 default tablespace users;
grant create session to TEST2;
grant create table to TEST2;
grant create session to TEST;
grant create table to TEST;
select * from sys.aa ;
授权
grant select on AA to TEST;
update sys.AA set name='喝喝';
delete from sys.AA ;
grant all on AA to TEST;
传播性
grant select on sys.AA to TEST2;
grant select on AA to TEST with grant option;
select * from dba_tab_privs where grantee='TEST'
revoke update on AA from TEST;
角色：
select * from dba_roles;
grant select, update,insert ,delete on AA to role_AA;
revoke all on AA from TEST,TEST2;
grant role_AA to TEST;
