常见问题1000例Oracle(八)
emp group by deptno;
begin
delete dept_sal;
for v_emp in cur_emp LOOP
DBMS_OUTPUT.put_line(v_emp.deptno||' '||v_emp.total_emp||' '||v_emp.total_sal);
insert into dept_sal values(v_emp.deptno,v_emp.total_emp,v_emp.total_sal);
end LOOP;
end;
--execute dml statement
INSERT INTO emp(empno,deptno,sal) VALUES('123','10',10000);
SELECT * FROM dept_sal;
DELETE EMP WHERE empno=123;
SELECT * FROM dept_sal;
---trigger example 3--
drop table employee;
CREATE TABLE employee ( id VARCHAR2(4) NOT NULL, name VARCHAR2(15) NOT NULL, age NUMBER(2) NOT NULL, sex CHAR NOT NULL );
DESC employee;
--
INSERT INTO employee VALUES('e101','zhao',23,'M');
INSERT INTO employee VALUES('e102','jian',21,'F');
INSERT INTO employee VALUES('e103','jian',21,'F');
-- ¨ ±í
CREATE TABLE old_employee AS SELECT * FROM employee;
DESC old_employee;
--trigger--
drop trigger save_delete_date;
create or replace trigger save_delete_date
after delete on employee
for each row
begin
insert into old_employee values(:old.id,:old.name,:old.age,:old.sex);
end;
---delete opreation--
DELETE employee;
delete old_employee;
SELECT * FROM old_employee
select * from employee;
----trigger exampel4---
-- 6------------------------
-- ¨ · ÷ ×÷CREATE DROP log_info±í
-- ¨±í
CREATE TABLE log_info ( manager_user VARCHAR2(15), manager_date VARCHAR2(15), manager_type VARCHAR2(15), obj_name VARCHAR2(15), obj_type VARCHAR2(15) );
-- ¨ · ÷
drop trigger trig_log_info;
CREATE OR REPLACE TRIGGER trig_log_info
AFTER CREATE OR DROP ON SCHEMA
BEGIN INSERT INTO log_info
VALUES(USER,SYSDATE,SYS.DICTIONARY_OBJ_NAME,SYS.DICTIONARY_OBJ_OWNER, SYS.DICTIONARY_OBJ_TYPE);
END;
/
CREATE OR REPLACE TRIGGER trig_log_info
AFTER CREATE OR DROP ON SCHEMA
BEGIN INSERT INTO log_info
VALUES(USER,SYSDATE,SYS.DICTIONARY_OBJ_NAME,SYS.DICTIONARY_OBJ_OWNER, SYS.DICTIONARY_OBJ_TYPE);
END;
/
--
CREATE TABLE a(id NUMBER);
CREATE TYPE aa AS OBJECT(id NUMBER);
SELECT * FROM log_info;
---study package--
create or replace package test_package
as
type outlist is ref cursor;
procedure getSeniorHighSchool(
maxrow in number,
minrow in number,
return_list out outlist);
end test_package;
----create package body---
create or replace body test_package
is
procedure getSeniorHighSchool(
maxrow in number,
minrow in number,
return_list out outlist)
as
begin
for
select * from (select a.*,rownum rnum from
(
--
) a where rownum<=maxrow) where rnum >=minrow;
end;
---test
create table employee_gary(employee_id number, last_name varchar2(2),salary number(10,2),department_id number);
insert into employee_gary values(1,'qu',10000,001);
insert into employee_gary values(1,'qu',10000,002);
insert into employee