pl_sql

PL/SQL����

���� www.atguigu.com��

׼������:
set serveroutput on

hellowrold ����

begin
dbms_output.put_line('hello world');
end;

[�﷨��ʽ]
--declare
--�����ı��������͡��α�
begin
--�����ִ�в��֣�������java���main()������
dbms_output.put_line('helloworld');
--exception
--���begin���г��ֵ��쳣���ṩ�����Ļ���
--when .... then ...
--when .... then ...
end;


					�����﷨

1.ʹ��һ������

declare
--����һ������
v_name varchar2(25);
begin
--ͨ�� select ... into ... ���Ϊ������ֵ
select last_name into v_name
from employees
where employee_id = 186;

-- ��ӡ������ֵ
dbms_output.put_line(v_name);
end;

2.ʹ�ö������

declare
--��������
v_name varchar2(25);
v_email varchar2(25);
v_salary number(8, 2);
v_job_id varchar2(10);
begin
--ͨ�� select ... into ... ���Ϊ������ֵ
--����ֵ�ı�����SELECT�е�����Ҫһһ��Ӧ
select last_name, email, salary, job_id into v_name, v_email, v_salary, v_job_id
from employees
where employee_id = 186;

-- ��ӡ������ֵ
dbms_output.put_line(v_name || ', ' || v_email || ', ' || v_salary || ', ' || v_job_id);

end;

                   	��¼����

3.��¼����

��¼���;��൱��java�е��� ����¼��������������ı������൱�����������������ԡ�

3.1 �Զ����¼����

declare
--����һ����¼����
type customer_type is record(
v_cust_name varchar2(20),
v_cust_id number(10));

--�����Զ����¼���͵ı���
v_customer_type customer_type;
begin
v_customer_type.v_cust_name := '���»�';
v_customer_type.v_cust_id := 1001;

dbms_output.put_line(v_customer_type.v_cust_name||','||v_customer_type.v_cust_id); --���
end;

3.2 �Զ����¼����

declare
--����һ����¼����
type emp_record is record(
v_name varchar2(25),
v_email varchar2(25),
v_salary number(8, 2),
v_job_id varchar2(10));

--�����Զ����¼���͵ı���
v_emp_record emp_record;
begin
--ͨ�� select ... into ... ���Ϊ������ֵ
select last_name, email, salary, job_id into v_emp_record
from employees
where employee_id = 186;

-- ��ӡ������ֵ
dbms_output.put_line(v_emp_record.v_name || ', ' || v_emp_record.v_email || ', ' ||
v_emp_record.v_salary || ', ' || v_emp_record.v_job_id);
end;

4.ʹ�� %type �����������̬�Ļ�ȡ���ݵ���������

declare
--����һ����¼����
type emp_record is record(
v_name employees.last_name%type,
v_email employees.email%type,
v_salary employees.salary%type,
v_job_id employees.job_id%type);

--�����Զ����¼���͵ı���
v_emp_record emp_record;
begin
--ͨ�� select ... into ... ���Ϊ������ֵ
select last_name, email, salary, job_id into v_emp_record
from employees
where employee_id = 186;

-- ��ӡ������ֵ
dbms_output.put_line(v_emp_record.v_name || ', ' || v_emp_record.v_email || ', ' ||
v_emp_record.v_salary || ', ' || v_emp_record.v_job_id);
end;

5.ʹ�� %rowtype

declare
--����һ����¼���͵ı���
v_emp_record employees%rowtype;
begin
--ͨ�� select ... into ... ���Ϊ������ֵ
select * into v_emp_record
from employees
where employee_id = 186;

-- ��ӡ������ֵ
dbms_output.put_line(v_emp_record.last_name || ', ' || v_emp_record.email || ', ' ||
v_emp_record.salary || ', ' || v_emp_record.job_id || ', ' ||
v_emp_record.hire_date);
end;

6.1 ��ֵ��䣺ͨ������ʵ�ֲ�ѯ���

declare
v_emp_record employees%rowtype;
v_employee_id employees.employee_id%type;
begin
--ʹ�ø�ֵ����λ�������и�ֵ
v_employee_id := 186;

