PLSQL P12

/*
Nama    : Izzeldin Addarda
NIM     : 41519010045
*/

SET SERVEROUTPUT ON;
SET ECHO ON;
/* Contoh Program #1 - Select Statement */
DECLARE
    v_emp_name VARCHAR2(100);
BEGIN
    SELECT first_name || ' ' || last_name
    INTO v_emp_name
    FROM employees
WHERE employee_id=100;
    DBMS_OUTPUT.PUT_LINE(v_emp_name);
END;
/

/* Contoh Program #2 - Upadate Statement */
DECLARE
    v_number_rows NUMBER(5);
BEGIN
    UPDATE employees
    SET salary = salary + 1000;
    IF SQL%NOTFOUND THEN
        DBMS_OUTPUT.PUT_LINE('Tidak ada record yang berhasil di update');
    ELSIF SQL%FOUND THEN
        v_number_rows := SQL%ROWCOUNT;
        DBMS_OUTPUT.PUT_LINE('Ada ' || v_number_rows || ' record yang telah berhasil di update');
    END IF;
END;
/

/* Di bawah ini adalah contoh program PL/SQL menggunakan Explicit Cursor: */
DECLARE
    empno employees.employee_id%TYPE;
    fname employees.first_name%TYPE;
    lname employees.last_name%TYPE;
    CURSOR emp_cursor IS SELECT employee_id, first_name, last_name FROM employees;
BEGIN
    OPEN emp_cursor;
        LOOP
        FETCH emp_cursor INTO empno, fname, lname;
            EXIT WHEN emp_cursor%NOTFOUND;
            DBMS_OUTPUT.PUT_LINE( TO_CHAR (empno) ||' '||fname ||' '|| lname);
        END LOOP;
    CLOSE emp_cursor;
END;
/

/* Menampilkan 10 data dari table employees menggunakan cursor: */
DECLARE
    empno employees.employee_id%TYPE;
    fname employees.first_name%TYPE;
    ename employees.last_name%TYPE;
    i int;
    CURSOR emp_cursor IS SELECT employee_id, first_name, last_name FROM employees;
BEGIN
    i:=0;
    -- nilai awal utk nomor
    OPEN emp_cursor;
        DBMS_OUTPUT.PUT_LINE ('10 Data Employees');
        DBMS_OUTPUT.PUT_LINE ('=================');
        DBMS_OUTPUT.PUT_LINE ('No Emp_id Name');
        DBMS_OUTPUT
        .PUT_LINE ('-----------------------');
        LOOP
            FETCH emp_cursor INTO empno, fname,ename;
            EXIT WHEN emp_cursor%NOTFOUND OR emp_cursor%ROWCOUNT > 10 ;
            i:=i+1; --menambah nomor
            DBMS_OUTPUT.PUT_LINE(I ||' ' || TO_CHAR(empno)
            ||' '|| fname ||' '|| ename);
        END LOOP;
    CLOSE emp_cursor;
END;
/