oracle复习笔记之PL/SQL程序所要了解的知识点_oracle_

 declare v_sal number(10); （注意每句话后面别忘记了分号，跟java中的一样） begin select salary into v_sal from employees where employee_id = 100; dbms_output.put_line(v_sal); end; 

 declare v_sal number(10); （注意，这里声明的空间大小不能比原表中的小） v_email varchar2(20); v_hire_date date; begin select salary,email,hire_date into v_sal,v_email,v_hire_date from employees where employee_id = 100; dbms_output.put_line(v_sal||','||v_email||','||v_hire_date); end; 或者： declare v_sal employees.salary%type; v_email employees.email%type; v_hire_date employees.hire_date%type; begin select salary,email,hire_date into v_sal,v_email,v_hire_date from employees where employee_id = 100; dbms_output.put_line(v_sal||','||v_email||','||v_hire_date); end; 

 declare type emp_record is record( v_sal employees.salary%type, v_email employees.email%type, v_hire_date employees.hire_date%type ); v_emp_record emp_record; begin select salary,email,hire_date into v_emp_record from employees where employee_id = 100; dbms_output.put_line(v_emp_record.v_sal||','||v_emp_record.v_email||','|| v_emp_record.v_hire_date); end; 

 declare type emp_record is record( -- v_emp_id employees.employee_id%type, -- v_sal employees.salary%type v_emp_id number(10) := 120, v_sal number(10,2) :=12000 ); v_emp_record emp_record; begin -- select employee_id,salary into v_emp_record from employees where employee_id = 123; dbms_output.put_line('employee_id:'||v_emp_record.v_emp_id||' '||'salary:'|| v_emp_record.v_sal); end; 

 declare v_emp_record employees%rowtype; begin select * into v_emp_record from employees where employee_id = 123; dbms_output.put_line('employee_id:'||v_emp_record.employee_id||' '||'salary:'|| v_emp_record.salary); end; 使用记录来执行update操作： declare v_emp_id number(10); begin v_emp_id :=123; update employees set salary = salary + 100 where employee_id = v_emp_id; dbms_output.put_line('执行成功！~~'); end; 

 declare v_sal employees.salary%type; begin select salary into v_sal from employees where employee_id =150; if v_sal >= 10000 then dbms_output.put_line('salary >= 10000'); elsif v_sal > 5000 then dbms_output.put_line('10000 > salary >= 5000'); else dbms_output.put_line('salary < 5000'); end if; dbms_output.put_line('salary:'||v_sal); end; 利用case ... when ... then ... when ...then ... else ... end实现上题; declare v_sal employees.salary%type; v_temp varchar2(20); begin select salary into v_sal from employees where employee_id =150; v_temp := case trunc(v_sal/5000) when 0 then 'salary < 5000' when 1 then '5000 <= salary < 10000' else 'salary >= 10000' end; dbms_output.put_line('salary:'||v_sal||' '||v_temp); end; 

 declare v_job_id employees.job_id%type; v_temp varchar2(20); begin select job_id into v_job_id from employees where employee_id =122; v_temp := 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('job_id:'||v_job_id||' '||v_temp); end; 

 declare v_i number(5) :=1; begin loop dbms_output.put_line(v_i); exit when v_i >=100; v_i := v_i + 1; end loop; end; 使用while实现： declare v_i number(5) :=1; begin while v_i <= 100 loop dbms_output.put_line(v_i); v_i := v_i + 1; end loop; end; 使用for...in...loop...end loop;实现： begin for c in 1..100 loop dbms_output.put_line(c); end loop; end; 

 declare v_i number(3):= 2; v_j number(3):= 2; v_flag number(1):= 1; 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_j :=2; v_i := v_i + 1; v_flag := 1; end loop; end; 

 declare v_flag number(1):= 1; begin for v_i in 2..100 loop for v_j in 2..sqrt(v_i) loop if mod(v_i,v_j)=0 then v_flag:=0; end if; end loop; if v_flag=1 then dbms_output.put_line(v_i); end if; v_flag := 1; end loop; end; 

 declare v_flag number(1):= 1; begin for v_i in 2..100 loop for v_j in 2..sqrt(v_i) loop if mod(v_i,v_j)=0 then v_flag:=0; goto label; end if; end loop; <> if v_flag=1 then dbms_output.put_line(v_i); end if; v_flag := 1; end loop; end; 

 begin for i in 1..100 loop if i=50 then goto label; end if; dbms_output.put_line(i); end loop; <> dbms_output.put_line('打印结束'); end; 或者： begin for i in 1..100 loop if i=50 then dbms_output.put_line('打印结束'); exit; end if; dbms_output.put_line(i); end loop; end; 

 declare v_empid employees.employee_id%type; v_lastName employees.last_name%type; v_sal employees.salary%type; cursor emp_sal_cursor is select employee_id,last_name,salary from employees where department_id = 80; begin open emp_sal_cursor; fetch emp_sal_cursor into v_empid,v_lastName,v_sal; while emp_sal_cursor%found loop dbms_output.put_line('employee_id:'||v_empid||', '||'last_name:'||v_lastName||', '||'salary:'||v_sal); fetch emp_sal_cursor into v_empid,v_lastName,v_sal; end loop; close emp_sal_cursor; end; 

 declare type emp_record is record( v_empid employees.employee_id%type, v_lastName employees.last_name%type, v_sal employees.salary%type ); v_emp_record emp_record; cursor emp_sal_cursor is select employee_id,last_name,salary from employees where department_id = 80; begin open emp_sal_cursor; fetch emp_sal_cursor into v_emp_record; while emp_sal_cursor%found loop dbms_output.put_line('employee_id:'||v_emp_record.v_empid||', '||'last_name:'|| v_emp_record.v_lastName||', '||'salary:'||v_emp_record.v_sal); fetch emp_sal_cursor into v_emp_record; end loop; close emp_sal_cursor; end; 

 declare cursor emp_sal_cursor is select employee_id,last_name,salary from employees where department_
                
提示：
                    本文由神整理自网络，如有侵权请联系本站删除！
                    

                    本站声明： 

                    1、本站所有资源均来源于互联网，不保证100%完整、不提供任何技术支持； 

                    2、本站所发布的文章以及附件仅限用于学习和研究目的;不得将用于商业或者非法用途；否则由此产生的法律后果，本站概不负责！
                
                

                

                                            
上一篇：Oracle 表三种连接方式使用介绍(sql优化)_oracle_
                                                                
下一篇：向Oracle数据库的CLOB属性插入数据报字符串过长错误_oracle_