Database (237) 썸네일형 리스트형 047 - [Oracle PL/SQL] Package - guidelines for packages 패키지를 만들때 알아야하는 기본 사항들은 아래와 같습니다. 046 - [Oracle PL/SQL] Package - without body 경우에 따라서 바디가 없는 패키지를 만들때가 있다. 아래와 같은 경우가 그러하다 # we can create package specification without body # this used when we want to define global variable create or replace package global_Measurement is c_mile_to_km constant number:=1.6093; c_kilo_to_mile constant number:=0.6214; end; execute dbms_output.put_line('60 mile:='||60* global_Measurement.c_mile_to_km||' KM' ); --------------------------------.. 045 - [Oracle PL/SQL] Package - Sample create table student ( student_id number, first_name varchar2(100), birthday date, constraint student_pk primary key (student_id) ); create sequence student_seq; --we need to create a package for insert, delete, query a student --1 we create the package spec. create or replace package General_student is procedure insert_student (p_first_name varchar2, p_birthday date); procedure delete_student (.. 044 - [Oracle PL/SQL] Package # create a function to calc square area create or replace function square_area ( p_side number ) return number is begin return p_side*p_side; end; select square_area(4) from dual; -------------------------------------------- SQUARE_AREA(4) -------------- 16 # create a function to calc rectangle area create or replace function rectangle_area ( p_l number,p_w number ) return number is begin return.. 043 - [Oracle PL/SQL] Restrictions when calling functions When a function is called from update/ delete , then then the function can not Query or modify database tables modified by that statement Error: mutating table create or replace function get_sal_tax (p_sal number) return number is begin commit;-- 컴파일 오류는 없지만 함수호출시 오류발생함. if p_sal2000 order by get_sal_tax(salary); ------------------------------------------------------------ ORA-14552: cannot perf.. 042 - [Oracle PL/SQL] Functions - user-defined functions # create a function to calc tax on salary # if salary 2000 order by get_sal_tax(salary); ------------------------------------------------------------------ EMPLOYEE_ID FIRST_NAME SALARY TAX ----------- -------------------- ---------- ---------- 146 Karen 13500 2025 145 John 14000 2100 101 Neena 17000 2550 102 Lex 17000 2550 100 Steven 24100 3615 041 - [Oracle PL/SQL] Functions - no_data_found # NOW LET TRY THIS # no data found not raised in select query, because the function not have exception # PL/SQL 로 실행하면 오류가 발생한다 begin dbms_output.put_line(get_sal(9999)) ; end; ---------------------------------------------------- Error report - ORA-01403: no data found ORA-06512: at "HR.GET_SAL", line 7 ORA-06512: at line 2 01403. 00000 - "no data found" *Cause: No data was found from the object.. 040 - [Oracle PL/SQL] Creating function # create a function to return the salary for an employee # so we need one parameter (in ) number ( employee_id ) # the return value should be also number because it is the salary create or replace function get_sal (p_emp_id number) return number is v_sal number; begin select salary into v_sal from employees where employee_id=p_emp_id; return v_sal; end; ------------------------------------------.. 이전 1 ··· 7 8 9 10 11 12 13 ··· 30 다음
