--1、編寫一個程序塊從emp表中顯示為'SMITH'的雇員的薪水和職位
declare?
v_job emp.job%type;
v_sal emp.sal%type;
begin
select job,sal into v_job,v_sal from emp where ename='SMITH';
dbms_output.put_line('SMITHD的職位是:'||v_job||'薪金:'||v_sal);
end;
--2、編寫一個程序塊，接受用戶輸入一個部門號，從dept表中顯示該部門的名稱與所在地
declare
v_dname dept.dname%type;
v_location dept.loc%type;
begin
select dname,loc into v_dname,v_location from dept
where deptno=&dnamber;
dbms_output.put_line('部門名稱:'||v_dname||'部門地址：'||v_location);
end;
--3、編寫一個程序塊，利用%type屬性，接受一個雇員號，從emp表中顯示該雇員的整體薪金（即：薪水+傭金）
declare
v_empno emp.empno%type;
v_sumsal emp.sal%type;
begin
select 12*(sal + nvl(comm,0)) into v_sumsal from emp where empno=&v_empno;
dbms_output.put_line('該員工的總的薪金：'||v_sumsal);
end;
select * from emp;
--4、編寫一個程序塊，利用%rowtype屬性，接受一個雇員號，從emp表中顯示該雇員的整體薪水（薪水+傭金）
declare
v_emp emp%rowtype;
begin
select * into v_emp from emp where empno=&雇員號;
dbms_output.put_line(v_emp.sal+nvl(v_emp.comm,0));
end;
--5、 5.某公司要根據雇員的職位來加薪，公司決定按下列加薪結構處理：
? ? ? ? ? ? ?-- Designation ? ?Raise
? ? ? ? ? ? ? -----------------------
? ? ? ? ? ? ?-- Clerk ? ? ? ? ?500
? ? ? ? ? ? ? --Salesman ? ? ? 1000
? ? ? ? ? ? ? --Analyst ? ? ? ?1500
? ? ? ? ? ? ? --Otherwise ? ? ?2000
--編寫一個程序塊,接受一個雇員名，從emp表中實現上述加薪處理。
declare?
? ? v_emp emp%rowtype;
?begin
? ? ? select * into v_emp from emp where ename='&name';
? ? ? if v_emp.job='CLERK' then
? ? ? ? ?update emp set sal=sal+500 where empno=v_emp.empno;
? ? ? elsif v_emp.job='SALESMAN' then
? ? ? ? ?update emp set sal=sal+1000 where empno=v_emp.empno;?
? ? ? elsif v_emp.job='ANALYST' then
? ? ? ? ?update emp set sal=sal+1500 where empno=v_emp.empno;?
? ? ? else ?
? ? ? ? ?update emp set sal=sal+2000 where empno=v_emp.empno; ?
? ? ? end if;
? ? ? commit;
end;
--6、編寫一個程序塊，將emp表中雇員名全部顯示出來


--解法一
declare
cursor emp_cursor is select ename from emp;
v_ename emp.ename%type;
begin
open emp_cursor;
loop
? fetch emp_cursor into v_ename;
exit when emp_cursor%notfound;
dbms_output.put_line(v_ename);
end loop;
close emp_cursor;
end;
--解法二
declare
cursor emp_cursor is select ename from emp;--得到多個cursor
begin
for v_name in emp_cursor
loop
dbms_output.put_line(v_name.ename);--遍歷多個cursor
end loop;
end;


--7、編寫一個程序塊，將emp表中前5人中名字顯示出來
--解法一
declare
cursor emp_cursor is select ename from emp where rownum<6;
begin
for v_name in emp_cursor
loop
dbms_output.put_line(v_name.ename);
end loop;
end;
--解法二
declare
? ?cursor v_cursor is select * from emp;
? ?v_count number :=1;
? ?begin
? ? for v_emp in v_cursor
? ? ? ?loop
? ? ? ? dbms_output.put_line(v_emp.ename);
? ? ? ? v_count := v_count+1;
? ? ? ? exit when v_count>5;
? ? ? ?end loop; ??
? ?end;
----------------------------------異常處理






