--创建存储过程
create or replace procedure proc_first
as
begin
dbms_output.put_line('hello world!');
end;
begin
proc_first;
end;
drop procedure proc_first
--带参数的存储过程 PS:括号内是参数,BEGIN前是变量
create or replace procedure proc_Second
(empid number) --is或as前是输入或输出参数
is
tempsalary number;--局部变量
begin
select sal into tempsalary from emp where empno = empid;
dbms_output.put_line(tempsalary);
end;
begin
proc_Second(empid => 7499);--命名传参,可混乱
proc_Second(7499);--匿名传参,顺序必须一直
end;
drop procedure proc_Second;
--带输出参数的存储过程
create or replace procedure proc_third(
empid number,
v_salary out number --表示输出参数
)as
begin
select sal into v_salary from emp where empno = empid;
end;
--调用带OUT的参数.备注,参数需在括号内,不需要用到OUT
declare
vsalary number;
begin
proc_third (7499,vsalary);
dbms_output.put_line(vsalary);
end;
drop procedure proc_third;
--INOUT存储过程
create or replace procedure proc_four(
paramOne in out number,--输入输出
paramTwo in out number
)
as
v1 number;
v2 number;
begin
v1:=paramOne*paramTwo;
v2:=Mod(paramOne,paramTwo);
paramOne:=v1;
paramTwo:=v2;
end;
--调用
declare
v1 number :=10;
v2 number :=2;
begin
proc_four(paramOne=>v1,paramTwo=>v2);
dbms_output.put_line(v1||' '||v2);
end;
drop procedure proc_four;
--函数
create or replace function firstfun return varchar2 as --必须先定义返回类型在as后才能声明局部变量
vuser varchar2(10);
begin
select username into vuser from user_users;--查询当前登录用户
return vuser;
end;
--调用
begin
dbms_output.put_line(firstfun);
end;
--有IN参数的函数
create or replace function SecondFun(empid emp.empno%type) return emp.ename%type as
empName emp.ename%type;
begin
select ename into empName from emp where emp.empno=empid;
return empName;
end;
--调用
declare
empid emp.empno%type;
begin
dbms_output.put_line(SecondFun(7499));
end;
--有OUT参数的函数
create or replace function ThirdFun(
empId emp.empno%type,
empName out emp.ename%type
) return emp.sal%type
as
Vsalary number;
begin
select ename ,sal into empName ,Vsalary from emp where emp.empno=empId;
return Vsalary;
end;
--调用
declare
ename emp.ename%type;
begin
dbms_output.put_line(ThirdFun(&empid,ename));
dbms_output.put_line(ename);
end;
--查询用户子程序和源代码
select * from user_source
select * from user_objects
--包
create or replace package my_page is --is后边通常书写的是局部变量
g_empNo number;
procedure proc_updateSalary_employee(
param_empNo number
);
function fun_get_salary(param_empNo number) return number;
end my_page;
--包的身体
create or replace package body my_page as --as后必须是包头实现
procedure proc_updateSalary_employee(
param_empNo number
) as
begin
update emp set sal=sal+1 where emp.empno=param_empNo;
end;
function fun_get_salary(param_empNo number) return number
as
salaryTemp number;
begin
select sal into salaryTemp from emp where emp.empno=param_empNo;
return salaryTemp;
end;
end my_page;
--调用
begin
dbms_output.put_line(my_page.fun_get_salary(7499));
my_page.proc_updateSalary_employee(7499);
dbms_output.put_line(my_page.fun_get_salary(7499));
end;
--授权
grant execute on my_page to hr;
begin
dbms_output.put_line(scott.my_page.fun_get_salary(7499));
end;