PL/SQL存储过程

存储过程语法

传入类型：输入 、输出、输入 和 输出

1.导入型形式参数（in类型）

CREATE OR REPLACE PROCEDURE raise_salary
(p_id IN emp.empno%TYPE)
IS
--变量声明
BEGIN
  UPDATE emp SET sal = sal * 1.1
  WHERE empno = p_id;
END raise_salary;
/
--存储过程的调用:
EXECUTE raise_salary (7839);

declare
  v_empno emp.empno%type :=&p_empno;
begin
  RAISE_SALARY(v_empno);
end;
/

2.导出型形式参数（out类型）

CREATE OR REPLACE PROCEDURE query_emp
(p_id IN emp.empno%TYPE,
  p_name OUT emp.ename%TYPE,
  p_salary OUT emp.sal%TYPE,
  p_comm OUT emp.comm%TYPE)
IS
BEGIN
  SELECT ename, sal, comm
  INTO p_name, p_salary, p_comm
  FROM emp
  WHERE empno = p_id;
END query_emp;
/

VARIABLE g_name VARCHAR2(25)
VARIABLE g_sal NUMBER
VARIABLE g_comm NUMBER
EXECUTE query_emp(7369, :g_name, :g_sal, :g_comm);
PRINT

declare
  v_empno emp.empno%type:=7499;
  v_sal emp.sal%type;
  v_ename emp.ename%type;
  v_comm emp.comm%type;
begin
  query_emp(v_empno,v_ename,v_sal,v_comm);
  dbms_output.put_line(v_ename||' '||v_sal||' '||v_comm);
end;
/

3.导入/导出型形式参数（in/out类型）

CREATE OR REPLACE PROCEDURE format_phone
(p_phone_no IN OUT VARCHAR2)
IS
BEGIN
p_phone_no := '(' || SUBSTR(p_phone_no,1,3) ||
')' || SUBSTR(p_phone_no,4,3) ||
'-' || SUBSTR(p_phone_no,7);
END format_phone;
/


VARIABLE g_phone_no VARCHAR2(15)
BEGIN
:g_phone_no:='8006330575';
END;
/
PRINT g_phone_no
EXECUTE format_phone (:g_phone_no)
PRINT g_phone_no

4.带有default值的参数

CREATE OR REPLACE PROCEDURE add_dept
(p_name IN dept.dname%TYPE DEFAULT 'unknown',
 p_loc IN dept.loc%TYPE DEFAULT 'BJ')
IS
BEGIN
  INSERT INTO dept(deptno,
  dname, loc)
  VALUES (departments_seq.NEXTVAL, p_name, p_loc);
END add_dept;
/

create sequence departments_seq start with 50;

BEGIN
add_dept;
add_dept ('TRAINING', 'SH');
END;
/

5.本地动态SQL语句(execute immediate)

create or replace procedure proc_test(
    table_name in varchar2,        --表名
    field1 in varchar2,                  --字段名
    datatype1 in varchar2,           --字段类型
    field2 in varchar2,                    --字段名
    datatype2 in varchar2) as        --字段类型
str_sql varchar(500);
begin
    str_sql:='create table '||table_name||'('||field1||' '||datatype1||','||field2||' '||datatype2||')';
    execute immediate str_sql;     --动态执行DDL语句
    exception
    when others then
      null;
end;

函数语法：一定要返回值并且只能返回一个值

CREATE OR REPLACE FUNCTION get_sal(p_id IN emp.empno%TYPE)RETURN NUMBERIS  v_salary emp.sal%TYPE :=0;BEGIN  SELECT sal   INTO v_salary  FROM emp  WHERE empno = p_id;  RETURN v_salary;END get_sal;/function调用VARIABLE g_salary NUMBEREXECUTE :g_salary := get_sal(7369);PRINT g_salary
declare
  v_sal number;
begin
  v_sal:=get_sal(7788);
  dbms_output.put_line(v_sal);
end;
/
select get_sal(7369) from dual

确定返回值函数与效率(可以减少函数调用次数，出现在where条件中更有利！)
确定返回值函数：当输入的值相同时，返回的值也相同，这叫做确定返回值函数

create table t1 (id varchar2(10));
begin
  for i in 1..10 loop
   insert into t1 values ('A');
  end loop;
  commit;
end;
/

创建一个普通函数
create or replace function wait_row(i_v   in varchar2,
                                    i_sec in number default 5)
  return varchar2
  parallel_enable 
as
begin
  p.g_no := p.g_no+1;--函数每调用一次计数器加1
  sys.dbms_lock.sleep(i_sec);--休眠的时间值
  return i_v;
end;
/

调用函数:
select wait_row(id,0.1) from t1;