摘要：
DECLARE v_sql VARCHAR2(1000) := ''; v_count NUMBER; BEGIN v_sql := v_sql || 'select count(1) from scott.emp t'; EXECUTE IMMEDIATE v_sql INTO v_count; dbms_output.put_li...
阅读全文
posted @ 2015-06-29 23:11
nick_huang
阅读(392)
推荐(0)
摘要：
CREATE OR REPLACE FUNCTION function_name RETURN DATE AS v_date DATE; BEGIN SELECT t.hiredate INTO v_date FROM scott.emp t WHERE rownum = 1; dbms_output.put_line(v_date); RETURN v_dat...
阅读全文
posted @ 2015-06-29 23:10
nick_huang
阅读(280)
推荐(0)
摘要：
declare v_sal number(5) := 6000; begin --if you could not see the output in console, you should set output on first use the command in command line : set serveroutput on dbms_output.put_l...
阅读全文
posted @ 2015-06-29 23:09
nick_huang
阅读(199)
推荐(0)
摘要：
-- refer: -- http://www.cnblogs.com/gnielee/archive/2009/09/09/1563154.html -- http://www.cnblogs.com/yudy/archive/2012/07/18/2597874.html CREATE OR REPLACE TYPE ty_str_split IS TABLE OF VARCHAR2 (...
阅读全文
posted @ 2015-06-29 23:08
nick_huang
阅读(302)
推荐(0)
摘要：
--Count the length of string select lengthb('select * from scott.emp') as countted_by_byte, length('select * from scott.emp') as countted_by_char from dual; --For some character encoding, the length...
阅读全文
posted @ 2015-06-29 23:07
nick_huang
阅读(815)
推荐(0)
摘要：
declare Type ref_cur_variable IS REF cursor; cur_variable ref_cur_variable; v_ename varchar2(10); v_deptno number(2); v_sal number(7,2); v_sql varchar2(100) := 'select ...
阅读全文
posted @ 2015-06-29 23:06
nick_huang
阅读(228)
推荐(0)
摘要：
create or replace function function_demo RETURN emp PIPELINED as Type ref_cur_emp IS REF CURSOR RETURN emp%RowType; cur_emp ref_cur_emp; rec_emp cur_emp%RowType; begin Ope...
阅读全文
posted @ 2015-06-29 23:05
nick_huang
阅读(210)
推荐(0)
摘要：
--PACKAGE CREATE OR REPLACE PACKAGE test_141215 is TYPE type_ref IS record( ENAME VARCHAR2(20), SAL NUMBER(10)); TYPE t_type_ref IS TABLE OF type_ref; FUNCTION retrie...
阅读全文
posted @ 2015-06-29 23:04
nick_huang
阅读(237)
推荐(0)
摘要：
--PACKAGE CREATE OR REPLACE PACKAGE test_141213 is TYPE type_ref IS record( ENAME VARCHAR2(20), WORK_CITY VARCHAR2(20), SAL NUMBER(10)); TYPE t_type_ref IS TABLE OF ty...
阅读全文
posted @ 2015-06-29 23:03
nick_huang
阅读(239)
推荐(0)
摘要：
declare Type ref_cur_variable IS REF cursor; cur_variable ref_cur_variable; v_empno scott.emp.empno%type; v_ename scott.emp.ename%type; v_sql varchar2(100) := 'select t.empno, t...
阅读全文
posted @ 2015-06-29 23:02
nick_huang
阅读(210)
推荐(0)
摘要：
declare Type ref_cur_variable IS REF cursor; cur_variable ref_cur_variable; rec_emp scott.emp%RowType; v_sql varchar2(100) := 'select * from scott.emp t'; begin Open cur_varia...
阅读全文
posted @ 2015-06-29 23:01
nick_huang
阅读(294)
推荐(0)
摘要：
declare Type ref_cur_emp IS REF CURSOR RETURN scott.emp%RowType; cur_emp ref_cur_emp; rec_emp cur_emp%RowType; v_sql varchar2(100) := 'select * from scott.emp t'; begin -- xxx...
阅读全文
posted @ 2015-06-29 23:00
nick_huang
阅读(376)
推荐(0)
摘要：
declare v_empno scott.emp.empno%type; v_sal scott.emp.sal%type; cursor cur_emp(v_empno number default 7369) is select t.empno, t.sal from scott.emp t where t.empno = v_empno; b...
阅读全文
posted @ 2015-06-29 22:59
nick_huang
阅读(314)
推荐(0)
摘要：
declare r_emp scott.emp%rowtype; cursor cur_emp is select t.* from scott.emp t; begin open cur_emp; if cur_emp%isopen then dbms_output.put_line('is open...'); e...
阅读全文
posted @ 2015-06-29 22:58
nick_huang
阅读(499)
推荐(0)
摘要：
CREATE OR REPLACE PACKAGE BODY temp_package_demo is FUNCTION f_demo(userid NUMBER) RETURN BOOLEAN IS v_temp varchar2(1); BEGIN SELECT 1 INTO v_temp FROM scott.emp WHERE empno...
阅读全文
posted @ 2015-06-29 22:57
nick_huang
阅读(405)
推荐(0)
摘要：
CREATE OR REPLACE PACKAGE temp_package_demo is v_demo NUMBER(3); PROCEDURE p_demo_1(userid NUMBER DEFAULT v_demo, SAL number); FUNCTION f_demo(userid NUMBER) RETURN BOOLEAN; END temp_pack...
阅读全文
posted @ 2015-06-29 22:56
nick_huang
阅读(362)
推荐(0)
摘要：
declare cursor cur_emp is select t.* from scott.emp t; begin for r_emp in cur_emp loop dbms_output.put_line(r_emp.empno || ' ' || r_emp.sal); end lo...
阅读全文
posted @ 2015-06-29 22:55
nick_huang
阅读(708)
推荐(0)
摘要：
declare r_emp scott.emp%rowtype; cursor cur_emp is select t.* from scott.emp t; begin open cur_emp; loop fetch cur_emp into r_emp; exit whe...
阅读全文
posted @ 2015-06-29 22:54
nick_huang
阅读(985)
推荐(0)
摘要：
declare v_empno scott.emp.empno%type; v_sal scott.emp.sal%type; cursor cur_emp is select t.empno, t.sal from scott.emp t; begin open cur_emp; loop fetch cur_e...
阅读全文
posted @ 2015-06-29 22:53
nick_huang
阅读(297)
推荐(0)
摘要：
declare v_sal number := 6000; begin loop v_sal := v_sal + 1; dbms_output.put_line(v_sal); exit when v_sal = 8000; end loop; end;
阅读全文
posted @ 2015-06-29 22:52
nick_huang
阅读(2458)
推荐(0)
摘要：
declare v_sal number := 6000; begin loop v_sal := v_sal + 1; dbms_output.put_line(v_sal); if v_sal = 8000 then exit; end if; end loo...
阅读全文
posted @ 2015-06-29 22:51
nick_huang
阅读(1003)
推荐(0)
摘要：
declare v_sal number := 6000; begin while (v_sal < 8000) loop v_sal := v_sal + 1; dbms_output.put_line(v_sal); end loop; end;
阅读全文
posted @ 2015-06-29 22:49
nick_huang
阅读(233)
推荐(0)
摘要：
declare v_display varchar2(10); begin for i in 1 .. 100 loop for j in reverse 1 .. 10 loop dbms_output.put_line(i || ' - ' || j); end loop; end loop...
阅读全文
posted @ 2015-06-29 22:48
nick_huang
阅读(246)
推荐(0)
浙公网安备 33010602011771号