过程和函数
作者:gqk
/*
参数的类型
输入参数IN:默认,实参赋值给形参
输出参数OUT:形参赋值给实参
输入输出参数IN OUT:形参和实参直接可以互相赋值
注意:参数和返回值的名称是不能写子参数的,比如number,varchar2是正确的,number(8,2) varchar2(50)是错误的
*/
参数的类型
输入参数IN:默认,实参赋值给形参
输出参数OUT:形参赋值给实参
输入输出参数IN OUT:形参和实参直接可以互相赋值
注意:参数和返回值的名称是不能写子参数的,比如number,varchar2是正确的,number(8,2) varchar2(50)是错误的
*/
--创建函数get_date,返回当前系统时间yyyy-mm-dd格式的日期字符串
CREATE OR REPLACE FUNCTION get_date
RETURN VARCHAR2
AS
v_date VARCHAR2(50);
BEGIN
v_date := TO_CHAR(SYSDATE,'yyyy-mm-dd');
RETURN v_date;
END;
CREATE OR REPLACE FUNCTION get_date
RETURN VARCHAR2
AS
v_date VARCHAR2(50);
BEGIN
v_date := TO_CHAR(SYSDATE,'yyyy-mm-dd');
RETURN v_date;
END;
--测试get_date
BEGIN
dbms_output.put_line(get_date);
END;
BEGIN
dbms_output.put_line(get_date);
END;
--创建函数get_result1,传入两个任意数字,返回两个数字的相加和(输入参数)
CREATE OR REPLACE FUNCTION get_result1(p_num1 IN NUMBER,p_num2 IN NUMBER)
RETURN NUMBER
IS
v_result NUMBER;
BEGIN
v_result := p_num1 + p_num2;
RETURN v_result;
END;
CREATE OR REPLACE FUNCTION get_result1(p_num1 IN NUMBER,p_num2 IN NUMBER)
RETURN NUMBER
IS
v_result NUMBER;
BEGIN
v_result := p_num1 + p_num2;
RETURN v_result;
END;
--测试get_result1
BEGIN
dbms_output.put_line( get_result1(5,3) );
END;
BEGIN
dbms_output.put_line( get_result1(5,3) );
END;
--创建函数get_result2,传入两个任意数字,返回两个数字的相加结果,相减结果,相乘结果(输出参数)
CREATE OR REPLACE FUNCTION get_result2
(p_num1 NUMBER,p_num2 NUMBER,p_result2 OUT NUMBER,p_result3 OUT NUMBER)
RETURN NUMBER
AS
v_result NUMBER;
BEGIN
v_result := p_num1 + p_num2;
p_result2 := p_num1 - p_num2;
p_result3 := p_num1 * p_num2;
RETURN v_result;
END;
CREATE OR REPLACE FUNCTION get_result2
(p_num1 NUMBER,p_num2 NUMBER,p_result2 OUT NUMBER,p_result3 OUT NUMBER)
RETURN NUMBER
AS
v_result NUMBER;
BEGIN
v_result := p_num1 + p_num2;
p_result2 := p_num1 - p_num2;
p_result3 := p_num1 * p_num2;
RETURN v_result;
END;
--测试get_result2
DECLARE
v_result2 NUMBER;
v_result3 NUMBER;
BEGIN
dbms_output.put_line(get_result2(5,3,v_result2,v_result3));
dbms_output.put_line(v_result2);
dbms_output.put_line(v_result3);
END;
DECLARE
v_result2 NUMBER;
v_result3 NUMBER;
BEGIN
dbms_output.put_line(get_result2(5,3,v_result2,v_result3));
dbms_output.put_line(v_result2);
dbms_output.put_line(v_result3);
END;
--创建函数get_result3,传入两个任意数字,返回两个数字的相加结果,相减结果,相乘结果(输入输出参数)
CREATE OR REPLACE FUNCTION get_result3(p_num1 IN OUT NUMBER,p_num2 IN OUT NUMBER)
RETURN NUMBER
AS
v_result NUMBER;
v_temp1 NUMBER := p_num1;
v_temp2 NUMBER := p_num2;
BEGIN
v_result := v_temp1 + v_temp2;
p_num1 := v_temp1 - v_temp2;
p_num2 := v_temp1 * v_temp2;
RETURN v_result;
END;
CREATE OR REPLACE FUNCTION get_result3(p_num1 IN OUT NUMBER,p_num2 IN OUT NUMBER)
RETURN NUMBER
AS
v_result NUMBER;
v_temp1 NUMBER := p_num1;
v_temp2 NUMBER := p_num2;
BEGIN
v_result := v_temp1 + v_temp2;
p_num1 := v_temp1 - v_temp2;
p_num2 := v_temp1 * v_temp2;
RETURN v_result;
END;
--测试get_result3
DECLARE
v_result2 NUMBER := 5;
v_result3 NUMBER := 3;
BEGIN
dbms_output.put_line(get_result3(v_result2,v_result3));
dbms_output.put_line(v_result2);
dbms_output.put_line(v_result3);
END;
DECLARE
v_result2 NUMBER := 5;
v_result3 NUMBER := 3;
BEGIN
dbms_output.put_line(get_result3(v_result2,v_result3));
dbms_output.put_line(v_result2);
dbms_output.put_line(v_result3);
END;
--创建存储过程add_dept,传入部门编号,部门名称,部门地址增加部门
CREATE OR REPLACE PROCEDURE add_dept
(p_deptno dept.deptno%TYPE,p_dname dept.dname%TYPE,p_loc dept.loc%TYPE)
AS
CREATE OR REPLACE PROCEDURE add_dept
(p_deptno dept.deptno%TYPE,p_dname dept.dname%TYPE,p_loc dept.loc%TYPE)
AS
BEGIN
INSERT INTO dept VALUES (p_deptno,p_dname,p_loc);
COMMIT;
END;
INSERT INTO dept VALUES (p_deptno,p_dname,p_loc);
COMMIT;
END;
--测试add_dept
BEGIN
add_dept(50,'NEC','北京');
END;
BEGIN
add_dept(50,'NEC','北京');
END;
--创建存储过程get_name2,传入员工编号,返回员工姓名
CREATE OR REPLACE PROCEDURE get_name2(p_empid NUMBER,p_name OUT VARCHAR2)
AS
CREATE OR REPLACE PROCEDURE get_name2(p_empid NUMBER,p_name OUT VARCHAR2)
AS
BEGIN
SELECT first_name||'.'||last_name INTO p_name
FROM employees
WHERE employee_id=p_empid;
END;
SELECT first_name||'.'||last_name INTO p_name
FROM employees
WHERE employee_id=p_empid;
END;
--测试get_name2
DECLARE
v_name VARCHAR2(50);
BEGIN
get_name2(100,v_name);
dbms_output.put_line(v_name);
END;
DECLARE
v_name VARCHAR2(50);
BEGIN
get_name2(100,v_name);
dbms_output.put_line(v_name);
END;