13.txt

Oracle database 10g基础教程（第二版） 源码

create procedure my_proc
as
begin
  null;
end my_proc;
/
 



create procedure my_proc
as
begin
  dbms_output.put_line('欢迎！');
end my_proc;
/





create or replace procedure my_proc
as
begin
  dbms_output.put_line('欢迎！');
end my_proc;
/



set serverout on
begin
  my_proc;
end;
/



exec my_proc;




create user Bush
identified by Bush;
grant connect, resource 
to Bush;



connect Bush/Bush
exec system.my_proc;



connect system/manager
grant execute on my_proc
to Bush;
connect Bush/Bush
exec system.my_proc;


connect system/manager
create table t(
n number
);
create or replace
procedure insert_into_t(p_parm in number)
is
begin
  insert into t values(p_parm);
end insert_into_t;
/


select *
from t;
exec insert_into_t(p_parm=>100);
select *
from t;




create or replace
procedure insert_into_t(
  p_parm1 in number,
  p_parm2 in number)
is
begin
  insert into t values(p_parm1);
  insert into t values(p_parm2);
end insert_into_t;
/


exec insert_into_t(p_parm1=>101, p_parm2=>102);
select *
from t;


drop table t;
create table t(
n number,
parm varchar2(20)
);
create or replace
procedure insert_into_t(
  p_parm1 in number,
  p_parm2 in number)
is
begin
  insert into t values(p_parm1, 'p_parm1');
  insert into t values(p_parm2, 'p_parm2');
end insert_into_t;
/


exec insert_into_t(p_parm1=>201, p_parm2=>202);
select *
from t;






create or replace
procedure three_parms(
  p_p1 number,
  p_p2 number,
  p_p3 number)
as
begin
  dbms_output.put_line('p_p1='||p_p1);
  dbms_output.put_line('p_p2='||p_p2);
  dbms_output.put_line('p_p3='||p_p3);
end three_parms;
/




set serverout on
exec three_parms(p_p2=>36, p_p3=>50, p_p1=>12);


exec three_parms(22, 71, 198);


exec three_parms(121, 5, p_p3=>669);


create or replace
procedure default_values(
  p_parm1 varchar2,
  p_parm2 varchar2 default 'Microsoft',
  p_parm3 varchar2 default 'IBM')
as
begin
  dbms_output.put_line(p_parm1);
  dbms_output.put_line(p_parm2);
  dbms_output.put_line(p_parm3);
end default_values;
/
set serverout on
exec default_values('Sybase', p_parm3=>'Oracle');








create or replace
procedure emp_lookup(
  p_empno in number,
  o_ename out emp.ename%type,
  o_sal out emp.sal%type)
as
begin
  select ename, sal
  into o_ename, o_sal
  from emp
  where empno = p_empno;
exception
  when NO_DATA_FOUND then
    o_ename := 'null';
    o_sal := -1;
end emp_lookup;
/


variable name varchar2(10);
variable sal number;
exec emp_lookup('7782', :name, :sal);



print name
print sal



set serverout on
declare
  l_ename emp.ename%type;
  l_sal emp.sal%type;
begin
  emp_lookup(7782, l_ename, l_sal);
  dbms_output.put_line('ename='||l_ename);
  dbms_output.put_line('sal='||l_sal);
end;
/




create or replace
procedure swap(
  p_parm1 in out number,
  p_parm2 in out number)
as
  l_temp number;
begin
  l_temp := p_parm1;
  p_parm1 := p_parm2;
  p_parm2 := l_temp;
end swap;
/


declare
  l_num1 number := 111;
  l_num2 number := 222;
begin
  dbms_output.put_line('交换前的变量值');
  dbms_output.put_line('l_num1='||l_num1);
  dbms_output.put_line('l_num2='||l_num2);
  swap(l_num1, l_num2);
  dbms_output.put_line('交换后的变量值');
  dbms_output.put_line('l_num1='||l_num1);
  dbms_output.put_line('l_num2='||l_num2);
end;
/




create or replace
function first_function return varchar2
as
begin
  return '欢迎！';
end first_function;
/



declare
  l_str varchar2(100) := null;
begin
  l_str := first_function;
  dbms_output.put_line(l_str);
end;
/



create or replace
procedure show_it(p_text varchar2)
as
begin
  dbms_output.put_line(p_text);
end show_it;
/
exec show_it(first_function);




create or replace
function total_compensation(
  p_salary number,
  p_commission number) return number
deterministic
as
begin
  return nvl(p_salary, 0) + nvl(p_commission, 0);
end total_compensation;
/





exec first_function;



create or replace
function bad_function return number
as
begin
  null;
end bad_function;
/
exec show_it(bad_function);










create or replace
function no_return_type
as
begin
  return null;
end no_return_type;
/
show errors







create or replace
package employee_pkg
as
  procedure print_ename(p_empno number);
  procedure print_sal(p_empno number);
end employee_pkg;
/




exec employee_pkg.print_ename(1234);





create or replace
package body employee_pkg
as

procedure print_ename(p_empno number)
is
  l_ename emp.ename%type;
begin
  select ename
  into l_ename
  from emp
  where empno = p_empno;
  dbms_output.put_line(l_ename);
exception
  when no_data_found then
    dbms_output.put_line('无效的数字。');
end print_ename;

procedure print_sal(p_empno number)
is
  l_sal emp.sal%type;
begin
  select sal
  into l_sal
  from emp
  where empno = p_empno;
  dbms_output.put_line(l_sal);
exception
  when no_data_found then
    dbms_output.put_line('无效的数字。');
end print_sal;

end employee_pkg;
/


set serverout on
exec employee_pkg.print_ename(1234);
exec employee_pkg.print_ename(7782);
exec employee_pkg.print_sal(7782);