--ͨ�� select ... into ... ���Ϊ������ֵ
select * into v_emp_record
from employees
where employee_id = v_employee_id;

-- ��ӡ������ֵ
dbms_output.put_line(v_emp_record.last_name || ', ' || v_emp_record.email || ', ' ||
v_emp_record.salary || ', ' || v_emp_record.job_id || ', ' ||
v_emp_record.hire_date);
end;

6.2 ͨ������ʵ��DELETE��INSERT��UPDATE�Ȳ���

declare
v_emp_id employees.employee_id%type;

begin
v_emp_id := 109;
delete from employees
where employee_id = v_emp_id;
end;

6.3 �޸�

declare
v_emp_id number(20);
begin
v_emp_id := 100;
update employees
set salary = salary + 100
where employee_id = v_emp_id;
end;


					���̿���


	   �����ж�

7.ʹ�� IF ... THEN ... ELSIF ... THEN ...ELSE ... END IF;

Ҫ��: ��ѯ�� 150�� Ա���Ĺ���, ���乤�ʴ��ڻ���� 10000 ���ӡ 'salary >= 10000';
���� 5000 �� 10000 ֮��, ���ӡ '5000<= salary < 10000'; �����ӡ 'salary < 5000'
(����һ)
declare
v_salary employees.salary%type;
begin
--ͨ�� select ... into ... ���Ϊ������ֵ
select salary into v_salary
from employees
where employee_id = 150;

dbms_output.put_line('salary: ' || v_salary);

-- ��ӡ������ֵ
if v_salary >= 10000 then
dbms_output.put_line('salary >= 10000');
elsif v_salary >= 5000 then
dbms_output.put_line('5000 <= salary < 10000');
else
dbms_output.put_line('salary < 5000');
end if;
(������)
declare
v_emp_name employees.last_name%type;
v_emp_sal employees.salary%type;
v_emp_sal_level varchar2(20);
begin
select last_name,salary into v_emp_name,v_emp_sal from employees where employee_id = 150;

 if(v_emp_sal >= 10000) then v_emp_sal_level := 'salary >= 10000';
 elsif(v_emp_sal >= 5000) then v_emp_sal_level := '5000<= salary < 10000';
 else v_emp_sal_level := 'salary < 5000';
 end if;
 
 dbms_output.put_line(v_emp_name||','||v_emp_sal||','||v_emp_sal);

end;

7+ ʹ�� CASE ... WHEN ... THEN ...ELSE ... END ������������

declare
v_sal employees.salary%type;
v_msg varchar2(50);
begin
select salary into v_sal
from employees
where employee_id = 150;

   --case ����������������
   /*
   case v_sal when salary >= 10000 then v_msg := '>=10000' 
              when salary >= 5000 then v_msg := '5000<= salary < 10000'
              else v_msg := 'salary < 5000'
   end;
   */
 
   v_msg := 
         case trunc(v_sal / 5000)
              when 0 then 'salary < 5000'
              when 1 then '5000<= salary < 10000'
              else 'salary >= 10000'
         end;
   
   dbms_output.put_line(v_sal ||','||v_msg);

end;

8.ʹ�� CASE ... WHEN ... THEN ... ELSE ... END;

Ҫ��: ��ѯ�� 122 ��Ա���� JOB_ID, ����ֵΪ 'IT_PROG', ���ӡ 'GRADE: A';
'AC_MGT', ��ӡ 'GRADE B',
'AC_ACCOUNT', ��ӡ 'GRADE C';
�����ӡ 'GRADE D'

declare
--��������
v_grade char(1);
v_job_id employees.job_id%type;
begin
select job_id into v_job_id
from employees
where employee_id = 122;

   dbms_output.put_line('job_id: ' || v_job_id);
   
   --���� v_job_id ��ȡֵ, ���� case �־�Ϊ v_grade ��ֵ
   v_grade :=  
           case v_job_id when 'IT_PROG' then 'A'
                         when 'AC_MGT' then 'B'
                         when 'AC_ACCOUNT' then 'C'
                         else 'D'
            end;
            
   dbms_output.put_line('GRADE: ' || v_grade);

end;

	   ѭ���ṹ

9.ѭ����ӡ1-100

