CREATE TABLESPACE tablespacename
DATAFILE ‘filename’ [SIZE integer [K|M]]
[AUTOEXTEND [OFF|ON]];
CREATE USER 命令的语法是：
CREATE USER MARTIN
IDENTIFIED BY martinpwd
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;
授权语法
GRANT CONNECT TO MARTIN;
GRANT RESOURCE TO MARTIN;
--|************************************************
create table tb_shopType
(
@H_404_25@ ID number(10) primary key,
@H_404_25@ typeName varchar2(10) not null
);
insert into tb_shopType values(1,'手机');
insert into tb_shopType values(2,'电脑');
insert into tb_shopType values(3,'MP3');
create table tb_shop
(
@H_404_25@ ID number(10) primary key,
@H_404_25@ shopId varchar2(20) unique not null,
@H_404_25@ shopName varchar2(20) not null,
@H_404_25@ price number(6,2) not null,
@H_404_25@ shopTypeId number(10) not null,
@H_404_25@ manufacturingDate date not null,
@H_404_25@ constraint ck_price check(price>0),
@H_404_25@ constraint fk_shopTypeId foreign key(shopTypeId)
@H_404_25@ references tb_shopType(ID)
);
insert into tb_shop values(1,'M0001','诺基亚 E71',1910.23,1,'04-4月-10');
insert into tb_shop values(2,'M0002','诺基亚 N89',2230.50,'01-4月-09');
insert into tb_shop values(3,'C0001','联想 Y460A-ITH',5549.50,2,'21-4月-10');
insert into tb_shop values(4,'C0002','华硕 F83E667Vf-SL',4999.00,'01-4月-09');
insert into tb_shop values(5,'MP0001','蓝魔T13FHD',599.00,3,'11-4月-09');
insert into tb_shop values(6,'MP0002','苹果iPod nano',1040.00,'01-4月-09');
alert table tb_shop add memo varchar2(200);
alert table tb_shop modify memo varchar2(50);
alert table tb_shop drop column memo;
truncate table tb_shop;
drop table tb_shop;
update tb_shop set price =price-100 where id=3
select * from tb_shop where manufacturingDate='11-4月-09';
delete from tb_shop where manufacturingDate=to_date('2009-4-11','yyyy-mm-dd');
alter user scott account unlock;
alter user scott identified by tiger;
alter user hr account unlock;
alter user hr identified by hr;
--查询工资最高的员工
select first_name,last_name,salary
from employees
where salary=(select max(salary) from employees);
select first_name,salary,department_id
from employees
where salary>all(select salary from employees where department_id=20);
select first_name,department_id from employees
where (salary,department_id) in (
select min(salary),department_id from employees
group by department_id
)order by department_id;
select employee_id,first_name,department_id
from employees a
where exists(
select * from employees b where b.manager_id=a.employee_id
)
order by department_id,employee_id;
create table emp(empId,ename,hireDate,deptId)
as
select a.employee_id,a.first_name||a.last_name,a.hire_date,a.department_id
from employees a
where a.department_id in (90,110);
insert into emp
select a.employee_id,a.department_id
from employees a where a.department_id=20;
delete emp where emp.deptid=
(select department_id from departments where department_name='Marketing');
update emp set(hiredate,deptid)=
(select hiredate,deptid from emp where emp.empid=206)
where emp.empid=100;
grant select on emp to scott;
grant update(empid,ename) on emp to scott;
revoke delete on emp from scott;
delete emp where empid=101;
savepoint p1;
insert into emp values(200,'孙悟空','12-2月-10',90);
select empid,ename from emp;
rollback to p1;
commit;
--************************************************************
set serveroutput on
set verify off
DECLARE
v_totalSal NUMBER(5);
v_deptno NUMBER(2);
BEGIN
@H_404_25@ select deptno into v_deptno from dept where dname=&dname;
@H_404_25@ select sum(sal) into v_totalSal from emp where deptno=v_deptno;
@H_404_25@ dbms_output.put_line('总工资为:'|| v_totalSal);
EXCEPTION
@H_404_25@ WHEN NO_DATA_FOUND THEN
@H_404_25@ dbms_output.put_line('输入的部门编号不存在！');
END;
/
DECLARE
v_empno number(4);
v_ename varchar(10);
v_hiredate date;
BEGIN
@H_404_25@ v_empno:=&empno;
@H_404_25@ vselect ename,hiredate into v_ename,v_hiredate from emp where empno=v_empno;
@H_404_25@ dbms_output.put_line('姓名:'|| v_ename);
@H_404_25@ dbms_output.put_line('出生年月:'|| to_char(v_hiredate,'yyyy"年"mm"月"dd"日"');
EXCEPTION
@H_404_25@ WHEN NO_DATA_FOUND THEN
@H_404_25@ dbms_output.put_line('输入的员工编号不存在！');
END;
/
DECLARE
v_pi CONSTANT NUMBER(6,5):=3.14;
v_r number(1):=2;
v_area number(6,2);
BEGIN
@H_404_25@ v_area:=v_pi*v_r;
@H_404_25@ DBMS_OUTPUT.PUT_LINE('圆周率:'|| v_pi);
@H_404_25@ DBMS_OUTPUT.PUT_LINE('半径:' || v_r);
@H_404_25@ DBMS_OUTPUT.PUT_LINE('面积:' || v_area);
END;
/
DECLARE
@H_404_25@ v_sal number(7,2);
@H_404_25@ v_comm number(7,2);
@H_404_25@ v_totalSal number(7,2);
BEGIN
@H_404_25@ select sal,comm into v_sal,v_comm
@H_404_25@ from emp where empno=&empno;
@H_404_25@ v_comm:=NVL(v_comm,0);
@H_404_25@ v_totalSal:=v_sal+v_comm;
@H_404_25@ DBMS_OUTPUT.PUT_LINE('基本工资:'|| v_sal);
@H_404_25@ DBMS_OUTPUT.PUT_LINE('补助:'|| v_comm);
@H_404_25@ DBMS_OUTPUT.PUT_LINE('总工资:'|| v_totalSal);
EXCEPTION
@H_404_25@ WHEN NO_DATA_FOUND THEN
@H_404_25@ DBMS_OUTPUT.PUT_LINE('请输入正确的雇员信息!');
END;
/
DECLARE
@H_404_25@ v_ename emp.ename%type;
@H_404_25@ v_sal emp.sal%type;
@H_404_25@ c_tax_rate constant number(3,2):=0.02;
@H_404_25@ v_tax_sal v_sal%type;
BEGIN
@H_404_25@ select ename,sal into v_ename,v_sal from emp where empno=&empno;
@H_404_25@ v_tax_sal:=v_sal*c_tax_rate;
@H_404_25@ DBMS_OUTPUT.PUT_LINE('雇员名:'|| v_ename);
@H_404_25@ DBMS_OUTPUT.PUT_LINE('雇员工资:'|| v_sal);
@H_404_25@ DBMS_OUTPUT.PUT_LINE('雇员所得税:'|| v_tax_sal);
EXCEPTION
@H_404_25@ WHEN NO_DATA_FOUND THEN
@H_404_25@ DBMS_OUTPUT.PUT_LINE('请输入正确的雇员信息!');
END;
/
DECLARE
@H_404_25@ v_emp_record detpt%rowtype;
BEGIN
@H_404_25@ select * frm v_emp_record from dept where deptno=&deptno;
@H_404_25@ DBMS_OUTPUT.PUT_LINE('部门编号:'|| v_emp_record.deptno);
@H_404_25@ DBMS_OUTPUT.PUT_LINE('部门名称:'|| v_emp_record.dname);
@H_404_25@ DBMS_OUTPUT.PUT_LINE('部门地区:'|| v_emp_record.loc);
END;
/
DECLARE
TYPE EMP_RECORD_TYPE IS RECORD
(
@H_404_25@ ename emp.ename%type,
@H_404_25@ sal emp.sal%type,
@H_404_25@ comm emp.comm%type,
@H_404_25@ total_sal sal%type
)
v_emp_record EMP_RECORD_TYPE;
BEGIN
@H_404_25@ select ename,sal,NVL(comm,0),sal+NVL(comm,0) into v_emp_record
@H_404_25@ from emp where empno=7521;
@H_404_25@ DBMS_OUTPUT.PUT_LINE('雇员名:'|| v_emp_record.ename);
@H_404_25@ DBMS_OUTPUT.PUT_LINE('工资:'|| v_emp_record.sal);
@H_404_25@ DBMS_OUTPUT.PUT_LINE('资金:'|| v_emp_record.comm);
@H_404_25@ DBMS_OUTPUT.PUT_LINE('总工资:'|| v_emp_record.total_sal);
END;
/
DECLARE
TYPE EMP_TABLE_TYPE IS TABLE OF NUMBER(4)
INDEX BY BINARY_INTEGER;
TYPE EMP_TABLE_TYPE_ENAMES IS TABLE OF emp.ename%type
INDEX BY BINARY_INTEGER;
v_emp_empnos EMP_TABLE_TYPE_EMPNOS;
v_emp_enames EMP_TABLE_TYPE_ENAMES;
BEGIN
@H_404_25@ v_emp_empnos(0):=7369;
@H_404_25@ v_emp_empnos(1):=7521;
@H_404_25@ v_emp_empnos(2):=7566;
@H_404_25@ select ename into v_emp_ename(0)
@H_404_25@ from emp where empno=v_emp_empnos(0);
@H_404_25@ select ename into v_emp_ename(1)
@H_404_25@ from emp where empno=v_emp_empnos(1);
@H_404_25@ select ename into v_emp_ename(2)
@H_404_25@ from emp where empno=v_emp_empnos(2);
@H_404_25@ dbms_output.put_line('雇员编号:'||v_emp_empnos(0)||' 雇员名:'||v_emp_enames(0));
@H_404_25@ dbms_output.put_line('雇员编号:'||v_emp_empnos(1)||' 雇员名:'||v_emp_enames(1));
@H_404_25@ dbms_output.put_line('雇员编号:'||v_emp_empnos(2)||' 雇员名:'||v_emp_enames(2));
END;
/
DECLARE
TYPE DEPT_TABLE_TYPE IS TABLE OF dept%ROWTYPE
INDEX BY BINARY_INTEGER;
v_dept_table DEPT_TABLE_TYPE;
BEGIN
@H_404_25@ select deptno,dname into v_dept_table(0).deptno,v_dept_table(0).dname
@H_404_25@ from dept where deptno=10;
@H_404_25@ select deptno,dname into v_dept_table(1).deptno,v_dept_table(1).dname
@H_404_25@ from dept where deptno=20;
@H_404_25@ select deptno,dname into v_dept_table(2).deptno,v_dept_table(2).dname
@H_404_25@ from dept where deptno=30;
@H_404_25@ dbms_output.put_line('部门编号 部门名称');
@H_404_25@ dbms_output.put_line(v_dept_table(0).deptno ||' '||v_dept_table(0).dname);
@H_404_25@ dbms_output.put_line(v_dept_table(1).deptno ||' '||v_dept_table(1).dname);
@H_404_25@ dbms_output.put_line(v_dept_table(2).deptno ||' '||v_dept_table(2).dname);
END;
/
declare
type dept_varray_type is varray(3) of varchar2(10);
v_dept_names_varray dept_varray_type:=dept_varray_type(null,null,null);
begin
@H_404_25@ v_dept_names_varray(1):='ACCOUNTING';
@H_404_25@ v_dept_names_varray(2):='RESEARCH';
@H_404_25@ v_dept_names_varray(3):='SALES';
@H_404_25@ dbms_output.put_line('===部门名称===');
@H_404_25@ dbms_output.put_line(v_dept_names_varray(1));
@H_404_25@ dbms_output.put_line(v_dept_names_varray(2));
@H_404_25@ dbms_output.put_line(v_dept_names_varray(3));
end;
/
declare
v_emp emp%rowtype;
v_dept_avgSal number(7,2);
begin
v_emp.empno:=&empno;
select sal,comm,deptno into v_emp.sal,v_emp.comm,v_emp.deptno
from emp where emp.empno=v_emp.empno;
dbms_output.put_line('雇员编号:'||v_emp.empno);
dbms_output.put_line('雇员更新前奖金:"|| nvl(v_emp.comm,0));
select avg(sal) into v_dept_avgSal from emp
where deptno=v_emp.deptno;
if v_emp.comm is null then
@H_404_25@ update emp set comm=v_dept_avgSal*0.1
@H_404_25@ where empno=v_emp.empno;
else
@H_404_25@ if v_emp.sal<v_dept_avgSal then
@H_404_25@ update emp set comm=comm+v_dept_avgSal*0.1
@H_404_25@ where empno=v_emp.empno;
@H_404_25@ else
@H_404_25@ update emp set comm=comm+v_emp.sal*0.1
@H_404_25@ where empno=v_emp.empno;
@H_404_25@ end if;
end if;
select comm into v_emp.comm from emp where empno=v_emp.empno;
dbms_output.put_line('雇员更新后奖金：'|| v_emp.comm);
excption
@H_404_25@ when no_data_found then
@H_404_25@ dbms_output.put_line('该雇员不存在');
end;
/
declare
v_deptno number(2):=&deptno;
begin
@H_404_25@ case v_deptno
@H_404_25@ when 10 then
@H_404_25@ update emp set comm=
@H_404_25@ case when comm is null then 100 else comm*1.1 end
@H_404_25@ where deptno=v_deptno;
@H_404_25@ when 20 then
update emp set comm=
@H_404_25@ case when comm is null then 200 else comm*1.2 end
@H_404_25@ where deptno=v_deptno;
@H_404_25@ when 30 then
@H_404_25@ update emp set comm=
@H_404_25@ case when comm is null then 300 else comm*1.3 end
@H_404_25@ where deptno=v_deptno;
@H_404_25@ else
@H_404_25@ dbms_output.put_line('不存在该部门!');
@H_404_25@ end case;
end;
/
declare
@H_404_25@ v_empno number(4):=&empno;
@H_404_25@ v_sal number(7,2);
begin
@H_404_25@ select sal into v_sal from emp
@H_404_25@ where empno=v_empno;
@H_404_25@ case
@H_404_25@ when v_sal<2000 then
@H_404_25@ dbms_output.put_line('一级工资');
@H_404_25@ when v_sal>=2000 then
@H_404_25@ dbms_output.put_line('二级工资');@H_404_25@
@H_404_25@ when v_sal>=3000 then
@H_404_25@ dbms_output.put_line('三级工资');
@H_404_25@ when v_sal>=4000 then
@H_404_25@ dbms_output.put_line('四级工资');
@H_404_25@ else
@H_404_25@ dbms_output.put_line('五级工资');
end case;
@H_404_25@ exception
@H_404_25@ when no_data_found then
@H_404_25@ dbms_output.put_line('请输入正确的雇员编号!');
end;
/
create table rnd_temp_table
(
@H_404_25@ ID NUMBER(4) primary key,
@H_404_25@ value varchar2(10) not null
)
declare
@H_404_25@ type rnd_varray_type is varray(4) of varchar2(10);
@H_404_25@ r_rnd_varray run_varray_type=rnd_varray_type('DALLAS','CHICAGO','BOSTON','NEWYORK');
@H_404_25@ v_loop number(2):=1;
@H_404_25@ v_index number(1);
begin
@H_404_25@ loop
@H_404_25@ if v_loop=6 then
@H_404_25@ exit;
@H_404_25@ end if
@H_404_25@ v_index:=floor(dbms_random.value(1,5));
@H_404_25@ insert into rnd_temp_table values(v_loop,v_rnd_varray(v_index));
@H_404_25@ v_loop:=v_loop+1;
@H_404_25@ end loop;
end;
/
create table tb_stock(
ID number(2) primary key,
shopName varchar2(10),
stock number(5),
day_sales_volume number(5),
max_stock number(5),
min_stock number(2),
);
insert into tb_stock values(1,'彩电',100,10,500,50);
insert into tb_stock values(2,'空调',200,20,40);
insert into tb_stock values(3,'电脑',50,15,20);
insert into tb_stock values(4,'手机',300,600,10);
declare
@H_404_25@ v_stock tb_stock%rowtype;
@H_404_25@ v_n number(4):=0;
begin
@H_404_25@ v_stock.ID:=&ID;
@H_404_25@ select stock,day_sales_volume,min_stock
@H_404_25@ into v_stock.stock,v_stock.day_sales_volume,v_stock.min_stock
@H_404_25@ from tb_stock
@H_404_25@ where ID=v_stock.ID;
@H_404_25@ while v_stock.stock>v_stock.min_stock loop
@H_404_25@ v_stock.stock:=v_stock.stock-v_stock.day_sales_volume;
@H_404_25@ v_n:=v_n+1;
@H_404_25@ end loop;
@H_404_25@ dbms_output.put_line('商品编号:'|| v_stock.ID);
@H_404_25@ dbms_output.pub_line('采购期限:'|| v_n);
exception
@H_404_25@ when no_data_found then
@H_404_25@ dbms_output.put_line('请输入正确的商品编号!');
end;
/
declare
@H_404_25@ type dept_table_type is table of dept%rowtype
@H_404_25@ index by binary_integer;
@H_404_25@ v_dept_table dept_table_type;
begin
@H_404_25@ select deptno,v_dept_table(0).dname
@H_404_25@ from dept where deptno=10;
select deptno,v_dept_table(1).dname
@H_404_25@ from dept where deptno=20;
select deptno,v_dept_table(2).dname
@H_404_25@ from dept where deptno=30;
@H_404_25@ dbms_output.put_line('部门编号 部门名称');
@H_404_25@ for i in 0..v_dept_table.COUNT-1 loop
@H_404_25@ dbms_output.put_line(v_dept_table(i).deptno || ' ' || v_dept_table(i).dname);
@H_404_25@ end loop;
end;
/
declare
@H_404_25@ v_sal emp.sal%type;
@H_404_25@ v_name emp.ename%type;
begin
@H_404_25@ select sal,ename into v_sal,v_ename
@H_404_25@ from emp where empno=&empno;
@H_404_25@ if v_sal<3000 then
@H_404_25@ update emp set comm=sal*0.1 where ename=v_ename;
@H_404_25@ else
@H_404_25@ null;
@H_404_25@ end if;
end;
/
declare
v_dept_row dept%rowtype;
begin
select * into v_dept_row from dept;
insert into dept values(10,'PRODUCE','CHINA');
exception
when too_many_rows then
dbms_output.put_line('返回了多行,请使用游标来处理多行记录的集合');
when dup_val_on_index then
dbms_output.put_line('主键不能重复!');
end;
/
declare
@H_404_25@ ept_no_emp exception;
begin
@H_404_25@ update emp set comm=
@H_404_25@ case when comm is null then 50 else comm*1.0 end
@H_404_25@ where empno=&empno;
@H_404_25@ if sql%notfound then
@H_404_25@ raise ept_no_emp;
@H_404_25@ else
@H_404_25@ dbms_output.put_line('该雇员的奖金已经更新！');
@H_404_25@ end if;
@H_404_25@ exception
@H_404_25@ when ept_no_emp then
@H_404_25@ dbms_output.put_line('该雇员不存在!');
end;
/
DECLARE
TYPE CREATE_TABLE_RECORD IS RECORD
(
@H_404_25@ field_name varchar2(15),
@H_404_25@ field_type varchar2(15),
@H_404_25@ field_explain varchar2(15)
);
TYPE DYNAMIC_sql_TABLE IS TABLE OF CREATE_TABLE_RECORD
INDEX BY BINARY_INTEGER;
v_dynamic_sql_table DYNAMIC_sql_TABLE;
v_create_table_name VARCHAR2(20);
v_dynamic_ddl_sql VARCHAR2(500):='';
v_dynamic_dcl_sql VARCHAR2(500):='';
v_grant_user VARCHAR2(10);
v_grant_authority VARCHAR2(10);
BEGIN
@H_404_25@ v_create_table_name:='STVD';
@H_404_25@ v_grant_user:='hr';
@H_404_25@ v_grant_authority:='select';
@H_404_25@ v_dynamic_sql_table(0).field_name:='sid';
@H_404_25@ v_dynamic_sql_table(0).field_type:='varchar2(10)';
@H_404_25@ v_dynamic_sql_table(0).field_explain:='primary key';
@H_404_25@ v_dynamic_sql_table(1).field_name:='sname';
@H_404_25@ v_dynamic_sql_table(1).field_type:='varchar2(10)';
@H_404_25@ v_dynamic_sql_table(1).field_explain:='not null';
@H_404_25@ v_dynamic_sql_table(2).field_name:='sclass';
@H_404_25@ v_dynamic_sql_table(2).field_type:='varchar2(10)';
@H_404_25@ v_dynamic_sql_table(2).field_explain:='not null';
@H_404_25@
@H_404_25@ v_dynamic_ddl_sql:='create table '|| v_create_table_name ||chr(13)||'('||chr(13);
@H_404_25@ for i in 0..v_dynamic_sql_table.COUNT-1 loop
@H_404_25@ v_dynamic_ddl_sql:=v_dynamic_ddl_sql ||
@H_404_25@ v_dynamic_sql_table(i).field_name ||' '||
@H_404_25@ v_dynamic_sql_table(i).field_type ||' '||
@H_404_25@ v_dynamic_sql_table(i).field_explain ||','|| chr(13);
@H_404_25@ end loop;
@H_404_25@ v_dynamic_ddl_sql:=substr(v_dynamic_ddl_sql,length(v_dynamic_ddl_sql)-2);
@H_404_25@ v_dynamic_ddl_sql:=v_dynamic_ddl_sql||chr(13)||')';
@H_404_25@ v_dynamic_dcl_sql:='grant '|| v_grant_authority||' on '||v_create_table_name||' to '||v_grant_user;
@H_404_25@ execute immediate v_dynamic_ddl_sql;
@H_404_25@ execute immediate v_dynamic_dcl_sql;
END;
/
DECLARE
@H_404_25@ v_dynamic_sql VARCHAR2(100);
BEGIN
@H_404_25@ v_dynamic_sql:='UPDATE emp SET sal=sal*(1+:percent/100.0) where deptno=:deptno';
@H_404_25@ execute immediate v_dynamic_sql using &percent,&deptno;
END;
/
declare
@H_404_25@ v_dynamic_sql varchar2(100);
@H_404_25@ v_sal number(7,2);
@H_404_25@ v_empno number(4):=&empno;
@H_404_25@ v_percent number(2):=&percent;
begin
@H_404_25@ select sal into v_sal from emp where empno=v_empno;
@H_404_25@ dbms_output.put_line('更新前工资：'||v_sal);
@H_404_25@ v_dynamic_sql:='UPDATE emp SET sal=sal*(1+:percent/100.0)
@H_404_25@ where empno=:empno returning sal into :sal';
@H_404_25@ execute immediate v_dynamic_sql using v_percent,v_empno RETURNING INTO v_sal;
@H_404_25@ dbms_output.put_line('增长率:'||v_percent||'%');
@H_404_25@ dbms_output.put_line('新工资:'|| v_sal);
end ;
/
declare
@H_404_25@ v_dynamic_sql varchar2(100);
@H_404_25@ emp_record emp%ROWTYPE;
begin
@H_404_25@ v_dynamic_sql:='select * from emp where empno=:empno';
@H_404_25@ execute immediate v_dynamic_sql into emp_record using &empno;
@H_404_25@ dbms_output.put_line('雇员'|| emp_record.ename || '的工资是：'||emp_record.sal);
end;
/
DECLARE
TYPE EMP_ENAME_TABLE IS TABLE OF emp.ename%TYPE
INDEX BY BINARY_INTEGER;
v_emp_ename_table EMP_ENAME_TABLE;
v_dynamic_sql VARCHAR2(100);
begin
v_dynamic_sql:='select ename from emp where deptno=:deptno';
execute immediate v_dynamic_sql
BULK COLLECT INTO v_emp_ename_table USING &deptno;
for i in 1..v_emp_ename_table.COUNT LOOP
@H_404_25@ dbms_output.put_line(v_emp_ename_table(i));
end loop;
end;
/
--***********************************************************************
create table tb_test(A varchar2(10),B varchar2(10));
insert into tb_test values('aa','bb');
insert into tb_test values('aa','cc');
insert into tb_test values('bb','cc');
insert into tb_test values('aa','cc');
select * from tb_test
create or replace procedure proc_del_dup_rec
as
begin
delete tb_test a where a.ROWID=(
@H_404_25@ select max(rowid) from tb_test b
@H_404_25@ where
@H_404_25@ a.a=b.a and a.b=b.b
);
end;
/
create or replace procedure proc_transit_station(
@H_404_25@ v_start_station tb_station.station_name%type,
@H_404_25@ v_end_station tb_station.station_name%type,
@H_404_25@ v_line_name tb_station.line_name%type:='536'
)
as
v_start_forder tb_station.forder%type;
v_end_forder tb_station.forder%type;
v_station_line varchar2(100);
type station_name_table_type is table of tb_station.station_name%type;
v_station_name_table station_name_table_type;
begin
select forder into v_start_forder from tb_station where line_name=v_line_name
and station_name=v_start_station;
select forder into v_end_forder from tb_station where line_name=v_line_name
and station_name =v_end_station;
if v_start_forder<=v_end_forder then
@H_404_25@ select station_name bulk collect into v_station_name_table
@H_404_25@ from tb_station
@H_404_25@ where line_name=v_line_name
@H_404_25@ and forder>=v_start_forder and
@H_404_25@ forder<=v_end_forder;
else
@H_404_25@ select station_name bulk collect into v_station_name_table
@H_404_25@ from tb_station
@H_404_25@ where line_name=v_line_name
@H_404_25@ and forder<=v_start_forder and
@H_404_25@ forder>=v_end_forder order by forder desc;
end if;
dbms_output.put_line(v_line_name || '公交车从【'|| v_start_station || '->' || v_end_station || '】站的公交线路:');
for i in v_station_name_table.FIRST..v_station_name_table.LAST LOOP
@H_404_25@ v_station_line:=v_station_line || v_station_name_table(i)||'->';
end loop;
v_station_line:=Substr(v_station_line,length(v_station_line)-2);
dbms_output.put_line(v_station_line);
exception
when no_data_found then
@H_404_25@ dbms_output.put_line('请输入正确的公交车次及公交线路!');
end;
/
call proc_transit_station('常青路','武胜路','536');
call proc_transit_station('常青路','武胜路');
create or replace procedure proc_query_emp
(
@H_404_25@ param_empno number,
@H_404_25@ param_ename out varchar2,
@H_404_25@ param_salary out number
)
as
begin
@H_404_25@ select ename,sal into param_ename,param_salary from emp
@H_404_25@ where empno=param_empno;
@H_404_25@ exception
@H_404_25@ when no_data_found then
@H_404_25@ raise_application_error(-20001,'该雇员不存在!');
end;
/
DECLARE
@H_404_25@ v_empno emp.empno%type:=7788;
@H_404_25@ v_ename emp.ename%type;
@H_404_25@ v_sal emp.sal%type;
BEGIN
@H_404_25@ proc_query_emp(v_empno,v_ename,v_sal);
@H_404_25@ dbms_output.put_line(v_ename||' '||v_sal);
END;
create or replace procedure proc_compute
(
@H_404_25@ param_num1 in out number,
@H_404_25@ param_num2 in out number
)
as
v1 number,
v2 number
begin
@H_404_25@ v1:=param_num1/param_num2;
@H_404_25@ v2:=mod(param_num1,param_num2);
@H_404_25@ param_num1:=v1;
@H_404_25@ param_num2:=v2;
end;
/
declare
@H_404_25@ v_num1 number(2):=10;
@H_404_25@ v_num2 number(2):=3;
begin
@H_404_25@ proc_compute(v_num1,v_num2);
@H_404_25@ dbms_output.put_line(v_num1);
@H_404_25@ dbms_output.put_line(v_num2);
end;
/
create or replace procedure proc_add_dept
(
@H_404_25@ param_deptno number,
@H_404_25@ param_dname varchar2,
@H_404_25@ param_loc varchar2:=null
)
as
begin
@H_404_25@ insert into dept values(param_deptno,param_dname,param_loc);
exception
@H_404_25@ when dup_val_on_index then
@H_404_25@ raise_application_error(-20000,'部门编号不能重复');
end;
/
call proc_add_dept(60,'MANAGER','BEIJING');
call proc_add_dept(70,'PRODUCT');
call proc_add_dept(param_deptno=>80,param_dname=>'PURCHASE',param_loc=>'WUHAN');
call proc_add_dept(90,'ADMIN',param_loc=>'WUHAN');
create or replace function fun_get_user
return varchar2
as
@H_404_25@ v_user varchar2(100);
begin
@H_404_25@ select username into v_user from user_users;
@H_404_25@ return v_user;
end;
/
declare
@H_404_25@ v_user varchar2(100);
begin
@H_404_25@ v_user:=fun_get_user;
@H_404_25@ dbms_output.put_line('当前的用户是:'|| v_user);
end;
/
create or replace fun_get_sal(param_ename varchar2)
return number
as
v_sal emp.sal%type;
begin
@H_404_25@ select sal into v_sal from emp
@H_404_25@ where
@H_404_25@ upper(param_ename)=upper(ename);
exception
@H_404_25@ when no_data_found then
@H_404_25@ raise_application_error(-20000,'该雇员不存在');
end;
/
declare
@H_404_25@ v_ename emp.ename%type:='&v_ename';
begin
@H_404_25@ dbms_output.put_line(fun_get_sal(v_ename));
end;
/
create or replace function fun_get_emp_info
(
@H_404_25@ param_ename varchar2,
@H_404_25@ param_dname out varchar2
)
return varchar2
as
@H_404_25@ v_ejob emp.job%type;
begin
@H_404_25@ select a.job,b.dname into v_ejob,param_dname
@H_404_25@ from emp a,dept b
@H_404_25@ wher a.deptno=b.deptno and
@H_404_25@ upper(a.ename)=upper(param_ename);
@H_404_25@ return v_ejob;
exception
@H_404_25@ when no_data_found then
@H_404_25@ raise_application_error(-20000,'该雇员不存在!');
end;
/
delcare
@H_404_25@ v_ename varchar2(20):='&v_ename';
@H_404_25@ v_dname varchar2(20);
@H_404_25@ v_ejob varchar2(20);
begin
@H_404_25@ v_ejob:=fun_get_emp_info(v_ename,v_dname);
@H_404_25@ dbms_output.put_line('雇员名称'|| v_ename);
@H_404_25@ dbms_output.put_line('部门名称'|| v_dname);
@H_404_25@ dbms_output.put_line('雇员岗位'|| v_ejob);
end;
/
create or replace function fun_compute
@H_404_25@ param_num1 number,
@H_404_25@ param_num2 in out number
)
return number;
as
v1 number
begin
@H_404_25@ v1:=param_num1/param_num2;
@H_404_25@ param_num2:=mod(param_num1,param_num2);
@H_404_25@ return v1;
end;
/
declare
@H_404_25@ v_num1 number(2):=10;
@H_404_25@ v_num2 number(2):=3;
@H_404_25@ v_result number(2);
begin
@H_404_25@ v_result:=fun_compute(v_num1,v_num2);
@H_404_25@ dbms_output.put_line(v_num2);
@H_404_25@ dbms_output.put_line(v_result);
end;
/
select text from user_source where name=upper('fun_compute');
col object_name formart a20
select object_name,created,status from user_objects
where object_type in ('PROCEDURE','FUNCTION');
select name,type from User_dependences a
where a.referenced_name='EMP';
alter table emp modify ename varchar2(30);
select object_name,status from user_objects a,User_dependencies b
where b.name=a.object_name and
a.object_type in ('PROCEDURE','FUNCTION') and b.referenced_name='EMP';
alter prodecure proc_query_emp compile;
create or replace package emp_package
as
@H_404_25@ g_deptno number(3):=30;
@H_404_25@ procedure pro_add_employee(
@H_404_25@ param_empno number,
@H_404_25@ param_ename varchar2,
@H_404_25@ param_sal number,
@H_404_25@ param_deptno number:=g_deptno
@H_404_25@ );
@H_404_25@ function fun_get_sal(param_empno number) return number;
end emp_package;
/
create or replace package body emp_package
as
@H_404_25@ function fun_validate_deptno(param_deptno number)
@H_404_25@ return boolean
@H_404_25@ as
@H_404_25@ v_temp number;
@H_404_25@ begin
@H_404_25@ select 1 into v_temp from dept
@H_404_25@ where deptno=param_deptno;
@H_404_25@ return true;
@H_404_25@ exception
@H_404_25@ when no_data_found then
@H_404_25@ return false;
@H_404_25@ end;
@H_404_25@ procedure pro_add_employee
@H_404_25@ (
@H_404_25@ param_empno number,
@H_404_25@ param_deptno number:=g_deptno
@H_404_25@ )
@H_404_25@ as
@H_404_25@ if fun_validate_deptno(param_deptno) then
@H_404_25@ insert into emp(empno,deptno)
@H_404_25@ values(param_empno,param_ename,param_sal,param_deptno);
@H_404_25@ else
@H_404_25@ raise_application_error(-20001,'不存在部门');
@H_404_25@ end if;
@H_404_25@ exception
@H_404_25@ when dup_val_on_index then
@H_404_25@ raise_application_error(-20002,'该雇员编号已经传值!');
@H_404_25@ end;
@H_404_25@ function fun_get_sal(param_empno number) return number
@H_404_25@ as
@H_404_25@ v_sal emp.sal%type;
@H_404_25@ begin
@H_404_25@ select sal into v_sal from emp where empno=param_empno;
@H_404_25@ return v_sal;
@H_404_25@ exception
@H_404_25@ when no_data_found then
@H_404_25@ raise_application_error(-20003,'该雇员不存在!');
@H_404_25@ end;
end emp_packgae;
/
select text from user_source
where name='EMP_PACKAGE' AND TYPE='PACKAGE';
---************************************************
DECLAREcursor emp_cursor ISSelect ename,sal from emp where deptno=30;v_ename emp.ename%type;v_sal emp.sal%type;BEGINOPETN emp_cursor;LOOPFETCH emp_cursor INTO v_ename,v_sal;EXIT WHEN emp_cursor%notfound;dbms_output.put_line(v_ename || ' '|| v_sal);END LOOP;CLOSE emp_cursor;END;/DECLARECURSOR emp_cursor ISSELECT ename,sal FROM emp WHERE deptno=10;TYPE EMP_RECORD IS RECORD(ename emp.ename%type,sal emp.sal%type);TYPE ENAME_TABLE_TYPE IS TABLE OF EMP_RECORD;v_ename_table ENAME_TABLE_TYPE;BEGINOPEN emp_cursor;FETCH emp_cursor BULK COLLECT INTO v_ename_table;CLOSE emp_cursor;FOR i in v_ename_table.FIRST..v_ename_table.LAST LOOPdbms_output.put_line(v_ename_table(i).ename || ' '||v_ename_table(i).sal);END LOOP;END;/DECLARECURSOR emp_cursor ISSELECT ename FROM emp WHERE deptno=10;TYPE ENAME_TABLE_TYPE IS TABLE OF VARCHAR2(10);v_ename_table ENAME_TABLE_TYPE;BEGINIF NOT emp_cursor%ISOPEN THENOPEN emp_cursor;END IF:FETCH emp_cursor BULK COLLECT INTO v_ename_tablel;DBMS_OUTPUT.PUT_LINE('提取的总计行数：'|| emp_cursor%ROWCOUNT);CLOSE emp_cursor;END:/DELCARECURSOR emp_cursor IS SELECT ename,sal FROM emp WHERE deptno=10;emp_record emp_cursor%ROWTYPE;BEGINOPEN emp_cursor;LOOPFETCH emp_cursor INTO emp_record;EXIT WHEN emp_cursor%NOTFOUND;DBMS_OUTPUT.PUT_LINE(emp_record.ename || ' ' || emp_record.sal);END LOOP;CLOSE emp_cursor;END;/DECLARECURSOR emp_cursor(param_dept NUMBER) ISSELECT ename,sal FROM emp WHERE deptno=param_dept;emp_record emp_cursor%ROWTYPE;BEGINOPEN emp_cursor(10);LOOPFETCH emp_cursor INTO emp_record;EXIT WHEN emp_cursor%NOTFOUND;DBMS_OUTPUT.PUT_LINE(emp_record.ename||' '|| emp_record.sal);END LOOP;CLOSE emp_cursor;END;/DECLARECURSOR emp_cursor ISSELECT ename,sal FROM emp FOR UPDATE;v_emp_row emp_cursor%ROWTYPE;v_update_emp_count NUMBER(2):=0;BEGINOPEN emp_cursor;LOOPFETCH emp_cursor INTO v_emp_row;EXIT WHEN emp_cursor%NOTFOUND;IF v_emp_row.sal<2000 THENUPDATE emp SET sal=sal+100 WHERE CURRENT OF emp_cursor;v_update_emp_count:=v_update_emp_count+1;END IF;END LOOP;DBMS_OUTPUT.PUT_LINE('共有' || v_upate_emp_count || '名雇员被更新了!');CLOSE emp_cursor;END;/DECLARECURSOR emp_cursor ISSELECT deptno FROM emp FOR UPDATE;v_emp_row emp_cursor%ROWTYPE;v_update_emp_count NUMBER(2):=0;BEGINOPEN emp_cursor;LOOPFETCH emp_cursor INTO v_emp_row;EXIT WHEN emp_cursor%NOTFOUND;IF v_emp_row.deptno=30 THENDELETE emp WHERE CURRENT OF emp_cursor;v_update_emp_count:=v_update_emp_count+1;END IF;END LOOP;DBMS_OUTPUT.PUT_LINE('共有' || v_upate_emp_count || '名雇员被删除了!');CLOSE emp_cursor;END;/DECLARECURSOR emp_cursor ISSELECT ename,sal FROM emp FOR UPDATE NOWART;v_emp_row emp_cursor%ROWTYPE;v_update_emp_count NUMBER(2):=0;BEGINOPEN emp_cursor;LOOPFETCH emp_cursor INTO v_emp_row;EXIT WHEN emp_cursor%NOTFOUND;IF v_emp_row.sal<2000 THENUPDATE emp SET sal=sal+100 WHERE CURRENT OF emp_cursor;v_update_emp_count:=v_update_emp_count+1;END IF;END LOOP;DBMS_OUTPUT.PUT_LINE('共有' || v_upate_emp_count || '名雇员被更新了!');CLOSE emp_cursor;END;/DECLARECURSOR emp_cursor IS SELECT ename,sal FROM emp;BEGINFOR emp_row IN emp_cursor LOOPDBMS_OUTPUT.PUT_LINE('第'|| emp_cursor%ROWCOUNT || '个雇员:' || emp_row.ename);END LOOP;END;/BEGINFOR emp_row IN (SELECT ename,sal FROM emp) LOOPDBMS_OUTPUT.PUT_LINE(emp_row.ename);END LOOP;END;/DECLARETYPE EMP_CURSOR_TYPE IS REF CURSOR;emp_cursor EMP_CURSOR_TYPE;emp_row emp%ROWTYPE;BEGINOPEN emp_cursor FORSELECT * FROM emp WHERE deptno=30;LOOPFETCH emp_cursocursor%NOTFOUND;DBMS_OUTPUT.PUT_LINE('第'||emp_cursor%ROWCOUNT ||'个雇员:'||emp_row_ename);END LOOP;CLOSE emp_cursor;END;/CREATE OR REPLACE PROCEDURE proc_getEmpsByDeptno(param_deptno NUMBER,param_resultset OUT SYS_REFCURSOR)ASBEGINOPEN param_resultset FORSELECT ename,sal FROM emp WHERE deptno_param_deptno;END;/DECLARETYPE EMP_RECORD_TYPE IS RECORD(ename varchar2(10),sal number(7,2));v_emp_rows SYS_REFCURSOR;v_deptno NUMBER(2):=30;v_emp_row EMP_RECORD_TYPE;BEGINproc_getEmpsByDeptno(v_deptno,v_emp_rows);LOOPFETCH v_emp_rows into v_emp_row;EXIT WHEN v_emp_rows%NOTFOUND;DBMS_OUTPUT.PUT_LINE('第'||v_emp_roow%ROWCOUNT||'个雇员 名称：'||v_emp_row.ename || ' 工资:'|| v_emp_row.sal);END LOOP；CLOSE v_emp_rows;END;/create or replace function fun_getEmpsByHireDateYear(param_HireDateYear NUMBER)return sys_refcursorasparam_resultset SYS_REFCURSOR;beginopen param_resultset forselect ename,sal from emp where extract(year from hiredate)=param_HireDateYear;return param_resultset;end;/declaretype emp_record_type is record(ename varchar2(10),2));v_emp_rows SYS_REFCURSOR;v_hireDateYear NUMBER(4):=1981;v_emp_row EMP_RECORD_TYPE;beginv_emp_rows:=fun_getEmpsByHireDateYear(v_hireDateYear);loopfetch v_emp_rows intoo v_emp_rowexit when v_emp_rows%notfound; DBMS_OUTPUT.PUT_LINE('第'||v_emp_roow%ROWCOUNT||'个雇员 名称：'||v_emp_row.ename || ' 工资:'|| v_emp_row.sal);END LOOP；CLOSE v_emp_rows;END;/declarev_empno number(4):=7700;beginupdate emp set empno=v_empno where empno=v_empno;if sql%found thendbms_output.put_line('存在该雇员');elsedbms_output.put_line('不存在该雇员');end if;end;/declarev_deptno number(2):=20;v_rows_count number;beginupdate emp set sal=sal+100 where deptno=v_deptno;v_row_count:=sql%rowcount;if v_rows_count=0 thendbms_output.put_line('没有雇员被更新!');elsedbms_output.put_line('共有'||v_rows_count || '个雇员被更新了！');end if;end;/create or replace trigger tr_sec_empbeforeinsert or update or deleteon empbeginif to_char(sysdate,'Dy') in ('星期六','星期日') then raise_application_error(-20000,'不能在休息日改变雇员信息');end if;end;/delete emp where empno= 7788;create or replace trigger tr_sec_empbeforeinsert or update or deletebegin if to_char(sysdate,'星期日') thencasewhen updating thenraise_application_error(-20001,'不能在休息日更新雇员信息');when deleting thenraise_application_error(-20002,'不能在休息日删除雇员信息');when inserting thenraise_application_error(-20003,'不能在休息日插入雇员信息');end case;end if;end;/create table audit_table(ID number primary key,tb_name varchar2(20) not null,ins number not null,upd number not null,del number not null,starttime date,endtime date);create sequenceincrement by 1start with 1maxvalue 9999999cache 10cycle;create or replace trigger tr_sec_empafterinsert or update or deleteon empdeclarev_temp number;beginselect count(*) into v_temp from audit_tablewhere tb_name='EMP';if v_temp=0 theninsert into audit_table values(seq_audit.nextnval,'EMP',SYSDATE,null);end if;casewhen inserting thenupdate audit_table set ins=ins+1,endtime=sysdatewhere tb_name='EMP';when updating thenupdate audit_table set upd=upd+1,endtime=sysdatewhere tb_name='EMP';when deleting thenupdate audit_table set del=del+1,endtime=sysdatewhere tb_name='EMP';end caseend;/create or replace tigger tr_emp_salbefore update of sal on empfor each rowbeginif :NEW.sal<:OLD.sal thenraise_application_error(-20000,'新工资不能小于原有工资‘）；end if;end;/create table audit_sal_change(ID number primary key,ename varchar2(20) not null,oldsal number(7,newsal number(7,auditTime date);create or replace trigger tr_sal_changeafter update oof sal on empfor each rowbegininsert into audit_sal_change values(seq_audit_sal_change.NEXTVAL,:OLD.ename,:OLD.sal,:NEW.sal,sysdate);end;/create or replace trigger tr_sal_changeafter update of sal on empfor each rowwhen (OLD.job='MANAGER')begininsert into audit_sal_change values(seq_audit_sal_change.NEXTVAL,sysdate);end;/create or replace view view_dept_emp asselect a.deptno,a.dname,b.empno,b.enamefrom dept a,emp bwhere a.deptno=b.deptno;insert into view_dept_emp values(30,'2012','JACK');create or replace trigger tr_instead_of_dept_empinstead of insert on view_dept_empfor each rowdeclarev_temp number;beginselect count(*) into v_temp from dept where deptno=:NEW.deptno;IF v_temp=0 theninsert into dept(deptno,dname) values(:new.deptno,:new.dename);END IF;select count(*) into v_temp from emp where empno=:NEW.empno;IF v_temp=0 theninsert into emp(empno,ename) values(:new.empno,:new.ename);END IF;end;/conn sys/tiger as sysdbacreate table event_table(event varchar2(30),event_time date)create or replace trigger tr_startupafter startup on databasebegininsert into event_table values(ora_sysevent,SYSDATE);end;/create or replace trigger tr_shutdownbefore shutdow on databasebegininsert into event_table values(ora_sysevent,sysdate);end;/create table log_table(username varchar2(30),logon_time date,logonff_time date,Ip varchar2(20));create or replace trigger tr_logonafter logon on databasewhen (ora_login_user not in ('SYS','SYSMAN'))begininsert into log_table(username,logon_time,ip)values(ora_login_user,ora_client_ip_address);end;/create or replace trigger tr_loginffbefore logoff on databasewhen (ora_login_user not in ('SYS',logoff_time,ora_client_ip_address);end;/create table event_ddl(event varchar2(20),username varchar2(10),owner varchar2(10),objname varchar2(20),objtype varchar2(10),ddl_time date);create or replace trigger tr_ddlafter ddl on scott.schemabegininsert into event_ddl values(ora_sysevent,ora_login_user,ora_dict_obj_owner,ora_dict_obj_name,ora_dict_obj_type,SYSDATE);end;/end;/)
