Oracle存储过程
1、无参,最简单存储过程
CREATE OR REPLACE PROCEDURE HELLO_WORLD IS
BEGIN DBMS_OUTPUT.PUT_LINE('Hello, World!');
END HELLO_WORLD;
输出
Hello, World!
2、输出查询语句
创建源数据
存储过程语句(求总薪资)
CREATE OR REPLACE PROCEDURE sum_salary
is
v_sum salary.salary%TYPE:=0;
CURSOR CURSOR_p IS SELECT salary FROM salary;
BEGIN FOR v_cursor IN CURSOR_p LOOPv_sum:=v_sum+v_cursor.salary;END LOOP;DBMS_OUTPUT.PUT_LINE('v_sum:'||v_sum);
END;
调用存储过程
BEGIN
sum_salary;
END;
命令行调用
exec zhang.sum_salary;
输出结果
v_sum:71000
SQL> exec zhang.sum_salary;
v_sum:71000
有参数调用
建表源数据
存储过程(有参数)
CREATE OR REPLACE PROCEDURE sum_salary
is
v_sum salary.salary%TYPE:=0;
CURSOR CURSOR_p IS SELECT salary FROM salary;
BEGIN FOR v_cursor IN CURSOR_p LOOPv_sum:=v_sum+v_cursor.salary;END LOOP;DBMS_OUTPUT.PUT_LINE('v_sum:'||v_sum);
END;
调用存储过程1
DECLARE
inp_continent PEOPLE.continent%TYPE;
resultpo PEOPLE.POPULATION%TYPE;
BEGINinp_continent:='Europ';sum_salary_1(inp_continent,resultpo);DBMS_OUTPUT.PUT_LINE('result:'||resultpo);
END;
输出结果
result:14000
调用存储过程2
DECLARE
inp_continent PEOPLE.continent%TYPE;
resultpo PEOPLE.POPULATION%TYPE;
BEGINinp_continent:='EUROP';sum_salary_1(inp_continent,resultpo);DBMS_OUTPUT.PUT_LINE('result:'||resultpo);
END;
输出结果
result:40000