9.ʹ��ѭ������ӡ 1 - 100.�����ַ�ʽ��

1). LOOP ... EXIT WHEN ... END LOOP
declare
--��ʼ������
v_i number(3) := 1;
begin
loop
--ѭ����
dbms_output.put_line(v_i);
--ѭ������
exit when v_i = 100;
--��������
v_i := v_i + 1;
end loop;
end;

2). WHILE ... LOOP ... END LOOP
declare
--��ʼ������
v_i number(3) := 1;
begin
--ѭ������
while v_i <= 100 loop
--ѭ����
dbms_output.put_line(v_i);
--��������
v_i := v_i + 1;
end loop;
end;

3).
begin
for i in 1 .. 100 loop
dbms_output.put_line(i);
end loop;
end;

10.if,while

10.�ۺ�ʹ�� if, while ���, ��ӡ 1 - 100 ֮�����������

(����: ���ҽ���������Լ��������, 2, 3, 5, 7, 11, 13, ...).
declare
v_flag number(1):=1;
v_i number(3):=2;
v_j number(2):=2;
begin

while (v_i<=100) loop
while v_j <= sqrt(v_i) loop
if (mod(v_i,v_j)=0) then v_flag:= 0;
end if;

      v_j :=v_j +1;
    end loop;
    
if(v_flag=1) then dbms_output.put_line(v_i);
end if;

    v_flag :=1;
    v_j := 2;
    v_i :=v_i +1;

end loop;

end;

(����)ʹ��forѭ��ʵ��1-100֮������������
declare
--���ֵ, ��Ϊ 1 ��������, ������
v_flag number(1) := 0;
begin
for i in 2 .. 100 loop

   v_flag := 1;     
     
   for j in 2 .. sqrt(i) loop
       if i mod j = 0 then
          v_flag := 0;	
       end if;        
   end loop;
   
   if v_flag = 1 then
       dbms_output.put_line(i);
   end if;

end loop;
end;

11.ʹ�� goto

declare
--���ֵ, ��Ϊ 1 ��������, ������
v_flag number(1) := 0;
begin
for i in 2 .. 100 loop
v_flag := 1;

for j in 2 .. sqrt(i) loop
if i mod j = 0 then
v_flag := 0;
goto label;
end if;
end loop;

<

end loop;
end;

11+.��ӡ1����100����Ȼ��������ӡ��50ʱ������ѭ�����������ӡ������
(����һ)
begin
for i in 1..100 loop
dbms_output.put_line(i);
if(i = 50) then
goto label;
end if;
end loop;

  <<label>>
  dbms_output.put_line('��ӡ����');

end;
(������)
begin
for i in 1..100 loop
dbms_output.put_line(i);
if(i mod 50 = 0) then dbms_output.put_line('��ӡ����');
exit;
end if;
end loop;
end;


					�α��ʹ��

12.�α�

��������ֵΪ��������

12.1 ʹ���α�

Ҫ��: ��ӡ�� 80 ���ŵ����е�Ա���Ĺ���:salary: xxx

declare
--1. �����α�
cursor salary_cursor is select salary from employees where department_id = 80;
v_salary employees.salary%type;
begin
--2. ���α�
open salary_cursor;

--3. ��ȡ�α�
fetch salary_cursor into v_salary;

--4. ���α����ѭ������: �ж��α����Ƿ�����һ����¼
while salary_cursor%found loop
dbms_output.put_line('salary: ' || v_salary);
fetch salary_cursor into v_salary;
end loop;

--5. �ر��α�
close salary_cursor;
end;

12.2 ʹ���α�

Ҫ��: ��ӡ�� 80 ���ŵ����е�Ա���Ĺ���: Xxx 's salary is: xxx

declare
cursor sal_cursor is select salary ,last_name from employees where department_id = 80;
v_sal number(10);
v_name varchar2(20);
begin
open sal_cursor;

fetch sal_cursor into v_sal,v_name;

while sal_cursor%found loop
dbms_output.put_line(v_name||'`s salary is '||v_sal);
fetch sal_cursor into v_sal,v_name;
end loop;

close sal_cursor;

end;

13.ʹ���α����ϰ:

