pl sql练习（1）

  1. 编写函数接受参数并返回字符串：Hello $var.然后赋值给绑定变量并打印；
 1 create or replace function hello_function
 2 ( pv_whom varchar2 ) return varchar2 is
 3 begin
 4 return 'Hello '||pv_whom||'.';
 5 end;
 6 /
 7 
 8 
 9 SQL> variable result varchar2(20);
10 SQL> call hello_function('Sam') into :result;
11 
12 Call completed.
13 
14 SQL> print :result
View Code

　　2.获取系统精确时间和精确到天的时间

 1 declare
 2   lv_date_1 date :=sysdate;
 3   lv_date_2 date :=lv_date_1;
 4 begin
 5   dbms_output.put_line('lv_date_1: '||to_char(lv_date_1,'dd-mon-yy hh24:mi:ss'));
 6   dbms_output.put_line('lv_date_2: '||to_char(trunc(lv_date_2),'dd-mon-yy hh24:mi:ss'));
 7 end;
 8 /
 9 
10 
11 SQL> 
12 lv_date_1: 22-8鏈-13 16:33:18
13 lv_date_2: 22-8鏈-13 00:00:00
View Code

 　　3.利用timestamp求出时间间隔

 1 declare
 2 lv_interval interval day(9) to second;
 3 lv_end_day date :=sysdate;
 4 lv_start_day date := '28-apr-2012';
 5 begin
 6   lv_interval :=to_timestamp(lv_end_day) -to_timestamp(lv_start_day);
 7   dbms_output.put_line(lv_interval);
 8 end;
 9 /
10 
11 SQL> 
12 +000000481 18:29:37.000000
View Code

 　　4.编写一个静态游标

 1 declare
 2   cursor c is
 3   select * from scott.dept;
 4 begin
 5   for i in c loop
 6   dbms_output.put_line(i.loc);
 7   end loop;
 8 end;
 9 /
10 
11 SQL> 
12 NEW YORK
13 DALLAS
14 CHICAGO
15 BOSTON
16 
17 PL/SQL procedure successfully completed.
View Code

 　　5.编写一个动态游标

 1 declare
 2      lv_search_sal pls_integer;
 3      cursor c (cv_search pls_integer) is
 4      select * from scott.emp where sal>cv_search;
 5      begin
 6      for i in c (&input) loop
 7      dbms_output.put_line(to_char(i.empno)||' '||i.ename||' '||i.job||' '||to_char(i.sal));
 8      end loop;
 9    end;
10 /
11 
12 
13 SQL> @afiedt.buf
14 Enter value for input: 990
15 old   6:      for i in c (&input) loop
16 new   6:      for i in c (990) loop
17 7499 ALLEN SALESMAN 1600
18 7521 WARD SALESMAN 1250
19 7566 JONES MANAGER 2975
20 7654 MARTIN SALESMAN 1250
21 7698 BLAKE MANAGER 2850
22 7782 CLARK MANAGER 2450
23 7788 SCOTT ANALYST 3000
24 7839 KING PRESIDENT 5000
25 7844 TURNER SALESMAN 1500
26 7876 ADAMS CLERK 1100
27 7902 FORD ANALYST 3000
28 7934 MILLER CLERK 1300
View Code

 　　6.利用游标查找符合要求的行然后更改每一行

 1 declare
 2   cursor c is
 3     select * from scott.emp where sal<990 for update;
 4 begin
 5   for i in c loop
 6     update scott.emp  set sal=sal*1.1
 7     where current of c;
 8   end loop;
 9 end;
10 /
View Code

 　　7.显式游标的批量处理数据，建议取游标集合中的250~300条数据一次性处理；

 1 declare
 2   type people_record is record
 3     (ename varchar2(30),sal pls_integer);
 4   type people_collection is table of people_record;
 5   lv_people_collection people_collection;
 6   cursor c is
 7     select ename,sal from scott.emp;
 8 begin
 9   open c;
10   loop
11     fetch c bulk collect into lv_people_collection limit 5;
12     exit when lv_people_collection.count=0;
13     for i in 1..lv_people_collection.count loop
14       dbms_output.put_line(lv_people_collection(i).ename||' '||to_char(lv_people_collection(i).sal));
15     end loop;
16     dbms_output.put_line(to_char(c%rowcount));
17   end loop;
18   close c;
19 end;
20 /
21 ~
22 
23 SQL> @a.sql;
24 SMITH 800
25 ALLEN 1600
26 WARD 1250
27 JONES 2975
28 MARTIN 1250
29 5
30 BLAKE 2850
31 CLARK 2450
32 SCOTT 3000
33 KING 5000
34 TURNER 1500
35 10
36 ADAMS 1100
37 JAMES 950
38 FORD 3000
39 MILLER 1300
40 14
41 
42 PL/SQL procedure successfully completed.
View Code

 

 

posted on 2013-08-22 17:36  a_badegg  阅读(403)  评论(1)  编辑  收藏  举报

导航