--8、編寫一個程序塊，接受一個雇員名，從emp表中顯示該雇員的工作崗位和薪水，若輸入的雇員名不存在
--顯示'該雇員不存在'信息
declare?
? ?v_emp emp%rowtype;
? ?my_exception Exception;
?begin
? ?
? ?select * into v_emp from emp where ename='&name';
? ?raise my_exception;
? ?
? ?exception
? ? ? ? ?when no_data_found then
? ? ? ? ? ? ? dbms_output.put_line('該雇員不存在!');
? ? ? ? ?when others then
? ? ? ? ? dbms_output.put_line(v_emp.job||'---'||v_emp.sal);
?end;


--9、接受兩個數相除并且顯示結果，如果第二個數為0，則顯示消息“除數不能為0”。
declare
? v_dividend float;
? v_divisor float;
? v_result float;
? my_exception Exception;
? begin
? ? ? ? v_dividend:=&被除數;
? ? ? ? v_divisor:=&除數;
? ? ? ? v_result:=v_dividend/v_divisor;
? ? ? ? raise my_exception;
? ? ? ? exception
? ? ? when my_exception then
? ? ? ? dbms_output.put_line(v_result);
? ? ? when others then
? ? ? ? dbms_output.put_line('除數不能為0');
? end;

--二.聲明和使用游標
-- 使用游標屬性?
-- 使用游標For循環工作
-- 聲明帶參數的游標
--（使用FOR UPDATE OF和CURRENT OF子句工作）




--1、通過使用游標來實現dept表中的部門名稱
declare
cursor dept_cursor is select dname from dept;
begin
for one_dept_cursor in dept_cursor
loop
dbms_output.put_line(one_dept_cursor.dname);
end loop;
end;


select dname from dept;


--2、使用for循環，接受一個部門號，從emp表中顯示該部門的所有雇員的姓名，工作和薪水
declare
cursor emp_cursor is select ename,job,sal from emp where deptno=&dno;
begin
for one_emp_cursor in emp_cursor
loop?
dbms_output.put_line(one_emp_cursor.ename||one_emp_cursor.job||one_emp_cursor.sal);
end loop;
end;


--3、使用帶參數的游標（實現第2題）
declare
cursor emp_cursor(dno number) is select ename,job,sal from emp where deptno=dno;
v_deptno number(10);


begin
v_deptno:= &部門號;
for one_emp_cursor in emp_cursor(v_deptno)
loop
dbms_output.put_line(one_emp_cursor.ename||one_emp_cursor.job||one_emp_cursor.sal);
end loop;
end;?
--4、編寫一個pl/sql程序塊，從emp表中名字以'A'或'S'開始的所有庫員按他們基本薪水的 10%100給他們加薪
declare
cursor emp_cursor is select * from emp where ename like 'A%' or ename like 'S%';
begin
? ?for one_cursor in emp_cursor
loop
update emp set sal=sal*1.1 where ename=one_cursor.ename;
-- dbms_output.put_line(one_cursor.ename);
end loop;
end;


select * from emp where ename like 'A%' or ename like 'S%';
select * from emp;




--5、emp表中對所有雇員按他們基本薪水的10%給他們加薪，如果增加后的薪水大于5000，則取消加薪


declare
cursor emp_cursor is select * from emp;
begin
? ? for one_emp in emp_cursor
loop
if one_emp.sal*1.1<5000
then update emp set sal=sal*1.1 where empno=one_emp.empno;
end if;
end loop;
end;


select * from emp where sal*1.1<5000;


--三，創建PL/SQL記錄和PL/SQL表
-- ? 創建過程
-- ? 創建函數


