plsql-存储过程

CREATE OR REPLACE PROCEDURE p1
(v_id in emp.empno%TYPE)
IS
BEGIN
UPDATE emp
SET sal = sal +1
WHERE empno = v_id;
commit;
END p1;
/

 

-验证原程序
select text from user_source where NAME =‘P1’;--大写
select OBJECT_NAME,OBJECT_TYPE,STATUS from user_objects
where OBJECT_TYPE='PROCEDURE';
EXECUTE P1 (7369); --单独运行过程
Begin --在模块中调用
P1(7900);
P1 (7902);
P1 (7839);
end;
/

参数导入和导出型的存储过程(in/out)
CREATE OR REPLACE PROCEDURE query_emp
(v_id        IN emp.empno%TYPE,
v_name  OUT emp.ename%TYPE,
v_salary  OUT emp.sal%TYPE,
v_comm OUT emp.comm%TYPE)
IS
BEGIN
SELECT ename, sal, comm
INTO v_name, v_salary, v_comm
FROM emp
WHERE empno = v_id;
END query_emp;
/
传变量到调用的模块
declare
v1 emp.ename%TYPE;
v2 emp.sal%TYPE;
v3 emp.comm%TYPE;
begin
query_emp(7654,v1,v2,v3);
dbms_output.put_line(v1);
dbms_output.put_line(v2);
dbms_output.put_line(v3);
end;
/

 

参数导入和导出共用变量型的存储过程(inout)
CREATE OR REPLACE PROCEDURE format_phone
(v_phone_no IN OUT VARCHAR2)
IS
BEGIN
v_phone_no := '(' || SUBSTR(v_phone_no,1,3) ||
')' || SUBSTR(v_phone_no,4,3) ||
'-' || SUBSTR(v_phone_no,7);
END format_phone;
/
传变量到调用的模块
declare
v1 varchar2(20);
begin
v1:='010456789';
format_phone(v1);
dbms_output.put_line(v1);
end;
/