��ӡ�� manager_id Ϊ 100 ��Ա���� last_name, email, salary ��Ϣ(ʹ���α�, ��¼����)

declare
--�����α�
cursor emp_cursor is select last_name, email, salary from employees where manager_id = 100;

       --������¼����
       type emp_record is record(
            name employees.last_name%type,
            email employees.email%type,
            salary employees.salary%type
       );
       
       -- ������¼���͵ı���
       v_emp_record emp_record;

begin
--���α�
open emp_cursor;

       --��ȡ�α�
       fetch emp_cursor into v_emp_record;
       
       --���α����ѭ������
       while emp_cursor%found loop
              dbms_output.put_line(v_emp_record.name || ', ' || v_emp_record.email || ', ' || v_emp_record.salary );                
              fetch emp_cursor into v_emp_record;
       end loop;
       
       --�ر��α�
       close emp_cursor;

end;
(������ʹ��forѭ��)
declare

  cursor emp_cursor is 
  select last_name,email,salary
  from employees
  where manager_id = 100;

begin

  for v_emp_record in emp_cursor loop
      dbms_output.put_line(v_emp_record.last_name||','||v_emp_record.email||','||v_emp_record.salary);
  end loop;

end;

14.�����α�, ������˾��Ա���Ĺ���:

���ʷ�Χ ��������
0 - 5000 5%
5000 - 10000 3%
10000 - 15000 2%
15000 - 1%

declare
--�����α�
cursor emp_sal_cursor is select salary, employee_id from employees;

--�����������
temp number(4, 2);

--�������α�ֵ�ı���
v_sal employees.salary%type;
v_id employees.employee_id%type;

begin
--���α�
open emp_sal_cursor;

--��ȡ�α�
fetch emp_sal_cursor into v_sal, v_id;

--�����α��ѭ������
while emp_sal_cursor%found loop
      --�ж�Ա���Ĺ���, ִ�� update ����
      --dbms_output.put_line(v_id || ': ' || v_sal);
        
      if v_sal <= 5000 then
         temp := 0.05;
      elsif v_sal<= 10000 then
         temp := 0.03;   
      elsif v_sal <= 15000 then
         temp := 0.02;
      else
         temp := 0.01;
      end if;
      
      --dbms_output.put_line(v_id || ': ' || v_sal || ', ' || temp);
      update employees set salary = salary * (1 + temp) where employee_id = v_id; 
              
      fetch emp_sal_cursor into v_sal, v_id;
end loop;
--�ر��α�
close emp_sal_cursor;

end;

ʹ��SQL�е� decode ����

update employees set salary = salary * (1 + (decode(trunc(salary/5000), 0, 0.05,
1, 0.03,
2, 0.02,
0.01)))

15.�����α� for ѭ����� 14.

declare
--�����α�
cursor emp_sal_cursor is select salary, employee_id id from employees;

--�����������
temp number(4, 2);

begin
--�����α��ѭ������
for c in emp_sal_cursor loop
--�ж�Ա���Ĺ���, ִ�� update ����
--dbms_output.put_line(v_id || ': ' || v_sal);

      if c.salary <= 5000 then
         temp := 0.05;
      elsif c.salary <= 10000 then
         temp := 0.03;   
      elsif c.salary <= 15000 then
         temp := 0.02;
      else
         temp := 0.01;
      end if;
      
      --dbms_output.put_line(v_id || ': ' || v_sal || ', ' || temp);
      update employees set salary = salary * (1 + temp) where employee_id = c.id;
end loop;

end;

16*. ���������α�

declare
--�����α�
cursor emp_sal_cursor(dept_id number, sal number) is
select salary + 1000 sal, employee_id id
from employees
where department_id = dept_id and salary > sal;

--�����������
temp number(4, 2);

begin
--�����α��ѭ������
for c in emp_sal_cursor(sal => 4000, dept_id => 80) loop
--�ж�Ա���Ĺ���, ִ�� update ����
--dbms_output.put_line(c.id || ': ' || c.sal);

      if c.sal <= 5000 then
         temp := 0.05;
      elsif c.sal <= 10000 then
         temp := 0.03;   
      elsif c.sal <= 15000 then
         temp := 0.02;
      else
         temp := 0.01;
      end if;
      
      dbms_output.put_line(c.sal || ': ' || c.id || ', ' || temp);
      --update employees set salary = salary * (1 + temp) where employee_id = c.id;
