pl sql练习(1)
- 编写函数接受参数并返回字符串:Hello $var.然后赋值给绑定变量并打印;
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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
2.获取系统精确时间和精确到天的时间
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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
3.利用timestamp求出时间间隔
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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
4.编写一个静态游标
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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.
5.编写一个动态游标
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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
6.利用游标查找符合要求的行然后更改每一行
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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 /
7.显式游标的批量处理数据,建议取游标集合中的250~300条数据一次性处理;
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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.