set serveroutput on;
show error;
declare
v_ename varchar2(20);
v_sal number;
begin
select ename, sal into v_ename, v_sal from emp where empno=&no;
dbms_output.put_line('雇员名:' || v_ename || '薪水:' || v_sal);
exception
when no_data_found then
dnms_output.put_line('未找该员工');
end;
create [or replace] procedure 过程名_pro[(参数)] is
begin
执行语句
end;
exec 过程名[(参数)];
create function 函数名_func(参数)
return 返回值类型 is
返回变量名 返回变量具体类型;
begin
select 需要返回的字段 into 返回变量名 from 表名 where 字段 = 参数;
return 返回变量名;
end;
var 变量名 变量类型;
call 函数名(参数) into:变量名;
print 变量名;
create package 包名 is
procedure 储存过程名(参数);
function 函数名(参数) return 返回值类型;
end;
create or replace package body 包名 is
procedure 储存过程名(参数) is
begin
insert into test1 values(v_id, v_name);
end;
function 函数名(参数)
return 返回值类型 is
返回变量 返回变量具体类型;
begin
select 需要返回的字段 into 返回变量名 from 表名 where 字段 = 参数;
return 返回变量;
end;
end;
call 包名.储存过程名(3, '使用包插入的值');
select 包名.函数名('SCOTT') from dual;
select * from test1;
declare
type emp_record_type is record(name emp.ename%type, salary emp.sal%type, title emp.job%type);
emp_record emp_record_type;
begin
select ename, sal, job into emp_record from emp where empno = 7654;
dbms_output.put_line('姓名:' || emp_record.name || ', 薪水:' || emp_record.salary || ', 工作:' || emp_record.title);
end;
declare
type sp_table_type is table of emp.ename%type index by binary_integer;
sp_table sp_table_type;
begin
select ename into sp_table(-1) from emp where empno = 7654;
dbms_output.put_line('姓名:' || sp_table(-1));
end;
declare
type sp_emp_cursor is ref cursor;
test_cursor sp_emp_cursor;
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
open test_cursor for select ename, sal from emp;
loop
fetch test_cursor into v_ename, v_sal;
exit when test_cursor%notfound;
dbms_output.put_line('姓名:' || v_ename ||', 薪水: ' || v_sal);
end loop;
close test_cursor;
end;