視頻課：https://edu.csdn.net/course/play/7940

創建表空間的語法是：

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
(
ID number(10) primary key,
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
(
ID number(10) primary key,
shopId varchar2(20) unique not ?null,
shopName varchar2(20) not null,
price number(6,2) not null,
shopTypeId number(10) not null,
manufacturingDate date not null,
constraint ck_price check(price>0),
constraint fk_shopTypeId foreign key(shopTypeId)
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,1,'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,2,'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,3,'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,last_name,salary,department_id?
from employees
where salary>all(select salary from employees where department_id=20);


select first_name,last_name,salary,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,last_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.first_name||a.last_name,a.hire_date,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
select deptno into v_deptno from dept where dname=&dname;
select sum(sal) into v_totalSal from emp where deptno=v_deptno;
dbms_output.put_line('總工資為:'|| v_totalSal);
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('輸入的部門編號不存在！');
END;
/


DECLARE
?v_empno number(4);
?v_ename varchar(10);
?v_hiredate date;
BEGIN
v_empno:=&empno;
vselect ename,hiredate into v_ename,v_hiredate from emp where empno=v_empno;
dbms_output.put_line('姓名:'|| v_ename);
dbms_output.put_line('出生年月:'|| to_char(v_hiredate,'yyyy"年"mm"月"dd"日"');
EXCEPTION
WHEN NO_DATA_FOUND THEN
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
v_area:=v_pi*v_r;
DBMS_OUTPUT.PUT_LINE('圓周率:'|| v_pi);
DBMS_OUTPUT.PUT_LINE('半徑:' || v_r);
DBMS_OUTPUT.PUT_LINE('面積:' || v_area);
END;
/


DECLARE
v_sal number(7,2);
v_comm number(7,2);
v_totalSal number(7,2);
BEGIN
select sal,comm into v_sal,v_comm?
from emp where empno=&empno;


v_comm:=NVL(v_comm,0);


v_totalSal:=v_sal+v_comm;


DBMS_OUTPUT.PUT_LINE('基本工資:'|| v_sal);
DBMS_OUTPUT.PUT_LINE('補助:'|| v_comm);
DBMS_OUTPUT.PUT_LINE('總工資:'|| v_totalSal);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('請輸入正確的雇員信息!');
END;
/




DECLARE
v_ename emp.ename%type;
v_sal emp.sal%type;
c_tax_rate constant number(3,2):=0.02;
v_tax_sal v_sal%type;
BEGIN
select ename,sal into v_ename,v_sal from emp where empno=&empno;
v_tax_sal:=v_sal*c_tax_rate;
DBMS_OUTPUT.PUT_LINE('雇員名:'|| v_ename);
DBMS_OUTPUT.PUT_LINE('雇員工資:'|| v_sal);
DBMS_OUTPUT.PUT_LINE('雇員所得稅:'|| v_tax_sal);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('請輸入正確的雇員信息!');
END;
/


DECLARE
v_emp_record detpt%rowtype;
BEGIN
select * frm v_emp_record from dept where deptno=&deptno;
DBMS_OUTPUT.PUT_LINE('部門編號:'|| v_emp_record.deptno);
DBMS_OUTPUT.PUT_LINE('部門名稱:'|| v_emp_record.dname);
DBMS_OUTPUT.PUT_LINE('部門地區:'|| v_emp_record.loc);
END;
/


DECLARE
?TYPE EMP_RECORD_TYPE IS RECORD
?(
ename emp.ename%type,
sal emp.sal%type,
comm emp.comm%type,
total_sal sal%type
?)
?v_emp_record EMP_RECORD_TYPE;
?BEGIN
select ename,sal,NVL(comm,0),sal+NVL(comm,0) into v_emp_record
from emp where empno=7521;
DBMS_OUTPUT.PUT_LINE('雇員名:'|| v_emp_record.ename);
DBMS_OUTPUT.PUT_LINE('工資:'|| v_emp_record.sal);
DBMS_OUTPUT.PUT_LINE('資金:'|| v_emp_record.comm);
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
v_emp_empnos(0):=7369;
v_emp_empnos(1):=7521;
v_emp_empnos(2):=7566;


select ename into v_emp_ename(0)?
from emp where empno=v_emp_empnos(0);


select ename into v_emp_ename(1)?
from emp where empno=v_emp_empnos(1);


select ename into v_emp_ename(2)?
from emp where empno=v_emp_empnos(2);


dbms_output.put_line('雇員編號:'||v_emp_empnos(0)||' 雇員名:'||v_emp_enames(0));
dbms_output.put_line('雇員編號:'||v_emp_empnos(1)||' 雇員名:'||v_emp_enames(1));
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
select deptno,dname into v_dept_table(0).deptno,v_dept_table(0).dname
from dept where deptno=10;
select deptno,dname into v_dept_table(1).deptno,v_dept_table(1).dname
from dept where deptno=20;
select deptno,dname into v_dept_table(2).deptno,v_dept_table(2).dname
from dept where deptno=30;


dbms_output.put_line('部門編號 ? ?部門名稱');


dbms_output.put_line(v_dept_table(0).deptno ||' ? ? ? ? '||v_dept_table(0).dname);
dbms_output.put_line(v_dept_table(1).deptno ||' ? ? ? ? '||v_dept_table(1).dname);
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
v_dept_names_varray(1):='ACCOUNTING';
v_dept_names_varray(2):='RESEARCH';
v_dept_names_varray(3):='SALES';
dbms_output.put_line('===部門名稱===');
dbms_output.put_line(v_dept_names_varray(1));
dbms_output.put_line(v_dept_names_varray(2));
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
update emp set comm=v_dept_avgSal*0.1
where empno=v_emp.empno;
? else
if v_emp.sal<v_dept_avgSal then
update emp set comm=comm+v_dept_avgSal*0.1
where empno=v_emp.empno;
else
update emp set comm=comm+v_emp.sal*0.1
where empno=v_emp.empno;
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
when no_data_found then
dbms_output.put_line('該雇員不存在');
end;
/


declare
?v_deptno number(2):=&deptno;
begin
case v_deptno
when 10 then
?update emp set comm=
?case when comm is null then 100 else comm*1.1 end
?where deptno=v_deptno;
when 20 then
? ? ? ? ? update emp set comm=
?case when comm is null then 200 else comm*1.2 end
?where deptno=v_deptno;
when 30 then
?update emp set comm=
?case when comm is null then 300 else comm*1.3 end
?where deptno=v_deptno;
else
?dbms_output.put_line('不存在該部門!');
end case;
end;
/


declare
v_empno number(4):=&empno;
v_sal number(7,2);
begin
select ?sal into v_sal from emp?
where empno=v_empno;
case
when v_sal<2000 then
dbms_output.put_line('一級工資');
when v_sal>=2000 then
dbms_output.put_line('二級工資');
when v_sal>=3000 then
dbms_output.put_line('三級工資');
when v_sal>=4000 then
dbms_output.put_line('四級工資');
else
dbms_output.put_line('五級工資');
? ? ? ? end case;
exception
when no_data_found then
dbms_output.put_line('請輸入正確的雇員編號!');
end;
/


create table rnd_temp_table
(
ID NUMBER(4) primary key,
value varchar2(10) not null
)


declare
type rnd_varray_type is varray(4) of varchar2(10);
r_rnd_varray run_varray_type=rnd_varray_type('DALLAS','CHICAGO','BOSTON','NEWYORK');
v_loop number(2):=1;
v_index number(1);
begin
loop
if v_loop=6 then
exit;
end if
v_index:=floor(dbms_random.value(1,5));
insert into rnd_temp_table values(v_loop,v_rnd_varray(v_index));
v_loop:=v_loop+1;
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,500,40);
insert into tb_stock values(3,'電腦',50,15,100,20);
insert into tb_stock values(4,'手機',300,15,600,10);


declare
v_stock tb_stock%rowtype;
v_n number(4):=0;
begin
v_stock.ID:=&ID;


select stock,day_sales_volume,min_stock
into v_stock.stock,v_stock.day_sales_volume,v_stock.min_stock
from tb_stock
where ID=v_stock.ID;


while v_stock.stock>v_stock.min_stock loop
v_stock.stock:=v_stock.stock-v_stock.day_sales_volume;
v_n:=v_n+1;
end loop;


dbms_output.put_line('商品編號:'|| v_stock.ID);
dbms_output.pub_line('采購期限:'|| v_n);
exception
when no_data_found then
dbms_output.put_line('請輸入正確的商品編號!');
end;
/


declare
type dept_table_type is table of dept%rowtype
index by binary_integer;
v_dept_table dept_table_type;
begin
select deptno,dname into v_dept_table(0).deptno,v_dept_table(0).dname
from dept where deptno=10;


? ? ? ? select deptno,dname into v_dept_table(1).deptno,v_dept_table(1).dname
from dept where deptno=20;


? ? ? ? select deptno,dname into v_dept_table(2).deptno,v_dept_table(2).dname
from dept where deptno=30;


dbms_output.put_line('部門編號 ? ?部門名稱');
for i in 0..v_dept_table.COUNT-1 loop
dbms_output.put_line(v_dept_table(i).deptno || ' ? ? ? ? ?' || v_dept_table(i).dname);
end loop;


end;
/


declare
v_sal emp.sal%type;
v_name emp.ename%type;
begin
select sal,ename into v_sal,v_ename
from emp where empno=&empno;


if v_sal<3000 then
update emp set comm=sal*0.1 where ename=v_ename;
else
null;
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
ept_no_emp exception;
begin
update emp set comm=
case when comm is null then 50 else comm*1.0 end
where empno=&empno;




if sql%notfound then
raise ept_no_emp;
else
dbms_output.put_line('該雇員的獎金已經更新！');
end if;
exception
when ept_no_emp then
dbms_output.put_line('該雇員不存在!');
end;
/


DECLARE
TYPE CREATE_TABLE_RECORD IS RECORD
(
field_name varchar2(15),
field_type varchar2(15),
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
v_create_table_name:='STVD';
v_grant_user:='hr';
v_grant_authority:='select';
v_dynamic_sql_table(0).field_name:='sid';
v_dynamic_sql_table(0).field_type:='varchar2(10)';
v_dynamic_sql_table(0).field_explain:='primary key';
v_dynamic_sql_table(1).field_name:='sname';
v_dynamic_sql_table(1).field_type:='varchar2(10)';
v_dynamic_sql_table(1).field_explain:='not null';
v_dynamic_sql_table(2).field_name:='sclass';
v_dynamic_sql_table(2).field_type:='varchar2(10)';
v_dynamic_sql_table(2).field_explain:='not null';

v_dynamic_ddl_sql:='create table '|| v_create_table_name ||chr(13)||'('||chr(13);


for i in 0..v_dynamic_sql_table.COUNT-1 loop
v_dynamic_ddl_sql:=v_dynamic_ddl_sql ||
v_dynamic_sql_table(i).field_name ||' '||
v_dynamic_sql_table(i).field_type ||' '||
v_dynamic_sql_table(i).field_explain ||','|| chr(13);
end loop;


v_dynamic_ddl_sql:=substr(v_dynamic_ddl_sql,0,length(v_dynamic_ddl_sql)-2);


v_dynamic_ddl_sql:=v_dynamic_ddl_sql||chr(13)||')';
v_dynamic_dcl_sql:='grant '|| v_grant_authority||' on '||v_create_table_name||' to '||v_grant_user;
execute immediate v_dynamic_ddl_sql;
execute immediate v_dynamic_dcl_sql;
END;
/


DECLARE
v_dynamic_sql VARCHAR2(100);
BEGIN
v_dynamic_sql:='UPDATE emp SET sal=sal*(1+:percent/100.0) where deptno=:deptno';
execute immediate v_dynamic_sql using &percent,&deptno;
END;
/


declare
v_dynamic_sql varchar2(100);
v_sal number(7,2);
v_empno number(4):=&empno;
v_percent number(2):=&percent;
begin
select sal into v_sal from emp where empno=v_empno;
dbms_output.put_line('更新前工資：'||v_sal);
v_dynamic_sql:='UPDATE emp SET sal=sal*(1+:percent/100.0)
where empno=:empno returning sal into :sal';
execute immediate v_dynamic_sql using v_percent,v_empno RETURNING INTO v_sal;
dbms_output.put_line('增長率:'||v_percent||'%');
dbms_output.put_line('新工資:'|| v_sal);
end ;
/


declare
v_dynamic_sql varchar2(100);
emp_record emp%ROWTYPE;
begin
v_dynamic_sql:='select * from emp where empno=:empno';
execute immediate v_dynamic_sql into emp_record using &empno;
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
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','bb');
insert into tb_test values('aa','cc');
insert into tb_test values('bb','cc');
select * from tb_test


create or replace procedure proc_del_dup_rec
as
begin
?delete tb_test a where a.ROWID=(
select max(rowid) from tb_test b
where
a.a=b.a and a.b=b.b
);
end;
/


create or replace procedure proc_transit_station(
v_start_station tb_station.station_name%type,
v_end_station tb_station.station_name%type,
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
select station_name bulk collect into v_station_name_table
from tb_station?
where line_name=v_line_name?
and forder>=v_start_forder and
forder<=v_end_forder;
?else
select station_name bulk collect into v_station_name_table
from tb_station
where line_name=v_line_name?
and forder<=v_start_forder and
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
v_station_line:=v_station_line || v_station_name_table(i)||'->';
? ?end loop;
? ?v_station_line:=Substr(v_station_line,0,length(v_station_line)-2);
? ?
? ?dbms_output.put_line(v_station_line);


?exception
? when no_data_found then
dbms_output.put_line('請輸入正確的公交車次及公交線路!');
end;
/


call proc_transit_station('常青路','武勝路','536');
call proc_transit_station('常青路','武勝路');


create or replace procedure proc_query_emp
(?
param_empno number,
param_ename out varchar2,
param_salary out number
)
as
begin
select ename,sal into param_ename,param_salary from emp?
where empno=param_empno;
exception
when no_data_found then
raise_application_error(-20001,'該雇員不存在!');
end;
/


DECLARE
v_empno emp.empno%type:=7788;
v_ename emp.ename%type;
v_sal emp.sal%type;
BEGIN
proc_query_emp(v_empno,v_ename,v_sal);
dbms_output.put_line(v_ename||' '||v_sal);
END;


create or replace procedure proc_compute
(?
param_num1 in out number,
param_num2 in out number
)
as
?v1 number,
?v2 number
begin
v1:=param_num1/param_num2;
v2:=mod(param_num1,param_num2);
param_num1:=v1;
param_num2:=v2;
end;
/


declare
v_num1 number(2):=10;
v_num2 number(2):=3;
begin
proc_compute(v_num1,v_num2);
dbms_output.put_line(v_num1);
dbms_output.put_line(v_num2);
end;
/


create or replace procedure proc_add_dept
(
param_deptno number,
param_dname varchar2,
param_loc varchar2:=null
)
as
begin
insert into dept values(param_deptno,param_dname,param_loc);
exception
when dup_val_on_index then
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
v_user varchar2(100);
begin
select username into v_user from user_users;
return v_user;
end;
/


declare
v_user varchar2(100);
begin
v_user:=fun_get_user;
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
select sal into v_sal from emp
where
upper(param_ename)=upper(ename);
exception
when no_data_found then
raise_application_error(-20000,'該雇員不存在');
end;
/


declare
v_ename emp.ename%type:='&v_ename';
begin
dbms_output.put_line(fun_get_sal(v_ename));
end;
/


create or replace function fun_get_emp_info
(
param_ename varchar2,
param_dname out varchar2
)
return varchar2
as
v_ejob emp.job%type;
begin
select a.job,b.dname into v_ejob,param_dname
from emp a, dept b
wher a.deptno=b.deptno and
upper(a.ename)=upper(param_ename);
return v_ejob;
exception
when no_data_found then
raise_application_error(-20000,'該雇員不存在!');
end;
/


delcare
v_ename varchar2(20):='&v_ename';
v_dname varchar2(20);
v_ejob varchar2(20);
begin
v_ejob:=fun_get_emp_info(v_ename,v_dname);
dbms_output.put_line('雇員名稱'|| v_ename);
dbms_output.put_line('部門名稱'|| v_dname);
dbms_output.put_line('雇員崗位'|| v_ejob);
end;
/


create or replace function fun_compute?
param_num1 number,
param_num2 in out number
)
return number;
as
?v1 number
begin
v1:=param_num1/param_num2;
param_num2:=mod(param_num1,param_num2);
return v1;
end;
/


declare
v_num1 number(2):=10;
v_num2 number(2):=3;
v_result number(2);
begin
v_result:=fun_compute(v_num1,v_num2);
dbms_output.put_line(v_num2);
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,created,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
g_deptno number(3):=30;
procedure pro_add_employee(
param_empno number,
param_ename varchar2,
param_sal number,
param_deptno number:=g_deptno
);
function fun_get_sal(param_empno number) return number;
end emp_package;
/


create or replace package body emp_package?
as
function fun_validate_deptno(param_deptno number)
return boolean
as
v_temp number;
begin
select 1 into v_temp from dept?
where deptno=param_deptno;
return true;
exception
when no_data_found then
return false;
end;


procedure pro_add_employee
(
param_empno number,
param_ename varchar2,
param_sal number,
param_deptno number:=g_deptno
)
as
if fun_validate_deptno(param_deptno) then
insert into emp(empno,ename,sal,deptno)
values(param_empno,param_ename,param_sal,param_deptno);
else
raise_application_error(-20001,'不存在部門');
end if;
exception
when dup_val_on_index then
raise_application_error(-20002,'該雇員編號已經傳值!');
end;


function fun_get_sal(param_empno number) return number
as
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno=param_empno;
return v_sal;
exception
when no_data_found then
raise_application_error(-20003,'該雇員不存在!');
end;
end emp_packgae;
/


select text from user_source
where name='EMP_PACKAGE' AND TYPE='PACKAGE';

---************************************************

DECLARE
cursor emp_cursor IS
Select ename,sal from emp ?where deptno=30;
v_ename emp.ename%type;
v_sal emp.sal%type;
BEGIN
OPETN emp_cursor;
LOOP
FETCH 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;
/


DECLARE
CURSOR emp_cursor IS
SELECT 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;
BEGIN
OPEN 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 LOOP
dbms_output.put_line(v_ename_table(i).ename || ' ? '||v_ename_table(i).sal);
END LOOP;
END;
/


DECLARE
CURSOR emp_cursor IS
SELECT ename FROM emp WHERE deptno=10;
TYPE ENAME_TABLE_TYPE IS TABLE OF VARCHAR2(10);
v_ename_table ENAME_TABLE_TYPE;
BEGIN
IF NOT emp_cursor%ISOPEN THEN
OPEN emp_cursor;
END IF:
FETCH emp_cursor BULK COLLECT INTO v_ename_tablel;
DBMS_OUTPUT.PUT_LINE('提取的總計行數：'|| emp_cursor%ROWCOUNT);
CLOSE emp_cursor;
END:
/


DELCARE
CURSOR emp_cursor IS SELECT ename,sal FROM emp WHERE deptno=10;
emp_record emp_cursor%ROWTYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH 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;
/


DECLARE
CURSOR emp_cursor(param_dept NUMBER) IS?
SELECT ename,sal FROM emp WHERE deptno=param_dept;
emp_record emp_cursor%ROWTYPE;
BEGIN
OPEN emp_cursor(10);
LOOP
FETCH 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;
/


DECLARE
CURSOR emp_cursor IS?
SELECT ename,sal FROM emp FOR UPDATE;
v_emp_row emp_cursor%ROWTYPE;
v_update_emp_count NUMBER(2):=0;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_emp_row;
EXIT WHEN emp_cursor%NOTFOUND;
IF v_emp_row.sal<2000 THEN
UPDATE 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;
/






DECLARE
CURSOR emp_cursor IS?
SELECT deptno FROM emp FOR UPDATE;
v_emp_row emp_cursor%ROWTYPE;
v_update_emp_count NUMBER(2):=0;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_emp_row;
EXIT WHEN emp_cursor%NOTFOUND;
IF v_emp_row.deptno=30 THEN
DELETE ?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;
/
?
DECLARE
CURSOR emp_cursor IS?
SELECT ename,sal FROM emp FOR UPDATE NOWART;
v_emp_row emp_cursor%ROWTYPE;
v_update_emp_count NUMBER(2):=0;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_emp_row;
EXIT WHEN emp_cursor%NOTFOUND;
IF v_emp_row.sal<2000 THEN
UPDATE 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;
/


DECLARE
CURSOR emp_cursor IS SELECT ename,sal FROM emp;
BEGIN
FOR emp_row IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE('第'|| emp_cursor%ROWCOUNT || '個雇員:' || emp_row.ename);
END LOOP;
END;
/


BEGIN
FOR emp_row IN (SELECT ename,sal FROM emp) LOOP
DBMS_OUTPUT.PUT_LINE(emp_row.ename);
END LOOP;
END;
/


DECLARE
TYPE EMP_CURSOR_TYPE IS REF CURSOR;
emp_cursor EMP_CURSOR_TYPE;
emp_row emp%ROWTYPE;
BEGIN
OPEN emp_cursor FOR
SELECT * FROM emp WHERE deptno=30;
LOOP
FETCH 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
)AS
BEGIN
OPEN param_resultset FOR
SELECT ename,sal FROM emp WHERE deptno_param_deptno;
END;
/


DECLARE
TYPE 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;
BEGIN
proc_getEmpsByDeptno(v_deptno,v_emp_rows);
LOOP
FETCH 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_refcursor
as
param_resultset SYS_REFCURSOR;
begin
open param_resultset for
select ename,sal from emp where extract(year from hiredate)=param_HireDateYear;
return param_resultset;
end;
/


declare
type emp_record_type is record
(
ename varchar2(10),
sal number(7,2)
);
v_emp_rows SYS_REFCURSOR;
v_hireDateYear NUMBER(4):=1981;
v_emp_row EMP_RECORD_TYPE;
begin
v_emp_rows:=fun_getEmpsByHireDateYear(v_hireDateYear);
loop
fetch v_emp_rows intoo 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;
/


declare
v_empno number(4):=7700;
begin
update emp set empno=v_empno where empno=v_empno;
if sql%found then
dbms_output.put_line('存在該雇員');
else
dbms_output.put_line('不存在該雇員');
end if;
end;
/


declare
v_deptno number(2):=20;
v_rows_count number;
begin
update emp set sal=sal+100 where deptno=v_deptno;
v_row_count:=sql%rowcount;
if v_rows_count=0 then
dbms_output.put_line('沒有雇員被更新!');
else
dbms_output.put_line('共有'||v_rows_count || '個雇員被更新了！');
end if;
end;
/


create or replace trigger tr_sec_emp
before
insert or update or delete
on emp
begin
if to_char(sysdate,'Dy') in ('星期六','星期日') then
raise_application_error(-20000,'不能在休息日改變雇員信息');
end if;
end;
/


delete emp where empno= 7788;


create or replace trigger tr_sec_emp
before
insert or update or delete
begin
if to_char(sysdate,'Dy') in ('星期六','星期日') then
case
when updating then
raise_application_error(-20001,'不能在休息日更新雇員信息');
when deleting then
raise_application_error(-20002,'不能在休息日刪除雇員信息');
when inserting then
raise_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 sequence?
increment by 1
start with 1
maxvalue 9999999
cache 10
cycle;


create or replace trigger tr_sec_emp
after
insert ?or update or delete
on emp
declare
v_temp number;
begin
select count(*) into v_temp from audit_table
where tb_name='EMP';
if v_temp=0 then?
insert into audit_table values(seq_audit.nextnval,'EMP',0,0,0,SYSDATE,null);
end if;
case
when inserting then
update audit_table set ins=ins+1,endtime=sysdate
where tb_name='EMP';
when updating then
update audit_table set upd=upd+1,endtime=sysdate
where tb_name='EMP';
when deleting then
update audit_table set del=del+1,endtime=sysdate
where tb_name='EMP';
end case
end;
/


create or replace tigger tr_emp_sal
before update of sal on emp
for each row
begin
if :NEW.sal<:OLD.sal then
raise_application_error(-20000,'新工資不能小于原有工資‘）；
end if;
end;
/


create table audit_sal_change(
ID number primary key,
ename varchar2(20) not null,
oldsal number(7,2) not null,
newsal number(7,2) not null,
auditTime date
);


create or replace trigger tr_sal_change
after update oof sal on emp
for each row
begin
insert into audit_sal_change values(seq_audit_sal_change.NEXTVAL,:OLD.ename,:OLD.sal,:NEW.sal,sysdate);
end;
/


create or replace trigger tr_sal_change
after update of sal on emp
for each row
when (OLD.job='MANAGER')
begin
insert into audit_sal_change values(seq_audit_sal_change.NEXTVAL,:OLD.ename,:OLD.sal,:NEW.sal,sysdate);
end;
/


create or replace view view_dept_emp as
select a.deptno,a.dname,b.empno,b.ename
from dept a,emp b
where a.deptno=b.deptno;


insert into view_dept_emp values(30,'ADMIN','2012','JACK');


create or replace trigger tr_instead_of_dept_emp
instead of insert on view_dept_emp
for each row
declare
v_temp number;
begin
select count(*) into v_temp from dept where deptno=:NEW.deptno;
IF v_temp=0 then
insert 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 then
insert into emp(empno,ename) values(:new.empno,:new.ename);
END IF;
end;
/


conn sys/tiger as sysdba


create table event_table
(
event varchar2(30),
event_time date
)


create or replace trigger tr_startup
after startup on database
begin
insert into event_table values(ora_sysevent,SYSDATE);
end;
/


create or replace trigger tr_shutdown
before shutdow on database
begin
insert 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_logon
after logon on database
when (ora_login_user not in ('SYS','SYSMAN'))
begin
insert into log_table(username,logon_time,ip)
values(ora_login_user,SYSDATE,ora_client_ip_address);
end;
/


create or replace trigger tr_loginff
before logoff on database
when (ora_login_user not in ('SYS','SYSMAN'))
begin
insert into log_table(username,logoff_time,ip)
values(ora_login_user,SYSDATE,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_ddl
after ddl on scott.schema
begin
insert into event_ddl values(
ora_sysevent,
ora_login_user,
ora_dict_obj_owner,
ora_dict_obj_name,
ora_dict_obj_type,
SYSDATE
);
end;
/
end;
/
)

總結

以上是生活随笔為你收集整理的Oracle代码大全.从入门到熟练的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。