- 编写函数接受参数并返回字符串：Hello $var.然后赋值给绑定变量并打印；
create or replace function hello_function
( pv_whom varchar2 ) return varchar2 is
begin
return 'Hello '||pv_whom||'.';
end;
/ SQL> variable result varchar2(20);
SQL> call hello_function('Sam') into :result; Call completed. SQL> print :result
2.获取系统精确时间和精确到天的时间
declare
lv_date_1 date :=sysdate;
lv_date_2 date :=lv_date_1;
begin
dbms_output.put_line('lv_date_1: '||to_char(lv_date_1,'dd-mon-yy hh24:mi:ss'));
dbms_output.put_line('lv_date_2: '||to_char(trunc(lv_date_2),'dd-mon-yy hh24:mi:ss'));
end;
/ SQL>
lv_date_1: 22-8鏈-13 16:33:18
lv_date_2: 22-8鏈-13 00:00:00
3.利用timestamp求出时间间隔
declare
lv_interval interval day(9) to second;
lv_end_day date :=sysdate;
lv_start_day date := '28-apr-2012';
begin
lv_interval :=to_timestamp(lv_end_day) -to_timestamp(lv_start_day);
dbms_output.put_line(lv_interval);
end;
/ SQL>
+000000481 18:29:37.000000
4.编写一个静态游标
declare
cursor c is
select * from scott.dept;
begin
for i in c loop
dbms_output.put_line(i.loc);
end loop;
end;
/ SQL>
NEW YORK
DALLAS
CHICAGO
BOSTON PL/SQL procedure successfully completed.
5.编写一个动态游标
declare
lv_search_sal pls_integer;
cursor c (cv_search pls_integer) is
select * from scott.emp where sal>cv_search;
begin
for i in c (&input) loop
dbms_output.put_line(to_char(i.empno)||' '||i.ename||' '||i.job||' '||to_char(i.sal));
end loop;
end;
/ SQL> @afiedt.buf
Enter value for input: 990
old 6: for i in c (&input) loop
new 6: for i in c (990) loop
7499 ALLEN SALESMAN 1600
7521 WARD SALESMAN 1250
7566 JONES MANAGER 2975
7654 MARTIN SALESMAN 1250
7698 BLAKE MANAGER 2850
7782 CLARK MANAGER 2450
7788 SCOTT ANALYST 3000
7839 KING PRESIDENT 5000
7844 TURNER SALESMAN 1500
7876 ADAMS CLERK 1100
7902 FORD ANALYST 3000
7934 MILLER CLERK 1300
6.利用游标查找符合要求的行然后更改每一行
declare
cursor c is
select * from scott.emp where sal<990 for update;
begin
for i in c loop
update scott.emp set sal=sal*1.1
where current of c;
end loop;
end;
/
7.显式游标的批量处理数据，建议取游标集合中的250~300条数据一次性处理；
declare
type people_record is record
(ename varchar2(30),sal pls_integer);
type people_collection is table of people_record;
lv_people_collection people_collection;
cursor c is
select ename,sal from scott.emp;
begin
open c;
loop
fetch c bulk collect into lv_people_collection limit 5;
exit when lv_people_collection.count=0;
for i in 1..lv_people_collection.count loop
dbms_output.put_line(lv_people_collection(i).ename||' '||to_char(lv_people_collection(i).sal));
end loop;
dbms_output.put_line(to_char(c%rowcount));
end loop;
close c;
end;
/
~ SQL> @a.sql;
SMITH 800
ALLEN 1600
WARD 1250
JONES 2975
MARTIN 1250
5
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
TURNER 1500
10
ADAMS 1100
JAMES 950
FORD 3000
MILLER 1300
14 PL/SQL procedure successfully completed.