plsql
1.HelloWorld
DECLARE
i integer;
begin
DBMS_OUTPUT.PUT_LINE(‘hello world‘);
end;
2.Assignment
DECLARE
V_NAME varchar(20) := ‘张三‘;
V_SAL NUMBER;
V_ADDR VARCHAR(200);
BEGIN
V_SAL := 1500
DBMS_OUTPUT.PUT_LINE(‘姓名:‘||V_NAME||‘, 薪水‘||V_SAL)
END
3.SELECT
DECLARE
V_NAME emp.ename%TYPE;
V_SAL emp.sal%TYPE;
BEGIN
SELECT ename, sal into V_NAME, V_SAL FROM emp where empno=7839;
DBMS_OUTPUT.PUT_LINE(‘姓名:‘||V_NAME||‘, 薪水‘||V_SAL)
END
4.RECORD
DECLARE
v_emp emp%ROWTYPE
BEGIN
SELECT * into v_emp FROM emp where empno=7839;
DBMS_OUTPUT.PUT_LINE(‘姓名:‘||v_emp.ename||‘, 薪水‘||v_emp.sal);
END
5.IF STMT
DECLARE
v_count NUMBER;
BEGIN
SELECT count(1) INTO v_count FROM EMP;
IF v_count > 20 THEN
DBMS_OUTPUT.PUT_LINE(‘记录超过20条‘||NUMBER);
ELSIF v_count >= 10 THEN
DBMS_OUTPUT.PUT_LINE(‘记录超过10条‘||NUMBER);
ELSE
DBMS_OUTPUT.PUT_LINE(‘记录10条以下‘||NUMBER);
END IF;
END;
6.LOOP
-- 循环打印1~10
DECLARE
v_num NUMBER := 1;
BEGIN
LOOP
EXIT WHEN v_num > 10;
DBMS_OUTPUT.PUT_LINE(v_num);
END LOOP;
END;
7.CURSOR
DECLARE
-- cursor
CURSOR c_emp IS SELECT ename, sal FROM emp;
-- data
v_ename emp.ename%TYPE;
v_sal emp.sal%TYPE;
BEGIN
-- open cursor
OPEN c_emp;
-- loop
LOOP
-- get data
FETCH c_emp INTO v_ename, v_sal;
-- exit
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_ename||‘-‘||v_sal);
END LOOP;
-- close
CLOSE c_emp;
END;
8.PARAM CURSOR
DECLARE
-- cursor
CURSOR c_emp(v_deptno emp.deptno%TYPE) IS SELECT ename, sal FROM emp where deptno=v_deptno;
-- data
v_ename emp.ename%TYPE;
v_sal emp.sal%TYPE;
BEGIN
-- open cursor
OPEN c_emp(10);
-- loop
LOOP
-- get data
FETCH c_emp INTO v_ename, v_sal;
-- exit
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_ename||‘-‘||v_sal);
END LOOP;
-- close
CLOSE c_emp;
END;
9.Procedure
-- 创建存储过程
CREATE OR REPLACE PROCEDURE p_hello as
BEGIN
DBMS_OUTPUT.PUT_LINE(‘hello world‘);
END p_hello;
-- 调用存储过程
BEGIN
p_hello;
p_hello;
p_hello;
END;
-- 或者
exec p_hello;
10.PARAM Procedure
-- 创建存储过程
CREATE OR REPLACE PROCEDURE query_emp(i_empno IN emp.empno%TYPE) as
v_name emp.name%TYPE;
v_sal emp.sal%TYPE;
BEGIN
SELECT ename, sal into v_name, v_sal FROM emp where empno = i_empno
DBMS_OUTPUT.PUT_LINE(v_name || ‘ - ‘ || v_sal);
END query_emp;
-- 调用
DECLARE
i INTEGER := 7839;
BEGIN
query_emp(i);
END;
-- 或者
exec query_emp(7839);
11.INPUT OUTPUT
-- 创建存储过程
CREATE OR REPLACE PROCEDURE query_emp(i_empno IN emp.empno%TYPE, o_sal OUT emp.sal%TYPE) AS
BEGIN
SELECT sal into sal FROM emp where empno = i_empno
END query_emp;
-- 调用
DECLARE
v_sal emp.sal%TYPE ;
BEGIN
query_emp(7839, v_sal);
DBMS_OUTPUT.PUT_LINE(v_sal);
END;