常见问题1000例Oracle(七)
student_emailaddress,
student_introduce;
end;
end LOOP;
end gary_test4;
--execute the procedure
--execute proc_name;
set serveroutput on;
--execute gary_test1
declare
student_name varchar2(20);
begin
gary_test1(1,student_name);
end;
--execute gary_test2
declare
student_name varchar2(20);
begin
gary_test2(2,student_name);
DBMS_OUTPUT.put_line('----studnet name-----');
DBMS_OUTPUT.put_line(student_name);
end;
--execute gary_test3
declare
student_name varchar2(20);
begin
gary_test3(3,student_name);
end;
--execute gary_test4
set serveroutput on;
declare
student_name varchar2(20);
begin
gary_test4(1,student_name);
end;
-- trigger
--create table
drop table test;
drop table test_log;
CREATE TABLE test ( t_id NUMBER(4), t_name VARCHAR2(20), t_age NUMBER(2), t_sex CHAR );
CREATE TABLE test_log ( l_user VARCHAR2(15), l_type VARCHAR2(15), l_date VARCHAR2(30) );
---create trigger
drop trigger test_trigger;
create or replace trigger test_trigger
after delete or insert or update on test
declare v_type test_log.l_type%type:='none';
begin
--insert
if INSERTING then
v_type:='insert';
--insert into test_log values('007','||||','||sysdate||');
Dbms_Output.put_line(' ');
---upate
elsif UPDATING then
v_type:='update';
--insert into test_log values('007','||v_type||','||sysdate||');
Dbms_Output.put_line(' ');
--delete
elsif DELETING then
v_type:='delete';
--insert into test_log values('007',v_type,'||sysdate||');
Dbms_Output.put_line(' ');
end if;
insert into test_log values(user,v_type,TO_CHAR(sysdate,'yyyy-mm-dd hh24:mi:ss'));
end;
--execute the trigger
insert into test values(0001,'garyqu','20','f');
INSERT INTO test VALUES(101,'zhao',22,'M');
UPDATE test SET t_age = 30 WHERE t_id = 101;
DELETE test WHERE t_id = 101;
select * from test;
select * from test_log;
------trigger example 2 summary ---
drop table emp;
create table emp(empno varchar2(20),deptno varchar2(20),sal number(20,2));
drop table dept_sal;
create table dept_sal AS
select deptno,count(empno) AS total_emp,sum(sal) AS total_sal from emp group by deptno;
DESC dept_sal;
--create trigger--
drop trigger emp_info;
create or replace trigger emp_info
after insert or update or delete on emp
declare Cursor cur_emp is
select deptno,count(empno) AS total_emp,sum(sal) AS total_sal from