1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121
|
--添加列 ALTER table tbl_company_emp add description varchar2(200) null;
select * from tbl_company_emp;
insert into tbl_company_emp (empno) values (1);
create or replace package pkg_emp as type tmp_tbl is table of emp%rowtype index by binary_integer; type emprectyp is record( emp_no number, sal number ) procedure fire_employye(p_emp_id number); end pkg_emp;
create or replace package body pkg_emp as procedure fire_employye(p_emp_id number) is begin delete form emp where empno=emp_id; end; end pkg_emp
create or replace package pkg_test is procedure add_data(p_empno in tbl_company_emp.empno%type, p_ename tbl_company_emp.ename%type, p_job tbl_company_emp.job%type); procedure update_data(p_empno tbl_company_emp.empno%type, p_ename tbl_company_emp.ename%type, p_job tbl_company_emp.job%type); end pkg_test;
--特别注意分号结尾!!!! create or replace package body pkg_test is
procedure add_data(p_empno in tbl_company_emp.empno%type, p_ename tbl_company_emp.ename%type, p_job tbl_company_emp.job%type) is v_empnocount NUMBER; begin select count(*) into v_empnocount from tbl_company_emp where empno = p_empno; if v_empnocount = 0 then insert into tbl_company_emp (empno) values (p_empno); end if; end; procedure update_data(p_empno tbl_company_emp.empno%type, p_ename tbl_company_emp.ename%type, p_job tbl_company_emp.job%type) is begin update TBL_COMPANY_EMP set ename= p_ename, job = p_job where empno=p_empno; end; end pkg_test;
select * from TBL_COMPANY_EMP;
begin pkg_test.update_data('0489', 'dfgs', 'sdg'); end;
update TBL_COMPANY_EMP set ename='abc',job='job' where empno='1'
declare empno TBL_COMPANY_EMP.Empno%type := 44; job TBL_COMPANY_EMP.Job%type := 'gggg'; ename TBL_COMPANY_EMP.ename%type := 'rrr'; dml_sql varchar(2000) := 'update TBL_COMPANY_EMP set :setValSql where :conditionSql'; set_val_sql varchar(2000) :=''; condition_sql varchar(2000) :=''; begin set_val_sql := set_val_sql || ',job='''||job||''''; if job <> '' then set_val_sql := set_val_sql || ',job='||job; end if; set_val_sql := set_val_sql || ',ename='''||ename||''''; if ename <> '' then set_val_sql := set_val_sql || ',ename='||ename; end if; DBMS_OUTPUT.PUT_LINE(set_val_sql); set_val_sql := substr(set_val_sql,2); condition_sql := ' empno='||empno; DBMS_OUTPUT.PUT_LINE(set_val_sql); DBMS_OUTPUT.PUT_LINE(condition_sql); execute immediate dml_sql using set_val_sql,condition_sql; end;
--动态sql create or replace function get_tablecount(table_name in varchar2) return pls_integer is sql_query varchar2(9999) := 'select count(*) from ' || table_name; l_return pls_integer; begin execute immediate sql_query into l_return; return l_return; end;
declare v_count pls_integer; begin v_count := get_tablecount('tbl_company_emp'); DBMS_OUTPUT.PUT_LINE('tbl_company_emp count:' || v_count); end;
|