存储过程
一、函数
1.1 函数格式
create or reeplace function func_name( dept_id number, salary number)
return number
is
begin
exception
end ;
1.2 定义函数
helloworld函数
create or replace function hello_world
return varchar2
is
begin
return 'hello' ;
end ;
执行函数 打印数据
SQL > ed
begin
dbms_output. put_line( hello_world) ;
end ;
SQL > /
SQL > select hello_world from dual;
补充
回复: 为什麽dbms_output.put_line('hello')不打印?
sql> show all
serveroutput OFF
sql> set serveroutput on
sql> exec
1.3 函数详细
1. 带参函数
create or replace function hello_world( v_logo varchar2)
return varchar2
is
begin
return 'hello ' || v_logo;
end ;
SQL > select hello_world( 'hhhh' ) from dual;
2. 定义变量函数
create or replace function get_sysdate
return date
is
v_date date ;
begin
v_date := sysdate;
return v_date;
end ;
3. 多个参数的函数
create or replace function add_param( v_num1 number, v_num2 number)
return number
is
v_sum number;
begin
v_sum := v_num1 + v_num2;
return v_sum;
end ;
4. 定义变量多个(使用游标的函数)
create or replcae function get_sal( dept_id number)
return number
is
v_sumsal number( 10 ) := 0 ;
cursor salary_cursor is select salary from employess where department_id = dept_id
begin
for c in salary_cursor loop
v_sumsal := v_sumsal + c. salary;
end loop ;
return v_sumsal;
end ;
declare
v_dept_id number( 4 ) := 80 ;
begin
dbms_output. put_line( get_sal( v_dept_id) ) ;
end ;
5. 多个返回值
create or replace function get_sql( dept_id number, total_count out number)
return number
is
v_sumsal number( 10 ) := 0 ;
cursor salary_cursor is select salary from employees where department_id = dept_id;
begin
total_count := 0 ;
for c in salary_cursor loop
v_sumsal := v_sumsal + c. salary;
total_count := total_count + 1 ;
end loop ;
return v_sumsal;
end ;
declare
v_num number( 5 ) := 0 ;
begin
dbms_output. put_line( get_sal( 80 , v_num) ) ;
dbms_output. put_line( v_num) ;
end ;
二、存储过程
2.1 函数格式
create or replcae procedure get_sal( dept_id number, sumsal out number)
is
begin
end ;
2.2 存储过程
1. 多个变量存储过程
create or replcae procedure get_sal( dept_id number, sumsal out number)
is
v_sumsal number( 10 ) := 0 ;
cursor salary_cursor is select salary from employess where department_id = dept_id
begin
sumsal := 0 ;
for c in salary_cursor loop
sumsal := sumsal + c. salary;
end loop ;
dbms_output. put( sumsal) ;
end ;
declare
v_sal number( 4 ) := 0 ;
begin
get_sal( 80 , v_sal) ;
end ;
2. 复杂执行体
create or replace procedure add_sal( dept_sal number, temp_sql out number)
is
cursor sal_cursor is select employee_id, salary, hire_date from employees where department_id = dept_id;
v_i number( 4 , 2 ) := 0 ;
begin
temp_sql := 0 ;
for c in sal_cursor loop
if to_char( c. hire_date, 'yyyy' ) < '1999' then v_i := 0.05 ;
elseif to_char( c. hire_date, 'yyyy' ) < '1998' then v_i := 0.03 ;
else v_i := 0.01 ;
end if ;
update employees set salary = salary * ( 1 + v_i) where employee_id = c. employee_id;
temp_sql := temp_sql + c. salary * v_i;
end loop ;
end ;
3 循环
create or replace procedure genoid
as
n_count number := 0 ;
begin
while n_count < 10 loop
dbms_output. put_line( n_count) ;
n_count := n_count + 1 ;
end loop ;
commit ;
end ;
2.3 删除
删除存储过程 DROP PROCEDURE 存储过程名字
删除函数 DROP FUNCTION 函数名字
删除触发器 DROP TRIGGER 触发器名字