随笔分类 - Oracle PLSQL 语法
摘要：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...
阅读全文
摘要：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...
阅读全文
摘要：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...
阅读全文
摘要：-- 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 (...
阅读全文
摘要：--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...
阅读全文
摘要：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 ...
阅读全文
摘要：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...
阅读全文
摘要：--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...
阅读全文
摘要：--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...
阅读全文
摘要：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...
阅读全文
摘要：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...
阅读全文
摘要：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...
阅读全文
摘要：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...
阅读全文
摘要：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...
阅读全文
摘要：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...
阅读全文
摘要：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...
阅读全文
摘要：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...
阅读全文
摘要：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...
阅读全文
摘要：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...
阅读全文
摘要：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;
阅读全文
