ORACLE如何使用存储过程实现添加、修改、删除、查询

-- ORACLE如何使用存储过程实现添加、修改、删除、查询?

--现在我以scott用户中的EMP表给大家详细讲解。

SELECT * FROM EMP;

-----------------------------------
-- * 添加 *--
-----------------------------------
-- 创建
CREATE OR REPLACE PROCEDURE P_EMP_INSERT
(V_ID NUMBER,V_NAME IN VARCHAR2) -- 定义输入变量
IS
BEGIN
INSERT INTO EMP(EMPNO,ENAME) VALUES(V_ID,V_NAME);
COMMIT;
END;

-- 执行
BEGIN
P_EMP_INSERT(8000,'CC');
END;

-----------------------------------
-- * 修改 *--
-----------------------------------
-- 创建
CREATE OR REPLACE PROCEDURE P_EMP_UPDATE
(V_ID IN NUMBER,V_NAME IN VARCHAR2) -- 定义输入变量
IS
BEGIN
UPDATE EMP SET ENAME=V_NAME WHERE EMPNO=V_ID;
COMMIT;
END;

-- 执行
BEGIN
P_EMP_UPDATE(8000,'AA');
END;

-----------------------------------
-- * 删除 *--
-----------------------------------
-- 创建
CREATE OR REPLACE PROCEDURE P_EMP_DELETE
(V_ID IN NUMBER) -- 定义输入变量
IS
BEGIN
DELETE FROM EMP WHERE EMPNO=V_ID;
COMMIT;
END;

-- 执行
BEGIN
P_EMP_DELETE(8000);
END;

-----------------------------------
-- * 查询 *--
-----------------------------------
-- 条件查询 只返回一条数据
CREATE OR REPLACE PROCEDURE P_EMP_GETOBJBYID
(V_ID IN NUMBER, -- 定义输入变量
V_NAME OUT VARCHAR2) -- 定义输出变量
IS
BEGIN
SELECT ENAME INTO V_NAME FROM EMP WHERE EMPNO=V_ID;
END;

-- 执行条件查询 只返回一条数据
DECLARE
P_NAME VARCHAR2(10):='';
BEGIN
P_EMP_GETOBJBYID(7788,P_NAME);
dbms_output.put_line('PNAME:' || P_NAME);
END;

-- 模糊查询 返回多条数据
CREATE OR REPLACE PROCEDURE P_EMP_SELECT
(RESULTLIST OUT SYS_REFCURSOR,V_ID IN NUMBER,V_NAME IN VARCHAR2)
IS
SQL_STR VARCHAR2(500);
BEGIN
SQL_STR:='SELECT * FROM EMP WHERE 1=1 ';
DBMS_OUTPUT.put_line('V_ID='||V_ID || ' V_NAME='||V_NAME);
IF(V_ID<>0) THEN
BEGIN
SQL_STR:=SQL_STR || ' AND EMPNO>= ' || V_ID;
END;
END IF;
IF(V_NAME IS NOT NULL) THEN -- 判断字符串是否为空
BEGIN
SQL_STR:=SQL_STR || ' AND ENAME LIKE '''|| V_NAME ||'%'' '; -- 字符串是四个单引号 ''A%''
END;
END IF;
DBMS_OUTPUT.put_line(' SQL_STR:'||SQL_STR);-- 输出SQL语句
OPEN RESULTLIST FOR SQL_STR;
END;

-- 执行模糊查询 返回多条数据
DECLARE
MYCRS SYS_REFCURSOR;-- 注意这里用分号；
V_EMP EMP%ROWTYPE;
BEGIN
-- 调用过程，返回的是已经打开的CURSOR
P_EMP_SELECT(MYCRS,7900,'M');
LOOP
FETCH MYCRS INTO V_EMP;
EXIT WHEN MYCRS%NOTFOUND;
DBMS_OUTPUT.put_line(V_EMP.EMPNO||' '||V_EMP.ENAME);
END LOOP;
CLOSE MYCRS;
END;