pl_sql

标签：salary end sal -- emp sql id pl

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);

-- ��ӡ��ֵ
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;

<>
if v_flag = 1 then
dbms_output.put_line(i);
end if;

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 ... ��ѯĳ�˵Ĺ��, ��û�в�ѯ��, �� "δ�ҵ��"

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;ִ��]
[�洢��̣��û�з��ֵ��ɺ

标签：salary,end,sal,--,emp,sql,id,pl
From： https://www.cnblogs.com/szrup0126/p/16949418.html

PL/SQL��

1.ʹ��һ��

2.ʹ�ö��

3.��¼��

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

5.ʹ�� %rowtype

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

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

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

9.ѭ��ӡ1-100

10.if,while

11.ʹ�� goto

12.�α�

13.ʹ��α��ϰ:

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

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

16*. ��α�

17.��ʽ�α�:

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

18.�쳣�Ļ��:

19.��ָ��Ա��

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

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

21.�Զ��쳣:

22.�洢��ʹ洢��

相关文章

赞助商

阅读排行

pl_sql

PL/SQL����

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

2.ʹ�ö������

3.��¼����

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

5.ʹ�� %rowtype

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

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

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

9.ѭ����ӡ1-100

10.if,while

11.ʹ�� goto

12.�α�

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

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

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

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

17.��ʽ�α�:

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

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

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

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

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

21.�Զ����쳣:

22.�洢�����ʹ洢����

相关文章

赞助商

阅读排行

PL/SQL��

1.ʹ��һ��

2.ʹ�ö��

3.��¼��

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

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

9.ѭ��ӡ1-100

13.ʹ��α��ϰ:

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

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

16*. ��α�

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

18.�쳣�Ļ��:

19.��ָ��Ա��

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

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

21.�Զ��쳣:

22.�洢��ʹ洢��