PLSQL初级爬坑篇
来源:互联网 发布:显示器颜色校准软件 编辑:程序博客网 时间:2024/06/13 04:48
今天一下午都在看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 让鼠标所在的变量高亮:
tools --> performance --> Editor, 找到 Hightlight Color,设置颜色。
5 更新中。。。
- PLSQL初级爬坑篇
- PLSQL
- PLSQL
- PLSQL
- plsql
- plsql
- plsql
- plsql
- plsql
- PLSQL
- PLsql
- PLsql
- plsql
- plsql
- plsql
- PLSQL
- plsql
- PLSQL
- touch的理解
- 一个比较有意思的算法题,猴子选大王
- PCM 音量调节
- memcached了解使用和常用命令详解
- RabbitMQ消息队列(七):适用于云计算集群的远程调用(RPC)
- PLSQL初级爬坑篇
- svn错误信息汇总
- Android 之屏幕适配
- 最好理解的卷积
- stm32 usb hid
- Elasticsearch中同时实现对某一字段的精确查询和模糊查询的方法
- move构造函数,move赋值函数
- axis实现webservice
- input边框隐藏