end loop;

end;

17.��ʽ�α�:

����ָ��Ա�� salary(�ǹ��� 10)�������Ա��û���ҵ������ӡ�����޴��ˡ� ��Ϣ

begin
update employees set salary = salary + 10 where employee_id = 1005;

     if sql%notfound then
        dbms_output.put_line('���޴���!');
     end if;

end;


					�쳣����

[Ԥ�����쳣]
declare

v_sal employees.salary%type;
begin
select salary into v_sal
from employees
where employee_id >100;

dbms_output.put_line(v_sal);

exception
when Too_many_rows then dbms_output.put_line('���������̫����');
end;

[��Ԥ�����쳣]
declare

v_sal employees.salary%type;
--����һ���쳣
delete_mgr_excep exception;
--���Զ�����쳣��oracle�Ĵ����������
PRAGMA EXCEPTION_INIT(delete_mgr_excep,-2292);
begin
delete from employees
where employee_id = 100;

select salary into v_sal
from employees
where employee_id >100;

dbms_output.put_line(v_sal);

exception
when Too_many_rows then dbms_output.put_line('���������̫����');
when delete_mgr_excep then dbms_output.put_line('Manager����ֱ�ӱ�ɾ��');
end;

[�û��Զ����쳣]
declare

v_sal employees.salary%type;
--����һ���쳣
delete_mgr_excep exception;
--���Զ�����쳣��oracle�Ĵ����������
PRAGMA EXCEPTION_INIT(delete_mgr_excep,-2292);

--����һ���쳣
too_high_sal exception;
begin

select salary into v_sal
from employees
where employee_id =100;

if v_sal > 1000 then
raise too_high_sal;
end if;

delete from employees
where employee_id = 100;

dbms_output.put_line(v_sal);

exception
when Too_many_rows then dbms_output.put_line('���������̫����');
when delete_mgr_excep then dbms_output.put_line('Manager����ֱ�ӱ�ɾ��');
--�����쳣
when too_high_sal then dbms_output.put_line('���ʹ�����');
end;

18.�쳣�Ļ�������:

ͨ�� select ... into ... ��ѯij�˵Ĺ���, ��û�в�ѯ��, ����� "δ�ҵ�����"

declare
--����һ������
v_sal employees.salary%type;
begin
--ʹ�� select ... into ... Ϊ v_sal ��ֵ
select salary into v_sal from employees where employee_id = 1000;
dbms_output.put_line('salary:��' || v_sal);
exception
when No_data_found then
dbms_output.put_line('δ�ҵ�����');
end;

��

declare
--����һ������
v_sal employees.salary%type;
begin
--ʹ�� select ... into ... Ϊ v_sal ��ֵ
select salary into v_sal from employees;
dbms_output.put_line('salary:��' || v_sal);
exception
when No_data_found then
dbms_output.put_line('δ�ҵ�����!');
when Too_many_rows then
dbms_output.put_line('���ݹ���!');
end;

19.����ָ��Ա������

���繤��С��300�����100���� NO_DATA_FOUND �쳣, TOO_MANY_ROWS ���д���.

declare
v_sal employees.salary%type;
begin
select salary into v_sal from employees where employee_id = 100;

if(v_sal < 300) then update employees set salary = salary + 100 where employee_id = 100;
else dbms_output.put_line('���ʴ���300');
end if;
exception
when no_data_found then dbms_output.put_line('δ�ҵ�����');
when too_many_rows then dbms_output.put_line('�����������̫��');
end;

20.������Ԥ������쳣����: "Υ������Լ������"

declare
--1. �����쳣
temp_exception exception;

--2. ���䶨��õ��쳣��������׼�� ORACLE ������ϵ������ʹ�� EXCEPTION_INIT ���
PRAGMA EXCEPTION_INIT(temp_exception, -2292);
begin
delete from employees where employee_id = 100;

exception
--3. �����쳣
when temp_exception then
dbms_output.put_line('Υ��������Լ��!');
end;

