PLSQL初级爬坑篇

来源:互联网 时间:1970-01-01

今天一下午都在看oracle存储过程、PLSQL的用法,完全初级,所以免不了各种查资料,现把各种新收获记录于此。

1 表

insert into students(id,name) values('1', 'guojb');;

update students set name = 'philip';

update 一行数据:

update 一行记录:
    update temploy
       set empname   = p_name,
           empgender = p_gender,
           empdpt    = p_dpt,
           entertime = p_entertime
     where empid = p_id;


2 存储过程:

创建存储过程:

CREATE OR REPLACE PROCEDURE PINSERT_EMPLOY_INFO(P_ID        IN NUMBER,
                                                P_NAME      IN VARCHAR2,
                                                P_GENDER    IN VARCHAR2,
                                                P_DPT       IN VARCHAR2,
                                                P_ENTERTIME IN DATE,
                                                RESULTSTR   OUT VARCHAR2) IS
  V_LINE NUMBER(10);
BEGIN
  SELECT COUNT(*) INTO V_LINE FROM TEMPLOY WHERE EMPID = P_ID;
  IF V_LINE <= 0 THEN
    INSERT INTO TEMPLOY
      (EMPID, EMPNAME, EMPGENDER, EMPDPT, ENTERTIME)
    VALUES
      (P_ID, P_NAME, P_GENDER, P_DPT, P_ENTERTIME);
  ELSE
    UPDATE TEMPLOY
       SET EMPNAME   = P_NAME,
           EMPGENDER = P_GENDER,
           EMPDPT    = P_DPT,
           ENTERTIME = P_ENTERTIME
     WHERE EMPID = P_ID;
  END IF;
  RESULTSTR := 'insert sucess with id: ' || P_ID;
  RETURN;
EXCEPTION
  WHEN OTHERS THEN
    RESULTSTR := 'insert with exception';
    RETURN;
END PINSERT_EMPLOY_INFO;

在PLSQL中测试存储过程:

新建command window,
SQL> set serveroutput on;
SQL> declare 
  2  v_result varchar2(200);
  3  begin
  4  pinsert_employ_info(6, 'guojb', 'male', 'devp', to_date('2014-04-03', 'yyyy-mm-dd'), v_result);
  5  dbms_output.put_line(v_result);
  6  end;
  7  /

  输出结果:
  insert sucess with id: 6
PL/SQL procedure successfully completed


3  在PLSQL中test时,报错,

ORA-0131:debugging requires the debug connect session system privilege
原因是用户权限不够,使用以下命令授予权限:
GRANT debug any procedure, debug connect session TO username;

4 更新中。。。


相关阅读:
Top