PLSQL_笔记及代码
PLSQL day01
sd0711
<1>hello
BEGIN
DBMS_OUTPUT.PUT_LINE('Tom''s pen');
END;
<2>%type
DECLARE
/*
v_id NUMBER(7);
v_fname VARCHAR2(25);
v_salary NUMBER(11,2);
*/
v_id s_emp.id%TYPE;
v_fname s_emp.first_name%TYPE;
v_salary s_emp.salary%TYPE;
BEGIN
SELECT id, first_name, salary
INTO v_id, v_fname, v_salary FROM s_emp
WHERE id = 1;
DBMS_OUTPUT.PUT_LINE(v_id || ' ' || v_fname || ' ' || v_salary);
END;
<3>record
DECLARE
TYPE t_emp IS RECORD(
id s_emp.id%TYPE,
fname s_emp.first_name%TYPE,
salary s_emp.salary%TYPE
);
TYPE t_emp2 IS RECORD(
id s_emp.id%TYPE,
fname s_emp.first_name%TYPE,
salary s_emp.salary%TYPE
);
v_emp t_emp;
v_emp2 t_emp2;
BEGIN
SELECT id, first_name, salary
INTO v_emp
FROM s_emp
WHERE id = 1;
--v_emp2 := v_emp;
v_emp2.id := v_emp.id;
v_emp2.fname := v_emp.fname;
v_emp2.salary := v_emp.salary;
DBMS_OUTPUT.PUT_LINE(v_id || ' ' || v_fname || ' ' || v_salary);
END;
<4>%rowtype
DECLARE
v_emp s_emp%ROWTYPE;
BEGIN
SELECT *
INTO v_emp
FROM s_emp
WHERE id = 1;
DBMS_OUTPUT.PUT_LINE(v_emp.id || ' ' || v_emp.first_name || ' ' || v_emp.salary); END;
<5>table
DECLARE
TYPE t_emp IS TABLE OF s_emp%ROWTYPE
INDEX BY BINARY_INTEGER;
v_emp t_emp;
BEGIN
SELECT *
INTO v_emp(100)
FROM s_emp
WHERE id = 1;
SELECT *
INTO v_emp(-4)
FROM s_emp
WHERE id = 2;
DBMS_OUTPUT.PUT_LINE(v_emp(100).id || ' ' || v_emp(100).first_name); DBMS_OUTPUT.PUT_LINE(v_emp(-4).id || ' ' || v_emp(-4).first_name); END;
<6>scope
<
DECLARE
v_Num NUMBER := 10;
BEGIN
<
DECLARE
v_Num NUMBER := -10;
BEGIN
DBMS_OUTPUT.PUT_LINE('v_Num: ' || Outer.v_Num);
END;
END;
<7>if
DECLARE
v_id s_emp.id%TYPE;
v_sal s_emp.salary%TYPE;
v_bonus s_emp.salary%TYPE;
BEGIN
v_id := 1;
SELECT salary
INTO v_sal
FROM s_emp
WHERE id = v_id;
IF v_sal < 1000 THEN
v_bonus := v_sal * 0.1;
ELSIF v_sal BETWEEN 1000 AND 2000 THEN
v_bonus := v_sal * 0.15;
ELSIF v_sal > 2000 THEN
v_bonus := v_sal * 0.2;
END IF;
DBMS_OUTPUT.PUT_LINE('id: ' || v_id || ' bonus: ' || v_bonus); END;
<8>loop
DECLARE
TYPE t_emp IS TABLE OF s_emp%ROWTYPE INDEX BY BINARY_INTEGER; v_emp t_emp;
v_cnt BINARY_INTEGER := 1;
BEGIN
LOOP
SELECT * INTO v_emp(v_cnt)
FROM s_emp
WHERE id = v_cnt;
v_cnt := v_cnt + 1;
EXIT WHEN v_cnt > 5;
END LOOP;
v_cnt := 1;
WHILE v_cnt <=5 LOOP
DBMS_OUTPUT.PUT_LINE(v_emp(v_cnt).id || ' ' || v_emp(v_cnt).salary);
v_cnt := v_cnt + 1;
END LOOP;
END;
<9>for
DECLARE
v_emp s_emp%ROWTYPE;
BEGIN
FOR v_cnt IN REVERSE 1..5 LOOP
SELECT * INTO v_emp
FROM s_emp
WHERE id = v_cnt;
DBMS_OUTPUT.PUT_LINE(v_emp.id || ' ' || v_emp.salary);
END LOOP;
END;
<10>sursor
DECLARE
v_emp s_emp%ROWTYPE;
v_did s_emp.dept_id%TYPE := 31;
CURSOR cur_emp IS SELECT * FROM s_emp WHERE dept_id = v_did; BEGIN
OPEN cur_emp;
FETCH cur_emp INTO v_emp;
DBMS_OUTPUT.PUT_LINE(v_emp.id || ' ' || v_emp.first_name);
FETCH cur_emp INTO v_emp;
DBMS_OUTPUT.PUT_LINE(v_emp.id || ' ' || v_emp.first_name);
CLOSE cur_emp;
END;
<11>sursor
DECLARE
v_emp s_emp%ROWTYPE;
CURSOR cur_emp IS
SELECT a.* FROM s_emp a, s_dept b
WHERE a.dept_id = b.id AND b.region_id = 1;
BEGIN
OPEN cur_emp;
LOOP
FETCH cur_emp INTO v_emp;
EXIT WHEN cur_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_emp.id || ' ' || v_emp.first_name || ' ' || v_emp.dept_id); END LOOP;
CLOSE cur_emp;
END;
<12>sursor
DECLARE
v_dept s_emp.dept_id%TYPE;
v_avgsal s_emp.salary%TYPE;
CURSOR cur_emp IS
SELECT dept_id, avg(salary) avgsal FROM s_emp
GROUP BY dept_id ORDER BY avgsal DESC;
BEGIN
OPEN cur_emp;
FETCH cur_emp INTO v_dept, v_avgsal;
WHILE cur_emp%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(v_dept || ' ' || v_avgsal);
FETCH cur_emp INTO v_dept, v_avgsal;
END LOOP;
CLOSE cur_emp;
END;
PLSQL day01
<13>sursor
DECLARE
/*
CURSOR cur_emp IS
SELECT id, first_name, salary
FROM s_emp
WHERE dept_id = 31;
*/
BEGIN
--FOR v_emp IN cur_emp LOOP
FOR v_emp IN (SELECT id, first_name, salary FROM s_emp
WHERE dept_id = 31)
LOOP
DBMS_OUTPUT.PUT_LINE(v_emp.id || ' ' || v_emp.first_name || ' ' || v_emp.salary); END LOOP;
END;
<14>sursor
DECLARE
v_emp s_emp%ROWTYPE;
CURSOR cur_emp(p_deptNUMBER) IS
SELECT * FROM s_emp WHERE dept_id = p_dept FOR UPDATE;
BEGIN
OPEN cur_emp(31);
LOOP
FETCH cur_emp INTO v_emp;
EXIT WHEN cur_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_emp.id || ' ' || v_emp.first_name);
END LOOP;
CLOSE cur_emp;
END;
<15>sursor
BEGIN
UPDATE s_emp SET salary = 1000
WHERE first_name = 'zhang';
IF SQL%ROWCOUNT = 0 THEN
INSERT INTO s_emp (id, first_name, last_name, salary) values(s_emp_id.nextval, 'zhang', 'san', 1000);
END IF;
END;
<16>exception
DECLARE
v_emp s_emp%ROWTYPE;
v_id s_emp.id%TYPE := 100;
e_MyException EXCEPTION;
BEGIN
SELECT * INTO v_emp FROM s_emp WHERE id = v_id;
IF v_emp.salary < 1000 THEN
RAISE e_MyException;
END IF;
EXCEPTION
WHEN e_MyException THEN
DBMS_OUTPUT.PUT_LINE('error: ' || SQLCODE);
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('error: ' || SQLCODE);
DBMS_OUTPUT.PUT_LINE('error: ' || SQLERRM);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('other error: ' || SQLCODE);
END;
<17>procedure
CREATE OR REPLACE PROCEDURE pro_hello(
p_id IN s_emp.id%TYPE,
p_fname OUT s_emp.first_name%TYPE) IS
BEGIN
SELECT first_name INTO p_fname FROM s_emp WHERE id = p_id;
END;
<18>test<17>
DECLARE
v_fname s_emp.first_name%TYPE;
BEGIN
--pro_hello;
--pro_hello(2);
pro_hello(2, v_fname);
DBMS_OUTPUT.PUT_LINE('hello, ' || v_fname);
END;
<19>procedure
CREATE OR REPLACE PROCEDURE pro_param(
p_out OUT NUMBER,
p_in IN NUMBER := 100);
BEGIN
DBMS_OUTPUT.PUT_LINE('p_in: ' || p_in);
p_out := -10;
DBMS_OUTPUT.PUT_LINE('p_out: ' || p_out);
--p_in := 100;
END;
<20>test<19>
DECLARE
v_out NUMBER := 10;
BEGIN
--pro_param(1, v_out);
pro_param(1, p_out=>v_out);
END;
<21>function
CREATE OR REPLACE FUNCTION fun_hello(
p_id IN s_emp.id%TYPE)
RETURN s_emp.first_name%TYPE IS
v_fname s_emp.first_name%TYPE;
BEGIN
SELECT first_name INTO v_fname FROM s_emp WHERE id = p_id; RETURN v_fname;
END;
<22>test<21>
DECLARE
v_fname s_emp.first_name%TYPE;
BEGIN
v_fname := fun_hello(1);
DBMS_OUTPUT.PUT_LINE('hello, ' || v_fname);
END;
<23>procedure
CREATE OR REPLACE PROCEDURE pro_getName(
p_id IN s_emp.id%TYPE,
p_fname OUT s_emp.first_name%TYPE)
IS
BEGIN
SELECT b.first_name INTO p_fname FROM s_emp a, s_emp b
WHERE a.manager_id = b.id(+) AND a.id = p_id;
END;
<24>test<23>
DECLARE
v_manager s_emp.first_name%TYPE;
BEGIN
pro_getName(2, v_manager);
DBMS_OUTPUT.PUT_LINE('manager: ' || v_manager);
END;
<25>function
CREATE OR REPLACE FUNCTION fun_calSalary(
p_id s_emp.id%TYPE,
p_mon NUMBER)
RETURN s_emp.salary%TYPE IS
v_pct s_https://www.360docs.net/doc/0d2033978.html,mission_pct%TYPE;
v_base s_emp.salary%TYPE;
v_SaleAmt s_ord.total%TYPE;
BEGIN
SELECT salary, commission_pct INTO v_base, v_pct FROM s_emp WHERE id = p_id; IF v_pct IS NULL THEN
RETURN v_base;
ELSE
SELECT SUM(total) INTO v_SaleAmt FROM s_ord
WHERE sales_rep_id=p_id AND to_number(to_char(date_ordered, 'mm'))=p_mon;
IF v_SaleAmt IS NULL THEN
RETURN v_base;
ELSE
RETURN v_base + v_SaleAmt*v_pct/100;
END IF;
END IF;
END;
<26>test<25>
DECLARE
v_sal s_emp.salary%TYPE;
BEGIN
v_sal := fun_calSalary(11, 9);
DBMS_OUTPUT.PUT_LINE('salary: ' || v_sal);
END;
<27>package
CREATE OR REPLACE PACKAGE pack_emp IS
v_emp s_emp%ROWTYPE;
PROCEDURE AddEmp(p_emp s_emp%ROWTYPE);
END;
<28>package body
CREATE OR REPLACE PACKAGE BODY pack_emp IS
PROCEDURE AddEmp(p_emp s_emp%ROWTYPE)
IS
BEGIN
INSERT INTO s_emp(id, first_name, last_name, salary) VALUES(s_emp_id.nextval,
p_emp.first_name, p_https://www.360docs.net/doc/0d2033978.html,st_name, p_emp.salary);
COMMIT;
END;
END;
<29>test<28>
BEGIN
pack_emp.v_emp.first_name := 'li';
pack_emp.v_https://www.360docs.net/doc/0d2033978.html,st_name := 'si';
pack_emp.v_emp.salary := 1100;
pack_emp.AddEmp(pack_emp.v_emp);
END;
<30>trigger
CREATE OR REPLACE TRIGGER trg_emp
AFTER INSERT OR UPDATE OR DELETE ON s_emp FOR EACH ROW
DECLARE
v_cnt NUMBER;
BEGIN
SELECT count(*) INTO v_cnt FROM s_emp;
DBMS_OUTPUT.PUT_LINE(v_cnt || ' rows in s_emp' );
END;
<31>本地动态SQL
DECLARE
v_sqlstr VARCHAR2(500);
v_col VARCHAR2(20);
BEGIN
/*
v_sqlstr := 'create table temp_cj(id number(1), col varchar2(20))'; EXECUTE IMMEDIATE v_sqlstr;
*/
v_sqlstr := 'insert into temp_cj values(:v1, :v2)';
FOR v_cnt IN 1..5 LOOP
v_col := 'row ' || v_cnt || ' inserted';
EXECUTE IMMEDIATE v_sqlstr USING v_cnt, v_col;
END LOOP;
END;