--3、創建一個過程，能像dept表中添加一個新紀錄（in參數）
create or replace procedure insert_dept(dept_no in number,dept_name in varchar2,dept_loc in varchar2)
is
begin
insert into dept values(dept_no,dept_name,dept_loc);
end;
--調用存儲過程：
declare
begin
insert_dept(50,'人事部','南京');
end;
select * from emp;
select * from dept;
--4、創建一個過程，從emp表中帶入雇員的姓名，返回該雇員的薪水值(out參數)，然后調用過程
create or replace procedure
?find_emp3(emp_name in varchar2,emp_sal out number)
?is
? ? ?v_sal number(5);
?begin
? ? ? select sal into v_sal from emp where ename = emp_name;
? ? ? emp_sal:=v_sal;
?end;
?
declare
mysal number;
begin
? find_emp3('SMITH',mysal);
dbms_output.put_line(mysal);
end;
--5、編寫一個程序塊，接受一個雇員號與一個百分數，從emp表中將該雇員的薪水增加輸入的百分比。


create or replace procedure
? ?update_sal(emp_no in number,parsent in float)
?is
? ?begin
? ? ?update emp set sal=sal+sal*parsent where empno=emp_no;
? ?end;


begin
? update_sal(7937,0.5);
?end;


select * from emp;


--7、創建一個函數，他以部門號作為參數傳遞并且使用函數顯示那個部門名稱與位置，然后調用此函數


create or replace function
? find_dept(dept_no number)
? return dept%rowtype
? is
? ?v_dept dept%rowtype;
? ?begin
? ? select * into v_dept from dept where deptno=dept_no;
? ? return v_dept;
? end;

--調用
declare
? v_dept dept%rowtype;
? begin
? ? ? ? ?v_dept:=find_dept(50);
? ? ? ? ?dbms_output.put_line(v_dept.dname||'---'||v_dept.loc);
?end;




--四，創建程序包
? ?-- 創建程序件
? ?-- 創建觸發器
?
?
--6、創建一個語句級別觸發器，不允許用戶在"Sundays"使用emp表
create or replace trigger control_emp
before update or delete or insert on emp


begin
if to_char(sysdate,'DY','nls_date_language=AMERICAN') in ('SUM')?
then raise_application_error(-20001,'不允許在星期天操作emp表');
end if;
end;

select to_char(sysdate,'day','nls_date_language=AMERICAN') from dual;--英文星期
select to_char(sysdate,'day') from dual;--中文星期
select to_char(sysdate,'hh24:mi') from dual;
?if to_char(sysdate,'day') in ('星期六','星期日') or?
?to_char(sysdate,'hh24:mi') not between '08:30' and '18:00'




--5.創建一個行級別觸發器，停止用戶刪除雇員名為"SMITH"的記錄。
? ?create or replace trigger delete_smith
? ? ?before delete on emp?
? ? ?for each row
? ? ?when (old.ename='SMITH')
? ? ?begin
? ? ?raise_application_error(-20001,'不能刪除該條信息!');
? ? ?end;
?
?--4.創建一個行級別觸發器，將從emp表中刪除的記錄輸入到ret_emp表中。
create or replace trigger delete_emp
? ? ? after delete on emp?
? ? ?for each row
? ? ? begin
? ? ? ?insert into ret_emp values(:old.empno,:old.ename,:old.job,
? ? ? ? ?:old.mgr,:old.hiredate,:old.sal,:old.comm,:old.deptno);
? ? ? ?end;
?


--1.創建在dept表中插入和刪除一個記錄的數據包，它且有一個函數(返回插入或刪除的部門名稱)和兩個過程。
然后調用包。
create or replace package pack_1
?is
? procedure find_emp(emp_no in number,emp_name out varchar2);
? procedure find_emp1(emp_name in varchar2,emp_no out number);
? function find_dname(dept_no number)
? return varchar2;
? end pack_1;
? ??
create or replace package body pack_1
is
?function find_dname(dept_no number)
?return varchar2
is?
?v_dname varchar2(20);
?begin?
? select dname into v_dname from dept where deptno=dept_no;
? retrun v_dname;
? end;
end pack_1;


--調用包：
declare
? v_dname varchar2(20);
? begin
? ?v_dname:=pack_1.find_dname(50);
? ?dbms_output.put_line(v_dname);
? ?end;

總結

以上是生活随笔為你收集整理的oracle数据库block、tigger、function、package的全部內容，希望文章能夠幫你解決所遇到的問題。

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