21.�Զ����쳣:

����ָ��Ա�����ʣ�����100������Ա�����������׳��û��Զ����쳣: no_result

declare
--�Զ����쳣
no_result exception;
begin
update employees set salary = salary + 100 where employee_id = 1001;

--ʹ����ʽ�α�, �׳��Զ����쳣
if sql%notfound then
raise no_result;
end if;

exception

--���������׳����쳣
when no_result then
dbms_output.put_line('����ʧ��');
end;


				�洢�����͹���

22.�洢�����ʹ洢����

[�洢�������з���ֵ��������ɺ�ͨ��select function() from dual;ִ��]
[�洢���̣�����û�з���ֵ��������ɺ󣬲���ʹ��select��䣬ֻ��ʹ��pl/sql��ִ��]

[��ʽ]
--����������(������������)
create or replace function func_name(v_param varchar2)
--����ֵ����
return varchar2
is
--PL/SQL���������¼���͡��α������(������ǰ���declare�IJ���)
begin
--������(����ʵ����ɾ�IJ�Ȳ���������ֵ��Ҫreturn)
return 'helloworld'|| v_logo;
end;

22.1 ������ helloworld: ����һ�� "helloworld" ���ַ���

create or replace function hello_func
return varchar2
is
begin
return 'helloworld';
end;

ִ�к���

begin
dbms_output.put_line(hello_func());
end;

���ߣ� select hello_func() from dual;

22.2 ����һ��"helloworld: atguigu"���ַ���������atguigu ��ִ�к���ʱ���롣

--����������(������������)
create or replace function hello_func(v_logo varchar2)
--����ֵ����
return varchar2
is
--PL/SQL�����������
begin
--������
return 'helloworld'|| v_logo;
end;

22.3 ����һ���洢���������ص�ǰ��ϵͳʱ��

create or replace function func1
return date
is
--�������
v_date date;
begin
--������
--v_date := sysdate;
select sysdate into v_date from dual;
dbms_output.put_line('���Ǻ���Ŷ');

   return v_date;

end;

ִ�з�1��
select func1 from dual;
ִ�з�2��
declare
v_date date;
begin
v_date := func1;
dbms_output.put_line(v_date);
end;

23.������������: ���������

create or replace function add_func(a number, b number)
return number
is
begin
return (a + b);
end;

ִ�к���

begin
dbms_output.put_line(add_func(12, 13));
end;
����
select add_func(12,13) from dual;

24.����һ������:

��ȡ�������ŵĹ����ܺ�, Ҫ��:���źŶ���Ϊ����, �����ܶ��Ϊ����ֵ.

create or replace function sum_sal(dept_id number)
return number
is

   cursor sal_cursor is select salary from employees where department_id = dept_id;
   v_sum_sal number(8) := 0;   

begin
for c in sal_cursor loop
v_sum_sal := v_sum_sal + c.salary;
end loop;

   --dbms_output.put_line('sum salary: ' || v_sum_sal);
   return v_sum_sal;

end;

ִ�к���

begin
dbms_output.put_line(sum_sal(80));
end;

25.���� OUT �͵IJ���:

��Ϊ����ֻ����һ������ֵ, PL/SQL �������ͨ�� OUT �͵IJ���ʵ���ж������ֵ

Ҫ��: ����һ������: ��ȡ�������ŵĹ����ܺ� �� �ò��ŵ�Ա������(����Ϊ OUT ���͵IJ���).
Ҫ��: ���źŶ���Ϊ����, �����ܶ��Ϊ����ֵ.

create or replace function sum_sal(dept_id number, total_count out number)
return number
is

   cursor sal_cursor is select salary from employees where department_id = dept_id;
   v_sum_sal number(8) := 0;   

begin
total_count := 0;

   for c in sal_cursor loop
       v_sum_sal := v_sum_sal + c.salary;
       total_count := total_count + 1;
   end loop;       

   --dbms_output.put_line('sum salary: ' || v_sum_sal);
   return v_sum_sal;

end;

ִ�к���:

delare
v_total number(3) := 0;

begin
dbms_output.put_line(sum_sal(80, v_total));
dbms_output.put_line(v_total);
end;

