1、设置set serveroutput on参数,为了能在控制台看到输出的数据
2、基本格式
declare
--填写变量、记录类型等
type emp_record is record(
--两种类型推断的方式
v_id number(11), --自己定义变量类型
v_name emp.name%type --自动进行类型推断
);
v_emp_record emp_record;--创建record的实例,类似java的创建实例类
begin
--程序执行的主体
select id,name into v_emp_record from emp
where id=101;
exception --若有异常,进行异常逻辑编写
dbms_out.put_line(v_emp_record.id||','||v_emp_record.name);--打印输出
end;
/
3、流程控制
条件判断(两种)
方式1:if xxx then else then xx else xx end if;
方式2:case ... when ... then ... end;
循环结构(三种)
方式1:loop ... exit when ... end loop;
方式2:while ... loop.... end loop;
方式3:for i in ... loop ... end loop;
--判断结构例子:
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('5000<=v_sal<=10000');
else
dbms_output.put_line('salary>5000');
end if;
end;
/
declare
v_job_id varchar2(22);
begin
select job_id into v_job_id from employees where employee_id =123;
if v_job_id='IT_PROG' then
dbms_output.put_line('garde:A');
elsif v_job_id='AC_MGT' then
dbms_output.put_line('grade:B');
elsif v_job_id='AC_ACCOUNT' then
dbms_output.put_line('grade:C');
else
dbms_output.put_line('grade:D');
end if;
end;
/
--打印1-100
declare
v_num number(4) :=1;
begin
loop
dbms_output.put_line(v_num);
exit when v_num=100;
v_num :=v_num+1;
end loop;
end;
/
--打印2-100的质数
declare
v_num number(3) :=2;
v_j number(2) :=2;
v_flag number(1) :=1;
begin
while v_num<=100 loop
while v_j<sqrt(v_num) loop
if mod(v_num,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_num);
end if;
v_j :=2;
v_num :=v_num+1;
v_flag := 1;
end loop;
end;
/
declare
v_i number(3) :=2;
v_j number(2) :=2;
v_flag number(2) :=1;
begin
for v_i in 2..100 loop
v_flag :=1;
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;
end loop;
end;
/
4、游标的使用(类似于java的Iterator)
--打印部门id为80的员工工资:
declare
--定义一个变量类型
type emp_record is record(
v_sal number(11),
v_empid employees.employee_id%type
);
--声明一个记录类型的变量
v_emp_record emp_record;
--定义游标
cursor emp_sal_cursor is select salary,employee_id 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(v_emp_record.v_empid||','||v_emp_record.v_sal);
fetch emp_sal_cursor into v_emp_record;
end loop;
--关闭游标
close emp_sal_cursor;
end;
/
5、存储函数和存储过程
create or replace function func_name(id number,name varchar2,salary number)
return number
is
--定义函数使用过程中,需要定义的变量,记录类型、cursor
begin
end;
/
create or replace function hello_world1(v_logo varchar2)
return varchar2
is
begin
dbms_output.put_line('这是存储函数');
return 'helloworld,'||v_logo;
end;
/
begin
dbms_output.put_line(hello_world1('nike'));
end;
/
create or replace function getdate
return date
is
v_date date;
begin
v_date :=sysdate;
return v_date;
end;
/
create or replace function add_nums(i number,j number)
return number
is
v_sum number;
begin
v_sum:=i+j;
return v_sum;
end;
/
select add_nums(1,2) from dual;
create or replace function get_sal(dept_id number)
return number
is
v_sumsal number(10) :=0;
cursor sal_cursor is select salary from employees where department_id=dept_id;
begin
for c in sal_cursor loop
v_sumsal := v_sumsal+c.salary;
end loop;
return v_sumsal;
end;
/
select get_sal(70) from dual;
create or replace procedure get_sal2(dept_id number,sumsal out number)
is
cursor sal_cursor is select salary from employees where department_id=dept_id;
begin
sumsal :=0;
for c in sal_cursor loop
sumsal := sumsal+c.salary;
end loop;
dbms_output.put_line(sumsal);
end;
/
declare
v_num number(10) :=0;
begin
get_sal2(80,v_num);
end;
/