26*. ����һ���洢����:

��ȡ�������ŵĹ����ܺ�(ͨ�� out ����), Ҫ��:���źź͹����ܶ��Ϊ����

create or replace procedure sum_sal_procedure(dept_id number, v_sum_sal out number)
is

   cursor sal_cursor is select salary from employees where department_id = dept_id;

begin
v_sum_sal := 0;

   for c in sal_cursor loop
       --dbms_output.put_line(c.salary);
       v_sum_sal := v_sum_sal + c.salary;
   end loop;       

   dbms_output.put_line('sum salary: ' || v_sum_sal);

end;
[ִ��]
declare
v_sum_sal number(10) := 0;
begin
sum_sal_procedure(80,v_sum_sal);
end;

27*. �Զ���һ���洢����������²���:
�Ը�������(��Ϊ�������)��Ա�����м�н����, ���䵽��˾��ʱ���� (? , 95) �ڼ�, Ϊ���н %5
[95 , 98) %3
[98, ?) %1
�õ����·��ؽ��: Ϊ�˴μ�н��˾ÿ����Ҫ���⸶�����ٳɱ�(����һ�� OUT �͵��������).

create or replace procedure add_sal_procedure(dept_id number, temp out number)
is
   cursor sal_cursor is 
   select employee_id id, hire_date hd, salary sal 
   from employees where department_id = dept_id;

   a number(4, 2) := 0;

begin
   temp := 0;  
   for c in sal_cursor loop
       a := 0;    
   
       if c.hd < to_date('1995-1-1', 'yyyy-mm-dd') then
          a := 0.05;
       elsif c.hd < to_date('1998-1-1', 'yyyy-mm-dd') then
          a := 0.03;
       else
          a := 0.01;
       end if;
       
       temp := temp + c.sal * a;
       update employees set salary = salary * (1 + a) where employee_id = c.id;
   end loop; 
end;

					������

һ��helloworld����Ĵ�����
create or replace trigger hello_trigger
after
update on employees
--for each row
begin
dbms_output.put_line('hello...');
--dbms_output.put_line('old.salary:'|| :OLD.salary||',new.salary'||:NEW.salary);
end;
Ȼ��ִ�У�update employees set salary = salary + 1000;

  1. �������� helloworld: ��дһ��������, ���� emp ���в����¼ʱ, ��ӡ 'helloworld'

create or replace trigger emp_trigger
after
insert on emp
for each row
begin
dbms_output.put_line('helloworld');
end;

  1. �м�������: ÿ���� employees ���е�һ����¼, ���ᵼ�´�����ִ��

create or replace trigger employees_trigger
after
update on employees
for each row
begin
dbms_output.put_line('�޸���һ����¼!');
end;

��伶������: һ�� update/delete/insert ���ֻʹ������ִ��һ��

create or replace trigger employees_trigger
after
update on employees
begin
dbms_output.put_line('�޸���һ����¼!');
end;

  1. ʹ�� :new, :old ���η�

create or replace trigger employees_trigger
after
update on employees
for each row
begin
dbms_output.put_line('old salary: ' || :old.salary || ', new salary: ' || :new.salary);
end;

  1. ��дһ��������, �ڶ� my_emp ��¼����ɾ����ʱ��, �� my_emp_bak ���б��ݶ�Ӧ�ļ�¼

1). ׼������:
create table my_emp as select employee_id id, last_name name, salary sal from employees
create table my_emp_bak as select employee_id id, last_name name, salary sal from employees where 1 = 2

2).
create or replace trigger bak_emp_trigger
before delete on my_emp
for each row

begin
insert into my_emp_bak values(:old.id, :old.name, :old.sal);
end;

posted @   三之日up  阅读(9)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· winform 绘制太阳,地球,月球 运作规律
· 震惊!C++程序真的从main开始吗?99%的程序员都答错了
· AI与.NET技术实操系列(五):向量存储与相似性搜索在 .NET 中的实现
· 超详细:普通电脑也行Windows部署deepseek R1训练数据并当服务器共享给他人
· 【硬核科普】Trae如何「偷看」你的代码?零基础破解AI编程运行原理
点击右上角即可分享
微信分享提示